MySQL數據庫的性能監控方法

本篇內容主要講解“MySQL數據庫的性能監控方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL數據庫的性能監控方法”吧!

SHOW STATUS;
FLUSH STATUS;

查看當前連接數 SHOW STATUS LIKE 'Thread_%';
Thread_cached:被緩存的線程的個數
Thread_running：處于激活狀態的線程的個數
Thread_connected：當前連接的線程的個數
Thread_created：總共被創建的線程的個數

Thread cache hits
Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;
Connections = SHOW GLOBAL STATUS LIKE 'Connections';
TCH=(1 - (Threads_created / Connections)) * 100

查看活動連接內容
SHOW PROCESSLIST;

如果 TCH數小于90%,創建連接耗費了時間,增大Thread_cached數量

QPS
Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime

TPS
Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
TPS=(Com_commit + Com_rollback)/Uptime

QPS 和 TPS值一定要實時監控,如果接近架構搭建時的測試峰值,愿上帝與你同在

Read/Writes Ratio
Qcache_hits = SHOW GLOBAL STATUS LIKE 'Qcache_hits';
Com_select = SHOW GLOBAL STATUS LIKE 'Com_select';
Com_insert = SHOW GLOBAL STATUS LIKE 'Com_insert';
Com_update = SHOW GLOBAL STATUS LIKE 'Com_update';
Com_delete = SHOW GLOBAL STATUS LIKE 'Com_delete';
Com_replace = SHOW GLOBAL STATUS LIKE 'Com_replace';
R/W=(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

讀寫比,優化的重要依據,讀的多就去優化讀,寫的多就去優化寫

Slow queries per minute
Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
SQPM=Slow_queries / (Uptime/60)

Slow queries /Questions Ratio
Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';
Questions = SHOW GLOBAL STATUS LIKE 'Questions';
S/Q=Slow_queries/Questions

新版本上線時要著重關注慢查詢,讓測試去踢開發者的屁股吧

Full_join per minute
Select_full_join = SHOW GLOBAL STATUS LIKE 'Select_full_join';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
FJPM=Select_full_join / (Uptime/60)

沒有使用索引而造成的full_join,優化索引去吧

Innodb buffer read hits
Innodb_buffer_pool_reads = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
Innodb_buffer_pool_read_requests = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
IFRH=(1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

InnoDB Buffer命中率 目標 95%-99%;

Table Cache
Open_tables= SHOW GLOBAL STATUS LIKE 'Open_tables';
Opened_tables= SHOW GLOBAL STATUS LIKE 'Opened_tables';
table_cache= SHOW GLOBAL STATUS LIKE 'table_cache';

table_cache應該大于 Open_tables 小于 Opened_tables

到此，相信大家對“MySQL數據庫的性能監控方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！