MySQL并發(fā)參數(shù)調(diào)整的示例分析

這篇文章主要介紹了MySQL并發(fā)參數(shù)調(diào)整的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

Mysql并發(fā)參數(shù)調(diào)整

從實現(xiàn)上來說，MySQL Server 是多線程結(jié)構(gòu)，包括后臺線程和客戶服務(wù)線程。多線程可以有效利用服務(wù)器資源，提高數(shù)據(jù)庫的并發(fā)性能。在Mysql中，控制并發(fā)連接和線程的主要參數(shù)包括 max_connections、back_log、thread_cache_size、table_open_cahce。

1、max_connections

采用max_connections 控制允許連接到MySQL數(shù)據(jù)庫的最大數(shù)量，默認(rèn)值是 151。如果狀態(tài)變量 connection_errors_max_connections 不為零，并且一直增長，則說明不斷有連接請求因數(shù)據(jù)庫連接數(shù)已達(dá)到允許最大值而失敗，這是可以考慮增大max_connections 的值。
說明：當(dāng)同時連接的請求超過151個之后，就沒有可用的連接來處理客戶端的請求了。后面進(jìn)來的這些連接都會處于等待狀態(tài)。等待MySQL連接被釋放，如果沒有空閑的連接，就會請求超時

Mysql 最大可支持的連接數(shù)，取決于很多因素，包括給定操作系統(tǒng)平臺的線程庫的質(zhì)量、內(nèi)存大小、每個連接的負(fù)荷、CPU的處理速度，期望的響應(yīng)時間等。在Linux 平臺下，性能好的服務(wù)器，支持 500-1000 個連接不是難事，需要根據(jù)服務(wù)器性能進(jìn)行評估設(shè)定。

2、back_log

back_log 參數(shù)控制MySQL監(jiān)聽TCP端口時設(shè)置的積壓請求棧大小。如果MySql的連接數(shù)達(dá)到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源，將會報錯。5.6.6 版本之前默認(rèn)值為 50 ， 之后的版本默認(rèn)為 50 + （max_connections / 5）， 但最大不超過900。
說明：當(dāng)客戶端同時發(fā)過來的請求并發(fā)量大于151，后面過來的請求處于等待的狀態(tài)。那么等待的連接數(shù)可以達(dá)到back_log。這些新來的請求就會被存儲在堆棧當(dāng)中。以等待某個連接被釋放。該堆棧的數(shù)量就通過back_log來設(shè)置。

如果需要數(shù)據(jù)庫在較短的時間內(nèi)處理大量連接請求， 可以考慮適當(dāng)增大back_log 的值。

3、table_open_cache

該參數(shù)用來控制所有SQL語句執(zhí)行線程可打開表緩存的數(shù)量， 而在執(zhí)行SQL語句時，每一個SQL執(zhí)行線程至少要打開 1 個表緩存。該參數(shù)的值應(yīng)該根據(jù)設(shè)置的最大連接數(shù) max_connections 以及每個連接執(zhí)行關(guān)聯(lián)查詢中涉及的表的最大數(shù)量來設(shè)定 ：

max_connections x N ；
說明：這個不是針對于某個會話的，這個是針對于所有客戶端執(zhí)行線程。表緩存的數(shù)量，也就是每個SQL語句中操作表的個數(shù)，比如一條SQL語句一般至少操作一張表。操作一張表，就有1個表緩存，操作多張表，就有多個表緩存。

mysql> show variables like 'table_open_cache%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| table_open_cache           | 431   || table_open_cache_instances | 16    |+----------------------------+-------+2 rows in set (0.06 sec)

4、thread_cache_size

為了加快連接數(shù)據(jù)庫的速度，MySQL 會緩存一定數(shù)量的客戶服務(wù)線程以備重用，通過參數(shù) thread_cache_size 可控制 MySQL 緩存客戶服務(wù)線程的數(shù)量。
說明：這就相當(dāng)于在MySQL的服務(wù)端開啟了一個線程池，客戶端有請求過來，我們在線程池中拿出一個線程來執(zhí)行任務(wù)處理。

mysql> show variables like 'thread_cache_size%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| thread_cache_size | 8     |+-------------------+-------+1 row in set (0.00 sec)

一共緩存了8個線程信息。

5、innodb_lock_wait_timeout

該參數(shù)是用來設(shè)置InnoDB 事務(wù)等待行鎖的時間，默認(rèn)值是50ms ， 可以根據(jù)需要進(jìn)行動態(tài)設(shè)置。對于需要快速反饋的業(yè)務(wù)系統(tǒng)來說，可以將行鎖的等待時間調(diào)小，以避免事務(wù)長時間掛起； 對于后臺運(yùn)行的批量處理程序來說， 可以將行鎖的等待時間調(diào)大， 以避免發(fā)生大的回滾操作。

說明：如果在比較快速響應(yīng)的業(yè)務(wù)系統(tǒng)中，如果沒有拿到行鎖，就直接報錯即可，不需要讓事務(wù)長時間響應(yīng)。
可以看到，默認(rèn)超時時間為50ms

mysql> show variables like 'innodb_lock_wait_timeout%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50    |+--------------------------+-------+1 row in set (0.01 sec)

    | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50    |+--------------------------+-------+1 row in set (0.01 sec)

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“MySQL并發(fā)參數(shù)調(diào)整的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!