As promised, my slides from FOSDEM25 and Pre-FOSDEM MySQL Belgian Days :
- MySQL Scalability & Systems Evaluation Overview 2025 [PDF]
- Profiling MySQL Memory & CPU Usage Directly From MySQL [PDF}
And since I was asked several times about MySQL test case demonstrating glibc-malloc memory fragmentation / leaks -- here are all the details :
Test Scenario To Stress Malloc
The main idea with test scenario is to execute the same test workload in several loops without restarting MySQL Server, and along test execution observe the level of used RAM by MySQL process. The test workload must progress step by step from low load level to high load (so on each loop we should progressively allocate more RAM from low to high load, and then free allocated RAM before the next loop).
Generally the main biggest RAM allocation in MySQL is InnoDB Buffer Pool (BP). And this memory is allocated on-demand until we don't reach the BP size limit, after what BP will no more grow and its memory will just be reused.
In the given test scenario we'll limit BP size to 32GB and progress with the following steps for each "full run" with 20 loops:
- create sysbench database
- generate 100GB data set to fill up 32GB BP (so all BP RAM will be allocated)
then on each "loop" :
- drop and recreate sysbench database
- load 20GB dataset
- execute OLTP_RW test workload with 1, 16, 64, 256, 1024, 1250, 1500, 2000 users
- each load level is executed during 5min
execute 20 loops like this, then switch to the next malloc allocator.
Detailed steps to reproduce the test scenario
1) deploy MySQL Server 8.4-LTS on your system
2) use the following configuration for MySQL Server :
[mysqld] max_connections = 4000 innodb_dedicated_server = ON innodb_buffer_pool_size = 32000M # to allow non-SSL & "native" connections require_secure_transport=OFF mysql-native-password=ON # switch Binlog=off to simplify the test case skip_log_bin=1
3) start MySQL Server with tcmalloc / with glibc-malloc
- by default MySQL is using glibc-malloc which is coming with your Linux distro
- you can use
LD_PRELOAD=/path/to/tcmalloc
to use tcmalloc or other - check with
pldd
which malloc library is used bymysqld
process
4) deploy and configure BMK-kit pkg in /BMK
- detailed HOWTO : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html
5) start the following Shell script to execute the described above test scenario :
#!/bin/bash TM=300 LOOPS=20 # load 100GB data bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 2>&1 sleep 20 for NN in $(seq 1 $LOOPS) do echo "CASE: == LOOP: $NN ==" | tee -a /var/tmp/log # recreate sysbench database mysql -u root -h 127.0.0.1 -P 5400 -f <<EOF drop database sysbench ; create database sysbench ; EOF # load 20GB data bash /BMK/sb_exec/sb11-Prepare_10M_8tab-InnoDB.sh 2>&1 sleep 20 # execute OLTP_RW test for usr in 1 16 64 256 1024 1250 1500 2000 do timeout $(($TM+60)) bash \ /BMK/sb_exec/sb11-OLTP_RW_10M_8tab-uniform-trx.sh $usr $TM 2>&1 sleep 10 done sleep 20 done echo "CASE: End.." | tee -a /var/tmp/log
6) monitor MySQL RAM usage (RSS) with "top" or "ps" or any other..
The Results
libc-malloc -vs- tcmalloc :
libc-malloc 10GB+ over 1 week :
jemalloc -vs- tcmalloc :
All in all, tcmalloc from "gperftools" was giving the best results in my case (and this is also without compromising performance). Will be curious to know what is working better for you on your systems, (and, just in case, how often you're hitting OOM-killer ;-))
Thank you for using MySQL !!