MySQL引數配置優化

max_connections
Variable Scope：      Global
Dynamic Variable：  Yes
Default：                   151 （mysql5.5+）
Meaning：                 允許客戶端同時連線的最大數
預設值以前是100,MySQL5.5+後151，但是預設值對大部分應用來說這都不夠。通過觀察Max_used_connections
狀態變數隨著時間的變化。可以告訴你伺服器連線是不是在某個時間點有個尖峰。如果這個值達到了max_connections，說明客戶端至少被拒絕了一次。
建議值： 500+ （設定為你認為正常情況下有300或者更多連線，則可以設定為500或更多）


thread_cache_size
Variable Scope：       Global
Dynamic Variable：   Yes
Default：                    0（mysql5.6.7-）
Meaning：                 有多少執行緒應該快取重用
其預設值在mysql5.6.8+（autosized），根據如下公式得到：8 + (max_connections / 100)，其上限值為100.設定這個變數，可以通過觀察伺服器一段時間的活動，來計算一個有理有據的值。
觀察Threads_connected狀態變數並且找到它在一般情況下的最大值和最小值。例如：若Threads_connected狀態從150變化到175，可以設定執行緒快取為75。但是不用設定的非常大，因為保持大量
等待連線的空閒執行緒並沒有什麼真正的用處。也可以觀察Threads_created狀態隨時間的變化。如果這個值很大或一直增長，這是另一個線索，告訴你可能需要調大thread_cache_size變數。
Threads_cached來檢視有多少執行緒已經在快取中了。
建議值：50-100


table_open_cache
Variable Scope:         Global
Dynamic Variable:     Yes


Default:                      400( mysql5.6.7- )
Meaning:                    所有執行緒開啟表的數量
從官方文件看出在MySQL5.6.8+開始預設值為2000，就能簡單的判斷出原來預設值是不夠的。可以通過觀察Opened_tables其值及其一段時間的變化來檢查該變數。如果看到Opened_tables的值很大
並且又不經常執行FLUSH TABLES（執行其命令強制所有的表重新關閉且開啟），那麼可能你應該增加該變數的值。
建議值： 4096（有另一種說法：這個值從max_connections的10倍開始設定）


open_files_limit
Variable Scope：       Global
Dynamic Variable：   No


Default：                    0 （mysql5.6.7-）
Meaning：                 作業系統允許mysqld服務開啟的檔案數。
其預設值在mysql5.6.8+（autosized），根據如下公式得到：
1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none


要知道每個MyISAM表開啟需要2個檔案控制程式碼；每個客戶端的連線也是一個檔案控制程式碼。有效的open_files_limit的值是基於系統啟動時所指定的值和max_connections，table_open_cache有關聯。
建議值：65535 （其值在大多作業系統是最安全的）


table_definition_cache
Variable Scope ：       Global
Dynamic Variable ：   Yes


Default ：                    400（mysql5.6.7-）
Meaning：                   快取表定義的的數量（以.frm結尾的檔案）
其預設值在mysql5.6.8+（autosized），根據如下公式得到：400 + (table_open_cache / 2)   其上限值為：2000。常可以把table_definition_cache 設定得足夠高，以快取所有的表定義。
除非有上萬張表，否則這可能是最簡單的方法。
建議值：  根據真正的資料庫中表的數量（例如：資料庫例項有1000張表，可以將其設定為1000+）


back_log
Variable Scope:           Global
Dynamic Variable:       No


Default:                        50( mysql5.6.5- )
Meaning:                     在很短時間內，可以有多少個請求連結在堆疊中等待被處理。
其預設值在mysql5.6.6+（autosized），根據如下公式得到：  50 + (max_connections / 5) 其上限值為：900。
如果每秒的連線數很多，可以將其值調大。其值和OS的TCP/IP連結有關聯，和核心引數net.ipv4.tcp_max_syn_backlog的值相關，back_log的值不能大於其值。
建議值：2048 


max_allowed_packet
Variable Scope：         Global
Dynamic Variable：     Yes


Default:                         1MB（mysql5.6.5-）
Meaning:                       這個設定防止伺服器傳送太大的包，也會控制多大的包可以被接受。
其預設值在mysql5.6.6+為4MB，其預設值可能太小了，但設定太大也可能有危險。如果設定太小，有時複製上會出現問題，通常表現為備庫不能接收主庫發過來的複製資料。使用mysql和mysqldump
客戶端程式都可以指定其值的大小。
建議值： 16MB


max_connect_errors
Variable Scope：          Global
Dynamic Variable：      Yes


Default：                        100（mysql5.6.6+）
Meaning：                     最大的連線錯誤數
如果有時網路短暫抽風了，或者應用配置出現錯誤，或另有問題，如許可權，在短暫的時間內不斷地嘗試連結，客戶端可能被列入黑名單，然後將無法連線，知道再次重新整理主機快取（FLUSH HOSTS）。
這個選項的預設設定太小了，很容易導致問題。你也許希望增加這個值，實際上，如果知道伺服器可以充分抵禦蠻力攻擊，可以把這個值設定的非常大，以有效地禁用主機黑名單。這個選項也就是
所謂的可以防止暴力破解。
建議值： 1000000 （其值為Percona 給出的建議值，但是應該確定其主機的已有抵禦蠻力攻擊的能力）


skip_name_resolve
Variable Scope：          Global
Dynamic Variable：      No


Default：                       OFF
Meaning：                     DNS查詢
這個選項禁用了另一個網路相關和鑑權認證的陷進：DNS查詢。DNS是MySQL連線過程中的一個薄弱環節。當連線伺服器時，它試圖確定連線和使用的主機的主機名，作為身份驗證憑據的一部分。
（就是說，你的憑據是使用者名稱，主機名，以及密碼，並不只是使用者名稱和密碼）但是驗證主機來源，伺服器需要執行DNS的正向和反向查詢。要是DNS有問題就悲劇了，在某些時間點這是必然的事。
為了避免這種情況，我們強烈建議設定這個選項，在驗證時關閉DNS查詢，這樣即快又安全。
建議值： ON


log_bin
Variable Scope：           Global
Dynamic Variable：       No


Meaning：                     是否開啟binlog
開啟此選項用來支援複製和時間點恢復。
建議值： 設定其值為mysql-bin來避免其預設生成的檔名（也就是與主機名無關）


sync_binlog
Variable Scope：           Global
Dynamic Variable：       Yes


Default：                         0
Meaning：                      控制MySQL怎麼重新整理二進位制日誌到磁碟
預設值為0，意味著MySQL並不重新整理，有作業系統自己決定什麼時候重新整理快取到持久化裝置。如果這個值比0大，它指定了兩次重新整理到磁碟的動作之間間隔多少次二進位制日誌寫操作
（如果autocommit被設定了，每個獨立的語句都是一次寫，否則就是一個事務一次寫）。如果沒有設定sync_binlog為1，那麼崩潰以後可能導致二進位制日誌沒有同步事務資料。這可以輕易地導致複製中斷，
並且使得及時恢復變得不可能。無論如何，可以把這個值設定為1來獲得安全的保障。這樣就會要求MySQL把二進位制日誌和事務日誌兩個檔案重新整理到不同的位置。這可能需要磁碟尋道，相對來說是個很慢的操作。
建議值：   1


expire_log_days
Variable Scope ：           Global
Dynamic Variable：        Yes


Default：                         0
Meaning：                      伺服器在指定的天數之後清理舊的二進位制日誌
如果啟用了二進位制日誌，應該開啟這個選項，可以讓伺服器在指定的天數之後清理舊的二進位制日誌。如果不啟用，最終伺服器的空間會被耗盡，導致伺服器卡住或崩潰。
建議值：  7~14


