Checking MySQL: mysqltuner (and setting up your .my.cnf)

There are many ways to monitor your MySQL server; I’m going to start posting a few small articles monitoring your MySQL server.
Today we’ll start with the least intrusive and simplest method and also setup .my.cnf to ensure your scripts can login to MySQL.
Setting this up and following the first advice from this script will take 5 minutes of your time and provide you with a bit of insight.

DISCLAIMER: I’m not the author of this tool and you should exercise sound judgement in implementing any advice from mysqltuner.

For the script to run correctly you must have MySQL root access or at least an account with sufficient privileges. The script itself can be run as a non privileged user.
Let’s create one for this article:

useradd mysqlmonitor
sudo -i -u mysqlmonitor

create the .my.cnf file in your favorite editor with the following contents

[client]
user=root
password=MYSQLPASSWORDGOESHERE

Make sure this file can only be read by this user (and root)
chmod 600 .my.cnf

You can retrieve the script very easily:

wget mysqltuner.pl

Be sure to set the permissions on the resulting file:

chmod 700 mysqltuner.pl

After inspecting the file you should be able to run it by executing: ./mysqltuner.pl and get a report much like this:

>> 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.28-29.1-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 51M (Tables: 715)
[–] Data in InnoDB tables: 39M (Tables: 336)
[–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[–] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 44

——– Security Recommendations ——————————————-
[!!] User ‘@localhost’ has no password set.

——– Performance Metrics ————————————————-
[–] Up for: 33d 11h 10m 37s (12M q [4.324 qps], 627K conn, TX: 51B, RX: 1B)
[–] Reads / Writes: 94% / 6%
[–] Total buffers: 2.1G global + 2.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.5G (5% of installed RAM)
[OK] Slow queries: 0% (0/12M)
[OK] Highest usage of available connections: 9% (46/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/6.9M
[OK] Key buffer hit rate: 100.0% (35M cached / 2K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 677K sorts)
[!!] Joins performed without indexes: 29977
[!!] Temporary tables created on disk: 43% (411K on disk / 950K total)
[OK] Thread cache hit rate: 99% (46 created / 627K connections)
[!!] Table cache hit rate: 13% (1K open / 9K opened)
[OK] Open file limit used: 2% (1K/65K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
[OK] InnoDB data size / buffer pool: 40.0M/1.0G

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_cache (> 10240)

To have this run every week is just a matter of setting up the cron properly:

crontab -e

[email protected]
@weekly $HOME/mysqltuner.pl

Soon I will post how to setup a MySQL my.cnf that will work well for use in webhosting along with a few tips to safely try these configurations and revert back if needed.

Leave a Reply