Changes

The best tuning parameters are # <code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL# <code>innodb_log_file_size</code> at least enough for 60 minutes of log writes (because sequential writes to a log file are faster than disk)# <code>innodb_io_capacity</code> based on your disk IOPS<ref>Bill Karwin, Percona http://www.slideshare.net/billkarwin/mysql-55-guide-to-innodb-status</ref> <ol><li><code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL A rule of thumb would be to make it 10% larger than your database size. If it's 10% smaller than your db, you probably won't notice a problem. This is one reason to run your DB on a dedicated server: so that you can allocate as much memory as possible to the database without worrying about other OS and application memory requirements.<ref>https://www.percona.com/blog/20082011/1104/2104/howinnodb-toflushing-calculatetheory-aand-good-innodb-log-file-sizesolutions/</ref> <ref>https://www.percona.com/blog/20112007/0411/0403/innodbchoosing-flushing-theory-and-solutionsinnodb_buffer_pool_size/</ref><li><code>innodb_log_file_size</code> at least enough for 60 minutes of log writes (because sequential writes to a log file are faster than disk) <ref>https://www.percona.com/blog/20072008/11/0321/choosinghow-to-calculate-a-good-innodb-log-file-innodb_buffer_pool_sizesize/</ref>Do this at peak usage, or use a bigger time window for sampling.<source lang="bashmysql">SHOW ENGINE INNODB STATUS-- turn on the pager; setting it to 'grep sequence'pager grep sequence-- run our status report; wait 60 seconds and run it againshow engine innodb status\G select sleep(60); show engine innodb status\G-- turn the pager offnopager</source>Now you take the last number minus the first number, and convert to MB<source lang="mysql">select (43599802491 - 43599758916) / 1024 / 1024 as MB_per_minute;