MySQL Innodb Optimizations

MySQL Innodb Optimization[1]

• innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory
• innodb_log_file_size[2] - Log file should be big enough to hold at least one hour's worth of transactions. Calculate this using the following (run at peak DB usage time)

1. SHOW ENGINE STATUS\G
2. Copy down the value for "Log Sequence Number"  
3. Wait 60 seconds
4. SHOW ENGINE STATUS\G
5. Copy down the new value for "Log Sequence Number"
6. Subtract the value in Step 5 from the value in Step 2 and multiply by 6 

Eg.

Log sequence number 176285544460

Log sequence number 176286476144

mysql>  SELECT ((176286476144 - 176285544460)*60) / 1024 / 1024;
+--------------------------------------------------+
| ((176286476144 - 176285544460)*60) / 1024 / 1024 |
+--------------------------------------------------+
|                                      53.31138611 |
+--------------------------------------------------+
1 row in set (0.00 sec)

The resulting value is a good approximate  for the size of the log file

•  MySQL Tuner perl script, download here (github here)

[1] https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/
[2] https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/