Differences

This shows you the differences between two versions of the page.

--- mysql:tunning [2009/05/25 00:35]
127.0.0.1 external edit
+++ mysql:tunning [2011/01/01 19:16] (current)
a
@@ Line 10: / Line 10: @@
   * **''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               |
++--------------------+
+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]]