1 優(yōu)化連接池

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

max_connections，MySQL最大并發(fā)連接數(shù)，默認(rèn)值是151，最大連接數(shù)上限是16384；

• 經(jīng)驗：實際連接數(shù)是最大連接數(shù)的 85% 較為合適。
  設(shè)置 max_used_connections 方法：

– 查詢數(shù)據(jù)庫目前設(shè)置的最大并發(fā)連接數(shù)是多少
SHOW VARIABLES LIKE ‘max_connections’;

– 查詢數(shù)據(jù)庫目前實際連接的并發(fā)數(shù)是多少
SHOW STATUS LIKE ‘max_used_connections’;

– 在MySQL配置文件 /etc/my.cnf 中設(shè)置 max_connections=3000，表示修改最大連接數(shù)為3000。
注意：需要重啟 MySQL 才能生效。
– MySQL為每個連接創(chuàng)建緩沖區(qū)，所以不應(yīng)該盲目上調(diào)最大連接數(shù)。

如果最大連接數(shù)達到了上面設(shè)置的 3000，會消耗大約 800M 內(nèi)存。

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

3. 優(yōu)化請求堆棧

back_log，存放執(zhí)行請求的堆棧大小，默認(rèn)值是50。
– 該值設(shè)置為最大并發(fā)連接數(shù)的 20%～30% 較為合適。
設(shè)置 back_log 方法：
– 在MySQL配置文件 /etc/my.cnf 中，設(shè)置 back_log=600
– 修改后需要重啟 MySQL 才能生效。

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

4. 修改連接超時時間

wait-timeout，超時時間，單位是秒，連接默認(rèn)超時為8小時，連接長期不用不銷毀，比較浪費資源。
– 經(jīng)驗：設(shè)置超時時間為 10 分鐘 wait-timeout=600。

5. 優(yōu)化內(nèi)存緩沖池

緩沖池運行機制
– 在MySQL5.5之前，廣泛使用的和默認(rèn)的存儲引擎是MyISAM。MyISAM使用操作系統(tǒng)緩存來緩存數(shù)據(jù)。InnoDB需要innodb buffer pool中處理緩存，所以非常需要有足夠的InnoDB buffer pool空間。
– 緩沖區(qū)分為 熱數(shù)據(jù)區(qū) / 冷數(shù)據(jù)區(qū)，兩者空間占比約為 7/3，每區(qū)中的數(shù)據(jù)集依使用頻率按順序依次排列。
當(dāng)一個新的查詢結(jié)果出現(xiàn)后，首先考慮存放到冷數(shù)據(jù)區(qū)，當(dāng)冷數(shù)據(jù)區(qū)的結(jié)果集使用達到一定頻率，會被改存到熱數(shù)據(jù)區(qū)，使用頻率最好的數(shù)據(jù)集會被存放到熱區(qū)的首位，當(dāng)然也有熱區(qū)轉(zhuǎn)到冷區(qū)的狀況。
InnoDB 緩沖池不僅僅是一個緩存，MySQL InnoDB buffer pool 包含四部分：
– 數(shù)據(jù)緩存，InnoDB 數(shù)據(jù)頁面；
– 索引緩存，索引數(shù)據(jù)；
– 緩沖數(shù)據(jù)，臟頁（在內(nèi)存中修改尚未寫入到磁盤的數(shù)據(jù)）；
– 內(nèi)部結(jié)構(gòu)，如自適應(yīng)哈希索引，行鎖等。

innodb_buffer_pool_instances，內(nèi)存緩沖池。
– buffer_pool 把需要緩沖的數(shù)據(jù) hash 到不同的緩沖池中，這樣可以并行的內(nèi)存讀寫。通過減少爭用不同線程對緩存頁面進行讀寫的爭用，將緩沖池劃分為多個單獨的實例可以提高并發(fā)性。
– MySQL 5.7、MySQL 8.0 下 innodb_buffer_pool_instances 默認(rèn)為 1，若 MySQL 存在高并發(fā)和高負(fù)載訪問，設(shè)置為 1 則會造成大量線程對 buffer_pool 的單實例互斥鎖競爭，這樣會消耗一定量的性能的。

– innodb_buffer_pool_instances 建議設(shè)置為 cpu核心數(shù)。
innodb_buffer_pool_chunk_size，緩沖池每塊大小，默認(rèn)128M。
– pool_chunk_size 一般不做改動，使用默認(rèn)值就可以。
innodb_buffer_pool_size，緩沖池的承載總量。
– innodb_buffer_pool_size 可以緩存索引和行數(shù)據(jù)，值越大、IO讀寫就越少；
– 設(shè)置規(guī)則：innodb_buffer_pool_size = （innodb_buffer_pool_chunk_size * {N}塊 ）* innodb_buffer_pool_instances
– 如果單純的做數(shù)據(jù)庫服務(wù)，該參數(shù)可以設(shè)置到電腦物理內(nèi)存的80%；
– 為了更好的配合 pool_instance，pool_size 需要設(shè)置為 pool_instance 和 pool_chunk_size 的整數(shù)倍，這樣可以被 pool_instance 整除，為每個 buffer pool 實例平均分配內(nèi)存。如果設(shè)置的值不是倍數(shù)，MySQL會自動將 pool_size 調(diào)整為 pool_chunk_size 的倍數(shù)。

6. 優(yōu)化并發(fā)線程數(shù)

