Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.4.14, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Hi Team,
Select query hanged the server with high memory usage and leads to OOM in MariaDB 10.4.14.
[root@localhost ~]# mysql -uroot -p --socket=/tmp/mysql_sandbox10414.sock
|
Enter password:
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 10
|
Server version: 10.4.14-MariaDB MariaDB Server
|
...
|
MariaDB [test]> CREATE TABLE `u_flexera_cloud_usage_client` (
|
...
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
|
Query OK, 0 rows affected (0.037 sec)
|
|
MariaDB [test]> CREATE TABLE `u_flexera_cloud_usage` (
|
...
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.039 sec)
|
|
MariaDB [test]> quit
|
Bye
|
[root@localhost ~]#
|
Query is something like
explain
|
SELECT u_flexera_cloud_usage_client0.`sys_id`
|
FROM (u_flexera_cloud_usage_client u_flexera_cloud_usage_client0
|
LEFT JOIN u_flexera_cloud_usage u_flexera_cloud_usage1 ON u_flexera_cloud_usage_client0.`u_usage` = u_flexera_cloud_usage1.`sys_id` )
|
WHERE u_flexera_cloud_usage1.`u_feature_name` = 'ToscaCommander' AND u_flexera_cloud_usage1.`u_server` = 'b319fdf2376a7a0ca30fa16043990e72' AND u_flexera_cloud_usage_client0.`u_host_id` IN ('6a426ad6cef5e0830cb64bfcbff4e0b3ोरोबकहसोी') AND u_flexera_cloud_usage1.`u_collected` > '2020-08-21 08:00:25' AND u_flexera_cloud_usage_client0.`sys_id` != '2c6ac48f1b7298d0f518ea0e6e4bcbb3' AND u_flexera_cloud_usage_client0.`sys_id` != '2c6ac48f1b7298d0f518ea0e6e4bcbba' ....
|
There is a long list of sys_id != and condition.
[root@localhost ~]#
|
[root@localhost ~]# mysql -uroot -p --socket=/tmp/mysql_sandbox10414.sock test < lq1.sql
|
Enter password:
|
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
|
[root@localhost ~]#
|
...
|
..
|
[root@localhost data]# cat /var/log/messages | grep mysqld
|
....
|
Aug 7 05:38:45 localhost kernel: mysqld invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
|
Aug 7 05:38:45 localhost kernel: mysqld cpuset=/ mems_allowed=0
|
Aug 7 05:38:45 localhost kernel: CPU: 0 PID: 18039 Comm: mysqld Kdump: loaded Not tainted 3.10.0-1127.el7.x86_64 #1
|
Aug 7 05:38:45 localhost kernel: [17906] 0 17906 28355 1 13 87 0 mysqld_safe
|
Aug 7 05:38:45 localhost kernel: [18004] 0 18004 1133133 431832 1713 400514 0 mysqld
|
Aug 7 05:38:45 localhost kernel: Out of memory: Kill process 18004 (mysqld) score 890 or sacrifice child
|
Aug 7 05:38:45 localhost kernel: Killed process 18004 (mysqld), UID 0, total-vm:4532532kB, anon-rss:1727328kB, file-rss:0kB, shmem-rss:0kB
|
|
[root@localhost ~]# top | grep mysqld
|
18103 root 20 0 4399336 1.6g 0 S 46.0 90.6 2:35.61 mysqld
|
18103 root 20 0 4464872 1.6g 0 S 88.8 89.9 2:38.38 mysqld
|
18103 root 20 0 4530408 1.6g 0 S 85.7 91.3 2:41.01 mysqld
|
18103 root 20 0 4530408 1.6g 0 S 19.8 91.9 2:42.33 mysqld
|
18167 root 20 0 1253308 83844 11188 S 2.6 4.5 0:00.08 mysqld -- OOM happened and server restarted.
|
17906 root 20 0 113424 892 612 S 0.3 0.0 0:00.02 mysqld_safe
|
18167 root 20 0 1253308 83844 11188 S 0.3 4.5 0:00.09 mysqld
|
Required table structures and query is attached with this bug.
Attachments
Issue Links
- causes
-
MDEV-30605 Wrong result while using index for group-by
- Closed
- duplicates
-
MDEV-26059 Query in the range optimizer doesn't react to KILL
- Closed
- relates to
-
MDEV-9764 MariaDB does not limit memory used for range optimization
- Closed
-
MDEV-21958 Query having many NOT-IN clauses running forever and causing available free memory to use completely
- Closed
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...