[MDEV-6714] mysqldump slow with tables in big databases Created: 2014-09-09  Updated: 2015-06-01  Resolved: 2015-05-30

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 5.5.40, 10.0.14
Fix Version/s: 10.1.5

Type: Bug Priority: Major
Reporter: Stoykov (Inactive) Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: mysqldump, verified


 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 .

 Comments   
Comment by Elena Stepanova [ 2015-02-22 ]

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.

Comment by Elena Stepanova [ 2015-02-27 ]

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.

Comment by Vicențiu Ciorbaru [ 2015-03-19 ]

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.

Comment by Vicențiu Ciorbaru [ 2015-05-30 ]

Fixed with:
https://github.com/MariaDB/server/commit/ae4b24340d8f6d23ef7f4a82df3f981d65d9b060

Generated at Thu Feb 08 07:14:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.