mysql服務(wù)端參數(shù)

1、innodb_buffer_pool_size （innodb索引buffer pool緩沖區(qū)大?。?/h4>

默認(rèn)大小為128M， 官方推薦其配置為系統(tǒng)內(nèi)存的 50% 到 75% 。

一般innodb_buffer_pool_size要結(jié)合以下兩個參數(shù)來設(shè)置：

1. innodb_buffer_pool_chunk_size

從 MySQL 5.7.5 開始，我們可以動態(tài)修改 InnoDB Buffer Pool 的大小。這個新特性同時也引入了一個參數(shù) innodb_buffer_pool_chunk_size，buffer pool 會根據(jù)這個參數(shù)值的整數(shù)倍增加或減小。增大或減小緩沖池大小時，將以 chunk（塊）的形式執(zhí)行操作。chunk 大小由innodb_buffer_pool_chunk_size配置選項定義，默認(rèn)值為 128 MB。

2. innodb_buffer_pool_instances

一般地，mysql 5.7、8.0 下 INNODB_BUFFER_POOL_INSTANCES 默認(rèn)為1（單實例），若 mysql 存在高并發(fā)和高負(fù)載訪問，設(shè)置為1則會造成大量線程對 BUFFER_POOL 的單實例互斥鎖競爭，這樣會存在一定量的性能問題， 該參數(shù)可以設(shè)置為服務(wù)器 CPU 核心數(shù)，這樣可在一定程度上提供并發(fā)性能。

該參數(shù)僅在你設(shè)置innodb_buffer_pool_size 等于1GB或大于1GB時才生效， 該參數(shù)的主要作用在于，對緩沖池在數(shù) GB 范圍內(nèi)的系統(tǒng)，將緩沖池劃分為單獨的實例可以通過減少不同線程讀取和寫入緩存頁面時的鎖競爭，以此來提高 MySQL 的并發(fā)性（相當(dāng)于多實例）

2、innodb_log_file_size （innodb索引redolog大?。?/h4>

2.3 常用操作
1、查看 innodb_log_file_size 大小

root@mysql 16:14:  [(none)]>show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 134217728 |
+----------------------+-----------+
1 row in set (0.06 sec)

2、實時觀察 Redo Log 寫入量

a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res

用于監(jiān)控是很有用的，隨時查看參數(shù)值的變化情況。

2.4 什么情況下調(diào)整該參數(shù)
innodb_log_file_size 值越大，在緩沖池中需要檢查點刷新的行為就越少，因此也越節(jié)約磁盤I/O，但更大的日志文件也意味著在崩潰時恢復(fù)得更慢。建議將日志文件的大小設(shè)置為 256MB 或更大，這樣可以滿足一般情況下的需要。

當(dāng)然，大家要根據(jù)實際情況，結(jié)合 innodb_log_files_in_group、innodb_buffer_pool_size 的值來確定最合適當(dāng)前服務(wù)的 innodb_log_file_size 值。

3、innodb_flush_log_at_trx_commit （redolog持久化策略）

默認(rèn)值為 1：在每次事務(wù)提交時寫入日志并刷新到磁盤。
設(shè)置為 0 時：每秒寫入日志并刷新到磁盤一次。未刷新日志的事務(wù)可能會在崩潰中丟失。
設(shè)置為 2 時：每次事務(wù)提交后寫入日志，并每秒刷新到磁盤一次。未刷新日志的事務(wù)可能會在崩潰中丟失。

優(yōu)化：https://blog.csdn.net/jnrjian/article/details/129704893

4、sync_binlog （binlog持久化策略）

sync_binlog = 0 （mysql默認(rèn)值 ）由文件系統(tǒng)決定將 binlog 同步到硬盤。
sync_binlog = 1 每提交一次事務(wù)，寫一次 binlog，并使用 fsync() 同步到硬盤（mysql 在 autocommit模式下）。
sync_binlog = n 每提交一次事務(wù)，寫一次 binlog，達到 n 次后，調(diào)用 fsync() 同步到硬盤。

————————————————
版權(quán)聲明：本文為CSDN博主「云計算-Security」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/IT_ZRS/article/details/120622265

5、sort_buffer_size （單個鏈接 排序緩沖區(qū)）

–表示每個 需要進行排序的線程分配的緩沖區(qū)的大小
增加這個參數(shù)的值可以提高 ORDER BY 或 GROUP BY 操作的速度。默認(rèn)數(shù)值是2 097 144字節(jié)（約2MB）。對于內(nèi)存在4GB
左右的服務(wù)器推薦設(shè)置為6-8M，如果有100個連接，那么實際分配的總共排序緩沖區(qū)大小為100 × 6 ＝ 600MB

6、join_buffer_size （單個鏈接聯(lián)合查詢 緩沖區(qū)）

默認(rèn)8m，表示 聯(lián)合查詢操作所能使用的緩沖區(qū)大小 ，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享。

7、read_buffer_size （單個鏈接 讀取單個表數(shù)據(jù)緩沖區(qū)）