innodb_thread_concurrency，代表并發(fā)線程數(shù)。
– 默認(rèn)是0，表示沒有設(shè)置線程數(shù)量的上限。
– 不是分配給 MySQL 的線程越多越好，線程多反而會損耗cpu性能，導(dǎo)致速度變慢。
– 經(jīng)驗：并發(fā)線程數(shù)應(yīng)該設(shè)置為 cpu 核心數(shù)的兩倍。
– 注意：這個變量特定于Solaris 8和更早的系統(tǒng)，MySQL 5.7.2中刪除了這個變量。
設(shè)置 innodb_thread_concurrency 方法：
– 在MySQL配置文件 /etc/my.cnf 中，設(shè)置 innodb_thread_concurrency=8。
– 查看cpu型號
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
– 查看cpu核心數(shù)
cat /proc/cpuinfo | grep “cores”|uniq

7. 優(yōu)化線程池

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

7.1 查看線程池的狀態(tài)：

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 中存放的最大連接線程數(shù)。
– 在短連接的應(yīng)用中，Threads_cached 的功效非常明顯，因為在應(yīng)用中數(shù)據(jù)庫的連接和創(chuàng)建是非常頻繁的。如果不使用 Threads_cached，那么消耗的資源是非常頻繁的。
– 在長連接中雖然帶來的改善沒有短連接的那么明顯，但是好處是顯而易見的。但并不是越大越好，大了反而浪費資源，這個的確定一般認(rèn)為和物理內(nèi)存有一定關(guān)系。
– Mysql默認(rèn)值為9。
設(shè)置 thread_cache_size 方法：
– 參考下面額對照表，根據(jù)物理內(nèi)存設(shè)置對應(yīng)的 thread_cache_size 數(shù)值：

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

3G —> 64

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

mysql> set global thread_concurrency=4；
thread_handling
運用 Thread_Cache 處理連接的方式，從 5.1.19 添加的新特性，有兩個值可選 no-threads、one-thread-per-connection。
– no-threads ：服務(wù)器使用一個線程
– one-thread-per-connection ：服務(wù)器為每個客戶端請求使用一個線程

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

7.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里面為了提高客戶端請求創(chuàng)建連接過程的性能，提供了一個連接池也就是 Thread_cache 池（大小是thread_cache_size），將空閑的連接線程放在連接池中，而不是立即銷毀。
這樣的好處就是，當(dāng)又有一個新的請求的時候，mysql不會立即去創(chuàng)建連接 線程，而是先去 Thread_Cache 中去查找空閑的連接線程，如果存在則直接使用，不存在才創(chuàng)建新的連接線程。Thread_cache 值表示已經(jīng)被線程緩存池緩存的線程個數(shù)。

Threads_connected
當(dāng)前處于連接狀態(tài)的線程個數(shù)，等于 show processlist。

Threads_created
Threads_created 表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn) Threads_created 值過大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗資源，可以適當(dāng)增加配置文件中 thread_cache_size 值。

Threads_running
處于激活狀態(tài)的線程的個數(shù)，這個一般都是遠小于Threads_connected的。

8. 優(yōu)化日志

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

錯誤日志：啟動、關(guān)閉、運行時 產(chǎn)生的異常記錄，建議開啟，設(shè)置 log_error
查詢?nèi)罩荆嚎蛻舳诉B接和執(zhí)行的腳本，建議關(guān)閉，設(shè)置 general_log
慢查詢?nèi)罩荆河涗洺瑫r的查詢，記錄不適用索引的查詢等，建議關(guān)閉，設(shè)置 slow_query_log
二進制日志：用于數(shù)據(jù)同步復(fù)制，需發(fā)送的數(shù)據(jù)日志，多用于集群，如需開啟，設(shè)置 log_bin
中繼日志：用于數(shù)據(jù)同步復(fù)制時，接收到的數(shù)據(jù)日志，多用于集群，如需開啟，設(shè)置 relay_log

9. 鎖優(yōu)化

9.1. innodb 鎖優(yōu)化

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

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

9.2. MyISAM 鎖優(yōu)化

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

縮短鎖定時間
– 盡兩減少大的復(fù)雜Query，將復(fù)雜Query 分拆成幾個小的Query 分布進行；
– 盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速；
– 盡量讓MyISAM 存儲引擎的表只存放必要的信息，控制字段類型；
– 利用合適的機會優(yōu)化MyISAM 表數(shù)據(jù)文件；

max_write_lock_count：
缺省情況下，寫操作的優(yōu)先級要高于讀操作的優(yōu)先級，即便是先發(fā)送的讀請求，后發(fā)送的寫請求，此時也會優(yōu)先處理寫請求，然后再處理讀請求。這就造成一 個問題：一旦我發(fā)出若干個寫請求，就會堵塞所有的讀請求，直到寫請求全都處理完，才有機會處理讀請求。此時可以考慮使用 max_write_lock_count：
max_write_lock_count=1
有了這樣的設(shè)置，當(dāng)系統(tǒng)處理一個寫操作后，就會暫停寫操作，給讀操作執(zhí)行的機會。
low-priority-updates：
我們還可以更干脆點，直接降低寫操作的優(yōu)先級，給讀操作更高的優(yōu)先級。
low-priority-updates=1
綜合來看，concurrent_insert=2是絕對推薦的，至于max_write_lock_count=1和low-priority- updates=1，則視情況而定，如果可以降低寫操作的優(yōu)先級，則使用low-priority-updates=1，否則使用 max_write_lock_count=1。
set-variable = max_allowed_packet=1M
set-variable = net_buffer_length=2K文章來源地址http://www.zghlxwxcb.cn/news/detail-765763.html

到了這里，關(guān)于mysql_性能參數(shù)調(diào)優(yōu)詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！