In this article, we’ll take a closer look at MySQL InnoDB tuning. With our MySQL Support Service, Bobcares can address any InnoDB tuning concerns.

MySQL InnoDB Tuning

The MySQL InnoDB storage engine performs many of its optimizations automatically, hence many performance-tuning tasks involve monitoring to ensure that the DB is performing well, and changing config options when performance drops.

Tips For Tuning MySQL InnoDB

• Adjust InnoDB buffer pool size: If the CPU usage percentage with our workload is less than 70%, the workload is probably disk-bound. This happens when too many transactions commit, or the buffer pool is too small. Readjusting the buffer pool size is the best option. The ideal value of the buffer pool is 70%-80% of available memory.
• Make big log files: Make the log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files will cause many unnecessary disks writes.
• Dump/Restore buffer pool: This feature speeds up restarts by saving and restoring the contents of the buffer pool.
• Keep log buffer big: Innodb writes modified data records into its log buffer and then stores them in the memory, saving disc I/O for big transactions by not writing the log of modifications to the disc before the transaction commit. 4 MB to 8 MB is an ideal option.
• Configure thread concurrency: With changes to the InnoDB engine, it is better to allow the engine to handle concurrency by leaving it at the default value of 0. If concurrency problems emerge, we can adjust this value. A recommended value is two times the number of CPUs plus the number of drives. Because it is a dynamic variable, we can change it without restarting the MySQL server.
• Set innodb_flush_method to O_DIRECT: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it is set to O_DIRECT avoids double buffering with the buffer pool and the filesystem cache. Given that you have a hardware RAID controller and battery-backed write cache.
• Move purge operations into a background thread: To effectively measure the results of this setting, tune the other I/O-related and thread-related configuration settings first.
• Configure the innodb_buffer_pool_instances: Configuring the size and number of innodb_buffer_pool_instances is useful in highly concurrent workloads as it may reduce contention of the global mutexes.
• Use innodb_adaptive_hash_index option: Turning the adaptive hash indexing feature on and off using the innodb_adaptive_hash_index option. We can change this setting during periods of unusual activity, then restore it to its original setting.
• Control the amount of prefetching that InnoDB does with its read-ahead operations: When the system has unused I/O capacity, more read-ahead can improve the performance of queries. Too much read-ahead can cause periodic drops in performance on a heavily loaded system.

App Tuning

There will be some changes we like to make to our app. Let’s see more about them.

• Firstly, when doing updates, make sure to use transactions for consistency and improved efficiency.
• Secondly, if the app contains any writes, be ready to deal with any deadlocks that may occur.
• Finally, we should go over the table layout and see how we can use the properties like clustering by primary key, having primary keys in all indexes, quick lookups by primary keys, and huge unpacked indexes.

[Need help with another query? We are just a click away.]

Conclusion

To conclude, our skilled Support team provides some tips for MySQL Innodb tuning.