Monitoring MySQL with NGINX Amplify

Monitoring MySQL with NGINX Amplify

table.nginx-blog, table.nginx-blog th, table.nginx-blog td {
border: 2px solid black;
border-collapse: collapse;
}
table.nginx-blog {
width: 100%;
}
table.nginx-blog th {
background-color: #d3d3d3;
align: left;
padding-left: 5px;
padding-right: 5px;
padding-bottom: 2px;
padding-top: 2px;
line-height: 120%;
}
table.nginx-blog td {
padding-left: 5px;
padding-right: 5px;
padding-bottom: 2px;
padding-top: 5px;
line-height: 120%;
}
table.nginx-blog td.center {
text-align: center;
padding-bottom: 2px;
padding-top: 5px;
line-height: 120%;
}

The initial surge of web servers for the Internet tended to run the famous LAMP stack: Linux, Apache, MySQL, and PHP (or Perl). However, for higher‑performance sites, the LAMP stack is often replaced by the LEMP stack: Linux, NGINX (Engine‑x), MySQL, and PHP, Perl, and/or Python. Today, more than half of the world’s busiest 100,000 websites use NGINX.

The use of NGINX instead of the Apache web server as the frontend to popular PHP applications like WordPress, Drupal, and Joomla enables more efficient utilization of the underlying server and the OS resources, and often manifests itself in the ability to serve at least ten times more users on the same hardware.

For instance, it’s common to see NGINX deployed with a popular PHP application. In this case, NGINX typically works as a local web accelerator and PHP‑FPM serves as the application server. This setup has proven extremely useful for offloading SSL termination, content caching, authentication, and other aspects of HTTP security from the PHP application to NGINX.

In a previous release of NGINX Amplify, we added the ability to collect PHP‑FPM metrics. Now we’re announcing another useful plug‑in for Amplify, which collects and visualizes metrics for MySQL, making LEMP monitoring with Amplify complete. The same plug‑in works for other servers compatible with MySQL, such as MariaDB and Percona.

MySQL and compatible databases are very popular. MySQL is generally considered the #1 relational database, ahead of alternatives such as Microsoft SQL Server, MongoDB, PostgreSQL, NoSQL, Oracle. So the addition of MySQL metrics collection to Amplify will empower a great many users of NGINX.

Configuring the MySQL Plug-in

As when it monitors NGINX or PHP‑FPM, the Amplify agent needs to be able to detect the MySQL master process automatically when it’s installed, and start to collect the metrics. If everything is set up properly, you immediately see a set of out-of-the-box graphs for MySQL in Amplify, along with a few useful extended metrics like the utilization of the InnoDB buffer pool.

Let’s proceed to the actual configuration process for the Amplify MySQL plug‑in. In order for the Amplify agent to monitor MySQL, you need to do the following:

1. Create a new MySQL user for the Amplify agent, called amplify-agent.

   $ mysql -u root -p
   ...
   mysql> CREATE USER 'amplify-agent'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD_HERE';
   Query OK, 0 rows affected (0.01 sec)

   where YOUR_PASSWORD_HERE is a secure password specifically for the amplify-agent user account. (Note this is NOT the password for the MySQL root user!)

2. Verify that the amplify-agent user can read MySQL metrics.

   $ mysql -u amplify-agent -p
   ...
   mysql> show global status;
   +---------------------------+--------------------------------------------------+
   | Variable_name             | Value                                            |
   +---------------------------+--------------------------------------------------+
   | Aborted_clients           | 0                                                |
   ...
   | Uptime_since_flush_status | 1993                                             |
   +---------------------------+--------------------------------------------------+
   353 rows in set (0.01 sec)

3. Update the Amplify agent software to the most recent version.

4. Add the following to /etc/amplify-agent/agent.conf:

   [extensions]
   ..
   mysql = True
   
   [mysql]
   #host =
   #port =
   unix_socket = /var/run/mysqld/mysqld.sock
   user = amplify-agent
   password = YOUR_PASSWORD_HERE

   where YOUR_PASSWORD_HERE is the same as in Step 1.

5. Restart the Amplify agent.

The agent is now able to detect the MySQL master process and collect the metrics.

Troubleshooting

