• 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 !1
    eaa00a88
Name
Last commit
Last update
neo Loading commit data...
tools Loading commit data...
.gitignore Loading commit data...
BUGS.rst Loading commit data...
CHANGELOG.rst Loading commit data...
COPYING Loading commit data...
MANIFEST.in Loading commit data...
README.rst Loading commit data...
TESTS.txt Loading commit data...
TODO Loading commit data...
UPGRADE.rst Loading commit data...
ZODB3.patch Loading commit data...
importer.conf Loading commit data...
neo.conf Loading commit data...
neoadmin Loading commit data...
neoctl Loading commit data...
neolog Loading commit data...
neomaster Loading commit data...
neomigrate Loading commit data...
neostorage Loading commit data...
setup.py Loading commit data...