Mysql配置調優詳細總結

針對mysql的conf檔案中的配置調優參數，進行全面詳細的總結。

1） Max_connections

（1）簡介
Mysql的最大連接配接數，如果伺服器的并發請求量比較大，可以調高這個值，當然這是要建立在機器能夠支撐的情況下，
因為如果連接配接數越來越多，mysql會為每個連接配接提供緩沖區，就會開銷的越多的記憶體，是以需要适當的調整該值，
不能随便去提高設值。
（2）判斷依據
show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+
（3）修改方式舉例
vim /etc/my.cnf 
Max_connections=1024
補充:
    1.開啟資料庫時,我們可以臨時設定一個比較大的測試值
    2.觀察show status like 'Max_used_connections';變化
    3.如果max_used_connections跟max_connections相同,
    那麼就是max_connections設定過低或者超過伺服器的負載上限了，
    低于10%則設定過大.           

2） back_log

（1）簡介
mysql能暫存的連接配接數量，當主要mysql線程在一個很短時間内得到非常多的連接配接請求時候它就會起作用，
如果mysql的連接配接資料達到max_connections時候，新來的請求将會被存在堆棧中，等待某一連接配接釋放資源，
該推棧的數量及back_log,如果等待連接配接的數量超過back_log，将不被授予連接配接資源。
back_log值指出在mysql暫時停止回答新請求之前的短時間内有多少個請求可以被存在推棧中，
隻有如果期望在一個短時間内有很多連接配接的時候需要增加它
（2）判斷依據
show full processlist
發現大量的待連接配接程序時，就需要加大back_log或者加大max_connections的值
（3）修改方式舉例
vim /etc/my.cnf 
back_log=1024           

3) wait_timeout和interactive_timeout

（1）簡介
wait_timeout：指的是mysql在關閉一個非互動的連接配接之前所要等待的秒數
interactive_timeout：指的是mysql在關閉一個互動的連接配接之前所需要等待的秒數，
比如我們在終端上進行mysql管理，使用的即使互動的連接配接，這時候，
如果沒有操作的時間超過了interactive_time設定的時間就會自動的斷開，預設的是28800，可調優為7200。
wait_timeout:如果設定太小，那麼連接配接關閉的就很快，進而使一些持久的連接配接不起作用
（2）設定建議
如果設定太大，容易造成連接配接打開時間過長，在show processlist時候，能看到很多的連接配接 ，
一般希望wait_timeout盡可能低
（3）修改方式舉例
wait_timeout=60
interactive_timeout=1200
長連接配接的應用，為了不去反複的回收和配置設定資源，降低額外的開銷。
一般我們會将wait_timeout設定比較小，interactive_timeout要和應用開發人員溝通長連結的應用是否很多。
如果他需要長連結，那麼這個值可以不需要調整。
另外還可以使用類外的參數彌補。           

4） key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度
通過key_read_requests和key_reads可以直到key_baffer_size設定是否合理。
mysql> show variables like "key_buffer_size%";
key_buffer_size=64M

           

5）max_connect_errors

max_connect_errors是一個mysql中與安全有關的計數器值，
它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼等情況，
當超過指定次數，mysql伺服器将禁止host的連接配接請求，
直到mysql伺服器重新開機或通過flush hosts指令清空此host的相關資訊 max_connect_errors的值與性能并無太大關系。
修改/etc/my.cnf檔案，在[mysqld]下面添加如下内容
max_connect_errors=2000           

6）sort_buffer_size

（1）簡介：
每個需要進行排序的線程配置設定該大小的一個緩沖區。增加這值加速
ORDER BY 
GROUP BY
distinct
union 

（2）配置依據
Sort_Buffer_Size并不是越大越好，由于是connection級的參數，過大的設定+高并發可能會耗盡系統記憶體資源。
列如：500個連接配接将會消耗500*sort_buffer_size（2M）=1G記憶體
（3）配置方法
 修改/etc/my.cnf檔案，在[mysqld]下面添加如下：
sort_buffer_size=1M           

7) max_allowed_packet

（1）簡介：
mysql根據配置檔案會限制，server接受的資料包大小。
（2）配置依據：
有時候大的插入和更新會受max_allowed_packet參數限制，導緻寫入或者更新失敗，更大值是1GB，
必須設定1024的倍數
（3）配置方法：
max_allowed_packet=32M           

8) join_buffer_size

select a.name,b.name from a join b on a.id=b.id where xxxx
用于表間關聯緩存的大小，和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每個連接配接獨享。
盡量在SQL與方面進行優化，效果較為明顯。
優化的方法：在on條件列加索引，至少應當是有MUL索引           

9) thread_cache_size

