MySQL/Optimization: Difference between revisions

Line 28:

WHERE VARIABLE_NAME LIKE 'QCACHE%'

) AS stats;

</source>

To make your changes permanent, set it in your global option configuration file (/etc/mysql/my.cnf)

query_cache_size = 80M

</source>

Line 33:

Line 37:

Look for long-running transactions; and deadlocks

The best tuning parameters are

The best tuning parameters are <ref>Bill Karwin, Percona http://www.slideshare.net/billkarwin/mysql-55-guide-to-innodb-status</ref>

~~# <code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL~~

<ol>

~~# <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)~~

<li><code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL

~~# <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> <ref>https://www.percona.com/blog/~~2008~~/11/21/~~how~~-to-~~calculate~~-a-~~good-innodb-log-file-size~~/</ref> <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>

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>

<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.

~~SHOW ENGINE INNODB STATUS~~\G;

-- turn on the pager; setting it to 'grep sequence'

pager grep sequence

-- run our status report; wait 60 seconds and run it again

show engine innodb status\G select sleep(60); show engine innodb status\G

-- turn the pager off

nopager

</source>

Now you take the last number minus the first number, and convert to MB

select (43599802491 - 43599758916) / 1024 / 1024 as MB_per_minute;

</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';</source>

The default is 5242880 which is 5MB

<li><code>innodb_io_capacity</code> based on your disk IOPS

</ol>

@@ Line 28: / Line 28: @@
 WHERE VARIABLE_NAME LIKE 'QCACHE%'
 ) AS stats;
+</source>
+To make your changes permanent, set it in your global option configuration file (/etc/mysql/my.cnf)
+<source lang="ini">
+query_cache_size = 80M
 </source>
@@ Line 33: / Line 37: @@
 Look for long-running transactions; and deadlocks
-The best tuning parameters are
+The best tuning parameters are <ref>Bill Karwin, Percona http://www.slideshare.net/billkarwin/mysql-55-guide-to-innodb-status</ref>
-# <code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL
+<ol>
-# <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)
+<li><code>innodb_buffer_pool_size</code> as much as you can spare after OS and MySQL
-# <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> <ref>https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/</ref> <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>
+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>
-<source lang="bash">
+<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.
-SHOW ENGINE INNODB STATUS\G;
+<source lang="mysql">
+-- turn on the pager; setting it to 'grep sequence'
+pager grep sequence
+-- run our status report; wait 60 seconds and run it again
+show engine innodb status\G select sleep(60); show engine innodb status\G
+-- turn the pager off
+nopager
+</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;
 </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';</source>
+The default is 5242880 which is 5MB
+<li><code>innodb_io_capacity</code> based on your disk IOPS
+ </ol>
 {{References}}