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

背景： 生产上发现有套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 更新动态链接库的链接器程序缓存
shell 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方式启动（生产一般用这种方式）
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