How can I avoid "repair using keycache" in MySQL?

Question

This is really crazy. I have followed every setup instruction I found in interwebs, and I can't get past this.

Basically, I have a table with about 8 million rows. I need to backup this table as follows:

create table mytable_backup like mytable 

And it takes several hours on my production server, which is an Amazon EC2 instance running through EngineYard. It only takes a few minutes on my MacBook Pro. This is another of the unpleasant things MySQL does in the background, and you can't figure out how it decides to do something so stupidly slow.

By the way, there are over 330G available in the tmp directory, so this is not a problem.

But here is what "free -m" gives:

 deploy@domU-12-31-39-02-35-31 ~ $ free -m total used free shared buffers cached Mem: 1740 1728 11 0 14 1354 -/+ buffers/cache: 359 1380 Swap: 895 2 893 

I don’t know how to read this, but the “11” under the free column doesn’t look very good.

I run:

 Server version: 5.0.51-log Gentoo Linux mysql-community-5.0.51 

Here is my configuration file:

 # /etc/mysql/my.cnf: The global mysql configuration file. # $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $ # The following options will be passed to all MySQL clients [client] port = 3306 [mysql] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqladmin] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlcheck] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqldump] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlimport] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlshow] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [myisamchk] character-sets-dir=/usr/share/mysql/charsets [myisampack] character-sets-dir=/usr/share/mysql/charsets [mysqld_safe] err-log = /db/mysql/log/mysql.err # To allow table cache to be raised open-file-limit = 4096 [mysqld] max_connections = 300 innodb_file_per_table = 1 log-slow-queries = /db/mysql/log/slow_query.log long_query_time = 2000000 ft_min_word_len = 3 max_heap_table_size = 64M tmp_table_size = 64M server-id = 1 log-bin = /db/mysql/master-bin log-bin-index = /db/mysql/master-bin.index # END master/slave configuration character-set-server = utf8 default-character-set = utf8 user = mysql port = 3306 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /db/mysql/log/mysqld.err basedir = /usr datadir = /db/mysql key_buffer = 32M max_allowed_packet = 32M table_cache = 1024 thread_cache = 512 sort_buffer_size = 100M net_buffer_length = 64K read_buffer_size = 1M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 100M myisam_max_sort_file_size = 2G myisam_repair_threads = 1 language = /usr/share/mysql/english # security: # using "localhost" in connects uses sockets by default # skip-networking # bind-address = 127.0.0.1 # point the following paths to different dedicated disks tmpdir = /mnt/mysql/tmp # log-update = /path-to-dedicated-directory/hostname # you need the debug USE flag enabled to use the following directives, # if needed, uncomment them, start the server and issue # #tail -f /tmp/mysqld.sql /tmp/mysqld.trace # this will show you *exactly* what happening in your server ;) #log = /tmp/mysqld.sql #gdb #debug = d:t:i:o,/tmp/mysqld.trace #one-thread # the rest of the innodb config follows: # don't eat too much memory, we're trying to be safe on 64Mb boxes # you might want to bump this up a bit on boxes with more RAM innodb_buffer_pool_size = 1275M # this is the default, increase it if you have lots of tables innodb_additional_mem_pool_size = 16M # # i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-( # and upstream wants things to be under /var/lib/mysql/, so that the route # we have to take for the moment #innodb_data_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ #innodb_log_group_home_dir = /var/lib/mysql/ # you may wish to change this size to be more suitable for your system # the max is there to avoid run-away growth on your machine innodb_data_file_path = ibdata1:20M:autoextend # we keep this at around 25% of of innodb_buffer_pool_size # sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size) innodb_log_file_size = 96M # this is the default, increase it if you have very large transactions going on innodb_log_buffer_size = 8M # this is the default and won't hurt you # you shouldn't need to tweak it innodb_log_files_in_group = 2 # see the innodb config docs, the other options are not always safe # This is not good for performance when used with bin_sync. Disabling. innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 50 query_cache_size = 16M query_cache_type = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] # uncomment the next directive if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M ft_min_word_len = 3 [mysqlhotcopy] interactive-timeout 

Answer 1

You can try

 create table backup_table as (select * from production table) engine=myisam 

This should create a table with only data and with none of the keys. Then you can add the keys by doing

 alter table backup_table add index(column_name) 

I have done this successfully several times, and it is usually 2 times faster than inserting with keys in place.

Answer 2

For what it's worth, 11 megabytes for free is great. That 11 megabytes of memory is not used for anything, and wasted in terms of hardware. The real number is "1380" used in caches, plus 11 megabytes is not used. If necessary, the cache can be deflated.

About 1,400 MB of RAM is available on your system.

Answer 3

You need to look at myisam_max_sort_file_size and myisam_sort_buffer_size settings

If the sum of all keys is less than myisam_max_sort_file_size, the sorting will in the worst case fall into the MyISAM table, which is good.

Otherwise, it will return to the key cache. This means loading the necessary .MYI index pages into keycache and moving those index pages in memory. Nobody wants this !!!!

In your current setting, 2G is specified for this variable.

Look at the built keys. Add them. If the sum of all key sizes exceeds 2G, keycache is complete! You will need to increase this value. You can increase this value for a session to 4G with

 SET myisam_max_sort_file_size = 1024 * 1024 * 1024 * 4; SET myisam_sort_buffer_size = 1024 * 1024 * 1024 * 4; 

or you can set the number directly as follows:

 SET myisam_max_sort_file_size = 4294967296; SET myisam_sort_buffer_size = 4294967296; 

before executing ENABLE KEYS;

If you're just interested in backing up data, why index it? Try using the ARCHIVE storage engine. It has no indexing. Follow these steps:

 CREATE TABLE mytable_backup LIKE mytable; ALTER TABLE mytable_backup ENGINE=ARCHIVE; INSERT INTO mytable_backup SELECT * FROM mytable; 

I also noticed that you are using Amazon EC2. I have never been to EC2 before. Run this command:

  SHOW ENGINES; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 

If the ARCHIVE storage engine is displayed in the list and the support is yes, you have the option of backing up to the ARCHIVE table. If not, you should configure the myisam_max_sort_file_size and myisam_sort_buffer_size parameters.