作者：付祥

现居珠海，次要负责 Oracle、MySQL、mongoDB 和 Redis 保护工作。

本文起源：原创投稿

*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

异常现象

开发人员反馈，有一台服务器内存简直被 MySQL 耗尽了，执行 top 命令，输入如下：

这台机器是个测试环境，MySQL 是开发本人装置的，数据库版本 5.6.51 ，机器总内存32G，MySQL 占了29G。

剖析过程

查看 MySQL 启动工夫发现1个星期前才启动，猜想之前因为主机内存耗尽触发了 OOM ，查看 MySQL 谬误日志，发现每隔10几天，MySQL 就异样敞开一次：

2022-02-24 03:03:42 20981 [Note] InnoDB: Database was not shutdown normally!2022-03-13 02:31:40 4134 [Note] InnoDB: Database was not shutdown normally!2022-03-31 02:31:08 6846 [Note] InnoDB: Database was not shutdown normally!2022-04-12 02:31:41 1159 [Note] InnoDB: Database was not shutdown normally!2022-04-23 04:41:51 6773 [Note] InnoDB: Database was not shutdown normally!2022-05-04 02:31:52 2499 [Note] InnoDB: Database was not shutdown normally!2022-05-13 04:56:06 23010 [Note] InnoDB: Database was not shutdown normally!2022-05-30 02:31:33 3244 [Note] InnoDB: Database was not shutdown normally!

查看操作系统日志，进一步验证了 MySQL 耗尽主机内存，触发 OOM ：

# grep oom-killer /var/log/messages* /var/log/messages-20220605:May 30 02:31:30 vm10-136-9-24 kernel: mysqld invokedoom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0

查看 my.cnf 配置文件，发现简直都是默认配置，innodb_buffer_pool_size 等于默认值128M :

mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+| Variable_name | Value               | +-------------------------+-----------+| innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+

以后数据库有500多个连贯，都是 Sleep 状态，从 MySQL 谬误日志发现 performance_schema 下的表构造全是谬误的，预计 MySQL 版本做了降级，没有执行 upgrade升 级数据字典，意味着一些内存诊断信息便不能从 PS 获取：

2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'cond_instances' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_current' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_history' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_history_long' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrongstructure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_by_instance' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrongstructure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrongstructure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrongstructure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'events_waits_summary_global_by_event_name' has the wrongstructure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'file_instances' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'file_summary_by_event_name' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'file_summary_by_instance' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table 'performance_schema'.'host_cache'has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'mutex_instances' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'objects_summary_global_by_type' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'performance_timers' has the wrong structure2022-06-09 11:19:08 27468 [ERROR] Native table'performance_schema'.'rwlock_instances' has the wrong structure.....省略输入.........mysql> show variables like 'performance_schema'; +--------------------+-------+| Variable_name | Value | +--------------------+-------+| performance_schema | ON | +--------------------+-------+1 row in set (0.00 sec)mysql> use performance_schemaDatabase changedmysql> show tables;Empty set (0.00 sec)

show engine innodb status ，截取内存信息如下：

BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 736104382Buffer pool size 8191Free buffers 1024Database pages 6851Old database pages 2508Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1280745, not young 13379564731.59 youngs/s, 0.10 non-youngs/sPages read 97753005, created 121179, written 11633600.06 reads/s, 0.88 creates/s, 12.12 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 6851, unzip_LRU len: 0 I/O sum[597]:cur[0], unzip sum[0]:cur[0]

数据字典分配内存居然达到700M，通过 lsof 命令发现，MySQL 关上了大量的 MyISAM 表分区文件，每个分区占用2个文件句柄，这应该是其内存应用高的起因：

lsof|grep "#P#"|grep -E "MYD$|MYI$"|wc -l29826

MyISAM 存储引擎，数据块通过操作系统缓存，索引块由 key buffer 缓存，大小通过参数 key_buffer_size 管制，以后默认值如下：

mysql> show variables like 'key_buffer_size'; +-----------------+---------+| Variable_name | Value     | +-----------------+---------+| key_buffer_size | 8388608 | +-----------------+---------+1 row in set (0.00 sec)

MySQL 默认应用 GLIBC 内存分配器，通过 gdb 调用 malloc_stats()函数剖析内存应用状况：

gdb -ex "call (void) malloc_stats()" --batch -p $(pidof mysqld)

上述命令执行实现后，会将内存应用状况打印到 MySQL 谬误日志：

