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.

Logwatch: How to fix Dovecot Unmatched Entries (CentOS / RHEL 6)

UPDATE: Thanks jagro.nl and Julian Stokes I was alerted to the fact that the current HEAD version of the script no longer works. You can use my old version instead.

You may have noticed the extra useless notices by Dovecot in your Logwatch (you do check your logwatch every day right?)
These kinds of messages can be found under the Dovecot logs:

**Unmatched Entries**
dovecot: imap(useraccount): Connection closed bytes=16/338: 1 Time(s)
dovecot: imap(useraccount): Connection closed bytes=17/340: 7 Time(s)
dovecot: imap(useraccount): Connection closed bytes=18/342: 3 Time(s)

And depending on your activity you could be getting this kind of message a few hundred times per day on more busy setups.
A quick Google check reveals the following bug reports for Redhat: Bug 666376 and Bug 669161

This was fixed in Rawhide, but apparently hasn’t made it to Redhat proper yet.

Let’s fix this in the cleanest possible way.
Logwatch stores the script originals in /usr/share/logwatch/scripts/services/* and these are bundled in the RPM that is installed by yum.
Changing these scripts wouldn’t help much as these are overwritten by updates.

Logwatch also maintains a folder in /etc under /etc/logwatch/scripts/services/ where you can place your own scripts (they will replace the scripts in /usr/share; according to the manpage)

Just download the latest from the repository and name it dovecot
http://logwatch.svn.sourceforge.net/viewvc/logwatch/scripts/services/

You should use this version for now and save it  as dovecot http://barry.coffeesprout.com/dovecot

With the dovecot script downloaded to /etc/logwatch/scripts/services/dovecot your report should now look a lot cleaner.

Run logwatch and you should see something like:

Dovecot IMAP and POP3 Successful Logins: 316
Dovecot disconnects: 286

I hope this has been somewhat helpful to you