mysql 性能參數調優詳解

1 優化連接池

連接池運行機制
MySQL連接器中的連接池，用以提高數據庫密集型應用程序的性能和可擴展性，默認啟用。MySQL連接器負責管理連接池中的多個連接，自動創建、打開、關閉和破壞連接，多個連接的創建，可滿足多客戶端的頻繁連接，連接的重復使用獲得最佳性能。
MySQL連接器
每三分鐘運行一次后臺作業，并從池中刪除閑置（未使用）超過三分鐘的連接。池清理釋放客戶端和服務器端的資源。這是因為在客戶端每個連接都使用一個Socket，而在服務器端每個連接都使用一個Socket和一個線程。

max_connections，MySQL最大并發連接數，默認值是151，最大連接數上限是16384；

經驗：實際連接數是最大連接數的 85% 較為合適。設置 max_used_connections 方法

查詢數據庫目前設置的最大并發連接數是多少

SHOW VARIABLES LIKE ‘max_connections’;

查詢數據庫目前實際連接的并發數是多少
SHOW STATUS LIKE ‘max_used_connections’;
在MySQL配置文件 /etc/my.cnf 中設置 max_connections=3000，表示修改最大連接數為3000。

注意：需要重啟 MySQL 才能生效。 – MySQL為每個連接創建緩沖區，所以不應該盲目上調最大連接數。

如果最大連接數達到了上面設置的 3000，會消耗大約 800M 內存。

其他連接池設置：
開啟連接池： Pooling=true，默認開啟
復用時重置連接狀態： ConnectionReset=True
保持連接設置： CacheServerProperties=True
連接超時回收(秒)： ConnectionLifeTime=300
支持的最大連接數量： Max Pool Size=100
保持最小的連接數量： Min Pool Size=10

2. 優化請求堆棧

back_log，存放執行請求的堆棧大小，默認值是50。

該值設置為最大并發連接數的 20%～30% 較為合適。
設置 back_log 方法：
在MySQL配置文件 /etc/my.cnf 中，設置 back_log=600
修改后需要重啟 MySQL 才能生效。

back_log 在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。
也就是說，如果MySql的連接數達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。
將會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL |
login | NULL 的待連接進程時. back_log值不能超過TCP/IP連接的偵聽隊列的大小。
若超過則無效，查看當前系統的TCP/IP連接的偵聽隊列的大小命令：cat
/proc/sys/net/ipv4/tcp_max_syn_backlog，目前系統為1024。
對于Linux系統推薦設置為大于512的整數。修改系統內核參數，可以編輯/etc/sysctl.conf去調整它。
如：net.ipv4.tcp_max_syn_backlog = 2048，改完后執行sysctl -p 讓修改立即生效。查看mysql
當前系統默認back_log值，命令：
show variables like ‘back_log’;

3. 修改連接超時時間

wait-timeout，超時時間，單位是秒，連接默認超時為8小時，連接長期不用不銷毀，比較浪費資源。
查看

SHOW VARIABLES LIKE 'wait_timeout%';

經驗：設置超時時間為 10 分鐘 wait-timeout=600

4. 優化內存緩沖池

緩沖池運行機制

在MySQL5.5之前，廣泛使用的和默認的存儲引擎是MyISAM。MyISAM使用操作系統緩存來緩存數據。InnoDB需要innodb buffer pool中處理緩存，所以非常需要有足夠的InnoDB buffer pool空間。
緩沖區分為熱數據區 / 冷數據區，兩者空間占比約為 7/3，每區中的數據集依使用頻率按順序依次排列。當一個新的查詢結果出現后，首先考慮存放到冷數據區，當冷數據區的結果集使用達到一定頻率，會被改存到熱數據區，使用頻率最好的數據集會被存放到熱區的首位，當然也有熱區轉到冷區的狀況。

InnoDB 緩沖池不僅僅是一個緩存，MySQL InnoDB buffer pool 包含四部分：
1. 數據緩存，InnoDB 數據頁面；
2. 索引緩存，索引數據；
3. 緩沖數據，臟頁（在內存中修改尚未寫入到磁盤的數據）；
4. 內部結構，如自適應哈希索引，行鎖等。

innodb_buffer_pool_instances 內存緩沖池。

