優(yōu)化介紹
準備一臺MySQL的虛擬機環(huán)境，最好修改為4G內(nèi)存，4核CPU
image
啟動3306節(jié)點的 mysql,并導入t100w的庫
環(huán)境準備
cpu:4 , mem:8G , mysql:5.7.26全新環(huán)境
壓力測試準備
/etc/init.d/mysqld start
mysql < t100w.sql
mysqladmin -uroot -p password 123
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
image
優(yōu)化哲學
為什么
`為了獲得成就感?
為了證實比系統(tǒng)設(shè)計者更懂數(shù)據(jù)庫?
為了從優(yōu)化成果來證實優(yōu)化者更有價值?
NO!NO!NO!NO!NO!NO!
但通常事實證實的結(jié)果往往會和您期待相反！
優(yōu)化有風險，涉足需謹慎！`
優(yōu)化風險
優(yōu)化不總是對一個單純的環(huán)境進行！還很可能是一個復雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風險，只不過你沒能力意識到和預(yù)見到！
任何的技術(shù)可以解決一個問題，但必然存在帶來一個問題的風險！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失??！
穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要！
優(yōu)化不可避免涉及到變更，變更就有風險！
優(yōu)化使性能變好，維持和變差是等概率事件！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔當風險，但會所有的人分享優(yōu)化成果！
所以優(yōu)化工作是由業(yè)務(wù)需要驅(qū)使的?。?！
誰參與
DBA—數(shù)據(jù)庫管理員(系統(tǒng),硬件,網(wǎng)路,存儲,代碼,架構(gòu)思維)
數(shù)據(jù)庫管理員
業(yè)務(wù)部門代表
應(yīng)用程序架構(gòu)師
應(yīng)用程序設(shè)計人員
應(yīng)用程序開發(fā)人員
硬件及系統(tǒng)管理員
存儲管理員
優(yōu)化的方向
安全方向優(yōu)化（業(yè)務(wù)持續(xù)性）
性能方向優(yōu)化（業(yè)務(wù)高效性）
優(yōu)化的范圍及思路
優(yōu)化范圍：
存儲、主機和操作系統(tǒng):
主機架構(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è)計、參數(shù)）
內(nèi)存
數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)
實例配置
架構(gòu)設(shè)計 :
性能 : 讀寫分離,分布式
安全 : 主備,多活
安全&性能 : 分布式,NewSQL
image
優(yōu)化工具和命令
系統(tǒng)層面
CPU：top
top命令：
%CPU %MEM COMMAND
400.0 4.3 mysqld
%Cpu(s): 99.0 us, 1.0 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
按1鍵 展開:
%Cpu0 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu(s): 99.0 us, 1.0 sy, 0.0 id, 0.0 wa
0.0 id : CPU空閑的百分比
5% 以上,還是有空閑的.
99.0 us : 用戶程序占用的CPU百分比
對于mysql程序,處理數(shù)據(jù).
1.0 sy : 內(nèi)核程序占用的CPU百分比
資源管理,資源調(diào)度等,計算.
sys過高: 鎖,并發(fā)連接數(shù)較高,數(shù)據(jù)處理分析應(yīng)用
0.0 wa : 花在等待的CPU時間
IO方面出了問題
鎖.大事務(wù)
IO本身: IOPS達到峰值
[root@db01 ~]# top -H -p 9846
MEM
Swap: 1560568 total, 1560568 free, 0 used. 7386000 avail Mem
IO:
[root@db01 ~]# iostat -dk 1
Linux 3.10.0-957.el7.x86_64 (db01) 08/23/2019 _x86_64_ (4 CPU)
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 1.65 36.24 115.44 223266 711168
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 0.00 0.00 0.00 0 0
數(shù)據(jù)庫層面
基礎(chǔ)優(yōu)化:
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
slowlog
擴展類深度優(yōu)化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
優(yōu)化思路分解
硬件優(yōu)化
真實的硬件（PC Server）: DELL R系列 ，華為，浪潮，HP，聯(lián)想
云產(chǎn)品：ECS、數(shù)據(jù)庫RDS、DRDS
IBM 小型機 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高計算能力（i系列，IBM power系列）
CPU密集型： I 系列的，主頻很高，核心少
IO密集型： E系列（至強），主頻相對低，核心數(shù)量多
內(nèi)存
建議2-3倍cpu核心數(shù)量 （ECC）
磁盤選擇
SATA-III SAS Fc SSD（sata） pci-e ssd Flash
主機 RAID卡的BBU(Battery Backup Unit)關(guān)閉
存儲
根據(jù)存儲數(shù)據(jù)種類的不同，選擇不同的存儲設(shè)備
配置合理的RAID級別(raid5、raid10、熱備盤)
r0 :條帶化 ,性能高
r1 :鏡像，安全
r5 :校驗+條帶化，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高，最少四塊盤，浪費一半的空間（高IO要求）
HDS
EMC2(DELL)
IBM
DELL 華為
網(wǎng)絡(luò)
1、硬件買好的（單卡單口）
2、網(wǎng)卡綁定（bonding），交換機堆疊
主備模式: mode=1
負載均衡: mode=0
注意: 多交換機要做堆疊.
以上問題，提前規(guī)避掉。
操作系統(tǒng)優(yōu)化
Swap調(diào)整
cat /proc/sys/vm/swappiness
臨時
echo 0 >/proc/sys/vm/swappiness
永久
vim /etc/sysctl.conf
vm.swappiness=0
sysctl -p
這個參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache。在內(nèi)存緊張的情況下，數(shù)值越低越傾向于釋放文件系統(tǒng)cache。
當然，這個參數(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 默認是deadline
cat /sys/block/sda/queue/scheduler
#臨時修改為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)化的難度。
應(yīng)用端
- 開發(fā)過程規(guī)范,標準
- 減少爛SQL:不走索引,復雜邏輯,切割大事務(wù).
- 避免業(yè)務(wù)邏輯錯誤,避免鎖爭用.
這個階段,需要我們DBA深入業(yè)務(wù),或者要和開發(fā)人員\業(yè)務(wù)人員配合實現(xiàn)
優(yōu)化,最根本的是"優(yōu)化"人.
----oldguo
MySQL參數(shù)優(yōu)化測試
虛擬機vm12.5，OS centos 6.9（系統(tǒng)已優(yōu)化），cpu4（I5 4440 3.1GHZ）,MEM4GB ,HardDisk:SSD
參數(shù)優(yōu)化細節(jié)：
max_connections 最大連接數(shù)
key_buffer_size 臨時表
innodb_flush_log_at_trx_commit 雙一:redo log的刷寫策略,每次事務(wù)提交時,首先刷寫os buffer,立即刷寫到磁盤.
innodb_buffer_pool_size 數(shù)據(jù)緩沖區(qū)大小,建議不要超過物理內(nèi)存80%
max_allowed_packet 允許最大數(shù)據(jù)包的大小
sync_binlog 雙一:二進制日志的刷寫策略,每次事務(wù)提交理解刷寫二進制日志
innodb_flush_method
刷寫策略:
O_DIRECT :
刷寫buffer pool 繞過OS buffer ,直接刷磁盤
刷寫redo buffer 先到OS buffer ,再到磁盤
Max_connections *****
（1）簡介
Mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)請求量比較大，可以調(diào)高這個值，當然這是要建立在機器能夠支撐的情況下，因為如果連接數(shù)越來越多，mysql會為每個連接提供緩沖區(qū)，就會開銷的越多的內(nèi)存，所以需要適當?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ù)器的負載上限了，
低于10%則設(shè)置過大.
back_log ***
（1）簡介
mysql能暫存的連接數(shù)量，當主要mysql線程在一個很短時間內(nèi)得到非常多的連接請求時候它就會起作用，如果mysql的連接數(shù)據(jù)達到max_connections時候，新來的請求將會被存在堆棧中，等待某一連接釋放資源，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過back_log，將不被授予連接資源。
back_log值指出在mysql暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在推棧中，只有如果期望在一個短時間內(nèi)有很多連接的時候需要增加它
（2）判斷依據(jù)
show full processlist
發(fā)現(xiàn)大量的待連接進程時，就需要加大back_log或者加大max_connections的值
（3）修改方式舉例
vim /etc/my.cnf
back_log=1024
wait_timeout和interactive_timeout ****
（1）簡介
wait_timeout：指的是mysql在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)
interactive_timeout：指的是mysql在關(guān)閉一個交互的連接之前所需要等待的秒數(shù)，比如我們在終端上進行mysql管理，使用的即使交互的連接，這時候，如果沒有操作的時間超過了interactive_time設(shè)置的時間就會自動的斷開，默認的是28800，可調(diào)優(yōu)為7200。
wait_timeout:如果設(shè)置太小，那么連接關(guān)閉的就很快，從而使一些持久的連接不起作用
（2）設(shè)置建議
如果設(shè)置太大，容易造成連接打開時間過長，在show processlist時候，能看到很多的連接 ，一般希望wait_timeout盡可能低
（3）修改方式舉例
wait_timeout=60
interactive_timeout=7200
長連接的應(yīng)用，為了不去反復的回收和分配資源，降低額外的開銷。
一般我們會將wait_timeout設(shè)定比較小，interactive_timeout要和應(yīng)用開發(fā)人員溝通長鏈接的應(yīng)用是否很多。如果他需要長鏈接，那么這個值可以不需要調(diào)整。
另外還可以使用類外的參數(shù)彌補。
key_buffer_size *****
（1）簡介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關(guān)
《2》內(nèi)存臨時表的創(chuàng)建有關(guān)（多表鏈接、子查詢中、union）
在有以上查詢語句出現(xiàn)的時候，需要創(chuàng)建臨時表，用完之后會被丟棄
臨時表有兩種創(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個索引讀取請求，有2個請求在內(nèi)存中沒有找到直接從硬盤中讀取索引
控制在 5%以內(nèi) 。
注：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>
通常地，我們習慣以 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 是否過多，從而認定當前服務(wù)器運行狀況的優(yōu)劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以內(nèi)
參數(shù)優(yōu)化結(jié)果
vim /etc/my.cnf
[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=2048M
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
鎖的監(jiān)控及處理
鎖等待模擬
概念：
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)
vim /etc/my.cnf
innodb_lock_wait_timeout=28800
image
看有沒有鎖等待
SHOW STATUS LIKE 'innodb_row_lock%';
查看哪個事務(wù)在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_query : 被阻塞的語句是誰
trx_mysql_thread_id: 被阻塞的會話ID
查看鎖源,誰鎖的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被鎖的和鎖定它的之間關(guān)系
## waiting_pid : 等待的會話線程號
## waiting_query : 等待的語句
## blocking_pid : 鎖源的線程號 :2
## sql_kill_blocking_query : 處理建議
## sql_kill_blocking_connection: 處理建議
找到鎖源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=2;
## THREAD_ID = 27
找到鎖源的SQL語句
-- 當前在執(zhí)行的語句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=27;
主從優(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
索引
簡單SQL語句
max_connections 最大連接數(shù)
key_buffer_size 臨時表
innodb_flush_log_at_trx_commit 雙一:redo log的刷寫策略,每次事務(wù)提交時,首先刷寫os buffer,立即刷寫到磁盤.
innodb_buffer_pool_size 數(shù)據(jù)緩沖區(qū)大小,建議不要超過物理內(nèi)存80%
max_allowed_packet 允許最大數(shù)據(jù)包的大小
sync_binlog 雙一:二進制日志的刷寫策略,每次事務(wù)提交理解刷寫二進制日志
innodb_flush_method
O_DIRECT :
刷寫buffer pool 繞過OS buffer ,直接刷磁盤
刷寫redo buffer 先到OS buffer ,再到磁盤
優(yōu)化項目:鎖的監(jiān)控及處理
背景:
硬件環(huán)境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10
在例行巡檢時,發(fā)現(xiàn)9-11點時間段的CPU壓力非常高(80-90%)
項目的職責
>1.通過top詳細排查,發(fā)現(xiàn)mysqld進程占比達到了700-800%
2.其中有量的CPU是被用作的SYS和WAIT,us處于正常
3.懷疑是MySQL 鎖 或者SQL語句出了問題
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多個current_waits,說明當前很多鎖等待情況
情況二:
1000多個lock_waits,說明歷史上發(fā)生過的鎖等待很多
5 查看那個事務(wù)在等待(被阻塞了)
6 查看鎖源事務(wù)信息(誰鎖的我)
7 找到鎖源的thread_id
8 找到鎖源的SQL語句
找到語句之后,和應(yīng)用開發(fā)人員進行協(xié)商
>(1)開發(fā)人員描述,此語句是事務(wù)掛起導致
我們提出建議是臨時kill 會話,最終解決問題
(2)
開發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問題導致的死鎖,產(chǎn)生了大量鎖等待
臨時解決方案,將阻塞事務(wù)的會話kill掉.
最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項目結(jié)果:
經(jīng)過排查處理,鎖等待的個數(shù)減少80%.解決了CPU持續(xù)峰值的問題.
鎖監(jiān)控設(shè)計到的命令:
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;
死鎖監(jiān)控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
