利用MySQL系統(tǒng)數(shù)據(jù)庫做性能負(fù)載診斷的方法

某大師曾說過，像了解自己的老婆 一樣了解自己管理的數(shù)據(jù)庫，個(gè)人認(rèn)為包含了兩個(gè)方面的了解：

1，在穩(wěn)定性層面來說，更多的是關(guān)注高可用、讀寫分離、負(fù)載均衡，災(zāi)備管理等等high level層面的措施（就好比要保證生活的穩(wěn)定性）

2，在實(shí)例級(jí)別的來說，需要關(guān)注內(nèi)存、IO、網(wǎng)絡(luò)，熱點(diǎn)表，熱點(diǎn)索引，top sql，死鎖，阻塞，歷史上執(zhí)行異常的SQL（好比生活品質(zhì)細(xì)節(jié)）MySQL的performance_data庫和sys庫提供了非常豐富的系統(tǒng)日志數(shù)據(jù)，可以幫助我們更好地了解非常細(xì)節(jié)的，這里簡(jiǎn)單地列舉出來了一些常用的數(shù)據(jù)。

sys庫是以較為可讀化的方式封裝了performance_data中的某些表，因此這些個(gè)數(shù)據(jù)來源還是performance_data庫中的數(shù)據(jù)。

這里粗略列舉出個(gè)人常用的一些系統(tǒng)數(shù)據(jù)，可以在實(shí)例級(jí)別更加清楚地了解MySQL的運(yùn)行過程中資源分配情況。

Status中的信息

MySQL的status變量只是給出了一個(gè)總的信息，從status變量上無法得知詳細(xì)資源的消耗，比如IO或者內(nèi)存的熱點(diǎn)在哪里，庫、表的熱點(diǎn)在哪里，如果想要知道具體的明細(xì)信息就需要系統(tǒng)庫中的數(shù)據(jù)。

前提要開啟performance_schema，因?yàn)閟ys庫的視圖是基于performance_schema的庫的。

內(nèi)存使用：

內(nèi)存/innodb_buffer_pool使用

概要innodb_buffer_pool的使用情況summary，已知當(dāng)前實(shí)例262144*16/1024 = 4096MB buffer pool,已使用23260*16/1024 363MB

innodb_buffer_pool已占用內(nèi)存的明細(xì)信息，可以按照庫\表的維度來統(tǒng)計(jì)

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT 
  database_name,
  SUM(compressed_size)/1024/1024 AS allocated_memory,
  SUM(data_size)/1024/1024 AS data_memory,
  SUM(is_hashed)*16/1024 AS is_hashed_memory,
  SUM(is_old)*16/1024 AS is_old_memory
FROM 
(
 SELECT 
  case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1),'`','')
  else  'system_database' end as database_name,
  case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,INSTR(TABLE_NAME,'.')+1),'`','')
  ELSE 'system_obj' END AS table_name,
  if(compressed_size = 0, 16384, compressed_size) AS compressed_size,
  data_size,
  if(is_hashed = 'YES',1,0) is_hashed,
  if(is_old = 'YES',1,0) is_old
 FROM information_schema.innodb_buffer_page
 WHERE TABLE_NAME IS NOT NULL
) t
GROUP BY database_name
ORDER BY allocated_memory DESC
LIMIT 10;

庫\表的讀寫統(tǒng)計(jì)，邏輯層面的熱點(diǎn)數(shù)據(jù)統(tǒng)計(jì)

目標(biāo)表是performance_schema.table_io_waits_summary_by_table，某些文章上說是邏輯IO，其實(shí)這里跟邏輯IO并無關(guān)系，這個(gè)表中的字段含義是基于表，讀寫的到的行數(shù)的統(tǒng)計(jì)。至于真正的邏輯IO層面的統(tǒng)計(jì)，筆者目前還有不知道有哪個(gè)可用的系統(tǒng)表來查詢。這個(gè)庫可以很清楚地看到這個(gè)表中的統(tǒng)計(jì)結(jié)果是怎么計(jì)算出來的。

基于表的讀寫的行的次數(shù)統(tǒng)計(jì)，這是一個(gè)累計(jì)值，單純的看這個(gè)值本身，個(gè)人覺得意義不大，需要定時(shí)收集計(jì)算差值，才具備參考意義。

以下按照庫級(jí)別統(tǒng)計(jì)表的讀寫情況。

庫\表的讀寫統(tǒng)計(jì)，物理IO層面的熱點(diǎn)數(shù)據(jù)統(tǒng)計(jì)

按照物理IO的維度統(tǒng)計(jì)熱點(diǎn)數(shù)據(jù)，哪些庫\表消耗了多少物理IO。這里原始系統(tǒng)表中的數(shù)據(jù)是一個(gè)累計(jì)統(tǒng)計(jì)的值，最極端的情況就是一個(gè)表為0行，卻存在大量的物理讀寫IO。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT 
 database_name,
 IFNULL(cast(sum(total_read) as signed),0) AS total_read,
 IFNULL(cast(sum(total_written) as signed),0) AS total_written,
 IFNULL(cast(sum(total) AS SIGNED),0) AS total_read_written
