4

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/

  • 1
    I can not tell - it common and often practice. You really do not need put all MySQL to tmpfs, it ok just point mysql temp folder into it. Most of bottlenecks from sort operations (ORDER BY, GROUP BY) which actively use temporary tables. Also, tempts - could use swap, for my personal view - RAM disk would be more preferred in this case. – a_vlad Mar 26 '17 at 23:25
  • AFTER unit test completed, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Aug 29 '18 at 0:12
  • Hi @WilsonHauck, unfortunately that's too much for and this project is behind us so I'll not be able to post all this statistics which indeed can help. We did based our config on this: jotschi.de/2014/02/03/high-performance-mysql-testdatabase – confiq Aug 29 '18 at 10:54
  • @confiq If you could post ONLY - SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; immediately after your TESTING, I will be able to help you. – Wilson Hauck Aug 29 '18 at 13:16
  • @confiq If you would like to learn how to make it faster, please post, just the SGS and SGV after the 'test cycle' and I will prepare suggestions that will have significant performance improvements. – Wilson Hauck Sep 6 '18 at 20:59
2

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 set thread_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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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