Automatic database tuning tips: Handling restarts

Business continuity is a top priority, and many organizations cannot operate without their production databases running 24/7. This requirement makes automatic database configuration tuning tricky because some knobs require you to restart the database before they take effect. We refer to these as static knobs. Contrast this with dynamic knobs that do not need you to restart the database.

Here are four key lessons we’ve learned building and implementing the OtterTune. Whether you use OtterTune, or you tune by hand, these tips are helpful when it comes to the dreaded database restart.

Tune dynamic knobs first, then static knobs second

To get the most benefit from database tuning, you will need to target multiple knobs, some dynamic and some static. To avoid too many restarts, you should start with tuning the dynamic knobs first. Then if you are still unsatisfied with your database’s performance, expand the tuning scope to include static knobs.

Tune within a specific time window

If your databases are running in production all the time, you will want to tune them within a specific time window each day. The ideal window is one where the load is not too much that configuration changes could cause degradations.

It may only be possible to restart once a day, in which case, you can still get on with tuning the database, but it may take longer to reach a near-optimal configuration. OtterTune allows you to specify custom restart time windows.

Remember that time-to-restart varies

It is crucial to keep in mind that with some databases, the time it takes for the system to come back online after restarting is not deterministic. For example, if you change MySQL’s INNODB_LOG_FILE_SIZE knob, the restart time varies depending on whether the new value is greater or less than the previous value.

If you reduce the log file size, MySQL may have to compact the log file upon restart to get it under the new threshold. That process could take quite a bit of time, depending on the disk speed and other factors. Because of this, it makes sense in some instances to restrict yourself to a once-daily database restart.

Create a control database

If your current database cannot tolerate restarts and you have the tools available, you can copy the data onto a server with similar hardware and replay a query trace. This way, you can tune static knobs offline using a control database. The list below includes some of the open source tools you can use to accomplish this.

• Copy database

  • PostgreSQL = pg_dump and pg_restore

  • MySQL = mysqldump

• Capture query trace

  • PostgreSQL = PostgreSQL Log (see here for configuration)

  • MySQL = MySQL Slow Query Log (see here for configuration)

• Replay query trace

  • PostgreSQL = pgreplay

  • MySQL = Percona Playback

A Final Thought

Whether you’re tuning production databases or have greater flexibility and more time to test out your tuning settings, database tuning frequently requires restarts. The key is to choose knobs wisely, be willing to restart only within set windows, and create an environment that allows you to measure the success of your efforts.