FROM
(
 SELECT 
  substring(REPLACE(file, '@@datadir/', ''),1,instr(REPLACE(file, '@@datadir/', ''),'/')-1) AS database_name,
  count_read,
  case 
   when instr(total_read,'KiB')>0 then replace(total_read,'KiB','')/1024
   when instr(total_read,'MiB')>0 then replace(total_read,'MiB','')/1024
   when instr(total_read,'GiB')>0 then replace(total_read,'GiB','')*1024
  END AS total_read,
  case 
   when instr(total_written,'KiB')>0 then replace(total_written,'KiB','')/1024
   when instr(total_written,'MiB')>0 then replace(total_written,'MiB','')
   when instr(total_written,'GiB')>0 then replace(total_written,'GiB','')*1024
  END AS total_written,
  case 
   when instr(total,'KiB')>0 then replace(total,'KiB','')/1024
   when instr(total,'MiB')>0 then replace(total,'MiB','')
   when instr(total,'GiB')>0 then replace(total,'GiB','')*1024
  END AS total
 from sys.io_global_by_file_by_bytes 
 WHERE FILE LIKE '%@@datadir%' AND instr(REPLACE(file, '@@datadir/', ''),'/')>0 
)t
GROUP BY database_name
ORDER BY total_read_written DESC;

ps：個(gè)人不太喜歡MySQL自定義的format_***函數(shù)，這個(gè)函數(shù)的初衷是好的，把一些數(shù)據(jù)（時(shí)間，存儲(chǔ)空間）等格式化成更加可讀的模式。但是卻不支持單位的參數(shù)，更多的時(shí)候想以某個(gè)固定的單位來顯示，比如格式化一個(gè)的時(shí)間，格式化后根據(jù)單位大小可能會(huì)顯示微妙，或者是毫秒，或者是秒，或者分鐘，或者天。比如想把時(shí)間統(tǒng)一格式化成秒，對(duì)不起，不支持，某些個(gè)數(shù)據(jù)不僅僅是看一眼那么簡(jiǎn)單，甚至是要讀出來存檔分析的，因此這里不建議也不會(huì)使用那些個(gè)format函數(shù)

TOP SQL 統(tǒng)計(jì)

可以按照?qǐng)?zhí)行時(shí)間，阻塞時(shí)間，返回行數(shù)等等維度統(tǒng)計(jì)top sql。
另外可以按照時(shí)間篩選last_seen，可以統(tǒng)計(jì)最近某一段時(shí)間出現(xiàn)過的top sql

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT 
 schema_name,
 digest_text,
 count_star,
 avg_timer_wait/1000000000000 AS avg_timer_wait,
 max_timer_wait/1000000000000 AS max_timer_wait,
 sum_lock_time/count_star/1000000000000 AS avg_lock_time ,
 sum_rows_affected/count_star AS avg_rows_affected,
 sum_rows_sent/count_star AS avg_rows_sent ,
 sum_rows_examined/count_star AS avg_rows_examined,
 sum_created_tmp_disk_tables/count_star AS avg_create_tmp_disk_tables,
 sum_created_tmp_tables/count_star AS avg_create_tmp_tables,
 sum_select_full_join/count_star AS avg_select_full_join,
 sum_select_full_range_join/count_star AS avg_select_full_range_join,
 sum_select_range/count_star AS avg_select_range,
 sum_select_range_check/count_star AS avg_select_range,
 first_seen,
 last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE last_seen>date_add(NOW(), interval -1 HOUR)
ORDER BY 
max_timer_wait
-- avg_timer_wait
-- sum_rows_affected/count_star 
-- sum_lock_time/count_star
-- avg_lock_time
-- avg_rows_sent
DESC
limit 10;

需要注意的是，這個(gè)統(tǒng)計(jì)是按照MySQL執(zhí)行一個(gè)事務(wù)消耗的資源做統(tǒng)計(jì)的，而不是一個(gè)語句，筆者一開始懵逼了一陣子，舉個(gè)簡(jiǎn)單的例子。

參考如下，這里是循環(huán)寫個(gè)數(shù)據(jù)的一個(gè)存儲(chǔ)過程，調(diào)用方式就是call create_test_data(N)，寫入N條測(cè)試數(shù)據(jù)。
比如call create_test_data(1000000)就是寫入100W的測(cè)試數(shù)據(jù)，這個(gè)執(zhí)行過程耗費(fèi)了幾分鐘的時(shí)間，按照筆者的測(cè)試實(shí)例情況，avg_timer_wait的維度，絕對(duì)是一個(gè)TOP SQL。

但是在查詢的時(shí)候，始終沒有發(fā)現(xiàn)這個(gè)存儲(chǔ)過程的調(diào)用被列為TOP SQL，后面嘗試在存儲(chǔ)過程內(nèi)部加了一個(gè)事物，然后就順利地收集到了整個(gè)TOP SQL.