For MySQL metrics collection to work, the Amplify agent must run in the same process environment as MySQL and be able to find the mysqld processes with ps(1). For example, if the MySQL server runs inside a Docker container on the host system where the Amplify agent is running, you need to add the Amplify agent to that Docker container.

Here’s a list of possible causes if the MySQL metrics aren’t being collected:

• The MySQL instance isn’t local. At this time, you need to run the agent on the host where the MySQL server is started.
• The amplify-agent user can’t query the global status metrics. You can easily check it as the mysql(1) client, and fix the permissions if necessary.

If checking the above issues doesn’t help, enable the Amplify agent’s debug log, restart the agent, wait a few minutes, and then create an issue via the Intercom chat button in the bottom‑right corner of the Amplify window. Attach the log to the Intercom chat. We’ll be happy to help.

MySQL Metrics in Amplify

Below is the list of the MySQL metrics that are currently supported in Amplify. The agent retrieves most of the metrics from the MySQL global status variables. (Some metric names are broken across multiple lines for improved table layout.)

Amplify Metric	Description	PHP‑FPM Status Metric
mysql.global.connections	Number of connection attempts to the MySQL server (successful or not).	SHOW GLOBAL STATUS LIKE "Connections";
mysql.global.questions	Number of statements executed by the server. See MySQL reference manual for details.	SHOW GLOBAL STATUS LIKE "Questions";
mysql.global.select	Number of select statements executed.	SHOW GLOBAL STATUS LIKE "Com_select";
mysql.global.insert	Number of insert statements executed.	SHOW GLOBAL STATUS LIKE "Com_insert";
mysql.global.update	Number of update statements executed.	SHOW GLOBAL STATUS LIKE "Com_update";
mysql.global.delete	Number of delete statements executed.	SHOW GLOBAL STATUS LIKE "Com_delete";
mysql.global.writes	Sum of preceding insert, update, and delete counters.	–
mysql.global.commit	Number commit statements executed.	SHOW GLOBAL STATUS LIKE "Com_commit";
>mysql.global.slow_queries	Number of queries that have taken more than long_query_time seconds.	SHOW GLOBAL STATUS LIKE "Slow_queries";
mysql.global.uptime	Number of seconds that the server has been up.	SHOW GLOBAL STATUS LIKE "Uptime";
mysql.global.aborted_ connects	Number of failed attempts to connect to the MySQL server.	SHOW GLOBAL STATUS LIKE "Aborted_connects";
mysql.global.innodb_buffer_ pool_read_requests	Number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.	SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_ reads";
mysql.global.innodb_buffer_ pool_hit_ratio<	Hit ratio reflecting the efficiency of the InnoDB buffer pool.	–
mysql.global.innodb_buffer_ pool_pages_total	Total size of the InnoDB buffer pool, in pages.	SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_ pages_total";
mysql.global.innodb_buffer_ pool_pages_free	Number of free pages in the InnoDB buffer pool.	SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_ pages_free";
mysql.global.innodb_buffer_ pool_util	InnoDB buffer pool utilization.	–
mysql.global.threads_ connected	Number of currently open connections.	SHOW GLOBAL STATUS LIKE "Threads_connected";
mysql.global.threads_running	Number of threads that are not sleeping.	SHOW GLOBAL STATUS LIKE "Threads_running";

Among the metrics it makes sense to check periodically are the following:

• Number of currently open connections
• Number of executed MySQL statements (for example com_select)
• Number of slow queries
• InnoDB pool efficiency
• Overall MySQL availability

Conclusion

We hope it’ll be convenient for you to see the MySQL metrics in NGINX Amplify and have a broader view into the application behavior. With the metrics for NGINX, the Linux OS, PHP‑FPM, and now MySQL, monitoring a LEMP stack with Amplify becomes complete.

Here are some useful links and resources for monitoring MySQL with Amplify:

• MySQL metrics in the Amplify documentation
• Connecting to the MySQL Server in the MySQL documentation
• End‑User Guidelines for Password Security in the MySQL documentation
• Server Status Variables in the MySQL documentation

We're planning to monitor even more application stack components in Amplify soon, so please keep in touch. If you have any suggestions, let us know.

Many thanks for using NGINX Amplify!

The post Monitoring MySQL with NGINX Amplify appeared first on NGINX.

Source: Monitoring MySQL with NGINX Amplify