my.cnf: Improve speed of adding new columns and indexes in large MySQL tables

Question

I have a large table (> 10 million rows) which I am adding new columns and indexes to.

This is taking progressively longer and longer to complete.

I have a powerful server with 16GB of memory available.

What are the best settings in my.cnf to increase in order to speed this process up?

Answer 1

read: http://peter-zaitsev.livejournal.com/11772.html

Answer 2

use maatkit's tuning-primer. It'll look at your stats and give you recommendations on what settings you need to change.

Bottom line though is that you shouldn't be changing your schema or indexes. As you noticed, that method won't last as you get bigger and bigger. So maybe look at your design again.

You can also throw more hardware it at. Sounds like you need a disk array with a lot of disks.

Answer 3

If using Innodb engine, set innodb_buffer_pool_size = 12000M in your my.cnf. This allocates 12G to MySQL for caching your data. If your machine is dedicated to MySQL, you may be able to go as high as 14G.