Blog

Top 10 things to monitor on your MySQL

Top 10 things to monitor on your MySQL monyog

Monitoring MySQL servers is no rocket science provided you know what to monitor. MySQL gives a comprehensive list of variables to check your server’s health and performance. Let me walk you through the crucial variables you should be monitoring. Lets assume that you have one or more MySQL servers, which have been setup and running fine. Here are the top 10 things to monitor on your MySQL.

1. MySQL availability

Yes, this is the first thing you should be looking at! It would not make sense to monitor your MySQL if its not even available. MySQL downtime is simply not acceptable in production. At the same time ensuring zero downtime does not guarantee maximum performance.
You can execute

-mysqladmin -h 192.168.1.95 -u root -p status

to know if MySQL is running or you can just ping MySQL using service mysqld status if you are using RedHat Linux.

2. Presence of insecure users and databases

Do any of your MySQL users have ‘%’ as host? Meaning that the user can connect to MySQL from anywhere around the world? If yes, your servers are prone to attacks. For maximum security, it would be better to give literal host values instead of ‘%’. If for instance, your host is localhost then the hacker will have to gain access to localhost first before touching your servers.Do you have MySQL users with excessive privileges? Why this question you might wonder, as MySQL by default comes with a root user who has super privileges. Unfortunately the word root is too well known but fortunately it does not have any significance in MySQL at all. You can simply give any other name instead of root.

CREATE USER 'obsure_name'@'%’ IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'obsure_name'@'%' WITH GRANT OPTION;
DROP USER 'root'@‘%’;
FLUSH PRIVILEGES;

In addition, ensure that all your MySQL users have passwords.
The default MySQL installation comes with a database named ‘test’ that anyone can access. This database is intended only for testing or tutorial purpose. The ‘test’ can be accessed by users who do not have explicit privileges granted to that database. Therefore databases named ‘test’ should be avoided on production servers.

3. Aborted connects

aborted_connects gives the total number of failed attempts to connect to MySQL. Excess aborted_connects indicate that the client does not have enough privileges, or the client uses an incorrect password, or someone is trying to hack into your server.
aborted_connects is a global status value and can be retrieved using-

SHOW GLOBAL STATUS LIKE 'aborted_connects';

4. Error log

MySQL Error log not only contains information on server start and stop time but also critical errors that occur while the server is running. On some operating systems, the error log also contains stack trace of MySQL before it crashed. Any entry of type [error] obviously needs your attention.

5. Innodb Deadlocks

Innodb rolls back transactions if there is a deadlock. Knowing its occurrence is important to trace back the root cause of deadlock. You should know what deadlocks occured and verify that applications have handled them properly or take proper action.
To detect deadlocks, use

SHOW ENGINE INNODB STATUS;

6. Change in server configuration

Ever wondered why your server was performing perfectly a few weeks ago but isn’t now? Then you should be looking at the MySQL configuration changes that were made in the recent past. MySQL configuration plays a critical role in your server’s performance.

7. Slow Query log

Consists of all the queries that exceed long_query_time seconds to execute. More slow queries would mean more disk reads, more memory usage, more CPU usage which ultimately just slows down your servers, causes bottlenecks and hence results in poor performance. Slow Query log is where you find queries that are potential targets for optimization.

8. Slave lag

Most production servers have one or more replication slaves. Monitoring slaves are equally important. If slave_sql_running, slave_io_running is NO, then your slave has stopped replicating and should be fixed first. Higher seconds_behind_master is, the slower is your slave. By executing -

SHOW SLAVE STATUS;

you can track your replicas.

9. Percentage of maximum allowed connections

A high value of percentage of maximum allowed connections(max_used_connections / max_connections) tells that you could soon run out of client connections. In other words, new connection requests will simply be refused. So make sure your max_connections is large enough to suite your application. By executing-

SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'max_used_connections';

you can track maximum allowed connections.

10. Percentage of full table scans

Percentage of full table scans is best represented using

((Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)).

An increase in full table scans (or in creation of temporary tables or similar) may turn satisfactory performance into non-satisfactory. You should understand the queries causing this and consider changes to schemas or queries if needed. You can retrieve these metric by executing-

SHOW GLOBAL STATUS LIKE "Handler_read%";

There are many tools that do all the above and much more. If you are looking for a free command line tool, you might want to try Percona toolkit. But if you are looking for a GUI based tool, you might be looking for MONyog – MySQL Monitor and advisor.

10 thoughts on “Top 10 things to monitor on your MySQL

    • It’s not possible to suggest a specific value for ‘Percentage of full table scans’. It depends on server factors
      * Size of the tables
      * How much memory the tables occupy

      Let me explain a little more- A full table scan on a table with a few hundred rows could rarely be a problem
      but a table with a million rows sure sounds like one.

      If your server is currently doing fine, then you might not need worry about ‘Percentage of full table scans’.
      Monitor this over a period of time to see if this value is increasing. If yes, then you should first identify
      those queries. You can check the ‘Slow Query log’ to see all queries taking more then long_query_time
      seconds. Then you should optimize those queries such that they use indexes or create new indexes. Compare these
      optimized queries with the original. If the performance improvement of a SELECT statement is not big then it is a
      ‘trade-off’ between performance of READs and WRITEs.

      Remember that each index slows down INSERT, UPDATE and DELETE statements a little as the index needs to be maintained
      by the server in addition to using disk space.

  1. With regards to point #2, I favour a firewall preventing access to the MySQL port rather than using explicit hostnames in MySQL user accounts, for two important reasons:

    1) If the client address changes (e.g. office ISP change, dynamic IP allocation by their ISP, web server address changes, etc.) using a specific hostname means you’ll have to update their MySQL user account to match. For an ISP with dynamic IP allocation, that could happen quite regularly.

    2) If you add more sites for a particular client (e.g. a second office, another web server in the herd) you need to copy the existing MySQL user details to create a new MySQL user account with the new hostname. You’ll also need to make sure that any privilege changes to the account is performed across *all* of the related MySQL accounts – which may be easy if there’s only one or two sites, but connections from large server farms make even the smallest privilege change quite a significant undertaking.

    In terms of connectivity, I always recommend either a VPN or SSH tunnel for connecting to the MySQL server – this also provides transport-layer encryption (no need to mess around with certificates for MySQL). Both of these are easily permitted by firewalls (even on a non-standard port, if you like “security by obscurity” – although I always recommend changing the SSH listening port, to stop drive-by scanning)

    If you *do* feel inclined to set up host-specific MySQL user accounts, use IP addresses and add the “skip-name-resolve” option to your server configuration – this will save you a DNS lookup on each connection attempt.

    • @Richard .. all your points are valid.

      However not all MySQL users manage the server themselves but use a ‘managed hosting’ concept (because it is simpler and cheaper and they don’t have the expertise in house) where setting MySQL configuration options, firewall settings are out of reach. This also applies to some Cloud-based hosting concepts (Amazon RDS for instance).

  2. Pingback: Tweet Parade (no.36 Sep 2012) | gonzoblog.nl

  3. Pingback: Blodhemn ! | Tweet Heat – The hottest Tweets of the Month [Sep 2012]

  4. Pingback: Tweet Heat - The hottest Tweets of the Month [Sep 2012] | Inspired Magazine

  5. Hi,

    Few week ago when i am running some query that show the exact output from client side. Now the situation is when i am running the same query it`s show the error My SQL server lost connection from client window. But same query will query continuous run from Server side. I just want to know where is exact problem.

  6. Pingback: How To Fix How To Change Password In Sqlyog Errors - Windows Vista, Windows 7 & 8

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>