MySQL Bugs: #33057: mysql command line client slows down and uses 100% CPU when restoring dump

Bug #33057	mysql command line client slows down and uses 100% CPU when restoring dump
Submitted:	7 Dec 2007 5:31	Modified:	8 Jan 2008 17:25
Reporter:	Alexander Rubin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.52,5.0.54	OS:	Any
Assigned to:	Daniel Fischer	CPU Architecture:	Any
Tags:	mysql client, mysql command line client, regression

Description:
mysql command line client slows down significantly and uses 100% CPU when restoring dump file. 
(extended inserts). After a while can crash.

mysql  Ver 14.12 Distrib 5.0.52, for redhat-linux-gnu (x86_64) using readline 5.1

Only affected mysql client (not server) in version 5.0.52, version 5.0.50 is fine.
mysql 5.0.52 is using readline 5.1 and mysql 5.0.50 is using readline 5.0

Tested with RedHat 4/5 and Ubuntu 7, 64bit

How to repeat:
mysqldump <db> <large_table_name> >dump.sql
(with mysql 5.0.52)
mysql <dump.sql

see process utilization with top and/or run strace
strace shows significant delays between reading data from disk

Suggested fix:
fix mysql command line client in 5.0.52

i confirm this observation on 5.0.52-win32-noinstall package.
just using some 11GB dumpfile I created, the 5.0.52 could only read about 1MB per minute, while the 5.0.50 mysql.exe could load at about 6MB per second!!

maybe fix for bug #26215 is related?  I see nothing in the changelogs for this, btw..

example regression file

Attachment: t1.sql (, text), 127.57 KiB.

I attached extremely simple dump file, that illustrates the regression:

5.0.52 and later:

real    0m17.661s
user    0m15.417s
sys     0m1.880s

5.0.50:

real    0m0.380s
user    0m0.008s
sys     0m0.012s

do note, that it is just 100k line, at few megabytes client hangs for minutes, and I guess, hours-per-query are also possible at reasonable packet sizes.

oprofile:

8408     64.7317  vmlinux-debug-2.6.22-14-generic native_safe_halt
4135     31.8346  mysql                    add_line(String&, char*, char*, bool*)

so, nearly 100% execution is spent in add_line

This has been reproduced on Linux and MacOSX  systems.

The regression is caused by this changeset. Unapplying it makes the problem go away:

ChangeSet@1.2521.66.1, 2007-11-02 13:40:34+03:00, kaa@polly.(none) +4 -0
  Fix for:
    bug #26215: mysql command line client should not strip comments
                from SQL statements
  and
    bug #11230: Keeping comments when storing stored procedures

Many thanks to Domas again for helping nail this down. :-)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40304

ChangeSet@1.2584, 2007-12-20 21:37:21+01:00, df@pippilotta.erinye.com +1 -0
  BUG#33057 mysql command line client slows down and uses 100% CPU when restoring dump

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

The fix for Bug#11230 and Bug#26215 introduced a significant
input-parsing slowdown for the mysql client. This has been corrected.

Bug #33757 was marked as a duplicate of this one.

Bug #34578 was marked as a duplicate of this one.

For customers having this issue: you can use the command line client binary from 5.0.56.