Bureaucrats, confirmed, Administrators
4,558
edits
Changes
adds shorthand method for Selecting variables
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/2011/04/04/innodb-flushing-theory-and-solutions/</ref><ref>https://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/</ref>
See [https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html the manual] for resizing the buffer.
<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/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/</ref> Do this at peak usage, or use a bigger time window for sampling.
<source lang="mysql">
</source>
Multiply that number by 60 to get an hours worth of log (and divide by 2 because there are two log files) Compare that to the current configuration
<source lang="mysql">SHOW variables like 'innodb_log_file_size';-- can be written as a SELECT statement using shorthand like this, and the value display is not limitedSELECT @@innodb_log_file_size;</source>
The default is 5242880 which is 5MB