MySQL Hanging Completely when `ALTER TABLE... ENABLE KEYS`

Question

I know very little about database administration but I have to deal with some very large tables on my site.

This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it does is MySQL. I use half MyISAM and half InnoDB tables.

I have a couple of tables in MyISAM with billions of rows. Every day I have a script which disables keys, add a few million more rows, then enables keys again. The ALTER TABLE... ENABLE KEYS causes the server to basically stop for a couple of hours. No web site which uses MySQL will load at all, even though they are not accessing the tables being altered at all.

Please also advise me on how to setup the my.cnf file to fix this issue and optimize for rebuilding these indexes as fast as possible. Someone told me to increase the key_buffer_size, but I'm unsure about whether this is good as everyone seems to have a different opinion..? Currently it looks like this:

[client]
port        = 3306
socket      = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
max_allowed_packet = 512M
table_open_cache = 1024
sort_buffer_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 24G
thread_cache_size = 12
query_cache_size = 256M
thread_concurrency = 16
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
innodb_file_per_table = 1
table_cache = 1024
key_buffer = 256M
key_buffer_size = 12G
myisam_repair_threads = 4
big-tables
bind-address = 127.0.0.1
max_connections = 400
tmp_table_size = 4G
max_heap_table_size = 4G
log_bin = /backup/mysql-bin-logs/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 12G
local-infile=1
net_read_timeout = 1800
net_write_timeout = 1800


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
local-infile=1

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
key_buffer = 256M

[mysqlhotcopy]
interactive-timeout

MySQL Version

innodb_version 5.5.30
protocol_version 10
version 5.5.30-log
version_comment MySQL Community Server (GPL) by Remi
version_compile_machine x86_64
version_compile_os Linux

UPDATE

I've started a bounty. I changed some of the my.conf settings (also updated in this post). Then when I tried to rebuild the indexes on the large table it started out with Repair with 8 threads (even though the number of repair threads is set to 4) and then when I checked it a few hours later the same command was on Repair with keycache, which is now where it's sitting. So somehow it degraded down from the sort to keycache method (I have no idea why!)

Please help me optimize this! It's supposed to run every day but currently takes several days just to ALTER TABLE... ENABLE KEYS.

Here are some other variables I was asked for, which I do not understand but might help you help me:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| key_cache_block_size | 1024  |
+----------------------+-------+
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| key_buffer_size | 12884901888 |
+-----------------+-------------+
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 0     |
+-------------------+-------+

UPDATEDATE 2 (5/21)

Thinking it would solve me problem, I have changed my script to entirely truncate the table, reinsert all the rows, and add the indexes one at a time. Instead of disabling keys, adding new rows and then enabling keys.

Unfortunately it did not help because the index creation still goes to repair with keycache.

Here is the result of SHOW CREATE TABLE research_storage1:

CREATE TABLE `research_storage1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `word1` mediumint(8) unsigned NOT NULL,
  `word2` mediumint(8) unsigned NOT NULL,
  `origyear` smallint(5) unsigned NOT NULL,
  `cat` tinyint(3) unsigned NOT NULL,
  `pibn` int(10) unsigned NOT NULL,
  `page` smallint(5) unsigned NOT NULL,
  `pos` smallint(5) unsigned NOT NULL,
  `num` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pibnpage` (`pibn`,`page`,`word2`,`word1`),
  KEY `word21pibn` (`word2`,`word1`,`pibn`,`num`),
  KEY `word12num` (`word1`,`word2`,`num`),
  KEY `cat1` (`cat`,`word1`),
  KEY `year1` (`origyear`,`word1`),
  KEY `catyear1` (`cat`,`origyear`,`word1`),
  KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin DATA DIRECTORY='/storage/researchdb/' INDEX DIRECTORY='/storage/researchdb/';

I also ran this command: SELECT data_length/power(1024,3) dat,index_length/power(1024,3) ndx FROM information_schema.tables WHERE table_schema='dbname' AND table_name='tablename'; But the problem with this is that I currently have 2 tables for this table, 1 which is truncated, and 1 which has all the data but no indexes (the former will be replaced with the latter once indexes are complete)... the reason being because I am unable to build the damn indexes (hence the problem). Here is the info for the truncated table, and then the table with data but no indexes:

