如果您對數(shù)據(jù)庫感興趣，可以添加 DBA解決方案QQ群：895979329

1.優(yōu)化哲學(xué)

1.1 為什么優(yōu)化？

為了獲得成就感?
為了證實(shí)比系統(tǒng)設(shè)計(jì)者更懂?dāng)?shù)據(jù)庫?
為了從優(yōu)化成果來證實(shí)優(yōu)化者更有價(jià)值?

image.png

但通常事實(shí)證實(shí)的結(jié)果往往會和您期待相反！
優(yōu)化有風(fēng)險(xiǎn)，涉足需謹(jǐn)慎！

1.2 優(yōu)化風(fēng)險(xiǎn)

優(yōu)化不總是對一個(gè)單純的環(huán)境進(jìn)行！還很可能是一個(gè)復(fù)雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風(fēng)險(xiǎn)，只不過你沒能力意識到和預(yù)見到！
任何的技術(shù)可以解決一個(gè)問題，但必然存在帶來一個(gè)問題的風(fēng)險(xiǎn)！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失??！

穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要！
優(yōu)化不可避免涉及到變更，變更就有風(fēng)險(xiǎn)！
優(yōu)化使性能變好，維持和變差是等概率事件！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔(dān)當(dāng)風(fēng)險(xiǎn)，但會所有的人分享優(yōu)化成果！
所以優(yōu)化工作是由業(yè)務(wù)需要驅(qū)使的?。。?

1.3 誰參與優(yōu)化

數(shù)據(jù)庫管理員
業(yè)務(wù)部門代表
應(yīng)用程序架構(gòu)師
應(yīng)用程序設(shè)計(jì)人員
應(yīng)用程序開發(fā)人員
硬件及系統(tǒng)管理員
存儲管理員

image.png

1.4 優(yōu)化方向

安全優(yōu)化（業(yè)務(wù)持續(xù)性）
性能優(yōu)化（業(yè)務(wù)高效性）

1.5 優(yōu)化的范圍及思路

優(yōu)化范圍：
存儲、主機(jī)和操作系統(tǒng):
    主機(jī)架構(gòu)穩(wěn)定性
    I/O規(guī)劃及配置
    Swap
    OS內(nèi)核參數(shù)
        網(wǎng)絡(luò)問題
應(yīng)用程序:（Index，lock，session）
        應(yīng)用程序穩(wěn)定性和性能
        SQL語句性能
    串行訪問資源
    性能欠佳會話管理
數(shù)據(jù)庫優(yōu)化:（內(nèi)存、數(shù)據(jù)庫設(shè)計(jì)、參數(shù)）
    內(nèi)存
    數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)
    實(shí)例配置

優(yōu)化效果和成本的評估：

image.png

2. 優(yōu)化工具的使用

2.1 系統(tǒng)層面的

2.1.1 CPU

top

cpu使用情況的平均值：

image.png

CPU每個(gè)核心的分別使用的情況（按1）：

image.png

程序是如何使用CPU的？

系統(tǒng)給每個(gè)程序分配CPU的時(shí)候，以時(shí)間來劃分表的。

CPU有效工作時(shí)間?

計(jì)算: 程序運(yùn)行,數(shù)據(jù)處理
控制: 少量的關(guān)于申請資源和釋放資源等

CPU無效工作時(shí)間

等待 IO

CPU各項(xiàng)指標(biāo)說明

0.0 us
用戶程序，在運(yùn)行過程中，使用的CPU時(shí)間的占比。
我們希望的是越高越好，盡量控制在90%
0.0 sy
控制: 資源管理，內(nèi)核的工作(系統(tǒng)調(diào)用)
sys高的原因: 
      1.  bug ,中病毒了
      2.  鎖的問題
99.9 id 
CPU空間的時(shí)間占比      

0.0 wa
CPU花在等待上的時(shí)間
wa高的原因：
          1. 鎖
          2. IO （raid，過度條帶化）
          3. 索引
