mysql資料庫效能診斷

賀子_DBA時代發表於2016-10-20
MySql資料庫
一：檢查mysql所在的伺服器的效能，linux環境使用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
關於top的結果主要關注下 load average: 0.11, 0.31, 0.32 
系統負載(任務佇列的平均長度)三個值分別為1分鐘、5分鐘、15分鐘前到現在的平均值,小於1正常【這三個一般會小於1，如果持續高於5，請仔細檢視那個程式影響系統的執行】”
[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：每秒這個裝置相關的讀取請求有多少被Merge了（當系統呼叫需要讀取資料的時候，VFS將請求發到各個FS，如果FS發現不同的讀取請求讀取的是相同Block的資料，FS會將這個請求合併Merge）；wrqm/s：每秒這個裝置相關的寫入請求有多少被Merge了。
rsec/s：每秒讀取的扇區數；
wsec/：每秒寫入的扇區數。
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 平均請求扇區的大小
avgqu-sz 是平均請求佇列的長度。毫無疑問，佇列長度越短越好。   
await：  每一個IO請求的處理的平均時間（單位是微秒毫秒）。這裡可以理解為IO的響應時間，一般地系統IO響應時間應該低於5ms，如果大於10ms就比較大了。
         這個時間包括了佇列時間和服務時間，也就是說，一般情況下，await大於svctm，它們的差值越小，則說明佇列時間越短，反之差值越大，佇列時間越長，說明系統出了問題。
svctm    表示平均每次裝置I/O操作的服務時間（以毫秒為單位）。如果svctm的值與await很接近，表示幾乎沒有I/O等待，磁碟效能很好，如果await的值遠高於svctm的值，則表示I/O佇列等待太長，         系統上執行的應用程式將變慢，上例中1.92高於0.34說明存在一定的等待。
%util： 在統計時間內所有處理IO時間，除以總共統計時間。例如，如果統計間隔1秒，該裝置有0.8秒在處理IO，而0.2秒閒置，那麼該裝置的%util = 0.8/1 = 80%，所以該引數暗示了裝置的繁忙程度
。一般地，如果該引數是100%表示裝置已經接近滿負荷執行了（當然如果是多磁碟，即使%util是100%，因為磁碟的併發能力，所以磁碟使用未必就到了瓶頸）。
二：檢視有沒有阻塞：
mysql> show processlist;        關注state  有沒有lock的狀態。有的話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慢查詢相關的主要的三個引數為
（1）slow_query_log    #指定是否開啟慢查詢日誌(0/1或者off/on)
（2）slow_query_log_file  # 指定慢日誌檔案存放位置，可以為空，系統會給一個預設的檔案host_name-slow.log
（3）long_query_time   #設定慢查詢的閥值，超出次設定值的SQL即被記錄到慢查詢日誌，預設值為10s 
（4）log_queries_not_using_indexes: 不使用索引的慢查詢日誌是否記錄到索引  （on/off)
（5）min_examined_row_limit：查詢檢查返回少於該引數指定行的SQL不被記錄到慢查詢日誌 。
2)mysql>  show status like '%Slow_queries%';     #顯示了當前慢查詢的數量，也就是正在執行的。如果顯示0，說明當前沒有正在執行的時間超過long_query_time 值的sql.你需要去慢查詢日誌中檢視。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2    |
+---------------+-------+
1 row in set (0.00 sec)
3）直接檢視慢查詢日誌的內用就可以，如下顯示了兩條慢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來格式化慢查詢日誌的格式，便於我們查閱。
#獲取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
例如：
以下是按照最大耗用時間排最後，只顯示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   ###最耗時間的依次排序。
由於這個慢查詢日誌是無限增大的，他是好長時間的一個累計，而資料庫效能慢的時候，往往你只需要查詢當時的那段時間的慢sql語句，然後針對性的去最佳化，所以沒必要使用mysqldumpslow 去做什麼排序之類的，因為最耗時的sql可能僅僅是在很久以前執行過一次，直接用tail -n 去看某個時間段的就可以了。
確定了影響效能的慢sql之後，然後針對性的去最佳化，加索引，改寫sql,。。。。。
體會：mysql效能診斷過程和oracle類似，也是首先檢視mysql的伺服器的效能，然後看mysql資料的效能（有沒有鎖之類的）最後確定效能慢的sql.然後針對性的去最佳化。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2126818/，如需轉載，請註明出處，否則將追究法律責任。

相關文章