Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.17
-
None
-
CentOS Linux release 7.9.2009
Description
We noticed one issue on MariaDB 10.4.17 , the primary key isn't used on range lookups (full table scan is done):
|
|
# show create table jos_email_message\G
|
|
|
Table: jos_email_message
|
Create Table: CREATE TABLE `jos_email_message` (
|
`id` int(11) NOT NULL,
|
`email_message` longtext NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
|
# explain format=json SELECT count(id) FROM jos_email_message WHERE `id` between 2114506 AND 2114624\G
|
|
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "jos_email_message",
|
"access_type": "index",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"rows": 20489158,
|
"filtered": 5.8e-4,
|
"attached_condition": "jos_email_message.`id` between 2114506 and 2114624",
|
"using_index": true
|
}
|
}
|
}
|
# explain SELECT count(`id`) FROM jos_email_message WHERE `id` between 2114506 AND 2114624;
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| 1 | SIMPLE | jos_email_message | index | PRIMARY | PRIMARY | 4 | NULL | 20489158 | Using where; Using index |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
1 row in set (0.001 sec)
|
|
|
# explain SELECT count(`id`) FROM jos_email_message WHERE `id` >= 2114506;
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| 1 | SIMPLE | jos_email_message | index | PRIMARY | PRIMARY | 4 | NULL | 20489158 | Using where; Using index |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
1 row in set (0.006 sec)
|
|
|
# explain SELECT count(`id`) FROM jos_email_message WHERE `id` <= 2114506;
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
| 1 | SIMPLE | jos_email_message | index | PRIMARY | PRIMARY | 4 | NULL | 20489158 | Using where; Using index |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+
|
1 row in set (0.004 sec)
|
|
|
# explain SELECT count(`id`) FROM jos_email_message WHERE `id` = 2114506;
|
+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | SIMPLE | jos_email_message | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+
|
1 row in set (0.001 sec)
|
|
We didn't have this problem on 10.4.13, prior to upgrading to 10.4.17, and we don't have this problem on 10.3.27 (master data)
# explain SELECT count(`id`) FROM jos_email_message WHERE `id` between 2114506 AND 2114624;
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | jos_email_message | range | PRIMARY | PRIMARY | 4 | NULL | 119 | Using where; Using index |
|
+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+
|
|
We've tried re-creating the table, doing analyze and forcing the index, they didn't do any difference:
- ANALYZE TABLE jos_email_message;
- ALTER TABLE jos_email_message FORCE;
- SELECT count(`id`) FROM jos_email_message FORCE INDEX(`PRIMARY`) WHERE `id` between 2114506 AND 2114624;
I've done a copy of the table, but using a simple string on email_message field, instead of the HTML text we usually store there:
- CREATE TABLE test_tbl LIKE jos_email_message;
- INSERT INTO test_tbl SELECT id,'test' FROM jos_email_message;
and on the new table the index lookup works just fine:
# explain SELECT count(`id`) FROM `test_tbl` WHERE `id` between 2114506 AND 2114624;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | test_tbl | range | PRIMARY | PRIMARY | 4 | NULL | 119 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
The only difference between those two would be the size, the original one is around 64Gb and the test one was only around 500Mb.
Again, we didn't face this problem on 10.4.13 and don't have this problem on 10.3.27 with the exact same data.
Seems to be a regression introduced recently in MariaDB 10.4 branch.
Just let me know if you need any other info.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
Activity
Interesting, in current 10.4 (commit 04741dc736e803b0a91c76d19de464e25b4a1977), stat_n_leaf_pages is back to 358.
looking through the commits, 5fbfdae130950d0a5a07d4b909f3bf1ff0498d34..04741dc736e803b0a91c76d19de464e25b4a1977 , the only fix that looks related is:
commit 5991bd6215054f21ec5c36fc9345ffb50f1b2d04
|
Author: Eugene Kosov <claprix@yandex.ru>
|
Date: Wed Nov 25 16:01:38 2020 +0300
|
|
|
MDEV-24275 InnoDB persistent stats analyze forces full scan forcing lock crash
|
|
This is a fixup patch for MDEV-23991 afc9d00c66db946c8240fe1fa6b345a3a8b6fec1
|
|
We really should read result.n_leaf_pages, which was set previously.
|
|
Analysis and fix was provided by Jukka Santala. Thanks!
|
|
Reviewed by: Marko Mäkelä
|
|
Indeed, if I apply that patch, stat_n_leaf_pages goes back to normal.
10.4.13
Run till exit from #0 ha_innobase::estimate_rows_upper_bound (this=0x7ffe6c02b4b8) at /home/psergey/dev-git/10.4.13/storage/innobase/handler/ha_innodb.cc:138490x0000555556313bef in ha_innobase::read_time (this=0x7ffe6c02b4b8, index=0, ranges=1, rows=2436) at /home/psergey/dev-git/10.4.13/storage/innobase/handler/ha_innodb.cc:13924Value returned is $21 = 51004110.4.17
Run till exit from #0 ha_innobase::estimate_rows_upper_bound (this=0x7fbddc020758) at /home/psergey/dev-git/10.4.17/storage/innobase/handler/ha_innodb.cc:138250x000055936593d7bf in ha_innobase::read_time (this=0x7fbddc020758, index=0, ranges=1, rows=2436) at /home/psergey/dev-git/10.4.17/storage/innobase/handler/ha_innodb.cc:13900Value returned is $23 = 1424So, estimate_rows_upper_bound() has returned 1424 where the table has 10K rows! This breaks the call contract and can cause issues in filesort.