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?

  • What is the slow part? Creating the database(s) and table(s)? Or populating those tables?
    – Rick James
    Aug 17, 2022 at 18:30
  • 1
    @RickJames populating those table I believe
    – nulll
    Aug 18, 2022 at 8:28
  • 1
    How is the "populating" done? LOAD DATA (best); batched INSERTs (good); single-row INSERTs (terrible); other?
    – Rick James
    Aug 18, 2022 at 16:38
  • 1
    Single row inserts. Thanks for your advice of preferring LOAD DATA, I'll work on it but I have to balance the maintainability of the tests.
    – nulll
    Aug 20, 2022 at 9:52
  • Any reason you can't use a docker image pre-populated with data, that you can instantiate and delete at will in a matter of seconds?
    – mustaccio
    Aug 20, 2022 at 16:34

2 Answers 2


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
  • I would not recommend using a memory table, because it could change some of the behavior of your application, and therefore your tests wouldn't be reliable. Aug 21, 2022 at 16:54

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.