因此說performance_schema.events_statements_summary_by_digest里面的統(tǒng)計(jì)，是基于事務(wù)的，而不是某一個(gè)批處理的執(zhí)行時(shí)間的。

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
 IN `loopcnt` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
 -- START TRANSACTION; 
  while loopcnt>0 do
   insert into test_mrr(rand_id,create_date) values (RAND()*100000000,now(6));
   set loopcnt=loopcnt-1;
  end while;
 -- commit;
END

另外一點(diǎn)比較有意思的是，這個(gè)系統(tǒng)表是為數(shù)不多的支持truncate的，當(dāng)然它在內(nèi)部，也是在不斷收集的一個(gè)過程。

執(zhí)行失敗的SQL 統(tǒng)計(jì)

一直以為系統(tǒng)不會(huì)記錄執(zhí)行失敗的\解析錯(cuò)誤的SQL，比如想統(tǒng)計(jì)因?yàn)槌瑫r(shí)而執(zhí)行失敗的語句，后面才發(fā)現(xiàn)，這些信息，MySQL會(huì)完整地記錄下來

這里會(huì)詳細(xì)記錄執(zhí)行錯(cuò)誤的語句，包括最終執(zhí)行失?。ǔ瑫r(shí)之類的），語法錯(cuò)誤，執(zhí)行過程中產(chǎn)生了警告之類的語句。用sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest篩選一下即可。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

select 
 schema_name,
 digest_text,
 count_star,
 first_seen,
 last_seen
from performance_schema.events_statements_summary_by_digest
where sum_errors>0 or sum_warnings>0 
order by last_seen desc;

Index使用情況統(tǒng)計(jì)

基于performance_schema.table_io_waits_summary_by_index_usage這個(gè)系統(tǒng)表，其統(tǒng)計(jì)的維度同樣是“按照某個(gè)索引查詢返回的行數(shù)的統(tǒng)計(jì)”。

可以按照哪些索引使用最多\最少等情況進(jìn)行統(tǒng)計(jì)。

不過這個(gè)統(tǒng)計(jì)有一個(gè)給人潛在一個(gè)誤區(qū)：
count_read,count_write,count_fetch,count_insert,count_update,count_delete統(tǒng)計(jì)了某個(gè)索引上使用到索引的情況下，受影響的行數(shù)，sum_timer_wait是累計(jì)在該索引上等待的時(shí)間。

如果使用到了該索引，但是沒有數(shù)據(jù)受影響（就是沒有DML語句的條件沒有命中數(shù)據(jù)），將count_***不會(huì)統(tǒng)計(jì)進(jìn)來，但是sum_timer_wait會(huì)統(tǒng)計(jì)進(jìn)來
這就存在一個(gè)容易受到誤導(dǎo)的地方，這個(gè)索引明明沒有命中過很多次，但是卻產(chǎn)生了大量的timer_wait，索引看到類似的信息，也不能貿(mào)然刪除索引。

等待事件統(tǒng)計(jì)

MySQL數(shù)據(jù)庫中的任何一個(gè)動(dòng)作，都需要等待（一定的時(shí)間來完成），一共有超過1000個(gè)等待事件，分屬不懂的類別，每個(gè)版本都不一樣，且默認(rèn)不是所有的等待事件都啟用。

個(gè)人認(rèn)為等待事件這個(gè)東西，僅做參考，不具備問題的診斷性，即便是再優(yōu)化或者低負(fù)載的數(shù)據(jù)庫，累計(jì)一段時(shí)間，某些事件仍舊會(huì)積累大量的等待事件。

這些事件的等待事件，不一定都是負(fù)面性的，比如事物的鎖等待，是在并發(fā)執(zhí)行過程中必然會(huì)生成的，這個(gè)等待事件的統(tǒng)計(jì)結(jié)果，也是累計(jì)的，單純的看一個(gè)直接的值，不具備任何參考意義。
除非定期收集，做差值計(jì)算，根據(jù)實(shí)際情況，才具備參考意義。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1) 
FROM performance_schema.setup_instruments
GROUP BY 1 
ORDER BY 2 DESC;


SELECT
event_name,
count_star,
sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
order by sum_timer_wait desc
limit 100;

最后，需要注意的是，

1，MySQL提供的諸多的系統(tǒng)表（視圖）中的數(shù)據(jù)，單純的看這個(gè)值本身，因?yàn)樗且粋€(gè)累計(jì)值，個(gè)人覺得意義不大，尤其是avg_***，需要結(jié)合多方面的綜合因素，做參考使用。
2，任何系統(tǒng)表的查詢，都可能對(duì)系統(tǒng)性能的本身造成一定的影響，不要再對(duì)系統(tǒng)可能產(chǎn)生較大負(fù)面影響的情況下做數(shù)據(jù)的統(tǒng)計(jì)收集。

總結(jié)

以上所述是小編給大家介紹的利用MySQL系統(tǒng)數(shù)據(jù)庫做性能負(fù)載診斷的方法,希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
如果你覺得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！