TokuDBgotchas: slowINFORMATION_SCHEMATABLES_MySQL

TokuDBgotchas: slowINFORMATION_SCHEMATABLES We are usingPercona Server + TokuDB engineextensively inPercona Cloud Toolsand getting real usage operational experience with this engine. so I want to share some findings we came into SS, in hope it may help someone in their work with TokuDB.

So, one problem I faced is thatSELECT * FROM INFORMATION_SCHEMA.TABLESIs quite slow when I have thousands tables in TokuDB. How slow? For example...

select * from information_schema.tables limit 1000;...1000 rows in set (18 min 31.93 sec)

Select * frominformation_schema.tableslimit1000; ... 1000 rowsinset (18min31. 93sec)

This is very similar to what InnoDB faced a couple years back. InnoDB solved it by addingvariableinnodb_stats_on_metadata.

So what happens with TokuDB? There is an explanation from Rich Prohaska at Tokutek: "Tokudb has too much overhead for table opens. tokuDB does a calculation on the table when it is opened to see if it is empty. this calculation can be disabled when'tokudb_empty_scan=disabled'."

So let's see what we havetokudb_empty_scan=disabled

select * from information_schema.tables limit 1000;...1000 rows in set (3 min 4.59 sec)

Select * frominformation_schema.tableslimit1000; ... 1000 rowsinset (3min4. 59sec)

An impressive improvement, but still somewhat slow. Tokutek promises a fix to improve it in the next TokuDB 7.2 release.