MariaDB 10.6 InnoDB setttings for unit-tests: maximize performance at the expense of persistence

Question

I have a MariaDB 10.6 the db is used for phpunit tests. The database is dropped and recreated each time I run the unit tests.

I would like to maximize the speed of the interactions (read/write) with this db while I totally don't care about persistence.

I was thinking of converting all tables to MEMORY engine, I can't find detailed infos about this engine on mariadb docs, but the mysql docs says that TEXT/BLOB fields are not supported as well as transactions. So, no way.

I was thinking of converting the tables to TEMPORARY tables, but this was based on the false belief that they are stored in RAM.

I came across the idea of running InnoDB on RAM setting --datadir=/dev/shm/mysql/, but elsewhere I read that this is not a good idea 'cause MariaDB won't be able to handle a reboot.

So I think that the best thing to do is to tweak InnoDB settings to maximize performance at the expense of persistence.

I saw this answer but it refers to MySQL 5.6 and a lot of things changed in InnoDB since that release.

What settings do you recommend?

Answer 1

I experimented a lot with different methods of loading data for a consulting client who wanted a 1TB-sized dataset for doing testing, and they wanted to minimize time to reload data to the initial state. I put some of the findings in a presentation: Load Data Fast!

But ultimately, the solution the client went with was to load their test database once, then use an LVM snapshot to save the initial state of the filesystem with the datadir. Then they could revert to that snapshot repeatedly. It was much quicker to do that than to reload data. You should do a clean shutdown of InnoDB before making the LVM snapshot, to make startup quicker.

Another solution would be to use transportable tablespaces. That is, load the test tables once, flush and export the tablespaces, and copy them to another directory as a canonical initial state of data. Then before each test run, do the reverse operation. See https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces for the details on this method. It will take some work on your part to develop scripts to do it during testing setup.

If you're committed to reloading data during each test run, some InnoDB settings that would help reduce durability:

• innodb_doublewrite=0
• innodb_flush_log_at_trx_commit=0
• innodb_flush_log_at_timeout=60 (or more)
• innodb_log_buffer_size=256MB (or more)
• innodb_log_file_buffering=ON (MariaDB 10.8.4)
• disable binary log
• disable query logs

Answer 2

To set things up, load the database(s) in the MySQL instance with the desired data. Then use mysqldump to dump that data to a file, say, base.sql. Be sure to include the option to include DROP DATABASEs built in. Also, if you have stored routines (procs, functions, triggers), be sure to ask for them.

Then, at the start of each new 'test', simply do

mysql -u ... -p... < base.sql

That will clear out all the old data, rebuild the tables, and populate them.