Mar 30

How to: Install and use MySQLTuner to Optimize your MySQL configuration / performance

How to: Install and use MySQLTuner to Optimize your MySQL configuration / performance

I am writing this post mostly because there is in my opinion a much better way to install MySQLTuner on your machine that the usual apt-get install MySQLTuner. As many of you would probably already know MySQLTuner is a well known and widely use tool. It gives you a very nice summary of how your configuration is performing and points you were you can make improvements to get even better performance out of your database server. I generally recommend people to install Percona (which is a drop-in replacement for MySQL). Percona is based on MySQL code but its cooler (they add more performance features you might only find in the commercial MySQL version and they do a better job of keeping up to date than Oracle does). Because of that I even wrote on the article: How to: Install MySQL in an Ubuntu Server instructions on how to install Percona instead of MySQL at the end as a suggested alternative.

Regardless, as I mentioned earlier the main reason for this article is to focus on an alternative to simply apt-get install MySQLTuner. And why is that? Well, let’s see what running that command has to say on one of my server box:

# apt-get install MySQLTuner
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
libperconaserverclient18.1 percona-server-common-5.6
Use ‘apt-get autoremove’ to remove them.
The following extra packages will be installed:
libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
Suggested packages:
libipc-sharedcache-perl tinyca mailx
The following packages will be REMOVED:
percona-server-client-5.6 percona-server-server-5.6
The following NEW packages will be installed:
libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 mysqltuner
0 upgraded, 9 newly installed, 2 to remove and 0 not upgraded.
Need to get 24.8 MB/24.8 MB of archives.
After this operation, 20.4 MB disk space will be freed.
Do you want to continue [Y/n]? N

As highlighted on bold, the installer will try to remove Percona server, will install instead mysql and a bunch of other libraries. Most of the time this is fine but many times you’ll find you only want the tool and you’re happy with Percona as I am. So what to do?

Fortunately MySQLTuner is a tool written on perl and only requires client libraries to connect to mysql which is not a big deal (percona client is available). Simply go to: https://github.com/major/MySQLTuner-perl and download the mysqltuner.pl file to get the latest and greatest (do a wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl). Once you have downloaded the script, all you have to do is go to the location where you saved it and execute perl mysqltuner.pl and voila!

Now, if you are looking for some tips on how to use this tool and recommended changes here are a few highlights:

I. If you are using only PHP (think WordPress) then definitively change the wait_timeout.

By default it is 8 hours but if for some reason the application doesn’t close the connection it might stay there hanging for up to 8 hours (so you start getting a lot of sleeping open connections which consume resources.

wait_timeout = 30       #default is 28800

You can leave the interactive timeout alone. This is generally a higher value than wait_timeout and as you might guess it requires interaction for it to remain on wait state.

II. Change the max number of connections.

On my installation it was 500! Each connection uses a bit of resources as well even if not in use. MySQLTuner can help you see over time what is the maximum number of connections you end up using so you can adjust accordingly. For example, if you are using PHP-FPM you will most likely not use more connections than the max number of child processes you have. Consider that and examine the output of MySQLTuner to decide what’s the best number. Leave your maximum number of connections (after having MySQL running for at least 24 hours, preferably 7 days so you get metrics for an entire week) at about 80% (so increase the maximum usage by 25% when setting up this variable)

max_connections = 50  #default is 500

III. Run MySQLTuner

Let’s examine some output:

# perl mysqltuner.pl

>> MySQLTuner 1.3.0 – Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering


——– Security Recommendations ——————————————-

[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 4h 51m 32s (354K q [20.247 qps], 3K conn, TX: 2B, RX: 45M)
[–] Reads / Writes: 90% / 10%
[–] Total buffers: 420.0M global + 1.1M per thread (26 max threads)
[OK] Maximum possible memory usage: 447.6M (14% of installed RAM)
[OK] Slow queries: 0% (56/354K)
[OK] Highest usage of available connections: 42% (11/26)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Key buffer hit rate: 99.0% (100 cached / 1 reads)
[OK] Query cache efficiency: 78.9% (260K cached / 330K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Temporary tables created on disk: 36% (3K on disk / 8K total)
[OK] Thread cache hit rate: 99% (11 created / 3K connections)
[OK] Table cache hit rate: 28% (363 open / 1K opened)
[OK] Open file limit used: 0% (48/65K)
[OK] Table locks acquired immediately: 100% (85K immediate / 85K locks)
[OK] InnoDB buffer pool / data size: 128.0M/49.3M
[OK] InnoDB log waits: 0
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
tmp_table_size (> 30M)
max_heap_table_size (> 30M)

As I mentioned earlier, you want to run this against your server at least 24 hours after you have started MySQL so the recommendations are most accurate (I would recommend running it 7 days, 30 days, etc. again as you might get more insight the more use the server gets). But please don’t feel you won’t get any important information if you run it say an hour or immediately after starting. Immediately after starting you can see if you screwed something up with memory. While after an hour might have valuable insight if you are just starting to optimize your setup. For example, here I already got to use all the size for my temporary tables so this error most likely won’t be going away no matter how long I run MySQL. With time you’ll learn which recommendations you should wait to listen to and which you can implement right away.

Making good use of that recommendation also note the previous one “When making adjustments, make tmp_table_size and max_heap_table_size equal”.

All changes to your /etc/mysql/my.cnf usually require a service restart. I have tried the reload parameter but mysqltuner does not seem to note the change.

My final piece of advice is to focus on performance metrics and the recommendations section:

  1. If you are sharing your server box with other applications try to have the Maximum possible memory usage within your estimates of how much RAM you are willing to commit to MySQL
  2. As I mentioned make sure your Maximum Number of Connections is at about 80% of your available ones. Run MySQL for a while and add 25% more connections to the maximum you get.
  3. Configure Slow Queries. Indicate how many seconds constitute a slow query and see if you get a few or too many.
  4. Make sure all the buffers have enough RAM.
  5. Configure your swappiness to a low value. Swapping RAM does take a bit of performance out of a MySQL server. Remember you are using most of these buffers / cache to avoid having to go to the disk to get information. For more information on configuring how aggressively to swap look at this post: How to: Configure Swappiness in Ubuntu
  6. Keep calm and continue improving your configuration ;). You’ll find right out of the box no server is the same. With time and looking at the metrics you’ll see if you primarily write or read, your activity and memory needs and will size the server better and adjust parameters to the load you are receiving. Percona has a tool to help you come up with some starting values at: https://tools.percona.com/wizard. Keep in mind you should understand them and apply them perhaps individually instead of just copy pasting the entire thing. If your server has been used before then some of the new settings might make it crash as it created tables with different settings. Also, using some parameters might break applications like WordPress.

Enhanced by Zemanta

Leave a Reply

%d bloggers like this: