--- title: "10 Database Tuning Tips for Peak Workloads" publish_date: 2018-11-14 updated_date: 2024-03-19 author: "MariaDB" channel: - name: "Product" url: "/ja/resources/blog/channel/product.md" tags: - name: "Binlog" url: "/resources/blog/tag/binlog.md" - name: "Black Friday" url: "/resources/blog/tag/black-friday.md" - name: "High Availability" url: "/resources/blog/tag/high-availability.md" - name: "How to" url: "/resources/blog/tag/how-to.md" - name: "InnoDB" url: "/resources/blog/tag/innodb.md" - name: "Scaling" url: "/resources/blog/tag/scaling.md" --- # 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](https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_buffer_pool_size "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](https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_log_file_size "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](https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#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](https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#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](https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_flush_log_at_trx_commit "innodb_flush_log_at_trx_commit") 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](https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_io_capacity "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](https://mariadb.com/kb/en/mariadb/server-system-variables/#thread_cache_size "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](https://mariadb.com/kb/en/library/server-system-variables/#max_heap_table_size) and [tmp\_table\_size](https://mariadb.com/kb/en/mariadb/server-system-variables/#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](https://mariadb.com/kb/en/mariadb/server-system-variables/#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](https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#sync_binlog), 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](https://go.mariadb.com/GLBL-WBN_2018-11-08HolidayReadiness-Campaign_Registration-LPNew.html), 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.