====== MySQL tunning ======

See also:
  * [[http://rudd-o.com/archives/2006/03/02/tuning-a-mysql-server-in-5-minutes/|Tuning a MySQL server in 5 minutes]]

===== 3 Minute MySQL (tuning) =====

  * **''max_connections''** - set to the same (or a little bigger than) as MaxChildren from apache if you’re only using one mysql connection per request (this is most common)
  * **''key_buffer_size''** - Set to roughly half your available ram.
  * **''thread_cache_size''** - set it until show status like ‘threads_created’ quits increasing.
  * **''table_cache_size''** - set until show status like ‘opened_tables’ quits increasing. If you have thousands of tables this might not be possible

===== How to enable performance_schema in MySQL 5.5 =====
One of the cool feature of MySQL 5.5 performance schema is not enabled by default to enable it in the my.cnf for linux add variable under mysqld

   [mysqld]
   performance_schema

to check if the performance_schema 
<code>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
</code>

==== Tune MySQL parameters ====


  * download mysqltuner.pl or tuning-primer.sh and run them. While they won't give you exactly same recommendations it will give you nudge into right direction.
  * set **''innodb_buffer_pool_size''** to some reasonable number.
  * use **Maatkit** or more specifically ''mk-query-digest''. You can feed it with ''/var/log/mysql/mysql-slow.log'' or run in on live system:

   $ wget maatkit.org/get/mk-query-digest
   $ perl mk-query-digest --processlist localhost --interval 0.01

==== Tune Linux ====


    * turn off readhead

      hdparm -a 0 /dev/sdb

    * use noop or deadline scheduler for block device on which is logical volume with MySQL

      echo deadline > /sys/block/sdb/queue/scheduler

    * reduce swappiness

      echo 0 > /proc/sys/vm/swappiness

    * disable write barriers (only with battery backed controller!)

      mount /mnt/koha -o remount,nobarrier


**Optimization links:**
   * [[http://github.com/rackerhacker/MySQLTuner-perl/raw/master/mysqltuner.pl|mysqltuner.pl]]
   * [[http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh|tuning-primer.sh]]
   * [[http://www.maatkit.org/|Maatkit Power tools for open-source databases]]
   * [[http://www.maatkit.org/doc/mk-query-digest.html|mk-query-digest - Parses logs and more. Analyze,  transform, filter, review and report on queries]]
   * [[http://www.youtube.com/watch?v=tT8olI-Kcxw|mk-query-digest on Youtube]]
   * [[http://www.maatkit.org/wp-content/uploads/2010/03/query-analysis-with-mk-query-digest.pdf|Presentation about mk-query-digest from PgEast 2010]]
   * [[http://yoshinorimatsunobu.blogspot.com/2009/08/accessing-mysql-tables-from-udf-storage.html|Accessing MySQL tables from UDF + storage engine API]]
   * [[http://html5tv.rot13.org/mysql2010-linux-performance.html|Linux Performance Tuning and Stabilization Tips]]
   * [[http://en.oreilly.com/mysql2010/public/schedule/detail/13252|Linux Performance Tuning and Stabilization Tips]]