Try These MySQL Database Monitoring Best Practices

Getting management’s attention is a good thing, if it happens for positive reasons. But many database administrators would actually prefer to stay off of the radar. Because DBAs are in charge of making sure the organization’s databases are up and running smoothly, when things are going right, DBAs generally get to hang back and do their thing in peace. But when something goes wrong, suddenly you’re the top name on the company’s Most Wanted list.

DBAs have a lot of responsibility within an organization, and there are many ways things could go south, but by following a few MySQL database monitoring best practices, you can be sure you are recognized by management for your razor-sharp wit rather than the database outage that took three days to resolve.

Conduct Regular Health Checks of Your Database

For a MySQL DBA, scheduling regular health checks for your databases is almost as important as scheduling them for yourself (Note: Consider this your reminder to make an appointment for that physical you’ve been putting off!). 

Like people, not all databases have the same maintenance needs, so health checks should be tailored to the specific function requirements of the database. For example, mission- or life-critical databases need more frequent checks than non-critical databases. The failure of a missile defense system has far harsher implications than the failure of a local bookstore’s frequent customer rewards program.

When setting up your routine health checks, there are a few areas to focus on that will provide information that will help you determine ongoing MySQL issues that need to be addressed.

MySQL Availability

Availability is probably the most important metric to keep track of. If the database isn’t available, there’s not much point in tracking the rest of these metrics until it is fixed. Run -mysqladmin -h 192.168.1.95 -u root -p status to check availability, then begin diagnostics if there’s an issue.

Failed Connections

Tracking the number of failed connections over time can detect malicious activity as well as less sinister errors within the application such as misapplied permissions and incorrect passwords. 

Run the command SHOW GLOBAL STATUS LIKE ‘aborted_connects’; to determine how many aborted connection tries there have been on the database for a given time period.

Error Logs

Monitoring error logs is a great way to see what errors have occurred and whether there are any patterns that may indicate a larger recurring issue that needs to be addressed. 

Deadlocks in InnoDB

MySQL deadlocks occur when different transactions hold a lock on a resource the other transaction needs. Deadlocks result in slow processing and timeouts—and annoyed users. Use SHOW ENGINE INNODB STATUS; to locate deadlocks and resolve them.

Configuration Changes

A sudden decline in performance could be caused by a dozen different things. However, you can save yourself some troubleshooting time by reviewing recent configuration changes and adjusting any that are negatively affecting performance.

Slow Queries Log

Slow queries make the database run slower because they use excessive CPU and memory.  Review the Slow Queries log regularly to determine whether any queries are showing excessive run time, then troubleshoot to determine and rectify the root cause. 

Maintain Visibility to Understand the Root Cause of MySQL Performance Issues 

Conducting regular health checks on MySQL databases is essential to maintaining high availability and minimizing chronic performance issues, but there are some limitations to how useful health checks are to overall system troubleshooting. 

Because periodic performance issues may not show up during routine health checks, it is important to establish continuous visibility into your MySQL environment. Quickly identifying and resolving performance issues means less downtime and fewer performance problems negatively impacting users. 

One of the best ways you can maintain visibility into MySQL database systems is to set alerts for critical events so you can react quickly when a threshold is exceeded and get to the root cause of the problem.

At a minimum, you should set alert thresholds for the following common sources of performance degradation: 

• Significant deviations from baseline MySql performance metrics
• High CPU usage
• Query latency
• Query errors
• Connection limits
• Buffer pool usage

Identify and Resolve Performance Issues Quickly

You may be able to get away with tracking performance using in-house scripts in a small, static environment, but the scripts won’t replace the proactive monitoring required to keep up with large, dynamic systems that are constantly growing.

In almost every situation, a commercial database monitoring tool is going to be your best bet for identifying and resolving issues quickly before they cause major problems.

There are many performance monitoring tools on the market offering a wide range of features at a lot of different price points. Picking the right tool for your organization is going to boil down to which one best meets your specific needs at a price that fits your budget.

Although there is no one-size-fits-all tool out there, there are some important database monitoring features you’ll want to look for in whichever performance monitoring tool you’re considering:

• Scalability
• Mobile monitoring
• Intuitive UI
• Budget-friendly but feature-rich
• No connection cap
• Multidimensional workload analysis
• Alarm root cause analysis
• Blocking analysis
• Smart alarms
• Historical data tracking