+------+------------------------+
| dat  | ndx                    |
+------+------------------------+
|    0 | 0.00000095367431640625 |
+------+------------------------+
+-------------------+--------------------+
| dat               | ndx                |
+-------------------+--------------------+
| 51.61232269741595 | 27.559160232543945 |
+-------------------+--------------------+

Please also note that the table will be 10x larger than this before all the data is received.

Can you shared the exact version of mysql that you're using (show variables like '%version%';)? Some rather old versions have major bugs with ENABLE KEYS. There are also some tidbits on index creation here that might help. — Nathan Jolly, May 09 '13 at 09:44
The next time you have ALTER TABLE ... ENABLE KEYS running please run SHOW PROCESSLIST; and post the output of that Process only. — RolandoMySQLDBA, May 09 '13 at 13:39
Can you post the output of the following statements (SHOW VARIABLES LIKE 'key_cache_block_size';, SHOW VARIABLES LIKE 'key_buffer_size';, SHOW GLOBAL STATUS LIKE '%Key_blocks_unused%';)? — Cristian Porta, May 10 '13 at 06:33
@Alasdair according to this formula 100 - ((key_blocks_unused * key_cache_block_size ) * 100 / key_buffer_size ) your cache hit ratio is 100% so it seems that your key_buffer_size is too small. Can you post the size of your table(MYI and MYD) and the index_length (SELECT INDEX_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA = <yourschema> AND TABLE_NAME = <yourtable>;)? Than we can try using Multiple Key Cahce... — Cristian Porta, May 16 '13 at 06:23
How do you load millions of rows daily ? Do you do INSERT INTO tbl VALUES (...),(...),(...)...(...),(...); ? Do you use LOAD DATA INFILE ...; ? — RolandoMySQLDBA, May 17 '13 at 18:53
Please run this query : SELECT data_length/power(1024,3) dat,index_length/power(1024,3) ndx FROM information_schema.tables WHERE table_schema='yourdb' AND table_name='yourtable'; and post what dat and ndx are. — RolandoMySQLDBA, May 17 '13 at 18:56
Please run this query : SELECT data_length/power(1024,3) dat,index_length/power(1024,3) ndx FROM information_schema.tables WHERE table_schema='yourdb' AND table_name='yourtable'; and post what dat and ndx are. — RolandoMySQLDBA, May 20 '13 at 03:25

RolandoMySQLDBA · Accepted Answer · 2013-05-22T16:15:03.760

Right now, you are in a very fortunate position. I noticed you have big-tables defined. This is preventing you from experiencing "Table is Full" errors. Why is this good?

Whenever you get "Repair With Keycache" as a status, you have no free space to do file sorting. Making sort_buffer_size bigger isn't necessarily the answer since temp tables become disk files immediately.

You have two options

OPTION #1 : Increase Diskspace for datadir

The data volume where /var/lib/mysql (or whatever datadir is) resides may not have enough room to house a materialized temp table on disk. I would suggest increasing the disk volume's size to, at least, twice its size.

DRAWBACK : A one-time maintenance to move the database to the bigger disk.

OPTION #2 : Separate Disk for Temp Tables

Perhaps having a separate disk volume who sole purpose in life is to house temp tables should be set up. Try this

Step 01 : Install a disk with the same size as the home of datadir
Step 02 : mkdir /tmptables
Step 03 : Mount the new disk volume to the folder /tmptables
Step 04 : chown mysql:mysql /tmptables
Step 05 : Add this to my.cnf
- tmpdir=/tmptables
Step 06 : service mysql restart

Once you make the disk and add tmpdir, you should have more elbow room.

DRAWBACK : Transferring of the temp tables contents from /tmptables back to the home of datadir for certain SQL commands (such as DDL).

UPDATE 2013-05-21 00:10 EDT

You simply don't have enough memory. All the cores in the world cannot help MyISAM.

SUGGESTION #1 : Shorten your keys

I can tell from the keys that you are trying to retrieve all data from the indexes and avoid touching the table. All well and good IF IT WEREN'T FOR THE BILLIONS OF ROWS.

Here is something worth considering: Shorten the keys for each index

