1392546 - Speed up MySQL I/O in Vagrant/Travis

Status: RESOLVED FIXED

(Tree Management :: Treeherder, enhancement, P2)

Description

[Ed Morley [:emorley]]

I happened to see a GitHub comment [1] that suggested using a ramdisk for MySQL storage on Travis to work around slow I/O.

It turns out doing so halves the time taken for our tests to run.

Whilst experimenting with that, I also tried a bunch of I/O related MySQL prefs both on Travis and locally (given locally we won't want to use a ramdisk since we'll want the DB to persist) to see what else works.

The biggest win locally was setting innodb_flush_log_at_trx_commit=0, which reduced test runtime by 20%.

[1] https://github.com/travis-ci/travis-ci/issues/3049#issuecomment-215084577

Comment 1

[GitHub Autolander Bot]

Attachment: [treeherder] mozilla:faster-dev-mysql > mozilla:master

Comment 2

[Cameron Dawson [:camd]]

Comment on attachment 8899769 [details] [review]
[treeherder] mozilla:faster-dev-mysql > mozilla:master

Sweet!!  A few more of these improvements and you'll reduce the Test run time to complete before it's begun.  ;)

Comment 3

[Treeherder GitHub Bugbot]

Commits pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/7c5b26dac184b705ca5a9b8f70f58914916d3b54
Bug 1392546 - Travis/Vagrant: Remove bind-address from mysql.cnf

Since as of bug 1362443 connections can still be made to the DB from
outside Vagrant even if `bind-address` is not overridden.

https://github.com/mozilla/treeherder/commit/049f29d845cc31078b8ed524baf261602a13add3
Bug 1392546 - Travis/Vagrant: Disable MySQL slow query log

Since it's mostly pointless locally, given that the DB is rarely
large enough to cause a slow query and that it's unlikely that devs
check the logs anyway.

https://github.com/mozilla/treeherder/commit/14e43640fe0d544e028ab5eee49244d28e9993c6
Bug 1392546 - Travis/Vagrant: Set innodb_flush_log_at_trx_commit=0

By default MySQL#s InnoDB flushes to disk after every transaction to
reduce the chance of data loss in the case of a crash. During testing
and development this is not necessary and only serves to limit I/O
throughput. By switching to mode `0` (flushing every second), test
runtimes are reduced by 20-30%.

See:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

https://github.com/mozilla/treeherder/commit/81c19cf2134767e158548e57c93d7de8ef2eb813
Bug 1392546 - Travis: Store /var/lib/mysql on a ramdisk

This reduces Python test runtime by another 30% on top of the previous
changes. Inspired by:
https://github.com/travis-ci/travis-ci/issues/3049#issuecomment-215084577