image.png

show profile 知識(shí)結(jié)構(gòu)用法

image.png

performance_scheme 關(guān)注數(shù)據(jù)庫運(yùn)行過程種性能相關(guān)參數(shù)，比起show profile 更加詳細(xì)

-- 打開等待事件的采集器配置項(xiàng)開關(guān)，需要修改setup_instruments配置表中對(duì)應(yīng)的采集器配置項(xiàng)
  UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';

--打開等待事件的保存表配置開關(guān)，修改setup_consumers配置表中對(duì)應(yīng)的配置項(xiàng)
  UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';

--\G linux 系統(tǒng)看起來不亂
--當(dāng)配置完成之后可以查看當(dāng)前server正在做什么，可以通過查詢events_waits_current表來得知，該表中每個(gè)線程只包含一行數(shù)據(jù)，用于顯示每個(gè)線程的最新監(jiān)視事件

  select * from events_waits_current\G

舉例子說明
*************************** 1. row ***************************
            THREAD_ID: 11
             EVENT_ID: 570
         END_EVENT_ID: 570
           EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
               SOURCE: 
          TIMER_START: 4508505105239280
            TIMER_END: 4508505105270160
           TIMER_WAIT: 30880
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 67918392
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
/*該信息表示線程id為11的線程正在等待buf_dblwr_mutex鎖，等待事件為30880
屬性說明：
    id:事件來自哪個(gè)線程，事件編號(hào)是多少
    event_name:表示檢測到的具體的內(nèi)容
    source:表示這個(gè)檢測代碼在哪個(gè)源文件中以及行號(hào)
    timer_start:表示該事件的開始時(shí)間
    timer_end:表示該事件的結(jié)束時(shí)間
    timer_wait:表示該事件總的花費(fèi)時(shí)間
注意：_current表中每個(gè)線程只保留一條記錄，一旦線程完成工作，該表中不會(huì)再記錄該線程的事件信息
*/


/*
_history表中記錄每個(gè)線程應(yīng)該執(zhí)行完成的事件信息，但每個(gè)線程的事件信息只會(huì)記錄10條，再多就會(huì)被覆蓋，*_history_long表中記錄所有線程的事件信息，但總記錄數(shù)量是10000，超過就會(huì)被覆蓋掉
*/
select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

/*
_history表中記錄每個(gè)線程應(yīng)該執(zhí)行完成的事件信息，但每個(gè)線程的事件信息只會(huì)記錄10條，再多就會(huì)被覆蓋，*_history_long表中記錄所有線程的事件信息，但總記錄數(shù)量是10000，超過就會(huì)被覆蓋掉
*/
select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

performance_schema實(shí)踐操作

--1、哪類的SQL執(zhí)行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪類SQL的平均響應(yīng)時(shí)間最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪類SQL排序記錄數(shù)最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪類SQL掃描記錄數(shù)最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪類SQL使用臨時(shí)表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪類SQL返回結(jié)果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪個(gè)表物理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
--8、哪個(gè)表邏輯IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪個(gè)索引訪問最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪個(gè)索引從來沒有用過？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪個(gè)等待事件消耗時(shí)間最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某條SQL的執(zhí)行情況，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每個(gè)階段的時(shí)間消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每個(gè)階段的鎖等待情況
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;