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%’
|
| 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
|