Tuning MySQL for speedy column/index creation during development

Question

Assume a MySQL MyISAM table with one gigabyte of data and one gigabyte of indexes.

Furthermore, assume that during development columns and indexes will be added and removed from/to the table quite frequently. Due to the size of the database the column/index creation is slow when using the standard non-tuned MySQL settings.

Which MySQL server parameters should be tuned in order to minimize the time it takes to add new columns/indexes?

Solution 1

I believe the key settings you should look at are key_buffer_size, myisam_max_sort_file_size and myisam_sort_buffer_size. key_buffer_size is one of the most important settings overall for MyISAM, but the other two might need some explanation.

From http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html:

myisam_max_sort_file_size

The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance.

myisam_sort_buffer_size

The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

The maximum allowable setting for myisam_sort_buffer_size is 4GB.

So basically, if you expect your indexes to be larger than myisam_max_sort_file_size, you might want to look at tweaking that up. If not, the myisam_sort_buffer_size might warrant some attention.

Keep in mind that creating indexes on a MyISAM table locks the entire table. If you're doing this constantly on large tables, you could have some serious performance issues, no matter how many settings you tweak.

Solution 2

Below are some tips.

1. Set the key_buffer_size the memory needed for storing the index. The size allocated should be greater than the size required for the column * number of row that need to indexed. In your case it should be greater than 1 GB as you have mentioned that the index size as 1 GB. For MyISAM Engine, index can be stored in memory, but not the data.

2. If the index need to created on varchar fields, limit the number the bytes used for indexing. For example if a column is of varchar(500), creating index on this will be an over kill and it will be of no use if MySQL is able to scope down to few records by using only the first few chars. For example 5 chars.

3. For adding new columns, MySQL would have to create a temporary table and copy all the data with the new column value. To avoid this, its better to create a new table with the primary key from the main table and the new columns.

Hope this helps.