- MySQL參數(shù)優(yōu)化測(cè)試
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='FGCD'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose
5. 數(shù)據(jù)庫(kù)實(shí)例層優(yōu)化細(xì)節(jié)：
5.1 參數(shù)優(yōu)化
Max_connections ★★★★★
（1）簡(jiǎn)介
Mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)請(qǐng)求量比較大，可以調(diào)高這個(gè)值，當(dāng)然這是要建立在機(jī)器能夠支撐的情況下，因?yàn)槿绻B接數(shù)越來(lái)越多，mysql會(huì)為每個(gè)連接提供緩沖區(qū)，就會(huì)開(kāi)銷的越多的內(nèi)存，所以需要適當(dāng)?shù)恼{(diào)整該值，不能隨便去提高設(shè)值。
（2）判斷依據(jù)
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
補(bǔ)充:
1.開(kāi)啟數(shù)據(jù)庫(kù)時(shí),我們可以臨時(shí)設(shè)置一個(gè)比較大的測(cè)試值
2.觀察show status like 'Max_used_connections';變化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections設(shè)置過(guò)低或者超過(guò)服務(wù)器的負(fù)載上限了，
低于10%則設(shè)置過(guò)大.
小問(wèn)題:
配置修改為1000,但是最終生效的是214個(gè)Connections? 為啥?
/etc/security/limits.conf
nofile 文件句柄數(shù)放開(kāi)限制 65535
http 409錯(cuò)誤,達(dá)到連接數(shù)上限
mysqlslap: Error when connecting to server: 1040 Too many connections
select concat("kill ",id,";")
from processlist
where COMMAND='sleep'
into outfile '/tmp/kill.sql';
back_log ★★★
（1）簡(jiǎn)介
mysql能暫存的連接數(shù)量，當(dāng)主要mysql線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求時(shí)候它就會(huì)起作用，如果mysql的連接數(shù)據(jù)達(dá)到max_connections時(shí)候，新來(lái)的請(qǐng)求將會(huì)被存在堆棧中，等待某一連接釋放資源，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過(guò)back_log，將不被授予連接資源。
back_log值指出在mysql暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在推棧中，只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接的時(shí)候需要增加它
（2）判斷依據(jù)
show full processlist
發(fā)現(xiàn)大量的待連接進(jìn)程時(shí)，就需要加大back_log或者加大max_connections的值
（3）修改方式舉例
vim /etc/my.cnf
back_log=100
wait_timeout和interactive_timeout ★★★★
（1）簡(jiǎn)介
wait_timeout：指的是mysql在關(guān)閉一個(gè)非交互的連接之前所要等待的秒數(shù)
interactive_timeout：指的是mysql在關(guān)閉一個(gè)交互的連接之前所需要等待的秒數(shù)，比如我們?cè)诮K端上進(jìn)行mysql管理，使用的即使交互的連接，這時(shí)候，如果沒(méi)有操作的時(shí)間超過(guò)了interactive_time設(shè)置的時(shí)間就會(huì)自動(dòng)的斷開(kāi)，默認(rèn)的是28800，可調(diào)優(yōu)為7200。
wait_timeout:如果設(shè)置太小，那么連接關(guān)閉的就很快，從而使一些持久的連接不起作用
（2）設(shè)置建議
如果設(shè)置太大，容易造成連接打開(kāi)時(shí)間過(guò)長(zhǎng)，在show processlist時(shí)候，能看到很多的連接 ，一般希望wait_timeout盡可能低
（3）修改方式舉例
wait_timeout=60
interactive_timeout=1200
長(zhǎng)連接的應(yīng)用，為了不去反復(fù)的回收和分配資源，降低額外的開(kāi)銷。
一般我們會(huì)將wait_timeout設(shè)定比較小，interactive_timeout要和應(yīng)用開(kāi)發(fā)人員溝通長(zhǎng)鏈接的應(yīng)用是否很多。如果他需要長(zhǎng)鏈接，那么這個(gè)值可以不需要調(diào)整。
另外還可以使用類外的參數(shù)彌補(bǔ)。
5.4 key_buffer_size ★★★★★
（1）簡(jiǎn)介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關(guān)
《2》臨時(shí)表的創(chuàng)建有關(guān)（多表鏈接、子查詢中、union）
在有以上查詢語(yǔ)句出現(xiàn)的時(shí)候，需要?jiǎng)?chuàng)建臨時(shí)表，用完之后會(huì)被丟棄
臨時(shí)表有兩種創(chuàng)建方式：
內(nèi)存中------->key_buffer_size
磁盤(pán)上------->ibdata1(5.6)
ibtmp1 (5.7）
（2）設(shè)置依據(jù)
可以使用檢查狀態(tài)值created_tmp_disk_tables得知：
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
公式:
Created_tmp_disk_tables/Created_tmp_disk_tables+ Created_tmp_tables <10%
看以下例子：
在調(diào)用mysqldump備份數(shù)據(jù)時(shí)，大概執(zhí)行步驟如下：
180322 17:39:33 7 Connect root@localhost on
7 Query /*!40100 SET @@SQL_MODE='' */
7 Init DB guo
7 Query SHOW TABLES LIKE 'guo'
7 Query LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query show create table `guo`
7 Query show fields from `guo`
7 Query show table status like 'guo'
7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query UNLOCK TABLES
7 Quit
其中，有一步是：show fields from guo。從slow query記錄的執(zhí)行計(jì)劃中，可以知道它也產(chǎn)生了 Tmp_table_on_disk。
所以說(shuō)，以上公式并不能真正反映到mysql里臨時(shí)表的利用率，有些情況下產(chǎn)生的 Tmp_table_on_disk 我們完全不用擔(dān)心，因此沒(méi)必要過(guò)分關(guān)注 Created_tmp_disk_tables，但如果它的值大的離譜的話，那就好好查一下，你的服務(wù)器到底都在執(zhí)行什么查詢了。
（3）配置方法
key_buffer_size=4M
5.5 query_cache_size ★★★
（1）簡(jiǎn)介：
查詢緩存簡(jiǎn)稱QC，使用查詢緩沖，mysql將查詢結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的select語(yǔ)句（區(qū)分大小寫(xiě)）,將直接從緩沖區(qū)中讀取結(jié)果。
SQL: select * from t1 where id=10; ---> hash---> xxxxx
id zhangsan
select * from t1 where id=11; ---->hash ----->yyy
小例子:
由于分區(qū)表,開(kāi)啟了查詢換存,并發(fā)查詢能力降低.
分區(qū)表不走查詢緩存,查詢緩存越大,并發(fā)能力.
1000+ 降低到 400+
5.6 max_connect_errors ★★★
max_connect_errors是一個(gè)mysql中與安全有關(guān)的計(jì)數(shù)器值，它負(fù)責(zé)阻止過(guò)多嘗試失敗的客戶端以防止暴力破解密碼等情況，當(dāng)超過(guò)指定次數(shù)，mysql服務(wù)器將禁止host的連接請(qǐng)求，直到mysql服務(wù)器重啟或通過(guò)flush hosts命令清空此host的相關(guān)信息 max_connect_errors的值與性能并無(wú)太大關(guān)系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容
max_connect_errors=2000
5.7 sort_buffer_size ★★★
（1）簡(jiǎn)介：
每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速
ORDER BY
GROUP BY
distinct
union
（2）配置依據(jù)
Sort_Buffer_Size并不是越大越好，由于是connection級(jí)的參數(shù)，過(guò)大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)內(nèi)存資源。
列如：500個(gè)連接將會(huì)消耗500*sort_buffer_size（2M）=1G內(nèi)存
（3）配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M
5.8 max_allowed_packet *****
（1）簡(jiǎn)介：
mysql根據(jù)配置文件會(huì)限制，server接受的數(shù)據(jù)包大小。
（2）配置依據(jù)：
有時(shí)候大的插入和更新會(huì)受max_allowed_packet參數(shù)限制，導(dǎo)致寫(xiě)入或者更新失敗，更大值是1GB，必須設(shè)置1024的倍數(shù)
（3）配置方法：
max_allowed_packet=32M
5.9 join_buffer_size ****
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表間關(guān)聯(lián)緩存的大小，和sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。
盡量在SQL與方面進(jìn)行優(yōu)化，效果較為明顯。
優(yōu)化的方法：在on條件列加索引，至少應(yīng)當(dāng)是有MUL索引
替代方案:
1. 選擇結(jié)果集少的作為驅(qū)動(dòng)表
2. 多表連接時(shí),加入合適的where條件和索引
BNL BKA
a
id name aid
1 a 11
2 b 22
3 c 33
4 d 44
5 e 55
b
id addr tel
11 bj 110
22 sh 120
33 tj 119
44 cq 112
55 sz 114
5.10 thread_cache_size *****
(1)簡(jiǎn)介
服務(wù)器線程緩存，這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開(kāi)連接時(shí),那么客戶端的線程將被放到緩存中以響應(yīng)下一個(gè)客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限),如果線程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能.
（2）配置依據(jù)
通過(guò)比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。
設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8，2GB配置為16，3GB配置為32，4GB或更高內(nèi)存，可配置更大。
服務(wù)器處理此客戶的線程將會(huì)緩存起來(lái)以響應(yīng)下一個(gè)客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限)
試圖連接到MySQL(不管是否連接成功)的連接數(shù)
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_cached :代表當(dāng)前此時(shí)此刻線程緩存中有多少空閑線程。
Threads_connected:代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€(gè)連接就需要一個(gè)線程，所以也可以看成當(dāng)前被使用的線程數(shù)。
Threads_created:代表從最近一次服務(wù)啟動(dòng)，已創(chuàng)建線程的數(shù)量，如果發(fā)現(xiàn)Threads_created值過(guò)大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗cpu SYS資源，可以適當(dāng)增加配置文件中thread_cache_size值。
Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù)。并不是代表正在使用的線程數(shù)，有時(shí)候連接已建立，但是連接處于sleep狀態(tài)。
(3)配置方法：
thread_cache_size=32
整理：
Threads_created ：一般在架構(gòu)設(shè)計(jì)階段，會(huì)設(shè)置一個(gè)測(cè)試值，做壓力測(cè)試。
結(jié)合zabbix監(jiān)控，看一段時(shí)間內(nèi)此狀態(tài)的變化。
如果在一段時(shí)間內(nèi)，Threads_created趨于平穩(wěn)，說(shuō)明對(duì)應(yīng)參數(shù)設(shè)定是OK。
如果一直陡峭的增長(zhǎng)，或者出現(xiàn)大量峰值，那么繼續(xù)增加此值的大小，在系統(tǒng)資源夠用的情況下（內(nèi)存）
5.11 innodb_buffer_pool_size *****
（1）簡(jiǎn)介
對(duì)于InnoDB表來(lái)說(shuō)，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。
（2）配置依據(jù)：
InnoDB使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。
對(duì)于單獨(dú)的MySQL數(shù)據(jù)庫(kù)服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%,一般我們建議不要超過(guò)物理內(nèi)存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M
show engine innodb status \G
5.12 innodb_flush_log_at_trx_commit ******
（1）簡(jiǎn)介
主要控制了innodb將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0、1、2三個(gè)。
0，表示當(dāng)事務(wù)提交時(shí)，不做日志寫(xiě)入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)一次；
1，
每次事務(wù)的提交都會(huì)引起redo日志文件寫(xiě)入、flush磁盤(pán)的操作，確保了事務(wù)的ACID；
2，每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作,但每秒鐘完成一次flush磁盤(pán)操作。
（2）配置依據(jù)
實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時(shí)插入10000條記錄只需要2秒，設(shè)置為0時(shí)只需要1秒，而設(shè)置為1時(shí)則需要229秒。因此，MySQL手冊(cè)也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。
根據(jù)MySQL官方文檔，在允許丟失最近部分事務(wù)的危險(xiǎn)的前提下，可以把該值設(shè)為0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
雙1標(biāo)準(zhǔn)中的一個(gè)1
5.13 innodb_thread_concurrency ***
（1）簡(jiǎn)介
此參數(shù)用來(lái)設(shè)置innodb線程的并發(fā)數(shù)量，默認(rèn)值為0表示不限制。
（2）配置依據(jù)
在官方doc上，對(duì)于innodb_thread_concurrency的使用，也給出了一些建議，如下：
如果一個(gè)工作負(fù)載中，并發(fā)用戶線程的數(shù)量小于64，建議設(shè)置innodb_thread_concurrency=0；
如果工作負(fù)載一直較為嚴(yán)重甚至偶爾達(dá)到頂峰，建議先設(shè)置innodb_thread_concurrency=128，
并通過(guò)不斷的降低這個(gè)參數(shù)，96, 80, 64等等，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)，
例如，假設(shè)系統(tǒng)通常有40到50個(gè)用戶，但定期的數(shù)量增加至60，70，甚至200。你會(huì)發(fā)現(xiàn)，
性能在80個(gè)并發(fā)用戶設(shè)置時(shí)表現(xiàn)穩(wěn)定，如果高于這個(gè)數(shù)，性能反而下降。在這種情況下，
建議設(shè)置innodb_thread_concurrency參數(shù)為80，以避免影響性能。
如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶線程使用的虛擬CPU更多（比如20個(gè)虛擬CPU），
建議通過(guò)設(shè)置innodb_thread_concurrency 參數(shù)為這個(gè)值（也可能更低，這取決于性能體現(xiàn)），
如果你的目標(biāo)是將MySQL與其他應(yīng)用隔離，你可以l考慮綁定mysqld進(jìn)程到專有的虛擬CPU。
但是需 要注意的是，這種綁定，在myslqd進(jìn)程一直不是很忙的情況下，可能會(huì)導(dǎo)致非最優(yōu)的硬件使用率。在這種情況下，
你可能會(huì)設(shè)置mysqld進(jìn)程綁定的虛擬 CPU，允許其他應(yīng)用程序使用虛擬CPU的一部分或全部。
在某些情況下，最佳的innodb_thread_concurrency參數(shù)設(shè)置可以比虛擬CPU的數(shù)量小。
定期檢測(cè)和分析系統(tǒng)，負(fù)載量、用戶數(shù)或者工作環(huán)境的改變可能都需要對(duì)innodb_thread_concurrency參數(shù)的設(shè)置進(jìn)行調(diào)整。
128 -----> top cpu
設(shè)置標(biāo)準(zhǔn)：
1、當(dāng)前系統(tǒng)cpu使用情況，均不均勻
top
2、當(dāng)前的連接數(shù)，有沒(méi)有達(dá)到頂峰
show status like 'threads_%';
show processlist;
（3）配置方法：
innodb_thread_concurrency=8
方法:
1. 看top ,觀察每個(gè)cpu的各自的負(fù)載情況
2. 發(fā)現(xiàn)不平均,先設(shè)置參數(shù)為cpu個(gè)數(shù),然后不斷增加(一倍)這個(gè)數(shù)值
3. 一直觀察top狀態(tài),直到達(dá)到比較均勻時(shí),說(shuō)明已經(jīng)到位了.
5.14 innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，對(duì)于較大的事務(wù)，可以增大緩存大小。
innodb_log_buffer_size=128M
設(shè)定依據(jù)：
1、大事務(wù)： 存儲(chǔ)過(guò)程調(diào)用 CALL
2、多事務(wù)
5.15 innodb_log_file_size = 256M *****
設(shè)置 ib_logfile0 ib_logfile1
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設(shè)置可以提高性能.
innodb_log_file_size = 256M
innodb_log_files_in_group = 3-5 *****
為提高性能，MySQL可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。推薦設(shè)置為3
read_buffer_size = 1M **
MySql讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)，MySql會(huì)為它分配一段內(nèi)存緩沖區(qū)。如果對(duì)表的順序掃描請(qǐng)求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過(guò)增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和 sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享
read_rnd_buffer_size = 1M **
MySql的隨機(jī)讀（查詢操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，MySql會(huì)首先掃描一遍該緩沖，以避免磁盤(pán)搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySql會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開(kāi)銷過(guò)大。
注：順序讀是指根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找實(shí)際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同，因此訪問(wèn)方式是隨機(jī)的。
bulk_insert_buffer_size = 8M **
批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認(rèn)為8M
change buffer
binary log *****
log-bin=/data/mysql-bin
binlog_cache_size = 2M
//為每個(gè)session 分配的內(nèi)存，在事務(wù)過(guò)程中用來(lái)存儲(chǔ)二進(jìn)制日志的緩存, 提高記錄bin-log的效率。沒(méi)有什么大事務(wù)，dml也不是很頻繁的情況下可以設(shè)置小一點(diǎn)，如果事務(wù)大而且多，dml操作也頻繁，則可以適當(dāng)?shù)恼{(diào)大一點(diǎn)。前者建議是--1M，后者建議是：即 2--4M
max_binlog_cache_size = 8M
//表示的是binlog 能夠使用的最大cache 內(nèi)存大小
max_binlog_size= 512M
//指定binlog日志文件的大小，如果當(dāng)前的日志大小達(dá)到max_binlog_size，還會(huì)自動(dòng)創(chuàng)建新的二進(jìn)制日志。你不能將該變量設(shè)置為大于1GB或小于4096字節(jié)。默認(rèn)值是1GB。在導(dǎo)入大容量的sql文件時(shí)，建議關(guān)閉sql_log_bin，否則硬盤(pán)扛不住，而且建議定期做刪除。
expire_logs_days = 7
//定義了mysql清除過(guò)期日志的時(shí)間。
二進(jìn)制日志自動(dòng)刪除的天數(shù)。默認(rèn)值為0,表示“沒(méi)有自動(dòng)刪除”。
binlog_format=row
sync_binlog=1
雙1標(biāo)準(zhǔn)(基于安全的控制)：
sync_binlog=1
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';
安全參數(shù) *****
Innodb_flush_method=(O_DIRECT, fsync)
1、fsync ：
（1）在數(shù)據(jù)頁(yè)需要持久化時(shí)，首先將數(shù)據(jù)寫(xiě)入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P(pán)
（2）在redo buffuer需要持久化時(shí)，首先將數(shù)據(jù)寫(xiě)入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P(pán)
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫(xiě)入磁盤(pán)
2、 Innodb_flush_method=O_DIRECT
（1）在數(shù)據(jù)頁(yè)需要持久化時(shí)，直接寫(xiě)入磁盤(pán)
（2）在redo buffuer需要持久化時(shí)，首先將數(shù)據(jù)寫(xiě)入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P(pán)
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫(xiě)入磁盤(pán)
一般情況下，我們更偏向于安全。
“雙一標(biāo)準(zhǔn)”
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT
偏向性能
innodb_flush_log_at_trx_commit=0
sync_binlog=0
innodb_flush_method=fsync
- 參數(shù)優(yōu)化結(jié)果
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
再次壓力測(cè)試 ：
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='FGCD'" engine=innodb \
--number-of-queries=2000 -uroot -p -verbose
7. 鎖的監(jiān)控及處理★★★★★★
7.1 鎖等待模擬
概念：
MDL : metadata lock
元數(shù)據(jù),數(shù)據(jù)字典鎖?
存儲(chǔ)位置(5.7):
frm
ibdata1
mysql
p_s
sys
information_schema
什么時(shí)候觸發(fā)?
DDL ----> MDL
備份 ----> FTWRL
手工觸發(fā) FTWRL
Table Lock
DDL 操作
lock table
Record Lock
update
delete
insert
select for update /lock in shared mode
GAP Lock
update t1 set salary=3000 where salary<3000;
1000
1999
1800
1500
insert into 1700
Next Lock
X
IX
S
IS
=============================================================
tx1:
USE oldboy
UPDATE t_100w SET k1='av' WHERE id=10;
## tx2:
USE oldboy
UPDATE t_100w SET k1='az' WHERE id=10;
監(jiān)控鎖狀態(tài):
1. 看有沒(méi)有鎖等待
SHOW STATUS LIKE 'innodb_row_lock%';
2. 查看哪個(gè)事務(wù)在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事務(wù)ID號(hào)
trx_state : 當(dāng)前事務(wù)的狀態(tài)
trx_mysql_thread_id:連接層的,連接線程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 當(dāng)前被阻塞的操作(一般是要丟給開(kāi)發(fā)的)
7.3.查看鎖源,誰(shuí)鎖的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被鎖的和鎖定它的之間關(guān)系
locked_table : 哪張表出現(xiàn)的等待
waiting_trx_id: 等待的事務(wù)(與上個(gè)視圖trx_id 對(duì)應(yīng))
waiting_pid : 等待的線程號(hào)(與上個(gè)視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務(wù)ID
blocking_pid : 鎖源的線程號(hào)
7.4. 找到鎖源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41
7.5. 找到鎖源的SQL語(yǔ)句
-- 當(dāng)前在執(zhí)行的語(yǔ)句
SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;
-- 執(zhí)行語(yǔ)句的歷史
SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;
得出結(jié)果,丟給開(kāi)發(fā)
表信息
被阻塞的
鎖源SQL
練習(xí):
一鍵獲得以上信息,請(qǐng)寫(xiě)出具體的SQL語(yǔ)句
7.6 優(yōu)化項(xiàng)目:鎖的監(jiān)控及處理
背景:
硬件環(huán)境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10
在例行巡檢時(shí),發(fā)現(xiàn)9-11點(diǎn)時(shí)間段的CPU壓力非常高(80-90%)項(xiàng)目的職責(zé)
2.1 通過(guò)top詳細(xì)排查,發(fā)現(xiàn)mysqld進(jìn)程占比達(dá)到了700-800%
2.2 其中有量的CPU是被用作的SYS和WAIT,us處于正常
2.3 懷疑是MySQL 鎖 或者SQL語(yǔ)句出了問(wèn)題
2.4 經(jīng)過(guò)排查slowlog及鎖等待情況,發(fā)現(xiàn)有大量鎖等待及少量慢語(yǔ)句
(1) pt-query-diagest 查看慢日志
(2) 鎖等待有沒(méi)有?
db03 [(none)]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
情況一:
有100多個(gè)current_waits,說(shuō)明當(dāng)前很多鎖等待情況
情況二:
1000多個(gè)lock_waits,說(shuō)明歷史上發(fā)生過(guò)的鎖等待很多
2.5 查看那個(gè)事務(wù)在等待(被阻塞了)
2.6 查看鎖源事務(wù)信息(誰(shuí)鎖的我)
2.7 找到鎖源的thread_id
2.8 找到鎖源的SQL語(yǔ)句
- 找到語(yǔ)句之后,和應(yīng)用開(kāi)發(fā)人員進(jìn)行協(xié)商
(1)
開(kāi)發(fā)人員描述,此語(yǔ)句是事務(wù)掛起導(dǎo)致
我們提出建議是臨時(shí)kill 會(huì)話,最終解決問(wèn)題
(2)
開(kāi)發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問(wèn)題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
臨時(shí)解決方案,將阻塞事務(wù)的會(huì)話kill掉.
最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項(xiàng)目結(jié)果:
經(jīng)過(guò)排查處理,鎖等待的個(gè)數(shù)減少80%.解決了CPU持續(xù)峰值的問(wèn)題.
鎖監(jiān)控涉及到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
7.7 死鎖監(jiān)控
show engine innodb status\G ----> 最后一次放生的死鎖信息.
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
主從延時(shí)?
- 主庫(kù)原因:
(1) binlog寫(xiě)入不及時(shí).
sync_binlog=1?
IO太慢. ---> SSD
(2) 傳統(tǒng)(無(wú)GTID)
傳輸日志binlog是串行工作的.
5.6 以后加入了GTID,并行傳輸.
(3) 其他原因
網(wǎng)絡(luò)慢
主庫(kù)壓力大
從庫(kù)太多
主從配置不一致
主從參數(shù)
主從版本 - 從庫(kù)原因:
SQL線程串行工作.
5.7 中加入Logical_clock 邏輯時(shí)鐘,可以實(shí)現(xiàn)SQL線程并行回放relaylog
8. 主從優(yōu)化：
5.7 從庫(kù)多線程MTS
基本要求:
5.7以上的版本(忘記小版本)
必須開(kāi)啟GTID
binlog必須是row模式
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心數(shù)作為標(biāo)準(zhǔn)
CHANGE MASTER TO
MASTER_HOST='10.0.0.128',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_AUTO_POSITION=1;
start slave;
擴(kuò)展PT工具的應(yīng)用:
- pt工具安裝
[root@master ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm - 常用工具使用介紹
2.1 pt-archiver 歸檔表
重要參數(shù)
--limit 100 每次取100行數(shù)據(jù)用pt-archive處理
--txn-size 100 設(shè)置100行為一個(gè)事務(wù)提交一次，
--where 'id<3000' 設(shè)置操作條件
--progress 5000 每處理5000行輸出一次處理信息
--statistics 輸出執(zhí)行過(guò)程及最后的操作統(tǒng)計(jì)。（只要不加上--quiet，默認(rèn)情況下pt- archive都會(huì)輸出執(zhí)行過(guò)程的）
--charset=UTF8 指定字符集為UTF8—這個(gè)最后加上不然可能出現(xiàn)亂碼。
--bulk-delete 批量刪除source上的舊數(shù)據(jù)(例如每次1000行的批量刪除操作)
注意: 需要?dú)w檔表中至少有一個(gè)索引,做好是where條件列有索引
使用案例：
1.歸檔到數(shù)據(jù)庫(kù)
db01 [test]>create table test1 like t100w;
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --dest h=10.0.0.51,D=test,t=test1,u=oldguo,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics
2.只清理數(shù)據(jù)
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset
3.只把數(shù)據(jù)導(dǎo)出到外部文件，但是不刪除源表里的數(shù)據(jù)
pt-archiver --source h=10.0.0.11,D=world,t=city,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"
2.2 pt-osc
pt-osc工作流程：
1、檢查更改表是否有主鍵或唯一索引，是否有觸發(fā)器
2、檢查修改表的表結(jié)構(gòu)，創(chuàng)建一個(gè)臨時(shí)表，在新表上執(zhí)行ALTER TABLE語(yǔ)句
create table bak like t1;
alter table bak add telnum char(11) not null;
3、在源表上創(chuàng)建三個(gè)觸發(fā)器分別對(duì)于INSERT UPDATE DELETE操作
create trigger
a
b
c
4、從源表拷貝數(shù)據(jù)到臨時(shí)表，在拷貝過(guò)程中，對(duì)源表的更新操作會(huì)寫(xiě)入到新建表中
insert into bak select * from t1
5、將臨時(shí)表和源表rename（需要元數(shù)據(jù)修改鎖，需要短時(shí)間鎖表）
6、刪除源表和觸發(fā)器，完成表結(jié)構(gòu)的修改。
pt-osc工具限制
1、源表必須有主鍵或唯一索引，如果沒(méi)有工具將停止工作
2、如果線上的復(fù)制環(huán)境過(guò)濾器操作過(guò)于復(fù)雜，工具將無(wú)法工作
3、如果開(kāi)啟復(fù)制延遲檢查，但主從延遲時(shí)，工具將暫停數(shù)據(jù)拷貝工作
4、如果開(kāi)啟主服務(wù)器負(fù)載檢查，但主服務(wù)器負(fù)載較高時(shí)，工具將暫停操作
5、當(dāng)表使用外鍵時(shí)，如果未使用--alter-foreign-keys-method參數(shù)，工具將無(wú)法執(zhí)行
6、只支持Innodb存儲(chǔ)引擎表，且要求服務(wù)器上有該表1倍以上的空閑空間。
pt-osc之a(chǎn)lter語(yǔ)句限制
1、不需要包含alter table關(guān)鍵字，可以包含多個(gè)修改操作，使用逗號(hào)分開(kāi)，如"drop clolumn c1, add column c2 int"
2、不支持rename語(yǔ)句來(lái)對(duì)表進(jìn)行重命名操作
3、不支持對(duì)索引進(jìn)行重命名操作
4、如果刪除外鍵，需要對(duì)外鍵名加下劃線，如刪除外鍵fk_uid, 修改語(yǔ)句為"DROP FOREIGN KEY _fk_uid"
pt-osc之命令模板
--execute表示執(zhí)行
--dry-run表示只進(jìn)行模擬測(cè)試
表名只能使用參數(shù)t來(lái)設(shè)置，沒(méi)有長(zhǎng)參數(shù)
pt-online-schema-change \
--host="127.0.0.1" \
--port=3358 \
--user="root" \
--password="root@root" \
--charset="utf8" \
--max-lag=10 \
--check-salve-lag='xxx.xxx.xxx.xxx' \
--recursion-method="hosts" \
--check-interval=2 \
--database="testdb1" \
t="tb001" \
--alter="add column c4 int" \
--execute
例子：
pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add column age int default 0" D=test,t=t1 --print --execute
pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add index idx(name)" D=test,t=t1 --print --execute
2.3 pt-table-checksum
2.3.1 創(chuàng)建數(shù)據(jù)庫(kù)
Create database pt CHARACTER SET utf8;
創(chuàng)建用戶checksum并授權(quán)
GRANT ALL ON *.* TO 'checksum'@'10.0.0.%' IDENTIFIED BY 'checksum';
flush privileges;
2.3.2 參數(shù):
--[no]check-replication-filters：是否檢查復(fù)制的過(guò)濾器，默認(rèn)是yes，建議啟用不檢查模式。
--databases | -d：指定需要被檢查的數(shù)據(jù)庫(kù)，多個(gè)庫(kù)之間可以用逗號(hào)分隔。
--[no]check-binlog-format：是否檢查binlog文件的格式，默認(rèn)值yes。建議開(kāi)啟不檢查。因?yàn)樵谀J(rèn)的row格式下會(huì)出錯(cuò)。
--replicate`：把checksum的信息寫(xiě)入到指定表中。
--replicate-check-only：只顯示不同步信息
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,u=checksum,p=checksum,P=3306
#!/bin/bash
date >> /root/db/checksum.log
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test --tables t1 -u'checksum' -p'checksum' -h'10.0.0.51' >> /tmp/checksum.log
date >> /root/db/checksum.log
2.4 pt-table-sync
主要參數(shù)介紹
--replicate ：指定通過(guò)pt-table-checksum得到的表.
--databases : 指定執(zhí)行同步的數(shù)據(jù)庫(kù)。
--tables ：指定執(zhí)行同步的表，多個(gè)用逗號(hào)隔開(kāi)。
--sync-to-master ：指定一個(gè)DSN，即從的IP，他會(huì)通過(guò)show processlist或show slave status 去自動(dòng)的找主。
h= ：服務(wù)器地址，命令里有2個(gè)ip，第一次出現(xiàn)的是Master的地址，第2次是Slave的地址。
u= ：帳號(hào)。
p= ：密碼。
--print ：打印，但不執(zhí)行命令。
--execute ：執(zhí)行命令。
pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --print
pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --execute
2.5 mysql死鎖監(jiān)測(cè)
pt-deadlock-logger h='127.0.0.1' --user=root --password=123456
2.6 主鍵沖突檢查
pt-duplicate-key-checker --database=world h='127.0.0.1' --user=root --password=123456
2.7 pt-kill 語(yǔ)句
常用參數(shù)說(shuō)明
--daemonize 放在后臺(tái)以守護(hù)進(jìn)程的形式運(yùn)行；
--interval 多久運(yùn)行一次，單位可以是s,m,h，d等默認(rèn)是s –不加這個(gè)默認(rèn)是5秒
--victims 默認(rèn)是oldest,只殺最古老的查詢。這是防止被查殺是不是真的長(zhǎng)時(shí)間運(yùn)行的查詢，他們只是長(zhǎng)期等待 這種種匹配按時(shí)間查詢，殺死一個(gè)時(shí)間最高值。
--all 殺掉所有滿足的線程
--kill-query 只殺掉連接執(zhí)行的語(yǔ)句，但是線程不會(huì)被終止
--print 打印滿足條件的語(yǔ)句
--busy-time 批次查詢已運(yùn)行的時(shí)間超過(guò)這個(gè)時(shí)間的線程；
--idle-time 殺掉sleep 空閑了多少時(shí)間的連接線程，必須在--match-command sleep時(shí)才有效—也就是匹配使用 -- –match-command 匹配相關(guān)的語(yǔ)句。
----ignore-command 忽略相關(guān)的匹配。 這兩個(gè)搭配使用一定是ignore-commandd在前 match-command在后，
--match-db cdelzone 匹配哪個(gè)庫(kù)
command有：Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump
例子：
### 殺掉空閑鏈接sleep 5秒的 SQL 并把日志放到/home/pt-kill.log文件中
/usr/bin/pt-kill --user=用戶名 --password=密碼 --match-command Sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
### 查詢SELECT 超過(guò)1分鐘
/usr/bin/pt-kill --user=用戶名 --password=密碼 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
### Kill掉 select IFNULl.*語(yǔ)句開(kāi)頭的SQL
pt-kill --user=用戶名 --password=密碼 --victims all --busy-time=0 --match-info="select IFNULl.*" --interval 1 -S /tmp/mysqld.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
### kill掉state Locked
/usr/bin/pt-kill --user=用戶名 --password=密碼 --victims all --match-state='Locked' --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
### kill掉 a庫(kù)，web為10.0.0.11的鏈接
pt-kill --user=用戶名 --password=密碼 --victims all --match-db='a' --match-host='10.0.0.11' --kill --daemonize --interval 10 -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print-log=/tmp/pt-kill.log &
### 指定哪個(gè)用戶kill
pt-kill --user=用戶名 --password=密碼 --victims all --match-user='root' --kill --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
### 查詢SELECT 超過(guò)1分鐘路
pt-kill --user=用戶名 --password=密碼 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
### kill掉 command query | Execute
pt-kill --user=用戶名 --password=密碼 --victims all --match-command= "query|Execute" --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
2.8 其他
pt-find ---找出幾天之前建立的表
pt-slave-restart -----主從報(bào)錯(cuò)，跳過(guò)報(bào)錯(cuò)
pt-summary ---整個(gè)系統(tǒng)的的概述
pt-mysql-summary ---MySQL的表述，包括配置文件的描述
pt-duplicate-key-checker ---檢查數(shù)據(jù)庫(kù)重復(fù)索引
