微認(rèn)證：基于BoostKit的MySQL性能優(yōu)化

基于BoostKit的MySQL性能優(yōu)化

1. 數(shù)據(jù)庫發(fā)展趨勢(shì)

1.1 數(shù)據(jù)發(fā)展趨勢(shì)

傳統(tǒng)數(shù)據(jù)庫經(jīng)歷從單機(jī)到主備庫，到RAC集群的演化，而RAC集中式架構(gòu)性能擴(kuò)展有限，以互聯(lián)網(wǎng)流派為主分布式數(shù)據(jù)庫應(yīng)對(duì)大并發(fā)量已成為主流

數(shù)據(jù)庫場(chǎng)景每個(gè)線程同時(shí)處理10個(gè)并發(fā)，單RAC節(jié)點(diǎn)處理在1000并發(fā)內(nèi)，RAC架構(gòu)3個(gè)節(jié)點(diǎn)以上線性度以不能擴(kuò)展

互聯(lián)網(wǎng)陣營的阿里Oceanbase，騰訊TD-SQL基于自身業(yè)務(wù)支撐孕育出有生命力的分布式數(shù)據(jù)庫，Ping CAP的分布式TiDB深耕企業(yè)市場(chǎng)

云化數(shù)據(jù)庫存在物理機(jī)多實(shí)例，Docker，虛擬機(jī)多種部署方式，IO延時(shí)挑戰(zhàn)更大，需要更低延時(shí)的網(wǎng)絡(luò)和存儲(chǔ)

1.2 數(shù)據(jù)庫領(lǐng)域架構(gòu)及技術(shù)趨勢(shì)

1.3 TaiShan 數(shù)據(jù)庫解決方案生態(tài)規(guī)劃

1.4 TaiShan + 開源MySQL主流解決方案架構(gòu)

1.5 鯤鵬數(shù)據(jù)庫解決方案優(yōu)勢(shì)

2. MySQL數(shù)據(jù)架構(gòu)介紹

2.1 MySQL介紹

MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，尤其在web應(yīng)用，MySQL是最好的關(guān)系型數(shù)據(jù)庫軟件之一

MySql數(shù)據(jù)庫采用雙授權(quán)政策，分為社區(qū)版和商業(yè)版

MySQL體積小、速度快、成本低，中小型網(wǎng)站通常選擇MySQL作為網(wǎng)站數(shù)據(jù)庫

2.2 MySql數(shù)據(jù)庫架構(gòu)

2.3 MySQL執(zhí)行流程

2.4 InnoDB引擎架構(gòu)

2.5 InnoDB表結(jié)構(gòu)

2.6 InnoDB - 索引組織表

在InnoDB存儲(chǔ)引擎中，表都是根據(jù)主鍵順序組織存放的，這種存儲(chǔ)方式的表組成索引組織表，葉子節(jié)點(diǎn)存儲(chǔ)的是索引值和數(shù)據(jù)本身

2.7 InnoDB - 二級(jí)索引

二級(jí)索引的葉子節(jié)點(diǎn)存儲(chǔ)的是索引值以對(duì)應(yīng)行數(shù)據(jù)的主鍵，通過主鍵索引獲得最終的數(shù)據(jù)

2.8 堆表

堆表就是一般的表，數(shù)據(jù)隨機(jī)存放，由空閑空間決定，無先后之分

數(shù)據(jù)和索引分開存放，索引保存記錄所在位置的rowid

2.9 索引組織表 vs 堆表

索引組織表

數(shù)據(jù)有序存放的，數(shù)據(jù)的位置是預(yù)先預(yù)定好的，與插入順序沒有關(guān)系

查詢效率高，但插入數(shù)據(jù)慢

堆表

寫入速度快

數(shù)據(jù)和索引是分開存放的

數(shù)據(jù)更新可能出現(xiàn)行遷移

2.10 InnoDB特性

Change buffer

用戶緩存非唯一性二級(jí)索引頁變化的數(shù)據(jù)結(jié)構(gòu)，當(dāng)需要修改的非唯一性二級(jí)索引頁不在緩沖池中，將變化的索引頁緩存在change buffer中

兩次寫（double write）

Double write 保證數(shù)據(jù)頁完整的被寫入到數(shù)據(jù)文件中，避免出現(xiàn)部分寫

自適應(yīng)哈希索引（adaptive hash index）

對(duì)頻繁訪問的二級(jí)索引建立哈希索引，提升性能

預(yù)讀機(jī)制（read-ahead）

