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.

在面試MySQL DBA或者那些打算做MySQL性能優(yōu)化的人時(shí)，我最喜歡問題是：MySQL服務(wù)器按照默認(rèn)設(shè)置安裝完之后，應(yīng)該做哪些方面的調(diào)節(jié)呢？

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.

令我很驚訝的是，有多少人對這個(gè)問題無法給出合理的答案，又有多少服務(wù)器都運(yùn)行在默認(rèn)的設(shè)置下。

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.

盡管你可以調(diào)節(jié)很多MySQL服務(wù)器上的變量，但是在大多數(shù)通常的工作負(fù)載下，只有少數(shù)幾個(gè)才真正重要。如果你把這些變量設(shè)置正確了，那么修改其他變量最多只能對系統(tǒng)性能改善有一定提升。

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.

key_buffer_size - 這對MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設(shè)置為可用內(nèi)存的 30-40%。合理的值取決于索引大小、數(shù)據(jù)量以及負(fù)載 -- 記住，MyISAM表會使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，因此需要留出部分內(nèi)存給它們，很多情況下數(shù)據(jù)比索引大多了。盡管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 卻設(shè)置為 4GB 的情況是非常少的。這么做太浪費(fèi)了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以適應(yīng)給予磁盤的臨時(shí)表索引所需。

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.