多cpu使用情況監(jiān)控：
主要判斷我們cpu多核心有沒有被充分利用。
現(xiàn)象：單顆很忙，其他很閑，對于MySQL來講，有可能是并發(fā)參數(shù)設(shè)定不合理導(dǎo)致的。

2.1.2 MEM

KiB Mem :  4028432 total,  3774464 free,   116240 used,   137728 buff/cache
### 1.2.1 名稱介紹
total :總內(nèi)存大小
free  ：空閑的
used  ：在使用的
buff/cache ：緩沖區(qū) 和 緩存

2.1.3 內(nèi)存管理子系統(tǒng)：

slab Allocator
buddy system 
程序=指令+數(shù)據(jù)
對于page cache來講(OS buffer)
1. 內(nèi)存的可用空間的計(jì)算   free +buffer cache 
2. 內(nèi)存回收(buffer)的方式:
        (1) 寫入磁盤
        (2) swap  
對于數(shù)據(jù)庫來講：需要將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% 會使用swap
Linux 7操作系統(tǒng)
內(nèi)存使用達(dá)到100%-30%（70%）時(shí)候，才會時(shí)候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)象說明
1. IO 高 cpu us 也高,屬于正?，F(xiàn)象
2. CPU  us高  IO很低   ,MySQL 不在做增刪改查,有可能是存儲過程,函數(shù),排序,分組,多表連接
3. Wait,SYS 高  , IO低:IO出問題了,鎖等待過多的幾率比較大. 
IOPS：每秒磁盤最多能夠發(fā)生的IO次數(shù)，這是個(gè)定值 
頻繁小事務(wù),IOPS很高,達(dá)到閾值,可能IO吞吐量沒超過IO最大吞吐量.無法新的IO了
存儲規(guī)劃有問題.

2.3 數(shù)據(jù)庫優(yōu)化工具

    show status  
    show variables 
    show index  
    show processlist 
    show slave status
    show engine innodb status 
    desc /explain 
        slowlog
    擴(kuò)展類深度優(yōu)化:
    pt系列
    mysqlslap 
    sysbench 
    information_schema 
    performance_schema
    sys

3. 優(yōu)化思路分解

3.1 硬件優(yōu)化

主機(jī)

真實(shí)的硬件（PC Server）: DELL  R系列 ，華為，浪潮，HP，聯(lián)想
云產(chǎn)品：ECS、數(shù)據(jù)庫RDS、DRDS
IBM 小型機(jī) P6  570  595   P7 720  750 780     P8 

CPU根據(jù)數(shù)據(jù)庫類型

OLTP 
OLAP  
IO密集型：線上系統(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)），主頻相對低，核心數(shù)量多

內(nèi)存

建議2-3倍cpu核心數(shù)量 （ECC）

磁盤選擇

SATA-III   SAS    Fc    SSD（sata） pci-e  ssd  Flash
主機(jī) RAID卡的BBU(Battery Backup Unit)關(guān)閉

存儲

根據(jù)存儲數(shù)據(jù)種類的不同，選擇不同的存儲設(shè)備
配置合理的RAID級別(raid5、raid10、熱備盤)   
r0 :條帶化 ,性能高
r1 :鏡像，安全
r5 :校驗(yàn)+條帶化，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高，最少四塊盤，浪費(fèi)一半的空間（高IO要求）

網(wǎng)絡(luò)

1、硬件買好的（單卡單口）
2、網(wǎng)卡綁定（bonding），交換機(jī)堆疊
以上問題，提前規(guī)避掉。

3.2 操作系統(tǒng)優(yōu)化

Swap調(diào)整

echo 0 >/proc/sys/vm/swappiness的內(nèi)容改成0（臨時(shí)），
/etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p

這個(gè)參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache。在內(nèi)存緊張的情況下，數(shù)值越低越傾向于釋放文件系統(tǒng)cache。
當(dāng)然，這個(gè)參數(shù)只能減少使用swap的概率，并不能避免Linux使用swap。

