Monitoring your MySQL configuration

by Sep 26, 2012

Monitoring your MySQL configuration
While monitoring MySQL servers, managing their configuration becomes important. There could be a need of comparing configuration of one or more MySQL or a server’s running configuration with that of its own My.cnf. Let me explain this a little more.

If you are in a situation where a server ‘A’ is performing better than a server ‘B’ while both have the same resources and environment, then the first thing you might want to do is compare their configuration. Then, make the configuration changes in server ‘B’ looking into ‘A’. There by fine tuning ‘B’ to level up to ‘A’. Comparing MySQL configurations is the key here.

When you change a MySQL Global variable when the server is running, for reasons like – fine tuning your application or upgrading your MySQL or any other, the changes do not persist. In other words, these changes are lost forever when the server restarts and the old values are reverted back. So comparing configuration becomes essential here.

You can do this comparison in many ways-

Manually

Simply use a file comparison tool to find the differences of the 2 My.cnf files!

Manual my cnf comparision

Percona Toolkit – Free command line utility

Percona Toolkit comes with a neat tool pt-config-diff, which can compare the differences between two MySQL configuration files as well as compare the running configuration of a server with its own(or any other) My.cnf file.

Compare the running configuration of a server with its own My.cnf file-

pt-config-diff path_to_My.cnf -host

Note: The obvious limitation here is that it can not show any difference when a global variable is not mentioned in the my.cnf file and that variable is changed while the server is running. It just compares the output of SHOW GLOBAL VARIABLES with the contents of my.cnf.

This tool is excellent for comparing 2 or more my.cnf files.

pt-config-diff path_to_first_my.cnf path_to_the_second_my.cnf

You can also compare the running configurations of 2 or more MySQL servers.

pt-config-diff host1 host2

Percona Toolkit Free command line utility

Refer documentation for more details on pt-config-diff

MONyog – GUI based Monitoring tool

Using MONyog’s Server Config, you can compare configuration of 2 or more servers side by side. You can also view only the differences between 2 or more servers. MONyog shows the differences by analyzing the output of SHOW GLOBAL VARIABLES;

MONyog server Config

In addition to this, MONyog can also track your configuration changes as and when you make it, even if its made while the server is running. Changes are recorded against timestamps. So you can go ahead and compare changes between 2 different timestamps for a certain server. You can always dig up historic data to analyze with the current data. In other words, you have version control for your global variables!

MONyog can also send notification emails or SNMP traps on any change in server configuration, there by alerting the DBAs.

Note that some configuration options are not always exposed as variables. This is particularly true with MySQL servers before 5.5. Examples of important configuration options that are not exposed as variables in 5.0 and 5.1 – but are in 5.5 – include default-storage-engine and skip-name-resolve.

If you are curious, you can try a free unrestricted copy of MONyog for a month. If you have any suggestions or questions, you are welcome to email them to support@webyog.com. Cheers!