第十二章節(jié) MySQL 工具應(yīng)用及全面優(yōu)化

一、 PT（percona-toolkits）工具的應(yīng)用:

1. pt工具安裝

[root@master ~]# yum install -y? percona-toolkit-3.1.0-2.el7.x86_64.rpm

2. 常用工具使用介紹

2.1 pt-archiver 歸檔表

場景：

面試題： 億級的大表，delete批量刪除100w左右數(shù)據(jù)欧芽。

面試題： 定期按照時間范圍嫂粟，進(jìn)行歸檔表。

# 重要參數(shù)

--limit 100? ? ? ? 每次取100行數(shù)據(jù)用pt-archive處理? ?

--txn-size? 100? ? 設(shè)置100行為一個事務(wù)提交一次懂衩，? ?

--where 'id<3000'? 設(shè)置操作條件? ?

--progress 5000? ? 每處理5000行輸出一次處理信息? ?

--statistics? ? ? ? 輸出執(zhí)行過程及最后的操作統(tǒng)計勤婚。（只要不加上--quiet，默認(rèn)情況下pt- archive都會輸出執(zhí)行過程的）? ?

--charset=UTF8? ? ? 指定字符集為UTF8—這個最后加上不然可能出現(xiàn)亂碼臣疑。? ?

--bulk-delete? ? ? 批量刪除source上的舊數(shù)據(jù)(例如每次1000行的批量刪除操作)

注意:? 需要歸檔表中至少有一個索引,做好是where條件列有索引

使用案例：

1.歸檔到數(shù)據(jù)庫

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.51,D=world,t=city,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"

2.2 pt-osc

場景：?

? 修改表結(jié)構(gòu)、索引創(chuàng)建刪除

? 不能加快速度讯沈，但能減少業(yè)務(wù)影響（鎖）郁岩。

面試題 ： ? ?

pt-osc工作流程：

1、檢查更改表是否有主鍵或唯一索引缺狠，是否有觸發(fā)器

2问慎、檢查修改表的表結(jié)構(gòu)，創(chuàng)建一個臨時表挤茄，在新表上執(zhí)行ALTER TABLE語句

create table? bak like t1;

alter table bak add telnum char(11) not null;

3如叼、在源表上創(chuàng)建三個觸發(fā)器分別對于INSERT UPDATE DELETE操作

create trigger

a

b

c

4、從源表拷貝數(shù)據(jù)到臨時表穷劈，在拷貝過程中笼恰，對源表的更新操作會寫入到新建表中

insert into bak? select * from t1?

5、將臨時表和源表rename（需要元數(shù)據(jù)修改鎖歇终，需要短時間鎖表）

6社证、刪除源表和觸發(fā)器，完成表結(jié)構(gòu)的修改评凝。

pt-osc工具限制

1追葡、源表必須有主鍵或唯一索引，如果沒有工具將停止工作

2、如果線上的復(fù)制環(huán)境過濾器操作過于復(fù)雜宜肉，工具將無法工作

3匀钧、如果開啟復(fù)制延遲檢查，但主從延遲時谬返，工具將暫停數(shù)據(jù)拷貝工作

4之斯、如果開啟主服務(wù)器負(fù)載檢查，但主服務(wù)器負(fù)載較高時朱浴，工具將暫停操作

5吊圾、當(dāng)表使用外鍵時达椰，如果未使用--alter-foreign-keys-method參數(shù)翰蠢，工具將無法執(zhí)行

6、只支持Innodb存儲引擎表啰劲，且要求服務(wù)器上有該表1倍以上的空閑空間梁沧。

pt-osc之a(chǎn)lter語句限制

1、不需要包含alter table關(guān)鍵字蝇裤，可以包含多個修改操作廷支，使用逗號分開，如"drop clolumn c1, add column c2 int"

2栓辜、不支持rename語句來對表進(jìn)行重命名操作

3恋拍、不支持對索引進(jìn)行重命名操作

4、如果刪除外鍵藕甩，需要對外鍵名加下劃線施敢，如刪除外鍵fk_uid, 修改語句為"DROP FOREIGN KEY _fk_uid"

pt-osc之命令模板

## --execute表示執(zhí)行

## --dry-run表示只進(jìn)行模擬測試

## 表名只能使用參數(shù)t來設(shè)置，沒有長參數(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 state int not null default 1" D=test,t=t100w --print --execute

pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add index idx(num)" D=test,t=t100w --print --execute

2.3 pt-table-checksum

場景：? 校驗主從數(shù)據(jù)一致性

2.3.1 創(chuàng)建數(shù)據(jù)庫

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ù)制的過濾器狭莱，默認(rèn)是yes僵娃，建議啟用不檢查模式。

--databases | -d：指定需要被檢查的數(shù)據(jù)庫腋妙，多個庫之間可以用逗號分隔默怨。

--[no]check-binlog-format：是否檢查binlog文件的格式，默認(rèn)值yes骤素。建議開啟不檢查匙睹。因為在默認(rèn)的row格式下會出錯。

--replicate`：把checksum的信息寫入到指定表中济竹。

--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 ：指定通過pt-table-checksum得到的表.

--databases : 指定執(zhí)行同步的數(shù)據(jù)庫垃僚。

--tables ：指定執(zhí)行同步的表，多個用逗號隔開规辱。

--sync-to-master ：指定一個DSN谆棺，即從的IP，他會通過show processlist或show slave status 去自動的找主。

h= ：服務(wù)器地址改淑，命令里有2個ip碍岔，第一次出現(xiàn)的是Master的地址，第2次是Slave的地址朵夏。

u= ：帳號蔼啦。

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 pt-duplicate-key-checker

作用：檢查數(shù)據(jù)庫重復(fù)索引

pt-duplicate-key-checker --database=test? h='10.0.0.51' --user=oldguo --password=123

2.6 pt-kill 語句

場景： 無法正常kill的連接饥侵。

常用參數(shù)說明

--daemonize? 放在后臺以守護(hù)進(jìn)程的形式運行鸵赫；

--interval? 多久運行一次，單位可以是s,m,h躏升，d等默認(rèn)是s –不加這個默認(rèn)是5秒

--victims 默認(rèn)是oldest,只殺最古老的查詢辩棒。這是防止被查殺是不是真的長時間運行的查詢，他們只是長期等待 這種種匹配按時間查詢膨疏，殺死一個時間最高值一睁。

--all 殺掉所有滿足的線程

--kill-query? ? ? 只殺掉連接執(zhí)行的語句，但是線程不會被終止

--print? ? ? ? ? ? ? 打印滿足條件的語句

--busy-time 批次查詢已運行的時間超過這個時間的線程佃却；

--idle-time 殺掉sleep 空閑了多少時間的連接線程者吁，必須在--match-command sleep時才有效—也就是匹配使用 -- –match-command 匹配相關(guān)的語句。

----ignore-command 忽略相關(guān)的匹配饲帅。 這兩個搭配使用一定是ignore-commandd在前 match-command在后复凳，

--match-db cdelzone 匹配哪個庫

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 超過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.*語句開頭的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庫币呵，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 &

### 指定哪個用戶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 &

### 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 &

7. 顯示主從結(jié)構(gòu)：pt-slave-find

[root@db01 tmp]#? pt-slave-find -h10.0.0.51? -P3306 -uchecksum -pchecksum

10.0.0.51

Version? ? ? ? 5.7.28-log

Server ID? ? ? 51

Uptime? ? ? ? ? 27:57 (started 2020-05-15T13:24:15)

Replication? ? Is not a slave, has 1 slaves connected, is not read_only

Filters? ? ? ?

Binary logging? ROW

Slave status? ?

Slave mode? ? ? STRICT

Auto-increment? increment 1, offset 1

InnoDB version? 5.7.28

+- 10.0.0.52

? Version? ? ? ? 5.7.28-log

? Server ID? ? ? 52

? Uptime? ? ? ? ? 28:18 (started 2020-05-15T13:23:54)

? Replication? ? Is a slave, has 0 slaves connected, is not read_only

? Filters? ? ? ?

? Binary logging? ROW

? Slave status? ? 0 seconds behind, running, no errors

? Slave mode? ? ? STRICT

? Auto-increment? increment 1, offset 1

? InnoDB version? 5.7.28

[root@db01 tmp]#

8. 監(jiān)控主從延時

# pt-heartbeat

主庫：

pt-heartbeat --user=oldguo --ask-pass --host=10.0.0.51 --port=3306 --create-table -D test? --interval=1 --update --replace --daemonize

從庫：

pt-heartbeat --user=oldguo --ask-pass --host=10.0.0.52 --port=3306? -D test? --table=heartbeat --monitor

9. # pt-show-grants

作用:? 用戶和權(quán)限信息遷移怀愧。

pt-show-grants -h10.0.0.51? -P3306? -uchecksum -pchecksum

-- Grants dumped by pt-show-grants

-- Dumped from server 10.0.0.51 via TCP/IP, MySQL 5.7.28-log at 2020-05-15 17:11:06

-- Grants for 'checksum'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%';

ALTER USER 'checksum'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.0.0.%';

-- Grants for 'mysql.session'@'localhost'

CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';

ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;

GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';

GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';

GRANT SUPER ON *.* TO 'mysql.session'@'localhost';

-- Grants for 'mysql.sys'@'localhost'

CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';

ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;

GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';

GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';

GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';

-- Grants for 'repl'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%';

ALTER USER 'repl'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

-- Grants for 'root'@'10.0.0.%'

CREATE USER IF NOT EXISTS 'root'@'10.0.0.%';

ALTER USER 'root'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%';

-- Grants for 'root'@'localhost'

CREATE USER IF NOT EXISTS 'root'@'localhost';

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

二侨颈、 MySQL全面優(yōu)化

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

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

為了獲得成就感?

為了證實比系統(tǒng)設(shè)計者更懂?dāng)?shù)據(jù)庫?

為了從優(yōu)化成果來證實優(yōu)化者更有價值?

但通常事實證實的結(jié)果往往會和您期待相反芯义！

優(yōu)化有風(fēng)險哈垢，涉足需謹(jǐn)慎！

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

優(yōu)化不總是對一個單純的環(huán)境進(jìn)行扛拨！還很可能是一個復(fù)雜的已投產(chǎn)的系統(tǒng)耘分。

優(yōu)化手段本來就有很大的風(fēng)險，只不過你沒能力意識到和預(yù)見到绑警！

任何的技術(shù)可以解決一個問題求泰，但必然存在帶來一個問題的風(fēng)險！

對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果计盒。

保持現(xiàn)狀或出現(xiàn)更差的情況都是失斂势怠！

穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要章郁！

優(yōu)化不可避免涉及到變更枉氮，變更就有風(fēng)險志衍！

優(yōu)化使性能變好暖庄，維持和變差是等概率事件！

優(yōu)化不能只是數(shù)據(jù)庫管理員擔(dān)當(dāng)風(fēng)險楼肪，但會所有的人分享優(yōu)化成果培廓！

所以優(yōu)化工作是由業(yè)務(wù)需要驅(qū)使的！４航小肩钠！

1.3 誰參與優(yōu)化

數(shù)據(jù)庫管理員

業(yè)務(wù)部門代表

應(yīng)用程序架構(gòu)師

應(yīng)用程序設(shè)計人員

應(yīng)用程序開發(fā)人員

硬件及系統(tǒng)管理員

存儲管理員

1.4 優(yōu)化方向

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

性能優(yōu)化（業(yè)務(wù)高效性）

1.5 優(yōu)化的思路

基礎(chǔ)設(shè)施： 存儲、主機(jī)和操作系統(tǒng):

? ? 主機(jī)架構(gòu)穩(wěn)定性

? ? I/O規(guī)劃及配置

? ? Swap

? ? OS內(nèi)核參數(shù)

? ? 網(wǎng)絡(luò)問題

數(shù)據(jù)庫實例優(yōu)化:（數(shù)據(jù)庫設(shè)計暂殖、參數(shù)）

? ? 內(nèi)存

? ? 數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)

? ? 實例配置

業(yè)務(wù)層面:（Index价匠，lock，session）

schema 設(shè)計

? ? 應(yīng)用程序穩(wěn)定性和性能

? ? SQL語句性能

? ? 串行訪問資源

? ? 性能欠佳會話管理

架構(gòu)優(yōu)化：

高可用

讀寫分離

分布式

NoSQL

面試題： 你對數(shù)據(jù)庫系統(tǒng)都做過哪些優(yōu)化

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

2.1 系統(tǒng)層面的

OS層面你關(guān)注哪些指標(biāo)呛每？

CPU \ MEM? \IO

2.1.1 CPU ： 進(jìn)程和線程CPU利用

top

top 都要看什么踩窖？

%Cpu(s):? 0.1 us,? 0.1 sy,? 0.0 ni, 99.8 id,? 0.0 wa,? 0.0 hi,? 0.0 si,? 0.0 st

us ： 用戶程序，工作期間占用的cpu的占比實時狀態(tài)晨横。

CPU干正事的時間洋腮。

sy ： 系統(tǒng)程序？ 工作期間占用的cpu的占比實時狀態(tài)手形。

內(nèi)核態(tài)的程序運行時占用的cpu半分比啥供。

資源的監(jiān)控，分配库糠，回收伙狐，維護(hù)等工作。

=================================

如果MySQL數(shù)據(jù)庫服務(wù)器，SYS高可能是什么問題導(dǎo)致的贷屎？

1. 高并發(fā)窒百，會話過多。

2. 鎖豫尽。

3. 大表全表掃描篙梢。

4. 參數(shù)配置。

=================================

id ： 空閑

wa ： cpu花在等待上的時間百分比

等待誰美旧？IO

================

高并發(fā)渤滞，大事務(wù)。

raid 存儲規(guī)劃問題

緩存過小

全表掃描較多

隨機(jī)IO過多

================

%CPU 某程序?qū)τ贑PU使用的總占比榴嗅。

ps? -ef |grep mysqld ---> 1446

[root@db01 ~]# top -Hp 1446? ----> 12222(THREAD_OS_ID)----> PS.threads ---> thread_id(SQL) ---> PS.events_statements_history

select? SQL_TEXT? from events_statements_history where? THREAD_ID=28

2.1.2 MEM

KiB Mem :? 8155004 total,? 7631840 free,? 354772 used,? 168392 buff/cache? ? 7573204 avail Mem

KiB Swap:? 2097148 total,? 2097148 free,? ? ? ? 0 used.? ?

2.1.3 IO

[root@db01 ~]# iostat -dk 1

[root@db01 ~]# dd if=/dev/zero of=/mnt/bigfile bs=1M count=2048

2.2 數(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)化建議

3.1.1 主機(jī)

# 服務(wù)器品牌

真實的硬件（X86 PC Server）: DELL R系列 妄呕，華為，浪潮嗽测，HP绪励，聯(lián)想(IBM)

云產(chǎn)品：ECS、數(shù)據(jù)庫RDS唠粥、DRDS疏魏、polarDB

IBM 小型機(jī) P6? 570? 595? P7 720? 750 780? ? P8

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

OLTP? ： 在線事務(wù)處理 IO密集型，線上系統(tǒng)晤愧，OLTP主要是IO密集型的業(yè)務(wù)大莫，高并發(fā) ，E系列（至強(qiáng)）官份，主頻相對低只厘，核心數(shù)量多

OLAP? :? CPU密集型：數(shù)據(jù)分析數(shù)據(jù)處理，OLAP舅巷，cpu密集型的羔味，需要CPU高計算能力（i系列，IBM power系列 I 系列的钠右，主頻很高赋元，核心少 )

# 內(nèi)存

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

# 磁盤選擇

SATA-III? , SAS? ,? Fc? , SSD（sata）, pci-e? ssd , Flash

db01 [(none)]>show variables like '%innodb_io%'

? ? -> ;

+------------------------+-------+

| Variable_name? ? ? ? ? | Value |

+------------------------+-------+

| innodb_io_capacity? ? | 200? |

| innodb_io_capacity_max | 2000? |

+------------------------+-------+

2 rows in set (0.00 sec)

db01 [(none)]>

innodb_io_capacity? 磁盤類型

200-500? ? ? :? ? 普通 SATA III

500-2000? ? ? ：? ? SAS - SAS*6 raid

2000- 5000? ? :? ? SSD?

5000-8000? ? :? ? Flash

# 主機(jī) RAID卡的 BBU(Battery Backup Unit)關(guān)閉

案例 1 ： 有規(guī)律的一段時間，會產(chǎn)生性能低谷

場景：

每隔 28-90天 一段時間的性能低谷（ 1s - 3s+）

在MySQL日常運行期間爬舰，業(yè)務(wù)上反映们陆，每隔一段時間會出現(xiàn)性能下降的情況。

經(jīng)過監(jiān)控排查情屹，業(yè)務(wù)的并發(fā)QPS平均值在1000-1200左右坪仇。并不是太高。

平時的slowlog有部分慢查詢垃你，但放到MySQL端直接執(zhí)行都比較快椅文。

原因1：? 有規(guī)律

raid卡充放電（28-90天）喂很，WB-WT轉(zhuǎn)換

Raid卡 Consistency Check 一致性檢查 (168H) ， Patrol Read（168H） 巡讀

建議聯(lián)系: 系統(tǒng)管理員 關(guān)閉相應(yīng)功能

了解一下： LSI卡的管理命令 , MegaCli命令

原因2：

統(tǒng)計信息過舊,導(dǎo)致索引失效

# 存儲

根據(jù)存儲數(shù)據(jù)種類的不同皆刺，選擇不同的存儲設(shè)備

配置合理的RAID級別(raid5少辣、raid10、熱備盤)?

r0 :條帶化 ,性能高

r1 :鏡像羡蛾，安全

r5 :校驗+條帶化漓帅，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）

r10：安全+性能都很高痴怨，最少四塊盤忙干，浪費一半的空間（高IO要求）

避免使用LVM技術(shù)。

斷電數(shù)據(jù)損壞浪藻，修復(fù)復(fù)雜捐迫。

過度條帶化會增加IOPS壓力

# 網(wǎng)絡(luò)

1、硬件買好的（單卡單口）

2爱葵、網(wǎng)卡綁定（bonding）施戴，交換機(jī)堆疊

以上問題，提前規(guī)劃好萌丈。

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

# Swap調(diào)整

swap 被使用的策略 赞哗，通過以下參數(shù)控制

cat /proc/sys/vm/swappiness

C7 : 30? ? 使用量 70%

C6 : 60? ? 使用量 40%

echo 0 >/proc/sys/vm/swappiness? 的內(nèi)容改成0（臨時）

vim /etc/sysctl.conf

#添加：

vm.swappiness=0（永久）

sysctl -p

這個參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache浓瞪。

在內(nèi)存緊張的情況下懈玻，數(shù)值越低越傾向于釋放文件系統(tǒng)cache巧婶。

當(dāng)然乾颁，這個參數(shù)只能減少使用swap的概率，并不能避免Linux使用swap艺栈。

# IO調(diào)度策略

centos 7 默認(rèn)是deadline 英岭，比較適合于機(jī)械類的磁盤.

cat? /sys/block/sda/queue/scheduler

說明: 如果是SSD或者flash ,我們建議電梯(noop)的調(diào)度方式.

#臨時修改為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. 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='test' \

--query="select * from test.t100w where k2='FGCD'" engine=innodb \

--number-of-queries=2000 -uoldguo -p123 -verbose

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

4.1 Max_connections *****

（1）簡介

Mysql的最大連接數(shù)毅人，如果服務(wù)器的并發(fā)請求量比較大吭狡，可以調(diào)高這個值，當(dāng)然這是要建立在機(jī)器能夠支撐的情況下丈莺，因為如果連接數(shù)越來越多划煮，mysql會為每個連接提供緩沖區(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

補充:

? ? 1.開啟數(shù)據(jù)庫時,我們可以臨時設(shè)置一個比較大的測試值

? ? 2.觀察show status like 'Max_used_connections';變化

? ? 3.如果max_used_connections跟max_connections相同,

? ? 那么就是max_connections設(shè)置過低或者超過服務(wù)器的負(fù)載上限了，

? ? 低于10%則設(shè)置過大.

案例二：

連接數(shù)設(shè)置不生效的問題蟹略，214問題登失。

*　　soft　　nofile　　65536

*　　hard　　nofile　　65536

5.2 back_log ***

（1）簡介

mysql能暫存的連接數(shù)量，當(dāng)主要mysql線程在一個很短時間內(nèi)得到非常多的連接請求時候它就會起作用挖炬，如果mysql的連接數(shù)據(jù)達(dá)到max_connections時候揽浙，新來的請求將會被存在堆棧中，等待某一連接釋放資源意敛，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過back_log捏萍，將不被授予連接資源。

back_log值指出在mysql暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在推棧中空闲，只有如果期望在一個短時間內(nèi)有很多連接的時候需要增加它

（2）判斷依據(jù)

show full processlist

發(fā)現(xiàn)大量的待連接進(jìn)程時令杈，就需要加大back_log或者加大max_connections的值

（3）修改方式舉例

vim /etc/my.cnf

back_log=1024

5.3 wait_timeout和interactive_timeout ****

（1）簡介

wait_timeout：指的是mysql在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)

interactive_timeout：指的是mysql在關(guān)閉一個交互的連接之前所需要等待的秒數(shù)，比如我們在終端上進(jìn)行mysql管理碴倾，使用的即使交互的連接逗噩，這時候，如果沒有操作的時間超過了interactive_time設(shè)置的時間就會自動的斷開跌榔，默認(rèn)的是28800异雁，可調(diào)優(yōu)為7200。

wait_timeout:如果設(shè)置太小僧须，那么連接關(guān)閉的就很快纲刀，從而使一些持久的連接不起作用

（2）設(shè)置建議

如果設(shè)置太大，容易造成連接打開時間過長担平，在show processlist時候示绊，能看到很多的連接 ，一般希望wait_timeout盡可能低

（3）修改方式舉例

wait_timeout=120

interactive_timeout=7200

長連接的應(yīng)用暂论，為了不去反復(fù)的回收和分配資源面褐，降低額外的開銷。

一般我們會將wait_timeout設(shè)定比較小取胎，interactive_timeout要和應(yīng)用開發(fā)人員溝通長鏈接的應(yīng)用是否很多展哭。如果他需要長鏈接节芥，那么這個值可以不需要調(diào)整辙售。

另外還可以使用類外的參數(shù)彌補枫绅。

案例三：MySQL 連接長時間(7200和1200秒)無法釋放

場景： MySQL 5.7? 浅碾， DELL730 E5-2650? 96G內(nèi)存? 1主2從

Keepalive + LVS + 1主 2從

處理方法：

ipvsadmin -l -timeout?

Timeout (tcp? tcpfin? udp ):? 90 120 300

net.ipv4.tcp_keepalive_time = 60?

5.4 key_buffer_size *****

（1）簡介

key_buffer_size指定索引緩沖區(qū)的大小留储，它決定索引處理的速度君旦，尤其是索引讀的速度

《1》此參數(shù)與myisam表的索引有關(guān)

《2》臨時表的創(chuàng)建有關(guān)（多表鏈接夯巷、子查詢中键兜、union）

? ? 在有以上查詢語句出現(xiàn)的時候秧饮，需要創(chuàng)建臨時表映挂，用完之后會被丟棄

? ? 臨時表有兩種創(chuàng)建方式：

? ? ? ? ? ? ? ? ? ? ? ? 內(nèi)存中------->key_buffer_size

? ? ? ? ? ? ? ? ? ? ? ? 磁盤上------->ibdata1(5.6)

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ibtmp1 (5.7）

注：key_buffer_size只對myisam表起作用泽篮，即使不使用myisam表，但是內(nèi)部的臨時磁盤表是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)

或者已各自的一個時段內(nèi)的差額計算鞍时，來判斷基于內(nèi)存的臨時表利用率亏拉。所以，我們會比較關(guān)注 Created_tmp_disk_tables 是否過多逆巍，從而認(rèn)定當(dāng)前服務(wù)器運行狀況的優(yōu)劣及塘。

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

控制在5%-10%以內(nèi)

看以下例子：

在調(diào)用mysqldump備份數(shù)據(jù)時，大概執(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í)行計劃中，可以知道它也產(chǎn)生了 Tmp_table_on_disk灵再。

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

（3）配置方法

key_buffer_size=64M

5.5 query_cache_size ***

（1）簡介：

查詢緩存簡稱QC，主要緩存SQL語句hash值+執(zhí)行結(jié)果痴腌。

10條語句雌团，經(jīng)常做查詢。

案例四 ： 開QC 衷掷，導(dǎo)致性能降低辱姨。 QPS ，TPS降低戚嗅。

沒開起的時候。QPS 2000 TPS 500

開了之后直接降低到 800枢舶，200

為什么呢懦胞？

分區(qū)表。Query Cache 不支持凉泄。

5.6 sort_buffer_size ***

（1）簡介：

每個需要進(jìn)行排序的線程分配該大小的一個緩沖區(qū)躏尉。增加這值加速

ORDER BY

GROUP BY

distinct

union

（2）配置依據(jù)

Sort_Buffer_Size并不是越大越好，由于是connection級的參數(shù)后众，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源胀糜。

列如：500個連接將會消耗500*sort_buffer_size（2M）=1G內(nèi)存

（3）配置方法

修改/etc/my.cnf文件颅拦，在[mysqld]下面添加如下：

sort_buffer_size=1M

建議： 盡量排序能夠使用索引更好。

5.7 max_allowed_packet *****

（1）簡介：

mysql根據(jù)配置文件會限制教藻，server接受的數(shù)據(jù)包大小距帅。

（2）配置依據(jù)：

有時候大的插入和更新會受max_allowed_packet參數(shù)限制，導(dǎo)致寫入或者更新失敗括堤，更大值是1GB碌秸，必須設(shè)置1024的倍數(shù)

（3）配置方法：

max_allowed_packet=32M

5.8 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)存也是每個連接獨享讥电。

盡量在SQL與方面進(jìn)行優(yōu)化，效果較為明顯轧抗。

優(yōu)化的方法：在on條件列加索引恩敌，至少應(yīng)當(dāng)是有MUL索引

建議： 盡量能夠使用索引優(yōu)化更好。

5.9 thread_cache_size = 16 *****

(1)簡介

服務(wù)器線程緩存横媚，這個值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時,那么客戶端的線程將被放到緩存中以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限),如果線程重新被請求潮剪，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程分唾，增加這個值可以改善系統(tǒng)性能.

（2）配置依據(jù)

通過比較 Connections 和 Threads_created 狀態(tài)的變量抗碰，可以看到這個變量的作用。

設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8绽乔，2GB配置為16弧蝇，3GB配置為32，4GB或更高內(nèi)存折砸，可配置更大看疗。

服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(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)前此時此刻線程緩存中有多少空閑線程。

Threads_connected:代表當(dāng)前已建立連接的數(shù)量睦授，因為一個連接就需要一個線程两芳，所以也可以看成當(dāng)前被使用的線程數(shù)。

Threads_created:代表從最近一次服務(wù)啟動去枷，已創(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ù)特咆，有時候連接已建立，但是連接處于sleep狀態(tài)录粱。

(3)配置方法：

thread_cache_size=32

整理：

Threads_created? ：一般在架構(gòu)設(shè)計階段腻格，會設(shè)置一個測試值画拾，做壓力測試。

結(jié)合zabbix監(jiān)控菜职，看一段時間內(nèi)此狀態(tài)的變化青抛。

如果在一段時間內(nèi)，Threads_created趨于平穩(wěn)些楣，說明對應(yīng)參數(shù)設(shè)定是OK脂凶。

如果一直陡峭的增長，或者出現(xiàn)大量峰值愁茁，那么繼續(xù)增加此值的大小蚕钦，在系統(tǒng)資源夠用的情況下（內(nèi)存）

5.10 innodb_buffer_pool_size *****

（1）簡介

對于InnoDB表來說，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對于MyISAM表的作用一樣鹅很。

（2）配置依據(jù)：

InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引嘶居。

對于單獨的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%,一般我們建議不要超過物理內(nèi)存的70%促煮。

（3）配置方法

innodb_buffer_pool_size=2048M

5.11 innodb_flush_log_at_trx_commit ******

（1）簡介

主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點邮屁，取值分別為0、1菠齿、2三個佑吝。

0，表示當(dāng)事務(wù)提交時绳匀，不做日志寫入操作芋忿，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush&sync磁盤一次；

1疾棵，

每次事務(wù)的提交都會引起redo日志文件寫入戈钢、flush&sync磁盤的操作，確保了事務(wù)的ACID是尔；

2殉了，每次事務(wù)提交引起寫入日志文件的動作,但每秒鐘完成一次sync磁盤操作。

（2）配置依據(jù)

實際測試發(fā)現(xiàn)拟枚，該值對插入數(shù)據(jù)的速度影響非常大薪铜，設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒梨州，而設(shè)置為1時則需要229秒痕囱。因此，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)暴匠，這樣可以大幅提高速度。

根據(jù)MySQL官方文檔傻粘，在允許丟失最近部分事務(wù)的危險的前提下每窖，可以把該值設(shè)為0或2帮掉。

（3）配置方法

innodb_flush_log_at_trx_commit=1

雙1標(biāo)準(zhǔn)中的一個1

5.12 innodb_thread_concurrency ***

（1）簡介

此參數(shù)用來設(shè)置innodb線程的并發(fā)數(shù)量，默認(rèn)值為0表示不限制窒典。

（2）配置依據(jù)

在官方doc上蟆炊，對于innodb_thread_concurrency的使用，也給出了一些建議瀑志，如下：

如果一個工作負(fù)載中涩搓，并發(fā)用戶線程的數(shù)量小于64，建議設(shè)置innodb_thread_concurrency=0劈猪；

如果工作負(fù)載一直較為嚴(yán)重甚至偶爾達(dá)到頂峰昧甘，建議先設(shè)置innodb_thread_concurrency=128，

并通過不斷的降低這個參數(shù)战得，96, 80, 64等等充边，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)，

例如常侦，假設(shè)系統(tǒng)通常有40到50個用戶浇冰，但定期的數(shù)量增加至60，70聋亡，甚至200肘习。你會發(fā)現(xiàn)，

性能在80個并發(fā)用戶設(shè)置時表現(xiàn)穩(wěn)定坡倔，如果高于這個數(shù)漂佩，性能反而下降。在這種情況下致讥，

建議設(shè)置innodb_thread_concurrency參數(shù)為80仅仆，以避免影響性能。

如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶線程使用的虛擬CPU更多（比如20個虛擬CPU）垢袱，

建議通過設(shè)置innodb_thread_concurrency 參數(shù)為這個值（也可能更低墓拜，這取決于性能體現(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 ,觀察每個cpu的各自的負(fù)載情況

? ? 2. 發(fā)現(xiàn)不平均,先設(shè)置參數(shù)為cpu個數(shù),然后不斷增加(一倍)這個數(shù)值

? ? 3. 一直觀察top狀態(tài),直到達(dá)到比較均勻時,說明已經(jīng)到位了.

5.1.3 innodb_log_buffer_size

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

innodb_log_buffer_size=128M

設(shè)定依據(jù)：

1量承、大事務(wù)： 存儲過程調(diào)用 CALL

2、多事務(wù)

5.14 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)方式將日志文件寫到多個文件泣洞。推薦設(shè)置為3

read_buffer_size = 1M **

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

read_rnd_buffer_size = 1M **

MySql的隨機(jī)讀（查詢操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(例如甩挫，按照排序順序)贴硫，將分配一個隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時伊者，MySql會首先掃描一遍該緩沖英遭，以避免磁盤搜索，提高查詢速度亦渗，如果需要排序大量數(shù)據(jù)挖诸，可適當(dāng)調(diào)高該值。但MySql會為每個客戶連接發(fā)放該緩沖空間法精，所以應(yīng)盡量適當(dāng)設(shè)置該值多律，以避免內(nèi)存開銷過大痴突。

注：順序讀是指根據(jù)索引的葉節(jié)點數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點中的主鍵尋找實際行數(shù)據(jù)菱涤，而輔助索引和主鍵所在的數(shù)據(jù)段不同苞也，因此訪問方式是隨機(jī)的洛勉。

bulk_insert_buffer_size = 8M **

批量插入數(shù)據(jù)緩存大小粘秆，可以有效提高插入效率，默認(rèn)為8M

tokuDB? ? percona

myrocks?

RocksDB

TiDB

MongoDB

binary log *****

雙1標(biāo)準(zhǔn)(基于安全的控制)：

sync_binlog=1? 什么時候刷新binlog到磁盤收毫，每次事務(wù)commit

innodb_flush_log_at_trx_commit=1

安全參數(shù) *****

Innodb_flush_method=(O_DIRECT, fsync)

1攻走、fsync? ? ：

（1）在數(shù)據(jù)頁需要持久化時，首先將數(shù)據(jù)寫入OS buffer中此再，然后由os決定什么時候?qū)懭氪疟P

（2）在redo buffuer需要持久化時昔搂，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候?qū)懭氪疟P

但输拇，如果innodb_flush_log_at_trx_commit=1的話摘符，日志還是直接每次commit直接寫入磁盤

2、 Innodb_flush_method=O_DIRECT

（1）在數(shù)據(jù)頁需要持久化時策吠，直接寫入磁盤

（2）在redo buffuer需要持久化時逛裤，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候?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=4096M

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