MySQL CREATE INDEX slowing down

indexmyisamMySQLperformance

My analytics table has 190M rows, about 150GB ins size.
Storage engine is MyISAM with

key-buffer-size=16G
myisam_sort_buffer_size=2G
myisam-max-sort-file-size=200G.

Machine has 32GB memory.
While creating 10 simple indexes (no covering, no prefix, just column names using BTREE) I noticed:

first index – 40 minutes
second index – 50 minutes
…
fifth index – 2 hours
eleventh index – 3 1/2 hours

Each index adds between 1GB and 3GB to the .myi file.
Has anybody experienced a similar increase in run-time? Is there any way to get the performance of the first index creation for the other indexes?

Best Answer

You must be doing something odd with your DDL or you have a very, very old version of MySQL.

MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago.

Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum)
May 12, 2011 : Does MySQL still handle indexes in this way? (where I mention this again)

Without any additional details from you, I can only guess. Here it goes:

I suspect your must be just doing the following:

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);

I would like to suggest the following

SUGGESTION #1 : Build all 11 indexes at the same time

You should create all 11 indexes in a single DDL statement

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;

SUGGESTION #2 : Delay indexes building

You can disable the building of non-unique indexes so that sorting and be done.

Doing so can make a smaller BTREE for each index

ALTER TABLE mytable DISABLE KEYS;
... 
ALTER TABLE mytable ENABLE KEYS;

SUGGESTION #3 : Use a temp table

If you wish to test this manually, you can make a temp table, add all the indexes to the empty temp table, disable indexes, insert the data, enable indexes, and switch table names.

Here is a sample of what I just described to you

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

GIVE IT A TRY !!!

OBSERVATION #1

Performance implications should become quickly apparent if the index pages of monitor.result have to experience two things:

Experience 1) Swapping of the RAM making up the MyISAM Key Cache
Experience 2) Rotation in and out of the MyISAM Key Cache (sized by key_buffer_size)

Experience 1 is virtually unavoidable. As for Experience #2, it could result in needed index pages being purged out of the MyISAM Key Cache in the face of more recent queries against other MyISAM tables. Those needed index pages more be brought back by querying the corresponding table. The two experiences togther could make for a slower-than-expected query on relatively small tables.

However, you could minimize or neutralize any ill effects of swapping by assigning the index of monitor.result by creating a dedicated MyISAM Key Cache. It will be a cache that will contain only index pages from monitor.result.

How do you do that ???

First, recall that you mentioned that the index usage for monitor.result was 2.29MB. You can create that dedicated key cache with that size with a little headroom, say 2.5MB. Let us create that key cache like this:

SET GLOBAL monitor_result_private_cache.key_buffer_size = 1024 * 512 * 5;
CACHE INDEX monitor.result IN monitor_result_private_cache;
LOAD INDEX INTO CACHE monitor.result;

This will perform the following steps:

Create the Key Cache
Assign the Key Cache to the MyISAM Table using LOAD INDEX INTO CACHE
Load the Index Pages of the Assigned MyISAM Table into its Corresponding Cache

It would conveniently keep the index pages of that table from ever leaving the cache. The only table index pages would leave is if INSERTs into monitor.result increases the contents beyond 2.5MB. You must choose enough headroom to accommodate many INSERTs into monitor.result.

OBSERVATION #2

I also noticed the index you laid out for monitor.result:

PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`)

If any of your queries against monitor.result resemble something like this:

SELECT resoultion,value FROM monitor.result
WHERE server = 200 AND refid = 50 AND ... ;

You can speed queries by reordering the PRIMARY KEY

CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`) )            
ENGINE = MyISAM

or adding a UNIQUE index

CREATE  TABLE IF NOT EXISTS `monitor`.`result` (             
  `server` SMALLINT UNSIGNED NOT NULL ,             
  `ref_id` SMALLINT UNSIGNED NOT NULL ,             
  `request` TINYINT UNSIGNED NOT NULL ,             
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,             
  `resolution` TINYINT NOT NULL ,             
  `value` MEDIUMINT UNSIGNED NOT NULL ,             
  PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`),
  UNIQUE KEY uniqndx1 (`server`, `ref_id`, `request`, `recorded`)
ENGINE = MyISAM

If you add a UNIQUE index, you must double the dedicate keycache accordingly.

OBSERVATION #3

You mentioned a SATA drive. Good choice for archival, low-update historical data. Any MyISAM table on a SATA drive which has a dedicated keycache should not be bothered by the index lookup but the data retrival time from the drive would be up to you to benchmark to see if you could live with the running times.

Here is an alternative:

Create an index that has every column

CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`, `resolution`, `value`) )            
ENGINE = MyISAM

What does this do? It provides data retrieval of entire rows strictly from the index. Combining this with a dedicated keycache, you will essentially has the whole table in RAM. All queries would be fulfilled by the index and never touch the table, REGARDLESS of the table being on SAS, SATA, SSD, or even stone.

UPDATE 2012-01-26 18:18 EDT

Question 1: You may want to look into memcached. I believe that there is a version InnoDB with a memcached plugin. At least, that's what I heard.

Question 2: InnoDB is for transactional tables. If you have archive data, compressed MyISAM tables should fill the bill. In fact, you could look into the ARCHIVE storage engine.

Question 3: Storing an index on disk (MyISAM,InnoDB) is always standard and cannot be changed. You have use special commands or run special queries to preload caches.

Question 4: RAID-10 for high-writes, SSD for high-reads. Watch your disk surface temperatures !!!

Question 5: if the table is strictly for holding historical info, no need for overkill. As long as it is a table rarely read, no need for special caching considerations.

Mysql – How to speed up a loading data into MySQL from a zipped archive

Please keep in mind the behavior a mysqldump

It dumps all databases in alphabetical order
It dumps all tables in each database in alphabetical order
It loads each tables like this
- Drops the table
- Creates the Table
- Table is Loaded
  1. Disables Keys
  2. Locks the Table for Writes
  3. Multiple Exteneded INSERTs
  4. Unlock the Table
  5. Enable Keys
- When the keys of table are disabled, only PRIMARY KEYs and UNIQUE KEYs are reloaded. When keys are enabled, then all non-unique keys are built linearly.

I would recommend you add this to /etc/my.cnf setting bulk_insert_buffer_size as follows

[mysqld]
bulk_insert_buffer_size=256M
key_buffer_size=1G

because the default is 8M. You do not need to restart mysql. Just run this SQL command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

Give it a Try !!!