Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23634

Select query hanged the server and leads to OOM in MariaDB 10.4.14

    XMLWordPrintable

    Details

      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

        1. lq1.sql
          3.67 MB
        2. lq1.tables
          1 kB

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              niljoshi Nilnandan Joshi
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: