MySQL Version
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)
Shell Command
# os负载
uptime;
free -h;
# mysqld进程占用内存
top -b -n 1 | grep mysqld | grep -v mysqld_safe
# os + mysqld
top -b -n 1 -p $(pgrep -n mysqld)
SQL
- innodb_buffer_pool_size
-- 查看innodb_buffer_pool_size参数(单位GB)
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
-- 设置参数
SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;
-- 查看InnoDB缓存池已分配buffer_size
select * from sys.innodb_buffer_stats_by_schema limit 10;
- innodb engine status
show engine innodb status\G;
- processlist
-- 查看当前连接数
select count(*) from information_schema.processlist;
show processlist;
- max_connections
-- 查看和设置max_connections
show variables like 'max_connections';
SET GLOBAL max_connections = 3500;
-- 查看和设置max_user_connections
SELECT @@max_user_connections;
set global max_user_connections=1000;
SELECT @@max_user_connections;
# 排序统计Server的连接数
mysql -uroot -h$MYSQL_SERVER_IP -p$MYSQL_ROOT_PASSWORD -e "show processlist;" | awk '{print $2" "$4}' | uniq -c | sort -rn | grep -v User | head -n 10
- slow query
--设置慢查询时间阈值(单位：秒)
set global long_query_time=1;
--关闭 记录没有使用索引的sql
set global log_queries_not_using_indexes=OFF;
--开启记录慢查询日志
set global slow_query_log = 1;
--获取慢查询日志路径
show variables like '%slow_query%';
--关闭记录慢查询日志
set global slow_query_log = 0;
# 过滤 Query_time && Rows_examined
cat slow_query.log | grep Query_time
cat slow_query.log | grep -E 'Query_time: [1-9]'
- 分析table
-- 查看单表信息
show table status like 'table_name'\G;
show table status from DbName where name='table_name'\G;
-- 查看各个表数据量
SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' ORDER BY table_rows DESC limit 10;
-- 查看平均每条记录大小和表的总数据量
SELECT table_name,table_rows,CONCAT(ROUND(SUM((DATA_LENGTH + INDEX_LENGTH) / table_rows) / 1024, 2), ' KB') AS average_row_size,CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2), ' MB') AS total_data_size, CONCAT(ROUND(SUM(index_length) / (1024 * 1024), 2), ' MB') AS total_index_size FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
- 开启内存统计
-- 开启内存统计
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
-- 查看是否开启,yes是开启,no是关闭.
select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 10;
use performance_schema;
show tables like '%memory%';
# my.cnf
[mysqld]
performance-schema-instrument = 'memory/%=ON'
- memory_global
select * from sys.memory_global_total;
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
select * from sys.x$memory_global_by_current_bytes where event_name like "memory/sql/%" order by current_alloc desc limit 10;
Case
- 现象
mysqld进程CPU占用高，内存平均每小时上涨2G，内存持续上涨到80G+，直到耗尽操作系统内存。
- 排查
上调参数innodb_buffer_pool_size 问题未缓解，开启慢查询，发现大量单表百万行数据的全表扫描慢查询；
清理慢查需SQL表数据，解决慢查询SQL后，mysqld进程运维内存占用平稳。
- 优化
下调客户端查询参数，限制慢查询的建立的连接数和单个查询连接的缓存数据量。
参考文档
mysql内存查阅 mysql内存分析 - 郭大侠1 - 博客园
分析mysql实例内存使用情况_51CTO博客_mysql内存使用率过高
--统计事件消耗内存,那个event_name排最前,就代表这个事件模块占内存最多.例如:JOIN_CACHE就是join的操作,mem0mem就是客户端连接,没开启统计的话,数据可能并不会很多.
select event_name, concat(CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,' GB') AS CURRENT_NUMBER_OF_GB_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
--统计线程消耗内存,thread_id就是show processlist的thread_id,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
select thread_id, event_name, concat(CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,' GB') AS CURRENT_NUMBER_OF_GB_USED, concat(SUM_NUMBER_OF_BYTES_ALLOC/1024/1024/1024,' GB') AS SUM_NUMBER_OF_GB_ALLOC from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
--统计账户消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
select USER, HOST, EVENT_NAME, concat(CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,' GB') AS CURRENT_NUMBER_OF_GB_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_account_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10 ;
--统计主机消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
select HOST, EVENT_NAME, concat(CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,' GB') AS CURRENT_NUMBER_OF_GB_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
--统计用户消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
select USER, EVENT_NAME, concat(CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024,' GB') AS CURRENT_NUMBER_OF_GB_USED, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_user_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
show engine innodb status\G;
-- 开启收集内存的统计信息
use performance_schema;
UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where current_count > 0 limit 10;
--
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(event_name, '/', 2), '/', -1) AS event_type, ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS MB_CURRENTLY_USED
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY event_type
HAVING MB_CURRENTLY_USED > 0;
观察到 MySQL 内存占用一直在上升，尽管到达了
innodb_buffer_pool_size的内存上限，依然持续上涨，就算没有请求了，内存也只是维持不变，并不会下降。
技术分享 | MySQL 内存管理初探_ActionTech的博客-CSDN博客
MySQL实际占用物理内存比InnoDB_Buffer_Pool高很多且不释放，多占用的内存大多被内存分配器占用，为了高效管理内存，内存分配器通常会占用很多内存不释放；内存分配器不会永远不释放内存，需要达到某个阈值，释放一部分内存给操作系统；
MySQL 8.0.28 新功能 | MySQL 8.0不再担心被垃圾SQL搞爆内存 - 墨天轮
-- global_connection_memory_tracking
show global status like 'Global_connection_memory';
select @@global.connection_memory_limit;
数据库内存管理遇到的麻烦事儿，今天给一点解法 - 墨天轮
mysql查询库大小，表行数，索引大小
查看mysql库大小，表大小，索引大小
