MariaDB on ramfs - CentOS 7 edition

In the series of horrible hacks to run fixtured tests more efficiently. I decided to play with some well known innodb settings, ramfs and input format. I made a a small script for creating schema, loading dump and dropping it via cli which outputs the cycles per second (I know it has a lot of overhead compared to persistent connection). world.sql was snatched from Other MySQL documentation (example databases).

#!/usr/bin/bash
round=0
trap exit SIGINT
while true ; do
  if ! (( $round % 10 )); then
    round=0
    starttime=$(date +%s.%N)
  fi
  database=$(date -Ins|md5sum|cut -d' ' -f1)
  mysqladmin create "$database"
  mysql "$database" < /root/world.sql
  mysqladmin --force drop "$database" > /dev/null
  ((round++))
  echo "scale=3;$round/($(date +%s.%N)-$starttime)"|bc
done

Testbed is a VM running CentOS 7 on 4 cores of i7-6700K, enough ram to cache input and database, a year old consumer level SSD and 10.1.17-MariaDB server.

Test runs

I began with repository provided stock settings and started adding innodb related settings and switched data-dir to ramfs.

Settings	Per second	Better than stock %
stock	2.342	0%
+innodb_flush_log_at_trx_commit=2	2.877	22%
+innodb_flush_method=O_DIRECT	2.920	24%
stock on ramfs	5.933	153%
+innodb_flush_log_at_trx_commit=2	6.077	159%

The dump file format also has quite an impact on the throughput. The original world.sql was 397KB and had one INSERT per row. After dumping with --extended-insert file size was reduced to 244k and ~5000 INSERTs molded into 3 INSERTs (1 per table). +combine commands meaning CREATE DATABASE and data loading is done through a single client session.

Settings	Per second	Better than stock %
optimized ramfs	6.077	159%
+extended-insert	19.389	727%
+combine commands	22.192	848%

So the throughput is roughly 10 times better compared to stock with yucky dump file. Setup and teardown time was reduced from 450ms to 45ms.

Setup stuff

tmpfs / ramfs mount in /etc/fstab (uid and gid might vary)

tmpfs   /var/lib/mysql  ramfs   rw,uid=995,gid=992,context=system_u:object_r:mysqld_db_t:s0 0   0

SELinux needs a small tweak cause of tmpfs/ramfs mount or else:

type=AVC msg=audit(1474832992.425:212): avc:  denied  { associate } for  pid=27007 comm="mysqld" name="mysql.sock" scontext=system_u:object_r:mysqld_var_run_t:s0 tcontext=system_u:object_r:mysqld_db_t:s0 tclass=filesystem

Add a policy snippet:

allow mysqld_var_run_t mysqld_db_t:filesystem associate;

Systemd configuration cleanup /etc/systemd/system/mariadb.service.d/cleanup.conf

[Service]
ExecStartPre=/usr/bin/bash -c "rm -rf /var/lib/mysql/* && cp -r /var/lib/mysql_clean/* /var/lib/mysql/ && chown -R mysql. /var/lib/mysql"

/var/lib/mysql_clean is a copy of data-dir after user setup and innodb_log_file_size adjustment.