Top 10 things to monitor on your MySQL Server
Shalmali Aradhya
Monitoring MySQL server 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’s walk you through the crucial variables you should be monitoring. Let’s 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 it’s not even available. MySQL downtime is simply not acceptable in production. At the same time ensuring zero downtime does not guarantee maximum performance.
Just 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 a deadlock. You should know what deadlocks occurred and verify that applications have handled them properly or take proper action.
To detect deadlocks, you can 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 the 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 suit 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 the creation of temporary tables or similar) may turn a 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.
