We have a MySQL instance for unit tests. Our unit tests work very heavily on MySQL. Therefore, we put /var/lib/mysql
dir in tmpfs
file system because we don't need persistent data in unit tests.
Our performance peaked when we did this; however, I would like to consult with you what's the best configuration for MySQL in this case.
It's very common to put MySQL in tmpfs
(memory) and run non-important stuff like unit tests. The question is: What's best configuration for InnoDB to run in tmpfs
?
These are the configs we have in my.cnf
:
max_connections=350
innodb_fast_shutdown=2
innodb_log_file_size=1048576
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
All our databases are in InnoDB. We don't mind losing some of the data.
P.S. You might ask at this point, why not MEMORY
engine? That's because MEMORY
has table-level locking only, and part of our unit tests are based on this.
EDIT:
We've finished this project, we based our configuration from this blogpost:
http://jotschi.de/2014/02/03/high-performance-mysql-testdatabase/
Best Answer
the
innodb_log_file_size
looks rather small for what I assume is bulk loaded unit test data with lots of writes.A decent
innodb_buffer_pool_size
needs to be set that accounts for the active test size.Because crash safety isn't a concern, like
innodb_doublewrite=0
innodb_flush_log_at_trx_commit=0
, will speed up write transactions.max_connections
implies you are doing multithreaded clients and if those are connecting/disconnecting maybe setthread_cache_size
to the maximum number typically used.A
SHOW GLOBAL STATUS
after a test run might give a hint as to what other aspects could be tuned.