MySQL/MariaDB Database Performance Monitoring with Percona on Ubuntu Server

This tutorial is going to show you how to install PMM (Percona Monitoring and Management) on Ubuntu to monitor MySQL/MariaDB database performance.

Percona Monitoring and Management (PMM) Features

Percona is a provider of open-source database solutions. PMM features:

• Free and open-source. PMM uses client-server model. The client and server software are installed on your own hardware.
• Supports MySQL/MariaDB, PostgreSQL, MongoDB, and ProxySQL, so you can monitor all of your open source databases in one place.
• Supports InnoDB, XtraDB, and MyRocks storage engines for MySQL/MariaDB.
• Supports WiredTiger, MMAPv1, InMemory, and RocksDB storage engines for MongoDB.
• Query analytics and metrics monitors.
• Run checks for common database security issues.
• Easily identify unexpected database queries to improve data security.
• Supports Percona XtraDB Cluster.

To use PMM, you need to install the server component on a central server, then install the client component on your MySQL/MariaDB hosts that you want to monitor. The client sends performance statistics to the server. If you have only one MySQL/MariaDB host, then you can install both the server and client component on this host.

Step 1: Install Docker on Ubuntu 22.04/20.04 Server

The PMM server component is distributed as a Docker image, so we need to install Docker.

Docker is included in the Ubuntu software repository. However, to ensure that we have the latest version, we need to install it from Docker’s APT repository. Run the following command to add Docker repository to your Ubuntu server.

echo "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list

Next, run the following command to import the Docker GPG key to Ubuntu system so that APT can verify package integrity during installation.

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

And because this repository uses HTTPS connection, which I recommend all software repositories should be using, we also need to install apt-transport-https and ca-certificates package.

sudo apt install apt-transport-https ca-certificates

Finally, update the package index on your Ubuntu system and install docker-ce (Docker Community Edition).

sudo apt update

sudo apt install docker-ce

Once Docker is installed, the Docker daemon should be automatically started. You can check its status with:

systemctl status docker

If it’s not running, then start the daemon with this command:

sudo systemctl start docker

And enable autostart at boot time:

sudo systemctl enable docker

Check Docker version.

docker -v

Sample output:

Docker version 20.10.18, build b40c2f6

Step 2: Install PMM Server Component

Create Docker data volume for PMM server.

sudo docker create -v /srv --name pmm-data percona/pmm-server:latest /bin/true

Run the PMM server Docker container.

sudo docker run -d -p 8000:80 -p 8443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:latest

List running Docker containers.

sudo docker ps

You should find the pmm-server Docker container is running.

CONTAINER ID   IMAGE                  COMMAND                CREATED         STATUS                                                                                            
c9199659fdd9   percona/pmm-server:2   "/opt/entrypoint.sh"   9 seconds ago   Up 8 seconds (healthy)

Now you can access the PMM web-based dashboard via https://your-server-ip:8443. By default it’s using a self-signed TLS certificate, so you need to add a security exception in your web browser.

• In Firefox, click Advanced and click Accept the risk and Continue.
• In Google Chrome, click Advaned and Proceed to your-server-ip(unsafe) .

The default username/password is admin:admin. After the first login, you have to set a new password. (The password should not contain a / character, or the PMM client will have trouble conencting to the PMM server.)

If you have enabled the UFW firewall, then you need to allow TCP port 8443 in order to access the PMM dashboard.

sudo ufw allow 8443/tcp

PMM dashboard

There’s a default host in the dashboard, i.e, the pmm-server Docker container.

Step 3: Install PMM Client Component on Your MySQL/MariaDB Host

The PMM clients has Deb package repository for Debian/Ubuntu.

Download PMM repository package.

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

Install the package.

sudo apt install ./percona-release_latest*.deb

Now the Percona repository is added, update local package index and install PMM client.

sudo apt update

sudo apt install pmm2-client

The PMM agent will automatically start, as you can see with:

systemctl status pmm-agent

If it’s running, you can start it with:

sudo systemctl enable pmm-agent --now

Next, connect the client to the server with the following command.

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin_assword@your-server-ip:8443

Sample output:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Reload the PMM server dashboard. You can find the MySQL/MariaDB host at the bottom of the page.

Hint: If you want to configure the PMM agent to connect to a different PMM server, simply run the above command again. Of course, you need to change the IP address and password.

Now we need to configure the PMM client to monitor MySQL/MariaDB database. Log into MySQL/MariaDB console.

sudo mysql -u root

or

mysql -u root -p

Create a PMM user for monitoring.

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'secret_password' WITH MAX_USER_CONNECTIONS 10;

Grant permissions.

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

Flush privileges table

FLUSH PRIVILEGES;

And exit.

EXIT;

Run the following command to add the MySQL/MariaDB service to PMM.

sudo pmm-admin add mysql --username=pmm --password=secret_password --query-source=perfschema

Sample output:

MySQL Service added.
Service ID  : /service_id/8597086d-828e-47aa-a74f-9c86918af208
Service name: linuxbabe.com-mysql

Table statistics collection disabled (the limit is 1000, the actual table count is 2600).

Step 4: Monitor MySQL/MariaDB Database

If your MySQL/MariaDB is slow, the first thing you need to do is to check QPS (Queries per second), which is a metric that monitors the load on the database server. As you can see from the following screenshot, one of my MariaDB database servers is under heavy load (around 700 queries per second).