修改MySQL的配置參數(shù)innodb_flush_method，開啟O_DIRECT模式
這種情況下，InnoDB的buffer pool會直接繞過文件系統(tǒng)cache來訪問磁盤，但是redo log依舊會使用文件系統(tǒng)cache。值得注意的是，Redo log是覆寫模式的，即使使用了文件系統(tǒng)的cache，也不會占用太多

IO調(diào)度策略

centos 7 默認(rèn)是deadline
cat   /sys/block/sda/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ī)劃好以上所有問題，減輕MySQL優(yōu)化的難度。

3.3 應(yīng)用端

1. 開發(fā)過程規(guī)范,標(biāo)準(zhǔn)
2. 減少爛SQL:不走索引,復(fù)雜邏輯,切割大事務(wù).
3. 避免業(yè)務(wù)邏輯錯(cuò)誤,避免鎖爭用.
這個(gè)階段,需要我們DBA深入業(yè)務(wù),或者要和開發(fā)人員\業(yè)務(wù)人員配合實(shí)現(xiàn)

優(yōu)化,最根本的是"優(yōu)化"人.
                    ----oldguo      

4. MySQL參數(shù)優(yōu)化測試

虛擬機(jī)vm12.5，OS centos 6.9（系統(tǒng)已優(yōu)化），cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD

模擬數(shù)據(jù)庫數(shù)據(jù)
drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t_100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
插入100w條數(shù)據(jù)：
call rand_data(10000000);
commit;

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1000w where k2='FGCD'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose

5. 優(yōu)化細(xì)節(jié)：

5.1 參數(shù)優(yōu)化

Max_connections *****