Arena 0:system bytes = 2001301504in use bytes = 250961264Arena 1:system bytes = 12181504in use bytes = 1000800Arena 2:system bytes = 164257792in use bytes = 8032368Arena 3:system bytes = 1363267584in use bytes = 468958176Arena 4:system bytes = 335654912in use bytes = 708240Arena 5:system bytes = 2150400in use bytes = 254576Arena 6:system bytes = 32059392in use bytes = 1078000Arena 7:system bytes = 671559680in use bytes = 4884688Arena 8:system bytes = 44052480in use bytes = 935904Arena 9:system bytes = 43302912in use bytes = 2630256Arena 10:system bytes = 21729280in use bytes = 618848Arena 11:system bytes = 702341120in use bytes = 2745648Arena 12:system bytes = 63066112in use bytes = 1537360Arena 13:system bytes = 467128320in use bytes = 2199648Arena 14:system bytes = 1682067456in use bytes = 23873712Arena 15:system bytes = 1613938688in use bytes = 929648Arena 16:system bytes = 150749184in use bytes = 1593600Arena 17:system bytes = 1554382848in use bytes = 343840Arena 18:system bytes = 514367488in use bytes = 38418976Arena 19:system bytes = 88248320in use bytes = 3488848Arena 20:system bytes = 703705088in use bytes = 5674256Arena 21:system bytes = 469848064in use bytes = 417632Arena 22:system bytes = 172064768in use bytes = 2259808Arena 23:system bytes = 391884800in use bytes = 763104Arena 24:system bytes = 1414455296in use bytes = 26260272Arena 25:system bytes = 316915712in use bytes = 596432Arena 26:system bytes = 702865408in use bytes = 623840Arena 27:system bytes = 516800512in use bytes = 371040Arena 28:system bytes = 175669248in use bytes = 3301776Arena 29:system bytes = 26525696in use bytes = 1406640Arena 30:system bytes = 51970048in use bytes = 375072Arena 31:system bytes = 525869056in use bytes = 515651936Arena 32:system bytes = 363950080in use bytes = 696912Arena 33:system bytes = 1816637440in use bytes = 13213184Arena 34:system bytes = 1470251008in use bytes = 13774880Arena 35:system bytes = 703832064in use bytes = 2624144Arena 36:system bytes = 115941376in use bytes = 3248720Arena 37:system bytes = 777551872in use bytes = 978896Arena 38:system bytes = 45363200in use bytes = 45081504Arena 39:system bytes = 374652928in use bytes = 341904Arena 40:system bytes = 26222592in use bytes = 25993760Arena 41:system bytes = 20140032in use bytes = 386384Arena 42:system bytes = 702484480in use bytes = 700284096Arena 43:system bytes = 54947840in use bytes = 1650880Arena 44:system bytes = 516972544in use bytes = 3178016Arena 45:system bytes = 66084864in use bytes = 1186080Arena 46:system bytes = 1672466432in use bytes = 3988320Arena 47:system bytes = 3727360in use bytes = 1518624Arena 48:system bytes = 471752704in use bytes = 66314288Arena 49:system bytes = 491962368in use bytes = 2521952Arena 50:system bytes = 12431360in use bytes = 3573216Arena 51:system bytes = 58073088in use bytes = 720512Arena 52:system bytes = 24412160in use bytes = 1166080Arena 53:system bytes = 34963456in use bytes = 1003856Arena 54:system bytes = 28745728in use bytes = 3283728Arena 55:system bytes = 703614976in use bytes = 423398352Arena 56:system bytes = 31150080in use bytes = 30834032Arena 57:system bytes = 397848576in use bytes = 757680Arena 58:system bytes = 416169984in use bytes = 1561520Arena 59:system bytes = 702533632in use bytes = 4707824Arena 60:system bytes = 26615808in use bytes = 8339040Arena 61:system bytes = 174006272in use bytes = 317760Arena 62:system bytes = 3846144in use bytes = 3525664Arena 63:system bytes = 26365952in use bytes = 693104Total (incl. mmap):system bytes = 1166893056in use bytes = 348358880max mmap regions = 55max mmap bytes = 1919492096

将上述输入存入临时文件 /tmp/fx.txt ，计算调配给 MySQL 内存和 MySQL 应用内存如下：

# awk '{if($1 == "system") total+=$NF; else if ($1 == "in") used+=$NF }END{printtotal/1024/1024/1024,used/1024/1024/1024}' /tmp/fx.txt28.4044 2.87976

调配给 MySQL 内存28.4G，MySQL 应用内存才2.8G，表明内存碎片化太重大了，应用 google 内存分配器 tcmalloc 重启 MySQL 十几天后，top 命令察看 MySQL 占用内存稳固在5G：

小结

jemalloc 和 tcmalloc 之类的内存分配器在一些场景，尤其是在多核 CPU 和高并发工作负载下能提供更高效的性能，例如咱们熟知的 mongodb 应用 tcmalloc ，redis 应用 jemalloc 。