How to Optimize MySQL Settings

This page shows how MySQL settings can be optimized doing some predetermined steps. This has been proven reliable as i have used this a number of times with no issues.

There are few well-maintained shellscripts that can examine MySQL settings and make recommendations based on historic performance data (which is gathered by the scripts as well, automatically).

MySQLtuner (Perl)
MySQL Tuning Primer Script (Shell)

Both scripts produce similar recommendations and these two have compared and run to validate each other and see the results and just to make sure we don’t do anything bad.
MySQL Tuning Primer Script has been explained on this site. While for MySQL Tuner (Perl) i can give you procedure how.
1. Make a directory for your mysql scripts

[root@burnzdb]# mkdir /root/mysql-scripts/
[root@burnzdb]# cd /root/mysql-scripts/

2. Download MySQL Tuner and change mod or permission

[root@burnzdb mysql-scripts]# wget --no-check-certificate https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
chmod 700 mysqltuner.pl

3. Running the scripts – before you run the script it is very important that the MySQL server should have been running at least 48 hours continuously to get a proper recommendation and results.

[root@burnzdb mysql-scripts]# /root/mysql-scripts/mysqltuner.pl
 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.15-55-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 899M (Tables: 404)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 56
 
-------- Security Recommendations  -------------------------------------------
[!!] User '@db1.wp.powertvonline.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'root@::1' has no password set.
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 0m 29s (32M q [900.706 qps], 139K conn, TX: 194B, RX: 12B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 560.0M global + 12.4M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.8G (22% of installed RAM)
[OK] Slow queries: 0% (147K/32M)
[!!] Highest connection usage: 100%  (101/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.0G
[OK] Key buffer hit rate: 99.9% (1B cached / 2M reads)
[OK] Query cache efficiency: 85.6% (26M cached / 31M selects)
[!!] Query cache prunes per day: 4647549
[OK] Sorts requiring temporary tables: 0% (16 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 195
[!!] Temporary tables created on disk: 46% (491K on disk / 1M total)
[OK] Thread cache hit rate: 99% (514 created / 139K connections)
[!!] Table cache hit rate: 0% (128 open / 38K opened)
[OK] Open file limit used: 20% (212/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 180)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 32M)
    table_cache (> 128)

4. Analysing the Result
Although the results are generally safe, please observe caution while applying changes. It is highly recommend not to modify any parameter that doesn’t sound familiar to you; you don’t have to be an expert but at least keep reading the parameters’ explanations to start becoming familiar with them.

You can check the output of MySQL tuner which provides a nice summary and for each action item double check it against tuning primer’s longer output. Then write down all the action items that are similar on both outputs.

Read the list that you have and make sure you’re a bit familiar with what each of the variables mean. If unsure, read on MySQL’s official documentation. It’s easy to find, just google “mysql “.
Putting the suggestions in place

99% of the modifications will require a MySQL restart. It’s best to advise the client and ask for a time when the server is not so busy so you could revert the changes back in case MySQL doesn’t start anymore (that would be your worst case scenario).

Estimating how long MySQL will take is a bit tricky but depends on the size of the database(s). The bigger the databases, the longer a MySQL restart will take and it’s usually up to dozens of seconds (10 seconds to 1 minute).

Remember: Always do a backup of my.cnf before doing any changes. You might need to revert back to it.