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