Then you will need to use Query Analytics (QAN) to analyze those SQL queries.

If there are multiple databases, the Query Analytics panel can show you which database is the busiest.

What is the Perfect Hardware to Run MySQL/MariaDB?

• If your database is large and you need to scale your application, prepare for at least 8 CPU cores.
• Use NVMe SSD
• Use RAID to increase IO performance.
• The more RAM you have, the more data from the database can be kept in memory, thus avoiding disk access.
• You should not use swap space, which will greatly degrade database performance. However, Linux likes swapping for several reasons. You can read the following article to learn how to create and how to disable swap space.

Create Swap File on Cloud Linux Server to Prevent Out of Memory

Database Design

After choosing the hardware, the next step for a performant MySQL/MariaDB database is how to design your database, which is the developer’s job. The following factors should be considered:

• Schema: Design your database schema so data can be fetched as fast as possbile.
• Data types: Choose the right data type for efficient disk storage.

If you use an established web application like WordPress/WooCommerce or Magento, then database design is already taken care of.

Simple MySQL/MariaDB Perf Tunning

Here is the InnoDB configuration in my /etc/mysql/mariadb.conf.d/50-server.cnf file. This is a very simple performance tuning.

innodb_buffer_pool_size = 2048M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M

#Improving disk I/O performance
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 3

Where:

• The InnoDB buffer pool size needs to be at least half of total RAM or 70% of free RAM. ( Note: You should avoid swapping.) The InnoDB buffer pool is a temporary in-memory cache for MySQL/MariaDB, so if you have enough RAM, you should increase the parameter. The best scenario is that all your data resides in memory, so disk access can be eliminated.
• InnoDB log file size needs to be 25% of the buffer pool size.
• InnoDB can use direct IO on Linux and FreeBSD.
• Set the read IO threads and write IO thread to the maximum (64)
• Make MariaDB use 3 instances of InnoDB buffer pool. The number of instances needs to be the (number of CPU cores) * (threads per core) on your system.

After saving the changes, restart MariaDB.

sudo systemctl restart mariadb

Using mysqltuner

mysqltuner is a script for MySQL/MariaDB performance tuning. Install it from the Ubuntu repository.

sudo apt install mysqltuner

Then simply run

sudo mysqltuner

It will analyze your database server and give recommendations. For example, mysqltuner detected that my data size is 23.5G, so it suggests that I need to increase innodb_buffer_pool_size to 23.5G.

Using Redis Cache

Redis works like MySQL query cache. (Note: Query cache is removed in MySQL 8.) It caches query results in memory and can give tremendous performance improvements.

Install Redis Server and the PHP Extension

sudo apt install redis-server php-redis

If you use PHP7.4, then you need to install php7.4-redis.

sudo apt install php7.4-redis

If you use PHP8.1, then you need to install php8.1-redis

sudo apt install php8.1-redis

Recommended Reading: How to Install Multiple Versions of PHP on Ubuntu Server

After it’s installed, Redis should be automatically started. Check its status:

systemctl status redis

Sample output:

* redis-server.service - Advanced key-value store
     Loaded: loaded (/lib/systemd/system/redis-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2022-07-30 10:54:02 SAST; 1 months 11 days ago
       Docs: http://redis.io/documentation,
             man:redis-server(1)
   Main PID: 1176 (redis-server)
      Tasks: 4 (limit: 38329)
     Memory: 3.0M
     CGroup: /system.slice/redis-server.service
             `-1176 /usr/bin/redis-server 127.0.0.1:6379

You can also manually start it and enable auto start with system boot.

sudo systemctl enable --now redis-server

Then check the status of Redis PHP extension.

php --ri redis

Sample output:

redis

Redis Support => enabled
Redis Version => 5.3.7
Redis Sentinel Version => 0.1
Available serializers => php, json, igbinary
Available compression => lzf, zstd, lz4

We can see that the extension is enabled.  Now the back end is set up, let’s configure the WordPress frontend.  We need to install and configure the Redis Object Cache WordPress Plugin.

If you have installed W3 Total Cache, WP Super Cache or other caching plugins in WordPress, remove them first. Then install Redis Object Cache plugin by Till Kruss and active it.

Then go to Settings > Redis. Click Enable Object Cache.

You should see the connection is established.

You can also issue the following command on your Ubuntu server.

redis-cli monitor

You can see the Redis Cache processing in real time, which means Redis cache is working properly on your WordPress site.

If you encounter the following error, it means the Redis server requires a password to access.

NOAUTH Authentication required

Since Redis only listens on localhost, I don’t think a password is necessary, so you can disable password authentication. (If you use UFW firewall to block public access to port 6379, that also eliminates the need for password authentication.)

sudo nano /etc/redis/redis.conf

Find the requirepass line and comment it out.

#requirepass secret_password

Save and close the file. Restart Redis.

sudo systemctl restart redis-server

Hint: WordPress 6.1 introduces WP-Query cache, which produces massive database performance improvements out of the box.

Wrapping Up

I hope this article helped install PMM to monitor MySQL/MariaDB performance. Want to monitor Nginx and PHP-FPM performance? Please read the following article:

• LEMP Stack Performance Monitoring With Nginx Amplify on Ubuntu 20.04/18.04

You might also want to set up Nginx FastCGI cache to reduce server response time.

• Set Up Nginx FastCGI Cache to Reduce WordPress Server Response Time