（1）簡介
Mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)請求量比較大，可以調(diào)高這個(gè)值，當(dāng)然這是要建立在機(jī)器能夠支撐的情況下，因?yàn)槿绻B接數(shù)越來越多，mysql會為每個(gè)連接提供緩沖區(qū)，就會開銷的越多的內(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.開啟數(shù)據(jù)庫時(shí),我們可以臨時(shí)設(shè)置一個(gè)比較大的測試值
    2.觀察show status like 'Max_used_connections';變化
    3.如果max_used_connections跟max_connections相同,
    那么就是max_connections設(shè)置過低或者超過服務(wù)器的負(fù)載上限了，
    低于10%則設(shè)置過大. 

back_log ***

（1）簡介
mysql能暫存的連接數(shù)量，當(dāng)主要mysql線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請求時(shí)候它就會起作用，如果mysql的連接數(shù)據(jù)達(dá)到max_connections時(shí)候，新來的請求將會被存在堆棧中，等待某一連接釋放資源，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過back_log，將不被授予連接資源。
back_log值指出在mysql暫時(shí)停止回答新請求之前的短時(shí)間內(nèi)有多少個(gè)請求可以被存在推棧中，只有如果期望在一個(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=1024

wait_timeout和interactive_timeout ****

（1）簡介
wait_timeout：指的是mysql在關(guān)閉一個(gè)非交互的連接之前所要等待的秒數(shù)
interactive_timeout：指的是mysql在關(guān)閉一個(gè)交互的連接之前所需要等待的秒數(shù)，比如我們在終端上進(jìn)行mysql管理，使用的即使交互的連接，這時(shí)候，如果沒有操作的時(shí)間超過了interactive_time設(shè)置的時(shí)間就會自動(dòng)的斷開，默認(rèn)的是28800，可調(diào)優(yōu)為7200。
wait_timeout:如果設(shè)置太小，那么連接關(guān)閉的就很快，從而使一些持久的連接不起作用
（2）設(shè)置建議
如果設(shè)置太大，容易造成連接打開時(shí)間過長，在show processlist時(shí)候，能看到很多的連接 ，一般希望wait_timeout盡可能低
（3）修改方式舉例
wait_timeout=60
interactive_timeout=1200
長連接的應(yīng)用，為了不去反復(fù)的回收和分配資源，降低額外的開銷。
一般我們會將wait_timeout設(shè)定比較小，interactive_timeout要和應(yīng)用開發(fā)人員溝通長鏈接的應(yīng)用是否很多。如果他需要長鏈接，那么這個(gè)值可以不需要調(diào)整。
另外還可以使用類外的參數(shù)彌補(bǔ)。

5.4 key_buffer_size *****

（1）簡介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關(guān)
《2》臨時(shí)表的創(chuàng)建有關(guān)（多表鏈接、子查詢中、union）
     在有以上查詢語句出現(xiàn)的時(shí)候，需要?jiǎng)?chuàng)建臨時(shí)表，用完之后會被丟棄
     臨時(shí)表有兩種創(chuàng)建方式：
                        內(nèi)存中------->key_buffer_size
                        磁盤上------->ibdata1(5.6)
                                      ibtmp1 (5.7）
（2）設(shè)置依據(jù)
通過key_read_requests和key_reads可以直到key_baffer_size設(shè)置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> 
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 10    |
| Key_reads         | 2     |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> 
一共有10個(gè)索引讀取請求，有2個(gè)請求在內(nèi)存中沒有找到直接從硬盤中讀取索引
控制在 5%以內(nèi) 。
注：key_buffer_size只對myisam表起作用，即使不使用myisam表，但是內(nèi)部的臨時(shí)磁盤表是myisam表，也要使用該值。
可以使用檢查狀態(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     |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> 
通常地，我們習(xí)慣以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一個(gè)時(shí)段內(nèi)的差額計(jì)算，來判斷基于內(nèi)存的臨時(shí)表利用率。所以，我們會比較關(guān)注 Created_tmp_disk_tables 是否過多，從而認(rèn)定當(dāng)前服務(wù)器運(yùn)行狀況的優(yōu)劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以內(nèi)
看以下例子：
在調(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。

所以說，以上公式并不能真正反映到mysql里臨時(shí)表的利用率，有些情況下產(chǎn)生的 Tmp_table_on_disk 我們完全不用擔(dān)心，因此沒必要過分關(guān)注 Created_tmp_disk_tables，但如果它的值大的離譜的話，那就好好查一下，你的服務(wù)器到底都在執(zhí)行什么查詢了。 
（3）配置方法
key_buffer_size=64M

query_cache_size ***

（1）簡介：
查詢緩存簡稱QC，使用查詢緩沖，mysql將查詢結(jié)果存放在緩沖區(qū)中，今后對于同樣的select語句（區(qū)分大小寫）,將直接從緩沖區(qū)中讀取結(jié)果。

SQL層：
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;

1、查詢完結(jié)果之后，會對SQL語句進(jìn)行hash運(yùn)算，得出hash值,我們把他稱之為SQL_ID
2、會將存儲引擎返回的結(jié)果+SQL_ID存儲到緩存中。

存儲方式：
例子：select * from t1  where id=10;      100次

1、將select * from t1  where id=10; 進(jìn)行hash運(yùn)算計(jì)算出一串hash值，我們把它稱之為“SQL_ID"
2、將存儲引擎返回上來的表的內(nèi)容+SQLID存儲到查詢緩存中

使用方式：
1、一條SQL執(zhí)行時(shí)，進(jìn)行hash運(yùn)算，得出SQLID，去找query cache
2、如果cache中有，則直接返回?cái)?shù)據(jù)行，如果沒有，就走原有的SQL執(zhí)行流程

一個(gè)sql查詢?nèi)绻詓elect開頭，那么mysql服務(wù)器將嘗試對其使用查詢緩存。
注：兩個(gè)sql語句，只要想差哪怕是一個(gè)字符（列如大小寫不一樣；多一個(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)說明--------------------
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。
如果該值顯示較大，則說明Query Cache 中的內(nèi)存碎片較多了，F(xiàn)LUSH QUERY CACHE會對緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。
注：當(dāng)一個(gè)表被更新之后，和它相關(guān)的cache 
blocks將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部?？梢杂肍LUSH QUERY CACHE語句來清空free blocks

Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小。通過這個(gè)參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠，是需要增加還是過多了。

Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗(yàn)證我們的查詢緩存的效果。數(shù)字越大，緩存效果越理想。

