Coupon code: mysql14
Hurry up, offer expires soon!
Blog

Monitoring your MySQL configuration

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!

2 thoughts on “Monitoring your MySQL configuration

  1. I would like to ask you about following parameters which should be low or high for best performance:
    please tell me
    1) Thread cache size — should be low or high for best mysql performance
    2) thread cache hit rate — should be low or high for best mysql performance
    3) thread created — should be low or high for best mysql performance
    4) table lock — should be low or high for best mysql performance
    5) table lock wait — should be low or high for best mysql performance
    6) Maximum open file — should be low or high for best mysql performance
    7) total row returned — should be low or high for best mysql performance
    8) total row returned via index — should be low or high for best mysql performance
    9) table open — should be low or high for best mysql performance
    10) table cache — should be low or high for best mysql performance
    11) Average row per query — should be low or high for best mysql performance
    12) No. of byte received — should be low or high for best mysql performance
    13) No. of Byte sent — should be low or high for best mysql performance
    14) connection used — should be low or high for best mysql performance
    15) query in cache — should be low or high for best mysql performance
    16) query cache hit — should be low or high for best mysql performance
    17) query cache hit ratio — should be low or high for best mysql performance

    I request you to kindly provide me solution as soon as possible.
    I am waiting your response.

  2. @Guarav .. this blog is not the right place for offering support to individual users (such as you). Also the reply to your question may depend on what applications you have, how much RAM you have a vailable and if the machine is running MySQL only or other programs as well (such as a web server). There will often also have to be a ‘trade-off’. Some high settings may improve performance (a little) but can result in long recovery times in case of a crash.

    You are welcome to visit our Forums at http://forums.webyog.com – but please understand that we basically support our own products (SQLyog and MONyog). We do not offer consultancies for MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>