MySQL index creation is slow (on EC2)

Question

I have a fairly simple table

requestparams (
  requestid varchar(64) NOT NULL,
  requestString text,
) ENGINE=MyISAM;

After populating the table with "LOAD DATA", I am changing the schema and making "requestid" the primary key.

There are 11 million rows in this table and the data size is less than 2GB (size of the MYD file.) The index file size is around 600M at the end of the process.

Creating index takes less than 20 minutes on my laptop. But, when I ran the process on Amazon's EC2 (medium instance), the process took more than 12 hours. During entire process, the disk was superbusy with IO wait (as see by top) between 40-100%. The CPU was mostly idle. I don't think, the disk on EC2 are that slow.

On MySQL mailing list, some had suggested to change server variables myisam_sort_buffer_size & myisam_max_sort_file_size. I set them to 512MB & 4GB respectively. But index creation was equally slow. In fact, the memory usage by MySQL rarely went beyond 40M.

How do I fix this?

Solution: Increasing "key_buffer_size" helped. I set this value to 1GB and the process completed in 4 minutes. Make sure you verify the new settings with mysqladmin variables command.

Answer 1

The suggestions you received are correct. I suspect the changes you made didn't actually take effect. Try making the changes in my.cnf and restart the server.