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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5, 10.4.14
    • Fix Version/s: 10.4.19, 10.5.10
    • Component/s: Optimizer
    • Labels:
      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

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

          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:

                  Git Integration