5

I want to speed up testing web applications by configuring MySQL.

I create web applications with PHP and MySQL. When developing, tests are run every time files are saved. Therefore, faster execution of tests saves development time and makes me feel comfortable.

Though there are sereval ways to speed up tests, its database must be one of them. As a testing environment, all I need is quicker response. I don't need durability or efficiency of handling multiple connections.

In that case, what is the best configuration?

Akihiro HARAI
  • 574
  • 1
  • 8
  • 17
  • There ist no real answer to that because it depends on your available hardware .. assigning lots of real memory for indexes usually speeds things up. The fastest being the whole mysql setup running in memory. – snitch182 May 13 '14 at 12:38
  • Profile your tests to confirm that mysql takes a significant percentage of the time, and look at top or whatever to see what mysql is doing when your tests are running (on my laptop, it's not waiting for the disk!), and add that data to the question. – Dave Schweisguth May 13 '14 at 17:20

1 Answers1

7

It depends on test database setup.

If the database is running all the time, you'll have to tweak settings like buffer sizes and io threads. Like this answer suggests, there are tools that can test and produce recommandations for your server setup.

In unit tests where data integrity isn't that important, you can use in memory tables (ENGINE=MEMORY) and disable INNODB's double write (innodb_doublewrite=OFF). This will reduce data integrity but increase performance.

I'm using a library for setting up a the database every time the tests are run. By using these settings, the database are running in about a second:

  • innodb_fast_shutdown=2 (fast shut down)
  • innodb_log_file_size=1048576 (smallest possible log size)
  • innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend (smallest possible data file sizes for quicker startup)
Community
  • 1
  • 1
StumpDK
  • 441
  • 5
  • 6
  • > innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend this didn't work on my machine (5.6) – confiq Mar 16 '17 at 12:39
  • 1
    While these are good config settings, one of the big improvements we found was disabling innodb_file_per_table (which defaults to on past MySQL 5.5 I believe). When creating a lot of tables, it can making creating a table up to 4 times faster, which added up to quite a lot of time saved for our 200 plus tables. As with anything, test it with your specific testing needs. – Enthusiastic Programmer Jul 27 '19 at 13:07
  • @EnthusiasticProgrammer Thank you for this, I can also confirm that disabling `innodb_file_per_table` massively speeds up CREATE TABLE statements, in my case about 3x. – jlh Sep 16 '21 at 13:28