10 Database Tuning Tips for Peak Workloads
Is your database well equipped to handle peak workloads? As we head into the holiday season and the start of a new year, now is a great time to ensure your database is ready for whatever comes its way. With a little bit of planning, and tuning of a few key MariaDB system variables, you’ll ensure your database never wavers, no matter what volume of traffic you throw at it.
1. InnoDB Buffer Pool Size
Making the InnoDB buffer pool size as large as possible ensures you use memory rather than disks for most read operations (because the buffer pool is where data and indexes are cached).
2. InnoDB Log File Size
The redo logs make sure writes are fast and durable, and the InnoDB redo space size is important for write-intensive workloads. The logs’ size is determined by innodb_log-file-size. For best results, generally you’ll want to set a combined total size to be at least 1/4 (or even 1/2) of the InnoDB buffer pool size, or equal to one hour’s worth of log entries during peak load.
There are tradeoffs to consider here: Larger log files can lead to slower recovery in the event of a server crash, but they also reduce the number of checkpoints needed and reduce disk I/O. Evaluate the size of one hour’s worth of binary logs under operational load, then decide whether to increase the size of the InnoDB log files.
Getting the innodb log file size right is important to achieve good system performance. MariaDB’s InnoDB storage engine uses a fixed size (circular) redo log space. The size is controlled by innodb_log_file_size and innodb_log_files_in_group (default 2). Multiply those values to get the redo log space that’s available for use.
(Note: It shouldn’t matter whether you use innodb_log_file_size or innodb_log_files_in_group to control the redo space size; most people work with innodb_log_file_size and leave innodb_log_files_in_group alone.)
3. InnoDB Log Buffer Size
This one is much simpler. We recommend setting InnoDB log buffer size to 64M because a large InnoDB log buffer size means less disk I/O for larger transactions.
4. InnoDB Log Flush Interval
The innodb_flush_log_at_trx_commit variable controls when flushing of the log buffer to disk occurs. The safest option – and the one we recommend, despite its being the least performant – is innodb_flush_log_at_trx_commit = 1 (default), which flushes the log buffer to disk at each transaction commit.
However, if the disk cache has a battery backup, you might consider setting innodb_flush_log_at_trx_commit = 2. This writes the log buffer out to file on each commit but flushes to disk every second. A crash of MySQL should not lose data, but a server crash or power outage could lose a second or more. The battery backup mitigates this risk, giving this setting the best balance of performance and safety when using a battery-backed cache.
5. InnoDB IO Capacity
To determine the appropriate setting for you, we recommend benchmarking your storage. By default innodb_io_capacity is set to 1000, but it should be set to approximately the maximum number of IOPS the underlying storage can handle.
6. Thread Cache Size
The thread_cache_size is set to 200 in the current default configuration. If your Threads_created value rises at more than a few threads per minute, increase the thread_cache_size value.
7. Temporary Tables, tmp_table_size, and max_heap_table_size
A high number of temporary tables on disk has two common causes:
- Queries not using joins appropriately and creating large temporary tables
- The memory storage engine using fixed-length columns and assuming the worst-case scenario
MySQL limits the size of temporary tables in memory by using the lower of max_heap_table_size and tmp_table_size. These are per-client variables. Having a large value here can help reduce the number of temporary tables created on disk, but it raises the risk of reaching the server’s memory capacity since max_heap_table_size and tmp_table_size are per-client variables. Both are currently set to 64M by default; we recommend starting with a value between 32M to 64M, and tuning as needed from there.
8. Max Connections
Receiving frequent “Too many connections” errors means max_connections is too low. Using a connection pool at the application level or a thread pool at the MariaDB level can help with the number of connections. Often you need far more than the default 151 connections because the application does not properly close connections to the database. The main drawback of high max_connections values (perhaps over 1,000) is that the server will become unresponsive if it has to run that many active transactions.
9. Sync Binlog
By default, the OS flushes the binlog to disk. If there’s a server crash, transactions may be lost from the binary log, leading to replication being out sync. The safest option here (at the expense of a bit of speed) is to set sync_binlog = 1, causing the binlog file to be flushed on every commit.
10. Watch Our On-Demand Webinar: Readying Databases for Anything
When you watch the on-demand webinar on Readying Databases for Anything, you’ll learn which options are best suited to making sure your databases are ready to scale, perform their best, handle failures and keep your data protected during both anticipated growth and unexpected spikes.