MySQL替换默认内存分配器为tcmalloc

原创

保持热爱奔赴山海

发布于 2023-10-11 13:20:10

3200

发布于 2023-10-11 13:20:10

文章被收录于专栏：饮水机管理员饮水机管理员

这是一篇库存的记录，翻出来，补充了下对于MySQL8的内容。

背景：生产上发现有套MySQL实例的内存占有率一直在涨，这台机器日常只有连接（查询、修改数据）

在经历几次的大批量导入工单的后，MySQL的内存占用高居不下。查了ps库，内存使用的几个指标都是正常的，怀疑是glibc的内存碎片导致的，怀疑是glibc的内存碎片导致的。

1 安装libunwind

cd /root/
wget --no-check-certificate http://download.savannah.gnu.org/releases/libunwind/libunwind-0.99-beta.tar.gz
tar xf libunwind-0.99-beta.tar.gz
cd libunwind-0.99-beta
CFLAGS=-fPIC ./configure --enable-shared
make CFLAGS=-fPIC
make CFLAGS=-fPIC install

2 安装gperftools

cd /root/
wget https://github.com/gperftools/gperftools/releases/download/gperftools-2.10/gperftools-2.10.tar.gz
tar xf gperftools-2.10.tar.gz
cd gperftools-2.10
./configure
make -j 8 && make install
echo "/usr/local/lib" >> /etc/ld.so.conf.d/local.conf

3 做个软链接

# tcmalloc包必须在/usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu 这几个路径下，这里我做个软链接

ln -s /usr/local/lib/libtcmalloc* /usr/lib64/

4 更新动态链接库的链接器程序缓存

ldconfig

5 vim /etc/my.cnf 增加malloc-lib参数

[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/mysql.pid
malloc-lib=/usr/lib64/libtcmalloc_minimal.so

6 然后重启mysqld进程使其生效

/etc/init.d/mysql restart

7 查看效果：

lsof -n |grep tcmalloc

mysqld     51243                 mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51251          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51252          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51253          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51254          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51255          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51256          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51257          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51258          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51259          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51260          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51261          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51265          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51266          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51267          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51268          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51269          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51270          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51281          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51282          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51283          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld     51243  51285          mysql  mem       REG              253,0    1277192   41400849 /usr/local/lib/libtcmalloc_minimal.so.4.5.10

###################################

上面这种方法是适配MySQL5.7及以前版本的（mysql8之后移除了mysqld_safe）。

如果是mysqld8的话，可以用如下方法：

方法1、命令行里面启动（不推荐，这种仅供临时测试）：

export LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so
/usr/sbin/mysqld  前台启动
lsof -n |grep tcmalloc     查看效果

方法2、systemd方式启动（生产一般用这种方式）

参考：

https://wiki.pha.pub/books/109-TAs/page/mysql-80-jemalloc

https://www.percona.com/blog/enabling-jemalloc-on-percona-server-for-mysql-5-7-and-8-0-series/

vim /usr/lib/systemd/system/mysqld.service 可以看到里面有下面的2行：

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

这里的 =- 表示：可选前缀"-"，表示如果文件不存在，则不会读取它，并且不会记录错误或警告消息。

vim /etc/sysconfig/mysql 编辑这个文件（没有的话 vim也会自动新建）加一行

LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so

效果如下：

$ cat /etc/sysconfig/mysql

LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so

然后重启下mysqld进程

systemctl daemon-reload
systemd restart mysqld

再次查看，可以看懂tcmalloc生效了

$ lsof -n |grep tcmalloc        （或者 lsof -Pn -p $(pidof mysqld) | grep malloc）               

mysqld    237116                mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_ibu 237116 237124         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_log 237116 237125         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_rd- 237116 237126         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_rd- 237116 237127         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_rd- 237116 237128         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_rd- 237116 237129         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_wr- 237116 237130         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_wr- 237116 237131         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_wr- 237116 237132         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_io_wr- 237116 237133         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_pg_flu 237116 237134         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_buf_lr 237116 237135         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_mo 237116 237136         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_ch 237116 237137         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_fl 237116 237138         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_fl 237116 237139         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_wr 237116 237140         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_wr 237116 237141         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_log_fi 237116 237142         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_lo 237116 237148         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_er 237116 237149         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_buf_re 237116 237150         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_src_ma 237116 237151         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_dict_s 237116 237152         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_fts_op 237116 237153         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld    237116 237155         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
gr_handle 237116 237156         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
gr_handle 237116 237157         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
rocksdb:l 237116 237158         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
rocksdb:h 237116 237159         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld    237116 237190         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
rdb_bg    237116 237191         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
rdb_drop_ 237116 237192         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
mysqld    237116 237193         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
rdb_compa 237116 237194         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_buf_du 237116 237198         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_clone_ 237116 237199         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_pu 237116 237200         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_wk 237116 237201         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_wk 237116 237202         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
ib_srv_wk 237116 237203         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
sig_handl 237116 237204         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
gtid_zip  237116 237205         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237264         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237265         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237266         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237281         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237282         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237283         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237288         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237289         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237290         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10
connectio 237116 237304         mysql  mem       REG              253,0    1277192    5770490 /usr/local/lib/libtcmalloc_minimal.so.4.5.10

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 MySQL

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 MySQL

登录后参与评论

0 条评论

热度

MySQL替换默认内存分配器为tcmalloc

MySQL替换默认内存分配器为tcmalloc

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