Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Monitoring MySQL

DZone's Guide to

Monitoring MySQL

MySQL is one of the most common open-source databases used today. A working, performant database is key to app performance — so monitoring your database is crucial.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

This article is featured in the new DZone Guide to Databases. Get your free copy for more insightful articles, industry statistics, and more!

The Internet permeates our entire culture: we buy things online, take classes online, consume media using applications and websites, and communicate and keep in touch with people via social media and email on websites and applications. Databases contain all the raw data that applications and websites use to assemble a user-friendly access method for online and application interaction.

Databases are the backbone of the modern Internet. There are many database options out there, but one of the most ubiquitous and most-used databases is MySQL.

What Is MySQL?

MySQL is the world’s most popular free and open-source relational database. It is now over 20 years old. As an open-source software product, it is free to download, use, and reuse. Much of the development is done in the open-source community (outside of the company that is generally responsible for MySQL: Oracle).

MySQL is a fast, stable, multi-user, multi-threaded SQL database server, generally compliant with ANSI SQL 99. It represents the “M” in LAMP stack (and many other stack models). It is used by small and large websites, including Facebook, Google, YouTube, and Twitter.

A single instance of MySQL uses replication to update the data it contains. Replication is the concept of a master and slave using a binary log to copy database changes. Changes made to the master are written to the binary log, and then these logs are used to propagate changes to the slaves connected to the master.

Image title

Another important feature of MySQL (and any database, really) is high availability. This means guaranteeing that the database is working and available for applications and websites to query as much as possible (preferably,“always”). In high-traffic environments or under certain conditions, databases can get either overloaded or cut off from communication. This causes applications and websites to cease working or to slow down to the point that the user experience is greatly affected. For MySQL, high availability is achieved via:

  • Floating IP address between dual masters (so that if one goes down or becomes unresponsive, another takes over).
  • Using a proxy, such as:
    • HAProxy — TCP only (layer 4)
    • ProxySQL — MySQL protocol aware (layer 7)
  • Using other software technologies, like Galera Cluster:
    • This software has multiple writeable masters and virtually asynchronous replication.
    • Galera Cluster itself is open-source and has been incorporated into various software options, such as:
      • MariaDB Galera Cluster
      • Percona XtraDB Cluster

Why Monitor MySQL?

Your MySQL database is one tier in your typical website or application, and it is where the data is safely persisted to disk. The database tends to be the “black box” for most operations people: queries go in, results come out.

Monitoring, as a whole, is aimed at demystifying what metrics are key to your MySQL database performance. If you don’t monitor objective metrics like CPU, load, or queries per second, you cannot make predictions.

Predictions allow you to provision your environment correctly to ensure the best performance for your customer use case.

For example, you might want to predict:

  • The saturation point of a single server — or how long can we continue to increase traffic at the current rate before MySQL becomes a bottleneck?
  • What are the deviations from a specified benchmark — or how much work are you doing right now vs. how much is the most you can feasibly deliver?
    • Benchmarks are the key to determining your theoretical database maximum load.

You want your database platform to be accessible by many types of users in your operations, engineering, and even business units:

  • DBAs care about:
    • Low-level metrics on a per-MySQL basis.
    • Cluster-level metrics such as: How is replication? How is cluster membership in Percona XtraDBCluster?
    • Is flow control occurring?
    • Which queries are running in my database server? How often are they running? Which are the slowest-performing queries? What can I do to improve the performance?
  • Application developers care about:
    • What is the performance of my newly released query? What can I do to improve the performance, and does it need an index or a re-write?
    • What is the performance of the database tier in the application stack?
  • Business managers care about:
    • Is the database tier presenting a bottleneck to user response time? Is the database tier generating errors in my application?

What to Monitor in MySQL

So, what should you be monitoring in MySQL? There are many ways to answer this question, depending on what you are trying to do. However, for general performance issues, there are a few fairly standard metrics you can use to verify that your MySQL performance is being maintained:

  • Relationship of Threads_connected to Max_connections

    • As you approach Max_connections, your database will stop responding to new connections from your application and your website or application will go down.

  • Threads_running as a factor of the count of cores in your database server
    • As your workload increases, you should pay attention to the ratio of Threads_running vs. the count of cores.
      • Note that this ratio depends on how quickly your queries execute: if you are operating an OLTP application with ~100 milliseconds or faster query response time, you can generally sustain a higher average Threads_running because your queries enter and leave the InnoDB kernel more quickly.
      • Conversely, with an OLAP system and queries > 1 second, you will find that you cannot sustain much more than a 1:1 ratio of Threads_running to count of CPU cores as subsequent queries will be waiting in the queue to execute.
  • Uptime < 1800
    • This is an indication that your database server has recently restarted.
    • Restarts should be a rare occurrence, and always should be investigated.
  • Replication lag
    • MySQL replication works based on a master writing database changes to a local file called the Binary Log.
    • This Binary Log is copied down to slave(s) (your Read Replicas) and written into the slave’s Relay Log.
    • The slave’s Relay Log is then read and database changes applied locally.
    • This all occurs in an asynchronous fashion, which by design can lead to a Slave being “behind” in replication — events that occurred on the master at Time 0 may not be applied on the slave until Time 5.
    • The implication is that if you write a record to the master and immediately trigger a read from a replica, you might not yet see this data. Therefore, you want to pay attention to the slave’s server variable Seconds_behind_master and ensure you don’t read from a Slave that is “too far behind” (this is dependent on your particular application’s needs).

Percona Monitoring and Management

Percona Monitoring and Management (PMM) is one way to monitor your database. It is a free, open-source database monitoring and performance optimization tool for MySQL and MongoDB. The PMM software is built on other open-source projects (for example, Grafana, Prometheus, and Consul) that are deployed in your environment on your equipment (not a SaaS).

PMM provides secure communications using SSL between clients and server. It has the following features:

  • QAN for MySQL
    • Observe the queries consuming the most amount of time in MySQL
    • Visualize query characteristics such as:
      • Query response time, query count
      • Rows sent vs. rows examined
      • InnoDB operations: reads, waits
      • Exposes MySQL query plan information via EXPLAIN in table and JSON format so you can evaluate the level of optimization
      • Review the CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEXES for your query so you can make appropriate tuning modifications (index addition, column type changes, etc.
  • Metrics Monitor
    • Visualize activity over time
      • System-level resources: CPU, Load, Memory
      • MySQL: Queries Per Second (QPS), replication, storage engine-specific (for example, InnoDB, Aria, MyISAM)
      • Cluster: Galera Cluster, Percona XtraDB Cluster
      • ProxySQL overview

Conclusion

Monitoring your MySQL database allows you to understand what is happening as MySQL processes queries, provides insight into performance and what affects performance, and allows you to make informed predictions about what is needed in your database environment.

This article is featured in the new DZone Guide to Databases. Get your free copy for more insightful articles, industry statistics, and more!

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,monitoring ,mysql ,tutorial ,pmm

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}