預(yù)讀操作是一種IO優(yōu)化操作，異步將磁盤中數(shù)據(jù)頁讀到buffer pool中，預(yù)計(jì)這些頁會(huì)馬上被用到

3. MySQL性能影響因素及定位工具

3.1 影響數(shù)據(jù)庫性能因素

硬件及操作系統(tǒng)

CPU、IO、網(wǎng)絡(luò)、內(nèi)存

操作系統(tǒng)參數(shù)

數(shù)據(jù)庫層

數(shù)據(jù)庫參數(shù)

統(tǒng)計(jì)信息

業(yè)務(wù)層

并發(fā)數(shù)及數(shù)據(jù)量

慢SQL

3.2 硬件及操作系統(tǒng)層性能監(jiān)控

操作系統(tǒng)層性能監(jiān)控工具

CPU

top、vmstat

內(nèi)存

top、free

IO

iostat、vmstat

網(wǎng)絡(luò)

Sar -n DEV

SQL

Top SQL

熱點(diǎn)函數(shù)

perf

3.3 MySQL數(shù)據(jù)庫性能監(jiān)控工具

MySQL數(shù)據(jù)庫層定位工具

狀態(tài)變量

慢日志

Show full processlist 查看 session 執(zhí)行狀態(tài)

Show engine innodb status 查看引擎狀態(tài)

Performance_schema 查看性能視圖

SQL語句性能定位工具

Explain 查看執(zhí)行計(jì)劃

Profiling 查看執(zhí)行耗時(shí)

Optimizer trace 查看SQL解析、優(yōu)化、執(zhí)行過程

第三方定位工具

Innotop

Percona-toolkit

Zenoss MySQL Database Zenpack

3.4 MySQL數(shù)據(jù)庫層性能定位工具 - 狀態(tài)變量

MySQL數(shù)據(jù)庫中提供大量狀態(tài)變量

SQL執(zhí)行頻率

數(shù)據(jù)庫的磁盤讀寫

線程連接

Flush臟頁

查看狀態(tài)變量

select * from performance_schema.global_status;

select global status;

3.5 MySQL數(shù)據(jù)庫層性能定位工具 - 慢日志

慢日志是MySQL提供的一種日志記錄，用來記錄MySQL響應(yīng)時(shí)間超過閾值的語句

參數(shù)設(shè)置

Slow_query_log：是否開啟慢日志

Slow_query_log_file：MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯?chǔ)路徑

Long_query_time：慢查詢域值

慢日志分析

MySQL提供了日志分析工具mysqldumpslow

mysqldumpslow可根據(jù)訪問次數(shù)、鎖定時(shí)間、返回記錄、查詢時(shí)間等進(jìn)行排序

3.6 MySQL數(shù)據(jù)庫層性能定位工具 - show processlist

Show processlist/show full processlist 查看當(dāng)前MySQL是否由壓力，當(dāng)前執(zhí)行語句，當(dāng)前語句耗時(shí)等

執(zhí)行方法

show processlist/show full processlist

select * from information_schema.processlist

Column說明

ld：線程ID，使用kill id，殺死線程

db：數(shù)據(jù)庫

User：用戶

Command：當(dāng)前執(zhí)行命令狀態(tài)，sleep、Query、Execute

Time：消耗時(shí)間，單位秒

State：執(zhí)行狀態(tài)，Sending data，update，updating等

Info：執(zhí)行的sql語句

3.7 MySQL數(shù)據(jù)庫層性能定位工具 - performance_schema

監(jiān)控MySQL運(yùn)行過程中的資源消耗及等待情況

查看耗時(shí)語句

SELECT DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 3;

查看文件物理IO開銷

SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 3;

分析當(dāng)前最耗時(shí)的事件

select EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT from events_waits_summary_global_by_event_name order by count_star desc limit 5;

分析具體SQL各階段耗時(shí)

select t2.event_name,t2.THREAD_ID,t2.EVENT_ID,t2.TIMER_WAIT from events_statements_history_long t1,event_stages_history_long t2 where t1.event_id=t2.NESTING_EVENT_ID and t2.sql_next='?';

分析具體某階段耗時(shí)

select t2.event_id,t2.evnet_name,t2.object_name,t2.operation,t2.timer_waitl,t2.nesting_event_id,t1.timer_wait from events_stages_history t1,events_waits_history_long t2 where t1.event_id=t2.nesting_event_id and t1.thread_id='?';

3.8 MySQL InnoDB層性能定位工具 - show engine innodb status

