小程序开发 网站建设千锋教育广州校区

小程序开发 网站建设,千锋教育广州校区,网站产品策划,wordpress版08影院一、MySQL内存监控的重要性 内存相关问题是MySQL中除锁问题外最为复杂的故障类型之一。与锁问题通常具有明确的等待或死锁信息不同#xff0c;内存问题往往表现为性能的渐进式下降、OOM#xff08;内存耗尽#xff09;导致的进程异常终止或系统整体不稳定。构建一套完善的…一、MySQL内存监控的重要性内存相关问题是MySQL中除锁问题外最为复杂的故障类型之一。与锁问题通常具有明确的等待或死锁信息不同内存问题往往表现为性能的渐进式下降、OOM内存耗尽导致的进程异常终止或系统整体不稳定。构建一套完善的多维度内存监控体系能够帮助数据库管理员快速定位并诊断问题的根本原因。下文将以MySQL 5.7版本为例展开说明。二、多维度内存监控视图2.1 通过sys库进行内存监控MySQL 5.7的sys库内置了四个核心内存监控视图分别从不同维度展示内存的分配与使用情况sql查看所有内存监控视图SELECT table_name, table_commentFROM information_schema.tablesWHERE table_schema sysAND table_name LIKE memory%ORDER BY table_name;各视图功能概述视图名称分组维度主要应用场景memory_by_host_by_current_bytes客户端主机排查连接级内存泄漏memory_by_thread_by_current_bytes线程分析特定会话内存使用memory_by_user_by_current_bytes用户按用户审计内存消耗memory_global_by_current_bytes事件类型全局内存分配分析2.2 主机维度监控实战场景生产环境中发现MySQL内存占用异常攀升需识别内存消耗最高的客户端来源。sqlSELECTIFNULL(host, background) AS 连接主机,ROUND(current_allocated/1024/1024, 2) AS 当前分配_MB,ROUND(current_count_used, 0) AS 使用中的块数,ROUND(current_avg_alloc/1024, 2) AS 平均分配_KB,ROUND(current_max_alloc/1024/1024, 2) AS 最大分配_MB,ROUND(total_allocated/1024/1024, 2) AS 历史分配总量_MB,ROUND((current_allocated/total_allocated)100, 2) AS 内存使用率_百分比FROM sys.x$memory_by_host_by_current_bytesORDER BY current_allocated DESCLIMIT 10;结果分析示例主机 10.186.61.18 当前分配约1.88GB内存但内存使用率仅为1.49%可能为连接池中持久会话所致。background 系统线程占用约1GB内存且使用率较高需进一步分析其内部组件。可针对高内存占用的主机进行会话级深度追踪。三、Performance Schema内存监控详解3.1 五维度内存监控表Performance Schema提供了更为精细的内存监控能力sql查看相关监控表SHOW TABLES FROM performance_schema LIKE memory%summary%;核心表示例按账户统计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_name3.2 线程级内存监控实战场景业务高峰期内存急剧上升需定位具体SQL。sqlSELECTt.PROCESSLIST_ID AS 连接ID,CONCAT(t.PROCESSLIST_USER, , t.PROCESSLIST_HOST) AS 用户连接,t.PROCESSLIST_DB AS 数据库,m.EVENT_NAME AS 内存事件,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 当前使用_MB,ROUND(m.HIGH_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 峰值使用_MB,m.COUNT_ALLOC AS 分配次数,m.COUNT_FREE AS 释放次数,LEFT(t.PROCESSLIST_INFO, 200) AS 执行语句摘要FROM performance_schema.memory_summary_by_thread_by_event_name mJOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE t.PROCESSLIST_ID IS NOT NULLAND m.CURRENT_NUMBER_OF_BYTES_USED 0ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESCLIMIT 10;四、深度关联分析SQL级内存追踪4.1 关联分析实战场景复杂查询执行缓慢疑似内存分配过多。sqlSELECTt.PROCESSLIST_ID AS 会话ID,CONCAT(t.PROCESSLIST_USER, , t.PROCESSLIST_HOST) AS 账户信息,CASEWHEN m.EVENT_NAME LIKE memory/sql/% THEN SQL层内存WHEN m.EVENT_NAME LIKE memory/innodb/% THEN InnoDB存储引擎WHEN m.EVENT_NAME LIKE memory/myisam/% THEN MyISAM存储引擎WHEN m.EVENT_NAME LIKE memory/temptable/% THEN 临时表内存ELSE 其他内存END AS 内存类型,m.EVENT_NAME AS 具体事件,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 6) AS 当前使用_MB,ROUND(m.SUM_NUMBER_OF_BYTES_ALLOC/1024/1024, 2) AS 历史分配总量_MB,ROUND((m.SUM_NUMBER_OF_BYTES_FREE / NULLIF(m.SUM_NUMBER_OF_BYTES_ALLOC, 0)) 100, 2) AS 内存释放率_百分比,t.PROCESSLIST_TIME AS 执行时间_秒,LEFT(t.PROCESSLIST_INFO, 300) AS 完整SQL语句FROM performance_schema.memory_summary_by_thread_by_event_name mINNER JOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE t.PROCESSLIST_ID IS NOT NULLAND t.PROCESSLIST_INFO IS NOT NULLAND m.CURRENT_NUMBER_OF_BYTES_USED 10241024ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESCLIMIT 15;4.2 内存事件分类统计sqlSELECTCASEWHEN EVENT_NAME LIKE memory/sql/% THEN SQL层WHEN EVENT_NAME LIKE memory/innodb/% THEN InnoDB引擎WHEN EVENT_NAME LIKE memory/myisam/% THEN MyISAM引擎WHEN EVENT_NAME LIKE memory/performance_schema/% THEN 监控系统WHEN EVENT_NAME LIKE memory/temptable/% THEN 临时表ELSE 其他END AS 内存类别,COUNT() AS 事件数量,ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前使用_MB,ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 峰值使用_MB,ROUND(AVG(CURRENT_NUMBER_OF_BYTES_USED/1024), 2) AS 平均使用_KBFROM performance_schema.memory_summary_global_by_event_nameGROUP BY 内存类别ORDER BY 当前使用_MB DESC;五、关键内存事件深度解读5.1 SQL层核心内存组件1. main_mem_root – 查询主内存池该内存池用于存储查询解析与执行期间的临时数据结构。sqlSELECTt.PROCESSLIST_ID,CONCAT(t.PROCESSLIST_USER, , t.PROCESSLIST_HOST) AS user_host,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS mem_root_used_MB,m.HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS mem_root_high_MB,t.PROCESSLIST_TIME AS query_time_sec,LEFT(t.PROCESSLIST_INFO, 150) AS current_queryFROM performance_schema.memory_summary_by_thread_by_event_name mJOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE m.EVENT_NAME memory/sql/thd::main_mem_rootAND t.PROCESSLIST_ID IS NOT NULLAND m.CURRENT_NUMBER_OF_BYTES_USED 1010241024ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC;2. JOIN_CACHE – 连接操作缓存用于大表JOIN操作时的内存缓存。sqlSELECTt.PROCESSLIST_ID,t.PROCESSLIST_INFO AS query,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 2) AS join_cache_mb,ROUND(m.HIGH_NUMBER_OF_BYTES_USED/1024/1024, 2) AS join_cache_high_mbFROM performance_schema.memory_summary_by_thread_by_event_name mJOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE m.EVENT_NAME memory/sql/JOIN_CACHEAND m.CURRENT_NUMBER_OF_BYTES_USED 5010241024ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC;5.2 存储引擎内存组件InnoDB非缓冲池内存分析sqlSELECTEVENT_NAME,ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS current_mb,ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS high_mb,COUNT() AS thread_countFROM performance_schema.memory_summary_global_by_event_nameWHERE EVENT_NAME LIKE memory/innodb/%GROUP BY EVENT_NAMEHAVING current_mb 1ORDER BY current_mb DESC;六、内存问题排查工作流6.1 日常监控检查清单sql1. 全局内存健康检查SELECT全局内存使用 AS 检查项,ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024/1024, 2) AS 当前使用_GB,ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024/1024, 2) AS 历史峰值_GB,COUNT(DISTINCT EVENT_NAME) AS 内存事件类型数FROM performance_schema.memory_summary_global_by_event_name;2. 用户级内存TOP 5SELECTIFNULL(USER, system) AS 用户名,ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前使用_MB,ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 历史峰值_MBFROM performance_schema.memory_summary_by_user_by_event_nameGROUP BY USERORDER BY 当前使用_MB DESCLIMIT 5;3. 疑似内存泄漏检查分配次数远大于释放次数SELECTt.PROCESSLIST_ID,CONCAT(t.PROCESSLIST_USER, , t.PROCESSLIST_HOST) AS 用户连接,m.EVENT_NAME,m.COUNT_ALLOC,m.COUNT_FREE,m.COUNT_ALLOC m.COUNT_FREE AS 未释放次数,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 当前持有_MBFROM performance_schema.memory_summary_by_thread_by_event_name mJOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE t.PROCESSLIST_ID IS NOT NULLAND m.COUNT_ALLOC m.COUNT_FREEAND (m.COUNT_ALLOC m.COUNT_FREE) 1000ORDER BY (m.COUNT_ALLOC m.COUNT_FREE) DESCLIMIT 10;6.2 紧急内存问题排查流程场景内存使用持续增长接近OOM阈值。sql第一步定位内存消耗最高的会话SELECT紧急排查 AS 场景,t.PROCESSLIST_ID AS 会话ID,CONCAT(t.PROCESSLIST_USER, , t.PROCESSLIST_HOST) AS 来源,ROUND(SUM(m.CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 总内存_MB,GROUP_CONCAT(CONCAT(SUBSTRING_INDEX(m.EVENT_NAME, /, 1),:,ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 2),MB)ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESCSEPARATOR | ) AS 内存分布详情,t.PROCESSLIST_TIME AS 运行时间_秒,LEFT(t.PROCESSLIST_INFO, 200) AS 当前SQLFROM performance_schema.memory_summary_by_thread_by_event_name mJOIN performance_schema.threads t ON m.THREAD_ID t.THREAD_IDWHERE t.PROCESSLIST_ID IS NOT NULLAND m.CURRENT_NUMBER_OF_BYTES_USED 0GROUP BY t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST,t.PROCESSLIST_TIME, t.PROCESSLIST_INFOHAVING 总内存_MB 100ORDER BY 总内存_MB DESCLIMIT 20;第二步分析内存分配最频繁的事件SELECTEVENT_NAME,SUM(COUNT_ALLOC) AS 总分配次数,SUM(COUNT_FREE) AS 总释放次数,SUM(COUNT_ALLOC) SUM(COUNT_FREE) AS 未释放次数,ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前占用_MBFROM performance_schema.memory_summary_global_by_event_nameWHERE COUNT_ALLOC 0GROUP BY EVENT_NAMEHAVING 未释放次数 10000OR 当前占用_MB 500ORDER BY 未释放次数 DESC, 当前占用_MB DESCLIMIT 15;七、内存监控配置优化建议7.1 启用完整内存监控sql检查当前配置SELECT FROM performance_schema.setup_instrumentsWHERE NAME LIKE memory/%;启用所有内存监控生产环境需评估性能影响UPDATE performance_schema.setup_instrumentsSET ENABLED YES, TIMED YESWHERE NAME LIKE memory/%;选择性启用关键组件监控UPDATE performance_schema.setup_instrumentsSET ENABLED YESWHERE NAME IN (memory/sql/thd::main_mem_root,memory/sql/JOIN_CACHE,memory/innodb/mem0mem,memory/temptable/%);7.2 定期维护内存统计信息sql备份当前内存统计CREATE TABLE memory_stats_backup ASSELECT NOW() AS collect_time,FROM performance_schema.memory_summary_global_by_event_nameWHERE CURRENT_NUMBER_OF_BYTES_USED 0;重置统计谨慎操作会清除历史数据TRUNCATE TABLE performance_schema.memory_summary_global_by_event_name;八、源码级解读内存分配事件主要分为以下几类memory/sql/xxxSQL层内存分配memory/innodb/xxxInnoDB存储引擎内存如使用MyISAM则为memory/myisam/xxxmemory/temptable/xxx临时表相关内存在源码sql/sql_class.h中对main_mem_root的描述如下c/This memory root is used for two purposes:for conventional queries, to allocate structures stored in main_lexduring parsing, and allocate runtime data (execution plan, etc.)during execution.for prepared queries, only to allocate runtime data. The parsedtree itself is reused between executions and thus is stored elsewhere./MEM_ROOT main_mem_root;mem0mem是InnoDB引擎中用于内存分配与管理的核心模块。九、总结MySQL 5.7提供了从宏观到微观的全方位内存监控能力1. 多维度监控支持主机、用户、线程、事件等多角度交叉分析。2. 事件分类识别可清晰区分SQL层、存储引擎、临时表等不同组件内存使用。3. 深度关联分析结合线程信息可追踪至具体SQL语句。4. 历史趋势对比通过历史与当前数据对比及时发现异常模式。建议在生产环境中建立定期内存监控机制设置合理的阈值告警从而在内存问题萌芽阶段及时干预保障数据库系统稳定运行。来源小程序app开发|ui设计|软件外包|IT技术服务公司-木风未来科技-成都木风未来科技有限公司