Integration tests are a crucial part of the software development process, ensuring that different components of your application work seamlessly together. The faster the feedback, the faster release.
By conducting integration tests, development teams can have greater confidence in the reliability and performance of the complete software application. Tests not only verify the functionality of the code we have written but also ensure it behaves as expected for future developers when they make modifications.
In an ideal case, integration tests involve working with the database which is used in production to simulate real-world scenarios.
So how can we reduce the test runtime to give the engineers faster feedback and improve the CI pipeline when using MySQL or MariaDB?
Integration tests typically involve setting up a fresh test database, running test scenarios, and then tearing down the
database. Traditionally, this teardown process involves dropping and recreating tables in the database for each test case.
This is important so every test runs in an isolated environment to ensure that the test results are not affected by previous tests.
Having the ability to reset the database state to a known starting point is crucial for reproducing and investigating test
failures. Without this, debugging and fixing issues can become challenging.
Now imagine your integration test suite consists out of 500 tests. For every test, the database must be recreated. When the recreation process needs just one second more, the whole test suite would run more than 8 minutes longer.
Well, after switching from MariaDB to MySQL 8 some weeks ago, importing the database dump took not only 1 second more but 3. The whole process slowed down by a factor of 5.
MariaDB:
time mysql -uroot -ptest test < dump.sql
real 0m0.779s
MySQL 8:
time mysql -uroot -ptest test < dump.sql
real 0m3.802s
In summary, this means that a test suite with 500 tests would run 25 minutes longer than before which is absolutely not satisfactory.
After digging down the rabbit whole, I found out that it is not the data which needs longer to be imported but the CREATE TABLE command. There is also an open issue on the MySQL bug tracker: CREATE TABLE performance in MySQL 8 is considerably slower than 5.7
I found out that when using TRUNCATE TABLE instead of DROP TABLE and CREATE TABLE, the whole process got reduced by a factor of 2.
MariaDB:
time mysql -uroot -ptest test < dump_truncate.sql
real 0m0.449s
MySQL 8:
time mysql -uroot -ptest test < dump_truncate.sql
real 0m1.592s
At the moment, I am not sure why this is faster. According to the documentation, TRUNCATE drops and re-creates the table. Both are DDL statements and both require create and drop privileges. But looking at the CPU profiler, TRUNCATE does not call a creating table operation and has fewer operations.
CPU profiler for truncate:
| Status | Duration | CPU_user | CPU_system |
|---|---|---|---|
| starting | 0.000153 | 0.000153 | 0.000000 |
| Executing hook on transaction | 0.000006 | 0.000006 | 0.000000 |
| starting | 0.000048 | 0.000048 | 0.000000 |
| checking permissions | 0.009011 | 0.009391 | 0.000342 |
| waiting for handler commit | 0.000031 | 0.000047 | 0.000025 |
| waiting for handler commit | 0.000932 | 0.000000 | 0.000940 |
| query end | 0.000041 | 0.000000 | 0.000038 |
| closing tables | 0.000005 | 0.000000 | 0.000005 |
| waiting for handler commit | 0.000052 | 0.000000 | 0.000053 |
| freeing items | 0.000065 | 0.000000 | 0.000063 |
| cleaning up | 0.000018 | 0.000000 | 0.000018 |
CPU profiler for create:
| Status | Duration | CPU_user | CPU_system |
|---|---|---|---|
| starting | 0.000861 | 0.000300 | 0.000558 |
| Executing hook on transaction | 0.000020 | 0.000013 | 0.000007 |
| starting | 0.000121 | 0.000121 | 0.000000 |
| checking permissions | 0.000032 | 0.000031 | 0.000000 |
| Opening tables | 0.003244 | 0.003246 | 0.000000 |
| creating table | 0.017227 | 0.016526 | 0.000374 |
| After create | 0.000193 | 0.000127 | 0.000066 |
| waiting for handler commit | 0.000007 | 0.000004 | 0.000002 |
| waiting for handler commit | 0.000198 | 0.000136 | 0.000070 |
| query end | 0.000014 | 0.000009 | 0.000005 |
| closing tables | 0.000002 | 0.000002 | 0.000001 |
| waiting for handler commit | 0.000009 | 0.000006 | 0.000003 |
| freeing items | 0.000060 | 0.000059 | 0.000000 |
| cleaning up | 0.000017 | 0.000017 | 0.000000 |
You can easily migrate the dump to use TRUNCATE instead of DROP and CREATE altering the output of mysqldump with sed:
mysqldump --skip-add-drop-table -uroot -ptest test |
sed -r 's/CREATE TABLE (\`[^\`]+\`)/CREATE TABLE IF NOT EXISTS \1/g' |
sed -r 's/LOCK TABLES (\`[^\`]+\`)/TRUNCATE TABLE \1;\nLOCK TABLES \1/g' \
> dump.sql
The first line removes the DROP TABLE command. The second line adds the IF NOT EXISTS to the CREATE TABLE command and the third line prepends the LOCK TABLES command by the TRUNCATE TABLE command.
Since the test database with predefined fixtures for the integration test is usually very small, we can store the data in the memory which provides faster read and write access compared to traditional disk-based file systems.
MariaDB:
time mysql -uroot -ptest test < dump_truncate.sql
real 0m0.223s
MySQL 8:
time mysql -uroot -ptest test < dump_truncate.sql
real 0m0.786s
The whole import process has been again reduced by half for both, MySQL and MariaDB.
If you are using Docker, it is very easy to configure MySQL or MariaDB to use a tmpfs. Just add the option tmpfs to
your compose.yml.
services:
mysql8:
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: test
tmpfs:
- /var/lib/mysql:rw,noexec,nosuid,size=1G
In the example, a temporary filesystem with the size of 1 GB has been mounted to the data directory.
It is important to monitor your CI pipeline especially after switching services to detect slowdowns you didn't even think of.
Our integration test suite runtime has been reduced by 25 minutes to its original runtime before switching from MariaDB to MySQL.
Generally, MariaDB seems to be faster in here but with their current situation, we definitely do not relly on it. Let's see when the issue gets resolved on MySQL 8.
Hopefully, someone can explain to me why the TRUNCATE command runs significant faster and what the difference is.
Feel free to contact me!