Qcache_inserts：表示多少次未命中然后插入，意思是新來的SQL請求在緩存中未找到，不得不執(zhí)行查詢處理，執(zhí)行查詢處理后把結(jié)果insert到查詢緩存中。這樣的情況的次數(shù)越多，表示查詢緩存應(yīng)用到的比較少，效果也就不理想。當(dāng)然系統(tǒng)剛啟動(dòng)后，查詢緩存是空的，這很正常。

Qcache_lowmem_prunes：
多少條Query因?yàn)閮?nèi)存不足而被清除出QueryCache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有Query 被換出。這個(gè)數(shù)字最好長時(shí)間來看；如果這個(gè)數(shù)字在不斷增長，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。（上面的free_blocks和free_memory可以告訴您屬于哪種情況）

Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(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比例過低，其實(shí)就可以關(guān)閉查詢緩存了。使用redis專門緩存數(shù)據(jù)庫

Qcache_free_blocks    來判斷碎片
Qcache_free_memory   +   Qcache_lowmem_prunes  來判斷內(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)

mysql> 
-------------------配置說明-------------------------------
以上信息可以看出query_cache_type為off表示不緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
query_cache_size：查詢緩存大小 (注：QC存儲的最小單位是1024byte，所以如果你設(shè)定了一個(gè)不是1024的倍數(shù)的值，這個(gè)值會被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。)

query_cache_type：緩存類型，決定緩存什么樣的查詢，注意這個(gè)值不能隨便設(shè)置，必須設(shè)置為數(shù)字，可選項(xiàng)目以及說明如下：
如果設(shè)置為0，那么可以說，你的緩存根本就沒有用，相當(dāng)于禁用了。
如果設(shè)置為1，將會緩存所有的結(jié)果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。
如果設(shè)置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

修改/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é)阻止過多嘗試失敗的客戶端以防止暴力破解密碼等情況，當(dāng)超過指定次數(shù)，mysql服務(wù)器將禁止host的連接請求，直到mysql服務(wù)器重啟或通過flush hosts命令清空此host的相關(guān)信息 max_connect_errors的值與性能并無太大關(guān)系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下內(nèi)容
max_connect_errors=2000

sort_buffer_size ***

（1）簡介：
每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速
ORDER BY 
GROUP BY
distinct
union 

（2）配置依據(jù)
Sort_Buffer_Size并不是越大越好，由于是connection級的參數(shù)，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。
列如：500個(gè)連接將會消耗500*sort_buffer_size（2M）=1G內(nèi)存
（3）配置方法
 修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M

max_allowed_packet *****

（1）簡介：
mysql根據(jù)配置文件會限制，server接受的數(shù)據(jù)包大小。
（2）配置依據(jù)：
有時(shí)候大的插入和更新會受max_allowed_packet參數(shù)限制，導(dǎo)致寫入或者更新失敗，更大值是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ù)對應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。
盡量在SQL與方面進(jìn)行優(yōu)化，效果較為明顯。
優(yōu)化的方法：在on條件列加索引，至少應(yīng)當(dāng)是有MUL索引

thread_cache_size *****

(1)簡介
服務(wù)器線程緩存，這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時(shí),那么客戶端的線程將被放到緩存中以響應(yīng)下一個(gè)客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限),如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能.
（2）配置依據(jù)
通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。
設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8，2GB配置為16，3GB配置為32，4GB或更高內(nèi)存，可配置更大。
服務(wù)器處理此客戶的線程將會緩存起來以響應(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值過大的話，表明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ì)階段，會設(shè)置一個(gè)測試值，做壓力測試。
結(jié)合zabbix監(jiān)控，看一段時(shí)間內(nèi)此狀態(tài)的變化。
如果在一段時(shí)間內(nèi)，Threads_created趨于平穩(wěn)，說明對應(yīng)參數(shù)設(shè)定是OK。
如果一直陡峭的增長，或者出現(xiàn)大量峰值，那么繼續(xù)增加此值的大小，在系統(tǒng)資源夠用的情況下（內(nèi)存）