表示 每個線程連續(xù)掃描時為掃描的每個表分配的緩沖區(qū)的大?。ㄗ止?jié)） 。
當(dāng)線程從表中連續(xù)讀取記錄時需要用到這個緩沖區(qū)。
SET SESSION read_buffer_size=n可以臨時設(shè)置該參數(shù)的值。默認(rèn)為64K，可以設(shè)置為4M。

8、max_connections （最大鏈接數(shù)）

–表示 允許連接到MySQL數(shù)據(jù)庫的最大數(shù)量 (默認(rèn)值是 151)
如果狀態(tài)變量connection_errors_max_connections 不為零，并且一直增長，則說明不斷有連接請求因數(shù)據(jù)庫連接數(shù)已達到允許最大值而失敗，這是可以考慮增大max_connections 的值。在Linux 平臺下，性能好的服務(wù)器，支持 500-1000 個連接不是難事，需要根據(jù)服務(wù)器性能進行評估設(shè)定。這個連接數(shù) 不是越大 越好 ，因為這些連接會浪費內(nèi)存的資源。過多的連接可能會導(dǎo)致MySQL服務(wù)器僵死。

9、back_log （請求積壓數(shù)）

高并發(fā)要求下適當(dāng)增大
用于 控制MySQL監(jiān)聽TCP端口時設(shè)置的積壓請求棧大小 。如果MySql的連接數(shù)達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源，將會報錯。5.6.6 版本之前默認(rèn)值為 50 ， 之后的版本默認(rèn)為 50 + （max_connections / 5）， 對于Linux系統(tǒng)推薦設(shè)置為小于512的整數(shù)，但最大不超過900。如果需要數(shù)據(jù)庫在較短的時間內(nèi)處理大量連接請求， 可以考慮適當(dāng)增大back_log 的值。

10、thread_cache_size （空閑線程大?。?/h4>

–線程池緩存線程數(shù)量的大小
當(dāng)客戶端斷開連接后將當(dāng)前線程緩存起來，當(dāng)在接到新的連接請求時快速響應(yīng)無需創(chuàng)建新的線程 。這尤其對那些使用短連接的應(yīng)用程序來說可以極大的提高創(chuàng)建連接的效率。
那么為了提高性能可以增大該參數(shù)的值。默認(rèn)為’60’，可以設(shè)置為120。
可以通過如下幾個MySQL狀態(tài)值來適當(dāng)調(diào)整線程池的大?。?br> mysql> show global status like ‘Thread%’;

當(dāng) Threads_cached 越來越少，但 Threads_connected 始終不降，且 Threads_created 持續(xù)升高，可
適當(dāng)增加 thread_cache_size 的大小。

11、wait_timeout （鏈接超時時長）

指定 一個請求的最大連接時間 ，對于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。
wait_timeout=28800
interactive_timeout=28800
connect_timeout=10

12、鎖超時時間

lock_wait_timeout=30

13、推薦參數(shù)設(shè)置

[mysqld]
port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部鎖定，減少出錯幾率增強穩(wěn)定性。 
skip-name-resolve #禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠(yuǎn)程主機連接授權(quán)都要使用IP地址方式，否則MySQL將無法正常處理連接請求！ 
back_log = 384 
key_buffer_size = 256M 
max_allowed_packet = 4M 
thread_stack = 256K 
table_cache = 128K 
sort_buffer_size = 6M 
read_buffer_size = 4M 
read_rnd_buffer_size=16M 
join_buffer_size = 8M
myisam_sort_buffer_size = 64M 
table_cache = 512
thread_cache_size = 64 
query_cache_size = 64M 
tmp_table_size = 256M
max_connections = 768 
max_connect_errors = 10000000
wait_timeout = 10 
thread_concurrency = 8 #該參數(shù)取值為服務(wù)器邏輯CPU數(shù)量*2，在本例中，服務(wù)器有2顆物理CPU，而每顆物理CPU又支持H.T超線程，所以實際取值為4*2=8 
skip- networking #開啟該選項可以徹底關(guān)閉MySQL的TCP/IP連接方式，如果WEB服務(wù)器是以遠(yuǎn)程連接的方式訪問MySQL數(shù)據(jù)庫服務(wù)器則不要開啟該選項！否則將無法正常連接！ 
table_cache=1024 
innodb_additional_mem_pool_size=4M #默認(rèn)為2M 
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M #默認(rèn)為1M 
innodb_thread_concurrency=8 #你的服務(wù)器CPU有幾個就設(shè)置為幾。建議用默認(rèn)一般為8 
tmp_table_size=64M #默認(rèn)為16M，調(diào)到64-256最掛
thread_cache_size=120 query_cache_size=32M

14、數(shù)據(jù)類型優(yōu)化

對于 非負(fù)型 的數(shù)據(jù)（如自增ID、整型IP）來說，要優(yōu)先使用無符號整型 UNSIGNED 來存儲
遇到整數(shù)類型的字段可以用 INT 型
使用TIMESTAMP存儲時間
用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數(shù)
避免使用ENUM類型文章來源地址http://www.zghlxwxcb.cn/news/detail-759765.html

mysql客戶端參數(shù)

到了這里，關(guān)于【參數(shù)匯總】mysql服務(wù)端/客戶端常見優(yōu)化參數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！