After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.
After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.
Typical table structure:
CREATE TABLE `letter_archiv_12375` ( `user_id` int(12) unsigned NOT NULL DEFAULT '0', `letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0', `crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0', `mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0', `unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0', `send_date` date NOT NULL, `code` varchar(255) NOT NULL DEFAULT '', KEY `crypt_id` (`crypt_id`), KEY `letter_id` (`letter_id`), KEY `user_id` (`user_id`) ) ENGINE=TokuDB
A simple query like that takes 4 seconds on a table with 200m rows.
UPDATE hoovie_1.letter_archiv_14167 SET unsubscribe = 1 WHERE letter_id = "784547" AND user_id = "2881564";
The cardinality values are correct. EXPLAIN will result in:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE letter_archiv_14167 NULL range letter_id,user_id letter_id 3 const 1 100.00 Using where
The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).
The EXPLAIN will change to
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE letter_archiv_14167 NULL range user_id,letter_id user_id 4 const 99 100.00 Using where
We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.
So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.