MySQL 效能監控SQL語句

MySQL 效能監控

這篇文章主要列出MySQL可以用來查詢效能監控的SQL語句

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html

效能指標 SQL語句
緩存 show variables like “%Query_cache%”
thread_cache_size show variables like ‘threatd%’;
Database current connections show status like ‘Connections’;
Current threats status show status like ‘%thread%’;
Index Buffer size show variables like ‘key_buffer_size’;
Thread Cache hit Rate Cache Hit = (Connections – threads_created)  / (connections)
Max connections show variables like ‘max_connections’
Used Connections show global status like ‘Max_used_connections’

show global status like ‘Connections’

建議: ‘Max_used_connections / max_connections <= 85%’

Thread Queue show variables like ‘back_log’

越小越好

表示thread等待的數量

Max Connection Errors max_connection_errors

預設為10, 帳戶錯誤連接達到10次自動阻擋, 需要用flash hosts來解除

Temp Table show global status like ‘created_tmp%’

  1. 當記憶體不夠大的時候,就會建立臨時表
  2. 如果臨時表很大, 可檢查是否sort運算過多, join方式不當
Temp Table 記憶體最大空間 show variables where Variable_name in (‘tmp_table_size’, ‘max_heap_table_size’)
Table Scan狀態 show global status like ‘handler_read%’

show global status like ‘com_select’

Handler_read_rnd_next/Com_select > 4000 表示有過多的table scan

  1. 可能為索引無效率導致
  2. 增加read_buffer_size

 

 

Leave a Reply

Your email address will not be published.