tmp_table_size和max_heap_table_size
這兩個設定控制使用Memory引擎的記憶體臨時表能使用多大的記憶體。如果隱式記憶體臨時表的大小超過這兩個設定的值，將會被轉換為MyISAM表，所以它的大小可以繼續增長。（隱式臨時表是一種並非由自己建立，
而是伺服器建立，用於儲存執行行中的查詢的中間結果的表）應該簡單地把這兩個變數設為同樣的值。但是要謹防這個變數太大了，臨時表最好呆在記憶體裡，但是如果它們被撐得很大，實際上還是讓它們使用
磁碟比較好，否則可能會讓伺服器記憶體溢位。假設查詢語句沒有建立龐大的臨時表（通常可以通過合理的索引和查詢設計來避免），那把這些變數設大一點，免得把記憶體臨時錶轉換為磁碟臨時表。這個過程可以
在SHOW PROCESSLIST中看到。使用臨時表的情況可以通過狀態變數Created_tmp_tables 和 Created_tmp_disk_tables 來監控。
建議值： 設定兩個變數為同樣的值（這個大小要根據自己的SQL查詢級別及SQL語句的優化情況）


query_cache_size
Variable Scope：          Global
Dynamic Variable ：     Yes
Default：                       0（mysql5.6.7-）
Meaning:                       快取查詢結果的記憶體大小
查詢快取使用的總記憶體空間，單位是位元組。這個值必須是1024的整數倍，否則MySQL實際分配的資料會和你指定的略有不同。
建議值：<512MB




sort_buffer_size
Variable Scope:              Global, Session
Dynamic Variable:          Yes
Meaning:                        查詢需要做排序操作時為該快取分配記憶體大小
MySQL只會在有查詢需要做排序操作時才會為該快取分配記憶體，然後，一旦需要排序，MySQL就會立刻分配該引數指定大小的全部記憶體，而不管該排序是否需要這麼大的記憶體。 通過SHOW GLOBAL STATUS檢視如
Sort_merge_passes/s 有很多，可以考慮增加sort_buffer_size的值，以來加快order by 或 group by語句的查詢速度（其排序或分組操作已不能查詢優化或索引優化）。如果查詢必須使用一個更大的排序快取
才能更好的執行，可以考慮session級別的設定其值。
建議值：1MB


join_buffer_size
Variable Scope：            Global, Session
Dynamic Variable ：       Yes


Meaning：                       設定使用連線查詢快取的大小
此選項可以提高沒有使用索引的連線查詢的效能。全域性的建議不要設定太大，可以動態設定session級別的值。
建議值：  8MB


read_rnd_buffer_size
Variable Scope ：              Global, Session
Dynamic Variable ：          Yes
Default：                            256kb
Meaning：                          讀取排序行的快取的大小
MySQL只會在有查詢需要時才會為該快取分配記憶體，並且只會分配需要的記憶體大小而不是全部指定的大小。如果增加其值，可以提高order by 的操作效能。
建議值： 16MB


key_buffer_size
Variable Scope:                    Global
Dynamic Variable :               Yes


Default:                                 8MB
Meaning:                               MyISAM 表索引快取的大小
如果MySQL server的表全部是或者說大多為MyISAM儲存引擎的，可以考慮將其設定為OS記憶體的30%。其快取僅僅快取的是索引塊，而不快取資料。
建議值：  根據其資料庫中表的儲存引擎的型別來作為參考


myisam_sort_buffer_size
Variable Scope：                   Global, Session
Dynamic Variable ：              Yes
Default：                                8MB
Meaning：                             排序MyISAM的索引快取的大小
當REPAIR TABLE或者建立索引，修改索引時操作（CREATE INDEX，ALTER TABLE）時，分配給用於其MyISAM索引排序的大小。
建議值:  8MB~256MB


innodb_buffer_pool_size
Variable Scope :                    Global
Dynamic Variable:                 No
Default：                               128MB
Meaning：                             InnoDB緩衝池的大小
如果大部分都是InnoDB表，InnoDB緩衝池或許比其他任何東西更需要記憶體。InnoDB緩衝池並不僅僅快取索引：它還會快取行的資料，自適應雜湊索引，插入緩衝，鎖，以及其他內部資料結構。
建議值：80%+ 實體記憶體


innodb_buffer_pool_instances


