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

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 complet开发者_高级运维e.

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?

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.

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.

On such big tables you should use partitiong, SSD based RAID's. And use

innodb_flush_method=O_DIRECT

You should to increase performance of disk subsystem. Than more you have iops than better DB speed.