Bug #56965 mysqldump poor performance with large number of tables
Submitted: 23 Sep 2010 9:22 Modified: 29 Sep 2010 9:33
Reporter: Peter Surda Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.1.44 OS:Linux (2.6.18-164.6.1.el5 #1 SMP, CentOS 5.5)
Assigned to: CPU Architecture:Any
Tags: Contribution, mysqldump, performance, speed

[23 Sep 2010 9:22] Peter Surda
Description:
In several places, mysqldump tries to extract data by issuing queries "SHOW ..... LIKE" in order to extract information about objects, even if it has the exact name of the object already. This requires mysqld to scan all the files in that directory by a case insensitive substring search. If you have a lot of files (in my case, about 170k), this takes about 1 second per query while CPU (mysqld) is maxed. If you are dumping more than one object, mysqldump executes the query once per object (sometimes even more than one query per object).

The bug is similar in nature to #45526 but occurs on different occasions.

I was able to isolate three queries where SHOW ... LIKE is used (two for TABLE, one for TRIGGER) to extract data about an object whose name is already known and rewrote them to query the corresponding table from INFORMATION_SCHEMA directly. The queries themselves are significantly faster (from 1s down below to 0.1s, sometimes even 0.01s). Running full mysqldump on my system to dump all databases now takes 3 hours where it took 21 before (improvement by the factor of 7).

How to repeat:
- create a database
- create lot of tables in it (~100k)
- do mysqldump on that database (without specifying the table names)
- it will take very long (hours) even if the tables are empty
- if you dump triggers too, it will take even longer

Suggested fix:
Rewrite queries. I did that and it helped. I'll attach the patch. The patch does not fix #45526, that would according to my analysis change the behaviour.
[23 Sep 2010 9:41] Peter Surda
Fix

Attachment: mysqldump-speed.patch (text/x-patch), 5.32 KiB.

[23 Sep 2010 10:23] Peter Surda
Added missing table in the previous patch

Attachment: mysqldump-speed.patch (text/x-patch), 5.35 KiB.

[23 Sep 2010 11:15] Peter Laursen
STOP!! :-)

Please refer: bugs.mysql.com/bug.php?id=19588

It would be a disaster to replace SHOW TRIGGERS with SELECT FROM I_S.TRIGGERS as long as this is not fixed (the bug report was closed, but it is not fixed!).

We are currently rewriting our applications to use SHOW TRIGGERS instead of SELECT FROM I_S because the latter is hopeless if there are lots of databases where triggers are used extensively.
[23 Sep 2010 12:07] Peter Surda
Peter,

my patch does not trigger bug #19588 (or, it does not trigger it any more that in the absence of the patch). If you query INFORMATION_SCHEMA.TABLES or TRIGGERS, the query speed depends on whether you specify the table name explicitly or not. If you specify it explicitly (like my patch does), the directory still needs to be searched but there is no more substring search and stuff like that. There is no reason why my patch should have an adverse performance in situations where #19588 and also #45526 are present.

You can verify this easily when you compare these:

(scan one table, like my patch does, fastest)
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='db_with_many_tables' AND EVENT_OBJECT_TABLE = 'specific_table';
takes 0.01s

(scan a bit more due to a more intensive search):
SHOW TRIGGERS FROM db_with_many_tables LIKE 'trigger_name';
takes 0.70s

However, both:

SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='db_with_many_tables';

and

SHOW TRIGGERS FROM db_with_many_tables;

take forever, I aborted them after three minutes.

So, while it is true that querying INFORMATION_SCHEMA.TRIGGERS for all triggers is slower than SHOW TRIGGER for a specific trigger, by the factor of at least 257 (180/0.7), my approach is 70 times faster than SHOW TRIGGER. If you have more than 170k files, the difference is even higher :-).

I did this benchmark on a faster system than the one in the original post, so that explains why it's down from 1s to 0.70s.

In all the cases, the OS needs to read the complete directory listing, so that might slow things down. But after the OS caches the listing, LIKE becomes CPU-bound, and listing all triggers needs to open all tables (which cannot take further advantage of directory listing cache and takes forever).

Feel free to try my approach in your app. If you use SHOW TRIGGERS FROM db LIKE 'table_name', you can replace it with SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='db' AND EVENT_OBJECT_TABLE='table_name'. That should decrease CPU utilisation and have no other adverse effect.
[23 Sep 2010 12:28] Peter Laursen
We have another case from a customer where

1. SHOW TRIGGERS FROM `db`; 
.. is instantaneous, but

2. select `TRIGGER_NAME` from `INFORMATION_SCHEMA`.`TRIGGERS` where `TRIGGER_SCHEMA` = 'db'

.. takes forever.  And mysqldump (using 1) is also very fast here, but our current backup routine (using 2) 'takes forever'.

So it seems that everything can be stated and is reproducible or not depending on the environment?  All this really needs a clarification and cleanup now!
[23 Sep 2010 12:43] Peter Laursen
In our case it is not feasible everywhere to use the tablename. It is not only a backup/dump problem but we also need to list all triggers of a database in a GUI interface (and looping through all databases and tables would be horrible).

I am pretty confident that SHOW solves the actual problem for us (what does not disqualify you patch for mysqldump).

But I think MySQL developer team should get to the bottom of this now. The community cannot continue 'zigzag'ing from SHOW to SELECT and back to SHOW forever.
[23 Sep 2010 12:46] Peter Surda
Peter,

I showed four different queries performing differently. Your queries 1 and 2 correspond to my queries 4 and 3, respectively. Both of these are very slow on my system. But my patch does not deal with queries 3 and 4.
[25 Sep 2010 17:56] Sveta Smirnova
Thank you for the report.

Verified as described: could not backup 30465 empty tables in more than 1 hour.

I think your concern is correct and mysqldump should be fixed in some way.
[27 Sep 2010 19:32] Konstantin Osipov
The use of INFORMATION_SCHEMA is not going to speed things up.
[27 Sep 2010 20:04] Peter Laursen
In our (or rather our customer's) case using SHOW speeded up the listing of all triggers in a database with a factor 200-500 as compared to SELECT FROM I_S. It has now been confirmed.

It has to be said that this particular server has a lot of (I understand around 100,000) databases.
[29 Sep 2010 9:33] Peter Surda
Err, I posted a patch. You don't need to fix anything, just review/test/commit. Or, of course, reject.