共計(jì) 6616 個字符,預(yù)計(jì)需要花費(fèi) 17 分鐘才能閱讀完成。
本篇文章給大家分享的是有關(guān)怎么實(shí)現(xiàn) mysql 數(shù)據(jù)庫性能診斷,丸趣 TV 小編覺得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著丸趣 TV 小編一起來看看吧。
一:檢查 mysql 所在的服務(wù)器的性能,linux 環(huán)境使用 top 和 iostat
[root@S243 etc]# top
top – 11:40:41 up 23 days, 17:06, 2 users, load average: 0.11, 0.31, 0.32
Tasks: 617 total, 1 running, 616 sleeping, 0 stopped, 0 zombie
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 : 1.0%us, 0.7%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.3%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
關(guān)于 top 的結(jié)果主要關(guān)注下 load average: 0.11, 0.31, 0.32
系統(tǒng)負(fù)載 (任務(wù)隊(duì)列的平均長度) 三個值分別為 1 分鐘、5 分鐘、15 分鐘前到現(xiàn)在的平均值, 小于 1 正常【這三個一般會小于 1,如果持續(xù)高于 5,請仔細(xì)查看那個程序影響系統(tǒng)的運(yùn)行】”
[root@S243 etc]# iostat -d -x
Linux 2.6.32-131.0.15.el6.x86_64 (S243) 2016 年 10 月 20 日 _x86_64_ (24 CPU)
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 1.02 14.59 6.77 27.88 297.25 519.70 23.58 0.07 1.92 0.34 1.17
輸出信息的含義
rrqm/s:每秒這個設(shè)備相關(guān)的讀取請求有多少被 Merge 了(當(dāng)系統(tǒng)調(diào)用需要讀取數(shù)據(jù)的時(shí)候,VFS 將請求發(fā)到各個 FS,如果 FS 發(fā)現(xiàn)不同的讀取請求讀取的是相同 Block 的數(shù)據(jù),F(xiàn)S 會將這個請求合并 Merge);wrqm/s:每秒這個設(shè)備相關(guān)的寫入請求有多少被 Merge 了。
rsec/s:每秒讀取的扇區(qū)數(shù);
wsec/:每秒寫入的扇區(qū)數(shù)。
rKB/s:The number of read requests that were issued to the device per second;
wKB/s:The number of write requests that were issued to the device per second;
avgrq-sz 平均請求扇區(qū)的大小
avgqu-sz 是平均請求隊(duì)列的長度。毫無疑問,隊(duì)列長度越短越好。
await: 每一個 IO 請求的處理的平均時(shí)間(單位是微秒毫秒)。這里可以理解為 IO 的響應(yīng)時(shí)間,一般地系統(tǒng) IO 響應(yīng)時(shí)間應(yīng)該低于 5ms,如果大于 10ms 就比較大了。
這個時(shí)間包括了隊(duì)列時(shí)間和服務(wù)時(shí)間,也就是說,一般情況下,await 大于 svctm,它們的差值越小,則說明隊(duì)列時(shí)間越短,反之差值越大,隊(duì)列時(shí)間越長,說明系統(tǒng)出了問題。
svctm 表示平均每次設(shè)備 I / O 操作的服務(wù)時(shí)間(以毫秒為單位)。如果 svctm 的值與 await 很接近,表示幾乎沒有 I / O 等待,磁盤性能很好,如果 await 的值遠(yuǎn)高于 svctm 的值,則表示 I / O 隊(duì)列等待太長, 系統(tǒng)上運(yùn)行的應(yīng)用程序?qū)⒆兟侠?1.92 高于 0.34 說明存在一定的等待。
%util:在統(tǒng)計(jì)時(shí)間內(nèi)所有處理 IO 時(shí)間,除以總共統(tǒng)計(jì)時(shí)間。例如,如果統(tǒng)計(jì)間隔 1 秒,該設(shè)備有 0.8 秒在處理 IO,而 0.2 秒閑置,那么該設(shè)備的 %util = 0.8/1 = 80%,所以該參數(shù)暗示了設(shè)備的繁忙程度
。一般地,如果該參數(shù)是 100% 表示設(shè)備已經(jīng)接近滿負(fù)荷運(yùn)行了(當(dāng)然如果是多磁盤,即使 %util 是 100%,因?yàn)榇疟P的并發(fā)能力,所以磁盤使用未必就到了瓶頸)。
二:查看有沒有阻塞:
mysql show processlist; 關(guān)注 state 有沒有 lock 的狀態(tài)。有的話 kill 解決。
+———+————-+———————+—————+————-+———+—————————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
| 3 | mailer | 192.168.0.225:45135 | mailer | Sleep | 26 | | NULL |
| 8 | info_reader | 192.168.0.225:45194 | info | Sleep | 56 | | NULL |
| 23 | info_reader | 192.168.0.225:45451 | info | Sleep | 13 | | NULL |
| 78 | info_reader | 192.168.0.225:54249 | info | Sleep | 68 | | NULL |
| 180 | web_editer | 192.168.0.225:46200 | info | Sleep | 26 | | NULL |
| 226 | web_editer | 192.168.0.225:46584 | info | Sleep | 13 | | NULL |
| 2035 | info_reader | 192.168.0.225:53314 | info | Sleep | 36 | | NULL |
| 2052 | info_reader | 192.168.0.225:53447 | info | Sleep | 36 | | NULL |
| 2384 | ruby_syncer | 192.168.0.218:41868 | info | Sleep | 0 | | NULL |
| 2387 | ruby_syncer | 192.168.0.218:41870 | info | Sleep | 0 | | NULL |
三:查看 mysql 慢 sql
1)首先檢查是否開啟了慢 sql. 如下紅色顯示為開啟了 mysql 慢查詢。
mysql show variables like %query%
+——————————+——————————+
| Variable_name | Value |
+——————————+——————————+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 4.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 104857600 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /mysql/datadir/S243-slow.log |
+——————————+——————————+
13 rows in set (0.00 sec)
拓展:mysql 慢查詢相關(guān)的主要的三個參數(shù)為
(1)slow_query_log #指定是否開啟慢查詢?nèi)罩?0/ 1 或者 off/on)
(2)slow_query_log_file # 指定慢日志文件存放位置,可以為空,系統(tǒng)會給一個缺省的文件 host_name-slow.log
(3)long_query_time #設(shè)定慢查詢的閥值,超出次設(shè)定值的 SQL 即被記錄到慢查詢?nèi)罩荆笔≈禐?10s
(4)log_queries_not_using_indexes: 不使用索引的慢查詢?nèi)罩臼欠裼涗浀剿饕? (on/off)
(5)min_examined_row_limit:查詢檢查返回少于該參數(shù)指定行的 SQL 不被記錄到慢查詢?nèi)罩? 。
2)mysql show status like %Slow_queries% #顯示了當(dāng)前慢查詢的數(shù)量,也就是正在執(zhí)行的。如果顯示 0,說明當(dāng)前沒有正在執(zhí)行的時(shí)間超過 long_query_time 值的 sql. 你需要去慢查詢?nèi)罩局胁榭础?br />+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 2 |
+—————+——-+
1 row in set (0.00 sec)
3)直接查看慢查詢?nèi)罩镜膬?nèi)用就可以,如下顯示了兩條慢 sql.
[root@S243 datadir]# tail -n 10 S243-slow.log
# Time: 161020 15:19:39
# User@Host: mailer[mailer] @ [192.168.0.226] Id: 1372832
# Query_time: 12.617656 Lock_time: 0.000072 Rows_sent: 1 Rows_examined: 19796776
SET timestamp=1476947979;
select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state 100 and kehuguanzh0_.guanzhu_id= 4f41910e-0ec6-4042-8c4a-b2f0f9c8 and kehuguanzh0_.last_modify = 2016-09-20 and kehuguanzh0_.last_modify 2016-10-21 order by kehuguanzh0_.id desc limit 2;
# Time: 161020 15:21:19
# User@Host: mailer[mailer] @ [192.168.0.226] Id: 1372832
# Query_time: 12.489680 Lock_time: 0.000155 Rows_sent: 1 Rows_examined: 19796842
SET timestamp=1476948079;
select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state 100 and kehuguanzh0_.guanzhu_id= 53b344cf-6239-4882-afbb-772b90a4 and kehuguanzh0_.last_modify = 2016-09-20 and kehuguanzh0_.last_modify 2016-10-21 order by kehuguanzh0_.id desc limit 2;
我們也可以利用 mysqldumpslow 來格式化慢查詢?nèi)罩镜母袷剑阌谖覀儾殚啞?/p>
#獲取 mysqldumpslow 的幫助信息
[root@S243 datadir]# mysqldumpslow –help
Usage: mysqldumpslow [OPTS…] [LOGS…]
Parse and summarize the MySQL slow query log. Options are
–verbose verbose
–debug debug
–help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), at is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don t abstract all numbers to N and strings to S
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is * , i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don t subtract lock time from total time
例如:
以下是按照最大耗用時(shí)間排最后,只顯示 2 條的方式格式化日志文件
[root@S243 datadir]# mysqldumpslow -r -t 2 /mysql/datadir/S243-slow.log
Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log
Count: 1 Time=1.57s (1s) Lock=0.00s (0s) Rows=83.0 (83), root[root]@localhost
select table_schema,table_name,count(*) from tb_slow
group by table_schema,table_name order by N,N
Count: 4 Time=16.87s (67s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into tb_slow select * from tb_slow
[root@S243 datadir]# mysqldumpslow -s /mysql/datadir/S243-slow.log ### 最耗時(shí)間的依次排序。
由于這個慢查詢?nèi)罩臼菬o限增大的,他是好長時(shí)間的一個累計(jì),而數(shù)據(jù)庫性能慢的時(shí)候,往往你只需要查詢當(dāng)時(shí)的那段時(shí)間的慢 sql 語句,然后針對性的去優(yōu)化,所以沒必要使用 mysqldumpslow 去做什么排序之類的,因?yàn)樽詈臅r(shí)的 sql 可能僅僅是在很久以前執(zhí)行過一次,直接用 tail -n 去看某個時(shí)間段的就可以了。
確定了影響性能的慢 sql 之后,然后針對性的去優(yōu)化,加索引,改寫 sql,。。。。。
體會:mysql 性能診斷過程和 oracle 類似,也是首先查看 mysql 的服務(wù)器的性能,然后看 mysql 數(shù)據(jù)的性能(有沒有鎖之類的)最后確定性能慢的 sql. 然后針對性的去優(yōu)化。
以上就是怎么實(shí)現(xiàn) mysql 數(shù)據(jù)庫性能診斷,丸趣 TV 小編相信有部分知識點(diǎn)可能是我們?nèi)粘9ぷ鲿姷交蛴玫降摹OM隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注丸趣 TV 行業(yè)資訊頻道。