(1)簡介
伺服器線程緩存，這個值表示可以重新利用儲存在緩存中線程的數量,當斷開連接配接時,
那麼用戶端的線程将被放到緩存中以響應下一個客戶而不是銷毀(前提是緩存數未達上限),
如果線程重新被請求，那麼請求将從緩存中讀取,如果緩存中是空的或者是新的請求，
那麼這個線程将被重新建立,如果有很多新的線程，增加這個值可以改善系統性能.
（2）配置依據
通過比較 Connections 和 Threads_created 狀态的變量，可以看到這個變量的作用。
設定規則如下：1GB 記憶體配置為8，2GB配置為16，3GB配置為32，4GB或更高記憶體，可配置更大。
伺服器處理此客戶的線程将會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數未達上限)           

10) innodb_buffer_pool_size

（1）簡介
對于InnoDB表來說，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。
（2）配置依據：
InnoDB使用該參數指定大小的記憶體來緩沖資料和索引。
對于單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%,一般我們建議不要超過實體記憶體的70%。
（3）配置方法
innodb_buffer_pool_size=2048M           

11) innodb_flush_log_at_trx_commit

（1）簡介
主要控制了innodb将log buffer中的資料寫入日志檔案并flush磁盤的時間點，取值分别為0、1、2三個。
0，表示當事務送出時，不做日志寫入操作，而是每秒鐘将log buffer中的資料寫入日志檔案并flush磁盤一次；
1，
每次事務的送出都會引起redo日志檔案寫入、flush磁盤的操作，確定了事務的ACID；
2，每次事務送出引起寫入日志檔案的動作,但每秒鐘完成一次flush磁盤操作。

（2）配置依據
實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄隻需要2秒，設定為0時隻需要1秒，
而設定為1時則需要229秒。是以，MySQL手冊也建議盡量将插入操作合并成一個事務，這樣可以大幅提高速度。
根據MySQL官方文檔，在允許丢失最近部分事務的危險的前提下，可以把該值設為0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
雙1标準中的一個1           

12) innodb_log_buffer_size

此參數确定些日志檔案所用的記憶體大小，以M為機關。緩沖區更大能提高性能，對于較大的事務，可以增大緩存大小。
innodb_log_buffer_size=128M

設定依據：
1、大事務： 存儲過程調用 CALL
2、多事務           

13) innodb_log_file_size = 100M redo日志大小

設定 ib_logfile0  ib_logfile1 
此參數确定資料日志檔案的大小，以M為機關，更大的設定可以提高性能.
innodb_log_file_size = 100M           

14) innodb_log_files_in_group = 3 redo日志的個數，輪詢使用

為提高性能，MySQL可以以循環方式将日志檔案寫到多個檔案。推薦設定為3           

15) read_buffer_size = 1M

MySql讀入緩沖區大小。對表進行順序掃描的請求将配置設定一個讀入緩沖區，MySql會為它配置設定一段記憶體緩沖區。
如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，
可以通過增加該變量值以及記憶體緩沖區大小提高其性能。和 sort_buffer_size一樣，
該參數對應的配置設定記憶體也是每個連接配接獨享           

16) read_rnd_buffer_size = 1M

MySql的随機讀（查詢操作）緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，将配置設定一個随機讀緩存區。
進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜尋，提高查詢速度，如果需要排序大量資料，
可适當調高該值。但MySql會為每個客戶連接配接發放該緩沖空間，是以應盡量适當設定該值，以避免記憶體開銷過大。
注：順序讀是指根據索引的葉節點資料就能順序地讀取所需要的行資料。
随機讀是指一般需要根據輔助索引葉節點中的主鍵尋找實際行資料，而輔助索引和主鍵所在的資料段不同，
是以通路方式是随機的。           

17)bulk_insert_buffer_size = 8M 大量插入資料

批量插入資料緩存大小，可以有效提高插入效率，預設為8M
tokuDB    percona
myrocks   
RocksDB
TiDB
MongoDB           

18) binary log

log-bin=/data/mysql-bin
binlog_cache_size = 2M //為每個session 配置設定的記憶體，在事務過程中用來存儲二進制日志的緩存, 提高記錄bin-log的效率。沒有什麼大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以适當的調大一點。前者建議是--1M，後者建議是：即 2--4M
max_binlog_cache_size = 8M //表示的是binlog 能夠使用的最大cache 記憶體大小
max_binlog_size= 512M //指定binlog日志檔案的大小，如果目前的日志大小達到max_binlog_size，還會自動建立新的二進制日志。你不能将該變量設定為大于1GB或小于4096位元組。預設值是1GB。在導入大容量的sql檔案時，建議關閉sql_log_bin，否則硬碟扛不住，而且建議定期做删除。
expire_logs_days = 7 //定義了mysql清除過期日志的時間。
二進制日志自動删除的天數。預設值為0,表示“沒有自動删除”。
log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1
雙1标準(基于安全的控制)：
sync_binlog=1   什麼時候重新整理binlog到磁盤，每次事務commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';