Show engine innodb status 是MySQL提供的一個(gè)用于查看innodb引擎狀態(tài)信息的工具

信號(hào)

最新的外鍵錯(cuò)誤

最新的死鎖信息

事物信息

文件I/O

Insert Buffer 和自適應(yīng)hash

日志

Buffer pool和內(nèi)存

行操作數(shù)據(jù)

3.9 SQL語句性能定位工具 - 執(zhí)行計(jì)劃

執(zhí)行計(jì)劃是SQL在數(shù)據(jù)庫中執(zhí)行時(shí)的表現(xiàn)情況，用于單個(gè)SQL語句的性能分析、優(yōu)化等場(chǎng)景

執(zhí)行計(jì)劃使用explain關(guān)鍵字

執(zhí)行計(jì)劃列信息說明

type：連接使用類型

key：實(shí)際用到的索引

ref：當(dāng)前表在利用key列記錄中的索引進(jìn)行查詢時(shí)用到的行或常量

Rows：執(zhí)行查詢時(shí)估算返回的行數(shù)

Filtered：返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，越大越好，實(shí)際讀取的行數(shù)與需要返回的行數(shù)接近

Extra：額外的信息

3.10 SQL語句性能定位工具 - Profiling

Show profile 是MySQL提供可以用來分析當(dāng)前會(huì)話中語句執(zhí)行的資源消耗情況，用戶SQL調(diào)優(yōu)

開啟profiling：set profiling=1

執(zhí)行show profile 查看最近執(zhí)行的15次sql

執(zhí)行show profile for query query_id 查看具體SQL的執(zhí)行細(xì)節(jié)

Profiling的數(shù)據(jù)存放在information_schema.profiling表里

3.11 SQL語句性能定位工具 - optimizer trace

Optimizer trace 把MySQL Optimizer的決策和執(zhí)行過程輸出成文本，了解每個(gè)SQL是如何被解析、優(yōu)化并到執(zhí)行

開啟 optimizer trace：set optimizer_trace="enable=on"

Optimizer trace的信息存放在 Information_schema.optimize r_trace 表中

3.12 MySQL數(shù)據(jù)庫性能問題定位思路

4. MySQL優(yōu)化思路、

4.1 鯤鵬平臺(tái)性能優(yōu)化

關(guān)閉SMMU

進(jìn)入BIOS -> “Advanced > MISC Config” -> “Support Smmu” 設(shè)置 Disable

關(guān)閉預(yù)取

進(jìn)入BIOS -> “Advanced > MISC Config” -> “CPU Prefetching Configurtion” 設(shè)置 Disable

關(guān)閉irqbalance

systemctl stop irpbalance.service

systemctl disable irqbalance.servlce

systemctl status irqbalance.service

網(wǎng)卡中斷綁核

查詢中斷號(hào)

cat /proc/interrupts | grep $eth

手動(dòng)綁定中斷到不同CPU

echo $cpunum > /proc/irq/$irq/smp_affinity_list

4.2 編譯優(yōu)化

目前在ARM架構(gòu)，開源數(shù)據(jù)庫只有部分版本由ARM版本，其他版本都要通過源碼編譯安裝

源碼編譯安裝提供了更多的選擇，可以根據(jù)實(shí)際應(yīng)用場(chǎng)景選擇編譯選項(xiàng)，例如

MySQL數(shù)據(jù)庫支持多種字符集和多種引擎，可以根據(jù)實(shí)際情況只安裝需要的字符集和引擎

-with-extra-charsets=none

將MySQL編譯成靜態(tài)執(zhí)行文件而無需共享頁也能獲得更好的性能

-with-mysqld-ldflags=-all-static

根據(jù)ARM架構(gòu)，編譯的時(shí)候添加適配于ARM指令也能提供更好的性能

DCMAKE_C_FLAGS="-march=armv8.2-a+lse -mtune=tsv110"

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

MySQL內(nèi)存參數(shù)優(yōu)化

Innodb_buffer_pool_size

Innodb_buffer_pool_instance

Innodb_log_file_size

并發(fā)控制參數(shù)優(yōu)化

Innodb_thread_concurrency

Innodb_spin_wait_delay

Innodb_sync_spin_loops

Innodb_spin_wait_pause_multiplier

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

Innodb_io_capacity

Innodb_io_capacity_max

4.4 jdbc 連接優(yōu)化

useServerPrepStmts=true

Server端開啟 prepare statement，提升解析效率

cachePrepStmts=true

