Monitoring MySQL with NGINX Amplify

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 of the world’s [ngx_snippet name=’proportion-top-sites’] use NGINX than any other server.

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:

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!)

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)

Update the Amplify agent software to the most recent version.

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.

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	MySQL Global Status
`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!