innodb_buffer_pool_size *****

（1）簡介
對于InnoDB表來說，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對于MyISAM表的作用一樣。
（2）配置依據(jù)：
InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。
對于單獨(dú)的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%,一般我們建議不要超過物理內(nèi)存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit ******

（1）簡介
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時(shí)間點(diǎn)，取值分別為0、1、2三個(gè)。
0，表示當(dāng)事務(wù)提交時(shí)，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；
1，
每次事務(wù)的提交都會引起redo日志文件寫入、flush磁盤的操作，確保了事務(wù)的ACID；
2，每次事務(wù)提交引起寫入日志文件的動(dòng)作,但每秒鐘完成一次flush磁盤操作。

（2）配置依據(jù)
實(shí)際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時(shí)插入10000條記錄只需要2秒，設(shè)置為0時(shí)只需要1秒，而設(shè)置為1時(shí)則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個(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）簡介
此參數(shù)用來設(shè)置innodb線程的并發(fā)數(shù)量，默認(rèn)值為0表示不限制。
（2）配置依據(jù)
在官方doc上，對于innodb_thread_concurrency的使用，也給出了一些建議，如下：
如果一個(gè)工作負(fù)載中，并發(fā)用戶線程的數(shù)量小于64，建議設(shè)置innodb_thread_concurrency=0；
如果工作負(fù)載一直較為嚴(yán)重甚至偶爾達(dá)到頂峰，建議先設(shè)置innodb_thread_concurrency=128，
并通過不斷的降低這個(gè)參數(shù)，96, 80, 64等等，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)，
例如，假設(shè)系統(tǒng)通常有40到50個(gè)用戶，但定期的數(shù)量增加至60，70，甚至200。你會發(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），
建議通過設(shè)置innodb_thread_concurrency 參數(shù)為這個(gè)值（也可能更低，這取決于性能體現(xiàn)），
如果你的目標(biāo)是將MySQL與其他應(yīng)用隔離，你可以l考慮綁定mysqld進(jìn)程到專有的虛擬CPU。
但是需 要注意的是，這種綁定，在myslqd進(jìn)程一直不是很忙的情況下，可能會導(dǎo)致非最優(yōu)的硬件使用率。在這種情況下，
你可能會設(shè)置mysqld進(jìn)程綁定的虛擬 CPU，允許其他應(yīng)用程序使用虛擬CPU的一部分或全部。
在某些情況下，最佳的innodb_thread_concurrency參數(shù)設(shè)置可以比虛擬CPU的數(shù)量小。
定期檢測和分析系統(tǒng)，負(fù)載量、用戶數(shù)或者工作環(huán)境的改變可能都需要對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ù)，有沒有達(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í),說明已經(jīng)到位了.

innodb_log_buffer_size

此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，對于較大的事務(wù)，可以增大緩存大小。
innodb_log_buffer_size=128M

設(shè)定依據(jù)：
1、大事務(wù)： 存儲過程調(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)方式將日志文件寫到多個(gè)文件。推薦設(shè)置為3

read_buffer_size = 1M **

MySql讀入緩沖區(qū)大小。對表進(jìn)行順序掃描的請求將分配一個(gè)讀入緩沖區(qū)，MySql會為它分配一段內(nèi)存緩沖區(qū)。如果對表的順序掃描請求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和 sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享

read_rnd_buffer_size = 1M **

