MySQL Bugs: #34681: 5.1.23rc myisamchk problem with myisam_sort_buffer

Bug #34681	5.1.23rc myisamchk problem with myisam_sort_buffer_size variable
Submitted:	20 Feb 2008 0:12	Modified:	4 May 2008 9:10
Reporter:	Serge Yakubovich	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1.23rc	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi,

just doing:

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk -rq /var/lib/mysql5123/SEARCH/CS.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 50000000
- Fixing index 1
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk -r /var/lib/mysql5123/SEARCH/CS.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 0
- Fixing index 1
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk --myisam_sort_buffer_size=2G -rq /var/lib/mysql5123/SEARCH/CS.MYI
/usr/local/mysql5123/bin/myisamchk: unknown variable 'myisam_sort_buffer_size=2G'

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk --myisam_sort_buffer_size=2000000000 -rq /var/lib/mysql5123/SEARCH/CS.MYI
/usr/local/mysql5123/bin/myisamchk: unknown variable 'myisam_sort_buffer_size=2000000000'
[root@bandura LOAD]#                                                                                        

Inserting myisam_sort_buffer_size in my.cnf:

[myisamchk]
myisam_sort_buffer_size                 = 8G
sort_buffer_size                        = 8G

produces the same error "unknown variable ..."

OS:
/etc/redhat-release: CentOS release 5 (Final)

System: Linux bandura 2.6.18-8.el5 #1 SMP Thu Mar 15 19:46:53 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

More info - as in bug report:  http://bugs.mysql.com/bug.php?id=32349 ,but now for 5.1.23rc built from sources on the same OS and hardware

Previous 5.1.22rc myisamchk worked well on this server with the same my.cnf
Also, all ok on FreeBSD 6.2 box (5.1.23rc)

my.cnf: (for 32G RAM)
--------------------------------
# cat /etc/my.cnf
[client]
#password                               = your_password
#port                                   = 3307
socket                                  = /var/lib/mysql5123/mysql5123.sock

[mysqld]
#bind-address                           = 127.0.0.1
datadir                                 = /var/lib/mysql5123
socket                                  = /var/lib/mysql5123/mysql5123.sock
old_passwords                           = 1

log-slow-queries                        = /var/log/mysql5123_slow.log
long_query_time                         = 30
big-tables
safe-show-database
#low-priority-updates
symbolic-links                          = 1
#skip-external-locking
connect_timeout                         = 10
wait_timeout                            = 1800
max_connections                         = 256
max_user_connections                    = 256
max_connect_errors                      = 2000
max_allowed_packet                      = 16M
bulk_insert_buffer_size                 = 128M  #512M
join_buffer                             = 512M  #1G
sort_buffer_size                        = 64M   #512M
read_buffer_size                        = 64M   #512M
read_rnd_buffer_size                    = 64M   #512M
table_cache                             = 1024
thread_cache_size                       = 256
tmp_table_size                          = 512M
max_heap_table_size                     = 512M
query_cache_size                        = 64M
query_cache_limit                       = 64M
thread_concurrency                      = 64
tmpdir                                  = /E1/mysql_tmpdir

key_buffer_size                         = 8G    #24G    # increase for MyISAM only !
#key_buffer_size                                = 29G   #24G    # increase for MyISAM only !
#--key_cache_division_limit             = 40    #
#--key_cache_age_threshold                      = 60    #
myisam_sort_buffer_size                 = 8G    # increase for MyISAM only !
myisam_repair_threads                   = 8
myisam_max_sort_file_size               = 8G
myisam-recover                          = BACKUP,FORCE
#myisam_block_size                      = ?
#myisam_use_mmap                                = 1     # ?

innodb_additional_mem_pool_size         = 8M    # 1M
innodb_buffer_pool_size                 = 64M   # 8M
innodb_lock_wait_timeout                = 1800  # 50
innodb_commit_concurrency               = 0     # 4     # ?
innodb_support_xa                       = 0     # 1
innodb_locks_unsafe_for_binlog          = 1     # 0
innodb_flush_log_at_trx_commit          = 1     # 1
innodb_doublewrite                      = 1     # 1
innodb_file_io_threads                  = 16    # 4
#innodb_log_group_home_dir              = /E1/mysql
innodb_log_file_size                    = 64M   # 5M
innodb_log_buffer_size                  = 4M    # 1M
innodb_autoextend_increment             = 8M    # 8M
innodb_file_per_table                   = 0

innodb_force_recovery                   = 0     # 0 !!!
innodb_fast_shutdown                    = 2     # 0

[mysql.server]
user                                    = mysql
basedir                                 = /var/lib

[mysqld_safe]
open_files_limit                        = 8192
log-error                               = /var/log/mysqld5123.log
log-slow-queries                        = /var/log/mysql5123_slow.log
pid-file                                = /var/run/mysqld/mysqld5123.pid

[myisamchk]
#myisam_sort_buffer_size                = 8G
sort_buffer_size                        = 8G
-----------------------------

How to repeat:
Dont know - seems like THIS system depending ...

Suggested fix:
myisamchk -o ... works, but much slower

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk -o /var/lib/mysql5123/SEARCH/CS.MYI
- recovering (with keycache) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 0
2707000....

And, jet another thing with myisamchk that I do not understand
This is closely connected I think with my previous bug report

http://bugs.mysql.com/bug.php?id=32349  about repair of indexes

[root@bandura LOAD]# /usr/local/mysql5123/bin/myisamchk -o /var/lib/mysql5123/SEARCH/CS.MYI
- recovering (with keycache) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 0
Data records: 50000000

[root@bandura LOAD]# ls -l /var/lib/mysql5123/SEARCH/CS.MYI
-rw-rw---- 1 mysql mysql 1146311680 Feb 20 02:26 /var/lib/mysql5123/SEARCH/CS.MYI

[root@bandura LOAD]# /usr/local/mysql5122/bin/myisamchk -o /var/lib/mysql5123/SEARCH/CS.MYI
- recovering (with keycache) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 50000000

[root@bandura LOAD]# ls -l /var/lib/mysql5123/SEARCH/CS.MYI
-rw-rw---- 1 mysql mysql 1146311680 Feb 20 02:42 /var/lib/mysql5123/SEARCH/CS.MYI

[root@bandura LOAD]# /usr/local/mysql5122/bin/myisamchk -r /var/lib/mysql5123/SEARCH/CS.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql5123/SEARCH/CS.MYI'
Data records: 50000000
- Fixing index 1

[root@bandura LOAD]# ls -l /var/lib/mysql5123/SEARCH/CS.MYI
-rw-rw---- 1 mysql mysql 1116172288 Feb 20 02:54 /var/lib/mysql5123/SEARCH/CS.MYI

So, both 5.1.22rc and 5.1.23rc myisamchk -o produses index of 1146311680 bytes
while REPAIR TABLE ...[QUICK], myisamchk -r [-q] ( at least 5.1.22rc ) - 1116172288 bytes, why ? And with speed degradation up to 100-200+ times after table repair from SQL. Some wonders ,..

What's the difference you did during installation of MySQL 5.1.23 on FreeBSD and on Linux?
Did you use gcc both times?
Did you use readline?
Are the versions of gcc and readline the same of both systems?
Did you use the same tar ball for installation?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Serge,

we still need to know what exactly is different on your systems.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

also see bug #46578