Mysql tuning

MySql - tuning

mysqltuner.pl
https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

tuning primer sh
http://www.day32.com/MySQL/tuning-primer.sh

calculator
http://www.mysqlcalculator.com/


----- Slow Query Log -----
>= 5.1.6:

1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:
- Log details for queries expected to retrieve all rows instead of using an index:
   set global log_queries_not_using_indexes = 'ON'
- Set the path to the slow query log:
  set global slow_query_log_file ='/var/log/mysql/slow-query.log';
- Set the amount of time a query needs to run before being logged:
   set global long_query_time = '20';   (default is 10 seconds)
3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';

< 5.1.6: my.cnf

[mysqld]
log-slow-queries=/var/log/mysql/slow-query.log

Additional options:

  `long_query_time=20   (default is 10 seconds)`

Log details for queries expected to retrieve all rows instead of using an index:
 `log-queries-not-using-indexes`

4. service mysqld restart
5. Confirm the change is active by entering the MySQL shell and running the following:

show variables like '%slow%';

refer:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html