Bug #33172 Backup operation too slow?
Submitted: 12 Dec 2007 16:30 Modified: 26 Feb 2008 2:12
Reporter: Rafal Somla Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0 source OS:Any
Assigned to: Chuck Bell CPU Architecture:Any

[12 Dec 2007 16:30] Rafal Somla
Description:
It takes around 1 sec to backup an empty database and my impression is that before recent changes in the online backup code such operation was instant.

How to repeat:
CREATE DATABASE foo;
BACKUP DATABASE foo TO "foo.bak";

Observe that it takes around 1 sec to complete this statement.

Suggested fix:
There are many new areas in the backup code which could introduce delays:

- the backup stream library
- the BACKUP_BREAKPOINT infrastructure
- the progress reporting functions.

One shuld try to switch off parts of the code one by one and see which causes biggest performance drop. Then investigate that part of code and see where the problem lies.
[13 Dec 2007 10:17] Susanne Ebrecht
Please, let us know the exact version you used.
[25 Jan 2008 18:49] Chuck Bell
Using crude timing calculations, I have discovered that the most time spent during a backup of a database with one table with no data is:

(in kernel.cc)        %Time
Backup_info info()    64.83%   <--- Shocking!!!
backup_progress calls 15.33%   <--- Expected, but not horrible
mysql_backup()         6.64%   <--- Curious...
adding dbs             1.22%
open stream            0.43%
check_info()           0.39%
close stream           0.24%
location::find()       0.05%
breakpoints          < 0.01%

Doing the same for Robin's database with 112,050 rows (without si_objects):

(in kernel.cc)        %Time
mysql_backup()        91.57%   <--- Normal (it's over 100k rows!)
Backup_info info()     6.41%   <--- Still significant
backup_progress calls  0.89%   <--- Still significant
adding dbs             0.18%
open stream            0.04%
check_info()           0.03%
close stream           0.02%
location::find()       0.004%
breakpoints          < 0.001%

I plan to investigate Backup_info info() constructor delays.
[25 Jan 2008 19:04] Chuck Bell
98.82% of the time spent in Backup_info info(thd) is from the following:

  i_s_tables= get_schema_table(m_thd, ::get_schema_table(SCH_TABLES));
[25 Jan 2008 19:13] Chuck Bell
I've located the bottleneck. It's here:

./kernel.cc @1900 st->fill_table(thd,&arg,NULL);  // NULL = no select condition
[30 Jan 2008 15:31] Chuck Bell
Patch submitted for review. Implementation now extends to the si_objects class and the iterators used there. Iterators now use the where clause for iterating over objects.

See http://lists.mysql.com/commits/41440.
[31 Jan 2008 14:21] Chuck Bell
Box ticked for Rafal per his email.
[31 Jan 2008 16:02] Chuck Bell
Patch queued.
[31 Jan 2008 16:02] Chuck Bell
Patch queued.
[25 Feb 2008 20:19] Bugs System
Pushed into 6.0.5-alpha
[26 Feb 2008 2:12] Paul DuBois
Noted in 6.0.5 changelog.

Some performance problems with BACKUP DATABASE were corrected.
[14 Mar 2008 1:33] Paul DuBois
Correction: No changelog entry needed; this bug did not appear in any released version.