Speed up integration tests 10 times: MySQL on ramdisk, Windows development rig

10 Apr 2017

For the most part, tests in the project I’m currently participating in are end-to-end integration tests; therefore, the database is being repeatedly accessed causing high disk I/O, which eventually becomes a bottleneck. Multiple improvement options had been suggested and discussed by the team prior to picking a final solution:

• Rewrite all tests so that actual writing/reading would be tested only for StorageService. We’d need to mock large amounts of data for other tests. Time consuming, tough, but seems to be the most correct way.
• Use ENGINE = MEMORY in database. I’ve experimented with this option for some time, and it turned out to have some pitfalls: replacing the engine for the testing environment only, but keeping the production environment intact was a non-trivial task in this project because of the limitations introduced by architecture. A lot of tests were broken (presumably because MEMORY engine does not support transactions). MEMORY cannot store text and blob fields that are used in some tables.
• A quick end easy option: use a database entirely stored in memory, since persistence is not really critical for tests. Pros: execution time reduces from 25-40 minutes to 3 minutes. The database engine remains unchanged; therefore the database behavior corresponds to the production environment. Cons: It’s necessary to install drivers and set up configs (a one-time task, but must be repeated for each workplace). A developer also has to keep in mind that he needs to switch between MySQL configurations before, and after, the tests (each time). Load tests might start giving unexpected results, as the performance would be affected. Finally, it is a kludge.
• Repeatedly delete and re-create data creating snapshots in-between the tests.

The imdisk mentioned above has a few more drawbacks: one should remember all the other software that shares the same MySQL instance or run it as a second instance bounded to another port, but it would require altering the configs.

Install imdisk

Define the environment variable called MYSQL_PATH that points to a MySQL installation directory, excluding /bin, e.g.: C:\Program Files\MySQL\MySQL Server 5.5.

Create a new config file named my-ram.ini:

[mysqld]
datadir="R:/data/"

innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_buffer_pool_size=256M
log-output=NONE
slow-query-log=0

Execute quicktests_prepare.cmd before running tests. Run quicktests_restore.cmd after the tests

quicktests_prepare.cmd
Stops the normal mysql service.
Creates a ramdisk for data storage.
Creates mysqlram service and instructs it to run with a custom config.
Starts mysqlram service.
Creates an empty table.

quicktests_restore.cmd
Stops and removes the mysqlram service.
Removes ramdisk.
Restores notmal mysql service.

Windows:

quicktests_prepare.cmd

@echo off

if "%MYSQL_PATH%"=="" (
        echo error: MYSQL_PATH environment variable is not set!
	exit /B
)

if not exist "%MYSQL_PATH%\my-ram.ini" (
        echo error: file "%MYSQL_PATH%\my-ram.ini" doesn't exist!
	exit /B
)

net stop mysql
net stop mysqlram

echo Initializing RAM disk.
imdisk -D -m R:
imdisk -a -s 3G -m R: -p "/FS:NTFS /C /Y"

mkdir R:\data
xcopy "%MYSQL_PATH%\data" "R:\data" /S /E

echo Installing mysqlram service.
"%MYSQL_PATH%\bin\mysqld" --install-manual mysqlram --defaults-extra-file="%MYSQL_PATH%\my-ram.ini"

net start mysqlram

echo Waiting for mysqlram service to start.
timeout 5 > NUL

echo Setting password.
echo SET PASSWORD FOR 'root'@'localhost' = PASSWORD('r00tP4ss'); | mysql -u root

echo Creating DB.
echo DROP DATABASE IF EXISTS `test_db`; CREATE DATABASE `test_db`; | mysql -u root -pr00tP4ss

echo All done. Ready to run tests.

quicktests_restore.cmd

@echo off

if "%MYSQL_PATH%"=="" (
        echo error: MYSQL_PATH environment variable is not set!
	exit /B
)

echo Removing mysqram service.
net stop mysqlram
sc delete mysqlram

sc start mysql

echo Removing RAM disk.
imdisk -D -m R:

echo All done.

Cygwin:

quicktests_prepare.sh

cmd /c chcp 65001

net stop mysql
net stop mysqlram

echo "Initializing RAM disk."
imdisk -D -m R:
imdisk -a -s 3G -m R: -p "/FS:NTFS /C /Y"

#mkdir "$(cygpath.exe -u "R:\data")"
cp -a "$(cygpath.exe -u "${MYSQL_PATH}\data")" "$(cygpath.exe -u "R:\data")"

echo "Installing mysqlram service."
"$(cygpath.exe -u "${MYSQL_PATH}\bin\mysqld")" --install-manual mysqlram --defaults-extra-file="${MYSQL_PATH}\my-ram.ini"

net start mysqlram

echo "Waiting for mysqlram service to start."
sleep 5

echo "Setting password."
echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('r00tP4ss');" | mysql -u root --password=""

echo "Creating DB."
echo "DROP DATABASE IF EXISTS \`test_db\`; CREATE DATABASE \`test_db\`;" | mysql -u root -pr00tP4ss

echo -e "All done. Ready to run tests.\a"

quicktests_restore.sh

cmd /c chcp 65001

#if "%MYSQL_PATH%"=="" (
#        echo error: MYSQL_PATH environment variable is not set!
#	exit /B
#)

echo "Removing mysqram service."
net stop mysqlram
sc delete mysqlram

sc start mysql

echo "Removing RAM disk."
imdisk -D -m R:

echo -e "All done.\a"