MYSQL安装后的基本设置

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.