MYSQL日常优化指标说明
发表于:2023-02-06 13:32:13浏览:480次
查看整个数据库的大小分布情况
SELECT ENGINE,
COUNT(*) AS TABLES,
CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') ROWS,
CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') idx,
CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
ROUND(SUM(index_length)/SUM(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
GROUP BY ENGINE
ORDER BY SUM(data_length+index_length) DESC LIMIT 10;
清空某个指标表之前收集的数据 全部归零
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
查看某个数据库汇总延迟最高的语句
SELECT avg_timer_wait/1000000000000 AS AVG_TIMER_WAIT_S,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
查看某个数据库单条执行最长时间的语句
SELECT avg_timer_wait/1000000000000 AS AVG_TIMER_WAIT_S,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY AVG_TIMER_WAIT DESC LIMIT 100;
查询读取行数最多的语句
SELECT SUM_ROWS_EXAMINED/COUNT_STAR AS AVG_ROWS_EXAMINED,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY AVG_ROWS_EXAMINED DESC LIMIT 100;
查询执行次数最多的SQL语句
SELECT * FROM performance_schema.`events_statements_summary_by_digest` ORDER BY COUNT_STAR DESC LIMIT 100;
c查询返回结果集最多的 语句
SELECT SUM_ROWS_SENT/COUNT_STAR AS AVG_ROWS_SENT,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY AVG_ROWS_SENT DESC LIMIT 100;
c查询返回排序次数最多的 语句
SELECT SUM_SORT_ROWS/COUNT_STAR AS AVG_SORT_ROWS,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY AVG_SORT_ROWS DESC LIMIT 100;
查询是否无索引的语句
SELECT SUM_NO_INDEX_USED/COUNT_STAR AS AVG_NO_INDEX_USED,wowtalk_db.* FROM performance_schema.`events_statements_summary_by_digest` wowtalk_db ORDER BY AVG_NO_INDEX_USED DESC LIMIT 100;
#另外一个维度统计
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;
buffer 状态
SHOW STATUS LIKE '%buffer%';
1、连接设置
SHOW VARIABLES LIKE '%max_connection%';
SHOW STATUS LIKE '%Max_used_connections%';
#Max_used_connections/max_connection <=85%
2、存储在堆栈中的连接数量
SHOW VARIABLES LIKE '%back_log%';
#back_log 默认为50 建议修改为 128~512
3、数据连接关闭前等待时间
SHOW VARIABLES LIKE '%timeout%';
#修改interactive_timeout wait_timeout 2项的值,默认为28800,建议修改为7200
4、索引缓冲区的大小
SHOW STATUS LIKE '%read%';
#索引未缓存命中率 key_read/key_request_reads ~=0.001~0.01
查看写入日志是否有等待,好调整 log_buffer
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';#是否写入有等待
SHOW GLOBAL STATUS LIKE 'Innodb_log_writes';#日志写入次数
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';#写入吞吐量,自己时差的来监控 这个量算出 美妙日志的量,结合调整LOG——buffer
5、查询缓冲区的大小(query_cache_size)-目前未开启
SHOW VARIABLES LIKE '%query_cache%';
SHOW STATUS LIKE '%qcache%';
#缓存碎片率 Qcache_free_blocks/Qcache_total_blocks <20%
#缓存利用率 (query_cache_size-Qcache_free_memory)/query_cache_size<25%
#缓存命中率 Qcache_hits/Qcache_inserts>95%
6、顺序读、随机读、排序、连接缓冲区的大小,每个线程独占,建议设置为16MB
SHOW STATUS LIKE '%buffer%';
SHOW VARIABLES LIKE '%innodb_sort_buffer_size%';
#read_buffer_size
#read_rnd_buffer_size
#sort_buffer_size
#join_buffer_size
7、表缓冲区大小
SHOW STATUS LIKE '%tables%';
#table_cache 根据 open_tables opented_tables 大小来调整
8、内存表和临时表
SHOW STATUS LIKE '%table%';
#max_heap_table_size
#tmp_table_size
#内存表超过临时表大小,才需要调整内存表的大小
9、磁盘上临时表大小
SHOW STATUS LIKE '%tmp%';
SHOW STATUS LIKE '%thread%';
SHOW VARIABLES LIKE '%thread%';
#(Created_tmp_disk_tables/Created_tmp_tables)*100<25%
10、缓存线程的数量
SHOW VARIABLES LIKE '%tmp%';
#thread_cache_size
11、并发线程的数量
SHOW VARIABLES LIKE '%thread%';
#innodb_thread_concurrency (cpu+磁盘)数量的2倍