High Performance MySQL Test Database Setup

Sometimes your unit tests have run against a real database server. This post will not discuss the question whether this is avoidable or not. Please take a look at embedded database server (e.g: HSQLDB for Java and mocking techniques e.g: mockito) if you want to remove this dependency from your test environment.

Instead this post will show how to setup a blazing fast MySQL Server against which you can run your unit tests.

The main performance bonus is gained by the innodb_flush_log_at_trx_commit setting. This setting will change the flush behavior for the mysql server. Additionally linux tmpfs will be used to put the mysql data files directly into ram.

The mysql-tmpfs-wrapper.sh script will be used to replace the /etc/init.d/mysql start script. Symlink this file to /etc/init.d/myql-tmpfs.sh and use the sysv-rc-conf tool to remove the autostart/stop of the original /etc/init.d/mysql script. I used insserv /etc/init.d/mysql-tmpfs.sh to enable autostart for my wrapper script. {% gist https://gist.github.com/Jotschi/8792727 %}

The recreate_mysql_frombarecopy.sh will recreate the tmpfs ramdisk by using a bare copy of the mysql data directory. {% gist https://gist.github.com/Jotschi/8792866 %}

Add this mountpoint to your /etc/fstab file to define the mysql data tmpfs. You can use the stat tool to check the uid and gid for your mysql data directory (e.g: stat /opt/mysql/server-5.6/data)

none            /opt/mysql/server-5.6/data  tmpfs   defaults,size=1000M,uid=999,gid=1000,mode=0700          0       0

I suggest to not directly modify the my.cnf file. Instead use the default my.cnf file and add a includedir section to include files in /etc/mysql/conf.d). This step is of course not needed when you use the original debian mysql server.

[mysqld]
bind-address             = 0.0.0.0

thread_cache_size        = 8
query_cache_size         = 32M
thread_concurrency       = 8
key_buffer_size          = 256M
max_allowed_packet       = 500M
table_open_cache         = 512
sort_buffer_size         = 256M
net_buffer_length        = 8K
read_buffer_size         = 256K
read_rnd_buffer_size     = 8M
myisam_sort_buffer_size  = 64M

innodb_use_native_aio          = false
innodb_data_file_path          = ibdata1:10M:autoextend:max:15360M
innodb_file_format             = Barracuda
innodb_flush_method            = O_DIRECT
innodb_write_io_threads        = 64
innodb_read_io_threads         = 64

#innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size        = 2G
innodb_log_files_in_group      = 2
innodb_log_buffer_size         = 8M
innodb_log_file_size           = 128M

innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50

max_connect_errors       = 1000000
max_connections          = 350

The last important step is to create the bare copy. I suggest that you stripdown your mysql as much as possible. Example:

  • 1. Drop all databases except (mysql, perfomance_schema, information_schema)

  • 2. /etc/init.d/mysql stop

  • 3. Remove the ibdata and iblogfiles

  • 4. /etc/init.d/mysql start

  • 5. Mysql should work and should have recreated the ibdata and logfiles

  • 6. /etc/init.d/mysql stop

  • 7. cp -ra /opt/mysql/server-5.6/data /opt/mysql/server-5.6/databarecopy

  • 8. rm -rf /opt/mysql/server-5.6/data/*

  • 9. /etc/init.d/mysql-tmpfs.sh start

When put into lxc container a jenkins job can be created to restart the container on a regular basis and thus keep the mysql data directoy clean.

Happy testing!