As promised, my slides from FOSDEM25 and Pre-FOSDEM MySQL Belgian Days :

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 by mysqld process

4) deploy and configure BMK-kit pkg in /BMK

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 !!