久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

怎么實(shí)現(xiàn)mysql數(shù)據(jù)庫性能診斷

155次閱讀
沒有評論

共計(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è)資訊頻道。

正文完
 
丸趣
版權(quán)聲明:本站原創(chuàng)文章,由 丸趣 2023-07-19發(fā)表,共計(jì)6616字。
轉(zhuǎn)載說明:除特殊說明外本站除技術(shù)相關(guān)以外文章皆由網(wǎng)絡(luò)搜集發(fā)布,轉(zhuǎn)載請注明出處。
評論(沒有評論)
主站蜘蛛池模板: 咸丰县| 农安县| 平武县| 五原县| 广平县| 伊金霍洛旗| 乌苏市| 鄂托克旗| 西乡县| 静安区| 舞钢市| 阿图什市| 合水县| 唐海县| 博乐市| 遂川县| 乐陵市| 彝良县| 建宁县| 安图县| 武乡县| 会昌县| 蓬溪县| 霍州市| 瓮安县| 南和县| 泗阳县| 福鼎市| 金阳县| 洛阳市| 镇江市| 新干县| 金阳县| 共和县| 琼结县| 中江县| 东安县| 新和县| 邛崃市| 石渠县| 昂仁县|