MySQL Bugs: #50564: Repair Table taking too long

Bug #50564	Repair Table taking too long
Submitted:	23 Jan 2010 5:20	Modified:	25 Jan 2010 9:42
Reporter:	VIPUL JAIN	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.22-Debian_0ubuntu6.06.3-log	OS:	Linux (Debian_0ubuntu6.06)
Assigned to:		CPU Architecture:	Any
Tags:	myisam, repair table

Description:
I have a table 3.6GB with 4,14,000 rows, Index in three fields,

Accidentally ran repair table command on the table, it is taking too long to repair (waited for 13 hours), and if I restart my system it shows that table as crashed while repair,

How to recover the table?

Vipul

How to repeat:
repair table 'table name'

Repair Table taking too long

you can't kill a repair without corrupting the indexes.  try this in my.cnf
myisam_max_sort_file_size=900G to make it faster.

what does processlist say, 'repair by sort' or 'repair with keycache' ?
BTW, 5.0.22 is too old to use, and you should be using 5.0.89 or 5.1.42 these days.

It says,

repair by sort

Please, send your entire my.cnf file content.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock 
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
#
# For compatibility to other Debian packages that still use
# libmysqlclient10 and libmysqlclient12.
old_passwords   = 1
# 
# Instead of skip-networking the default is now to listen only on         
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#       
# * Fine Tuning       
#
key_buffer              = 16M
max_allowed_packet      = 16M               
thread_stack            = 128K
#
# * Query Cache Configuration
#            
query_cache_limit       = 1048576            
query_cache_size        = 16777216
query_cache_type        = 1
#       
# * Logging and Replication
#                 
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql.log        
#log            = /var/log/mysql/mysql.log    
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log-slow-queries       = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id              = 1
log-bin                 = /var/log/mysql/mysql-bin.log
expire-logs-days        = 20
max_binlog_size         = 104857600
#binlog-do-db           = include_database_name                           
#binlog-ignore-db       = include_database_name             
#
# * BerkeleyDB
#                     
# According to an MySQL employee the use of BerkeleyDB is now discouraged
# and support for it will probably cease in the next versions.
skip-bdb
#
# * InnoDB
# 
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features      
#       
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# 
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# ssl-ca=/etc/mysql/cacert.pem                
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names                
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition           

[isamchk]
key_buffer              = 16M

# 
# * NDB Cluster
#       
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#         
# The following configuration is read by the ndbd storage daemons,
# not from the ndb_mgmd management daemon.
# 
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1

Please, send the results of:

free
uname -a

Linux commands. I think you just had not configured your server properly for the load...

total       used       free     shared    buffers     cached
Mem:        524480     518524       5956          0      18652     410312
-/+ buffers/cache:      89560     434920
Swap:       430072         56     430016

uname -a
Linux ns.pediaindia.com 2.6.18-xenU-xp070501 #3 SMP Tue May 1 14:03:41 IST 2007 x86_64 GNU/Linux

We finally discovered that the free disc space was too less. we got that increased and repair was done in less than 6 mnutes.

I would be greatful if someone can help us in optimizing my.cnf file for handling the load properly.

Thanks

So, the problem was NOT a result of any bug in MySQL code.