Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.26
-
None
-
Ubuntu Xenial/Trusty
Description
Since upgrading some of our databases we have started to notice a significant performance drop due to simple indexes that were previously used, now being ignored by the optimiser. As an example we have nearly identical systems running 10.1.10 and 10.1.26. 10.1.10 is performant, and uses the index correctly, while 10.1.26 is not, and requires a FORCE index to get it to run efficiently.
Below are details of the two systems with some (superfluous) information redacted/changed. This has been most noticeable on tables we have that have many 100's of millions of rows (up to 700 million), but may have affected smaller ones as well, just that it hasn't been as obvious an issue. Note that this issue may have affected releases between 10.1.10 and 10.1.26, we can only confirm it as being at least in 10.1.26.
The only 'smoking gun' I can point to possibly at this stage is that the TABLE_ROWS and AVG_ROW_LENGTH data aren't updated on the system with the newer server version, which could point to a data issue after upgrading, however I understand that these stats aren't always correct. Prior to upgrade, this database was performant, but I don't have information as to whether these values were reporting correctly or not. FWIW, we have seen this data not up to dat
Please let me know if you need any more information. Note that this was one example of this, there was another database with completely different table and data, but with 100s of millions of rows that became similarly inefficient after upgrade, but we don't have the relative detail available for that one like for this example.
Latest 10.1.26 release with regression : (db_a)
# mysqld -V
|
mysqld Ver 10.1.26-MariaDB-1~xenial for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
|
System that is OK with older version of MariaDB : (db_b)
# mysqld -V
|
mysqld Ver 10.1.10-MariaDB-1~trusty for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
|
Below command is running a DESCRIBE on simple query of form "describe select * from big_table where char_id in ('abc','def','gef'...... approx 20,000 entries.....)".
# mysql -uroot db_a < /tmp/x.sql
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE big_table ALL char_id NULL NULL NULL 82623 Using where; Using filesort
|
|
|
# mysql -uroot db_b < /tmp/x.sql
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE big_table range char_id char_id 62 NULL 38663 Using where; Using filesort
|
Note that for only a handful of items in the IN (eg. 5) it uses the index correctly in both cases. It is however orders of magnitude more efficient to force the index with the larger IN clause. We didn't let the one not using the index run to completion as it was taking far too long, and consuming significant db resources, but it was verified multiple times before forcing the index usage which worked around the problem. The one that uses the index properly returns in seconds.
Table schema, identical on both except for AUTO_INCREMENT, some field names changed :
CREATE TABLE `big_table` ( |
`id` bigint(20) NOT NULL, |
`id1` tinyint(4) NOT NULL, |
`char1` varchar(20) DEFAULT NULL, |
`char_id` varchar(15) NOT NULL, |
`char2` varchar(30) NOT NULL DEFAULT '', |
`char3` varchar(160) NOT NULL DEFAULT '', |
`id2` tinyint(4) NOT NULL DEFAULT '0', |
`id3` tinyint(4) NOT NULL DEFAULT '0', |
`id4` int(11) NOT NULL DEFAULT '-1', |
`id5` int(11) NOT NULL DEFAULT '-1', |
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
`id6` mediumint(9) DEFAULT NULL, |
`sequence_id` int(11) NOT NULL AUTO_INCREMENT, |
`flag` tinyint(3) unsigned NOT NULL DEFAULT '0', |
`id6` mediumint(9) NOT NULL DEFAULT '-1', |
`id7` mediumint(9) NOT NULL DEFAULT '-1', |
`id8` mediumint(9) NOT NULL DEFAULT '-1', |
`id9` smallint(5) unsigned NOT NULL DEFAULT '0', |
PRIMARY KEY (`id`), |
UNIQUE KEY `sequence_id` (`sequence_id`), |
KEY `char_id` (`char_id`) |
) ENGINE=TokuDB AUTO_INCREMENT=966463846 DEFAULT CHARSET=utf8mb4 `COMPRESSION`=TOKUDB_FAST; |
Schema information from both databases for this table. Note that the stats on the disfunctional db here aren't updated :
MariaDB [db_a]> SELECT * FROM information_schema.tables WHERE table_schema = DATABASE() and table_name='big_table'\G |
*************************** 1. row ***************************
|
TABLE_CATALOG: def
|
TABLE_SCHEMA: db_a
|
TABLE_NAME: big_table
|
TABLE_TYPE: BASE TABLE |
ENGINE: TokuDB
|
VERSION: 10
|
ROW_FORMAT: Dynamic |
TABLE_ROWS: 0
|
AVG_ROW_LENGTH: 0
|
DATA_LENGTH: 78503998066
|
MAX_DATA_LENGTH: 9223372036854775807
|
INDEX_LENGTH: 22612881055
|
DATA_FREE: 18446744054885965551
|
AUTO_INCREMENT: 938484216
|
CREATE_TIME: 2017-06-18 00:53:40
|
UPDATE_TIME: 2017-09-11 21:51:12
|
CHECK_TIME: NULL |
TABLE_COLLATION: utf8mb4_general_ci
|
CHECKSUM: NULL |
CREATE_OPTIONS: `COMPRESSION`=TOKUDB_FAST
|
TABLE_COMMENT:
|
1 row in set (0.00 sec) |
MariaDB [db_b]> SELECT * FROM information_schema.tables WHERE table_schema = DATABASE() and table_name='big_table'\G |
*************************** 1. row ***************************
|
TABLE_CATALOG: def
|
TABLE_SCHEMA: db_b
|
TABLE_NAME: big_table
|
TABLE_TYPE: BASE TABLE |
ENGINE: TokuDB
|
VERSION: 10
|
ROW_FORMAT: Dynamic |
TABLE_ROWS: 713853307
|
AVG_ROW_LENGTH: 113
|
DATA_LENGTH: 80947464082
|
MAX_DATA_LENGTH: 9223372036854775807
|
INDEX_LENGTH: 24225657099
|
DATA_FREE: 12250841088
|
AUTO_INCREMENT: 966464388
|
CREATE_TIME: 2017-05-27 05:51:23
|
UPDATE_TIME: 2017-09-11 21:53:46
|
CHECK_TIME: NULL |
TABLE_COLLATION: utf8mb4_general_ci
|
CHECKSUM: NULL |
CREATE_OPTIONS: `COMPRESSION`=TOKUDB_FAST
|
TABLE_COMMENT:
|
1 row in set (0.01 sec) |
Explicit table row information for both :
MariaDB [db_a]> select count(*) from big_table; |
+-----------+ |
| count(*) | |
+-----------+ |
| 672204287 |
|
+-----------+ |
|
|
MariaDB [db_b]> select count(*) from big_table; |
+-----------+ |
| count(*) | |
+-----------+ |
| 700081095 |
|
+-----------+ |
1 row in set (3 min 42.56 sec) |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Closed
-
- links to