buffer_pool 把需要緩沖的數據 hash 到不同的緩沖池中，這樣可以并行的內存讀寫。通過減少爭用不同線程對緩存頁面進行讀寫的爭用，將緩沖池劃分為多個單獨的實例可以提高并發性。
MySQL 5.7、MySQL 8.0 下 innodb_buffer_pool_instances 默認為 1，若 MySQL 存在高并發和高負載訪問，設置為 1 則會造成大量線程對 buffer_pool 的單實例互斥鎖競爭，這樣會消耗一定量的性能的。
innodb_buffer_pool_instances 建議設置為 cpu核心數。
innodb_buffer_pool_chunk_size，緩沖池每塊大小，默認128M。
pool_chunk_size 一般不做改動，使用默認值就可以。
innodb_buffer_pool_size，緩沖池的承載總量。
innodb_buffer_pool_size 可以緩存索引和行數據，值越大、IO讀寫就越少；

設置規則：innodb_buffer_pool_size = （innodb_buffer_pool_chunk_size * {N}塊）*innodb_buffer_pool_instances

如果單純的做數據庫服務，該參數可以設置到電腦物理內存的80%；
為了更好的配合 pool_instance，pool_size 需要設置為 pool_instance 和 pool_chunk_size 的整數倍，這樣可以被 pool_instance 整除，為每個 buffer pool 實例平均分配內存。如果設置的值不是倍數，MySQL會自動將 pool_size 調整為 pool_chunk_size 的倍數。

5. 優化并發線程數

innodb_thread_concurrency，代表并發線程數。
默認是0，表示沒有設置線程數量的上限。不是分配給 MySQL 的線程越多越好，線程多反而會損耗cpu性能，導致速度變慢。

經驗：并發線程數應該設置為 cpu 核心數的兩倍。

注意：這個變量特定于Solaris 8和更早的系統，MySQL 5.7.2中刪除了這個變量。

設置 innodb_thread_concurrency 方法：

在MySQL配置文件 /etc/my.cnf 中，設置 innodb_thread_concurrency=8。

– 查看cpu型號
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
– 查看cpu核心數
cat /proc/cpuinfo | grep “cores”|uniq

6. 優化線程池

客戶端發起連接到 MySQL Server 后，MySQL Server監聽進程監聽到新的請求，然后 Sever 會為其分配一個新的 thread去處理此請求。
從建立連接之開始，CPU要給它劃分一定的 thread stack，然后進行用戶身份認證，建立上下文信息，最后請求完成，關閉連接，同時釋放資源。
在高并發的情況下，這個過程將給系統帶來巨大的壓力，不能保證性能。MySQL服務器的線程數需要在一個合理的范圍之內，這樣才能保證MySQL服務器健康平穩地運行。

6.1 查看線程池的狀態：

mysql> show variables like ‘thread%’;
±-------------------±--------------------------+
| Variable_name | Value |
±-------------------±--------------------------+
| thread_cache_size | 64 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
±-------------------±--------------------------+

thread_cache_size

thread_cache_size，Threads_cached 中存放的最大連接線程數。

在短連接的應用中，Threads_cached 的功效非常明顯，因為在應用中數據庫的連接和創建是非常頻繁的。如果不使用 Threads_cached，那么消耗的資源是非常頻繁的。
在長連接中雖然帶來的改善沒有短連接的那么明顯，但是好處是顯而易見的。但并不是越大越好，大了反而浪費資源，這個的確定一般認為和物理內存有一定關系。
Mysql默認值為9。
設置 thread_cache_size 方法：
參考下面額對照表，根據物理內存設置對應的 thread_cache_size 數值：

1G —> 8
2G —> 16
3G —> 32
3G —> 64

6.2 在 mysql 命令行中設置：

mysql> set global thread_cache_size=64；

thread_concurrency

thread_concurrency 應設為 CPU核數的2倍。
比如有一個雙核的CPU，那么thread_concurrency的應該為4。這個變量是針對Solaris系統的，如果設置這個變量的話，mysqld就會調用thr_setconcurrency()。這個函數使應用程序給同一時間運行的線程系統提供期望的線程數目。但是在5.7以后就已經拋棄了。
設置 thread_concurrency 方法：
– 在 mysql 命令行中設置：

mysql> set global thread_concurrency=4；

thread_handling

運用 Thread_Cache 處理連接的方式，從 5.1.19 添加的新特性，有兩個值可選 no-threads、one-thread-per-connection。

no-threads ：服務器使用一個線程
one-thread-per-connection ：服務器為每個客戶端請求使用一個線程

thread_stack

