• Kirill Smelkov's avatar
    mysql: force _getNextTID() to use appropriate/whole index · eaa00a88
    Kirill Smelkov authored
    Similarly to 13911ca3 on the same instance after MariaDB was upgraded to
    10.1.17 the following query, even after `OPTIMIZE TABLE obj`, started to execute
    very slowly:
    
        MariaDB [(none)]> SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
        +--------------------+
        | tid                |
        +--------------------+
        | 268707072758797063 |
        +--------------------+
        1 row in set (4.82 sec)
    
    Both explain and analyze says the query will/is using `partition` key but only partially (note key_len is only 10, not 18):
    
        MariaDB [(none)]> SHOW INDEX FROM neo1.obj;
        +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
        +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | obj   |          0 | PRIMARY   |            1 | partition   | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          0 | PRIMARY   |            2 | tid         | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          0 | PRIMARY   |            3 | oid         | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          0 | partition |            1 | partition   | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          0 | partition |            2 | oid         | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          0 | partition |            3 | tid         | A         |    28755928 |     NULL | NULL   |      | BTREE      |         |               |
        | obj   |          1 | data_id   |            1 | data_id     | A         |    28755928 |     NULL | NULL   | YES  | BTREE      |         |               |
        +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        7 rows in set (0.00 sec)
    
        MariaDB [(none)]> explain SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
        | id   | select_type | table | type | possible_keys     | key       | key_len | ref         | rows | Extra                    |
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
        |    1 | SIMPLE      | obj   | ref  | PRIMARY,partition | partition | 10      | const,const |    2 | Using where; Using index |
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+--------------------------+
        1 row in set (0.00 sec)
    
        MariaDB [(none)]> analyze SELECT tid FROM neo1.obj WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
        | id   | select_type | table | type | possible_keys     | key       | key_len | ref         | rows | r_rows     | filtered | r_filtered | Extra                    |
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
        |    1 | SIMPLE      | obj   | ref  | PRIMARY,partition | partition | 10      | const,const |    2 | 9741121.00 |   100.00 |       0.00 | Using where; Using index |
        +------+-------------+-------+------+-------------------+-----------+---------+-------------+------+------------+----------+------------+--------------------------+
        1 row in set (4.93 sec)
    
    By explicitly forcing (partition, oid, tid) index usage which is precisely designed to serve this and similar queries can avoid the query from being slow:
    
        MariaDB [(none)]> analyze SELECT tid FROM neo1.obj FORCE INDEX(`partition`) WHERE `partition`=5 AND oid=79613 AND tid>268707071353462798 ORDER BY tid LIMIT 1;
        +------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
        | id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                    |
        +------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
        |    1 | SIMPLE      | obj   | range | partition     | partition | 18      | NULL |    2 |   1.00 |   100.00 |     100.00 | Using where; Using index |
        +------+-------------+-------+-------+---------------+-----------+---------+------+------+--------+----------+------------+--------------------------+
        1 row in set (0.00 sec)
    
    /cc @jm, @vpelltier, @Tyagov
    
    /reviewed-on nexedi/neoppod!1
    eaa00a88
mysqldb.py 31.5 KB