開啟每個(gè)連接緩存 prepareStatement

preStmtCacheSize

緩存prepareStatement對(duì)象個(gè)數(shù)

preStmtCacheSqlLimit

prepareStatement對(duì)象大小

4.5 表結(jié)構(gòu)及SQL優(yōu)化

優(yōu)化目標(biāo)

減少IO次數(shù)：IO永遠(yuǎn)是數(shù)據(jù)庫的瓶頸，數(shù)據(jù)庫90%的時(shí)間都是被IO操作所占用

減少CPU計(jì)算：優(yōu)化CPU 運(yùn)算量

優(yōu)化原則

盡量使用索引訪問數(shù)據(jù)，減少IO操作

創(chuàng)建高性能索引，避免過多索引引起磁盤利用率以過高內(nèi)存占用，如果創(chuàng)建索引index key1(c1,c2,c3)，那么索引 index key2(c1,c2)->不需要 index key3(c1)->不需要

長字段索引，考慮創(chuàng)建前綴索引

避免復(fù)雜的多表join

減少參與排序的數(shù)據(jù)量或者不必要的排序，減少CPU計(jì)算

5. 性能優(yōu)化案例分享

5.1 MySQL數(shù)據(jù)庫優(yōu)化案例 - 參數(shù)優(yōu)化

問題現(xiàn)象

MySQL數(shù)據(jù)庫高并發(fā)讀場(chǎng)景下，并發(fā)壓力增加，數(shù)據(jù)庫性能無提升，CPU、IO、網(wǎng)絡(luò)均不存在瓶頸問題

問題分析

發(fā)現(xiàn)table_cache_hits命中率非常低，狀態(tài)變量Open_tables超過參數(shù)參數(shù)設(shè)置

優(yōu)化方法

調(diào)整參數(shù)table_open_cache=10000

5.2 MySQL數(shù)據(jù)優(yōu)化案例 - 回表優(yōu)化

問題現(xiàn)象

某web平臺(tái)的訂單查詢系統(tǒng)，在高并發(fā)下，數(shù)據(jù)庫服務(wù)器的CPU利用率達(dá)到90%，且?guī)缀醵急籑ySQL占用

問題分析

show full processlist 查看運(yùn)行SQL

查看SQL執(zhí)行計(jì)劃

Extra列顯示 “using index condition”，通過 O_ICAT_IDX 非唯一性索引過濾數(shù)據(jù)，再到基表找到所有符合條件的行

輔助索引訪問路徑

優(yōu)化方法

創(chuàng)建覆蓋索引

CREATE INDEX I_ID_1 on O_ITEM(I_CATEGORY,I_ID,I_DESC,I_DESCOUNT,I_NAME,I_PRICE,I_VERSION)

5.3 MySQL數(shù)據(jù)庫優(yōu)化案例 - spinlock優(yōu)化

問題現(xiàn)象

使用 benchmarksql 進(jìn)行tpcc測(cè)試，tpmC指標(biāo)一直比較低，CPU利用率大概在70%左右

問題分析

Perf工具查看熱點(diǎn)函數(shù)，熱點(diǎn)函數(shù)主要集中在queued_spin_lock_slowpath

Show engine innodb mutex 查看 mutex 爭(zhēng)用

優(yōu)化方法

MySQL的互斥自旋鎖是使用自帶的TTASEventMutex，與OS的spinlock相比，MySQL的自旋鎖支持自定義自旋時(shí)間，自旋結(jié)束后會(huì)釋放CPU時(shí)間片，讓其他的進(jìn)程使用CPU資源。MySQL的兩大熱點(diǎn)鎖 lock_sys->mutex 和 trx_sys->mutex 均使用TTASEventMutex來保護(hù)對(duì)應(yīng)的臨界區(qū)資源，實(shí)現(xiàn)多并發(fā)

默認(rèn)的自旋參數(shù)，Kunpeng920 tpcc場(chǎng)景在高并發(fā)場(chǎng)景下，會(huì)出現(xiàn)大量線程進(jìn)入 sync_array，并在進(jìn)出時(shí)會(huì)由于爭(zhēng)搶 sync_array->mutex 陷入內(nèi)核態(tài)，會(huì)出現(xiàn) queued_spin_lock_slowpath 的熱點(diǎn)函數(shù)

調(diào)整 Innodb_spin_wait_delay 和 Innodb_sync_spin_loops 參數(shù)，使得線程盡量少的陷入內(nèi)核態(tài)，充分利用CPU資源

MySQL 數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。