MySQL辦事器默許裝置以後調理機能的辦法。本站提示廣大學習愛好者：（MySQL辦事器默許裝置以後調理機能的辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是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.
在面試MySQL DBA或許那些盤算做MySQL機能優化的人時，我最愛好成績是：MySQL辦事器依照默許設置裝置完以後，應當做哪些方面的調理呢？
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.
雖然你可以調理許多MySQL辦事器上的變量，然則在年夜多半平日的任務負載下，只要多數幾個才真正主要。假如你把這些變量設置准確了，那末修正其他變量最多只能對體系機能改良有必定晉升。
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表，可以把它設置為可用內存的 30-40%。公道的值取決於索引年夜小、數據量和負載 -- 記住，MyISAM表會應用操作體系的緩存來緩存數據，是以須要留出部門內存給它們，許多情形下數據比索引年夜多了。雖然如斯，須要老是檢討能否一切的 key_buffer 都被應用了 -- .MYI 文件只要 1GB，而 key_buffer 卻設置為 4GB 的情形長短常少的。這麼做太糟蹋了。假如你很少應用MyISAM表，那末也保存低於 16-32MB 的 key_buffer_size 以順應賜與磁盤的暫時表索引所需。
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_buffer_pool_size - 這對Innodb表來講異常主要。Innodb比擬MyISAM表對緩沖更加敏感。MyISAM可以在默許的 key_buffer_size 設置下運轉的可以，但是Innodb在默許的 innodb_buffer_pool_size 設置下卻跟蝸牛似的。因為Innodb把數據和索引都緩存起來，無需留給操作體系太多的內存，是以假如只須要用Innodb的話則可以設置它高達 70-80% 的可用內存。一些運用於 key_buffer 的規矩有 -- 假如你的數據量不年夜，而且不會暴增，那末無需把 innodb_buffer_pool_size 設置的太年夜了。
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_additional_pool_size - 這個選項對機能影響其實不太多，至多在有差不多足夠內存可分派的操作體系上是如許。不外假如你依然想設置為 20MB(或許更年夜)，是以就須要看一下Innodb其他須要分派的內存有若干。
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.
innodb_log_file_size 在高寫入負載特別是年夜數據集的情形下很主要。這個值越年夜則機能絕對越高，然則要留意到能夠會增長恢復時光。我常常設置為 64-512MB，跟據辦事器年夜小而異。
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_log_buffer_size 默許的設置在中等強度寫入負載和較短事務的情形下，辦事器機能還可以。假如存在更新操作峰值或許負載較年夜，就應當斟酌加年夜它的值了。假如它的值設置太高了，能夠會糟蹋內存 -- 它每秒都邑刷新一次，是以無需設置跨越1秒所需的內存空間。平日 8-16MB 就足夠了。越小的體系它的值越小。
innodb_flush_logs_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
innodb_flush_logs_at_trx_commit 能否為Innodb比MyISAM慢1000倍而頭年夜？看來或許你忘了修正這個參數了。默許值是 1，這意味著每次提交的更新事務（或許每一個事務以外的語句）都邑刷新到磁盤中，而這相當消耗資本，特別是沒有電池備用緩存時。許多運用法式，特別是從 MyISAM改變過去的那些，把它的值設置為 2 便可以了，也就是不把日記刷新到磁盤上，而只刷新到操作體系的緩存上。日記依然會每秒刷新到磁盤中去，是以平日不會喪失每秒1-2次更新的消費。假如設置為 0 就快許多了，不外也絕對不平安了 -- MySQL辦事器瓦解時就會喪失一些事務。設置為 2 批示喪失刷新到操作體系緩存的那部門事務。
table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I've seen values over 100.000 used.
table_cache -- 翻開一個表的開支能夠很年夜。例如MyISAM把MYI文件頭標記該表正在應用中。你確定不願望這類操作太頻仍，所以平日要加年夜緩存數目，使得足以最年夜限制地緩存翻開的表。它須要用到操作體系的資本和內存，對以後的硬件設置裝備擺設來講固然不是甚麼成績了。假如你有200多個表的話，那末設置為 1024 或許比擬適合（每一個線程都須要翻開表），假如銜接數比擬年夜那末就加年夜它的值。我已經見過設置為 100,000 的情形。
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
thread_cache -- 線程的創立和燒毀的開支能夠很年夜，由於每一個線程的銜接/斷開都須要。我平日至多設置為 16。假如運用法式中有年夜量的騰躍並發銜接而且 Threads_Created 的值也比擬年夜，那末我就會加年夜它的值。它的目標是在平日的操作中無需創立新線程。
query_cache If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
query_cache -- 假如你的運用法式有年夜量讀，並且沒有運用法式級其余緩存，那末這很有效。不要把它設置太年夜了，由於想要保護它也須要很多開支，這會招致MySQL變慢。平日設置為 32-512Mb。設置完以後最好是跟蹤一段時光，檢查能否運轉優越。在必定的負載壓力下，假如緩存射中率太低了，就啟用它。
Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.
留意：就像你看到的下面這些全局表量，它們都是根據硬件設置裝備擺設和分歧的存儲引擎而分歧，然則會話變量平日是依據分歧的負載來設定的。假如你只要一些簡略的查詢，那末就無需增長 sort_buffer_size 的值了，雖然你有 64GB 的內存。弄欠好或許會下降機能。
我平日在剖析體系負載後才來設置會話變量。
P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.
P.S，MySQL的刊行版曾經包括了各類 my.cnf 典范文件了，可以作為設置裝備擺設模板應用。平日這比你應用默許設置好的多了。
