If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If the Unix
top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound.
InnoDBbuffer pool, it can be processed over and over by queries without requiring any disk I/O. Specify the size of the buffer pool with the option. This memory area is important enough that busy databases often specify a size approximately 80% of the amount of physical memory.
innodb_log_buffer_size- The size in bytes of the buffer that
InnoDBuses to write to the on disk. The default value is 8MB. A large enables large to run without a need to write the log to disk before the transactions . Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.
innodb_log_file_size- The size in bytes of each in a . The combined size of log files (
innodb_log_file_size* ) cannot exceed a maximum value that is slightly less than 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/
N-th of the size of the buffer pool, where
Nis the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration.
innodb_flush_log_at_trx_commit -Controls the balance between strict compliance for operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to one second worth of in a crash.
The default value of 1 is required for full ACID compliance. With this value, the is written out to the at each transaction commit and the to disk operation is performed on the log file.
With a value of 0, any process crash can erase the last second of transactions. The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.
With a value of 2, an operating system crash or a power outage can erase the last second of commit records. The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
innodb_buffer_pool_instances -The number of regions that the buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.This option takes effect only when you set the to a size of 1GB or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of and so that each buffer pool instance is at least 1GB.
Here is an sample for 32 G RAM of cpanel server.