NGINX.COM
Web Server Load Balancing with NGINX Plus

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 1 million busiest websites 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:

  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 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:

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!

Hero image

Learn how to deploy, configure, manage, secure, and monitor your Kubernetes Ingress controller with NGINX to deliver apps and APIs on-premises and in the cloud.



About The Author

NGINX part of F5

F5 NGINX

F5 is the company behind the popular open source project, NGINX. We offer a suite of technologies for developing and delivering modern applications. Together with F5, NGINX solutions bridge the gap between NetOps and DevOps, with multi‑cloud application services that span from code to customer.

About F5 NGINX

F5, Inc. is the company behind NGINX, the popular open source project. We offer a suite of technologies for developing and delivering modern applications. Together with F5, our combined solution bridges the gap between NetOps and DevOps, with multi-cloud application services that span from code to customer.

Learn more at nginx.com or join the conversation by following @nginx on Twitter.