CREATE TABLE `research_storage1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `word1` mediumint(8) unsigned NOT NULL,
  `word2` mediumint(8) unsigned NOT NULL,
  `origyear` smallint(5) unsigned NOT NULL,
  `cat` tinyint(3) unsigned NOT NULL,
  `pibn` int(10) unsigned NOT NULL,
  `page` smallint(5) unsigned NOT NULL,
  `pos` smallint(5) unsigned NOT NULL,
  `num` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pibnpage` (`pibn`,`page`),
  KEY `word21` (`word2`,`word1`),
  KEY `cat1` (`cat`,`word1`),
  KEY `year1` (`origyear`,`word1`),
  KEY `catyear1` (`cat`,`origyear`),
  KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
DATA DIRECTORY='/storage/researchdb/'
INDEX DIRECTORY='/storage/researchdb/';

SUGGESTION #2 : Stop using `ALTER TABLE...ENABLE KEYS;`

You should try to get the MyISAM imported out into a new table without using ENABLE KEYS.

CREATE TABLE `research_storagenew` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `word1` mediumint(8) unsigned NOT NULL,
  `word2` mediumint(8) unsigned NOT NULL,
  `origyear` smallint(5) unsigned NOT NULL,
  `cat` tinyint(3) unsigned NOT NULL,
  `pibn` int(10) unsigned NOT NULL,
  `page` smallint(5) unsigned NOT NULL,
  `pos` smallint(5) unsigned NOT NULL,
  `num` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pibnpage` (`pibn`,`page`,`word2`,`word1`),
  KEY `word21pibn` (`word2`,`word1`,`pibn`,`num`),
  KEY `word12num` (`word1`,`word2`,`num`),
  KEY `cat1` (`cat`,`word1`),
  KEY `year1` (`origyear`,`word1`),
  KEY `catyear1` (`cat`,`origyear`,`word1`),
  KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
DATA DIRECTORY='/storage/researchdb/'
INDEX DIRECTORY='/storage/researchdb/';
INSERT INTO `research_storagenew` SELECT * FROM `research_storage1`;
DROP TABLE `research_storage1`;
ALTER TABLE `research_storagenew` RENAME `research_storage1`;

SUMMARY

Look at the table definition again. There is 18 bytes for an index entry on just the pibnpage index. That's 18G per billion rows. Same goes with word21pibn. You just don't have enough room. It is imperative to try one of my newest suggestion to bypass the need to sort all these keys.

UPDATE 2013-05-22 12:15 EDT

YoU asked

About shortening the keys though: what kind of performance hit will this result in? Are we talking twice as long, 10x as long, 1000x as long?

I cannot say for sure what running time impact there will. However, I can say this: There may be some additional disk I/O because the indexes will no longer contain the needed column info. Queries will not have to turn to the .MYD file to retrieve additional column information. Please keep in mind that MyISAM is suitable for heavy-read queries.

The only tuning I can further recommend if there is supposed to be INSERTs and DELETEs in the middle of the day during heavy-read periods would be to enable concurrent INSERTs.

[mysqld]
concurrent_insert=1

This will allow INSERTs into MyISAM without cross checking for free blocks within the table. This may make the table grow a little faster.

As far as maintenance goes, you must use SUGGESTION #2 as not rely so much on ALTER TABLE ... ENABLE KEYS; on such bloated table. Perhaps you should think of moving /storage/researchdb/ to SSD.

Comments are not for extended discussion; this conversation has been moved to chat. — Paul White, Aug 29 '17 at 09:32

MySQL Hanging Completely when `ALTER TABLE... ENABLE KEYS`

1 Answers1

OPTION #1 : Increase Diskspace for datadir

OPTION #2 : Separate Disk for Temp Tables

UPDATE 2013-05-21 00:10 EDT

SUGGESTION #1 : Shorten your keys

SUGGESTION #2 : Stop using `ALTER TABLE...ENABLE KEYS;`

SUMMARY

UPDATE 2013-05-22 12:15 EDT

Linked

MySQL Hanging Completely when `ALTER TABLE... ENABLE KEYS`

1 Answers1

OPTION #1 : Increase Diskspace for datadir

OPTION #2 : Separate Disk for Temp Tables

UPDATE 2013-05-21 00:10 EDT

SUGGESTION #1 : Shorten your keys

SUGGESTION #2 : Stop using ALTER TABLE...ENABLE KEYS;

SUMMARY

UPDATE 2013-05-22 12:15 EDT

Linked

SUGGESTION #2 : Stop using `ALTER TABLE...ENABLE KEYS;`