当 GreatSQL 数据库处于高并发高负载时，内存可能会发现 mysqld 进程的消耗析内存消耗远远超出设置的 innodb_buffer_pool_size 时，有时候甚至会高达甚至超过系统内存的异常应用90%，遇到这种问题时，排查心里经常会发慌，攻略担心下一秒内存就会爆了发生 OOM，从系层面或者数据库hang死不响应。入分 本文和大家试着使用 GreatSQL 中的内存 sys schema 和 performance_schema 进行深入分析，找出内存消耗大户的消耗析源头，并尽可能解决问题。异常应用 
下面是排查详细的排查方法和步骤。 1. 确认实际内存消耗1.1 操作系统层面分析先检查确认 mysqld 进程的攻略内存具体消耗占用情况，做到心里有数，从系层面避免真的入分下一秒就发生 OOM 的问题： 复制$ free -ht free -ht total used free shared buff/cache available Mem: 30Gi 28Gi 240Mi 33Mi 2.0Gi 1.7Gi Swap: 0B 0B 0B Total: 30Gi 28Gi 240Mi $ ps aux | grep mysqld mysql 51931 23.0 89.8 32100800 29008060 ? Ssl Nov22 949:41 /data/apps/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64/bin/mysqld $ top -p $(pidof mysqld) -n 1 top - 05:36:37 up 4 days, 4:06, 1 user, load average: 5.56, 8.70, 10.87 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 8.4 us, 1.7 sy, 0.0 ni, 86.6 id, 3.4 wa, 0.0 hi, 0.0 si, 0.0 st MiB Mem : 31553.3 total, 265.6 free, 29148.6 used, 2139.2 buff/cache MiB Swap: 0.0 total, 0.0 free, 0.0 used. 1903.3 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 51931 mysql 20 0 30.6g 27.7g 4656 S 80.0 89.8 949:51.99 mysqld1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19. 在上述结果中重点关注几个指标： RES（Resident Memory）：物理内存占用，约 27.7G。内存VIRT（Virtual Memory）：虚拟地址空间大小，约 30.6G。看到 mysqld 进程当前内存消耗占比约 90%，还算可控，没到火烧眉毛的境地。 继续使用 pmap 查看 mysqld 进程中的内存分布情况： 复制$ pmap -x $(pidof mysqld) | sort -k3 -rn | head -n 20 total kB 32100804 29029796 29016940 00007f8a484d8000 5368992 5361664 5361664 rw--- [ anon ] 00007f892533d000 4698892 4691952 4691952 rw--- [ anon ] 00007f87e961e000 4564872 4556784 4556784 rw--- [ anon ] 00007f86adbe0000 4296832 4290544 4290544 rw--- [ anon ] 00007f86298bb000 1023252 1015792 1015792 rw--- [ anon ] 00007f866c350000 979648 979632 979632 rw--- [ anon ] 00007f87b8112000 719800 712688 712688 rw--- [ anon ] 00007f89046d4000 451760 444400 444400 rw--- [ anon ] 0000000005afc000 286800 282640 282640 rw--- [ anon ] 00007f8ba7715000 200300 200276 200276 rw--- [ anon ] 00007f8578000000 131072 131072 131072 rw--- [ anon ] 00007f8570000000 131072 131072 131072 rw--- [ anon ] 00007f8568000000 131072 131072 131072 rw--- [ anon ] 00007f8560000000 131072 131072 131072 rw--- [ anon ] 00007f8558000000 131072 131072 131072 rw--- [ anon ] 00007f8550000000 131048 131048 131048 rw--- [ anon ] 00007f8438000000 130668 130668 130668 rw--- [ anon ] 00007f8b98000000 65536 65536 65536 rw--- [ anon ] 00007f8b94000000 65536 65536 65536 rw--- [ anon ]1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22. 看到大量的匿名内存（anon）消耗较多内存，服务器托管这可能是由以下几个原因引起的： 动态分配的内存：GreatSQL 在运行过程中会频繁地进行内存分配和释放，这些内存通常是以匿名映射的形式存在于进程的虚拟地址空间中。 例如，GreatSQL 的线程池、缓存、临时表等都会动态分配内存。 缓冲区和缓存：GreatSQL 使用大量的缓冲区和缓存来提高性能，例如InnoDB Buffer Pool（以下简称 IBP）、InnoDB Log Buffer等。 这些缓冲区和缓存通常会占用大量的匿名内存。 线程堆栈： 每个线程都有自己的堆栈空间，这些堆栈空间也是匿名内存的一部分。 为了响应用户请求而创建了大量线程，那么这些线程的堆栈空间会占用不少内存。 临时文件： GreatSQL 在处理大查询或排序操作时，可能会使用临时表、临时文件，其内存映射也会占用匿名内存。 内存泄漏： 如果 GreatSQL 存在内存泄漏问题，也会导致匿名内存不断增加。云服务器提供商 可以针对上述各个模块/维度做进一步排查分析。 1.2 检查 IBP 内存相关配置使用以下 SQL 命令查询 IBP 等内存消耗较多的相关模块内存配置 复制greatsql> SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; +-------------------------+-------------+ | Variable_name | Value | +-------------------------+-------------+ | innodb_buffer_pool_size | 21474836480 | +-------------------------+-------------+ greatsql> SHOW GLOBAL VARIABLES LIKE innodb_log_buffer_size; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 33554432 | +------------------------+----------+1.2.3.4.5.6.7.8.9.10.11.12.13. 从上面可见 IBP 设置为 20G，但是 mysqld 进程的内存占用为 27.7G，超过 IBP 较多，这可能是由于用户的 SQL 请求（比如效率较低的慢查询 SQL）其他模块或线程引起。还需要继续排查。 2. 利用 Performance Schema 排查内存消耗来源从 5.6.6 版本开始，Performance Schema 默认启用，是一个内置的性能诊断工具，用于实时监控和分析 GreatSQL 服务器的运行状态。它提供了详细的性能数据，包括 内存分配的全局视图、SQL 语句的执行时间、线程活动、锁等待等详细信息，帮助开发者和 DBA 识别和解决性能瓶颈。 2.1 按内存模块查看占用使用 memory_summary_global_by_event_name 按模块查看内存分配情况： 复制greatsql> USE performance_schema; greatsql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED AS memory_bytes, CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS memory_mb FROM performance_schema.memory_summary_global_by_event_name WHERE CURRENT_NUMBER_OF_BYTES_USED > 0 ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10; +--------------------------------------------------------------------+--------------+----------------+ | EVENT_NAME | memory_bytes | memory_mb | +--------------------------------------------------------------------+--------------+----------------+ | memory/innodb/buf_buf_pool | 21957836800 | 20940.62500000 | | memory/group_rpl/GCS_XCom::xcom_cache | 1070853221 | 1021.24521351 | | memory/mysys/IO_CACHE | 84149952 | 80.25164795 | | memory/performance_schema/events_statements_summary_by_digest | 42240000 | 40.28320313 | | memory/innodb/log_buffer_memory | 33555440 | 32.00096130 | | memory/innodb/ut0link_buf | 25165888 | 24.00006104 | | memory/innodb/lock0lock | 22440096 | 21.40054321 | | memory/sql/TABLE | 15646883 | 14.92203045 | | memory/performance_schema/events_statements_history_long | 15040000 | 14.34326172 | | memory/performance_schema/events_errors_summary_by_thread_by_error | 14561280 | 13.88671875 | +--------------------------------------------------------------------+--------------+----------------+ 10 rows in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27. EVENT_NAME：具体内存分配的模块名称，如 memory/innodb/buffer_pool、memory/sql/temporary_table 等。CURRENT_NUMBER_OF_BYTES_USED：当前分配的云服务器内存总量。例如： 如果 memory/innodb/buf_buf_pool 值较高，说明 InnoDB buffer pool 占用较高。其他几个包含 memory/innodb 关键字的，也都是和 InnoDB 存储引擎相关的内存模块。其中 memory/mysys/MY_BITMAP::bitmap 主要用于管理位图数据结构 (bitmap) 的内存使用。它的设计初衷是为了实现高效的位存储与处理，主要用于存储和操作需要标志位（bit flag）来跟踪或控制的数据。其具体存储的数据包括但不限于：索引或表分区的状态：位图被用来记录索引或分区的使用状态。例如，在分区表扫描时，通过位图可以高效管理哪些分区需要扫描或已经扫描。 事务或锁状态：记录事务的特定标志位或锁的使用状态，比如资源锁（resource locks）的分配状态。 InnoDB 的内部操作：位图被用于跟踪一些内部存储引擎的优化过程，例如自适应哈希索引、页的脏位（dirty bit）标记等。 线程管理：管理线程池中线程的分配和使用状态。 性能统计：在某些性能分析的场景下，位图用于记录启用或禁用的统计模块。 其中 memory/group_rpl/GCS_XCom::xcom_cache 是 MGR Xcom cache，在 GreatSQL 8.0.32-26 中初始默认值即为 1GB，详情参考 Xcom cache分配静态化。其他几个包含 group_rpl 关键字特征的，也是和 MGR 相关的模块。其中 memory/mysys/IO_CACHE 是一个重要的内存管理模块，主要用于管理和优化文件I/O操作。IO_CACHE 提供了一个高效的缓存机制，可以显著提高文件读写操作的性能。主要存储的数据有：临时文件数据：排序、分组、联接等操作过程中生成的中间结果。 二进制日志：binlog 的写入和读取操作中使用缓存。 文件块：GreatSQL 访问文件时，将数据块加载到缓存中，避免重复读取。 表数据：表扫描或索引扫描时，用于缓存表或索引的数据。 如果 memory/sql/temporary_table 值较高，说明内存被临时表消耗。 如果 memory/innodb/hash_index 值较高，可能是 InnoDB 的自适应哈希索引占用内存。 上面的查询结果表明，memory/innodb/buf_buf_pool(IBP) 占用内存约 20G，memory/group_rpl/GCS_XCom::xcom_cache(MGR XCom Cache) 占用内存约 1G，都是符合预期的。但是 memory/mysys/IO_CACHE 占用的内存较高，需要重点排查。 2.2 跟踪各模块内存使用变化可以每间隔一段时间重复执行下面的 SQL 请求，观察各个模块的内存消耗变化，找出内存消耗增长较快的模块，它们可能就是导致 mysqld 进程消耗较大内存的“元凶”。 复制greatsql> USE performance_schema; greatsql> SELECT EVENT_NAME, SUM(SUM_NUMBER_OF_BYTES_ALLOC) / 1024 / 1024 AS total_memory_mb FROM performance_schema.memory_summary_global_by_event_name GROUP BY EVENT_NAME ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC LIMIT 10; +---------------------------------------------+------------------+ | EVENT_NAME | total_memory_mb | +---------------------------------------------+------------------+ | memory/innodb/memory | 3688428.98232269 | | memory/mysys/MY_BITMAP::bitmap | 289065.08729172 | | memory/group_rpl/transaction_data | 219301.70309544 | | memory/group_rpl/Gcs_message_data::m_buffer | 219176.21560478 | | memory/mysys/IO_CACHE | 102064.87601471 | | memory/group_rpl/GCS_XCom::xcom_cache | 57685.34130669 | | memory/sql/Log_event | 47153.59659863 | | memory/group_rpl/write_set_encoded | 35822.83545971 | | memory/innodb/buf_buf_pool | 20940.62500000 | | memory/group_rpl/certification_data | 11146.79415703 | +---------------------------------------------+------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25. 结合前面各模块当前占用的内存情况，从上述查询结果综合分析看，较大概率应该就是 memory/mysys/IO_CACHE 模块消耗内存过大。 2.3 按线程查看内存占用接着继续查看各线程内存占用情况，确认是否有个别线程（尤其是长连接线程）消耗了过多内存资源。使用 memory_summary_by_thread_by_event_name 查看各线程的内存分配，同时关联查询 threads 视图，可以显示各线程当前正在执行的 SQL 请求及其执行耗时： 复制-- 1. 查看各线程当前的内存分配情况 greatsql> USE performance_schema; greatsql> SELECT m.EVENT_NAME, m.COUNT_ALLOC, m.CURRENT_NUMBER_OF_BYTES_USED AS mem_sum, (m.CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024.0) AS mem_sum_mb, t.NAME, t.TYPE, t.PROCESSLIST_ID, LEFT(t.PROCESSLIST_INFO, 10) FROM memory_summary_by_thread_by_event_name m JOIN threads t USING (THREAD_ID) WHERE t.PROCESSLIST_ID != CONNECTION_ID() ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 20; +-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+ | EVENT_NAME | COUNT_ALLOC | mem_sum | mem_sum_mb | NAME | TYPE | PROCESSLIST_ID | LEFT(t.PROCESSLIST_INFO, 10) | +-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+ | memory/innodb/memory | 13 | 21888 | 0.02087402 | thread/group_rpl/THD_applier_module_receiver | FOREGROUND | 12 | Group repl | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39893 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39894 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39895 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39896 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39897 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39898 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39899 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39900 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39901 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39902 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39903 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39904 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39905 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39906 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39907 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39908 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39909 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39910 | load data | | memory/sql/THD::main_mem_root | 3 | 20576 | 0.01962280 | thread/sql/one_connection | FOREGROUND | 39911 | load data | +-------------------------------+-------------+---------+------------+----------------------------------------------+------------+----------------+------------------------------+ -- 2. 查看各线程汇总的内存分配情况 greatsql> SELECT m.EVENT_NAME, m.COUNT_ALLOC, m.SUM_NUMBER_OF_BYTES_ALLOC AS mem_sum, (m.SUM_NUMBER_OF_BYTES_ALLOC / 1024 / 1024.0) AS mem_sum_mb, t.NAME, t.TYPE, t.PROCESSLIST_ID, LEFT(t.PROCESSLIST_INFO, 10) FROM memory_summary_by_thread_by_event_name m JOIN threads t USING (THREAD_ID) WHERE t.PROCESSLIST_ID != CONNECTION_ID() ORDER BY m.SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 20; +----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+ | EVENT_NAME | COUNT_ALLOC | mem_sum | mem_sum_mb | NAME | TYPE | PROCESSLIST_ID | LEFT(t.PROCESSLIST_INFO, 10) | +----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+ | memory/sql/Log_event | 818062681 | 36821553500 | 35115.76986313 | thread/group_rpl/THD_applier_module_receiver | FOREGROUND | 12 | Group repl | | memory/innodb/memory | 258356 | 266640048 | 254.28776550 | thread/sql/one_connection | FOREGROUND | 40222 | load data | | memory/innodb/memory | 255478 | 263811432 | 251.59018707 | thread/sql/one_connection | FOREGROUND | 40204 | load data | | memory/innodb/memory | 217298 | 224575448 | 214.17183685 | thread/sql/one_connection | FOREGROUND | 40209 | load data | | memory/innodb/memory | 212201 | 219160304 | 209.00755310 | thread/sql/one_connection | FOREGROUND | 40215 | load data | | memory/innodb/memory | 209052 | 215978440 | 205.97309113 | thread/sql/one_connection | FOREGROUND | 40212 | load data | | memory/innodb/memory | 203823 | 210364872 | 200.61957550 | thread/sql/one_connection | FOREGROUND | 40220 | load data | | memory/innodb/memory | 201921 | 208627128 | 198.96233368 | thread/sql/one_connection | FOREGROUND | 40224 | load data | | memory/innodb/memory | 195252 | 202055944 | 192.69556427 | thread/sql/one_connection | FOREGROUND | 40214 | load data | | memory/innodb/memory | 193319 | 199526048 | 190.28286743 | thread/sql/one_connection | FOREGROUND | 40208 | load data | | memory/innodb/memory | 192498 | 198820216 | 189.60973358 | thread/sql/one_connection | FOREGROUND | 40227 | load data | | memory/innodb/memory | 191717 | 198099104 | 188.92202759 | thread/sql/one_connection | FOREGROUND | 40205 | load data | | memory/innodb/memory | 191234 | 197764864 | 188.60327148 | thread/sql/one_connection | FOREGROUND | 40202 | load data | | memory/innodb/memory | 190012 | 196401888 | 187.30343628 | thread/sql/one_connection | FOREGROUND | 40216 | load data | | memory/innodb/memory | 189098 | 195217576 | 186.17398834 | thread/sql/one_connection | FOREGROUND | 40207 | load data | | memory/innodb/memory | 188670 | 195084304 | 186.04689026 | thread/sql/one_connection | FOREGROUND | 40223 | load data | | memory/innodb/memory | 187466 | 193563912 | 184.59693146 | thread/sql/one_connection | FOREGROUND | 40218 | load data | | memory/innodb/memory | 187045 | 193354488 | 184.39720917 | thread/sql/one_connection | FOREGROUND | 40217 | load data | | memory/innodb/memory | 186838 | 193196152 | 184.24620819 | thread/sql/one_connection | FOREGROUND | 40219 | load data | | memory/innodb/memory | 186465 | 192576408 | 183.65517426 | thread/sql/one_connection | FOREGROUND | 40210 | load data | +----------------------+-------------+-------------+----------------+----------------------------------------------+------------+----------------+------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88. 从上面的查询结果可见，当前有较多的 LOAD DATA 请求正在运行，有可能是它们导致的内存占用较高的原因。 其中 CURRENT_NUMBER_OF_BYTES_USED 表示当前分配但尚未释放的内存块的聚合大小。CURRENT_NUMBER_OF_BYTES_USED = SUM_NUMBER_OF_BYTES_ALLOC − SUM_NUMBER_OF_BYTES_FREE。SUM_NUMBER_OF_BYTES_ALLOC 表示已分配内存块的聚合大小。SUM_NUMBER_OF_BYTES_FREE 表示已释放内存块的聚合大小。排查分析道这里，基本上可以推断是由于有大量并发 LOAD DATA 导入数据请求导致 mysqld 内存占用较高。 3. 利用 sys schema 简化分析相对于用 Performance Schema 排查分析，采用 sys schema 分析则更简单省事。接下来介绍如何利用 sys schema 分析。 GreatSQL sys schema 是一组视图、存储过程和函数的集合，它基于 performance_schema 提供了更易读和易用的性能数据汇总。sys schema 通过简化复杂的性能指标，帮助数据库管理员和开发人员快速诊断和优化 GreatSQL 的性能问题。 3.1 查看全局及各模块内存分布首先，查看当前全部内存分配情况： 复制greatsql> USE sys; greatsql> SELECT * FROM memory_global_total; +-----------------+ | total_allocated | +-----------------+ | 22.08 GiB | +-----------------+1.2.3.4.5.6.7. 在 IBP 设置为 20G 的前提下，从 memory_global_total 查询到的内存分配总数并没有超过太多，说明较大可能性是由于用户的 SQL 请求（比如效率较低的慢查询 SQL）或其他模块引起。 继续查询内存使用的全局分布情况： 复制greatsql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 20; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/buf_buf_pool | 160 | 20.45 GiB | 130.88 MiB | 160 | 20.45 GiB | 130.88 MiB | | memory/group_rpl/GCS_XCom::xcom_cache | 4295 | 1018.00 MiB | 242.71 KiB | 463303 | 1.13 GiB | 2.55 KiB | | memory/mysys/IO_CACHE | 175 | 280.82 MiB | 1.60 MiB | 539 | 906.46 MiB | 1.68 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/innodb/log_buffer_memory | 1 | 32.00 MiB | 32.00 MiB | 1 | 32.00 MiB | 32.00 MiB | | memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB | | memory/innodb/lock0lock | 9893 | 21.40 MiB | 2.22 KiB | 9893 | 21.40 MiB | 2.22 KiB | | memory/sql/TABLE | 5796 | 17.49 MiB | 3.09 KiB | 5798 | 17.50 MiB | 3.09 KiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 13.89 MiB | 55.33 KiB | 257 | 13.89 MiB | 55.33 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 13.66 MiB | 13.66 MiB | 1 | 13.66 MiB | 13.66 MiB | | memory/innodb/memory | 7583 | 12.28 MiB | 1.66 KiB | 8812 | 16.80 MiB | 1.95 KiB | | memory/performance_schema/file_instances | 4 | 11.00 MiB | 2.75 MiB | 4 | 11.00 MiB | 2.75 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 9.32 MiB | 9.32 MiB | 1 | 9.32 MiB | 9.32 MiB | | memory/performance_schema/table_handles | 1 | 9.06 MiB | 9.06 MiB | 1 | 9.06 MiB | 9.06 MiB | | memory/mysys/KEY_CACHE | 3 | 8.00 MiB | 2.67 MiB | 3 | 8.00 MiB | 2.67 MiB | | memory/innodb/sync0arr | 3 | 7.03 MiB | 2.34 MiB | 3 | 7.03 MiB | 2.34 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29. 在 sys schema 中，大部分视图都同时存储原始数据以及格式化后可读性更强的两种视图。所以上面的 SQL 查询还可以改成查询原始未格式化的视图： 复制greatsql> SELECT * FROM sys.x$memory_global_by_current_bytes LIMIT 20; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/buf_buf_pool | 160 | 21957836800 | 137236480.0000 | 160 | 21957836800 | 137236480.0000 | | memory/group_rpl/GCS_XCom::xcom_cache | 4068 | 1067435559 | 262398.1217 | 463303 | 1208663474 | 2608.7970 | | memory/mysys/IO_CACHE | 126 | 206147792 | 1636093.5873 | 539 | 950487072 | 1763426.8497 | | memory/performance_schema/events_statements_summary_by_digest | 1 | 42240000 | 42240000.0000 | 1 | 42240000 | 42240000.0000 | | memory/innodb/log_buffer_memory | 1 | 33555440 | 33555440.0000 | 1 | 33555440 | 33555440.0000 | | memory/innodb/ut0link_buf | 2 | 25165888 | 12582944.0000 | 2 | 25165888 | 12582944.0000 | | memory/innodb/lock0lock | 9893 | 22440096 | 2268.2802 | 9893 | 22440096 | 2268.2802 | | memory/sql/TABLE | 5796 | 18341476 | 3164.5059 | 5798 | 18351820 | 3165.1983 | | memory/performance_schema/events_statements_history_long | 1 | 15040000 | 15040000.0000 | 1 | 15040000 | 15040000.0000 | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 14561280 | 56658.6770 | 257 | 14561280 | 56658.6770 | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 14321664 | 14321664.0000 | 1 | 14321664 | 14321664.0000 | | memory/innodb/memory | 7562 | 12858512 | 1700.4115 | 8812 | 17615632 | 1999.0504 | | memory/performance_schema/file_instances | 4 | 11534336 | 2883584.0000 | 4 | 11534336 | 2883584.0000 | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 10240000 | 10240000.0000 | 1 | 10240000 | 10240000.0000 | | memory/performance_schema/memory_summary_by_thread_by_event_name | 1 | 9768960 | 9768960.0000 | 1 | 9768960 | 9768960.0000 | | memory/performance_schema/table_handles | 1 | 9502720 | 9502720.0000 | 1 | 9502720 | 9502720.0000 | | memory/mysys/KEY_CACHE | 3 | 8390864 | 2796954.6667 | 3 | 8390864 | 2796954.6667 | | memory/innodb/sync0arr | 3 | 7373032 | 2457677.3333 | 3 | 7373032 | 2457677.3333 | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29. 从上面两个查询结果可知，除了 IBP 和 MGR 之外，模块 memory/mysys/IO_CACHE 占用的内存最高，是重点分析排查对象。 查看 sys.memory_global_by_current_bytes 视图定义，可知它的原始数据来自 performance_schema： 复制greatsql> SHOW CREATE VIEW sys.memory_global_by_current_bytes\G | 
喜欢
讨厌