Spick and Span

Databases are complex beasts that, if not tamed, can bring your application to its knees. Throwing more hardware at the problem might give you some momentary respite, but this solution to satiate an ill-mannered database's unending appetite for resources will only end up breaking the bank.

In fact, what is required is to chip away the inconsistencies and optimize the database for maximum performance. This process isn't a one-time task and is easier said than done. Like security, performance optimization is an on-going process that begins as you roll out and provision the database during development and continues into the production environment.

Percona Monitoring and Management (PMM) [1] is an AGPL-licensed open source platform that, as its name suggests, will help you monitor and tweak your databases. PMM supports several popular open source databases (e.g., MySQL, MariaDB, PostgreSQL, and MongoDB) and can help you gain insight into their complexity and performance. Moreover, you can use PMM to manage databases, whether they are hosted in the cloud or on premises.

PMM lets you drill down and identify database-specific queries that are causing bottlenecks. One of PMMs highlights is its Query Analytics function, which can help identify problematic queries thanks to several useful filtering options.

Originally released in 2016, the second major release of the platform in 2019, dubbed PMM2, comes with lots of new features, including several enhancements to the Query Analytics function. A new Security threat tool, run daily, checks for common database security issues and flags any non-compliance.

Installing the Server

The main component of the PMM client-server architecture is a server that is responsible for aggregating and visualizing data from databases. The machine on which you install it becomes the monitoring host.

You can install the PMM server [2] as a Docker container or as a virtual appliance, or you can host it on the AWS cloud. To install it as a container, set up Docker on the monitoring host and enter the commands:

$ sudo docker create --volume /srv --name pmm-data percona/pmm-server:2 /bin/true

This command pulls the PMM2 image from the Percona repository and creates a persistent data container. Once the container has been created, you can fire it up with:

$ sudo docker run --detach --restart always --publish 80:80 --publish 443:443 --volumes-from pmm-data --name pmm-server percona/pmm-server:2

Now fire up a web browser and head to https://<IP address>:443 to bring up the PMM administration interface. Make sure you use the IP address or the hostname of the monitoring host on which you've installed the PMM server Docker image.

You can now log in with the default credentials, admin:admin . You'll be asked to reset the default password for the admin account as soon as you log in.

Installing Clients

Now that your monitoring server is all set up to receive and process data from the databases, it's time to install the PMM client on the database server.

The PMM client collects metrics about the database server, along with general system metrics, and packs it all to the PMM server. If you have multiple database servers, you'll have to install PMM clients on each of them. The PMM client is available as a 64-bit pre-compiled binary for both DEB- and RPM-based installations.

To begin, fetch and install the respective repository for your distribution and install the PMM client package. On DEB-based systems, the command would be:

$ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
$ sudo dpkg -i percona-release_latest.generic_all.deb
$ sudo apt-get update
$ sudo apt install pmm2-client

Similarly, on RPM-based installations, the commands would be:

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ sudo yum install pmm2-client

The PMM client package comprises a couple of command-line utilities, the most important being pmm admin, which is used to manage the client and hook up the database server to the PMM monitoring server:

$ sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP address>:443

Remember to use the appropriate values for the server URL option. You'll have to replace admin:admin with the username and password that you specified while logging into the administration interface of the monitoring server earlier. Also remember to replace the IP address with the address of the PMM monitoring server in your environment.

Once the server and the client are communicating, you'll have to configure how to send query metrics data from the database server to PMM. The process varies from one database server to another.

For instance, you can connect MySQL with:

$ pmm-admin add mysql --query-source=slowlog --<username>=root --password=<password>

Replace the username and password with the credentials of your MySQL database server. PMM can fetch query analytics with either slow log or the performance scheme, which isn't as data rich, although it contains all the critical data and is generally faster to parse. To use it, modify the above command to:

$ pmm-admin add mysql --query-source=perfschema --<username>=root --password=<password>

If you don't use MySQL, you can refer to PMM's official documentation to connect either PostgreSQL [3] or MongoDB [4].

Monitoring Performance

Now that the client is feeding data from the database server to PMM, it's time to log in to the monitoring server and use it to optimize the database. To begin, fire up the browser and head to PMM's administration interface, which is https://<IP address>:443 .

The Home Dashboard (Figure 1) gives you a rolled up view of all the hosts and database servers in your environment. PMM uses Grafana to visualize all the analytics.

Figure 1: Despite the number of visualizations, the dashboard remains quite responsive because it only loads the metrics as they come into view.

At the top left of the interface is a dashboard drop-down menu that lists all dashboards, organized into folders (Figure 2). The default PMM installation provides more than 30 dashboards, although the exact number available depends on the database server that is feeding in the information.

Figure 2: The dashboards list features a text box that you can use to search for the desired dashboard.

A dashboard can be classified broadly into four categories: the Overview dashboards provide a general view of the selected category (e.g., MySQL, MongoDB, OS, etc.), the Summary dashboards give a more detailed view of the object, the Details dashboards help display more detailed information about a particular object, and the Compare dashboards let you place the same type of objects next to each other to help spot differences.

You can begin exploring the interface with the main menu located in the top right corner. Use the Services pull-down menu to get a high-level overview of your database servers. From inside these overviews, you can bring up the Compare dashboard or one of the Details dashboards related to the service.

In any of the dashboards, click the title of the metric in which you are interested and select the View option from the context menu to visualize the metric over time. You can use the time range selector at the top of the interface to define a custom time period. One dashboard I find particularly useful is the Node Summary dashboard (Figure 3), which comes in handy for observing and making sense of hardware resource usage on servers by tracking several additional parameters.

Figure 3: Mouse over the i button in the title of a graph to view information about how a particular metric is collected and what it signifies.

For instance, instead of just CPU Usage, you can see CPU Saturation and Max Core Usage, as well, which is particularly useful because single queries in MySQL can only execute on one CPU core. You can use this dashboard to ensure your multicore CPUs are being used efficiently. Similarly, the Memory Utilization and Disk IO throughput metrics display additional parameters that can help ensure that the concerned resource is being used efficiently.

In addition to keeping an eye on your databases and database servers, you can use PMM to trigger alerts when a defined threshold is breached, either with Grafana's Alerting feature [5] or with an external Prometheus Alertmanager [6].