MySQL 内存高
项目上数据库32G内存服务器,在buffer_pool给了10G,没有大SQL运行的情况下实际占用了28G。
默认内存分配器可能存在分配内存不回收的问题
-- 运行以下命令如果内存减少,可以考虑更新内存分配器
gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
内存分配器
-- 使用jemalloc
git clone https://github.com/jemalloc/jemalloc
cd jemalloc
./autogen.sh
./configure
make && make install
export LD_PRELOAD=/usr/local/lib/libjemalloc.so
-- 使用tcmalloc
git https://github.com/gperftools/gperftools
./configure
make && make install
export LD_PRELOAD=/usr/local/lib/libtcmalloc.so
# /usr/lib/systemd/system/mysqld.service 里添加环境变量
Environment="LD_PRELOAD=/usr/local/lib/libjemalloc.so"
-- 查看是否生效
lsof -p `pidof mysqld` | grep -i jemalloc
更换后,内存文档在12G左右。
使用 performance_schema 分析内存占用,在某些版本 performance_schema 可能存在内存泄露问题
-- 查看内存占用
SELECT a.`EVENT_NAME`,a.`CURRENT_NUMBER_OF_BYTES_USED`/1024/1024 FROM performance_schema.`memory_summary_global_by_event_name` a ORDER BY a.`CURRENT_NUMBER_OF_BYTES_USED` DESC limit 10;
SELECT SUM(a.`CURRENT_NUMBER_OF_BYTES_USED`)/1024/1024 FROM performance_schema.`memory_summary_global_by_event_name` a ;
SELECT thread_id, event_name, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_by_thread_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 20;
SELECT sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 FROM performance_schema.memory_summary_by_thread_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 20;
参考文档
https://www.fordba.com/mysql_paper01.html
https://cloud.tencent.com/developer/article/1852862
https://developer.aliyun.com/article/982917
MySQL 可以更换效率更高的内存分配器