Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.40, 10.0.14
-
Fix Version/s: 10.1.5
-
Component/s: Scripts & Clients
Description
On an environment with more thatn 500000 tables, the mysqldump is slow on dumping separate tables.
mysqldump uses the traditional queries like
show table status like 'a\_test\_bgtable\_2'; SHOW TRIGGERS LIKE ''a\_test\_bgtable\_2'; SHOW TABLES LIKE ''a\_test\_bgtable\_2';
instead of querying information_schema.tables .
select * from information_schema.tables where table_schema = 'test_tmp' and table_name = 'a_test_bgtable_2';
takes 0.0011s, while
use test_tmp; show tables like 'a\_test\_bgtable\_2';
takes about 10 seconds. This makes the dumps unnecessary slow.
mysqldump could use the information_schema here (after checking the server version), or the server could detect LIKE-Patterns that can only expand to exactly one table name (because there are no wildcards) and use a more intelligent algorithm internally, e.g. use the implementation of information_schema.tables .
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Okay, I got the point – it's not about using I_S vs SHOW TABLES, but about being able to use '=' instead of 'LIKE'.
I'm not getting 10 sec with SHOW TABLES .. LIKE, but it's still considerable, about 3 sec vs almost none for the I_S query.
All the changes that I've done seem to be backwards compatible to MySQL 5.0 at least. I've followed as much as possible, any previous examples within mysqldump for string formatting. I hope the final patch takes care of any pitfalls.
I've also checked the lower_case_table_names variable to see if it has any effect and the code seems to work with both cases.
Strangely, we've had an opposite report:
CONJ-35.There the user complained that a SHOW CREATE-based connector was fast, while INFORMATION_SCHEMA-based one was slow.