mysql内存查阅 mysql内存分析
【1】整体内存分析
该部分脚本转自：https://blog.csdn.net/weixin_36114835/article/details/113210288
效果如下
#!/bin/sh # you might want to add some user authentication here mysql -e "show variables; show status" | awk ' {undefined VAR[$1]=$2 } END {undefined MAX_CONN = VAR["max_connections"] MAX_USED_CONN = VAR["Max_used_connections"] BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"] MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"] MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576 printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576 printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576 printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576 printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576 printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576 printf "+------------------------------------------+--------------------+\n" printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN printf "| %40s | %18d |\n", "max_connections", MAX_CONN printf "+------------------------------------------+--------------------+\n" printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576 printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576 printf "+------------------------------------------+--------------------+\n" }'
【2】具体内存分析
（2.1）连接内存
SELECT ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@tmp_table_size + 2*@@net_buffer_length ) / (1024 * 1024) AS MEMORY_PER_CON_MB;
【3】整体内存占用分析
（3.1）查看渠道与角度
从哪里开始排除MySQL内存泄漏问题？
假设这是一个Linux服务器，首先我们要
检查Linux操作系统和配置 ：
-
检查mysql错误日志和Linux日志文件（即/ var / log / messages或/ var / log / syslog）来识别崩溃。你可能会看到OOM Killer杀死MySQL的条目，可以使用“dmesg”来显示相关的详细信息。
-
检查可用的RAM： free -g cat / proc / meminfo
-
检查哪些应用程序正在使用RAM： “top”或“htop”
-
检查mysql配置： /etc/my.cnf 或general /etc/my* (including /etc/mysql/*等文件)，MySQL可能正在运行不同的my.cnf（run ps ax | grep mysql ）
-
运行 vmstat 5 5 以查看系统是否正在通过虚拟内存进行读/写以及是否正在进行交换
-
对于非生产环境，我们可以使用其他工具（如Valgrind，gdb等）来检查MySQL的使用情况.
（3.2）检查MySQL内部
我们也可以通过检查MySQL内部来发现潜在的MySQL内存泄露。MySQL在很多地方都会有内存分配，尤其是在以下情况下：
现在我们可以检查MySQL内部的东西来寻找潜在的MySQL内存泄漏。
MySQL在很多地方分配内存。特别：
-
Table cache
-
Performance_schema（运行： show engine performance_schema status ，并查看最后一行）。
-
InnoDB（运行 show engine innodb status 并检查缓冲池部分，为buffer_pool和相关缓存分配的内存）
-
RAM中的临时表（通过运行以下语句查找所有内存表： select * from information_schema .tables where engine ='MEMORY' ）
-
Prepared statements。
不过，从MySQL 5.7版本开始，我们就可以在performance_schema中查看内存分配。那么，如何使用呢？
首先，我们需要启用收集内存指标。Run：
UPDATE performance_schema.setup_instruments SET ENABLED='YES'WHERE NAME LIKE 'memory/%';
（3.3）常见内存使用查询库表
常见表
-- sys 库 memory_by_host_by_current_bytes memory_by_thread_by_current_bytes memory_by_user_by_current_bytes memory_global_by_current_bytes memory_global_total -- performance_schema 库 memory_summary_by_account_by_event_name：账号纬度的内存监控表 memory_summary_by_host_by_event_name：主机纬度的内存监控表 memory_summary_by_thread_by_event_name：线程维度的内存监控表 memory_summary_by_user_by_event_name：用户纬度的内存监控表 memory_summary_global_by_event_name：全局纬度的内存监控表
内存监控表
在performance_schema库下，提供多个维度的内存监控表，具体如下：
memory_summary_by_account_by_event_name：账号纬度的内存监控表
memory_summary_by_host_by_event_name：主机纬度的内存监控表
memory_summary_by_thread_by_event_name：线程维度的内存监控表
memory_summary_by_user_by_event_name：用户纬度的内存监控表
memory_summary_global_by_event_name：全局纬度的内存监控表
内存监控表均包括以下关键字段：
COUNT_ALLOC：内存分配次数
COUNT_FREE：内存回收次数
SUM_NUMBER_OF_BYTES_ALLOC：内存分配大小
SUM_NUMBER_OF_BYTES_FREE：内存回收大小
CURRENT_COUNT_USED：当前分配的内存，通过COUNT_ALLOC-COUNT_FREE计算得到
CURRENT_NUMBER_OF_BYTES_USED：当前分配的内存大小，通过SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE计算得到
LOW_COUNT_USED：CURRENT_COUNT_USED的最小值
HIGH_COUNT_USED：CURRENT_COUNT_USED的最大值
LOW_NUMBER_OF_BYTES_USED：CURRENT_NUMBER_OF_BYTES_USED的最小值
HIGH_NUMBER_OF_BYTES_USED：CURRENT_NUMBER_OF_BYTES_USED的最大值
（3.4）一个内存查询SQL
select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where current_count > 0;
通常，分配内存时会提供代码，所以在某些情况下搜索某些错误时，我们可能需要检查 MySQL 源代码。例如，对于在触发器中过度分配内存的错误：
某些情况下搜索某些错误时，我们可能需要检查MySQL源代码。
例如，对于在触发器中过度分配内存的错误：
mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
RAM中最大的块通常是缓冲池，但存储过程中的3G似乎也太高了。
根据MySQL源代码文档，SPHead表示存储程序的一个实例，该程序可能是任何类型（存储过程、函数、触发器、事件）。
在这种情况下，就会有潜在的内存泄漏。此外，如果我们想要更清楚的知道MySQL内存情况，还可以得到一个更高级别的总报告。
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;
【4】常用内存SQL查询
（4.1）总内存使用情况
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;
（4.2）查看线程占用
-- 具体线程 select THREAD_ID,EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 'used_MB' from performance_schema.memory_summary_by_thread_by_event_name where CURRENT_NUMBER_OF_BYTES_USED>0 order by CURRENT_NUMBER_OF_BYTES_USED desc limit 30; -- 线程总计 select sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 used_MB from memory_summary_by_thread_by_event_name;
【5】