MySql的隨機(jī)讀（查詢操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySql會為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。
注：順序讀是指根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找實(shí)際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同，因此訪問方式是隨機(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ù)過程中用來存儲二進(jìn)制日志的緩存, 提高記錄bin-log的效率。沒有什么大事務(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，還會自動(dòng)創(chuàng)建新的二進(jìn)制日志。你不能將該變量設(shè)置為大于1GB或小于4096字節(jié)。默認(rèn)值是1GB。在導(dǎo)入大容量的sql文件時(shí)，建議關(guān)閉sql_log_bin，否則硬盤扛不住，而且建議定期做刪除。
expire_logs_days = 7 //定義了mysql清除過期日志的時(shí)間。
二進(jìn)制日志自動(dòng)刪除的天數(shù)。默認(rèn)值為0,表示“沒有自動(dòng)刪除”。
log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1
雙1標(biāo)準(zhǔn)(基于安全的控制)：
sync_binlog=1   什么時(shí)候刷新binlog到磁盤，每次事務(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ù)頁需要持久化時(shí)，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P
（2）在redo buffuer需要持久化時(shí)，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤
2、 Innodb_flush_method=O_DIRECT
（1）在數(shù)據(jù)頁需要持久化時(shí)，直接寫入磁盤
（2）在redo buffuer需要持久化時(shí)，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時(shí)候?qū)懭氪疟P
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤

最安全模式：
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

6. 參數(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  
        
再次壓力測試  ：
 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. 看有沒有鎖等待
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號
trx_state : 當(dāng)前事務(wù)的狀態(tài)
trx_mysql_thread_id:連接層的,連接線程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 當(dāng)前被阻塞的操作(一般是要丟給開發(fā)的)

7.3.查看鎖源,誰鎖的我!

SELECT * FROM sys.innodb_lock_waits;     ## ====>被鎖的和鎖定它的之間關(guān)系

locked_table : 哪張表出現(xiàn)的等待 
waiting_trx_id: 等待的事務(wù)(與上個(gè)視圖trx_id 對應(yīng))
waiting_pid   : 等待的線程號(與上個(gè)視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務(wù)ID 
blocking_pid    : 鎖源的線程號

7.4. 找到鎖源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41

7.5. 找到鎖源的SQL語句

-- 當(dāng)前在執(zhí)行的語句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 執(zhí)行語句的歷史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

得出結(jié)果,丟給開發(fā)
表信息 
被阻塞的
鎖源SQL

練習(xí):
一鍵獲得以上信息,請寫出具體的SQL語句

7.6 優(yōu)化項(xiàng)目:鎖的監(jiān)控及處理

1. 背景: 
硬件環(huán)境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡檢時(shí),發(fā)現(xiàn)9-11點(diǎn)時(shí)間段的CPU壓力非常高(80-90%)

2. 項(xiàng)目的職責(zé)
    2.1 通過top詳細(xì)排查,發(fā)現(xiàn)mysqld進(jìn)程占比達(dá)到了700-800%
    2.2 其中有量的CPU是被用作的SYS和WAIT,us處于正常
    2.3 懷疑是MySQL 鎖 或者SQL語句出了問題
    2.4 經(jīng)過排查slowlog及鎖等待情況,發(fā)現(xiàn)有大量鎖等待及少量慢語句    
    (1) pt-query-diagest 查看慢日志  
    (2) 鎖等待有沒有?
    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,說明當(dāng)前很多鎖等待情況
    情況二:
            1000多個(gè)lock_waits,說明歷史上發(fā)生過的鎖等待很多
    2.5 查看那個(gè)事務(wù)在等待(被阻塞了)
    2.6 查看鎖源事務(wù)信息(誰鎖的我)
    2.7 找到鎖源的thread_id 
    2.8 找到鎖源的SQL語句
3. 找到語句之后,和應(yīng)用開發(fā)人員進(jìn)行協(xié)商   
    (1)
    開發(fā)人員描述,此語句是事務(wù)掛起導(dǎo)致
    我們提出建議是臨時(shí)kill 會話,最終解決問題
    (2) 
    開發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
    臨時(shí)解決方案,將阻塞事務(wù)的會話kill掉.
    最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項(xiàng)目結(jié)果:
    經(jīng)過排查處理,鎖等待的個(gè)數(shù)減少80%.解決了CPU持續(xù)峰值的問題.
    
鎖監(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  

8. 主從優(yōu)化：

## 5.7 從庫多線程MTS
基本要求:
5.7以上的版本(忘記小版本)
必須開啟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;

=====================MySQL END==================