1

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.

Any ideas?

0

Add this composite index:

INDEX(letter_id, user_id)  -- in either order

Do you deliberately not have a PRIMARY KEY?

  • Dropping and re-creating the index will solve the problem - i just wanted to focus on that particular problem when upgrading TokuDB from 5.6.x to 5.7.x. And off course: primary keys would be the best solution, but from time to time there are historical reasons... – Ralf Engler Jan 4 '18 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.