MySQL全面優(yōu)化
1.優(yōu)化哲學(xué)
1.1 為什么優(yōu)化？
為了獲得成就感?
為了證實(shí)比系統(tǒng)設(shè)計(jì)者更懂?dāng)?shù)據(jù)庫(kù)?
為了從優(yōu)化成果來(lái)證實(shí)優(yōu)化者更有價(jià)值?
但通常事實(shí)證實(shí)的結(jié)果往往會(huì)和您期待相反广料！
優(yōu)化有風(fēng)險(xiǎn)需了，涉足需謹(jǐn)慎漾脂！
1.2 優(yōu)化風(fēng)險(xiǎn)
優(yōu)化不總是對(duì)一個(gè)單純的環(huán)境進(jìn)行！還很可能是一個(gè)復(fù)雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來(lái)就有很大的風(fēng)險(xiǎn)显拳，只不過(guò)你沒(méi)能力意識(shí)到和預(yù)見(jiàn)到括细！
任何的技術(shù)可以解決一個(gè)問(wèn)題伪很，但必然存在帶來(lái)一個(gè)問(wèn)題的風(fēng)險(xiǎn)！
對(duì)于優(yōu)化來(lái)說(shuō)解決問(wèn)題而帶來(lái)的問(wèn)題控制在可接受的范圍內(nèi)才是有成果奋单。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失旓笔浴！
穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要览濒！
優(yōu)化不可避免涉及到變更呆盖，變更就有風(fēng)險(xiǎn)！
優(yōu)化使性能變好匾七，維持和變差是等概率事件絮短！
優(yōu)化不能只是數(shù)據(jù)庫(kù)管理員擔(dān)當(dāng)風(fēng)險(xiǎn)，但會(huì)所有的人分享優(yōu)化成果昨忆！
所以?xún)?yōu)化工作是由業(yè)務(wù)需要驅(qū)使的６∑怠！邑贴！
1.3 誰(shuí)參與優(yōu)化
數(shù)據(jù)庫(kù)管理員
業(yè)務(wù)部門(mén)代表
應(yīng)用程序架構(gòu)師
應(yīng)用程序設(shè)計(jì)人員
應(yīng)用程序開(kāi)發(fā)人員
硬件及系統(tǒng)管理員
存儲(chǔ)管理員
1.4 優(yōu)化方向
安全優(yōu)化（業(yè)務(wù)持續(xù)性）
性能優(yōu)化（業(yè)務(wù)高效性）
1.5 優(yōu)化的范圍及思路
優(yōu)化范圍：
存儲(chǔ)席里、主機(jī)和操作系統(tǒng):
主機(jī)架構(gòu)穩(wěn)定性
I/O規(guī)劃及配置
Swap
OS內(nèi)核參數(shù)
網(wǎng)絡(luò)問(wèn)題
應(yīng)用程序:（Index，lock拢驾，session）
應(yīng)用程序穩(wěn)定性和性能
SQL語(yǔ)句性能
串行訪問(wèn)資源
性能欠佳會(huì)話(huà)管理
數(shù)據(jù)庫(kù)優(yōu)化:（內(nèi)存奖磁、數(shù)據(jù)庫(kù)設(shè)計(jì)、參數(shù)）
內(nèi)存
數(shù)據(jù)庫(kù)結(jié)構(gòu)(物理&邏輯)
實(shí)例配置
優(yōu)化效果和成本的評(píng)估：
2. 優(yōu)化工具的使用
2.1 系統(tǒng)層面的
2.1.1 CPU
top
top -H
top -H -p pid
top -H -p 9618
CPU使用情況:
%Cpu(s):
0.1 us : 用戶(hù)進(jìn)程占用CPU時(shí)間占比
我們覺(jué)得越高越好,不要滿(mǎn)負(fù)荷
0.2 sy : 系統(tǒng)本身和內(nèi)核工作CPU時(shí)間占比
資源管理,維護(hù),分配,回收等等.
并發(fā)連接過(guò)高 .
鎖爭(zhēng)用
99.8 id : 空閑
0.0 wa : 等待時(shí)CPU時(shí)間占比
IO吞吐 有問(wèn)題 : 硬件,鏈路,RAID
IO/PS 峰值 : 大量隨機(jī)IO ,索引設(shè)計(jì)不合理,多表連接優(yōu)化不到位,大量子查詢(xún),大事務(wù).
鎖爭(zhēng)用
2.1.2 MEM
KiB Mem : 4028432 total, 3774464 free, 116240 used, 137728 buff/cache
1.2.1 名稱(chēng)介紹
total :總內(nèi)存大小
free ：空閑的
used ：在使用的
buff/cache ：緩沖區(qū) 和 緩存
2.1.3 內(nèi)存管理子系統(tǒng)：
slab Allocator
buddy system
程序=指令+數(shù)據(jù)
對(duì)于page cache來(lái)講(OS buffer)
- 內(nèi)存的可用空間的計(jì)算 free +buffer cache
- 內(nèi)存回收(buffer)的方式:
(1) 寫(xiě)入磁盤(pán)
(2) swap
對(duì)于數(shù)據(jù)庫(kù)來(lái)講：需要將swap屏蔽掉
2.1.4 swap
KiB Swap: 2097148 total, 2097148 free, 0 used. 3701464 avail Mem
Linux 6操作系統(tǒng)繁疤，默認(rèn)回收策略（buffer cache）咖为，不立即回收策略
內(nèi)存使用達(dá)到100%-60%時(shí)候秕狰，40% 會(huì)使用swap
Linux 7操作系統(tǒng)
內(nèi)存使用達(dá)到100%-30%（70%）時(shí)候，才會(huì)使用swap
cat /proc/sys/vm/swappiness
30
echo 0 >/proc/sys/vm/swappiness 的內(nèi)容改成0（臨時(shí)）
vim /etc/sysctl.conf
添加:
vm.swappiness=0
sysctl -p
2.2. iostat 命令
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
現(xiàn)象說(shuō)明
- IO 高 cpu us 也高,屬于正吃耆荆現(xiàn)象
- CPU us高 IO很低 ,MySQL 不在做增刪改查,有可能是存儲(chǔ)過(guò)程,函數(shù),排序,分組,多表連接
- Wait,SYS 高 , IO低: IO出問(wèn)題了,鎖等待過(guò)多的幾率比較大.
IOPS：每秒磁盤(pán)最多能夠發(fā)生的IO次數(shù)鸣哀，這是個(gè)定值
頻繁小事務(wù),IOPS很高,達(dá)到閾值,可能IO吞吐量沒(méi)超過(guò)IO最大吞吐量.無(wú)法新的IO了
存儲(chǔ)規(guī)劃有問(wèn)題.
2.3 數(shù)據(jù)庫(kù)優(yōu)化工具
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
slowlog
擴(kuò)展類(lèi)深度優(yōu)化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
3. 優(yōu)化思路分解
3.1 硬件優(yōu)化
3.1.1 主機(jī)
(1) 真實(shí)的硬件（PC Server）:
DELL R系列 彪杉，華為靡狞，浪潮，HP斋日，聯(lián)想 ,曙光
(2)云產(chǎn)品：
ECS饰恕、
數(shù)據(jù)庫(kù)RDS挠羔、DRDS\Polardb , TDSQL
(3) IBM 小型機(jī):
P6 570 595
P7 720 750 780
P8
3.1.2 CPU根據(jù)數(shù)據(jù)庫(kù)類(lèi)型
OLTP 在線(xiàn)事務(wù)處理系統(tǒng).
OLAP 數(shù)據(jù)分析,處理
IO密集型：線(xiàn)上系統(tǒng)，OLTP主要是IO密集型的業(yè)務(wù)埋嵌，高并發(fā)
CPU密集型：數(shù)據(jù)分析數(shù)據(jù)處理破加，OLAP，cpu密集型的莉恼，需要CPU高計(jì)算能力（i系列拌喉，IBM power系列）
CPU密集型： I 系列的，主頻很高俐银，核心少
IO密集型： E系列（至強(qiáng)）尿背，主頻相對(duì)低，核心數(shù)量多
3.1.3 內(nèi)存
建議2-8倍cpu核心數(shù)量 （ECC）
3.1.4 磁盤(pán)選擇
SATA-III SAS Fc SSD（sata） pci-e ssd Flash
主機(jī) RAID卡的BBU(Battery Backup Unit)關(guān)閉
3.1.3 存儲(chǔ)設(shè)備
根據(jù)存儲(chǔ)數(shù)據(jù)種類(lèi)的不同捶惜，選擇不同的存儲(chǔ)設(shè)備
配置合理的RAID級(jí)別(raid5田藐、raid10、熱備盤(pán))
r0 :條帶化 ,性能高
r1 :鏡像吱七，安全
r5 :校驗(yàn)+條帶化汽久，安全較高+性能較高（讀），寫(xiě)性能較低 （適合于讀多寫(xiě)少）
r10：安全+性能都很高踊餐，最少四塊盤(pán)景醇，浪費(fèi)一半的空間（高IO要求）
3.1.4 網(wǎng)絡(luò)
1、硬件買(mǎi)好的（單卡單口）
2吝岭、網(wǎng)卡綁定（bonding）三痰，交換機(jī)堆疊
以上問(wèn)題，提前規(guī)避掉窜管。
3.2 操作系統(tǒng)優(yōu)化
3.2.1 Swap調(diào)整
echo 0 >/proc/sys/vm/swappiness的內(nèi)容改成0（臨時(shí)）散劫，
vim /etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p
擴(kuò)展:內(nèi)存管理
三個(gè)區(qū)域 :
常駐內(nèi)存集: 程序運(yùn)行服務(wù)的
頁(yè)緩存page cache:
FREE list
LRU list
匿名頁(yè):Slab Allocator
將 page cache 劃分成了好多條鏈狀結(jié)構(gòu).
類(lèi)型一:
Free list(空閑的)
==================================================
| |
o o
類(lèi)型二 :
LRU list (正在被使用的)
冷================================================熱
i i
0 0
3. buddy system (內(nèi)存伙伴系統(tǒng))
內(nèi)存回收和重利用
cat /proc/sys/vm/swappiness ====> 0
free list 上為 0;
buddy system 進(jìn)行內(nèi)存回收和重利用
- 優(yōu)先釋放 Cache(負(fù)責(zé)查詢(xún)類(lèi)的內(nèi)存結(jié)構(gòu)),從冷到熱進(jìn)行釋放.
- Cache沒(méi)法釋放時(shí),會(huì)根據(jù)buffer從冷導(dǎo)熱,進(jìn)行回收和重用內(nèi)存
- 所有可被釋放 buffer 或cache,已經(jīng)全部被回收重用了,還是內(nèi)存緊缺的話(huà)
此時(shí),swap還是會(huì)被使用.
內(nèi)存泄露問(wèn)題:
8G 使用率達(dá)到了 95%以上
innodb_buffer_pool_size=2G
redo_buffer_size=256M
其他內(nèi)存總共: 1G 左右
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
3.2.2 IO調(diào)度策略
centos 7 默認(rèn)是 deadline
cat /sys/block/sda/queue/scheduler
cat /sys/block/vda/queue/scheduler
臨時(shí)修改為deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
更改到如下內(nèi)容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
IO ：
raid
no lvm
ext4或xfs
ssd
IO調(diào)度策略
提前規(guī)劃好以上所有問(wèn)題，減輕MySQL優(yōu)化的難度幕帆。
3.3 應(yīng)用層
開(kāi)發(fā)過(guò)程規(guī)范,標(biāo)準(zhǔn)
減少慢SQL:不走索引,復(fù)雜邏輯,切割大事務(wù).
like '%aa%'
!= not in
limit >500w
DDL ---> show processlist ; ---> kill ---> Online DDL ,pt-osc
delete 大量數(shù)據(jù). ----> pt-archive
update : 索引 , 鎖.避免業(yè)務(wù)邏輯錯(cuò)誤,避免鎖爭(zhēng)用.
a : update id=10 ; update id=100;
b : update id=100; update id=10;
這個(gè)階段,需要我們DBA深入業(yè)務(wù),或者要和開(kāi)發(fā)人員\業(yè)務(wù)人員配合實(shí)現(xiàn)
優(yōu)化,最根本的是"優(yōu)化"人.
----oldguo
- 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)銷(xiāo)的越多的內(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
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connecti
back_log ★★★
（1）簡(jiǎn)介
mysql能暫存的連接數(shù)量，當(dāng)主要mysql線(xiàn)程在一個(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)銷(xiāo)。
一般我們會(huì)將wait_timeout設(shè)定比較小屠阻，interactive_timeout要和應(yīng)用開(kāi)發(fā)人員溝通長(zhǎng)鏈接的應(yīng)用是否很多红省。如果他需要長(zhǎng)鏈接，那么這個(gè)值可以不需要調(diào)整国觉。
另外還可以使用類(lèi)外的參數(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)（多表鏈接痕寓、子查詢(xún)中傲醉、union）
在有以上查詢(xún)語(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单旁，但如果它的值大的離譜的話(huà)沪羔，那就好好查一下，你的服務(wù)器到底都在執(zhí)行什么查詢(xún)了象浑。
（3）配置方法
key_buffer_size=64M
query_cache_size ★★★
（1）簡(jiǎn)介：
查詢(xún)緩存簡(jiǎn)稱(chēng)QC蔫饰，使用查詢(xún)緩沖，mysql將查詢(xún)結(jié)果存放在緩沖區(qū)中愉豺，今后對(duì)于同樣的select語(yǔ)句（區(qū)分大小寫(xiě)）,將直接從緩沖區(qū)中讀取結(jié)果篓吁。
SQL層：
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;
1、查詢(xún)完結(jié)果之后粒氧，會(huì)對(duì)SQL語(yǔ)句進(jìn)行hash運(yùn)算越除，得出hash值,我們把他稱(chēng)之為SQL_ID
2、會(huì)將存儲(chǔ)引擎返回的結(jié)果+SQL_ID存儲(chǔ)到緩存中外盯。
存儲(chǔ)方式：
例子：select * from t1 where id=10; 100次
1摘盆、將select * from t1 where id=10; 進(jìn)行hash運(yùn)算計(jì)算出一串hash值，我們把它稱(chēng)之為“SQL_ID"
2饱苟、將存儲(chǔ)引擎返回上來(lái)的表的內(nèi)容+SQLID存儲(chǔ)到查詢(xún)緩存中
使用方式：
1孩擂、一條SQL執(zhí)行時(shí)，進(jìn)行hash運(yùn)算箱熬，得出SQLID类垦，去找query cache
2、如果cache中有城须，則直接返回?cái)?shù)據(jù)行蚤认，如果沒(méi)有，就走原有的SQL執(zhí)行流程
一個(gè)sql查詢(xún)?nèi)绻詓elect開(kāi)頭糕伐，那么mysql服務(wù)器將嘗試對(duì)其使用查詢(xún)緩存砰琢。
注：兩個(gè)sql語(yǔ)句，只要想差哪怕是一個(gè)字符（列如大小寫(xiě)不一樣；多一個(gè)空格等）,那么這兩個(gè)sql將使用不同的一個(gè)cache陪汽。
（2）判斷依據(jù)
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
---------------------狀態(tài)說(shuō)明--------------------
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)训唱。
如果該值顯示較大，則說(shuō)明Query Cache 中的內(nèi)存碎片較多了挚冤，F(xiàn)LUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理况增，從而得到一個(gè)空閑塊。
注：當(dāng)一個(gè)表被更新之后训挡，和它相關(guān)的cache
blocks將被free澳骤。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部舍哄⊙缌梗可以用FLUSH QUERY CACHE語(yǔ)句來(lái)清空f(shuō)ree blocks
Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小。通過(guò)這個(gè)參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠表悬，是需要增加還是過(guò)多了。
Qcache_hits：表示有多少次命中緩存丧靡。我們主要可以通過(guò)該值來(lái)驗(yàn)證我們的查詢(xún)緩存的效果蟆沫。數(shù)字越大，緩存效果越理想温治。
Qcache_inserts：表示多少次未命中然后插入饭庞，意思是新來(lái)的SQL請(qǐng)求在緩存中未找到，不得不執(zhí)行查詢(xún)處理熬荆，執(zhí)行查詢(xún)處理后把結(jié)果insert到查詢(xún)緩存中舟山。這樣的情況的次數(shù)越多，表示查詢(xún)緩存應(yīng)用到的比較少卤恳，效果也就不理想累盗。當(dāng)然系統(tǒng)剛啟動(dòng)后，查詢(xún)緩存是空的突琳，這很正常若债。
Qcache_lowmem_prunes：
多少條Query因?yàn)閮?nèi)存不足而被清除出QueryCache。通過(guò)“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合拆融，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠蠢琳，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有Query 被換出。這個(gè)數(shù)字最好長(zhǎng)時(shí)間來(lái)看镜豹；如果這個(gè)數(shù)字在不斷增長(zhǎng)傲须，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少趟脂。（上面的free_blocks和free_memory可以告訴您屬于哪種情況）
Qcache_not_cached：不適合進(jìn)行緩存的查詢(xún)的數(shù)量泰讽，通常是由于這些查詢(xún)不是 SELECT 語(yǔ)句或者用了now()之類(lèi)的函數(shù)。
Qcache_queries_in_cache：當(dāng)前Query Cache 中cache 的Query 數(shù)量；
Qcache_total_blocks：當(dāng)前Query Cache 中的block 數(shù)量菇绵；肄渗。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
90/ 10000 0 90
如果出現(xiàn)hits比例過(guò)低，其實(shí)就可以關(guān)閉查詢(xún)緩存了咬最。使用redis專(zhuān)門(mén)緩存數(shù)據(jù)庫(kù)
Qcache_free_blocks 來(lái)判斷碎片
Qcache_free_memory + Qcache_lowmem_prunes 來(lái)判斷內(nèi)存夠不夠
Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
（3）配置示例
mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
-------------------配置說(shuō)明-------------------------------
以上信息可以看出query_cache_type為off表示不緩存任何查詢(xún)
各字段的解釋?zhuān)?br>
query_cache_limit：超過(guò)此大小的查詢(xún)將不緩存
query_cache_min_res_unit：緩存塊的最小大小翎嫡，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4KB永乌，設(shè)置值大對(duì)大數(shù)據(jù)查詢(xún)有好處惑申，但如果你的查詢(xún)都是小數(shù)據(jù)查詢(xún)，就容易造成內(nèi)存碎片和浪費(fèi)翅雏。
query_cache_size：查詢(xún)緩存大小 (注：QC存儲(chǔ)的最小單位是1024byte圈驼，所以如果你設(shè)定了一個(gè)不是1024的倍數(shù)的值，這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值望几。)
query_cache_type：緩存類(lèi)型绩脆，決定緩存什么樣的查詢(xún)，注意這個(gè)值不能隨便設(shè)置橄抹，必須設(shè)置為數(shù)字靴迫，可選項(xiàng)目以及說(shuō)明如下：
如果設(shè)置為0，那么可以說(shuō)楼誓，你的緩存根本就沒(méi)有用玉锌，相當(dāng)于禁用了。
如果設(shè)置為1疟羹，將會(huì)緩存所有的結(jié)果主守，除非你的select語(yǔ)句使用SQL_NO_CACHE禁用了查詢(xún)緩存。
如果設(shè)置為2榄融，則只緩存在select語(yǔ)句中通過(guò)SQL_CACHE指定需要緩存的查詢(xún)参淫。
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=128M
query_cache_type=1
max_connect_errors ***
max_connect_errors是一個(gè)mysql中與安全有關(guān)的計(jì)數(shù)器值，它負(fù)責(zé)阻止過(guò)多嘗試失敗的客戶(hù)端以防止暴力破解密碼等情況剃袍，當(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
sort_buffer_size ★★★
（1）簡(jiǎn)介：
每個(gè)需要進(jìn)行排序的線(xià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
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
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索引
thread_cache_size★★★★★
(1)簡(jiǎn)介
服務(wù)器線(xiàn)程緩存，這個(gè)值表示可以重新利用保存在緩存中線(xiàn)程的數(shù)量,當(dāng)斷開(kāi)連接時(shí),那么客戶(hù)端的線(xiàn)程將被放到緩存中以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀(前提是緩存數(shù)未達(dá)上限),如果線(xiàn)程重新被請(qǐng)求沪哺，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求沈自，那么這個(gè)線(xiàn)程將被重新創(chuàng)建,如果有很多新的線(xiàn)程，增加這個(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ù)器處理此客戶(hù)的線(xiàn)程將會(huì)緩存起來(lái)以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀(前提是緩存數(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í)此刻線(xiàn)程緩存中有多少空閑線(xiàn)程。
Threads_connected:代表當(dāng)前已建立連接的數(shù)量插掂，因?yàn)橐粋€(gè)連接就需要一個(gè)線(xiàn)程灰瞻，所以也可以看成當(dāng)前被使用的線(xiàn)程數(shù)。
Threads_created:代表從最近一次服務(wù)啟動(dòng)辅甥，已創(chuàng)建線(xiàn)程的數(shù)量酝润，如果發(fā)現(xiàn)Threads_created值過(guò)大的話(huà)，表明MySQL服務(wù)器一直在創(chuàng)建線(xiàn)程璃弄，這也是比較耗cpu SYS資源要销，可以適當(dāng)增加配置文件中thread_cache_size值。
Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線(xiàn)程數(shù)夏块。并不是代表正在使用的線(xiàn)程數(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)存）
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
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
innodb_thread_concurrency ★★★
（1）簡(jiǎn)介
此參數(shù)用來(lái)設(shè)置innodb線(xiàn)程的并發(fā)數(shù)量，默認(rèn)值為0表示不限制炒俱。
（2）配置依據(jù)
在官方doc上盐肃，對(duì)于innodb_thread_concurrency的使用爪膊，也給出了一些建議，如下：
如果一個(gè)工作負(fù)載中砸王，并發(fā)用戶(hù)線(xiàn)程的數(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)能夠提供最佳性能的線(xiàn)程數(shù)，
例如般此，假設(shè)系統(tǒng)通常有40到50個(gè)用戶(hù)遍尺，但定期的數(shù)量增加至60侥衬，70诲侮，甚至200。你會(huì)發(fā)現(xiàn)屹培，
性能在80個(gè)并發(fā)用戶(hù)設(shè)置時(shí)表現(xiàn)穩(wěn)定默穴，如果高于這個(gè)數(shù)，性能反而下降褪秀。在這種情況下蓄诽，
建議設(shè)置innodb_thread_concurrency參數(shù)為80，以避免影響性能媒吗。
如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶(hù)線(xiàn)程使用的虛擬CPU更多（比如20個(gè)虛擬CPU）仑氛，
建議通過(guò)設(shè)置innodb_thread_concurrency 參數(shù)為這個(gè)值（也可能更低，這取決于性能體現(xiàn)）闸英，
如果你的目標(biāo)是將MySQL與其他應(yīng)用隔離调衰，你可以l考慮綁定mysqld進(jìn)程到專(zhuā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ù)載量、用戶(hù)數(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)到位了.
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ù)
innodb_log_file_size = 100M ★★★★★
設(shè)置 ib_logfile0 ib_logfile1
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位谒主，更大的設(shè)置可以提高性能.
innodb_log_file_size = 100M
innodb_log_files_in_group = 3 ★★★★★
為提高性能朝扼，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ī)讀（查詢(xún)操作）緩沖區(qū)大小异旧。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)提佣，將分配一個(gè)隨機(jī)讀緩存區(qū)吮蛹。進(jìn)行排序查詢(xún)時(shí)，MySql會(huì)首先掃描一遍該緩沖拌屏，以避免磁盤(pán)搜索潮针，提高查詢(xún)速度，如果需要排序大量數(shù)據(jù)倚喂，可適當(dāng)調(diào)高該值每篷。但MySql會(huì)為每個(gè)客戶(hù)連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值端圈，以避免內(nèi)存開(kāi)銷(xiāo)過(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
tokuDB percona
myrocks
RocksDB
TiDB
MongoDB
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)刪除”。
log-bin=/data/mysql-bin
binlog_format=row
sync_binlog=1
雙1標(biāo)準(zhǔn)(基于安全的控制)：
sync_binlog=1 什么時(shí)候刷新binlog到磁盤(pán)倚搬，每次事務(wù)commit
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的話(huà)庙楚，日志還是直接每次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的話(huà)，日志還是直接每次commit直接寫(xiě)入磁盤(pán)
最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
一般情況下沛善，我們更偏向于安全航揉。
“雙一標(biāo)準(zhǔn)”
innodb_flush_log_at_trx_commit=1 ★★★★★★★★★★
sync_binlog=1 ★★★★★★★★★★
innodb_flush_method=O_DIRECT
- 參數(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='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose
7. 鎖的監(jiān)控及處理
7.1 鎖等待模擬
概念：
Record Lock
Next Lock
GAP 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)
image.png
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:連接層的,連接線(xiàn)程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 : 等待的線(xiàn)程號(hào)(與上個(gè)視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務(wù)ID
blocking_pid : 鎖源的線(xiàn)程號(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ì)話(huà),最終解決問(wèn)題
(2)
開(kāi)發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問(wèn)題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
臨時(shí)解決方案,將阻塞事務(wù)的會(huì)話(huà)kill掉.
最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項(xiàng)目結(jié)果:
經(jīng)過(guò)排查處理,鎖等待的個(gè)數(shù)減少80%.解決了CPU持續(xù)峰值的問(wèn)題.
鎖監(jiān)控設(shè)計(jì)到的命令:
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
- 主從優(yōu)化：
5.7 從庫(kù)多線(xiàn)程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;
