Have Geek Brain

My.cnf: improves the speed of adding new columns and indexes on large MySQL tables

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

This is done gradually for longer and longer.

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

What are the best settings in my.cnf to speed things up?

+2


a source to share


3 answers


use maatkit tuning-primer. He will review your statistics and give you recommendations on which parameters you need to change.

The bottom line is that you shouldn't change your schema or indices. As you noticed, this method will not continue as you get bigger and bigger. So maybe take a look at your design again.



You can add more equipment as well. Sounds like you need a disk array with a lot of disks.

0


a source


If you are using the Innodb engine, set innodb_buffer_pool_size = 12000M in my.cnf. This allocates 12G for MySQL to cache your data. If your machine is dedicated to MySQL, you can go up to 14G.



0


a source


On such large tables, you should use partitiong , a RAID based SSD. And use

innodb_flush_method=O_DIRECT

You should improve the performance of the disk subsystem. The more speed you have, the better the speed of the database.

0


a source






More articles:


All Articles