Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.7
-
None
-
CentOs, TokuDB 7.1.0
Description
Hi All,
We are running into a very weird issue with slow and hanging queries on TokuDB 7.1.0. Here's some background and configs:
DB server has 264GB of ram. 1.7TB of RAID-10 SSD storage for data, and 876GB of RAID-1 SSD storage for binary logs.
Since beginning to write this post, we've identified the exact issue and it seems to be a bug in the way TokuDB handles indexes when an entry is deleted. Here's some background information as part of the original message I was writing, as well as our temporary workaround:
Previously we were running on MariaDB 5.3/XtraDB with no issues. The reason we switched to TokuDB is for space savings. We are now experiencing terrible performance issues on an email table, causing us to have to truncate the table every few months. We have a cleanup script running that deletes everything from the table that's older then 50 days. There are around 180M rows in the table, consisting of emails.
General config:
##Encodings/Collation
|
default-storage-engine=TokuDB
|
character-set-server=utf8
|
collation_server=utf8_unicode_ci
|
max_connections = 5000
|
thread_cache_size = 900
|
query_cache_size= 0
|
query_cache_type = 0
|
table_cache = 4096
|
TokuDB specific config:
##TokuDB
|
tokudb_cache_size = 180G
|
tokudb_lock_timeout = 20000
|
tokudb_row_format = tokudb_small
|
tokudb_directio = ON
|
tokudb_read_buf_size=1048576
|
tokudb_load_save_space=on
|
tokudb_commit_sync=off
|
Things run perfectly fine until simple queries of the type: select * from `InBox` where `id`=12345 limit 1
Here's an explain (from the slave DB, prior to our latest truncate on the master), please note that the slave server is identical to the master:
explain select * from `InBox` where `id`=752371516 limit 1;
|
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|
| 1 | SIMPLE | InBox | const | PRIMARY | PRIMARY | 8 | const | 1 | |
|
+------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
The select also returns in (0.00 sec).
Now here's one problem. If you do a query, that references an item that used to exist, but has been deleted. The query will scan the whole db. This can quickly kill the database:
If you do an explain
explain select * from `InBox` where `id`=209685691 limit 1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (35 min 10.07 sec)
|
or do a select:
select * from `InBox` where `id`=209685691 limit 1; |
Empty set (1 min 32.46 sec) |
But if you do it in an index that has never existed, this is not a problem:
select * from `InBox` where `id`=99752371516 limit 1; |
Empty set (0.00 sec) |
explain select * from `InBox` where `id`=99209685691 limit 1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.00 sec)
|
select * from `InBox` where `id`=99752371516 limit 1; |
Empty set (0.00 sec) |
As you can see by the execution times, the difference is huge, and any accidental access to an old, non-existant item is pretty catastrophic.
Our current workaround, is never to delete. We will have to wait till the table fills up, then truncate it.
Some additional worries from the previous message:
Additionally, previously on InnoDB, we would do EXPLAIN statements and use the # of rows estimated. This was very accurate to the # of rows returned. If i were to do the statement without the explain like i could do with certain indexes, EXPLAIN SELECT * from BLAH use index(`sometimeindex`) where `sometimeindex` > NOW() - interval 1 day and the # of estimated rows in the explain statement would be nearly equal to that if i ran the query itself. But now if i do that, the # of rows returned in the explain doesn't actually change.
We're worried that with the constant deletes, that the indexes are getting seriously messed up.
This is the schema for the (obfuscated) InBox table:
CREATE TABLE `InBox` ( |
`id` bigint(20) NOT NULL AUTO_INCREMENT, |
`cc1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
`cc2` timestamp NULL DEFAULT NULL, |
`cc3` tinyint(4) NOT NULL, |
`cc4` varchar(150) COLLATE utf8_unicode_ci NOT NULL, |
`cc5` varchar(150) COLLATE utf8_unicode_ci NOT NULL, |
`cc6` varchar(150) COLLATE utf8_unicode_ci NOT NULL, |
`cc7` varchar(150) COLLATE utf8_unicode_ci NOT NULL, |
`cc8` varchar(150) COLLATE utf8_unicode_ci NOT NULL, |
`cc9` longtext COLLATE utf8_unicode_ci NOT NULL, |
`cc10` longtext COLLATE utf8_unicode_ci NOT NULL, |
`cc11` timestamp NULL DEFAULT NULL, |
`cc12` tinyint(4) NOT NULL, |
`cc13` tinyint(4) NOT NULL, |
`cc14` tinyint(4) NOT NULL, |
`cc15` tinyint(4) NOT NULL, |
`cc16` tinyint(4) NOT NULL, |
`cc17` tinyint(4) NOT NULL, |
`cc18` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, |
`cc19` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, |
`cc20` varchar(255) COLLATE utf8_unicode_ci NOT NULL, |
`cc21` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `cc18` (`cc18`,`cc19`,`cc1`), |
KEY `cc11` (`cc11`,`cc1`), |
KEY `cc3` (`cc3`,`id`), |
KEY `cc4` (`cc4`,`cc1`), |
KEY `cc5` (`cc5`,`cc1`), |
KEY `cc20` (`cc20`), |
KEY `cc1` (`cc1`), |
KEY `cc16` (`cc16`,`cc1`), |
KEY `cc18_2` (`cc18`,`cc19`,`cc16`,`cc1`), |
KEY `cc21` (`cc21`), |
KEY `cc3_2` (`cc3`,`cc21`,`id`) |
) ENGINE=TokuDB AUTO_INCREMENT=934472644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=TOKUDB_SMALL; |
Here's the DELETE sql:
DELETE FROM `InBox` where `id` IN (1,2,3,4,5) limit 5; |
Thank you!
Alex