XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.4.8
    • N/A
    • Optimizer
    • None

    Description

      Ever since upgrading from 10.3.20 to MariaDB 10.4.8 I've been seeing problems with this kind of query.

      SELECT count(1)
      FROM invoice i,
        inv_rows ir
        LEFT JOIN product p ON (p.id = ir.product_id)
        LEFT JOIN temp_attributes a ON (
          a.obj_table = 'product'
          AND p.id = a.obj_id
          AND a.name = 'regular_giftcard'
        )
      WHERE ir.invoice_idRec = i.idRec
        AND i.state = 1
        AND i.confirmed = 1
        AND i.type_id <> 3
        AND i.type_id <> 4
        AND i.type_id <> 5
        AND i.type_id <> 7
        AND i.type_id <> 9
        AND ir.has_components = 0
        AND i.date >= '2019-01-01'
        AND i.date <= '2019-09-20';
      

      The optimizer never had any problems with this in the previous version. Now there are many cases when this query takes a long time, over 240s, which is our max_statement_time.
      The query works well when I a) calculate engine independent stats for it or b) drop the index on temp_attributes.name column. That index is needed for another query though.

      MariaDB [testdb]> set local max_statement_time=10;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
       
      ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
      MariaDB [testdb]> 
      MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
      +------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
      | id   | select_type | table | type       | possible_keys         | key            | key_len | ref                       | rows        | Extra                           |
      +------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
      |    1 | SIMPLE      | i     | ALL        | PRIMARY,date,type_id  | NULL           | NULL    | NULL                      | 492879      | Using where                     |
      |    1 | SIMPLE      | ir    | ref        | invoice_idRec         | invoice_idRec  | 4       | testdb.i.idRec       | 1           | Using where                     |
      |    1 | SIMPLE      | p     | eq_ref     | PRIMARY               | PRIMARY        | 4       | testdb.ir.product_id | 1           | Using index                     |
      |    1 | SIMPLE      | a     | ref|filter | obj_id,obj_table,name | obj_table|name | 107|152 | const                     | 301592 (0%) | Using where; Using rowid filter |
      +------+-------------+-------+------------+-----------------------+----------------+---------+---------------------------+-------------+---------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [testdb]> analyze table temp_attributes PERSISTENT FOR COLUMNS (id, obj_id, obj_table, name, value_text, value_int) 
          -> INDEXES (PRIMARY,obj_id, obj_table, name, value_text,value_int);
      +-----------------------------+---------+----------+-----------------------------------------+
      | Table                       | Op      | Msg_type | Msg_text                                |
      +-----------------------------+---------+----------+-----------------------------------------+
      | testdb.temp_attributes | analyze | status   | Engine-independent statistics collected |
      | testdb.temp_attributes | analyze | status   | OK                                      |
      +-----------------------------+---------+----------+-----------------------------------------+
      2 rows in set (3.20 sec)
       
      MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
      +----------+
      | count(1) |
      +----------+
      |    92819 |
      +----------+
      1 row in set (0.93 sec)
       
      MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
      +------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
      | id   | select_type | table | type       | possible_keys         | key           | key_len | ref                       | rows   | Extra                           |
      +------+-------------+-------+------------+-----------------------+---------------+---------+---------------------------+--------+---------------------------------+
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [testdb]> delete from mysql.column_stats;
      Query OK, 6 rows affected (0.00 sec)
       
      MariaDB [testdb]> delete from mysql.index_stats;
      Query OK, 11 rows affected (0.01 sec)
       
      MariaDB [testdb]> SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a ignore index (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
       
      +----------+
      | count(1) |
      +----------+
      |    92819 |
      +----------+
      1 row in set (1.45 sec)
       
      MariaDB [testdb]> 
      MariaDB [testdb]> explain SELECT count(1) FROM invoice i, inv_rows ir LEFT JOIN product p ON (p.id = ir.product_id) LEFT JOIN temp_attributes a IGNORE INDEX (name) ON (a.obj_table = 'product' AND p.id = a.obj_id AND a.name = 'regular_giftcard')  WHERE ir.invoice_idRec = i.idRec AND i.state=1 AND i.confirmed=1 AND i.type_id<>3 AND i.type_id<>4 AND i.type_id<>5 AND i.type_id<>7 AND i.type_id<>9 AND ir.has_components = 0 AND i.date>='2019-01-01' AND i.date<='2019-09-20';
      +------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
      | id   | select_type | table | type   | possible_keys        | key           | key_len | ref                       | rows   | Extra       |
      +------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
      |    1 | SIMPLE      | i     | ALL    | PRIMARY,date,type_id | NULL          | NULL    | NULL                      | 492879 | Using where |
      |    1 | SIMPLE      | ir    | ref    | invoice_idRec        | invoice_idRec | 4       | testdb.i.idRec       | 1      | Using where |
      |    1 | SIMPLE      | p     | eq_ref | PRIMARY              | PRIMARY       | 4       | testdb.ir.product_id | 1      | Using index |
      |    1 | SIMPLE      | a     | ref    | obj_id,obj_table     | obj_id        | 4       | testdb.p.id          | 2      | Using where |
      +------+-------------+-------+--------+----------------------+---------------+---------+---------------------------+--------+-------------+
      4 rows in set (0.00 sec)
       
      MariaDB [testdb]> show variables like 'version';
      +---------------+--------------------+
      | Variable_name | Value              |
      +---------------+--------------------+
      | version       | 10.4.8-MariaDB-log |
      +---------------+--------------------+
      1 row in set (0.00 sec)
       
      +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [testdb]> show create table temp_attributes;
      +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
      +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | temp_attributes | CREATE TABLE `temp_attributes` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `obj_id` int(11) NOT NULL,
        `obj_table` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
        `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
        `type` enum('int','text','double') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
        `value_text` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
        `value_int` int(11) NOT NULL,
        `value_double` double NOT NULL,
        PRIMARY KEY (`id`),
        KEY `obj_id` (`obj_id`),
        KEY `obj_table` (`obj_table`),
        KEY `value_text` (`value_text`),
        KEY `value_int` (`value_int`),
        KEY `name` (`name`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1700849 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=50000 |
      +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [testdb]> show variables like '%persistent%';
      +--------------------------------------+-------+
      | Variable_name                        | Value |
      +--------------------------------------+-------+
      | innodb_stats_persistent              | ON    |
      | innodb_stats_persistent_sample_pages | 30    |
      +--------------------------------------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [testdb]> show indexes from temp_attributes;
      +-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table           | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | temp_attributes |          0 | PRIMARY    |            1 | id          | A         |      967119 |     NULL | NULL   |      | BTREE      |         |               |
      | temp_attributes |          1 | obj_id     |            1 | obj_id      | A         |      967119 |     NULL | NULL   |      | BTREE      |         |               |
      | temp_attributes |          1 | obj_table  |            1 | obj_table   | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
      | temp_attributes |          1 | value_text |            1 | value_text  | A         |       80593 |     NULL | NULL   |      | BTREE      |         |               |
      | temp_attributes |          1 | value_int  |            1 | value_int   | A         |        7992 |     NULL | NULL   |      | BTREE      |         |               |
      | temp_attributes |          1 | name       |            1 | name        | A         |         376 |     NULL | NULL   |      | BTREE      |         |               |
      +-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      6 rows in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              heikilaaniste Heiki Laaniste
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.