Variable Scope ：                  Global
Dynamic Variable ：              No
Default：                                1（mysql5.6.5-）
Meaning：                              InnoDB快取池的例項個數
從InnoDB1.0.x版本開始，允許有多個緩衝池例項。每個頁根據雜湊值平均分配到不同的緩衝池例項中。這樣做的好處是減少資料庫內部資源競爭，增加資料庫的併發處理能力。可以通過引數
innodb_buffer_pool_instances來配置。在MySQL5.6.6+其預設值為：（autosized），除了Window 32bit其值是根據innodb_buffer_pool_size的大小動態得到，其它預設值為8.
建議值：4+ （mysql5.5+）


innodb_log_buffer_size
Variable Scope：                   Global
Dynamic Variable ：              No
Default：                                8MB
Meaning：                              InnoDB寫日誌檔案到磁碟上的緩衝大小
一個較大的日誌緩衝，可以使一個大的事務在commit之前不用將log寫到磁碟上。同樣的在update，delete，Insert
很多行時，也可以減少磁碟I/O的呼叫。
建議值：8~128MB


innodb_flush_log_at_trx_commit


Variable Scope：                   Global
Dynamic Variable：               Yes
Default：                                1
Meaning：                              控制日誌緩衝重新整理的頻繁程度
日誌緩衝必須重新整理到持久化儲存，以確保提交的事務完全被持久化了。如果和持久相比更在乎效能，可以修改
innodb_flush_log_at_trx_commit變數來控制日誌緩衝重新整理的頻繁程度。可能的設定如下：
0
把日誌緩衝寫到日誌檔案，並且每秒重新整理一次，但是事務提交時不做任何事情。
1
將日誌緩衝寫到日誌檔案，並且每次事務提交都重新整理到持久化儲存。這是預設的（並且是最安全的）設定，該設定
能保證不會丟失任何已經提交的事務，除非磁碟或者OS是’偽‘重新整理。
2
每次提交時把日誌緩衝寫到日誌檔案，但是並不重新整理。Innodb每秒做一次重新整理。0與2最重要的不同是，如果MySQL
程式“掛了”，2不會丟失事務。
建議值： 1


innodb_log_file_size


Variable Scope：                   Global
Dynamic Variable ：              No
Meaning：                              指定每個重做日誌檔案的大小
重做日誌檔案的大小設定對於InnoDB儲存引擎的效能有著非常大的影響。一方面重做日誌檔案不能設定的太大，如果設定得很大，在恢復時可能需要很長的時間；另一方面又不能設定太小了，
否則可能導致一個事務的日誌需要多次切換重做日誌檔案。
建議值： 根據自己能接受的方面（更好的效能or更短的恢復時間）決定


innodb_io_capacity
Variable Scope：                    Global
Dynamic Variable：                Yes
Default：                                  200
InnoDB曾經在程式碼裡寫死了假設伺服器執行在每秒100個I/O操作的單硬碟上。預設值很糟糕。現在可以告訴InnoDB伺服器有多大的I/O能力。有時需要把這個值設定得相當高（像SSD這樣極快的儲存
裝置上需要設定為上萬）才能穩定地重新整理髒頁。
建議值： 根據server的I/O能力有關係


innodb_read_io_threads和innodb_write_io_threads
這些選項控制有多少後臺執行緒可以被I/O操作使用。最近版本的MySQL裡，預設值4個讀執行緒和4個寫執行緒，對大部分
伺服器這都足夠了，尤其是MySQL5.5裡面可以用作業系統原生的非同步I/O以後。
建議值：各為4（即預設值）


innodb_log_files_in_group


Variable Scope：                    Global
Dynamic Variable：                No
Default ：                                2
Meaning：                               每組InnoDB重做日誌檔案的個數
建議值：                                    2


innodb_file_per_table


Variable Scope ：                   Global
Dynamic Variable ：               Yes


Default：                                 OFF（mysql5.6.5-）
Meaning：                              控制InnoDB表空間儲存形式
其預設值在mysql5.6.6+後為ON。開啟此選項後，關於InnoDB表的資料和索引單獨儲存在自己的表空間中（.ibd結尾的檔案）。否則，儲存在系統的表空間中（ibdata）。
建議值： ON

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2109403/，如需轉載，請註明出處，否則將追究法律責任。