每個連接被創建的時候，mysql分配給它的內存。這個值一般認為默認就可以應用于大部分場景了，除非必要非則不要動它。上面表示是256kb。

6.2 查看線程使用情況：

mysql> show global status like ‘Thread%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 41 |
| Threads_connected | 53 |
| Threads_created | 541 |
| Threads_running | 4 |
±------------------±------+

Threads_cached

MySQL里面為了提高客戶端請求創建連接過程的性能，提供了一個連接池也就是 Thread_cache 池（大小是thread_cache_size），將空閑的連接線程放在連接池中，而不是立即銷毀。
這樣的好處就是，當又有一個新的請求的時候，mysql不會立即去創建連接線程，而是先去 Thread_Cache 中去查找空閑的連接線程，如果存在則直接使用，不存在才創建新的連接線程。Thread_cache 值表示已經被線程緩存池緩存的線程個數。

Threads_connected

當前處于連接狀態的線程個數，等于 show processlist。

Threads_created

Threads_created 表示創建過的線程數，如果發現 Threads_created 值過大的話，表明MySQL服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中 thread_cache_size 值。

Threads_running

處于激活狀態的線程的個數，這個一般都是遠小于Threads_connected的。

7.優化日志

日志運行機制
MySQL在運行時，會有各種不同日志的記錄，大量的各種類型的日志產生，會對資源的開銷產生嚴重的影響，必要的時候我們選擇性的開啟。
但在生產環境時，有些日志并不是必須，以下列出MySQL各種日志信息：

錯誤日志：啟動、關閉、運行時產生的異常記錄，建議開啟，設置 log_error
查詢日志：客戶端連接和執行的腳本，建議關閉，設置 general_log
慢查詢日志：記錄超時的查詢，記錄不適用索引的查詢等，建議關閉，設置 slow_query_log
二進制日志：用于數據同步復制，需發送的數據日志，多用于集群，如需開啟，設置 log_bin
中繼日志：用于數據同步復制時，接收到的數據日志，多用于集群，如需開啟，設置 relay_log

8. 鎖優化

8.1. innodb 鎖優化

Innodb 存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發處理能力方面要遠遠優于MyISAM 的表級鎖定的。

盡可能讓所有的數據檢索都通過索引來完成，從而避免Innodb 因為無法通過索引鍵加鎖而升級為表級鎖定；
合理設計索引，讓Innodb 在索引鍵上面加鎖的時候盡可能準確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query 的執行；
盡可能減少基于范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；
盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度；
在業務環境允許的情況下，盡量使用較低級別的事務隔離，以減少MySQL 因為實現事務隔離級別所帶來的附加成本；
減少 innodb 死鎖產生概率的建議：
類似業務模塊中，盡可能按照相同的訪問順序來訪問，防止產生死鎖；
在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；
對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

9.2. MyISAM 鎖優化

在MyISAM里讀寫操作是串行的，但當對同一個表進行查詢和插入操作時，為了降低鎖競爭的頻率，根據concurrent_insert的設置，MyISAM是可以并行處理查詢和插入的

縮短鎖定時間

盡兩減少大的復雜Query，將復雜Query 分拆成幾個小的Query 分布進行；
盡可能的建立足夠高效的索引，讓數據檢索更迅速；
盡量讓MyISAM 存儲引擎的表只存放必要的信息，控制字段類型；
利用合適的機會優化MyISAM 表數據文件；
max_write_lock_count：
缺省情況下，寫操作的優先級要高于讀操作的優先級，即便是先發送的讀請求，后發送的寫請求，此時也會優先處理寫請求，然后再處理讀請求。這就造成一個問題：一旦我發出若干個寫請求，就會堵塞所有的讀請求，直到寫請求全都處理完，才有機會處理讀請求。此時可以考慮使用 max_write_lock_count：
max_write_lock_count=1
有了這樣的設置，當系統處理一個寫操作后，就會暫停寫操作，給讀操作執行的機會。
low-priority-updates：
我們還可以更干脆點，直接降低寫操作的優先級，給讀操作更高的優先級。
low-priority-updates=1
綜合來看，concurrent_insert=2是絕對推薦的，至于max_write_lock_count=1和low-priority- updates=1，則視情況而定，如果可以降低寫操作的優先級，則使用low-priority-updates=1，否則使用 max_write_lock_count=1。
set-variable = max_allowed_packet=1M
set-variable = net_buffer_length=2K