---
title: "MySQL Tuner quick run through"
date: "2013-03-04"
modified: "2013-03-14"
path: "/90-mysql-tuner-quick-run-through.html"
section: "/ramblings"
aliases:
  - "/90"
  - "/90-mysql-tuner-quick-run-through"
  - "/90.html"
  - "/ramblings/90-mysql-tuner-quick-run-through.html"
---

MySQL tuner is the simplest way to take a default MySQL install and tweaks its performance. I would highly recommend running a load test on the web application as this will help populate all of the caches and also help highlight any problems with indexes or similar.

```
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
chmod 755 mysqltuner.pl
```

Run a series of load tests on the application and then run the command:

`perl mysqltuner.pl`

Now edit the MySQL config with the settings suggested by the tuner script:

```
nano /etc/mysql/my.cnf
/etc/init.d/mysql restart
```

This final few steps you will need to iterate a couple of times to get the best results but you should see variables that you need adjusting at the base like:

```
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 1G)
```

All you need to do is edit the `/etc/mysql/my.cnf` file adding the changes under the `[mysqld]` block making sure though that you do not exceed resources available with your hardware:

```
query_cache_size        = 32M
join_buffer_size        = 2M
table_cache             = 600
innodb_buffer_pool_size = 1G
```

You can either run a [siege](/82-magento-siege-testing.html) load test on a "non-live" site to get the database populated, or leave the live site running for a few hours/days and then repeat the process. After a couple of iterations your database should be optimised as best it can.