Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-2104

Killed query locks ExeMgr and PrimProc and get all cpu resources

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Do
    • 1.1.7
    • Icebox
    • N/A
    • None
    • MCS installation on 1UM-1PM, CentOS7

    Description

      Killed query locks ExeMgr and PrimProc and get all cpu resources

      after query is being killed it's observed that ExeMgr and PrimProc are still still consuming all modules cpu .

      Note : ExeMgr and PrimProc get all cpu resources even after the query was remove from the mysql processlist. Below in 15 min after query was being killed
      attached also traces of ExeMgr and PrimProc

      run query towards 1TB data loaded DB
      kill the query
      check that ExeMgr and PrimProc are still using all cpu resources

       WITH frequent_ss_items AS
      (
               SELECT   Substr(i_item_desc, 1, 30 ) itemdesc,
                        i_item_sk                   item_sk,
                        d_date                      solddate,
                        Count(*)                    cnt
               FROM     store_sales,
                        date_dim,
                        item
               WHERE    ss_sold_date_sk = d_date_sk
               AND      ss_item_sk = i_item_sk
               AND      d_year IN (2000,
                                   2000+1,
                                   2000+2,
                                   2000+3)
               GROUP BY Substr(i_item_desc, 1, 30),
                        i_item_sk,
                        d_date
               HAVING   Count(*) >4), max_store_sales AS
      (
             SELECT Max(csales) tpcds_cmax
             FROM   (
                             SELECT   c_customer_sk,
                                      Sum(ss_quantity*ss_sales_price) csales
                             FROM     store_sales,
                                      customer,
                                      date_dim
                             WHERE    ss_customer_sk = c_customer_sk
                             AND      ss_sold_date_sk = d_date_sk
                             AND      d_year IN (2000,
                                                 2000+1,
                                                 2000+2,
                                                 2000+3)
                             GROUP BY c_customer_sk) a), best_ss_customer AS
      (
               SELECT   c_customer_sk,
                        Sum(ss_quantity *ss_sales_price) ssales
               FROM     store_sales,
                        customer
               WHERE    ss_customer_sk = c_customer_sk
               GROUP BY c_customer_sk
               HAVING   Sum(ss_quantity*ss_sales_price) >
                        (
                               SELECT (50/100.0)*tpcds_cmax
                               FROM   max_store_sales))
      SELECT Sum(sales)
      FROM   (
                    SELECT cs_quantity*cs_list_price sales
                    FROM   catalog_sales,
                           date_dim
                    WHERE  d_year = 2000
                    AND    d_moy = 2
                    AND    cs_sold_date_sk = d_date_sk
                    AND    cs_item_sk IN
                           (
                                  SELECT item_sk
                                  FROM   frequent_ss_items)
                    AND    cs_bill_customer_sk IN
                           (
                                  SELECT c_customer_sk
                                  FROM   best_ss_customer)
                    UNION ALL
                    SELECT ws_quantity*ws_list_price sales
                    FROM   web_sales,
                           date_dim
                    WHERE  d_year = 2000
                    AND    d_moy = 2
                    AND    ws_sold_date_sk = d_date_sk
                    AND    ws_item_sk IN
                           (
                                  SELECT item_sk
                                  FROM   frequent_ss_items)
                    AND    ws_bill_customer_sk IN
                           (
                                  SELECT c_customer_sk
                                  FROM   best_ss_customer)) a limit 100 
      

      MariaDB [(none)]> show processlist ;
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      | Id | User        | Host      | db         | Command | Time | State                    | Info                                                                                                 | Progress |
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      |  1 | system user |           | NULL       | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                 |    0.000 |
      |  2 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  4 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  3 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  5 | system user |           | NULL       | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                 |    0.000 |
      | 12 | root        | localhost | tpcds_1000 | Query   |  201 | executing                | create temporary table infinidb_vtable.$vtable_12 engine = aria as with frequent_ss_items as   (sele |    0.000 |
      | 14 | root        | localhost | NULL       | Query   |    0 | init                     | show processlist                                                                                     |    0.000 |
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      7 rows in set (0.00 sec)
       
      MariaDB [(none)]> kill 12 ;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [(none)]> show processlist ;
      +----+-------------+-----------+------------+---------+------+--------------------------+-----------------                                                                                                                                   -------------------------------------------------------------------------------------+----------+
      | Id | User        | Host      | db         | Command | Time | State                    | Info                                                                                                                                                                                                                                    | Progress |
      +----+-------------+-----------+------------+---------+------+--------------------------+-----------------                                                                                                                                   -------------------------------------------------------------------------------------+----------+
      |  1 | system user |           | NULL       | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                                                                                                                                                    |    0.000 |
      |  2 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                    |    0.000 |
      |  4 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                    |    0.000 |
      |  3 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                                                                                                                                                    |    0.000 |
      |  5 | system user |           | NULL       | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                                                                                                                                                    |    0.000 |
      | 12 | root        | localhost | tpcds_1000 | Killed  |  222 | executing                | create temporary                                                                                                                                    table infinidb_vtable.$vtable_12 engine = aria as with frequent_ss_items as   (sele |    0.000 |
      | 14 | root        | localhost | NULL       | Query   |    0 | init                     | show processlist                                                                                                                                                                                                                        |    0.000 |
      +----+-------------+-----------+------------+---------+------+--------------------------+-----------------                                                                                                                                   -------------------------------------------------------------------------------------+----------+
      7 rows in set (0.00 sec)
       
      MariaDB [(none)]> show processlist ;
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      | Id | User        | Host      | db         | Command | Time | State                    | Info                                                                                                 | Progress |
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      |  1 | system user |           | NULL       | Daemon  | NULL | InnoDB purge coordinator | NULL                                                                                                 |    0.000 |
      |  2 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  4 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  3 | system user |           | NULL       | Daemon  | NULL | InnoDB purge worker      | NULL                                                                                                 |    0.000 |
      |  5 | system user |           | NULL       | Daemon  | NULL | InnoDB shutdown handler  | NULL                                                                                                 |    0.000 |
      | 15 | root        | localhost | tpcds_1000 | Query   |   29 | executing                | create temporary table infinidb_vtable.$vtable_15 engine = aria as with frequent_ss_items as   (sele |    0.000 |
      | 16 | root        | localhost | NULL       | Query   |    0 | init                     | show processlist                                                                                     |    0.000 |
      +----+-------------+-----------+------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
      7 rows in set (0.01 sec)
      
      

      UM1

      Tasks: 214 total,   2 running, 212 sleeping,   0 stopped,   0 zombie
      %Cpu(s): 96.9 us,  0.0 sy,  0.0 ni,  1.6 id,  0.0 wa,  0.0 hi,  1.6 si,  0.0 st
      KiB Mem : 61848092 total, 35093536 free, 26280216 used,   474340 buff/cache
      KiB Swap:  1048572 total,   949844 free,    98728 used. 35036596 avail Mem
       
        PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
      22307 root      19  -1   27.5g  24.4g  11572 S 381.2 41.4  84:28.69 ExeMgr
      29494 root      20   0  162012   2272   1552 R   6.2  0.0   0:00.01 top
      
      

      PM1

      Tasks: 193 total,   1 running, 191 sleeping,   1 stopped,   0 zombie
      %Cpu(s): 56.8 us,  0.6 sy,  0.0 ni, 42.1 id,  0.4 wa,  0.0 hi,  0.1 si,  0.0 st
      KiB Mem : 61848092 total, 13504316 free, 46468524 used,  1875252 buff/cache
      KiB Swap:  1048572 total,   883196 free,   165376 used. 14678580 avail Mem
       
        PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
      21489 root      19  -1   45.4g  43.7g  12328 S 226.8 74.1  54:31.36 PrimProc
      13071 root      20   0 1705576  10484   3200 S   1.3  0.0  17:49.15 ProcMgr
      14704 root      20   0  162012   2332   1596 R   0.3  0.0   0:00.64 top
      
      

      Attachments

        1. um_trace.txt
          115 kB
        2. um_trace.txt
          115 kB
        3. MCOL-2104.url
          0.1 kB

        Issue Links

          Activity

            People

              Unassigned Unassigned
              winstone Zdravelina Sokolovska (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.