Bug #53272 REPAIR of large table causes mysql client to grow rapidly in memory
Submitted: 28 Apr 2010 22:12
Reporter: Adam Dixon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.1.40sp1 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[28 Apr 2010 22:12] Adam Dixon
Description:
Running Repair on table REPAIR TABLE ... USE_FRM; would cause mysql client binary to grow rapidly in memory usage.

Prior to starting the repair;

Tue Apr 27 19:09:06 EDT 2010
Memory:
total used free shared buffers cached
Mem: 7976 7938 38 0 44 7190
-/+ buffers/cache: 703 7273
Swap: 1027 0 1027
Total: 9004 7938 1065

PS AUX:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 27599 2.7 5.4 3225500 446032 pts/2 Sl 18:47 0:37 /usr/sbin/mysqld .....

Tue Apr 27 19:23:23 EDT 2010
Memory:
total used free shared buffers cached
Mem: 7976 7943 32 0 6 1339
-/+ buffers/cache: 6598 1378
Swap: 1027 1 1026
Total: 9004 7945 1058

PS AUX:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 8803 3.2 66.4 5504864 5426028 pts/3 S+ 19:14 0:17 mysql
mysql 27599 19.5 12.3 3914316 1004932 pts/2 Sl 18:47 7:07 /usr/sbin/mysqld ....

MySQL:
Id User Host db Command Time State Info
13039 root localhost source Query 534 Repair by sorting repair table nntp_dbrar_parts2 USE_FRM

Above you can see mysql client binary using 66% of system memory.

Analysis shows that the client recieves many many warnings (9 million)
"simply done mysql_store_result() with millions of warnings"

How to repeat:
Have not been able to repeat this locally, but it repeats every time with customer table.

Suggested fix:
Dont consume so much memory for this or handle it better.
[29 Apr 2010 1:17] Adam Dixon
Trying with 
mysql -e 'repair table...'; 
or 
mysql --quick 

Did not have an affect.

It also consumes all memory when using myisamchk.