Percona Community Blog - learn about MySQL, MariaDB, PostgreSQL, and MongoDB

docker tag:

perconalab/pmm-server:2.43.0-rc

PMM client package installation

Download AMD64 or Download ARM64 pmm2-client tarball for 2.43.0.
To install pmm2-client package, enable testing repository via Percona-release:

percona-release enable percona testing

Install pmm2-client package for your OS via Package Manager.

OVA

PMM2-Server-2.43.0.ova file

AMI

Run PMM Server hosted at AWS Marketplace instructions

ami-0db618c7da6e202f4

Timeline for Database on Kubernetes

Edith Puclla — Tue, 16 Jul 2024 00:00:00 UTC

The Evolution

Since its inception in June 2014, Kubernetes has dramatically transformed container orchestration, revolutionizing the management and scaling of applications. To mark its tenth anniversary, the Data on Kubernetes Community (DoKC) released an infographic showcasing key milestones and community contributions to the evolution of operators for managing stateful applications. This project was made possible by the collaboration of DoKC members Edith Puclla, Sergey Pronin, Robert Hodges, Gabriele Bartolini, Chris Malarky, Mark Kember, Paul Au, and Luciano Stabel.

Explore the infographic to see how Kubernetes has shaped the future of database management on Kubernetes.

Adoption and Impact

The CNCF says that 84% of organizations are using or considering Kubernetes, with 70% running stateful applications on it in production. The number of users and containers has grown, showing that more people are contributing, adopting cloud-native technologies, and finding new ways to use Kubernetes to manage stateful applications.

Looking Ahead

As we celebrate ten years of Kubernetes, the way databases are integrated keeps improving, thanks to community efforts and new technology. Percona Everest is an excellent example of this progress. It’s more than just a tool for databases; it represents the future of running databases on Kubernetes. It’s open-source and makes running any database on cloud-based Kubernetes clusters easy. If you want to try it, visit our Percona Everest GitHub Repository and give us a star if you like it. For feedback or comments, join the Percona Forum for Percona Everest discussion.

How to Provision a MongoDB Cluster in Kubernetes with Percona Everest Summary

Edith Puclla — Thu, 02 May 2024 00:00:00 UTC

Kubernetes continues evolving, and the complexity of deploying and managing databases within the ecosystem is a topic of considerable discussion and importance these days. This article summarizes a detailed discussion between Piotr Szczepaniak and Diogo Recharte, who offer insights and live demonstrations to simplify database operations on Kubernetes with a new technology for cloud-native applications: Percona Everest. If you want to watch the full video, check out How to Provision a MongoDB Cluster in Kubernetes Webinar.

Peter mentions that Initially, people were doubtful about using virtual machines for databases, just like they were skeptical about Kubernetes. However, the topic brings together many people who run databases on containers to share their use cases and new discussions at events like Data on Kubernetes Day at Kubecon.

The introduction of StatefulSets and Persistent Volumes has altered the perception of Kubernetes from being purely ephemeral to being capable of handling persistent data. This change is important for database applications that require data retention over time.

The Kubernetes ecosystem is rapidly expanding. This growth is thanks to its open-source nature and the continuous addition of new functionalities, such as support for specialized hardware like GPUs, which are crucial for AI and machine learning applications.

Peter also mentioned that its complexity is the main barrier to Kubernetes adoption for databases. Organizations often need help with the layer added by Kubernetes on top of database management. Also, failure in initial attempts to integrate Kubernetes can discourage organizations from further attempts, primarily due to a lack of internal expertise.

Benefits of Database as a Service (DBaaS)

DBaaS significantly reduces the time required for database provisioning, which is particularly useful in organizations needing rapid deployment. Public and private DBaaS solutions offer scalability, which is crucial for handling varying workloads and organizational growth without compromising performance.

Private vs. Public DBaaS

Private DBaaS offers more extensive customization options and control over databases, which is essential for companies with specific needs that public solutions cannot meet.

Data security and compliance with regulations are more manageable in a private DBaaS because it operate within the company’s internal infrastructure.

Demo to Deploying MongoDB on Kubernetes

Diogo presented a demo of deploying a MongoDB database using Percona’s Everest platform on Kubernetes, where he showed how to handle daily operations and disaster recovery scenarios efficiently. Watch the Percona Everest Demo on YouTube

The session explained how Kubernetes operators and custom resources help manage databases more easily. They do this by simplifying complex processes and automating regular tasks.

Some questions that users asked in this presentation are:

How do we handle the PV when the Pods go down?

The PV will remain in place; this is a standard functionality of a stateful set. After the Pod goes down, the replacement Pod will attach to the PVC, which is standard behavior for a stateful set.

What happens if the node in Kubernetes goes down?

It depends on the storage layer that you have configured in your cluster. If the storage class you are using is tied to that node, then placing it on a new node will provision a new one, and some reconciliation will occur within the database itself.

What is the current state of Percona Everest?

Percona Everest is currently in a Beta stage, and Percona aims to release a GA version. The project is fully open source, and anyone can join our project on GitHub. We appreciate feedback from the community.

Do you want to send us feedback or contribute in this cool project? We are completely open-source, you can visit Percona Everest on GitHub.

Using ProxySQL Query Mirroring to test query performance on a new cluster

Isobel Smith — Wed, 01 May 2024 00:00:00 UTC

ProxySQL is an SQL aware proxy, which gives DBA’s fine grained control over clients’ access to the MySQL cluster. A key part of our DBA team’s process in testing and preparing for major MySQL version upgrades is comparing query plans using ProxySQL query mirroring. This feature allows us to mirror queries to another cluster / host, by configuring query rules. What makes mirroring particularly useful is the ability to selectively mirror queries based on the query digest, or client user. Results from the queries that are mirrored are not returned to the client, and are sent to /dev/null.

Before configuring ProxySQL for Query Mirroring, ensure that the clients that you want to mirror the queries for, are able to connect to both the current, and the new cluster. You should also ensure that the ProxySQL monitor can connect to the new cluster, otherwise ProxySQL will mark the new hosts as offline, and the queries will not be mirrored there.

To set up query mirroring in ProxySQL:

In order to set up query mirroring, you need to add the new hosts into the mysql_server table in ProxySQL. This is how the current mysql_servers table looks, before we add the new host that we want to mirror the queries to:

MySQL> SELECT hostgroup_id, hostname FROM mysql_servers;
+--------------+--------------+
| hostgroup_id | hostname |
+--------------+--------------+
| 10 | 10.12.0.123 |
| 20 | 10.12.0.123 |
| 20 | 10.16.0.456 |
| 20 | 10.16.0.789 |
+--------------+--------------+
4 rows in set (0.01 sec)

It is important to choose a hostgroup_id that is not yet in use. You can double check the currently configured host groups in the mysql hostgroups table, as you do not want to inadvertently add the mirror hosts into the production traffic!

MySQL> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+----------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+----------------------+
| 10 | 20 | read_only | Async Cluster |
+------------------+------------------+------------+----------------------+
1 row in set (0.00 sec)

Please note, in our example, we are using async replication, so we check the mysql_replication_hostgroups table, but the hostgroups table you need to check, depends on the cluster architecture you are using:

async replica clusters check the mysql_replications_hostgroups table.
galera clusters check the mysql_galera_hostgroups table
group replication check the mysql_group_replication_hostgroups table.

We are using hostgroup 10 for the writer hostgroup, and hostgroup 20 for the reader. For this example, we will choose 100 for the mirror hostgroup_id. Once you have decided on an unused hostgroup ID, add the new clusters’ nodes to the mysql_servers table in ProxySQL.

MySQL> INSERT INTO mysql_servers(host, hostgroup, comment) VALUES ("10.12.0.987", 100, "mirror_cluster");
LOAD MYSQL SERVERS TO RUN;
SAVE MYSQL SERVERS TO DISK;

The mysql_servers table will now include the new host:

MySQL> SELECT hostgroup_id, hostname FROM mysql_servers;
+--------------+--------------+
| hostgroup_id | hostname |
+--------------+--------------+
| 10 | 10.12.0.123 |
| 20 | 10.12.0.123 |
| 20 | 10.16.0.456 |
| 20 | 10.16.0.789 |
| 100 | 10.12.0.987 |
+--------------+--------------+
4 rows in set (0.01 sec)

In order to enable query mirroring, you need to update the mirror_hostgroup column in the mysql_query_rules table. When mirroring is not enabled, the value of the mirror_hostgroup column is NULL. Our query rules before enabling query mirroring are defined as:

MySQL> select rule_id, username, match_digest, destination_hostgroup, mirror_hostgroup from mysql_query_rules;
+---------+------------------------+---------------------+-----------------------+------------------+
| rule_id | username | match_digest | destination_hostgroup | mirror_hostgroup |
+---------+------------------------+---------------------+-----------------------+------------------+
| 1 | myApplicationUser | ^SELECT.*FOR UPDATE | 10 | NULL |
| 2 | myApplicationUser | ^SELECT | 20 | NULL |
+---------+------------------------+---------------------+-----------------------+------------------+
2 rows in set (0.00 sec)

To enable mirroring, we just need to update the mirror_hostgroup. For this example, we will mirror all the SELECT queries made by myApplicationUser:

UPDATE mysql_query_rules SET mirror_hostgroup = 100 where rule_id=2;
LOAD mysql query rules TO RUN;
SAVE mysql query rules TO DISK;

The rules should now be updated:

MySQL> select rule_id, username, match_digest, destination_hostgroup, mirror_hostgroup from mysql_query_rules;
+---------+-----------------------+---------------------+-----------------------+------------------+
| rule_id | username | match_digest | destination_hostgroup | mirror_hostgroup |
+---------+-----------------------+---------------------+-----------------------+------------------+
| 1 | myApplicationUser | ^SELECT.*FOR UPDATE | 10 | NULL |
| 2 | myApplicationUser | ^SELECT | 20 | 100 |
+---------+-----------------------+---------------------+-----------------------+------------------+
2 rows in set (0.00 sec)

The incoming queries that match the query rule, (in our example above, this is all queries as matching the regular expression ‘^SELECT’, for myApplicationUser, excluding queries matching ‘^SELECT.*FOR UPDATE’), will now be mirrored to the new cluster. You can verify this by checking the MySQL processlist on the new cluster.

The stats_mysql_query_digest table on ProxySQL holds statistics for the queries that are being processed by ProxySQL. To use the stats_mysql_query_digest table, the global variables mysql-commands_stats and mysql-query_digests must be set to true, which is the default.

Comparing query performance between two clusters

Query the stats_mysql_query_digest table to compare the performance per query between the current and the new cluster:

MySQL> select
 (b.count_star+a.count_star)/2 as count,
 cast(round(((b.sum_time + 0.0)/(b.count_star + 0.0))/((a.sum_time + 0.0)/(a.count_star + 0.0)),2)*100 as int) as percent,
 cast(round(((b.sum_time + 0.0)/(b.count_star + 0.0))/((a.sum_time + 0.0)/(a.count_star + 0.0)),2)*100 as int)*(b.count_star+a.count_star)/2 as load ,
 substr(a.digest_text,1,150)
from
 stats_mysql_query_digest a
inner join
 stats_mysql_query_digest b on
 a.digest = b.digest
where
 a.hostgroup = 10
 and b.hostgroup = 100
order by
 percent ASC;

In this example, the current production cluster has hostgroup 10, and the new mirror cluster was assigned hostgroup 100. The queries with a percentage above 100 are the queries that perform slower on the new cluster, and may be worth investigating, while the queries with a percentage below 100 are more performant on the new cluster. To investigate queries, you can compare the EXPLAIN plan of the query on the current and the new cluster. We use PMM Query Analytics to compare query analytics and the explain plan of the queries on the two separate clusters.

It is worth noting, that you should allow enough time for the MySQL buffer pool to get filled, before checking the stats_mysql_query_digest table. Otherwise, the query times on the new cluster can be skewed, as the active dataset may not yet be in memory (whereas on the current cluster it might be). Also, keep in mind that if you are mirroring only a subset of queries, the load on the new cluster will be different to the current cluster, and could affect the query performance on the new cluster, so that they appear significantly faster. Checking the execution plan of the query to see whether it has changed, is therefore more important than looking at overall load.

To conclude, using query mirroring to test queries on a new system, before making the migration, allows you to compare latency and query plans per normalised query, and proactively detect any necessary alterations before switching live traffic to the new cluster.

Creating a Standby Cluster With the Percona Operator for PostgreSQL

Zsolt Parragi — Wed, 27 Mar 2024 00:00:00 UTC

In this video, Nickolay Ihalainen, a Senior Scaling Specialist at Percona Global Services, explains how to set up replication with standby clusters for Kubernetes databases using Percona’s open-source tools, including the Percona Operator for PostgreSQL

A Standby Cluster is a backup version of your main database. It’s there to keep your data safe and make sure your database can keep running even if something goes wrong with the main one.

For this demo, we use Percona Operators for PostgreSQL to create the clusters, which facilitates high availability setups and database management by automating deployment, scaling, and management tasks within Kubernetes environments.

Nickolay created a primary node and the configuration of replication to standby clusters for PostgreSQL, ensuring data redundancy and availability. This primary node has two standby databases in the same primary node.

Then, we have the object storage (S3) for backups, highlighting the importance of having offsite backups in different geographical locations to safeguard against data loss. This is where the primary node will access to make a data replication.

This demo also includes using Patroni to manage this process, enabling replication and failover between primary and standby servers, and PgBouncer, a tool that manages how applications are aligned to communicate with a PostgreSQL database.

Watch our complete hands on in this YouTube video:

You can find more instructions on how to deploy a standby cluster for Disaster Recovery, and also you can Create a Standby Cluster With the Percona Operator for PostgreSQL.

Is you have questions o feedback, write to us in our Percona Comunity Forum

Setting Up Your Environment for Kubernetes Operators Using Docker, kubectl, and k3d

Edith Puclla — Mon, 04 Mar 2024 00:00:00 UTC

If you are just starting out in the world of Kubernetes operators, like me, preparing the environment for their installation should be something we do with not much difficulty. This blog will quickly guide you in setting the minimal environment.

Kubernetes operators are invaluable for automating complex database operations, tasks that Kubernetes does not handle directly. Operators make it easy for us – they take care of essential tasks like backups and restores, which are crucial in database management.

If you want an introduction to Kubernetes Operators, I cover it in this 5-minute blog post, Exploring the Kubernetes Application Lifecycle With Percona.

Now that we know why Kubernetes Operators are essential let’s prepare our environment to install some of them. We are going to base this installation on Linux for now. Prerequisites: For this, we will need a basic understanding of Kubernetes concepts and some Linux command line skills. We also need Docker Engine to be able to use K3d at all for containerization. To test, make sure this command runs appropriately:

docker run hello-world

Installing kubectl

To manage and deploy applications on Kubernetes, we will need kubectl tool, which is included in most Kubernetes distributions. If it’s not installed, let’s do it following the official installation instructions:

To install the kubectl binary with curl on Linux, we need to download the latest release of kubectl using the command:

bash
curl -LO "https://dl.k8s.io/release/$(curl -L -s https://dl.k8s.io/release/stable.txt)/bin/linux/amd64/kubectl"

The previous binary installs kubectl in /usr/local/bin/kubectl. We need root ownership and specific permissions for secure execution.

sudo install -o root -g root -m 0755 kubectl /usr/local/bin/kubectl

To test the installation, we use the following:

kubectl version --client

kubectl version --client --output=yaml

If you receive a response like this, it indicates that you are ready to use kubectl.

Client Version: v1.29.2
Kustomize Version: v5.0.4-0.20230601165947-6ce0bf390ce3

Installing K3d

k3d is a lightweight tool that simplifies running k3s (Rancher Lab’s minimal Kubernetes distribution in Docker), enabling easy creation of single and multi-node k3s clusters for local development.

Install the current latest release of k3d with curl:

curl -s https://raw.githubusercontent.com/k3d-io/k3d/main/install.sh | bash

To test the installation, you can use the following:

k3d --help

If you see a message similar to this, you are ready to create your k3d Kubernetes clusters.

bash
https://k3d.io/
k3d is a wrapper CLI that helps you to easily create k3s clusters inside docker.
Nodes of a k3d cluster are docker containers running a k3s image.
All Nodes of a k3d cluster are part of the same docker network.
Usage:
k3d [flags]
k3d [command]
Available Commands:
cluster Manage cluster(s)
completion Generate completion scripts for [bash, zsh, fish, powershell | psh]
config Work with config file(s)

Starting the Kubernetes cluster

Let’s use K3d and create a Kubernetes cluster with three nodes. Using the flag -a, you can specify the number of nodes you want to add to the k3d cluster.

k3d cluster create database-cluster -a 3

Now, list details for our k3d cluster.

bash
k3d cluster list database-cluster

NAME SERVERS AGENTS LOADBALANCER
database-cluster 1/1 3/3 true

Now, our environment is ready to begin installing our Percona Kubernetes Operators.

Conclusion

In this tutorial, we chose k3d over Minikube due to its efficiency and speed in setting up Kubernetes clusters with multiple nodes, which are essential for effectively testing Kubernetes operators in a local environment. Although it’s possible to perform tests on a single node with both systems, k3d makes it easier to simulate a more realistic distributed environment, allowing us to utilize our resources more efficiently.

Take a look at our GitHub repository for our Percona Kubernetes Operators:

They are fully Open Source. And if you are looking for a version with a graphical interface, we have Percona Everest, our cloud-native database platform: docs.percona.com/everest

What’s Next? Let’s install our Kubernetes Operators!

Unexpected Stalled Upgrade to MySQL 8.0

Alexandre Vaniachine — Fri, 26 Jan 2024 00:00:00 UTC

A multi-tenant database is a database that serves multiple clients, or tenants, who share the same database schema but have separate data sets. One way to achieve data isolation for each client is to create a separate MySQL database for each tenant.

Some advantages of this approach are:

It allows for easy backup and restore of individual tenant data.
It simplifies the database administration and maintenance tasks, as each database can be managed independently.
Scaling is easily achieved by adding more database servers and distributing tenant databases across them.

This approach requires a large number of tables on each server. Combined with the default value of innodb_file_per_table=ON, this results in a large number of files that affects crash recovery time or Percona XtraBackup execution.

This blog post describes how to take care of a large number of files when upgrading to MySQL 8.0 in-place.

Version Selection

A steady stream of MySQL 8.0 minor releases provides improvements and refactoring of new MySQL 8.0 features. However, some of these releases introduce incompatibilities that require corresponding changes on the application side. Limiting scope of the application-side changes, we chose MySQL 8.0.25 version. This was our first step towards the major version 8.0.

Upgrade In-Place

A new MySQL 8.0 option is the upgrade in-place procedure. According to the upgrading guide:

An in-place upgrade is performed by using existing data on the server and involves the following actions:

Stopping the MySQL 5.7 server

Replacing the old binaries with MySQL 8.0 binaries

Starting the MySQL 8.0 server with the same data files.

While an in-place upgrade may not be suitable for all environments, especially those environments with many variables to consider, the upgrade should work in most cases.

As an exception, in the case of an environment with a large number of tables, the upgrade in-place may get stalled for weeks.

Below we describe how to debug and resolve such issue.

Encountering the Issue

In our test environment, we encountered a similar issue. Despite steady CPU usage, the in-place upgrade looks stalled. We monitored the upgrade progress by counting files modified in the last 24 hours. Monitoring revealed low modification rates, like

find /var/lib/mysql -name "*.ibd" -mtime -1 | wc -l
14887

that were decreasing. Although the InnoDB files continued to be modified, the decreasing modification rate was too low to be practical.

Investigating the Issue

To debug this problem we used the Linux perf tool. While the mysqld process was running during upgrade:

we collected perf data

perf record -F 10 -o mysqld.perf -p $(pidof mysqld) -- sleep 20;
[ perf record: Woken up 1 times to write data ]
[ perf record: Captured and wrote 0.256 MB mysqld.perf (1016 samples) ]

and produced the perf report

perf report --input mysqld.perf --stdio

# To display the perf.data header info, please use --header/--header-only options.
#
#
# Total Lost Samples: 0
#
# Samples: 1K of event 'cpu-clock'
# Event count (approx.): 101600000000
#
# Overhead Command Shared Object Symbol
# ........ ....... .................. ................................
#
 34.55% mysqld libc-2.17.so [.] __sched_yield
 14.86% mysqld [kernel.kallsyms] [k] __raw_spin_unlock_irq
 11.32% mysqld [kernel.kallsyms] [k] system_call_after_swapgs
 11.32% mysqld mysqld [.] Fil_shard::reserve_open_slot
 ...

To find out why mysqld process stuck in the Fil_shard::reserve_open_slot call, we checked the Percona Server source code that shows:

the function code

/** Wait for an empty slot to reserve for opening a file.
@return true on success. */
bool Fil_shard::reserve_open_slot(size_t shard_id) {
 size_t expected = EMPTY_OPEN_SLOT;

 return s_open_slot.compare_exchange_weak(expected, shard_id);
}

and the corresponding comments

The data structure (Fil_shard) that keeps track of the tablespace ID to
fil_space_t* mapping are hashed on the tablespace ID. The tablespace name to
fil_space_t* mapping is stored in the same shard. A shard tracks the flushing
and open state of a file. When we run out open file handles, we use a ticketing
system to serialize the file open, see Fil_shard::reserve_open_slot() and
Fil_shard::release_open_slot().

Apparently, the stalled upgrade process hit the open files limit, given the large number of files in our environment.

Resolving the Issue

To prevent the mysqld upgrade process from running out of open file handles, we followed Percona guidance for setting open files limit

Counted files as

find /var/lib/mysql/ -name "*.ibd" | wc -l
324780

and added another 1000 to this number for other miscellaneous open file needs.

Increased the innodb_open_files limit in two places:

added a corresponding line to configuration file /etc/my.cnf like

innodb_open_files = 325780

added a corresponding line to the systemd configuration file such as /etc/systemd/system/mysqld.service.d/override.conf like

[Service]
LimitNOFILE = 325780

With these adjustments our upgrade completed processing of a terabyte of data in just a few hours. To provide more visibility into the upgrade process we also increased the default level of error log verbosity by adding another line to the /etc/my.cnf file:

log_error_verbosity = 3

Increased verbosity enabled progress monitoring in the mysql error log during upgrade, like:

2023-10-28T00:27:09.331924Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-15) starting as process 16034
...
2023-10-28T00:27:09.353871Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-10-28T00:27:09.353986Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
...
2023-10-28T00:27:19.412572Z 1 [Note] [MY-012206] [InnoDB] Found 324780 '.ibd' and 0 undo files
2023-10-28T00:27:19.412757Z 1 [Note] [MY-012207] [InnoDB] Using 17 threads to scan 324780 tablespace files
2023-10-28T00:27:28.764032Z 0 [Note] [MY-012200] [InnoDB] Thread# 0 - Checked 15615/20298 files
...
2023-10-28T00:27:31.718051Z 0 [Note] [MY-012201] [InnoDB] Checked 20298 files
2023-10-28T00:27:31.718440Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 324780 files.
...
2023-10-28T00:27:48.821432Z 1 [Note] [MY-012922] [InnoDB] Waiting for purge to start
2023-10-28T00:27:48.878058Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-28T00:27:48.885203Z 1 [Note] [MY-011088] [Server] Data dictionary initializing version '80023'.
2023-10-28T00:27:49.187508Z 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade
...
2023-10-28T01:57:55.312683Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
...
2023-10-28T01:59:15.187709Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80025' started.
...
2023-10-28T03:01:09.880932Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80025' completed.
...
2023-10-28T03:01:13.905459Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.25-15' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 15, Revision a558ec2.

Discussion

While we were contemplating if this is a feature or a bug, MySQL release 8.0.28 refactored the related innodb_open_files code. Further details are provided in the corresponding open source commit WL#14591 InnoDB: Make system variable innodb_open_files dynamic:

- The `innodb_open_files` system variable can now be set with a dynamic SQL procedure `innodb_set_open_files_limit(N)`. If the new value is too low, an error is returned to client with the minimum value presented. If the value is out of bounds or of incorrect type, it will be reported as error also.
- `Fil_system::set_open_files_limit` was added to allow changes to the global opened files limit. The `Fil_system::m_max_n_open` is atomic now and extracted to a separate class `fil::detail::Open_files_limit`, instantiated as `Fil_system::m_open_files_limit`.
...
- `Fil_shard::reserve_open_slot`, Fil_shard::release_open_slot and static Fil_shard::s_open_slot were removed. Now we have CAS-based system of assuring the opened files will not exceed the limit set.

Thus, the new MySQL 8.0.28 feature – dynamic innodb_open_files variable – eliminated the need for open files limit adjustments in preparation for MySQL 8.0 upgrade.

Conclusions

Lessons learned:

Prepare for MySQL 8.0 upgrade in-place by taking a backup of the data directory.
Take advantage of the Percona Server open source code.
Follow guidance and advice posted in Percona blogs.

The content in this blog is provided in good faith by members of the open source community. Percona has not edited or tested the technical content. Views expressed are the authors’ own. When using the advice from this or any other online resource test ideas before applying them to your production systems, and always secure a working back up.

Our Top Picks from the Kubernetes 1.29 Release

Sergey Pronin — Fri, 12 Jan 2024 00:00:00 UTC

The latest Kubernetes version, 1.29, was released on December 13th 2023. Inspired by the Mandala and symbolizing universal perfection, it concludes the 2023 release calendar. This version comes with various exciting improvements, many of which will be helpful for users who run databases on Kubernetes.

Figure 1 - Mandala created in Excalidraw, not perfectly symmetrical.

Here, we highlight this latest release’s four key features and improvements. Let’s take a look at them together.

In-Place Update of Pod Resources

This alpha feature allows users to change requests and limits for containers without restarting. It simplifies scaling by a lot and opens new opportunities for auto scaling tools like HPA, VP, and Kubernetes Event-driven Autoscaling (KEDA). It removes the barriers of scaling the applications that were not easy to restart.

When resource resizing is not possible in-place, there are clear strategies for users and controllers (like StatefulSets, JobController, etc.) to handle the situation effectively.

It was first introduced in 1.27 but moved back to alpha as it requires additional architectural changes. It also has performance improvements and comes with Windows containers support. Read more about this in its Kubernetes Enhancement Proposals (KEP) and the issue #1287 created to add this feature.

Kubernetes VolumeAttributesClass ModifyVolume

The Kubernetes v1.29 release introduces an alpha feature enabling modification of volume attributes, like IOPS and throughput, by altering the volumeAttributesClassName in a PersistentVolumeClaim (PVC). This simplifies volume management by allowing direct updates within Kubernetes, avoiding the need for external provider API management. Previously, users had to create a new StorageClass resource and migrate to a new PVC; now, changes can be made directly in the existing PVC.

Discover further details in the KEP and issue #1287, which was established for the inclusion of this feature.

ReadWriteOncePod PersistentVolume Access Mode

Kubernetes offers access modes for Persistent Volumes (PVs) and Persistent Volume Claims (PVCs), including ReadWriteOnce, ReadOnlyMany, and ReadWriteMany. In particular, ReadWriteOnce restricts volume access to a single node, enabling multiple pods on that node to read from and write to the same volume concurrently. This setup ensures exclusive volume access on a per-node basis while allowing shared volume usage within the node. However, this introduces a potential issue, especially for applications that require exclusive access by a single pod. In this release, the ReadWriteOncePod access mode for PersistentVolumeClaims has become stable. Now that it is stable, a PVC can be configured to be mounted by a single Pod exclusively.

Here are the Kubernetes Enhancement Proposal (KEP) and issue #2485 that led to the development of this feature.

Make Kubernetes aware of the LoadBalancer behavior

kube-proxy’s handling of LoadBalancer Service External IPs is set to change. Traditional methods, such as IPVS and iptables, bind these IPs to nodes, optimizing traffic but causing issues with certain cloud providers and bypassing key Load Balancer features.

There are numerous problems with existing behavior:

Some cloud providers (Scaleway, Tencent Cloud, …) are using the LB’s external IP (or a private IP) as source IP when sending packets to the cluster. This is a problem in the ipvs mode of kube-proxy since the IP is bounded to an interface, and healthchecks from the LB is never coming back.
Some cloud providers (DigitalOcean, Scaleway, …) have features at the LB level (TLS termination, PROXY protocol, …). Bypassing the LB means missing these features when the packet arrives at the service (leading to protocol errors).

The solution would be to add a new field in the loadBalancer field of a Service’s status, like ipMode. This new field will be used by kube-proxy in order to not bind the Load Balancer’s External IP to the node (in both IPVS and iptables mode). The value VIP would be the default one (if not set, for instance), keeping the current behavior. The value Proxy would be used to disable the shortcut. This change allows more flexible handling of External IPs, maintaining current behavior as the default and offering an alternative to avoid these issues.

Read more about this in its Kubernetes Enhancement Proposals (KEP)

If you are interested in learning about databases on Kubernetes, you can start by running MySQL in Kubernetes. Explore the solutions, and weigh the pros and cons. Also, discover our predictions for Cloud Native technologies for this year.

Data on Kubernetes Community initiatives: Automated storage scaling

Sergey Pronin — Wed, 10 Jan 2024 00:00:00 UTC

In the world of Kubernetes, where everything evolves quickly. Automated storage scaling stands out as a critical challenge. Members of the Data on Kubernetes Community have proposed a solution to address this issue for Kubernetes operators.

If, like me, this is your first time hearing about Automated storage scaling, this will help you understand it better:

Storage scaling in Kubernetes Operators refers to the ability of an application running on Kubernetes to adjust its storage capacity automatically based on demand. In other words, it is about ensuring that an application has the right amount of storage available at any given time, optimizing for performance, cost, and operational efficiency, and doing this as automatically as possible.

As databases grow increasingly integral, the absence of unified solutions for storage scaling is becoming more evident. Let’s explore some existing solutions and their limitations:

pvc-autoresizer

This project detects and scales PersistentVolumeClaims (PVCs) when the free amount of storage is below the threshold. pvc-autoresizer It is and active open source project on GitHub.

There are certain downsides:

Works with PVCs only. It does not work with StatefulSet and does not have integration with Kubernetes Operator.
It requires Prometheus stack to be deployed.

Percona wrote a blog post about pvc-autoresizer to automate storage scaling for MongoDB clusters on Kubernetes.

EBS params controller

This controller provides a way to control IOPS and throughput parameters for EBS volumes provisioned by EBS CSI Driver with annotations on corresponding PersistentVolumeClaim objects in Kubernetes. It also sets some annotations on PVCs backed by EBS CSI Driver representing current parameters and last modification status and timestamps.

Find more about EBS params controller on GitHub.

Kubernetes Volume Autoscaler

This automatically increases the size of a Persistent Volume Claim (PVC) in Kubernetes when it is nearing full (either on space OR inode usage). It is a similar solution to pvc-autoresizer. Check out more about Kubernetes Volume Autoscaler

Kubernetes Event-driven Autoscaling(KEDA)

KEDA performs horizontal scaling for various resources in k8s, including custom resources. The metric tracking component is already figured out, but unfortunately, it does not work with vertical scaling or storage scaling yet. We opened an issue in GitHub to start the discussion.

As you can see, there are some limitations to performing Automated storage scaling, and to address this gap, the Data on Kubernetes community wants to develop a solution that solves practical problems and contributes to the open source community.

We’re tackling the significant challenge of unexpected disk usage alerts and potential system shutdowns due to insufficient volume space, a common issue in Kubernetes-based databases.

Possible Solutions

The following possible solutions were proposed:

Operators must be capable of changing the storage size when Custom Resource is changed.
Operators must create resources following certain standards, like applying annotations with indications of which fields should be changed
3rd party component (Scaler) will take care of monitoring the storage consumption and changing the field in the CR of the DB

Our goal as a community is to develop a fully automated solution to prevent these inconveniences and failures.

Final Thoughts

Once a new solution is validated and proven functional, it will benefit many communities, enabling them to integrate it with their operators. Additionally, it will present an excellent opportunity for Percona to incorporate it into our Operators, enhancing efficiency and facilitating automated storage scaling.

We invite those interested, especially in this particular project, to join us. This is an opportunity to be at the forefront of shaping the automated scaling solutions in Kubernetes. You can join the Data on Kubernetes community on Slack, specifically on the #SIG-Operator.

Are you interested in understanding Storage Autoscaling in databases? Explore our detailed example of Storage Autoscaling using the Percona Operator for MongoDB. For questions or discussions, feel free to join our experts on the Percona Community Forum

Volunteering as a Program Committee Member for Data on Kubernetes Day Europe 2024

Edith Puclla — Wed, 10 Jan 2024 00:00:00 UTC

The Data on Kubernetes Day Europe 2024 Program Committee is a group of professionals and experts responsible for organizing the Data on Kubernetes Day Europe 2024 content for the upcoming co-located events at Kubecon in Paris on 19 March.

As Data on Kubernetes community members, Sergey Pronin (Group Product Manager at @Percona) and I (Tech Evangelist) volunteered to evaluate proposal topics submitted for the event through the Sessionize platform. Not only us but also many other members of the Data on Kubernetes community participated as volunteers.

Community members who participate in this Program Committee evaluate proposals for talks, workshops, and other sessions submitted by potential speakers. This involves identifying each submission’s relevance, quality, and originality and being completely transparent and honest when reviewing a set of talks for the Data On Kubernetes Community co-located event.

Being a program committee means adhering to guidelines and following the Linux Foundation’s code of conduct.

Be professional and courteous.
Even express feedback constructively, not destructively.
Be considerate when choosing communication channels

After all program committee members completed their evaluations, the Data on Kubernetes Day Co-located Events Europe 2024 schedule was announced.

Look at this promising agenda:

We feel great to be a part of these efforts and to contribute to something significant by making it a reality at an in-person event. Thanks to the Linux Foundation for recognizing our efforts as Program Committee Members and for considering Percona, an active member of the DoK community.

In recognition of this support, we earned a badge from The Linux Foundation.

If you want to know more initiatives Percona have in the DoK community, read Data on Kubernetes Community initiatives: Automated storage scaling.

If you have any questions, remember to visit our Percona Community Forum.

Percona Monitoring and Management 2.41 preview release

Ondrej Patocka — Wed, 06 Dec 2023 00:00:00 UTC

Percona Monitoring and Management 2.41 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.41 is now available as a preview release.

To see the full list of changes, check out the PMM 2.41 Release Notes

PMM server Docker installation

docker tag:

perconalab/pmm-server:2.41.0-rc

PMM client package installation

Download the latest pmm2-client release candidate tarball for 2.41.
To install pmm2-client package, enable testing repository via Percona-release:

percona-release enable percona testing

Install pmm2-client package for your OS via Package Manager.

OVA

PMM2-Server-2.41.0.ova file

AMI

Run PMM Server hosted at AWS Marketplace instructions

ami-0a04085f4c721e913

The Importance of Anti-Affinity in Kubernetes

Edith Puclla — Thu, 30 Nov 2023 00:00:00 UTC

Last week, I embarked on the task of deploying our Percona Operator for MongoDB in Kubernetes. After completing the deployment process, I noticed that the status of the Custom Resource Definition for Percona Server for MongoDB was still displaying as ‘initializing’ and two of our Pods remained in a Pending state.

bash
edithpuclla@Ediths-MBP % kubectl get perconaservermongodbs.psmdb.percona.com -n mongodb
NAME ENDPOINT STATUS AGE
my-db-psmdb-db my-db-psmdb-db-mongos.mongodb.svc.cluster.local initializing 4m58s

bash
edithpuclla@Ediths-MBP % kubectl get pods -n mongodb
NAME READY STATUS RESTARTS AGE
my-db-psmdb-db-cfg-0 2/2 Running 0 109m
my-db-psmdb-db-cfg-1 2/2 Running 0 108m
my-db-psmdb-db-cfg-2 0/2 Pending 0 107m
my-db-psmdb-db-mongos-0 1/1 Running 0 106m
my-db-psmdb-db-mongos-1 1/1 Running 0 106m
my-db-psmdb-db-rs0-0 2/2 Running 0 109m
my-db-psmdb-db-rs0-1 2/2 Running 0 108m
my-db-psmdb-db-rs0-2 0/2 Pending 0 107m
my-op-psmdb-operator-77b75bbc7c-qd9ls 1/1 Running 0 118m

Upon further inspection of the pod in pending status, I discovered a clear indicator of the error:

bash
kubectl describe pod my-db-psmdb-db-cfg-2 -n mongodb
Events:
 Type Reason Age From Message
 ---- ------ ---- ---- -------
 Normal NotTriggerScaleUp 3m53s (x62 over 13m) cluster-autoscaler pod didn't trigger scale-up:
 Warning FailedScheduling 3m27s (x4 over 13m) default-scheduler 0/2 nodes are available: 2 node(s) didn't match pod anti-affinity rules. preemption: 0/2 nodes are available: 2 No preemption victims found for incoming pod..

I took a closer look at the YAML configuration of our CRD in the Replsets section, particularly drawn to the Affinity subsection.

kubectl describe perconaservermongodbs.psmdb.percona.com my-db-psmdb-db -n mongodb

Here’s what I discovered:

Affinity and Anti-Affinity are key parts of the scheduling process in Kubernetes, and both focus on ensuring that Pods are correctly assigned to Nodes in the cluster. You can configure a Pod to run on a specific node or group of nodes. There are several ways to achieve this, nodeSelector is the simplest way to constrain Pods to nodes with specific labels. Affinity and anti-affinity expand the types of constraints you can define and give you more flexibility.

Let’s explore what it means to specify anti-affinity rules for the ReplicaSets.

The key kubernetes.io/hostname is a well-known label in Kubernetes that is automatically assigned to each node in the cluster. It usually holds the value of the node’s hostname. When used as a topology key in anti-affinity rules, it implies that the rule should consider the hostname of the nodes. In simpler terms, it’s telling Kubernetes to not to schedule the pods of this ReplicaSet on the same physical or virtual host (node).

If we review our cluster, it has two nodes for installing the Percona Operator for MongoDB.

bash
edithpuclla@Ediths-MBP ~ % kubectl get nodes
NAME STATUS ROLES AGE VERSION
gke-mongo-operator-test-default-pool-7c118de9-b9vc Ready  68m v1.27.4-gke.900
gke-mongo-operator-test-default-pool-7c118de9-ts16 Ready  68m v1.27.4-gke.900

In the context of databases like MongoDB, High availability is often achieved through replication, ensuring that the database can continue to operate even if one or more nodes fail. Within a MongoDB Replica Set, there are multiple copies of the data, and these copies are hosted on different Replica Set members. The default HA MongoDB topology is a 3-member Replica Set. Percona Operator for MongoDB deploys MongoDB in the same topology by default. With anti-affinity set to kubernetes.io/hostname, that means at least 3 Kubernetes worker nodes are needed to deploy MongoDB.

We created the minimum three nodes that the Percona Operator for MongoDB needs. We see that we don’t have the error with Antiaffinity in the Pods because each Pod was located appropriately in different nodes. Now our operator and our database were deployed correctly.

bash
edithpuclla@Ediths-MBP ~ % kubectl get nodes
NAME STATUS ROLES AGE VERSION
gke-mongo-operator-test-default-pool-e4e024a8-1dj3 Ready  76s v1.27.4-gke.900
gke-mongo-operator-test-default-pool-e4e024a8-d6j2 Ready  74s v1.27.4-gke.900
gke-mongo-operator-test-default-pool-e4e024a8-jkkr Ready  76s v1.27.4-gke.900

If we list all the resources in our namespace, we can see that all pods are running properly and all the resources have been created.

bash
edithpuclla@Ediths-MBP ~ % kubectl get all -n mongodb
NAME READY STATUS RESTARTS AGE
pod/my-db-psmdb-db-cfg-0 2/2 Running 0 4m40s
pod/my-db-psmdb-db-cfg-1 2/2 Running 0 4m2s
pod/my-db-psmdb-db-cfg-2 2/2 Running 0 3m20s
pod/my-db-psmdb-db-mongos-0 1/1 Running 0 2m56s
pod/my-db-psmdb-db-mongos-1 1/1 Running 0 2m39s
pod/my-db-psmdb-db-rs0-0 2/2 Running 0 4m39s
pod/my-db-psmdb-db-rs0-1 2/2 Running 0 3m59s
pod/my-db-psmdb-db-rs0-2 2/2 Running 0 3m28s
pod/my-op-psmdb-operator-77b75bbc7c-q2rqh 1/1 Running 0 6m47s

NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/my-db-psmdb-db-cfg ClusterIP None  27017/TCP 4m40s
service/my-db-psmdb-db-mongos ClusterIP 10.72.17.115  27017/TCP 2m56s
service/my-db-psmdb-db-rs0 ClusterIP None  27017/TCP 4m39s

NAME READY UP-TO-DATE AVAILABLE AGE
deployment.apps/my-op-psmdb-operator 1/1 1 1 6m47s

NAME DESIRED CURRENT READY AGE
replicaset.apps/my-op-psmdb-operator-77b75bbc7c 1 1 1 6m47s

NAME READY AGE
statefulset.apps/my-db-psmdb-db-cfg 3/3 4m41s
statefulset.apps/my-db-psmdb-db-mongos 2/2 2m58s
statefulset.apps/my-db-psmdb-db-rs0 3/3 4m40s

In conclusion, affinity and anti-affinity in Kubernetes are tools for strategically placing pods in a cluster to optimize factors such as performance, availability, and compliance, which are critical for the smooth and efficient operation of containerized applications, also setting up anti-affinity rules like failure-domain.beta.kubernetes.io/zone in Kubernetes is a key strategy for keeping clusters running, especially in production environments. This approach spreads pods across different availability zones, which means if one zone has an issue, the others can keep the system running. It’s a smart way to ensure your cluster can handle unexpected outages, making it a popular choice for those who need their Kubernetes setups to be reliable and available at all times.

Learn more about our Percona Operator for MongoDB, and if you have questions or comments, you can write to us on our Percona Community Forum.

Data On Kubernetes

Edith Puclla — Fri, 10 Nov 2023 00:00:00 UTC

If you’ve attended one of the Kubecon talks or related events, you’ve likely encountered the phrase Data on Kubernetes. To understand what this means, let’s explore some fundamental concepts related to Kubernetes, workload, stateless, and stateful applications.

Kubernetes, workload, stateless and stateful applications

Kubernetes is a container orchestration tool that has already become an industry standard. When we talk about “container orchestration”, we are referring to the automated management and coordination of containers using Kubernetes.

Now, let’s explore what a workload is in the context of Kubernetes. A workload represents an application running on Kubernetes. An application may consist of a single component or multiple components working together. These components are packaged into containers operating within a group of Pods.

There are two types of workloads, depending on the nature of the application: Stateless and Stateful.

In a stateless application, the client session data is not stored on the server. This is because the application doesn’t need to retain past interactions to function. However, in a stateful application, storing client session data is essential as it is necessary for subsequent interactions within the application.

Now, we are already familiar with Kubernetes, workloads, stateless and stateful applications, and we also understand that Pods are responsible for managing these types of workloads.

Built-in Workload Resources in Kubernetes

In a Kubernetes cluster, we can have thousands of Pods, and we don’t need to directly manage them individually. Instead, we utilize workload resources to manage a group of Pods and choose what workload resource depends on the type of workload we are dealing with, Stateless or Stateful.

For example, if we have stateless applications, we can use the Deployment and ReplicaSet resources, which are well-suited for this type of workflow. On the other hand, the StatefulSet resource allows us to run Pods that need to maintain state.

Data on Kubernetes refers to the management and storage of data within the Kubernetes ecosystem. Kubernetes provides a robust framework for handling data, making it a versatile platform for both stateless and stateful applications while ensuring data durability, availability, and security.

The challenge

Kubernetes was initially designed to run stateless applications. However, the number of stateful applications running on Kubernetes has increased significantly. There are many challenges when it comes to running applications with state in Kubernetes, such as data management strategies, volume persistence, and others. According to the 2021 Data on Kubernetes report of more than 500 executives and technology leaders, 90% believe it is ready for stateful workloads, and a large majority (70%) are running them in production, with databases topping the list. This gives rise to initiatives aimed at standardizing the requirements for managing stateful applications on Kubernetes.

This is how Data on the Kubernetes Community emerges. The Data on Kubernetes (DoKc) community was established in spring 2020. It is an openly governed group of curious and experienced practitioners, drawing inspiration from the Cloud Native Computing Foundation (CNCF) and the Apache Software Foundation. They aim to help create and improve techniques for using Kubernetes with data.

There are several organizations that are part of the Data on Kubernetes community, and Percona is part of it as well. Percona is adding efforts in DoKC Operator SIG(Special Interest Groups), where we discuss gaps in information around K8s operators for the industry-at-large & co-creates projects to fill the gap. Watch the Kubernetes Database Operator Landscape panel discussion to learn more about the community efforts in Data on Kubernetes.

Conclusion

Data on Kubernetes is a crucial concept in the Kubernetes ecosystem. Kubernetes was initially designed for the stateless application, now faces the challenge of managing stateful workloads, and is more notable in databases. The Data on Kubernetes (DoKc) community has emerged to address these challenges and standardize the management of stateful applications, drawing inspiration from industry standards like CNCF and Apache Software Foundation.

If you want to be part of them, you are welcome to join DoKC. Also, check this outstanding Kubernetes Database Operators Landscape, where members of DoKC talk about operations for data workloads on Kubernetes.

Exploring Kubernetes Operators

Edith Puclla — Fri, 03 Nov 2023 00:00:00 UTC

The concept of Kubernetes Operators was introduced around 2016 by the CoreOS Linuxdevelopment team. They were in search of a solution to improve automated container management within Kubernetes, primarily with the goal of incorporating operational expertise directly into the software.

According to the Cloud Native Computing Foundation, “Operators are software extensions that use custom resources to manage applications and their components”.

Kubernetes is designed for automation, offering essential automation features. It can automatically deploy and manage workloads. The definition provided by CNCF regarding operators, as mentioned above, highlights the flexibility we have to customize the automation capabilities made possible by Kubernetes Operators using custom resources.

In Kubernetes, certain applications require manual attention as Kubernetes can not autonomously manage them. This is especially the case with databases. This is where Operators come into play. Databases are complex entities that also have complex database operations and features that Kubernetes itself may not inherently understand. While deploying a database in Kubernetes manually isn’t a problem, the true strength of operators shines during Day 2 operations, which include tasks such as backups, failover, and scaling. Operators automate these manual tasks for applications within Kubernetes.

The main challenge that arises when implementing containerized databases is the problem of data persistence. This is the challenge for containers in general, and it is more critical in the context of databases despite ongoing advances in container maturity. Kubernetes operators are designed to address this gap. While it is possible to use Kubernetes resources like Persistent Volume Claims (PVCs) without operators, operators simplify the process by providing a higher level of abstraction and automation.

It is possible to create new operators using the Kubernetes operator pattern concept. This allows you to extend cluster behavior without modifying the Kubernetes code by linking controllers to one or more custom resources. These Operators use and extend the Kubernetes API, a key component within the Kubernetes architecture, with the essential concepts for users to interact with the Kubernetes cluster. They create custom resources to add new functionality according to the needs of an application to be flexible and scalable. This is how we automate workloads using Kubernetes Operators.

One of the primary benefits of operators is the automation of repetitive tasks that are often managed by human operators, eliminating errors in application lifecycle management.

Conclusion

In this article, we explored an overview of what Kubernetes Operators are; we saw why they are necessary and the benefits of using them. I hope you have gained a general understanding of why Kubernetes Operators are valuable.

If you want to know more about Kubernetes operators designed specifically for databases, you can visit the Percona website, where you will find Kubernetes operators created by Percona for MongoDB, PostgreSQL, and MySQL.

Are you considering creating your own operator? Start by using the Operator-SDK. Additionally, you can watch Sergey Pronin’s (Group Product Manager At Percona) talk at the DoK Community about Migrating MongoDB to Kubernetes, where he discusses the reasons why Percona created an Operator for MongoDB.

Kubernetes Community Days UK: Keynote Cilium and eBPF

Edith Puclla — Tue, 24 Oct 2023 00:00:00 UTC

This week, at Kubernetes Community Days UK in London. Liz Rice, Chief Open Source Officer at Isovalent, delivered a keynote on Cilium, eBPF, and the new feature of Cilium: Mutual Authentication.

Figure 1. Liz Rice Keynote KCD UK, London. Tuesday 17, 2023

Cilium is an eBPF-powered open source, cloud native solution for delivering, securing, and observing network connectivity between workloads.

eBPF is a technology that allows us to create modules to modify the behavior of the Linux kernel, but why would we want to change the Linux kernel?

Some use cases for observability, security, and networking require tracking and monitoring our application, but we don’t want to constantly modify our application with these changes. It’s better to add a program that can observe the behavior of our application from the kernel.

But changing the Linux kernel can be, well, hard.

Figure 2. Adding features to the kernel (cartoon by Vadim Shchekoldin, Isovalent)

However, eBPF enables you to modify the kernel’s behavior without directly altering the kernel itself. It might sound unconventional, but eBPF makes this possible through the creation of programs for the Linux kernel. The Linux kernel accepts eBPF programs that can be loaded and unloaded as needed.

Figure 3. Adding kernel features with eBPF (cartoon by Vadim Shchekoldin, Isovalent)

To ensure that these eBPF programs written by us are secure, there is a mechanism in place that allows these programs to be verified safe for execution. This can be seen in eBPF verification and security. You don’t have to restart the kernel to deploy or remove eBPF applications, which makes eBPF one of the technology tools of the moment.

Liz also announced that Cilium recently graduated from CNCF. This means Cilium is considered stable and has been successfully used in production environments.

Figure 4. CNCF Project Maturity Levels

After understanding what eBPF is, let’s move on to the actual topic of Liz’s keynote. She spoke about Mutual Authentication with Cilium.

Mutual Authentication with Cilium was the last significant feature missing from Cilium Service Mesh. It’s a somewhat more complex topic related to mTLS (Mutual transport layer security).

mTLS is a mechanism that ensures the authenticity, integrity, and confidentiality of data exchanged between two entities in the network.

In Cilium 1.14, one of the most significant releases, Cilium introduces support for a feature that many developers have requested: mutual authentication. This feature simplifies the process of achieving mutual authentication between two workloads. It now only requires adding two lines of code to the YAML in the Cilium Network Policy to authenticate communication between two workloads.

Slightly more complex, isn’t it? Let’s explore Mutual Authentication with Cilium in a second blog post very soon. We’ll also examine how this is related to Kubernetes and why it matters when running databases on Kubernetes.

Check what the other Graduated and Incubating Projects are at CNCF, and don´t forget to subscribe to our Percona Community Blog to read more about Open Source, CNCF Projects, and Database.

Percona Monitoring and Management 2.40 preview release

Taras Kozub — Tue, 03 Oct 2023 00:00:00 UTC

Percona Monitoring and Management 2.40 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.40 is now available as a preview release.

To see the full list of changes, check out the PMM 2.40 Release Notes

PMM server Docker installation

docker tag:

perconalab/pmm-server:2.40.0-rc

PMM client package installation

Download the latest pmm2-client release candidate tarball for 2.40.
To install pmm2-client package, enable testing repository via Percona-release:

percona-release enable percona testing

Install pmm2-client package for your OS via Package Manager.

OVA

PMM2-Server-2.40.0.ova file

AMI

Run PMM Server hosted at AWS Marketplace instructions

ami-09895e9b605f14cbc

Dolphie, your real-time MySQL monitoring assistant

Charles Thompson — Tue, 22 Aug 2023 00:00:00 UTC

For as long as I can remember, Innotop has been the go-to terminal tool for real-time MySQL monitoring. It is an invaluable addition to any DBA’s toolkit, but unfortunately, it’s not really actively maintained these days, except for addressing critical issues, and it hasn’t kept pace with the evolving capabilities of modern terminals. With no viable alternatives except for InnotopGo, which is also no longer actively maintained and limited to MySQL 8 (while many still use 5.7), I decided to build my own in Python.

I call it, Dolphie

Initially, I relied on Python’s Rich package for the user interface. However, I recently stumbled upon Textual a few months ago, and it piqued my interest. It’s a framework that extends the capabilities of Rich, opening up a world of possibilities in the terminal. After experimenting with it for a few days, it inspired me to redevelop Dolphie with it, and I’ve been thoroughly pleased with the results. It has allowed me to showcase many of the features that will be displayed in this blog post!

Getting started

When you first start Dolphie, you’ll be greeted with a dashboard displaying various important MySQL metrics, along with a sparkline below it to measure the QPS (Queries per second) + process list. There are multiple ways to manipulate the process list, such as changing how it sorts, filtering by user/host/query text/database/time, killing threads, and much more.

There are currently four panels that can be toggled interchangeably for display:

Dashboard
Process list
Replication/Replicas
Graph Metrics

A big perk of transitioning to Textual is the integration of graphs. It’s as if I’ve incorporated a mini-PMM (Percona Monitoring and Management) right into Dolphie! The switches you see can be toggled on and off to display or hide their corresponding metrics on the graph.

Buffer Pool Requests Graph + Replication Panel

Checkpoint Graph

Redo Logs Graph

How are your redo logs performing? Dolphie shows you how much data is being written per second, the active count of redo logs (MySQL 8 only), and how much data is being written to it per hour (inspired by this blog post)

DML Graph

Thread data

Dolphie lets you display a thread’s information with an explanation of its query along + transaction history

Kill threads

Dolphie lets you terminate threads using a selected option. Notice how it autocompletes the input for you. This is a feature across the board. It will autocomplete any input that it can

Quick switch host

After using Dolphie extensively myself, I realized the need to simplify host switching. I found myself restarting it frequently just to change the host. This feature saves all the hosts you’ve connected to, allowing for autocomplete when you want to switch

Error log

In MySQL 8, I was delighted to see that the error log was in performance_schema. Of course, I had to support it! It has switches to toggle on/off event types and search functionality

Errant transactions

The Replicas panel will let you know if your replicas have any errant transactions and what they are

These are just some of the features that Dolphie has. There are many more that I haven’t covered, which you can discover for yourself and try out!

If you’d like to try Dolphie, it’s just a pip away:

pip install dolphie

I’m open to feedback and suggestions so don’t be a stranger :) If you’d like to contribute to the project, I’d be delighted to have you!

You can find Dolphie on its GitHub

Percona Monitoring and Management 2.38 preview release

Taras Kozub — Fri, 30 Jun 2023 00:00:00 UTC

Percona Monitoring and Management 2.38 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.38 is now available as a preview release.

To see the full list of changes, check out the PMM 2.38 Release Notes

PMM server Docker installation

docker tag:

perconalab/pmm-server:2.38.0-rc

Important: To use the DBaaS functionality during the PMM preview release, add the following environment variable when starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.38.0-rc

PMM client package installation

Download the latest pmm2-client release candidate tarball for 2.38.
To install pmm2-client package, enable testing repository via Percona-release:

percona-release enable percona testing

Install pmm2-client package for your OS via Package Manager.

OVA

PMM2-Server-2.38.0.ova file

AMI

Run PMM Server hosted at AWS Marketplace instructions

ami-09895e9b605f14cbc

Data on Kubernetes Meetup May 23

Edith Puclla — Thu, 01 Jun 2023 00:00:00 UTC

Percona has started to participate in Data on Kubernetes (DoK) meetings about Kubernetes Operators. These meetings are an initiative of DoK meetups that spotlight DoK case studies. In this blog post series, I will summarize the topics covered in each meeting.

On May 23, very interesting topics were discussed on the agenda. Let’s begin to summarize it.

We start with a new project proposal, which is called: Distributed Systems Operator Interface (DSOI). It is proposed by Adheip Singh from DataInfra, Nitish Tiwari from Parseable, and Itamar Marom from AppsFlyer.

This project is a set of best practices for building Kubernetes operators for distributed systems. The spec defines standard practices that can help define custom resources (CR). It consists of Kubernetes-native CRDs and specs and is not bound to any specific application. There are already two operators built using this set of practices:

If you want to contribute, send proposals, join datainfra-workspace or raise bugs in the GitHub repository of DSOI.

As the second item on the agenda, we have an update about the DoK Operator SIG Project Proposal - Security & Hardening Guide. This project is proposed by Robert Hodges, Altinity Inc.

This project is a guide to establishing a baseline for secure data management on Kubernetes by fortifying the database operators. The guide aims to identify the typical attack surfaces that exist for databases running on Kubernetes. It will establish a collection of best practices for enhancing their security through the utilization of operators.

Robert mentioned that he connected to TAG Security, which in turn led to a link to BadRobot, which is a scanner that checks operators for excessive privileges. Also, Robert presented to DoK Bay Area last week to introduce the problem of operator security.

For the Operator security & hardening guide, we can raise issues on sig-operator. They are currently seeking volunteers and contributors for their project; Find operator-security-hardening project on GitHub, or feel free to write Robert Hodges rhodges@altinity.com.

Finally, we have an update of the Operator Feature Matrix (OFM)

The Operator Feature Matrix (OFM) is a project from the Data on Kubernetes Community to create a standardized and vendor-neutral feature matrix for various Kubernetes operators that manage stateful workloads. This project is proposed by Alvaro Hernandez, and it is definitely a good project to contribute if you are looking to improve the end-user experience with the use of workloads in Kubernetes.

CloudNativePG project sent feedback to improve OFM. CloudNativePG is the Kubernetes operator that covers the full lifecycle of a highly available PostgreSQL database cluster. Planning to create a website for end-user adoption

There are other (non-Postgres) technologies, like Apache Druid, jumping on OFM. This is a work in progress.

The end of June is being considered for a 1.0 freeze, before which it is required to get as much feedback as possible. If you are interested, feedback can be as simple as opening an issue to discuss something; or sending a PR requesting improvements (or both). Feel free to do it on OFM GitHub Repo.

What experts said at Kubecon about Data on Kubernetes

Edith Puclla — Wed, 31 May 2023 00:00:00 UTC

Melissa Logan, managing director of Data on Kubernetes (DoK), led one of the best panels I’ve been to at a conference at Kubecon EU in Amsterdam about challenges with and the state of the art of running databases on Kubernetes.

This panel united the Data on Kubernetes Community Operator SIG and Kubernetes Storage SIG to discuss key features of Kubernetes database operators. Xing Yang from VMware, Sergey Pronin from Percona, and Álvaro Hernández from OnGres came together to discuss what works, what doesn’t, and where the industry is going. They also presented a feature matrix to help end users compare many database Operators.

If you are new to the topic of Kubernetes Operators, I wrote a blog post about Kubernetes Operators in a nutshell, you can read the first part of this article.

Let’s start by summarizing the challenges the panelists mentioned when running stateful applications on Kubernetes.

Some Operators have certain limitations, and there are security concerns. Database users always think about data encryption: is the data safe? What happens if the node goes down? What happens if we lose the storage?
The Operator model is very extensible and flexible, which is great, but on the other hand, there are so many Operators, and it becomes a challenge to choose the right one for our use cases.
People who are developing Operators also find challenges because every database has its native way of doing backups, but if you want to support more than one type of database, then it’s more challenging to find a generic way.

Does the framework capture what is needed for data workloads well?

There is a capability model for Operators that classify them into five levels. There is room for improvement in this model. It can be improved to build test compatibility and more objective measures of these capability levels if you look at level five, the top one for data workloads.

Security is the number one criterion that people use to evaluate Operators. How are they addressing security in Kubernetes Operators?
Users want to ensure that the Operator does not get a lot of privileges or does not interfere with other tenants in the Kubernetes cluster.
Now users are looking for more sophisticated ways with their existing security key-value storage. They can be sure they are safe.
The framework should provide ransomware protection, so when you back up your databases, you also want to have one immutable copy to provide your protection and recover from that.

Now Let’s talk about solutions

DoK started an Operator Special Interest Group (SIG), and community members have been meeting to discuss how as a group and as an industry, to collaborate to come up with solutions for some of the challenges end users face. The Operator SIG works with the Storage Technical Advisory Group (TAG), Storage SIG, and Security SIG.

According to data on Kubernetes’s (DoK) first report, 70% of responses are running workloads in production. More data workloads are running on Kubernetes, so it is essential to know what works well. Sharing knowledge and leveraging that expertise is vital at this stage.

There are things that SIG Operators detail in a document, like common patterns and features used when running databases on Kubernetes, best practices, the criteria for running a good operator, why observability is so important in the cloud-native environment and security.

The Operator Feature Matrix is a big initiative to help end users find operators based on different criteria to choose what they need. It is a project to compare different Operators. They are starting with database Operators, and one project is already defined: Postgres Operator Feature Matrix. Feel free to contribute to OFM.

New to Kubernetes Operators and databases? Check out Percona’s Operators for MySQL, PostgreSQL, and MongoDB.

Easy Way to Start Contributing to Open Source With PMM Documentation

Thu, 18 May 2023 00:00:00 UTC

If you are a user of Percona Monitoring and Management and noticed any typo or inaccurate information in its documentation, you can easily correct it yourself in the repository following detailed instructions in README.md. But if you are not experienced in open source contributions, you may still feel uneasy about following those steps. This post is for you! We will walk through the main steps with pictures and explanations.

Create a Fork

First, you need to create a fork from the main repository to your account. In the top-right corner of the page, click Fork - Create a new fork.

Build Documentation With Docker

The easiest way is to build documentation with Docker. If you don’t have it installed, download it from the Docker official website and follow the instructions. The process of installation is quick, and it is no more difficult than the installation of any other app.

Open your fork on GitHub and clone that repository to your local environment.

git clone git@github.com:{user-name}/pmm-doc.git

Change directory to pmm-doc.

cd pmm-doc

To check how our edits will look like, we need to build documentation for live previewing. Run:

docker run --rm -v $(pwd):/docs -p 8000:8000 perconalab/pmm-doc-md mkdocs serve --dev-addr=0.0.0.0:8000

Wait until you see INFO - Start detecting changes. When the documentation is ready to work with, it will be available at http://0.0.0.0:8000 in your browser, and it will reflect all changes that you make locally.

Make Changes

In a new Terminal tab, create a new branch and make your changes. Save them, create a commit, and push it to your fork.

Create a pull request to the main repository. You will also need to sign the CLA, so we could merge your changes.

You did it! Congratulations! Now wait for the feedback from the Percona team. If there is no problem with your PR, it will be merged into the main repository.

Next Steps

To make further changes, you need to keep your repository up-to-date with the upstream one. There are several ways to do it. You can find the information here. The simplest way is to do it using the GitHub interface. Just click on Sync fork and then Update branch.

After that, you will be able to update your local repository with git pull command.

If you face any problems with contributions to Percona repositories, don’t hesitate to contact us at community-team@percona.com or ask your question on the Percona Forum.

My Experience at Kubecon Europe in Amsterdam

Edith Puclla — Thu, 11 May 2023 00:00:00 UTC

Kubecon is the most significant event focused on the Kubernetes ecosystem. It takes place once a year in North America, Europe, and Asia. It is a perfect opportunity to learn from experts, meet friends, grow your network, and attend talks at a varied technical level and meetings focused on CNCF communities. This time I attended Kubecon in Amsterdam. The theme for this version of Kubecon was: community-in-bloom because we are still healing from COVID, and people are getting back to feeling comfortable participating in events.

It is not my first time attending Kubecon; this is my fourth time! What was different this time was that Percona, the company I work for, sponsored Kubecon. This means we also had a booth at the event to share what we do at Percona.

Yessss!! Percona had a booth in Kubecon, Amsterdam.

Percona is a 100% remote company, and Kubecon was the opportunity to meet part of the team I work with daily.

We started by meeting with friends in one of the most popular places in Amsterdam. I met people from different tech communities and CNCF ambassadors.

If you are in Amsterdam and don’t ride a bicycle, it is an incomplete experience. We rode a bike to the convention center and collected the event badge.

After that, there were three intense days, many activities, meetings, and sessions.

Community

I met with several members of the Docker and CNCF communities. This is the meeting of the Docker captains and various members of the Docker team. Such an amazing experience to know them in person.

And this is the group of photos that all the CNCF ambassadors had at Kubecon. There are 155 CNCF ambassadors around the world and contributors and advocates of the CNCF ecosystem.

Lightning Talks

I attended a large part of the lightning sessions, which were very inspiring. Each speaker has less than 5 minutes to explain a specific topic.

Kevin Patrick talked about the Armada as a Sandbox project in the CNCF, in which the principal goal is enabling batch processing across multiple Kubernetes clusters. Kevin made an introduction to Armada and showed the integration with Apache Airflow.

The next talk was about The CNCF Board Game Rules, where Peter O’Neill made an abstract about the world of the CNCF and imagined it as a role-playing game (RPG) board game. It was pretty nice for Peter to show us the adventure of CNCF with games.

This was one of my favorite lightning talks: [A Beginners Guide to Conference Speaking] (https://www.youtube.com/watch?v=jCz9QPrJ6Eo)with Paula Kennedy. She showed a friendly way to prepare proposals for CFPs and advice that will help us through the process.

Another good lightning session was about the Power-Aware Scheduling in Kubernetes with Yuan Chen from Apple. In this talk, Yan gave an overview of a new scheduler feature to support power-aware scheduling in Kubernetes and how it can help safely increase server hardware and data center infrastructure size and improve resource utilization and workload reliability for Kubernetes clusters.

Another of my favorites was Talking to Kubernetes with Rust with James Laverack, where he showed how to interact with Kubernetes in Rust.

Kubernetes Operators Panel Discussion

I also attended a Panel Discussion about Kubernetes Operators. In this panel discussion, Xing Yang, Melissa Logan, Sergey Pronin, and Alvaro Hernandez talked about the challenges that final users have when running data workloads in Kubernetes Operators. They also shared about the need to fulfill the process of data workloads. Check out this fantastic talk and learn more about Kubernetes Operators. A very curious and interesting fact is that most of the assistants use data workload with Kubernetes Operators

eBPF

The last talk I attended was an eBPF talk with Liz Rice (Chief Open Source Officer, Savant) In this talk, Liz shows a demo about how Cilium and its ClusterMesh feature can take care of many aspects of connectivity across multiple clusters in a cloud-agnostic way. Check this talk in CNCF YouTube Channel.

Clossign Kubecon Amsterdam

Finally, in Percona, we closed the event with a raffle to take home an Atari’; the expectations were relatively high and very fun, and many Percona lovers came to participate in the raffle.

You can check in which events Percona is going to be in the next months

Percona Monitoring and Management 2.37 preview release

Taras Kozub — Thu, 20 Apr 2023 00:00:00 UTC

Percona Monitoring and Management 2.37 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.37 is now available as a preview release.

You can find the Release Notes here

Percona Monitoring and Management server docker installation

docker tag:

perconalab/pmm-server:2.37.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variable when starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.37.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.37 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.37.0.ova

AMI

ami-013c92f3d0c727b8f

Using the JSON data type with MySQL 8 - Part II

Edith Puclla — Tue, 11 Apr 2023 00:00:00 UTC

If you read - Using the JSON data type with MySQL 8 - Part I, you will see that inserting data into MySQL of JSON type is a very common and effective practice. Now we’ll see how to do it with a Python project, using SQLAlchemy and Docker Compose, which further automates this example. You can run this example using a single command: docker-compose up

Before getting down to work, we will review some important concepts:

Percona Server for MySQL is an open source, drop-in replacement for MySQL Community that provides better performance, more scalability, and enhanced security features.
SQLAlchemy is a library that allows us to communicate between Python programs and databases.
Docker Compose is a tool for defining and running multi-container Docker applications.

Let’s start with the structure of this project:

We have a folder called app which contains the db.py file, and this is where we create the library database and establish the connection with this database.

bash
db_user = os.environ['DB_USER']
db_password = os.environ['DB_PASSWORD']

engine = create_engine(f"mysql+pymysql://{db_user}:{db_password}@db:3306/library")

In this file, we also create the class transactions. This will create the fields for the library databases with SQLAlchemy; we define the attributes, and they will be the database fields. We have four attributes: book_id, tittle, publishes, and labels. The last one (labels) of JSON data type.

bash
class transactions(base):
 __tablename__ = 'book'

 book_id = Column(Integer, primary_key=True)
 title = Column(String(50))
 publisher = Column(String(50))
 labels = Column(JSON)

 def __init__(self, book_id, title, publisher, labels):
 self.book_id = book_id
 self.title = title
 self.publisher = publisher
 self.labels = labels

base.metadata.create_all(engine)

Now let’s review the Python script called insert.py, where we use the transactions class to insert data into the database.

bash
import db
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=db.engine)
session = Session()

tr1 = db.transactions(1,'Green House', 'Joe Monter', '{"about" : {"gender": "action", "cool": true, "notes": "labeled"}}')

session.add(tr1)
session.commit()

Now let’s explore the docker-compose.yaml file, we have two services, the db and the api

bash
version: "3.8"
services:
 api:
 build: .
 container_name: api
 depends_on:
 db:
 condition: service_healthy
 db:
 image: percona/percona-server:8.0
 container_name: db
 restart: always
 environment:
 MYSQL_USER: root
 MYSQL_ROOT_PASSWORD: root
 MYSQL_DATABASE: library
 healthcheck:
 test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
 timeout: 20s
 retries: 10
 volumes:
 - my-db:/var/lib/mysql
 ports:
 - "3306:3306"
 expose:
 - "3306"

# Names for volume
volumes:
 my-db:

The db service uses the Percona Server for MySQL image (percona/percona-server:8.0) for the database and has a healthcheck that allows you to confirm when the database is started and ready to receive requests. The api service depends on the db service to start. The api service will build a Dockerfile, it does a build of the Python applications (of db.py and insert.py), so in this way, we can insert data into the database when it is ready.

It’s time to see the example in action; let’s locate it inside the json-mysql project and run docker-compose ps -d

Once this is done, we can connect to the database and query the table without needing to go inside the container with the following command:

bash
docker exec -i db mysql -uroot -proot <<< "use library;show tables;select \* from book;describe book;"

We can check the data types of our fields and the inserted data. You will also see the JSON data type “labels” data type.

bash
book_id title publisher labels
1 Green House Joe Monter "{\\"about\\" : {\\"gender\\": \\"action\\", \\"cool\\": true, \\"notes\\": \\"labeled\\"}}"
2 El camino Daniil Zotl "{\\"about\\" : {\\"gender\\": \\"documental\\", \\"cool\\": true, \\"notes\\": \\"labeled\\"}}"
3 London Bridge Mario Mesa "{\\"about\\" : {\\"gender\\": \\"drama\\", \\"cool\\": true, \\"notes\\": \\"labeled\\"}}"

bash
Field Type Null Key Default Extra
book_id int NO PRI NULL auto_increment
title varchar(50) YES NULL
publisher varchar(50) YES NULL
labels json YES NULL

Use “docker compose ps” to see your services running. In this case, we have the “db” service running, which is for the database, and we have “api” with the state “exited,” which means that the scripts to create the database and insert the data into the database “library” was created.

bash
NAME COMMAND SERVICE STATUS PORTS
api "/bin/sh -c 'bash -C…" api exited (0)
db "/docker-entrypoint.…" db running (healthy) 0.0.0.0:3306->3306/tcp, 33060/tcp

It was an example of inserting JSON data into MySQL using SQLAlchemy in Python and docker-compose for deployment.

You can find the project on GitHub. If there is any other way to make it better happy to hear it so I can improve this project.

You can explore more about Percona Server for MySQL, and if you want to see how this project start check Using the JSON data type with MySQL 8 - Part I

How to prevent unauthorized users from connecting to ProxySQL

Valentin TRAËN — Thu, 30 Mar 2023 00:00:00 UTC

ProxySQL is a great load balancer which however suffers from some shortcomings concerning the management of MySQL users. ProxySQL provides a firewall which, in my case, is not complete enough to properly manage users and secure their access. Indeed, this firewall does not accept subnets and keeps unauthorized connections in ProxySQL. We cannot then be sure of not suffering a DDOS attack on our ProxySQL instance. In this article, I will explain how I managed to overcome this problem.

Reminder of the principle of connection through ProxySQL

To understand what follows, you have to bear in mind how ProxySQL connects to MySQL. The user connects to Proxysql which then establishes the connection to MySQL. For this, ProxySQL maintains MySQL users in its internal database. The names of MySQL users, their passwords as well as the MySQL destination server are entered in the mysql_users table. At each connection request to a MySQL server, ProxySQL checks the presence of the user in the mysql_users table to connect itself to MySQL with this same user.

Something is missing, isn’t it?

Yes, the host associated with each MySQL user is missing!

In MySQL, users are configured to only be able to connect from ProxySQL. In ProxySQL we don’t have this information. By default, all users can therefore connect to ProxySQL from any IP address and ProxySQL will open connections to MySQL for them. As I specified in the introduction, ProxySQL provides a Firewall to overcome this problem, but this one is not really satisfactory.

Prevent connection to ProxySQL with an unauthorized user

In this part, our ProxySQL instance will allow user bob to connect to the MySQL (mysql_server) instance. Bob is allowed to connect from IP_1 but cannot from IP_2. The ProxySQL instance is running on IP_PROXYSQL.

In MySQL, user bob was created like this:

sql
CREATE USER 'bob'@'IP_PROXYSQL' IDENTIFIED BY 'PASSWORD';

In ProxySQL, let’s create bob like this:

sql
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('bob','PASSWORD',0);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

and declare the MySQL server like this:

sql
INSERT INTO mysql_servers(hostgroup_id,hostname) VALUES (1,'mysql_server');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

As you may have noticed, I didn’t declare the same hostgroup when creating the user and the server. Hostgroup 0 does not correspond to any MySQL server. By default, our user bob will therefore be able to connect to ProxySQL but his queries will not be redirected to any MySQL server. Let’s move on to host management. I will declare each authorized host in the mysql_query_rules table. In ProxySQL, this table is used, among other things, to assign different parameters to a connection. You see what I mean? Let’s declare our rule!

sql
INSERT INTO mysql_query_rules (rule_id,active,username,client_addr,destination_hostgroup,apply) VALUES (1,1,'bob','IP_1',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

I have just declared a rule indicating that all requests coming from user bob connected from IP_1 must be played on host 1. And icing on the cake, IP_1 can be a subnet (IP_1%), which would not have could not be possible with the firewall. From now on, bob will be able to perform queries from IP_1 and get results from MySQL. If bob plays a request from IP_2, he will not be able to obtain a result since the hostgroup queried will be 0 which does not correspond to any MySQL server. However, this is not satisfactory. Nothing prevents bob from creating a very large number of connections from IP_2. It won’t reach any MySQL servers but may be able to crash my ProxySQL instance. It’s time to deal with those unauthorized connections!

ProxySQL provides a scheduler which will be very useful here. This scheduler will allow us to play a bash script every x ms. I created this script in the ProxySQL datadir:

kill_connections.sh

bash
#!/bin/bash

PROXYSQL_USERNAME="${1}"
PROXYSQL_PASSWORD="${2}"
PROXYSQL_HOSTNAME="127.0.0.1"
PROXYSQL_PORT="6032"

mysql -u$PROXYSQL_USERNAME -p$PROXYSQL_PASSWORD -h$PROXYSQL_HOSTNAME -P$PROXYSQL_PORT -e "SELECT SessionID,user,cli_host FROM stats_mysql_processlist WHERE hostgroup = 0" | while read SessionID user cli_host; do
 if [ $SessionID != "SessionID" ]; then
 enabled_account=$(mysql -u$PROXYSQL_USERNAME -p$PROXYSQL_PASSWORD -h$PROXYSQL_HOSTNAME -P$PROXYSQL_PORT -se"SELECT count(*) FROM mysql_query_rules WHERE username = '$user' and '$cli_host' LIKE client_addr;")
 if [[ "$enabled_account" -eq 0 ]]; then
 mysql -u$PROXYSQL_USERNAME -p$PROXYSQL_PASSWORD -h$PROXYSQL_HOSTNAME -P$PROXYSQL_PORT -e "KILL CONNECTION $SessionID"
 fi

 fi
done

This script lists all the connections opened in ProxySQL on hostgroup 0. It then checks whether the connected user/host pair is authorized using the mysql_query_rules table. If not, the connection is killed. Let’s activate the scheduler in ProxySQL:

sql
INSERT INTO scheduler(filename, arg1, arg2, interval_ms) VALUES ('kill_connections.sh','proxysql_admin_user','proxysql_admin_password', 1000);
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;

Now, any connection opened in ProxySQL but not authorized will be automatically killed!

WARNING: unfortunately, the ProxySQL scheduler does not work like the MySQL scheduler. It is necessary to open the connection from a .sh file and therefore to indicate the ProxySQL administration credentials. These identifiers will then be visible by monitoring the list of server processes. To avoid this problem, I advise you to indicate the identifiers directly in the .sh file and to protect this file correctly on your server.

Additional Information

When I deploy ProxySQL, I always create a rule with a very high rule_id to block unauthorized connections; this is an additional barrier in case I forget something:

sql
INSERT INTO mysql_query_rules (rule_id,active,error_msg,destination_hostgroup) VALUES (999999999,1,'ProxySQL : Access denied',0);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This rule redirects unauthorized connections to hostgroup 0 (if ever a user was declared in mysql_users with a hostgroup leading to a MySQL server) and displays an error message for each request. I create all my rules to manage hosts with a rule_id > or = 10000. This allows me to have 9999 empty slots if I ever want to create other priority rules in mysql_query_rules.

sql
INSERT INTO mysql_query_rules (rule_id,active,username,client_addr,apply) VALUES ((SELECT IFNULL(MAX(rule_id)+1,10000) FROM mysql_query_rules WHERE rule_id != (SELECT MAX(rule_id) FROM mysql_query_rules) AND rule_id > 9999),1,'USERNAME','HOST',1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Don’t hesitate to ask me questions, I’ll be happy to answer them.

Percona Monitoring and Management 2.36 preview release

Taras Kozub — Mon, 20 Mar 2023 00:00:00 UTC

Percona Monitoring and Management 2.36 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.36 is now available as a preview release.

You can find the Release Notes here

Percona Monitoring and Management server docker installation

docker tag:

perconalab/pmm-server:2.36.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.36.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.36 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.36.0.ova

AMI

ami-0ce04c507ec1187b1

Some Notable Bugfixes in MySQL 8.0.32

Aleksandra Abramova — Wed, 15 Mar 2023 00:00:00 UTC

MySQL 8.0.32 came out recently and had some important bugfixes contributed by Perconians. Here is a brief overview of the work done.

Inconsistent data and GTIDs with mysqldump

Marcelo Altmann (Senior Software Engineer) fixed the bug when data and GTIDs backed up by mysqldump were inconsistent. It happened when the options –single-transaction and –set-gtid-purged=ON were both used because GTIDs on the server could have already increased between the start of the transaction by mysqldump and the fetching of GTID_EXECUTED. Marcelo developed a patch, and it was partially included in the release. Now, in MySQL 8.0.32, a FLUSH TABLES WITH READ LOCK is performed before fetching GTID_EXECUTED, to ensure its value is consistent with the snapshot taken by mysqldump. However, Percona Server for MySQL includes the entire patch, which does not require FLUSH TABLE WITH READ LOCK to work.

Marcelo also corrected the issue when the MySQL server exits on ALTER TABLE created an assertion failure: dict0mem.h:2498:pos < n_def.

Fixing garbled UTF characters

Kamil Holubicki (Senior Software Engineer) proposed a patch to fix garbled UTF characters in SHOW ENGINE INNODB STATUS. It happened because the string was truncated, and UTF characters (which are multibyte) were cut in the middle which caused garbage at the end of the string.

Duplicate table space objects in 5.6 to 8.0 upgrade

Rahul Malik (Software Engineer) investigated and fixed an issue when an 8.0 upgrade from MySQL 5.6 crashed with Assertion failure. It happened due to a duplicate table space object. All SYS_* tables are loaded, and then their table IDs are changed. Some SYS tables like SYS_ZIP_DICT, VIRTUAL can have ids > 1024 (say, 1028).

Changing table_ids of SYS_FIELDS from 4 to 1028 will conflict with the table_ids of those existing SYS_ZIP_DICT/VIRTUAL which haven’t been shifted by 1024 yet and are currently loaded with 1028. Hence, we need to change the IDs of that SYS tables in reverse order to fix it. So in the example above, SYS_FIELD is the first shift to 1028+1024, and then SYS_FIELD changes to 1028 to avoid conflicts.

Why open source databases matter

Great work by Marcelo, Kamil, Rahul, and everybody else who contributed to the MySQL 8.0.32 release.

This is why open source databases are so important. We can all help improve MySQL, and those improvements benefit all users of MySQL.

Percona is proud to be part of the MySQL community, and we hope you’ll join us in improving MySQL and its surrounding software. Check out our contributing page to find ways to contribute!

Using the JSON data type with MySQL 8

Edith Puclla — Mon, 13 Mar 2023 00:00:00 UTC

If you are a mobile app, frontend, backend, or game developer, you use data types such as string, numeric, or DateTime. You also know that since the advent of non-relational databases (NoSQL) such as MongoDB, which, by not being tied to a traditional SQL schema, do reading and writing on databases much faster. But MySQL showed that storing the JSON (JavaScript Object Notation) data type could also improve the speed of reading and writing relational databases.

This post will explore the JSON Data type in Percona Server for MySQL.

One of the key features of Percona Server is support for JSON data type, which allows for the storage of JSON documents within MySQL. It allows for more flexible and efficient storage of semi-structured data (which is more human-readable ) within a relational database.

We will install Percona Server for MySQL in a Docker container to make basic operations for inserting, modifying, and removing JSON data types.

To start, we will bring version 8.0 of Percona Server for MySQL; the name of this image in Docker Hub is percona-server. You will need Docker; if you don’t have it installed, follow the official Docker documentation.

docker pull percona/percona-server:8.0

We will run the container for Percona Server for MySQL, call our container percona-server and pass in an environment variable called MYSQL_ROOT_PASSWORD; This variable specifies a password that is set for the MySQL root account.

bash
docker run -d \
 --name percona-server \
 -e MYSQL_ROOT_PASSWORD=root \
 percona/percona-server:8.0

After confirming that our container is running with “docker ps,” we can enter our Percona Server for MySQL container to start executing commands.

docker exec -it percona-server /bin/bash

The Percona Server for MySQL database is already running, and we will proceed to connect to it:

mysql -uroot -p

Use root as a password.

Create the database called cinema

bash
CREATE DATABASE library;
USE library;

Create a table called books

bash
CREATE TABLE books (
 book_id BIGINT PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(100) UNIQUE NOT NULL,
 publisher VARCHAR(100) NOT NULL,
 labels JSON NOT NULL
) ENGINE = InnoDB;

Insert JSON type into books table

bash
INSERT INTO books(title,publisher, labels)
VALUES('Green House', 'Joe Monter', '{"about" : {"gender": "action", "cool": true, "notes": "labeled"}}');

INSERT INTO books(title,publisher, labels)
VALUES('El camino', 'Daniil Zotl', '{"about" : {"gender": "documental", "cool": true, "notes": "labeled"}}');

bash
select * from books;

As you can see, JSON is a more flexible data type than what you might be used to when working with data in MySQL.

Select with JSON_EXTRACT

bash
SELECT title, JSON_EXTRACT(labels, '$.about.notes') AS Notes FROM books;
A shortcut of JSON_EXTRACT is “->”
SELECT title, labels->'$.about.notes' AS Notes FROM books;

The short operator -> provides the same functionality as JSON_EXTRACT

bash
SELECT titulo, etiquetas->'$.acerca.genero' AS Genero FROM books;

Updating JSON type records

bash
UPDATE books SET labels = JSON_REPLACE(labels, '$.about.gender', 'romance') WHERE title = 'the roses';

UPDATE books SET labels = JSON_REPLACE(labels, '$.about.notes', 'not labeled') WHERE title = 'the roses';

bash
select * from books;

Deleting a JSON record

bash
DELETE FROM books WHERE book_id = 1 AND JSON_EXTRACT(labels, '$.about.gender') = "documental";

Deleting a value inside a JSON structure

bash
UPDATE books SET labels = JSON_REMOVE(labels, '$.about.notes') WHERE book_id = 2;

You can use these fundamental operations to manage JSON data types in Percona Server MySQL. This allows for more flexible and efficient data modeling and querying for applications that work with JSON data. How will that work in an application? Keep an eye out, I’ll be following this up with a blog about an application using JSON data in MySQL very soon.

Get more about Percona Server for MySQL documentation in our official documentation. And if you want to know why JSON is the preferred format for many developers and why it’s so popular, check out David Stokes’ blog: JSON and Relational Databases – Part One

Backups for MySQL With mysqldump

Mario García — Fri, 10 Mar 2023 00:00:00 UTC

Basic Usage

mysqldump is a client utility that can be used for doing logical backups. It will generate the necessary SQL statements to reproduce the original database.

Backup by Nick Youngson CC BY-SA 3.0 Pix4free

The following statements are some common uses of mysqldump:

mysqldump -u username -p database_name [table_name] > dump.sql
mysqldump -u username -p --databases db1_name db2_name > dump.sql
mysqldump -u username -p --all-databases > dump.sql

The first example is for backing up a single database. If you need to back up some specific tables instead of the whole database, write their names, space-separated.

With the --databases option, you can back up two or more databases, their names must be space separated.

To back up all the databases in your MySQL server, just append the --all-databases option.

The dump.sql file doesn’t contain the create database SQL statement. If you need it, add it with the -B option. This is unnecessary if you’re running mysqldump with the --databases and --all-databases options.

Ignoring tables when backing up a database is also possible with the --ignore-tables option.

$ mysqldump -u username -p database_name --ignore-tables=database_name.table1 > database_name.sql

If you need to ignore more than one database, just use the option as many times as needed.

$ mysqldump -u root -p database_name --ignore-table=database_name.table1 --ignore-table=database_name.table2 > database_name.sql

Schema Backup

In case you need to backup only the schema of your database with no data, run mysqldump with the --no-data option:

$ mysqldump -u username -p database_name --no-data > dump.sql

You can also backup the schema while running mysqldump with the --databases and --all-databases options.

$ mysqldump -u username -p --all-databases --no-data > dump.sql

$ mysqldump -u username -p --databases db1_name db2_name --no-data > dump.sql

Data Restore

To restore the databases in your dump.sql file, run the following command:

$ mysqldump -u root -p < dump.sql

If you need to restore a single database from the complete backup, you can do it by running any of the following statements:

$ mysqldump -u root -p -o database_name < dump.sql

$ mysqldump -u root -p --one-database database_name < dump.sql

In both cases, the database must exist in your MySQL server, as it only will restore the schema and the data.

Conditional Backup

If you need to create a backup that contains data that matches a condition, you can use a WHERE clause with mysqldump.

You can use a single where condition:

$ mysqldump database_name table_name --where="id > 500" > dump.sql

Or multiple conditions:

$ mysqldump database_name users --where="id > 500 and disabled = 0" > dump.sql

As explained here in the mysqldump.guru website.

For example, in a database with the following schema, built from the Movienet dataset:

Movienet Database

If you want to back up the movies produced in a specific country, like Mexico, a way to do it is by running mysqldump with a WHERE clause.

$ mysqldump -u root -p movienet movies --where=”country = 22” > dump.sql

22 is the country_id of Mexico in this particular database, created using this Python script.

You can also get those values by executing the following SQL statement:

select movies.movie_id, movies.title, countries.name as country from movies inner join countries on movies.country = countrie
s.country_id and movies.country = '22';

+-----------+-----------------------------------------------------------+---------+
| movie_id | title | country |
+-----------+-----------------------------------------------------------+---------+
| tt0047501 | Sitting Bull (1954) | Mexico |
| tt0049046 | Canasta de cuentos mexicanos (1956) | Mexico |
| tt0076336 | Hell Without Limits (1978) | Mexico |
| tt0082048 | El barrendero (1982) | Mexico |
| tt0082080 | Blanca Nieves y sus 7 amantes (1980) | Mexico |
| tt0083057 | El sexo de los pobres (1983) | Mexico |
| tt0110185 | El jardín del Edén (1994) | Mexico |
| tt0116043 | De jazmín en flor (1996) | Mexico |
| tt0121322 | El giro, el pinto, y el Colorado (1979) | Mexico |
| tt0133354 | Algunas nubes (1995) | Mexico |
| tt0207055 | La risa en vacaciones 4 (TV Movie 1994) | Mexico |
| tt0208889 | To and Fro (2000) | Mexico |
| tt0211878 | La usurpadora (TV Series 1998– ) | Mexico |
| tt0220306 | El amarrador 3 (1995) | Mexico |
| tt0229008 | El vampiro teporocho (1989) | Mexico |

Skipping Databases

There’s no option for mysqldump to skip databases when generating the backup, but here’s a solution that could work for you:

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
 EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -u username -p -ANe"${SQLSTMT}"`
do
 MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump -u username -p ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

The above BASH script will generate the backup of your MySQL server excluding the information_schema and mysql databases, listed in the EXCLUSION_LIST variable, as well as the databases of your choice in the DATABASES_TO_EXCLUDE variable.

Don’t forget to add the databases you want to exclude to the DATABASES_TO_EXCLUDE variable, replace the username, in both mysql and mysqldump commands, and add the required options to the MYSQLDUMP_OPTIONS variable.

Security Considerations

Some of the common questions in our forum are about how to do a partial restoration from a complete backup. For example, when you back up a database with mysqldump, you will get the statements for creating the schema of the database and inserting the data from your backup.

If you only need the schema, you can run mysqldump with the –no-data option. But if you need to restore the schema of a specific database from a complete backup, I found an interesting solution:

cat dump.sql | grep -v ^INSERT | mysql -u username -p

The above command will restore the schema of your database, skipping the SQL statements for inserting the data. It works well when you backup a single database, but there’s no reason to use it as you can get the schema with the --no-data option, instead of removing the inserts.

What happens if you try to run this command with a backup that includes all the databases in your server? You must be careful as this will try to overwrite the system schema in the mysql database which is dangerous. This database store authentication details and overriding the data will make you lose access to your server.

If you don’t need to backup the mysql database, run mysqldump with the --databases option to specify which databases you require or use the script shared in the Skipping Databases section.

Conclusion

Through this blog post you learned how to use mysqldump for backing up the databases in your MySQL server as well as some recommendations while using this tool. For advanced usage of mysqldump you can check this article in our blog.

Monitor your databases with Open Source tools like PMM

Edith Puclla — Mon, 06 Mar 2023 00:00:00 UTC

In this post, we will cover the value of database monitoring and how we can use Open Source tools like PMM Percona Monitoring and Management to monitor and manage databases effectively.

Why should I care about database monitoring?

Once you have passed the installation and configuration of your databases and it is well underway, you have to start monitoring it, and not only the database but the elements related to it.

Questions like these will begin to arise:

Is my database performing well?
- Are query response times consistently slow?
Is my database available and accepting connections?
- Connections to the database close to the maximum limit
Is my system stable?
- How about CPU, memory, and disk?
Am I experiencing avoidable downtime?
- Hardware failures, network outages.

Am I experiencing data loss?
- Disk crashes
- Human errors
Am I minimizing performance issues that can impact my business?
Can I quickly identify and resolve issues before they become more significant problems?

To answer these questions, you will need to find tools that let you keep your database monitored, and you can opt for free tools for monitoring. PMM is one of them, which is entirely open source.

Percona Monitoring and Management (PMM)

PMM is an open source database observability, monitoring, and management tool for:

MySQL
MariaDB
PostgreSQL
MongoDB
And others

It can also help to improve the performance of your databases, simplify their management and strengthen their security.

PMM is built on top of open source software

Grafana
VictoriaMetrics/Prometheus
ClickHouse
PostgreSQL
Docker

PMM Interface

There are three levels of depth:

Dashboards
Graphs
Metrics

Metrics & Database Monitoring

Important database metrics you should monitor:

It will depend on your specific database and use case
Monitor the metrics that are relevant to your database and your business
You should have alerts and monitoring processes to ensure you are aware of any problems as they occur or before

Some important metrics that could indicate potential database issues:

Query performance
High CPU utilization
High Memory usage
High Disk I/O
User Connection
Data growth
Others

Let’s analyze each of them, and they will also answer your questions at the beginning.

Long Query Response Times

PMM helps you monitor the performance of individual queries and identify slow-performing queries that need to be optimized. We can use Query Analytics in PMM to visualize all the queries running in our database; we can inspect each of them and see which is the one sending more queries per second and much longer it takes to execute it. Also, PMM will show you suggestions to fix or improve queries.

High CPU Utilization

PMM helps you monitor the number of CPU resources the database uses and identify performance bottlenecks.

In the section on CPU utilization, you will see how much of your CPU is being used in a period of time. This is very useful when you need to increase your resources.

High Memory usage

PMM helps you monitor the amount of memory being used by the database and determine if you need to add more memory or optimize your database configuration.

Disk I/O

PMM helps you monitor the number of disk I/O operations performed by the database and identify any potential performance bottlenecks. See here the panel of Disk IO Latency!

User connections

PMM helps you monitor the number of active database connections and determine if your user connection is sized appropriately. If you limit the number of users that should connect to your database, this panel will show you when you are reaching that limit so that you can increase the number.

Data growth

PMM helps you monitor your database growth over time and plan for capacity and performance needs. This dashboard helps to see the time period in which your database is growing and to be able to learn about performance issues or issues as they occur.

Summary

We see the importance of monitoring databases and how to explore PMM for some essential metrics to detect issues and prevent them on time.

Want to try PMM? We have a test environment to try PMM without having to install it first. Feel free to play with it and see how PMM works. If you like it, you can install PMM quickly and start using it in your own environment.

Exploring Databases on Containers with Percona Server for MySQL

Edith Puclla — Thu, 23 Feb 2023 00:00:00 UTC

In this blog, we will explore databases on containers. We will use Docker as a container engine tool and Percona Server for MySQL as a database administration tool. Both are open source tools.

MySQL is a relational database management system that stores data on disk. Percona Server for MySQL is a fork of MYSQL, providing much more advanced features. To run it correctly, we need to know volumes because we want to “persist” the data, the most important thing in databases.

Running a single Percona Server for MySQL container

First, let’s create a container without volumes:

Figure 1: From Percona Server for MySQL image to a running container in Docker

The following command will create a container called percona-server-1, where we can create databases and add some data.

bash
docker run -d --name percona-server-1 -e MYSQL_ROOT_PASSWORD=root percona/percona-server:8.0

Listing the image and the container:

After the container is created:

We have our base image, which is percona/percona-server:8.0
The base image in Docker is read-only. We can’t modify it. It allows you to spin up multiple containers from the same image with the same immutable base.
We can add data to our image. This new layer is readable and writable. If we create our database and populate it:

Accessing the detached container:

docker exec -it percona-server-1 /bin/bash

Connecting to the database

mysql -uroot -proot

Create a Database “cinema” and use it

bash
CREATE DATABASE cinema;
USE cinema;

Create table movies in Database “cinema”

bash
CREATE TABLE movies (
book_id BIGINT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) UNIQUE NOT NULL,
publisher VARCHAR(100) NOT NULL,
labels JSON NOT NULL
) ENGINE = InnoDB;

Insert data into Database “cinema”

sql
INSERT INTO movies(title,publisher, labels)
VALUES(‘Green House’, ‘Joe Monter’, ’{“about” : {“gender”: “action”, “cool”: true, “notes”: “labeled”}}’);

Checking table

sql
select \* from movies;

If you delete this container, everything will be deleted, too, your databases and your data because containers are temporary.

Figure 2: View of the layers that are generated when we create the container. Source: Severalnines AB

Running Multiple MySQL Containers

Now let’s see how the layers of two different containers work together.

bash
docker run -d --name percona-server-1 -e MYSQL_ROOT_PASSWORD=root percona/percona-server:8.0

bash
docker run -d --name percona-server-2 -e MYSQL_ROOT_PASSWORD=root percona/percona-server:8.0

Multiple containers can share the same base image, which is read-only. Each container can have its data state for reading and writing (Which is built on the top of the base image), but this state will be lost if we don’t create persistent volumes that can ve saved after the container is shut down.

Figure 3: View the layers generated when we create two different containers. Source: Severalnines AB

As we said before, “Volumes open the door for stateful applications to run efficiently in Docker.”

Running containers with Persistent Volumes

Now we will create a container with a persistent volume in Docker.

Figure 4: From Percona Server for MySQL image to a running container in Docker with volumes

percona-server is the base of the image. On top of that, we have all the changes we will make in the database. When we create the volume, we link a directory in the container with a directory on your local machine or in the machine where you want to persist the data. When you delete the container, you can attach another container to this volume to have the same data on a different container.

bash
docker run -d --name percona-server -e MYSQL_ROOT_PASSWORD=root -v local-datadir:/var/lib/mysql percona/percona-server:8.0

Figure 4: View of the layers that are generated when we create the container with volume.

Backing up and restroring databases

There are two kinds of backups in databases, logical and physical backups. We can use mysqldump to make logical backups and Percona XtraBackup, for physical backups. If we want to restore, we can use mysqldump and Percona XtraBackup, which offer much more advanced features.

Back up

bash
docker exec -it percona-server-backup mysqldump -uroot --password=root --single-transaction > /path/in/physical/host/dump.sql

Restore

bash
docker exec -it percona-server-restore mysql -u root --password=root < /path/in/physical/host/dump.sql

Now let’s share some tips to run databases on containers:

Constantly monitor your database and host system
Store data in a persistent volume outside the container
Limit resource utilization, e.g., Memory, CPU
Regularly back up the database and store the backup in a secure and separate location.
Have a plan for database migrations and disaster recovery.

We explored how databases work on containers. Volumes are the important thing to persist the data.

What is next? Watch this fantastic talk by Peter Zaitsev Open Source Databases on Kubernetes

Thanks for reading this! You can install Percona Server for MySQL from our Docker Repository and if you have doubts write us in our Percona community forum.

Percona Monitoring and Management 2.35 preview release

Taras Kozub — Tue, 14 Feb 2023 00:00:00 UTC

Percona Monitoring and Management 2.35 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.35 is now available as a preview release.

You can find the Release Notes here

Percona Monitoring and Management server docker installation

docker tag:

perconalab/pmm-server:2.35.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.35.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.35 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.35.0.ova

AMI

ami-09d19be2cfb10a60c

Binding your application to the database in the Kubernetes cluster

Denys Kondratenko — Tue, 24 Jan 2023 00:00:00 UTC

dbaas-operator is Yet Another DBaaS Kubernetes Operator (need to suggest yadbko as a name) that tries to simplify and unify Database Cluster deployments by building a higher abstraction layer on top of Percona Kubernetes Operators.

So it becomes much easier to deploy the DB cluster with dbaas-operator and PMM DBaaS on top of it.

But another part of the picture is applications and their workloads to connect to the deployed DB Clusters.

Services and Applications

On Kubernetes, application deployment could be done in many ways, either manually or as part of automatic deployments.

PMM DBaaS offers both - UI to create DB Clusters and get credentials and API to automate those actions.

dbaas-operator adds Kubernetes native API to that batch as well.

But both require additional automation to join the application and the database in one deployment and provide a service to the end user.

And that operation is a challenging task, as every application could expect credentials in some specific format: secrets with hardcoded structures, environment variables with custom names, mount point secrets in particular locations, etc.

Database services add their complexity to that picture by exposing their connections and secrets in a format that is convenient or makes sense for them.

Usually, some Continues Delivery system or deployment package (helm, etc.) ensures all components’ correct deployment sequence and health. So many custom pipelines and packages exist to connect a specific application to a database service.

But for simplicity and scalability, it would be nice to have some standard for connection or software that automates such a connection.

Service Binding

Connecting services is a known pattern: Service Discovery (broker, registry, repository) for the Service-Oriented Architecture.

servicebinding.io is another pattern to bind applications and workloads to the services (REST APIs, databases, event buses, etc.). This specification aims to create a Kubernetes-wide specification for communicating service secrets to workloads in a consistent way.

Service Binding Operator glues services and Kubernetes workflows together. It does so for the services and applications that support ServiceBinding specifications and those that don’t.

Out of the box Service Binding Operator supports Percona Operator for MySQL based on Percona XtraDB Cluster (PXC), so we will deploy Database Cluster with dbaas-operator and connect it to the simple Java application. We will use Spring PetClinic application that supports Spring Cloud Bindings.

Create an environment

We need to have Kubernetes cluster, Operator Lifecycle Manager (OLM) to install operators, and all required operators installed. In this blog, I would use minikube and assume that operator-sdk is installed on the system

Here is a link to the script that:

setups multi-node Kubernetes cluster
installs OLM
installs needed operators with the help of OLM

As a result we get cluster with all needed operators:

sh
$ kubectl get sub -A
NAMESPACE NAME PACKAGE SOURCE CHANNEL
default dbaas-operator dbaas-operator dbaas-catalog stable-v0
default percona-server-mongodb-operator percona-server-mongodb-operator dbaas-catalog stable-v1
default percona-xtradb-cluster-operator percona-xtradb-cluster-operator dbaas-catalog stable-v1
operators my-service-binding-operator service-binding-operator operatorhubio-catalog stable

Create Database Cluster

We will use dbaas-operator to demonstrate how easy it is to create DB Cluster with it:

sh
$ cat <apiVersion: dbaas.percona.com/v1
kind: DatabaseCluster
metadata:
 name: test-pxc-cluster
spec:
 databaseType: pxc
 databaseImage: percona/percona-xtradb-cluster:8.0.27-18.1
 databaseConfig: |
 [mysqld]
 wsrep_provider_options="debug=1;gcache.size=1G"
 wsrep_debug=1
 wsrep_trx_fragment_unit='bytes'
 wsrep_trx_fragment_size=3670016
 secretsName: pxc-sample-secrets
 clusterSize: 1
 loadBalancer:
 type: haproxy
 exposeType: ClusterIP
 size: 1
 image: percona/percona-xtradb-cluster-operator:1.11.0-haproxy
 dbInstance:
 cpu: "1"
 memory: 1G
 diskSize: 15G
EOF

$ kubectl get db
NAME SIZE READY STATUS ENDPOINT AGE
test-pxc-cluster 2 2 ready test-pxc-cluster-haproxy.default 5m

Create Spring PetClinic app and bind it to the database

sh
$ kubectl apply -f https://raw.githubusercontent.com/redhat-developer/service-binding-operator/master/samples/apps/spring-petclinic/petclinic-mysql-deployment.yaml
deployment.apps/spring-petclinic created
service/spring-petclinic created

$ kubectl get pods
NAME READY STATUS RESTARTS AGE
spring-petclinic-f7f587c5c-rvq2v 0/1 CrashLoopBackOff 2 (17s ago) 67s

As we didn’t create a binding yet, the application can’t connect to the database and thus fails.

Let us bind application to the database and verify it is working:

sh
$ cat <apiVersion: binding.operators.coreos.com/v1alpha1
kind: ServiceBinding
metadata:
 name: spring-petclinic
 namespace: default
spec:
 services:
 - group: pxc.percona.com
 version: v1
 kind: PerconaXtraDBCluster
 name: test-pxc-cluster
 application:
 name: spring-petclinic
 group: apps
 version: v1
 resource: deployments
EOF

$ kubectl get servicebindings
NAME READY REASON AGE
spring-petclinic True ApplicationsBound 4m47s

$ kubectl get deployments
NAME READY UP-TO-DATE AVAILABLE AGE
spring-petclinic 1/1 1 1 17m

$ minikube service spring-petclinic --url
http://192.168.39.215:31181

What we have done above:

Created kind: ServiceBinding, which takes PXC secrets and maps them to the application as mount points.
As PetClinic supports ServiceBinding spec with Spring framework, it understands those mount points and connects to the database.

Here is what mount point by ServiceBinding specification that Spring Cloud Bindings library parsed and connected to the database:

sh
$ kubectl exec deployment/spring-petclinic -- ls -la /bindings/spring-petclinic/..2023_01_20_21_33_47.4121788695

total 56
drwxr-xr-x 2 root root 320 Jan 20 21:33 .
drwxrwxrwt 3 root root 360 Jan 20 21:33 ..
-rw-r--r-- 1 root root 18 Jan 20 21:33 clustercheck
-rw-r--r-- 1 root root 5 Jan 20 21:33 database
-rw-r--r-- 1 root root 32 Jan 20 21:33 host
-rw-r--r-- 1 root root 17 Jan 20 21:33 monitor
-rw-r--r-- 1 root root 17 Jan 20 21:33 operator
-rw-r--r-- 1 root root 18 Jan 20 21:33 password
-rw-r--r-- 1 root root 4 Jan 20 21:33 port
-rw-r--r-- 1 root root 7 Jan 20 21:33 provider
-rw-r--r-- 1 root root 17 Jan 20 21:33 proxyadmin
-rw-r--r-- 1 root root 18 Jan 20 21:33 replication
-rw-r--r-- 1 root root 18 Jan 20 21:33 root
-rw-r--r-- 1 root root 5 Jan 20 21:33 type
-rw-r--r-- 1 root root 4 Jan 20 21:33 username
-rw-r--r-- 1 root root 17 Jan 20 21:33 xtrabackup
$ kubectl exec deployment/spring-petclinic -- cat /bindings/spring-petclinic/..2023_01_20_21_33_47.4121788695/database
mysql
$ kubectl exec deployment/spring-petclinic -- cat /bindings/spring-petclinic/..2023_01_20_21_33_47.4121788695/host
test-pxc-cluster-haproxy.default

Check the url that was exposed by minikube:

Summary

There are many ways to deploy applications and services and connect them.

I am trying to collect some of them under my personal repo to understand the problem deeper. Please suggest other ways by commenting under this blog or in repo.

ServiceBinding specification is a standardized way that scales easily and allows you to connect Kubernetes workloads to the database services.

I will propose to dbaas-operator to implement that specification so that it could expose different Database engines (mysql, mongo, pg) in a standard way.

Percona Monitoring and Management 2.34 preview release

Taras Kozub — Tue, 17 Jan 2023 00:00:00 UTC

Percona Monitoring and Management 2.34 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.34 is now available as a preview release.

You can find the Release Notes here

Percona Monitoring and Management server docker installation

docker tag:

perconalab/pmm-server:2.34.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.34.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.34 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.34.0.ova

AMI

ami-08c09a75c3dd22956

Setting Up PMM For Monitoring Your Databases on Windows

Mario García — Mon, 16 Jan 2023 00:00:00 UTC

Before deploying Percona Monitoring and Management (PMM) in production, you might want to test it or set up a development instance locally. Since many developers and DBAs have Windows desktops, I wanted to demonstrate how to set up PMM on Windows for an easy test environment. In this post, I’ll walk you through setting up PMM with Docker and WSL.

If you’re a Linux user, check the blog post I wrote on Setting up PMM for monitoring MySQL in a local environment. There you can find instructions for installing Percona Monitoring and Management (PMM) on Linux and how to set it up for monitoring a MySQL instance. Otherwise, continue reading to get PMM up and running on Windows.

Getting Started With PMM on Windows

If you’re a Windows user and want to try PMM, the recommended way for installing both the server and client would be to use the official Docker images and follow these guides:

Before running the commands in those guides, you should install Docker Desktop and Windows Subsystem for Linux (WSL). These instructions should work for users who are on current versions of Windows 10 and Windows 11. For installing WSL, follow the instructions on the Microsoft Learn website. Then, get the Docker Desktop installer. Now you’re ready to install and configure PMM.

PMM Server

As stated in the documentation, you can store data from your PMM in:

Docker volume (Preferred method)
Data container
Host directory

The preferred method is also recommended for Windows. Open PowerShell and execute the instructions in the Run Docker with volume section. I’ve reproduced the steps here to save you time:

Get the Docker image:

$ docker pull percona/pmm-server:2

Create a volume:

$ docker volume create pmm-data

Run the image:

$ docker run --detach --restart always \
--publish 443:443 \
-v pmm-data:/srv \
--name pmm-server \
percona/pmm-server:2

Change the password for the default admin user:

$ docker exec -t pmm-server change-admin-password

Once PMM Server is installed, open the browser and visit https://localhost. You will see the PMM login screen.

PMM Login Screen

Now that the server is up and running, you need to get its IP address before connecting the client to the server. To get the IP address, you need the name or container ID. You can get it by running:

$ docker ps

The docker ps command will give you a list of the containers running on your system, as follows:

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
fd988ad761aa percona/pmm-server:2 "/opt/entrypoint.sh" 2 months ago Up 11 minutes (healthy) 80/tcp, 0.0.0.0:443->443/tcp pmm-server

Take note of the container ID or name of the PMM Server container. Then, execute this command:

$ docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' your_container

Replacing your_container with the container ID or name copied previously.

PMM Client

Go to the Set Up PMM Client section in the documentation and follow the first two steps. All of these commands are executed from PowerShell.

In Step 3, you need to specify the IP address of the PMM Server by setting up the PMM_SERVER environment variable:

bash
$ $env:PMM_SERVER=’X.X.X.X:443’

Replacing X.X.X.X with the server’s IP address. Then, initialize the container:

$ docker run \
--rm \
--name pmm-client \
-e PMM_AGENT_SERVER_ADDRESS=$env:PMM_SERVER \
-e PMM_AGENT_SERVER_USERNAME=admin \
-e PMM_AGENT_SERVER_PASSWORD=admin \
-e PMM_AGENT_SERVER_INSECURE_TLS=1 \
-e PMM_AGENT_SETUP=1 \
-e PMM_AGENT_CONFIG_FILE=config/pmm-agent.yaml \
--volumes-from pmm-client-data \
percona/pmm-client:2

PMM_AGENT_SERVER_PASSWORD default value is admin. Replace this value with the password assigned when the server was configured.

Configure Your Database

Now that the client is connected to the server, you must configure PMM for monitoring your database. Follow the instructions below.

MySQL
- Installation
- Configuration
PostgreSQL
- Installation
- Configuration
MongoDB
- Installation
- Configuration

Once PMM is configured, the Home Dashboard will show the databases that are being monitored. For more information and advanced configuration, check the documentation.

PMM Home Dashboard

MySQL Installation

If you already have a MySQL instance running, skip the installation process and continue with the configuration.

On Windows, you can install Percona Server for MySQL on Ubuntu running under WSL, but it’s better to use the official Docker image, as the MySQL server would be running on the same network as PMM.

For installing MySQL using Docker, follow the instructions in the Percona Server for MySQL documentation.

You need to start the container with the latest version of Percona Server for MySQL 8.0:

$ docker run -d \
 --name ps \
 -e MYSQL_ROOT_PASSWORD=root \
 percona/percona-server:8.0

Where ps is the name of the container, and the default password for the root user is root. You can change these values according to your needs.

MySQL Configuration

Once Percona Server for MySQL is running, you need to get its IP address by running:

$ docker ps

bash
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1fb4ddb35e48 percona/pmm-client:2 "/usr/local/percona/…" 2 minutes ago Up 2 minutes pmm-client

Copy the container ID or name of the Percona Server for MySQL container. Then, execute this command:

$ docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' your_container

Replacing your_container with the value you copied previously.

The IP address of the PMM Client container is also needed.

For configuring PMM for monitoring MySQL, we need to create a PMM user. First, log into MySQL:

$ docker run -it --rm percona mysql -h MYSQL_SERVER -uroot -p

Where MYSQL_SERVER is the IP address of the Percona Server for MySQL container

Then, execute the following SQL statements

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

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

Replacing pass with your desired password, and localhost with the IP address of the PMM Client container.

And finally, register the MySQL server for monitoring:

bash
$ docker exec pmm-client pmm-admin add mysql --username=pmm --password=pass --host MYSQL_SERVER --query-source=perfschema

Where MYSQL_SERVER is the IP address of the Percona Server for MySQL container. Replace this value with the IP address and replace pass with the password of your pmm user.

PostgreSQL Installation

If you already have a PostgreSQL instance running, skip the installation process and continue with the configuration.

On Windows, you can install PostgreSQL using the Windows installer or install it on Ubuntu running under WSL, but it’s better to install it using the official image provided by the PostgreSQL project.

You need to start the container with the latest version of PostgreSQL:

bash
$ docker run --name postgres -e POSTGRES_PASSWORD=password -d postgres

Where password is the password for the default postgres user. Replace this value according to your needs.

PostgreSQL Configuration

Once PostgreSQL is running, you need to get its IP address by running:

$ docker ps

bash
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0460c671db12 postgres "docker-entrypoint.s…" 6 days ago Up 48 seconds 5432/tcp postgres

Copy the container ID or name of the PostgreSQL container. Then, execute this command:

$ docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' your_container

Replacing your_container with the value you copied previously.

The IP address of the PMM Client container is also needed.

For configuring PMM for monitoring PostgreSQL, we need to create a PMM user. First, log into PostgreSQL:

$ docker exec -it postgres psql --user postgres

Then, execute the following SQL statement:

sql
CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD '';

Replacing with your desired password.

And finally, register the PostgreSQL server for monitoring:

bash
$ docker exec pmm-client pmm-admin add postgresql --username=pmm --password=pass --host POSTGRESQL_SERVER --query-source=perfschema

Where POSTGRESQL_SERVER is the IP address of the PostgreSQL container. Replace this value with the IP address and replace pass with the password of your pmm user.

MongoDB Installation

If you already have a MongoDB instance running, skip the installation process and continue with the configuration.

On Windows, you can install Percona Server for MongoDB on Ubuntu running under WSL, but it’s better to use the official Docker image, as the MongoDB server would be running on the same network as PMM.

For installing MongoDB using Docker, follow the instructions in the Percona Server for MongoDB documentation.

You need to start the container with the latest version of Percona Server for MongoDB 6.0:

$ docker run --name psmdb -d percona/percona-server-mongodb:6.0

MongoDB Configuration

Once Percona Server for MongoDB is running, you need to get its IP address by running.

$ docker ps

bash
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2c3d291535b3 percona/percona-server-mongodb:6.0 "/entrypoint.sh mong…" 6 days ago Up 27 minutes 27017/tcp psmdb

Copy the container ID or name of the Percona Server for MongoDB container. Then, execute this command:

$ docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' your_container

Replacing your_container with the value you copied previously.

The IP address of the PMM Client container is also needed.

For configuring PMM for monitoring MongoDB, we need to create a PMM user. First, connect to the admin database in MongoDB using the MongoDB Shell (mongosh):

$ docker run -it --link psmdb --rm percona/percona-server-mongodb:6.0 mongosh mongodb://MONGODB_SERVER:27017/admin

Where MONGODB_SERVER is the IP address of your MongoDB server.

Then, create the user for PMM, executing the following instructions:

db.createRole({
 "role":"explainRole",
 "privileges":[
 {
 "resource":{
 "db":"",
 "collection":""
 },
 "actions":[
 "collStats",
 "dbHash",
 "dbStats",
 "find",
 "listIndexes",
 "listCollections"
 ]
 }
 ],
 "roles":[]
})

db.getSiblingDB("admin").createUser({
 "user":"pmm",
 "pwd":"",
 "roles":[
 {
 "role":"explainRole",
 "db":"admin"
 },
 {
 "role":"clusterMonitor",
 "db":"admin"
 },
 {
 "role":"read",
 "db":"local"
 }
 ]
})

Replacing with the password you want to assign to the pmm user.

And finally, register the MongoDB server for monitoring:

bash
$ docker exec pmm-client pmm-admin add mongodb --username=pmm --password=pass --host MONGODB_SERVER

Where MONGODB_SERVER is the IP address of the MongoDB container. Replace this value with the IP address and replace pass with the password of your pmm user.

Automating Percona's XtraBackup

Phil Plachta — Wed, 04 Jan 2023 00:00:00 UTC

Percona’s XtraBackup is a beautiful tool that allows for the backup and restoration of MySQL databases.

From the documentation:

The Percona XtraBackup tools provide a method of performing a hot backup of your MySQL data while the system is running. Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL®. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows.

It is great but it quickly becomes difficult to wield when using it multiple times per day across multiple environments. XtraBackup Automator attempts to make this easier by providing the ability to:

Schedule when we should create backups
- Times of day, when to make a base backup vs incremental
Archive old backups
- Decide what to do with the base backup and its increments when we are ready to create a new base
Maintain x days of archives
- Define how many archived backup groups should we keep before removing them from the file system

XtraBackup Automator automates away the management of MySQL backups.

Considerations Before Installing

I strongly recommend testing this on some sort of preproduction environment first. The thing I’ve seen most likely to cause trouble is the archival process. By default, this tool uses the gztar ’tarball’ as its compression method, which can be resource intensive if you are working on a large database backup. For instance, one of our servers (a Google Cloud Platform virtual machine with 8 vCPU, 32gb RAM, 1000GB SSD persistent disk, running Debian 10) with a ~140GB base backup currently jumps in CPU usage by ~13% for 4 hours, with a handful of 5%-15% jumps in RAM usage, while creating this archive. Another downside of this compression method is that it can take 10-20 minutes to unzip, depending on settings. The benefit of the tarball is that we are able to take these large backups from 140GB to < 10GB and this is worth all that other trouble for us as we want to have two weeks of daily backups. If these down sides are not acceptable, I recommend playing with the archive type as described in the config. I have not personally tested any other methods.

I am assuming that you have administrative access to the server this will run on as installing systemd services and timers requires root access. I see no reason why Cron Jobs could not be used to run this program, but I have never tested that and all documentation references systemd and its tools.

Info & Requirements

Developed On

OS:
- Debian GNU/Linux 10 (buster)
Python Version:
- Python 3.10.4
Python Packages
- Name: pexpect, Version: 4.8.0
Percona XtraBackup Version:
- XtraBackup version 8.0.28-21 based on MySQL server 8.0.28 Linux (x86_64)
MySQL
- MySql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

Required Python Libraries

pexpect

Required Files

Installing

Below is a general explanation of how to install and start running this program. I would suggest running the program manually via command line a couple times, in a preproduction environment, to verify things are working as you expect.

Download The Files

Download the Required Files from https://github.com/phildoesdev/xtrabackupautomator

Review Your Config Settings

Review the Configuration section of this readme and alter these settings to your liking.
Any altered folder paths may affect the create folder instructions below. At minimum you must include database login information, but alter as necessary. I suggest reading through all the config options to see what might be interesting to tweak.

Edit your systemd service and timer

If you change the location that the script should run from you must alter the file path in the xtrabackupautomator.service file. I will not explain much else here as there is a lot that might go into these settings. I have given some default settings that hopefully make sense.

I have also included several links that describe what is possible in the Sources & Links section. If there are specific questions in the future I will address them here.

Install the required dependencies

$ python3 -m pip install pexpect

Create the directory for our code to live in

bash
$ sudo mkdir /lib/xtrabackupautomator
$ sudo chmod 700 /lib/xtrabackupautomator

Create the directories for our backups to save to

bash
$ sudo mkdir -p /data/backups/mysql
$ sudo mkdir -p /data/backups/archive
$ sudo mkdir -p /data/backups/archive/archive_restore

$ sudo chmod 760 /data/backups/mysql
$ sudo chmod 700 /data/backups/archive
$ sudo chmod 700 /data/backups/archive/archive_restore
$ sudo chown -R root:root /data/backups/

Move your downloaded files

bash
$ sudo mv xtrabackupautomator.py /lib/xtrabackupautomator/.
$ sudo mv xtrabackupautomator.service /etc/systemd/system/.
$ sudo mv xtrabackupautomator.timer /etc/systemd/system/.

Enable your service and timer

bash
$ sudo systemctl daemon-reload

$ sudo systemctl enable xtrabackupautomator.timer

$ sudo systemctl start xtrabackupautomator.timer
$ sudo systemctl status xtrabackupautomator.timer

Congrats, you are now installed!

You have now installed XtraBackup Automator, it will begin running automatically according to your xtrabackupautomator.timer file. If you wish to run it manually, you can run the python file, or use my preferred method, the systemd start command to start it and journalctl to view its output:

bash
$ systemctl start xtrabackupautomator.service
$ journalctl -f -n 100 -u xtrabackupautomator

Unzipping and Restoring your Backup

I have included a link in the Sources & Links section on unzipping tar gz files.

I strongly suggest reading the official Percona documentation on restoring backups.

For a point of a reference, I will describe my generic unzip and restore process. I am using the directory /data/backups/archive/archive_restore/ as a place to unzip and restore from.

Executing any of the below commands can obviously be very dangerous as we must stop mysql, wipe the current data, and restore with our prepared backup. Read the documentation and come up with your own plan! The code below is only meant as a reference and may change greatly with time and environments. Always test your plans in a preprod environment!!

bash
# Always verify our version of Percona's XtraBackup and MySQL match before performing a backup... these differences can make restores fail or behave oddly.
$ sudo mysql --version
$ sudo xtrabackup --version

# Clean our restore folder, just to be safe
$ rm -r /data/backups/archive/archive_restore/*

# Unzip our archived backup to an empty folder. Always verify we have enough disk space to unzip before unzipping
$ sudo tar -xvf database_backup_12_23_2022__06_25_10.tar.gz -C /data/backups/archive/archive_restore/

# Sanity check, verify we are looking at the backup we think we are (This command checks the base folder, check the latest incremental folder we may have)
$ cd /data/backups/archive/archive_restore/data/backups/mysql/
$ sudo cat base/xtrabackup_info | grep 'tool_version\|server_version\|start_time\|end_time\|partial\|incremental'

# Prepare the base
$ sudo xtrabackup --prepare --apply-log-only --no-server-version-check --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base

# Prepare each incremental folder. This must be done for each incremental folder we wish to back up to.
$ sudo xtrabackup --prepare --apply-log-only --no-server-version-check --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base --incremental-dir=/data/backups/archive/archive_restore/data/backups/mysql/inc_0

# Repeat as necssary....
...

# Stop SQL and our backup script as we do not want it running mid restore
$ sudo systemctl stop mysql
$ sudo systemctl stop xtrabackupautomator.timer

# Wipe bad/corrupted sql data from current instance
$ sudo rm -rv /var/lib/mysql/*

# Verify our mysql data is wiped
$ sudo ls /var/lib/mysql

# I use this method to restore my base backup, there are other options but they did not work correctly in my environment
$ sudo xtrabackup --copy-back --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base

# Verify the contents are the size we expect as a sanity check and apply the correct ownership to the files
$ du -hs /var/lib/mysql/
$ sudo chown -R mysql:mysql /var/lib/mysql

# Restart mysql and xtrabackupautomator. Verify MySQL's status
$ sudo systemctl start mysql
$ sudo systemctl xtrabackupautomator.timer
$ sudo systemctl status mysql

Configuration

In an attempt to make this a one file, easy to install piece of software, I included the configuration struct in the xtrabackupautomator.py file, in the __init__ method of the XtraBackupAutomator class, on line ~60 (as of this writing). I will describe that struct, its default values, and other relevant information below. Most of this information can also be found in comments throughout, or in the getter methods for each variable.

== db ==
 -un
 [DEFAULT_VALUE: ""]
 XtraBackup user you set up during your initial configuration of Percona's XtraBackup

 -pw
 [DEFAULT_VALUE: ""]
 This user's password

 -host
 [DEFAULT_VALUE: "localhost"]
 The IP of your database

 -port
 [DEFAULT_VALUE: 3306]
 The port to access database


== folder_names ==
 -base_dir
 [DEFAULT_VALUE: "/data/backups/"]
 The root directory for all backup related things. Holds current backup and any archived backups.
 This is the default location and is reflected in the setup as we request you create this folder.
 If you change this directory in the config this change must be reflected in the setup.

 -datadir
 [DEFAULT_VALUE: "mysql/"]
 Folder that current backups will be saved to. This would be the folder that holds the base backup and any
 incremental backups before they are archived
 If you change this directory in the config this change must be reflected in the setup.
 *** XtraBackupAutomator WILL ARCHIVE AND DELETE ANYTHING IN HERE. THIS SHOULD BE AN EMPTY FOLDER, NOT UTILIZED BY ANYTHING ELSE.

 -archivedir
 [DEFAULT_VALUE: "archive/"]
 Folder that a group of backups will be archived to.
 If you change this directory in the config this change must be reflected in the setup.
 *** XtraBackupAutomator COULD POTENTIALLY DELETE ANY NON-DIRECTORY IN HERE.

== file_names ==
 -basefolder_name
 [DEFAULT_VALUE: "base"]
 Foldername for the base backup

 -incrementalfolder_perfix
 [DEFAULT_VALUE: "inc_"]
 Folder name prefix for incremental backups.
 Suffixed with the current number of incremental backups minus one
 e.g., 'inc_0'

 -archive_name_prefix
 [DEFAULT_VALUE: "database_backup_"]
 Prefix for the archive files.
 Suffixed by the datetime of the archive
 e.g., 'database_backup_11_28_2022__06_25_03.tar.gz'


== archive_settings ==
 -allow_archive
 [DEFAULT_VALUE: True]
 An override to enable/disable all archive settings.
 Currently, disabling this will cause the program to do a base backup and then incremental backups forever.

 -archive_zip_format
 [DEFAULT_VALUE: "gztar"]
 The default archive file type. I like tarballs because they zip our large database into a manageable file.
 However, tarballs can take a long time to create and require a fair amount of resources if your DB is large.
 This setting will depend on your system and the size of your DB. I recommend playing around with this.
 Other zip options: [Shutil Man Page](https://docs.python.org/3/library/shutil.html#shutil.make_archive)

 -archived_bu_count
 [DEFAULT_VALUE: 7]
 Keep x archived backups, once this threshold is reached the oldest archive will be deleted.
 Archiving daily, this is a week of archives.

 -enforce_max_num_bu_before_archive
 [DEFAULT_VALUE: True]
 One of two ways to 'force archive' of backups.
 This counts the # of incremental backup folders and initiates the archives once that number is reached.
 A sample use case is that in your systemd timer file is scheduled to do 5 backups throughout the day, so setting this to
 true and max_num_bu_before_archive_count set to 4 (because we do not count the base) would give you a 'daily archive'

 -max_num_bu_before_archive_count
 [DEFAULT_VALUE: 4]
 The max number of incremental backups to do before we archive (does not count the base).
 Set to 0 to archive after each base

 -enforce_archive_at_time
 [DEFAULT_VALUE: False]
 One of two ways to 'force archive' of backups.
 This will archive what ever base or incremental folders exist if a backup is happening within the
 archive_at_utc_24_hour hour. This is intended to make it easier to schedule when your archive and base backup occur.
 These can be resource intensive and so it is nice to do at off hours.
 *If this program is scheduled to run more than once during the 'archive_at_utc_24_hour' hour each run will cause an archive.

 -archive_at_utc_24_hour
 [DEFAULT_VALUE: 6]
 If a backup happens within this hour we will archive w/e was previously there and create a new base.
 Matching this with a time setup in your xtrabackupautomator.timer allows you to choose when your backups will
 occur.
 No explicit consideration for daylight savings time.
 Defaults to the hour of 1:00am EST, 6:00am UTC.


== general_settings ==
 -backup_command_timeout_seconds
 [DEFAULT_VALUE: 30]
 Give us 'backup_command_timeout_seconds' seconds for the command to respond.
 This is not the same as saying 'a backup can only take this long'.

 -max_time_between_backups_seconds
 [DEFAULT_VALUE: 60*60*24]
 Max number of seconds between this backup and the last.
 If the last backup is older than this we will archive and create a new base.
 This is in an attempt to prevent an incremental backup that might span days or weeks due to this service being
 turned off or some such.
 Defaults (arbitrarily) to 24 hours

 -additional_bu_command_params
 [DEFAULT_VALUE: ["no-server-version-check"]]
 Any additional parameters that you wish to pass along to your backup commands.
 We loop this list, put a '--' before each element and append it to the end of our backup commands.
 This gets applied to the base and incremental backup commands.
 These are params that I have found useful.


== log_settings ==
 -is_enabled
 [DEFAULT_VALUE: True]
 Enables/Disables all logging type settings. This was useful in testing, so I kept it around.

 -log_child_process_to_screen
 [DEFAULT_VALUE: True]
 If this is set to true the child process's output will be dumped to screen but not actually logged anywhere

 -is_log_to_file
 [DEFAULT_VALUE: True]
 If set to True we will try to log to the 'default_log_file' in the 'default_log_path' directory

 -default_log_path
 [DEFAULT_VALUE: "/var/log/"]
 The path that we will try to place our log file ('default_log_file')

 -default_log_file
 [DEFAULT_VALUE: "xtrabackupautomator.log"]
 The file name we will try to log to

Sources & Links

Official Percona XtraBackup Documentation
- https://docs.percona.com/percona-xtrabackup/8.0/index.html
Systemctl Overveiw
Systemctl Timers Overview
- https://linuxconfig.org/how-to-schedule-tasks-with-systemd-timers-in-linux
- https://opensource.com/article/20/7/systemd-timers
Systemctl Services Details
- https://www.freedesktop.org/software/systemd/man/systemd.service.html
Systemctl Timers Details
- https://www.freedesktop.org/software/systemd/man/systemd.timer.html
OnCalendar Expected Formats
- https://www.freedesktop.org/software/systemd/man/systemd.time.html#
Archive Zip Options
- https://docs.python.org/3/library/shutil.html#shutil.make_archive
How to Extract (Unzip) Tar Gz File
- https://linuxize.com/post/how-to-extract-unzip-tar-gz-file/
Restoring Xtrabackup Incremental Backups
- https://docs.percona.com/percona-xtrabackup/8.0/backup_scenarios/incremental_backup.html

Testing Kubernetes with KUTTL

Andrew Minkin — Fri, 16 Dec 2022 00:00:00 UTC

Automated testing is the only way to be sure that your code works. Enabling automated testing can be hard and we say a lot of tools to write automated tests in the industry since the beginning. Some veterans in the industry may remember Selenium, Cucumber frameworks that help automate testing in the browser. However, testing in Kubernetes can be hard.

In Percona we deal with Kubernetes and have different operators to automate the management of databases. It requires testing. A lot of testing. We have different frameworks to help us with it

Codecept.js to write UI tests for PMM. Also, we use a playwright for some cases.
We have tools to help us with API testing as well as automating some routines by running bash commands during the test step.

However, those frameworks are not applicable to test Kubernetes workloads as well as Kubernetes operators. I’ve been working in the PMM integrations team for six months and saw different approaches to automate testing for PMM/DBaaS. We have a Go test library with wrappers around kubectl and codecept.js for end-to-end tests for the User Interface.

What challenges do we have?

Well, to be sure that a database cluster creation works we need to automate the following steps

Installation of operators to Kubernetes cluster
Test integration with version service to respect compatibility matrix.
Create a database cluster and wait once it’ll be available
Do some assertions against Kubernetes as well as UI.

The main pain point here is that we need to wait up to 10-15 minutes for each step and we can’t have different test cases to cover as many cases as we can. Yet we can achieve some performance benefits by paralleling workloads, still, it requires learning Javascript and testing framework to work with it. We had some architectural changes recently and moved from our custom gRPC API to create and manage database clusters to an operator that runs on top of other operators and converts K8s’ Custom Resource from generic format to operator specific. We had a couple of options for this new project and after research, we chose kuttl as a framework for integration/e2e testing.

What is KUTTL anyway and why should I care?

KUTTL is the KUbernetes Test TooL. It’s written in Go and provides a declarative way to test Kubernetes operators using Kubernetes primitives. It’s easy to start kuttling. Let’s take a deeper look. I’ll use dbaas-operator as an example. DBaaS-operator is an operator that has a simple and generic Custom Resource Definition available to create Percona Server MongoDB or Percona XtraDB Cluster instances in kubernetes. It uses underlying operators as a dependencies. We have the following structure

e2e-tests
├── kind.yml
├── kuttl-eks.yml
├── kuttl.yml
└── tests
 └── pxc
 ├── 00-assert.yaml
 ├── 00-deploy-operators.yaml
 ├── 01-assert.yaml
 ├── 01-deploy-pxc.yaml
 ├── 02-assert.yaml
 ├── 02-upgrade-pxc.yaml
 ├── 03-assert.yaml
 ├── 03-restart-pxc.yaml
 ├── 04-delete-cluster.yaml
 ├── 05-assert.yaml
 ├── 05-create-cluster.yaml
 ├── 06-assert.yaml
 ├── 06-scale-up-pxc.yaml
 ├── 07-assert.yaml
 ├── 07-scale-down-pxc.yaml
 └── 08-delete-cluster.yaml

2 directories, 19 files

Let’s discuss these YAML files more

kind.yml contains settings to run Kind
kuttl.yml has all required settings for Kuttl framework and kuttl-eks.yml has some EKS specific configurations
tests folder has test steps and assertions

Kind and KUTTL settings

Let’s discuss Kind and kuttl settings and I’ll start with KUTTL first

yaml
apiVersion: kuttl.dev/v1beta1
kind: TestSuite
kindConfig: e2e-tests/kind.yml  # Path to Kind config that will be used to create Kind clusters
crdDir: config/crd/bases  # Path to a directory that contains CRD files. Kuttl will apply them before running tests
artifactsDir: /tmp/  # Path to a directory to store artifacts such as logs and other information
testDirs:
- e2e-tests/tests  # Path to directories that have test steps

Kind config is quite easy

yaml
kind: Cluster
apiVersion: kind.x-k8s.io/v1alpha4
nodes:
- role: control-plane
- role: worker
- role: worker
- role: worker
containerdConfigPatches:
- |-
 [plugins."io.containerd.grpc.v1.cri".registry.mirrors."localhost:5000"]
 endpoint = ["http://kind-registry:5000"]

The aforementioned config will use local registry and will create 3 k8s worker nodes controlled by control plane

Writing tests

At the first glance, kuttling can be easy because it uses Kubernetes primitives as a test step and assertion but I had a couple of problems to test my operator. Let’s take a look at a couple of examples. Since dbaas-operator depends on PXC operator we need to prepare our environment for testing. Let’s write first test that installs PXC operator and ensures that it was installed.

cat e2e-tests/tests/pxc/00-deploy-pxc-operator.yml

apiVersion: kuttl.dev/v1beta1
kind: TestStep
timeout: 10 # Timeout for the test step
commands:
 - command: kubectl apply -f https://raw.githubusercontent.com/percona/percona-xtradb-cluster-operator/v${PXC_OPERATOR_VERSION}/deploy/bundle.yaml -n "${NAMESPACE}"

KUTTL test steps easily extensible with commands. One can run even scripts as a prerequisite for a test case. PXC operator installs CRDs and creates a deployment and here’s an example of assertion.

cat e2e-tests/tests/pxc/00-assert.yml

apiVersion: kuttl.dev/v1beta1
kind: TestAssert
timeout: 120 # Timeout waiting for the state
---
apiVersion: apiextensions.k8s.io/v1
kind: CustomResourceDefinition
metadata:
 name: perconaxtradbclusters.pxc.percona.com
spec:
 group: pxc.percona.com
 names:
 kind: PerconaXtraDBCluster
 listKind: PerconaXtraDBClusterList
 plural: perconaxtradbclusters
 shortNames:
 - pxc
 - pxcs
 singular: perconaxtradbcluster
 scope: Namespaced
---
apiVersion: apiextensions.k8s.io/v1
kind: CustomResourceDefinition
metadata:
 name: databaseclusters.dbaas.percona.com
spec:
 group: dbaas.percona.com
 names:
 kind: DatabaseCluster
 listKind: DatabaseClusterList
 plural: databaseclusters
 shortNames:
 - db
 singular: databasecluster
 scope: Namespaced
---
apiVersion: apps/v1
kind: Deployment
metadata:
 name: percona-xtradb-cluster-operator
status:
 availableReplicas: 1
 observedGeneration: 1
 readyReplicas: 1
 replicas: 1
 updatedReplicas: 1

Our first test is ready and one needs to run kubectl kuttl test --config ./e2e-tests/kuttl.yml to run kuttl.

More advanced tests

We need to run our operator first to be able to work with resources and test it. KUTTL recomends configure it via TestSuite by the following example

yaml
apiVersion: kuttl.dev/v1beta1
kind: TestSuite
...
commands:
 - command: ./bin/manager
...

However, since dbaas-operator depends on underlying operators it needs to work correctly even if they are not present in a Kubernetes cluster. It has the following logic in the controller

// SetupWithManager sets up the controller with the Manager.
func (r *DatabaseReconciler) SetupWithManager(mgr ctrl.Manager) error {
 fmt.Println(os.Getenv("WATCH_NAMESPACE"))
 unstructuredResource := &unstructured.Unstructured{}
 unstructuredResource.SetGroupVersionKind(schema.GroupVersionKind{
 Group: "apiextensions.k8s.io",
 Kind: "CustomResourceDefinition",
 Version: "v1",
 })
 controller := ctrl.NewControllerManagedBy(mgr).
 For(&dbaasv1.DatabaseCluster{})
 err := r.Get(context.Background(), types.NamespacedName{Name: pxcCRDName}, unstructuredResource)
 if err == nil {
 if err := r.addPXCToScheme(r.Scheme); err == nil {
 controller.Owns(&pxcv1.PerconaXtraDBCluster{})
 }
 }
 err = r.Get(context.Background(), types.NamespacedName{Name: psmdbCRDName}, unstructuredResource)
 if err == nil {
 if err := r.addPSMDBToScheme(r.Scheme); err == nil {
 controller.Owns(&psmdbv1.PerconaServerMongoDB{})
 }
 }
 return controller.Complete(r)
}

The controller.Owns sets up a controller to watch specified resources and once they were changed it’ll run a reconciliation loop to sync changes. Also, it checks that operator is present in the cluster by checking that deployment and CRDs are available. It means that to make the operator work correctly in tests we need to choose from the following options

Restart operator once upsteam operator was installed by sending HUP signal
Run operator only after underlying operator is present in a cluster

Hence, I moved command as the next step before creating a cluster. You can see it below

cat e2e-tests/tests/pxc/01-deploy-pxc.yml

apiVersion: kuttl.dev/v1beta1
kind: TestStep
timeout: 10
commands:
 - script: WATCH_NAMESPACE=$NAMESPACE ../../../bin/manager
 background: true
---
apiVersion: dbaas.percona.com/v1
kind: DatabaseCluster
metadata:
 name: test-cluster
spec:
 databaseType: pxc
 databaseImage: percona/percona-xtradb-cluster:8.0.23-14.1
 databaseConfig: |
 [mysqld]
 wsrep_provider_options="debug=1;gcache.size=1G"
 secretsName: pxc-sample-secrets
 clusterSize: 1
 loadBalancer:
 type: haproxy
 exposeType: ClusterIP
 size: 1
 image: percona/percona-xtradb-cluster-operator:1.11.0-haproxy
 dbInstance:
 cpu: "1"
 memory: 1G
 diskSize: 15G

Note: command supports only simple commands and does not fully support env variables. It supports only $NAMESPACE, $PATH and $HOME. However, script solves the problem of setting WATCH_NAMESPACE environment variable.

In nutshell, the test step above does two things:

Runs the operator
Creates a database cluster

The assertion checks that kubernetes cluster has the DatabaseCluster object with ready status as well as PXC cluster with the same status.

yaml

apiVersion: kuttl.dev/v1beta1
kind: TestAssert
timeout: 600
---
apiVersion: dbaas.percona.com/v1
kind: DatabaseCluster
metadata:
 name: test-cluster
spec:
 databaseType: pxc
 databaseImage: percona/percona-xtradb-cluster:8.0.23-14.1
 databaseConfig: |
 [mysqld]
 wsrep_provider_options="debug=1;gcache.size=1G"
 secretsName: pxc-sample-secrets
 clusterSize: 1
 loadBalancer:
 type: haproxy
 exposeType: ClusterIP
 size: 1
 image: percona/percona-xtradb-cluster-operator:1.11.0-haproxy
 dbInstance:
 cpu: "1"
 memory: 1G
 diskSize: 15G
---
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
 name: test-cluster
spec:
 allowUnsafeConfigurations: true
 crVersion: 1.11.0
 haproxy:
 enabled: true
 image: percona/percona-xtradb-cluster-operator:1.11.0-haproxy
 serviceType: ClusterIP
 size: 1
 pxc:
 configuration: |
 [mysqld]
 wsrep_provider_options="debug=1;gcache.size=1G"
 expose: {}
 image: percona/percona-xtradb-cluster:8.0.23-14.1
 livenessProbes: {}
 readinessProbes: {}
 resources:
 requests:
 cpu: "1"
 memory: 1G
 serviceType: ClusterIP
 sidecarResources: {}
 size: 1
 volumeSpec:
 persistentVolumeClaim:
 resources:
 requests:
 storage: 15G
 secretsName: pxc-sample-secrets
 updateStrategy: SmartUpdate
 upgradeOptions:
 apply: 8.0-recommended
 schedule: 0 4 * * *
status:
 ready: 2
 size: 2
 state: ready

Caveats and notes

I had problems running tests in Kind. They were flaky because PXC operator can’t expose metrics and had problems with liveness probe. I haven’t figured out how to fix it but as a workaround I use minikube to run tests

 minikube start --nodes=4 --cpus=2 --memory=4g --apiserver-names host.docker.internal --kubernetes-version=v1.23.6
 minikube kubectl -- config view --flatten --minify > ~/.kube/test-minikube
 KUBECONFIG=~/.kube/test-minikube kubectl kuttl test --config ./e2e-tests/kuttl.yml

Further steps

There’s always room for improvement and I have these steps in mind

Use docker images and OLM bundles as a way to run the operator for tests. This will be the best way to simulate a production like environment.
Add more advanced tests for database clusters such as running queries, try loading data as well as capacity testing. It’s easily achivable with kuttl

Percona Monitoring and Management 2.33 preview release

Taras Kozub — Thu, 08 Dec 2022 00:00:00 UTC

Percona Monitoring and Management 2.33 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.33 is now available as a preview release.

Release notes can be found in here

Percona Monitoring and Management server docker

docker tag:

perconalab/pmm-server:2.33.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.33.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.33 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.33.0.ova

AMI

ami-005acacf35adcfa57

Percona Monitoring and Management 2.32 preview release

Taras Kozub — Fri, 04 Nov 2022 00:00:00 UTC

Percona Monitoring and Management 2.32 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.32 is now available as a preview release.

Release notes can be found in here

Percona Monitoring and Management server docker

docker tag:

perconalab/pmm-server:2.32.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.32.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.32 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.32.0.ova

AMI

ami-02cfe7580e77fb5fa

MySQL: Tracing a single query with PERFORMANCE_SCHEMA

Kristian Köhntopp — Tue, 18 Oct 2022 00:00:00 UTC

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

But this is about query execution in the server, and the instrumentation available to me in MySQL 8, at least to get things started. So we take the tour of performance schema, and then run one example query (a simple join) and see what we can find out about this query.

First published on https://blog.koehntopp.info/ and syndicated here with permission of the author.

Performance Schema, the 10.000 m view

The Manual has a major chapter that covers P_S in details. The original idea of P_S is to have a bunch of preallocated memory areas without locks, presented to the database itself as tables.

P_S is unusual in the way that P_S “tables” are never locked while you work with them. That means the values in a “table” can change while you read them. That is important - if you for example calculate percentages, they may not add up to 100%. If you ORDER BY, the sort may or may not be stable.

These are good properties: P_S will not freeze the server, and you won’t kill the server by working with P_S tables.

It is a good idea to make a copy of P_S tables while you work with them, by turning off subquery merging with select /*+ NO_MERGE(t) */ , and then materializing P_S tables in subqueries.

Originally, P_S also had no secondary indexes, so joining P_S tables against other P_S tables did not work efficiently. That was probably a good idea, because joining against a table that is changing while you execute the join is probably generating random results anyway. But because it is so common, and because MySQL itself does this now internally in sys.*, secondary indexes to join efficiently now exist. That does not make the joins more correct, but at least you get the result faster.

I wrote about all this in an earlier article.

Instruments, Objects, Actors, Threads and Consumers

The data P_S collects is centered around three major things:

Time consumed. In database servers, that is mostly wait time - waiting on I/O or locks.
Data transferred. In database servers, that is mostly pages read or written. In a way, this related to I/O wait.
Memory used. In database servers, that is buffers allocated - how large, and how often, and peak usage.

P_S collects this in the form of “events” (and takes care to note that P_S events are not binlog events or other any events). The collection points are in the database server code, which is instrumented, so the collectors are instruments.

The thing that the server code works on may be of a certain kind, for example a table or another object in the server, but have a variable identity (that would be different tables, with different names). Instruments can be filtered by using object names.

The activity done in the server is done on behalf of a database user, in the form of user@host or, new in MySQL 8, using roles. The entity on which behalf the server is working on is called the actor.

The activity done in the server is also done in the context of a thread, some of which are background threads, while the majority in a busy server are usually connection threads.

And finally, the data collected is put into the in-memory tables of P_S. These come in various groups, and are called consumers.

Data is collected from objects using instruments. Instruments can be turned on and off. Their collected data is then filtered by Objects, Actors and Threads, and finally dropped into consumers. Many consumers are aggregates, some collect information specific to one query execution.

For each of these things there is a setup_... table that controls how event data is collected by the instrumentation, filtered and the consumed in result tables. In parallel, object identities are collected in ..._instances tables, which are needed to resolve object identities.

sql
mysql [localhost:8025] {msandbox} (performance_schema) > show tables like "setup_%";
+----------------------------------------+
| Tables_in_performance_schema (setup_%) |
+----------------------------------------+
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_threads |
+----------------------------------------+
5 rows in set (0.00 sec)

mysql [localhost:8025] {msandbox} (performance_schema) > show tables like "%_instances";
+--------------------------------------------+
| Tables_in_performance_schema (%_instances) |
+--------------------------------------------+
| cond_instances |
| file_instances |
| mutex_instances |
| prepared_statements_instances |
| rwlock_instances |
| socket_instances |
+--------------------------------------------+
6 rows in set (0.00 sec)

mysql [localhost:8025] {msandbox} (performance_schema) > select * from socket_instances;
+----------------------------------------+-----------------------+-----------+-----------+-----------+-------+--------+
| EVENT_NAME | OBJECT_INSTANCE_BEGIN | THREAD_ID | SOCKET_ID | IP | PORT | STATE |
+----------------------------------------+-----------------------+-----------+-----------+-----------+-------+--------+
| wait/io/socket/mysqlx/tcpip_socket | 106328376 | 44 | 21 | :: | 18025 | ACTIVE |
| wait/io/socket/mysqlx/unix_socket | 106328688 | 44 | 22 | | 0 | ACTIVE |
| wait/io/socket/sql/server_tcpip_socket | 106329000 | 1 | 26 | 127.0.0.1 | 8025 | ACTIVE |
| wait/io/socket/sql/server_unix_socket | 106329312 | 1 | 28 | | 0 | ACTIVE |
| wait/io/socket/sql/client_connection | 106330560 | 50 | 41 | | 0 | ACTIVE |
+----------------------------------------+-----------------------+-----------+-----------+-----------+-------+--------+
5 rows in set (0.00 sec)

Current, History and History Long Tables vs. Summaries

P_S collects data in a lot of summary table, which should not interest us that much here. Our task is to look at the performance data of a single, individual query to better understand what happened when it ran.

These unaggregated tables are events_transactions, events_statements, events_stages and events_waits. For each of them, we have _current, _history or _history_long tables.

The _current tables contain one entry for the currently running thread. The _history tables contain a configurable number of entries for each thread, for example 10 per thread. And the _history_long tables contain a configurable number of entries, shared across all threads, for example 10.000 in total. As the server continues to execute statements and produce events, old entries are discarded and new entries are added, automatically. Additionally, each query execution is aggregated along several dimensions in summary tables. Summary tables state these dimension(s) using by_, for example _by_user_by_eventname or similar.

In current MySQL, P_S is enabled by default. But not all instruments and consumers are enabled, because some instrumentation slows query execution down, and some consumers can use a lot of memory. To be fast and safe, all memory is statically allocated at config change so the memory resource usage of P_S is constant and no allocations are made during execution.

We can enable all instrumentation completely with this SQL:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > UPDATE
 -> performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 494 rows affected (0.00 sec)
Rows matched: 1216 Changed: 494 Warnings: 0

mysql [localhost:8025] {msandbox} (performance_schema) > UPDATE
 -> performance_schema.setup_consumers SET ENABLED = 'YES';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 15 Changed: 0 Warnings: 0

When we look at one of these tables, for example events_statements_current, we see a structure like this:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > show create table events_statements_current\G
*************************** 1. row ***************************
 Table: events_statements_current
Create Table: CREATE TABLE `events_statements_current` (
 `THREAD_ID` bigint unsigned NOT NULL,
 `EVENT_ID` bigint unsigned NOT NULL,
 `END_EVENT_ID` bigint unsigned DEFAULT NULL,
 `EVENT_NAME` varchar(128) NOT NULL,
 `SOURCE` varchar(64) DEFAULT NULL,
 `TIMER_START` bigint unsigned DEFAULT NULL,
 `TIMER_END` bigint unsigned DEFAULT NULL,
 `TIMER_WAIT` bigint unsigned DEFAULT NULL,
...
 `NESTING_EVENT_ID` bigint unsigned DEFAULT NULL,
 `NESTING_EVENT_TYPE` enum('TRANSACTION','STATEMENT','STAGE','WAIT') DEFAULT NULL,
 `NESTING_EVENT_LEVEL` int DEFAULT NULL,
 `STATEMENT_ID` bigint unsigned DEFAULT NULL,
 PRIMARY KEY (`THREAD_ID`,`EVENT_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

That is, events are tagged with a THREAD_ID (which is not a CONNECTION_ID() as seen in processlist), an EVENT_ID/END_EVENT_ID bracket, various source and timer values and for further dissection, a NESTING_EVENT_ID and _TYPE.

We can translate processlist ids into thread ids using the P_S.THREADS table, and then use this to limit our view on the events_statements_current table:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select thread_id from threads
 -> where processlist_id = connection_id();
+-----------+
| thread_id |
+-----------+
| 50 |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_id,
 -> event_name,
 -> source,
 -> sys.format_time(timer_wait),
 -> sql_text
 -> from events_statements_current
 -> where thread_id = 50\G
*************************** 1. row ***************************
 thread_id: 50
 event_id: 88463
 event_name: statement/sql/select
 source: init_net_server_extension.cc:94
sys.format_time(timer_wait): 341.04 us
 sql_text: select thread_id, event_id, event_name, source, sys.format_time(timer_wait), sql_text from events_statements_current where thread_id = 50
1 row in set (0.00 sec)

So running this query took 341 Microseconds, or 0.341 ms. And sources are named after their location in the server sourcecode, filename and line number.

Events exist in a hierarchy: wait events nest within stage events, which nest within statement events, which nest within transaction events. Some nested events refer to their own type, for example, statement events can point to other statement events they are nested in. Other events refer to their enclosing context in the hierarchy. Nesting Event ID, Type and Level make this clear.

Instrument names

The manual explains Instrument Names.

They have path-like names that group instruments hierarchically, for example wait/io/file/innodb/innodb_data_file. This is a wait event, io related, specifically file I/O, more specific innodb and even more specific innodb_data_file. Looking at other fields in the events_waits_history table, we would see the file name as part of the OBJECT_SCHEMA.OBJECT_NAME designator for this event. That means, we can see how long we waited for I/O coming from this specific file or going to the file.

Further up in the nesting we would see, at the statement level, the actual SQL_TEXT, and also the number of rows scanned. That means we can get a rough estimate why this particular statement instance was slow - for example, the plan was good, the number of rows was low, but we see a lot of actual file IO waits, so probably the buffer pool was cold.

The manual page above discusses the instrument names at length, and it is important to get an overview of what exists and what is measured. Specifically, for statement level entries the instruments vary during query execution and become more detailed, as they reflect the progress in understanding of the server about the nature of the statement as it is executed.

An example run

In a freshly restarted idle server, we log in to a shell and use world for the world sample database. This is a tiny database, but because the server has been just restarted, nothing of it is cached. We run a simple query:

sql
mysql [localhost:8025] {msandbox} (world) > select
 -> co.continent,
 -> co.name as country,
 -> co.population as copop,
 -> ci.name as capital,
 -> ci.population as cipop
 -> from country as co join city as ci
 -> on co.capital = ci.id
 -> where continent = 'Europe';
+-----------+-------------------------------+-----------+------------------------------------+---------+
| continent | country | copop | capital | cipop |
+-----------+-------------------------------+-----------+------------------------------------+---------+
| Europe | Albania | 3401200 | Tirana | 270000 |
...
| Europe | Yugoslavia | 10640000 | Beograd | 1204000 |
+-----------+-------------------------------+-----------+------------------------------------+---------+
46 rows in set (0.00 sec)

Now let’s check what we can find out, using a second session:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select * from threads
 -> where processlist_db = 'world'\G
*************************** 1. row ***************************
 THREAD_ID: 48
 NAME: thread/sql/one_connection
 TYPE: FOREGROUND
 PROCESSLIST_ID: 9
 PROCESSLIST_USER: msandbox
 PROCESSLIST_HOST: localhost
 PROCESSLIST_DB: world
PROCESSLIST_COMMAND: Sleep
 PROCESSLIST_TIME: 4
 PROCESSLIST_STATE: NULL
 PROCESSLIST_INFO: NULL
 PARENT_THREAD_ID: 1
 ROLE: NULL
 INSTRUMENTED: YES
 HISTORY: YES
 CONNECTION_TYPE: Socket
 THREAD_OS_ID: 346752
 RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

In our case we are only interested in the fact that our thread/sql/one_connection in the processlist is shown as connection 9, but internally has a thread_id of 48. The Linux Operating System PID is 346752.

We can use this to check events_transactions_wait, and find

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_id,
 -> state,
 -> source,
 -> sys.format_time(timer_wait) as timer,
 -> nesting_event_id,
 -> nesting_event_type
 - from events_transactions_history
 -> where thread_id = 48;
+-----------+----------+-----------+-----------------+-----------+------------------+--------------------+
| thread_id | event_id | state | source | timer | nesting_event_id | nesting_event_type |
+-----------+----------+-----------+-----------------+-----------+------------------+--------------------+
| 48 | 179 | COMMITTED | handler.cc:1328 | 496.05 us | 85 | STATEMENT |
| 48 | 401 | COMMITTED | handler.cc:1328 | 559.99 us | 278 | STATEMENT |
| 48 | 5606 | COMMITTED | handler.cc:1328 | 3.03 ms | 5574 | STATEMENT |
+-----------+----------+-----------+-----------------+-----------+------------------+--------------------+
3 rows in set (0.00 sec)

Why are there three statement events? We can check:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_id,
 -> sql_text
 -> from events_statements_history
 -> where thread_id = 48 and event_id in (85, 278, 5574);
+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| thread_id | event_id | sql_text |
+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 48 | 85 | show databases |
| 48 | 278 | show tables |
| 48 | 5574 | select co.continent, co.name as country, co.population as copop, ci.name as capital, ci.population as cipop from country as co join city as ci on co.capital = ci.id where continent = 'Europe' |
+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

The mysql command line client is running from the sandbox with /home/kris/opt/mysql/8.0.25/bin/mysql --defaults-file=/home/kris/sandboxes/msb_8_0_25/my.sandbox.cnf world. Autocompletion for names is not disabled. So on client startup, the client invisibly runs show databases to learn the names of all databases for autocompletion. It then enters the world database as requested and runs show tables to learn the names of all tables in the world database.

Only then we come to the prompt and can paste our query.

We are only interested in thread_id = 48 AND event_id = 5574, our query.

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select *
 -> from events_statements_history
 -> where thread_id = 48 AND event_id = 5574\G
*************************** 1. row ***************************
 THREAD_ID: 48
 EVENT_ID: 5574
 END_EVENT_ID: 6624
 EVENT_NAME: statement/sql/select
 SOURCE: init_net_server_extension.cc:94
 TIMER_START: 61628458852000
 TIMER_END: 61631769329000
 TIMER_WAIT: 3310477000
 LOCK_TIME: 224000000
 SQL_TEXT: select co.continent, co.name as country, co.population as copop, ci.name as capital, ci.population as cipop from country as co join city as ci on co.capital = ci.id where continent = 'Europe'
 DIGEST: 409c336982f0d3d45c4b29da77fe83aed12c6043e8ce9771c11ec82ff347e647
 DIGEST_TEXT: SELECT `co` . `continent` , `co` . `name` AS `country` , `co` . `population` AS `copop` , `ci` . `name` AS `capital` , `ci` . `population` AS `cipop` FROM `country` AS `co` JOIN `city` AS `ci` ON `co` . `capital` = `ci` . `id` WHERE `continent` = ?
 CURRENT_SCHEMA: world
 OBJECT_TYPE: NULL
 OBJECT_SCHEMA: NULL
 OBJECT_NAME: NULL
 OBJECT_INSTANCE_BEGIN: NULL
 MYSQL_ERRNO: 0
 RETURNED_SQLSTATE: NULL
 MESSAGE_TEXT: NULL
 ERRORS: 0
 WARNINGS: 0
 ROWS_AFFECTED: 0
 ROWS_SENT: 46
 ROWS_EXAMINED: 92
CREATED_TMP_DISK_TABLES: 0
 CREATED_TMP_TABLES: 0
 SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
 SELECT_RANGE: 0
 SELECT_RANGE_CHECK: 0
 SELECT_SCAN: 0
 SORT_MERGE_PASSES: 0
 SORT_RANGE: 0
 SORT_ROWS: 0
 SORT_SCAN: 0
 NO_INDEX_USED: 0
 NO_GOOD_INDEX_USED: 0
 NESTING_EVENT_ID: NULL
 NESTING_EVENT_TYPE: NULL
 NESTING_EVENT_LEVEL: 0
 STATEMENT_ID: 125
1 row in set (0.00 sec)

The statement is statement/sql/select. It took 3310477000 picoseconds (3.31ms) to run. The sql_text is the full text of the statement (up to a cutoff point, in order to manage memory consumption). The parsed statement is called digest_text - identifiers are quoted, whitespace is normalized, actual constants are replaced with placeholders, and (not shown here) variable length WHERE ... IN (...) clauses are shortened with ellipses. This normalized digest is then hashed and produces an identifier for this group of identically formed statements, the digest. We learn about the number of rows looked at, 92 and sent, 46. No special flags indicating specific execution modes were set.

We can use the event_id to look even deeper:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_id,
 -> event_name,
 -> source,
 -> sys.format_time(timer_wait) as timer
 -> from events_stages_history
 -> where thread_id = 48
 -> and nesting_event_id = 5574
 -> order by event_id;
+-----------+----------+--------------------------------------+----------------------+-----------+
| thread_id | event_id | event_name | source | timer |
+-----------+----------+--------------------------------------+----------------------+-----------+
| 48 | 5610 | stage/sql/optimizing | sql_optimizer.cc:270 | 15.88 us |
| 48 | 5611 | stage/sql/statistics | sql_optimizer.cc:534 | 703.58 us | <- !!
| 48 | 5710 | stage/sql/preparing | sql_optimizer.cc:618 | 31.93 us |
| 48 | 5712 | stage/sql/executing | sql_union.cc:1126 | 2.26 ms | <- !!
| 48 | 6593 | stage/sql/end | sql_select.cc:586 | 1.86 us |
| 48 | 6594 | stage/sql/query end | sql_parse.cc:4542 | 1.93 us |
| 48 | 6596 | stage/sql/waiting for handler commit | handler.cc:1594 | 9.05 us |
| 48 | 6600 | stage/sql/closing tables | sql_parse.cc:4593 | 14.02 us |
| 48 | 6621 | stage/sql/freeing items | sql_parse.cc:5042 | 29.4 us |
| 48 | 6623 | stage/sql/cleaning up | sql_parse.cc:2252 | 1.17 us |
+-----------+----------+--------------------------------------+----------------------+-----------+
10 rows in set (0.00 sec)

These are the various execution stages of our statement - we select by thread_id and with the event_id of the statement, 5574 as a nesting_id, ordered by event_id. Time was consumed by the stage/sql/statistics phase, looking up table stats for a good execution plan, and then by the actual query execution in stage/sql/executing. The former took 0.7ms (703.58us), the latter 2.26ms.

We are interested in what took so long, specifically, so we look into waits for event_ids 5611 and 5712 - finding nothing, and also nothing particularly time-consuming:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_id,
 -> event_name,
 -> sys.format_time(timer_wait) as timer,
 -> nesting_event_type,
 -> nesting_event_id,
 -> operation
 -> from events_waits_history
 -> where thread_id = 48
 -> order by event_id;
+-----------+----------+------------------------------------------+-----------+--------------------+------------------+-----------+
| thread_id | event_id | event_name | timer | nesting_event_type | nesting_event_id | operation |
+-----------+----------+------------------------------------------+-----------+--------------------+------------------+-----------+
| 48 | 6614 | wait/synch/mutex/innodb/trx_mutex | 56.26 ns | STAGE | 6600 | lock |
| 48 | 6615 | wait/synch/mutex/innodb/trx_mutex | 48.64 ns | STAGE | 6600 | lock |
| 48 | 6616 | wait/synch/mutex/innodb/trx_mutex | 50.4 ns | STAGE | 6600 | lock |
| 48 | 6617 | wait/synch/mutex/innodb/trx_mutex | 46.88 ns | STAGE | 6600 | lock |
| 48 | 6618 | wait/synch/mutex/innodb/trx_mutex | 43.36 ns | STAGE | 6600 | lock |
| 48 | 6619 | wait/synch/mutex/innodb/trx_mutex | 45.12 ns | STAGE | 6600 | lock |
| 48 | 6620 | wait/synch/mutex/sql/LOCK_table_cache | 66.8 ns | STAGE | 6600 | lock |
| 48 | 6622 | wait/io/socket/sql/client_connection | 18.91 us | STAGE | 6621 | send |
| 48 | 6624 | wait/synch/mutex/sql/THD::LOCK_thd_query | 124.23 ns | STAGE | 6623 | lock |
| 48 | 6626 | wait/io/socket/sql/client_connection | NULL | WAIT | 6625 | recv |
+-----------+----------+------------------------------------------+-----------+--------------------+------------------+-----------+
10 rows in set (0.00 sec)

We can see I/O in a global summary, and the timings make sense in the context of the experiment:

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> object_type,
 -> object_schema,
 -> object_name,
 -> count_star,
 -> sys.format_time(max_timer_read) as `read`,
 -> sys.format_time(max_timer_write) as `write`,
 -> sys.format_time(max_timer_fetch) as `fetch`
 -> from table_io_waits_summary_by_table
 -> where object_schema = 'world'\G
*************************** 1. row ***************************
 object_type: TABLE
object_schema: world
 object_name: city
 count_star: 46
 read: 569.17 us
 write: 0 ps
 fetch: 569.17 us
*************************** 2. row ***************************
 object_type: TABLE
object_schema: world
 object_name: country
 count_star: 47
 read: 703.4 us
 write: 0 ps
 fetch: 703.4 us
...

But why are the I/O times not visible to us? That’s a bit unclear. My theory was that the reads happen asynchronously by some background thread. But a quick query shows no time spend on reader threads.

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> t.thread_id,
 -> t.name,
 -> t.type
 -> from threads as t join events_waits_summary_by_thread_by_event_name as e
 -> on t.thread_id = e.thread_id
 -> where e.max_timer_wait > 0
 -> and e.event_name = 'wait/io/file/innodb/innodb_data_file';
+-----------+---------------------------------------------+------------+
| thread_id | name | type |
+-----------+---------------------------------------------+------------+
| 1 | thread/sql/main | BACKGROUND |
| 9 | thread/innodb/io_write_thread | BACKGROUND |
| 10 | thread/innodb/io_write_thread | BACKGROUND |
| 11 | thread/innodb/io_write_thread | BACKGROUND |
| 12 | thread/innodb/io_write_thread | BACKGROUND |
| 13 | thread/innodb/page_flush_coordinator_thread | BACKGROUND |
| 33 | thread/innodb/clone_gtid_thread | BACKGROUND |
| 47 | thread/sql/one_connection | FOREGROUND |
| 48 | thread/sql/one_connection | FOREGROUND |
+-----------+---------------------------------------------+------------+
9 rows in set (0.00 sec)

We seem to be unable to attribute time spent loading data from disk to a specific thread, and we seem to be unable to account for the runtime of certain stages by looking at waits. That’s unexpected.

Memory only as summary

Diverse memory_% tables exist to track memory usage in the server. All of these tables are summary tables, there are no memory events tables that could trace memory usage per query. That might be okay.

sql
mysql [localhost:8025] {msandbox} (performance_schema) > show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
+-----------------------------------------+
5 rows in set (0.00 sec)

We can do interesting things with stuff such as memory_summary_by_thread_by_event_name, at least on our mostly idle server.

sql
mysql [localhost:8025] {msandbox} (performance_schema) > select
 -> thread_id,
 -> event_name,
 -> count_alloc,
 -> sum_number_of_bytes_alloc,
 -> high_number_of_bytes_used
 -> from memory_summary_by_thread_by_event_name
 -> where thread_id = 48
 -> and HIGH_NUMBER_OF_BYTES_USED > 0
 -> order by HIGH_NUMBER_OF_BYTES_USED desc limit 5;
+-----------+-------------------------------+-------------+---------------------------+---------------------------+
| thread_id | event_name | count_alloc | sum_number_of_bytes_alloc | high_number_of_bytes_used |
+-----------+-------------------------------+-------------+---------------------------+---------------------------+
| 48 | memory/sql/THD::main_mem_root | 22 | 1181008 | 613544 |
| 48 | memory/innodb/memory | 226 | 1059368 | 250032 |
| 48 | memory/sql/dd::objects | 205 | 47432 | 44648 |
| 48 | memory/innodb/ha_innodb | 26 | 35784 | 35784 |
| 48 | memory/innodb/fil0fil | 2 | 65600 | 32800 |
+-----------+-------------------------------+-------------+---------------------------+---------------------------+
5 rows in set (0.00 sec)

No EXPLAIN

Another thing that would be useful to collect from P_S is the actual execution plan of a query. But while we can explain a lot of statements by running EXPLAIN , and while we can EXPLAIN FOR CONNECT ..., the former is not the recorded execution plan, and the latter only works while the query is running. It’s the actual execution plan while the query executes, but it is not recorded.

Summary

A lot of information about query execution can be gathered from P_S. The query execution can be broken down in statements, stages and waits. Specifically, statements collect a lot of interesting quality flags. Stages can collect percentages of completion for long-running queries and give a general feel about where in the query execution time is spent. Waits should be able to attribute time to individual operations in the database server, but specifically for file I/O this seems to be more complicated, and I have not been able to solve it.

We can see waits for I/O summary tables, and we can see a lot of other statistical information in other summary tables. We can also use additional tables not covered here for debugging (for example DATA_LOCKS for locking behavior).

Memory instrumentation is interesting, but at this stage it is unclear to me if it is sufficient.

It seems to be really hard to record execution plans together with statements.

More experimentation with more complicated queries is necessary to see if it is possible to see things like sorting, temp files and similar operations, and attribute time to these operations.

The number of queries on P_S necessary to extract information about a single query is staggering, a 10:1 ratio. At least filters exist and are on by default, so that I do not have to hear my monitoring noise in my monitoring. That is good.

I could really use a single large JSON blob containing the entire package with performance data for a query, at once - one query to trace one query. That is, the information from transaction, statement, stages, waits, the execution plan and the memory consumption for a given transaction or statement, in one go.

First published on https://blog.koehntopp.info/ and syndicated here with permission of the author.

Learning Kubernetes Operators with Percona Operator for MongoDB

Edith Puclla — Thu, 13 Oct 2022 00:00:00 UTC

One of the topics that have resonated a lot for me since the first KubeCon I attended in 2018 is Kubernetes Operators.

The concept of Operators was introduced much earlier in 2016 by the CoreOS Linux development team. They were looking for a way to improve automated container management in Kubernetes.

What do we mean by a Kubernetes Operator?

We use the definition of CNCF. The Kubernetes project defines “Operator” simply: “Operators are software extensions that use custom resources to manage applications and their components“.

This means that among the applications that can be run on Kubernetes, there are applications that still require manual operations to manage them and complete the Kubernetes deployment cycle because Kubernetes itself can’t manage all these manual operations. It is what the Operators take care of, to automate those manual processes of the applications deployed in Kubernetes.

How can this be possible?

The Operators use/extend the Kubernetes API (this API has the basics needed for a user to interact with the Kubernetes cluster) and create custom resources to add new functionality according to the needs of an application to be flexible and scalable.

Once the creation of the custom resource is finished, it creates objects that can be managed using kubectl, as other default Kubernetes resources are managed, such as Deployments, Pods, etc.

Here we see the difference between the workflows with and without operators.

With Operators

Without Operators

The above illustration is based on a presentation by Sai Vennam.

It is time for an example! We will see how Percona Operator for MongoDB works.

Percona Operator for MongoDB contains everything we need to quickly and consistently deploy and scale Percona Server for MongoDB instances into a Kubernetes cluster on-premises or in the cloud.

You can find Percona Operator for MongoDB officially in:

Why does Percona Server for MongoDB (a database) need a Kubernetes Operator?

Kubernetes has been designed for stateless applications. Kubernetes in many cases doesn’t require operators for stateless applications because Kubernetes doesn’t need more automation logic. But stateful applications like databases do need operators because they cannot automate the entire process natively.

One of the main benefits of operators is the automation of repetitive tasks that are often handled by human operators, eliminating errors in application lifecycle management.

Installing MongoDB Percona Operator using GKE

This guide shows you how to deploy Percona Operator for MongoDB on Google Kubernetes Engine (GKE). We use GKE which takes less time to set up Kubernetes in Google Cloud just for the purpose of this demo. This demonstration assumes you have some experience with the platform. For more information on the GKE, see Kubernetes Engine Quickstart

As prerequisites, we need Google Cloud shell and Kubectl. You can find the installation guides for AWS and AZURE in the Percona documentation. Let´s start!

Creating a GKE cluster with three nodes.

bash
gcloud container clusters create my-cluster-name --project percona-product --zone us-central1-a --cluster-version 1.23 --machine-type n1-standard-4 --num-nodes=3

Now you should configure the command-line access to your newly created cluster to make kubectl able to use it.

gcloud container clusters get-credentials my-cluster-name --zone us-central1-a --project percona-product

Finally, use your Cloud Identity and Access Management [Cloud IAM] to control access to the cluster. The following command will give you the ability to create Roles and RoleBindings:

bash
kubectl create clusterrolebinding cluster-admin-binding --clusterrole cluster-admin --user $(gcloud config get-value core/account)

Install the Operator and deploy your MongoDB cluster

Create a new namespace called percona-demo-namespace

kubectl create namespace percona-demo-namespace

Set the context for the namespace

bash
kubectl config set-context $(kubectl config current-context) --namespace=percona-demo-namespace

Deploy the Operator

kubectl apply -f https://raw.githubusercontent.com/percona/percona-server-mongodb-operator/v1.13.0/deploy/bundle.yaml

The operator has been started, and you can deploy your MongoDB cluster:

kubectl apply -f https://raw.githubusercontent.com/percona/percona-server-mongodb-operator/v1.13.0/deploy/cr.yaml

When the process is over, your cluster will obtain the ready status. Check with:

 kubectl get psmdb.

Note: “psmdb” stands for Percona Server for MongoDB

Verifying the cluster operation

You will need the login and password for the admin user to access the cluster. Use kubectl get secrets command to see the list of Secrets objects

kubectl get secret my-cluster-name-secrets -o yaml

Bring it back to a human-readable form to MONGODB_DATABASE_ADMIN_PASSWORD and MONGODB_DATABASE_ADMIN_USER

We check the details of the Services, before testing the connection to the cluster

Run a Docker container with a MongoDB client and connect its console output to your terminal. The following command will do this, naming the new Pod percona-client:

bash
kubectl run -i --rm --tty percona-client --image=percona/percona-server-mongodb:4.4.16-16 --restart=Never -- bash -il

Now run mongo tool in the percona-client command shell using the login (which is normally clusterAdmin)

mongo "mongodb://clusterAdmin:Dgqjc1HElUvvGnH9@my-cluster-name-mongos.percona-demo-namespace.svc.cluster.local/admin?ssl=false"

Woolaaa! We have deployed MongoDB in Kubernetes using Operator, It works! :)

Now that you have the MongoDB cluster, you have full control to configure and manage MongoDB deployment from a single Kubernetes control plane, which means that you can manage MongoDB instances in the same way you manage default objects in Kubernetes like Deployments, Pods, or Services. For advanced configuration, topics see our guide Percona Operator for MongoDB.

Conclusion

Kubernetes Operators extend the Kubernetes API to automate processes that cannot be achieved natively with Kubernetes. This is the case for stateful applications like MongoDB. Percona develops Percona Operator for MongoDB that contains everything you need to quickly and consistently deploy and scale Percona Server for MongoDB instances into a Kubernetes cluster on-premises or in the cloud. You can try it on different cloud providers and tutorials for more advanced configurations.

You can find Percona Operator for MongoDB in Hacktoberfest! If you’re looking to improve your Kubernetes skills, this is a great project to start contributing to.

We also have a public roadmap of Percona Kubernetes Operators. If you have any feedback or want to draw our attention to a particular feature, feel free to be part of it and vote for issues! :)

Resources:

Percona Monitoring and Management 2.31 preview release

Taras Kozub — Mon, 19 Sep 2022 00:00:00 UTC

Percona Monitoring and Management 2.31 preview release

Hello folks! Percona Monitoring and Management (PMM) 2.31 is now available as a preview release.

Release notes can be found in here

Known issue

PMM-10735: OVF stopped working in a few minutes.

Percona Monitoring and Management server docker

docker tag:

perconalab/pmm-server:2.31.0-rc

Important: In order to use the DBaaS functionality during the Percona Monitoring and Management preview release, you should add the following environment variablewhen starting PMM server:

PERCONA_TEST_DBAAS_PMM_CLIENT=perconalab/pmm-client:2.31.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client release candidate tarball for 2.31 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

Creating a Kubernetes cluster on Amazon EKS with eksctl

Mario García — Tue, 13 Sep 2022 00:00:00 UTC

Amazon Elastic Kubernetes Service (Amazon EKS) is a managed Kubernetes service that makes it easy for you to run Kubernetes on AWS and on-premises. Kubernetes is an open-source system for automating deployment, scaling, and management of containerized applications. Amazon EKS is certified Kubernetes-conformant, so existing applications that run on upstream Kubernetes are compatible with Amazon EKS.

Getting started guides available in the AWS documentation explain two different procedures for creating an EKS cluster. One using eksctl, a simple command line utility for creating and managing Kubernetes clusters on Amazon EKS, and the other one using the AWS Management Console and AWS CLI.

Through this article, you will learn how to use eksctl for creating a Kubernetes cluster on Amazon EKS.

eksctl is a simple CLI tool for creating and managing clusters on EKS - Amazon’s managed Kubernetes service for EC2. It is written in Go, uses CloudFormation, and was created by Weaveworks.

For using eksctl, you must:.

Install kubectl.
Install AWS IAM Authenticator for Kubernetes.
Install AWS CLI.
Create a user with minimal IAM policies.

After running eksctl, you will get a cluster with default configuration:

Exciting auto-generated name
Two m5.large worker nodes
Use the official AWS EKS AMI
Default us-west-2 region
A dedicated VPC

Creating IAM user

Go to console.aws.amazon.com/iamv2, create a user group, named EKS, and attach the policies described in the minimal IAM policies section from the eksctl documentation.

These policies already exist, and you must attach them as they are.

AmazonEC2FullAccess (AWS managed)
AWSCloudFormationFullAccess (AWS managed)

In addition to previous policies, you must create:

EksAllAccess (click to expand)

{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": "eks:*",
 "Resource": "*"
 },
 {
 "Action": [
 "ssm:GetParameter",
 "ssm:GetParameters"
 ],
 "Resource": [
 "arn:aws:ssm:*::parameter/aws/*",
 "arn:aws:ssm:*::parameter/aws/*"
 ],
 "Effect": "Allow"
 },
 {
 "Action": [
 "kms:CreateGrant",
 "kms:DescribeKey"
 ],
 "Resource": "*",
 "Effect": "Allow"
 },
 {
 "Action": [
 "logs:PutRetentionPolicy"
 ],
 "Resource": "*",
 "Effect": "Allow"
 }
 ]
}

IAMLimitedAccess (click to expand)

{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": [
 "iam:CreateInstanceProfile",
 "iam:DeleteInstanceProfile",
 "iam:GetInstanceProfile",
 "iam:RemoveRoleFromInstanceProfile",
 "iam:GetRole",
 "iam:CreateRole",
 "iam:DeleteRole",
 "iam:AttachRolePolicy",
 "iam:PutRolePolicy",
 "iam:ListInstanceProfiles",
 "iam:AddRoleToInstanceProfile",
 "iam:ListInstanceProfilesForRole",
 "iam:PassRole",
 "iam:DetachRolePolicy",
 "iam:DeleteRolePolicy",
 "iam:GetRolePolicy",
 "iam:GetOpenIDConnectProvider",
 "iam:CreateOpenIDConnectProvider",
 "iam:DeleteOpenIDConnectProvider",
 "iam:TagOpenIDConnectProvider",
 "iam:ListAttachedRolePolicies",
 "iam:TagRole",
 "iam:GetPolicy",
 "iam:CreatePolicy",
 "iam:DeletePolicy",
 "iam:ListPolicyVersions"
 ],
 "Resource": [
 "arn:aws:iam:::instance-profile/eksctl-*",
 "arn:aws:iam:::role/eksctl-*",
 "arn:aws:iam:::policy/eksctl-*",
 "arn:aws:iam:::oidc-provider/*",
 "arn:aws:iam:::role/aws-service-role/eks-nodegroup.amazonaws.com/AWSServiceRoleForAmazonEKSNodegroup",
 "arn:aws:iam:::role/eksctl-managed-*"
 ]
 },
 {
 "Effect": "Allow",
 "Action": [
 "iam:GetRole"
 ],
 "Resource": [
 "arn:aws:iam:::role/*"
 ]
 },
 {
 "Effect": "Allow",
 "Action": [
 "iam:CreateServiceLinkedRole"
 ],
 "Resource": "*",
 "Condition": {
 "StringEquals": {
 "iam:AWSServiceName": [
 "eks.amazonaws.com",
 "eks-nodegroup.amazonaws.com",
 "eks-fargate.amazonaws.com"
 ]
 }
 }
 }
 ]
}

Replace , in both policies, with your AWS account ID, you can find it in the upper right corner, in the navigation bar. For other ways of getting your account ID, go to Your AWS account ID and its alias in the docs.

Add a new user, named eksctl, to the group previously created.

Don’t forget to download or copy your credentials, Access Key ID and Secret Access Key, as you will need them for setting up authentication.

Installing AWS CLI

On Linux, download the installer:

$ curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"

Unzip the installer:

$ unzip awscliv2.zip

And run the installer:

$ sudo ./aws/install

For instructions on how to install AWS CLI on other operating systems, go to Installing or updating the latest version of the AWS CLI in the documentation.

After installing AWS CLI, run the following command for setting up authentication locally:

$ aws configure --profile eksctl

It will ask you for your AWS credentials and default region.

Installing AWS IAM Authenticator

On Linux, run the following command:

$ curl -o aws-iam-authenticator https://s3.us-west-2.amazonaws.com/amazon-eks/1.21.2/2021-07-05/bin/linux/amd64/aws-iam-authenticator

Apply execute permissions to the binary:

$ chmod +x ./aws-iam-authenticator

Create a folder in your $HOME directory and add it to the $PATH variable:

$ mkdir -p $HOME/bin && cp ./aws-iam-authenticator $HOME/bin/aws-iam-authenticator && export PATH=$PATH:$HOME/bin

Add $HOME/bin to your .bashrc:

$ echo 'export PATH=$PATH:$HOME/bin' >> ~/.bashrc

For Mac and Windows, check Installing aws-iam-authenticator in the documentation.

Installing kubectl

NOTE: From the documentation - You must use a kubectl version that is within one minor version difference of your Amazon EKS cluster control plane. For example, a 1.22 kubectl client works with Kubernetes 1.21, 1.22, and 1.23 clusters.

As of today, the latest version of Kubernetes used by eksctl is 1.21. Run the following command for installing the corresponding version of kubectl:

$ curl -o kubectl https://s3.us-west-2.amazonaws.com/amazon-eks/1.21.2/2021-07-05/bin/linux/amd64/kubectl

Apply execute permissions to the binary:

$ chmod +x ./kubectl

Copy the binary to $HOME/bin:

$ cp ./kubectl $HOME/bin/kubectl

If you’re using another version of Kubernetes, check Installing or updating kubectl in the documentation, where you can also find instructions for other operating systems.

Installing eksctl and creating a Kubernetes cluster

Download the binary and copy it to /usr/local/bin:

$ curl --silent --location "https://github.com/weaveworks/eksctl/releases/latest/download/eksctl_$(uname -s)_amd64.tar.gz" | tar xz -C /tmp

$ sudo mv /tmp/eksctl /usr/local/bin

On Mac and Windows, you can install eksctl following the instructions in the GitHub repository.

Once installed, create a cluster with default configuration, and authenticate to AWS using IAM user created previously.

$ eksctl create cluster --profile eksctl

NOTE: From the documentation - That command will create an EKS cluster in your default region (as specified by your AWS CLI configuration) with one managed nodegroup containing two m5.large nodes.

For a cluster with custom configuration, create a config file, named cluster.yaml, with the following content:

yaml
apiVersion: eksctl.io/v1alpha5
kind: ClusterConfig

metadata:
 name: basic-cluster
 region: eu-north-1

nodeGroups:
 - name: ng-1
 instanceType: m5.large
 desiredCapacity: 10
 volumeSize: 80
 ssh:
 allow: true # will use ~/.ssh/id_rsa.pub as the default ssh key
 - name: ng-2
 instanceType: m5.xlarge
 desiredCapacity: 2
 volumeSize: 100
 ssh:
 publicKeyPath: ~/.ssh/ec2_id_rsa.pub

Run eksctl to create the cluster as follows:

$ eksctl create cluster -f cluster.yaml --profile eksctl

While running, eksctl will create the cluster and all the necessary resources.

It will take a few minutes to complete. After the command is executed, you can go to us-east-1.console.aws.amazon.com/eks/home?region=us-east-1#/clusters to see the cluster.

Don’t forget to replace us-east-1 in the URL, if your default region is different.

Cluster credentials can be found in ~/.kube/config. Try kubectl get nodes to verify that this file is valid, as suggested by eksctl.

If, for any reason, you need to delete your cluster, just run:

$ eksctl delete cluster --name=ferocious-painting-1660755039 --profile eksctl

Replacing name with corresponding value.

You’ve created your first Kubernetes cluster using eksctl. Check the documentation for more information on how to create and manage clusters.

Running PMM with Docker on Ubuntu 20.04

Edith Puclla — Fri, 05 Aug 2022 00:00:00 UTC

I started at Percona a few weeks ago and was looking for a quick way to learn about PMM (Percona Monitoring and Management), which is one of my favorite technologies within Percona to monitor the health of our database infrastructure, explore new patterns in the database behavior, manage and improve the performance of our databases, all with customizable dashboards and real-time alerts using Grafana and VictoriaMetrics.

The best of all is that PMM is Open Source, you can check the PMM repository in case you want to contribute.

There are many flavors for PMM installation, here I will describe the steps to install PMM on Ubuntu 20.04, using Docker for PMM Server on an Amazon EC2 instance.

This image summarizes our goal.

Requirements

An Amazon EC2 instance with Ubuntu 20.04
- This instance is configured with a Security Group with TCP port 443 open.
Docker
- You can install Docker by following this guide.
- Manage Docker as a non-root user: sudo usermod -aG docker $USER
MySQL
- I am using Percona Server for MySQL from Percona apt repository

Installing PMM Server with Docker

Download PMM server Docker image

docker pull percona/pmm-server:2

Create the data volume container

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

Run PMM server container

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

Verify the creation of the container.

docker ps

Start a web browser and in the address bar enter the IP address of the PMM server host: https://:443/. For example, https://172.31.53.46. If you are running on your local machine use https://localhost:443/. Woohoo! We have a PMM Server running and we can see our dashboard!

Note: Some browsers may not trust the self-signed SSL certificate when you first open the URL. If this is the case, Chrome users may want to type thisisunsafe to bypass the warning.

The user and password are “admin” and “admin”, It will ask you to change the password after login in for the first time, for this demo I will use admin2020 as a password. We will use these credentials to register the node in PMM Server later.

Until now we have only PMM Server. To monitor a database, we need a PMM client.

Installing PMM client

PMM Client is a collection of agents and exporters that run on the host being monitored. Let´s install it using the repository package.

Download Percona Repo Package

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

Install Percona Repo Package

bash
sudo apt install ./percona-release_latest.$(lsb_release -sc)_all.deb

Update apt cache

sudo apt update

Install Percona Monitoring and Management Client

sudo apt install pmm2-client

Checking the installation. We will use pmm-admin in the next steps.

sudo pmm-admin -v

Creating a user for monitoring

Let’s create a user in MySQL.

Login in MySQL for use the command-line: mysql -uroot -p
Create a “pmm” user with “welcOme1!” As a password

bash
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'welcOme1!' WITH MAX_USER_CONNECTIONS 10;

Give “pmm” user with specific permission to monitor the database

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

Checking if the user was created correctly with the respective permissions, use

bash
 show grants for 'pmm'@'localhost';

Connect Client to Server

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin2020@172.17.0.1:443

Note: I am using 172.17.0.1 because this is the private IP where the PMM Server is running. You can get this IP by entering the docker container and typing “hostname -I”

After registering your client with the server you will see this information:

Check if the node was registered

pmm-admin inventory list nodes

A new node should appear in the list which is pmm-server

Adding a MySQL Database to monitoring

Use pmm-admin to register the database with the user we created in MySQL

bash
sudo pmm-admin add mysql --username=pmm --password=welcOme1! --query-source=perfschema

In the dashboard, we will see that our node and database are registered and ready to be monitored by PMM.

That’s it! :) We learned how to monitor our databases for free with Percona Monitoring Database (PMM). Additionally, you can go to the next level by registering a PMM instance with Percona Platform and receive more information.

I hope you’ve enjoyed this tutorial, and if you need help following it, feel free to contact the Percona team support. We will be happy to help.

Percona Monitoring and Management 2.29.0 Preview Release

Taras Kozub — Tue, 12 Jul 2022 00:00:00 UTC

Percona Monitoring and Management 2.29.0 Preview Release

Hello folks! Percona Monitoring and Management (PMM) 2.29.0 is now available as a Preview Release.

We encourage you to try this PMM Preview Release in testing environments only, as these packages and images are not fully production-ready. The final version is expected to be released through the standard channels in the coming week.

Release Notes can be found in here

Known issues

PMM-10312: Metrics are not displayed on Experimental Overview and Summary dashboards

Percona Monitoring and Management server docker

docker tag:

perconalab/pmm-server:2.29.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client Release Candidate tarball for 2.29.0 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

PMM2-Server-2.29.0.ova

AMI

ami-0e68224439dd6f200

Percona Monitoring and Management 2.28.0 Preview Release

Taras Kozub — Thu, 05 May 2022 00:00:00 UTC

Percona Monitoring and Management 2.28.0 Preview Release

Hello folks! Percona Monitoring and Management (PMM) 2.28.0 is now available as a Preview Release.

Release Notes can be found in here

Percona Monitoring and Management server docker

docker tag:

perconalab/pmm-server:2.28.0-rc

Percona Monitoring and Management client package installation

Download the latest pmm2-client Release Candidate tarball for 2.28.0 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

Artifact: PMM2-Server-2.28.0.ova

AMI

Artifact: ami-09ce0dc58b2f81889

The MySQL Workshop Book Review

David Stokes — Tue, 03 May 2022 00:00:00 UTC

Good books on MySQL for beginners are rare and excellent ones are even rarer. I often get requests from novices starting with MySQL or intermediates looking to level up on recommendations on books targeted at their level. The MySQL Workshop (Amazon link) by Thomas Pettit and Scott Cosentino is a must buy for those two groups, or those of us who would like a handy reference.

This is a great book and I recommend getting a copy regardless of your MySQL expertise.

The Basics

At seven hundred pages, this book has a wide scope that starts with background concepts like data normalization, proceeds into creating databases, SQL, and administration. And there are sections on programming with Node.js, working with Microsoft applications, loading data, DBA tasks, and logical backups. There are exercises at the end of the chapters with solutions at the end of the book.

Writing such a book is a tremendous task and the authors need to be applauded as they have produced a great book.

The Nitty-Gritty

MySQL is a complex product and introducing concepts with a fresh approach is hard to do but this book does it consistently. Complex topics like creating functions are explained thoroughly without being bogged down in minute details.

Does it cover everything? Nope, and no book under a few thousand pages will ever do that (while keeping pace with product development). There are minor omissions like constraint checks which a still fairly new but I would like to point you to the section on triggers that is the clearest explanation on the subject I have found.

The writing style is concise, the formatting easy on the eyes, and I am sure the book will be very popular.

Liquibase Data is Git for Databases

Robert Reeves — Mon, 25 Apr 2022 00:00:00 UTC

Author’s Note: Robert will be demoing Liquibase Data at Percona Live 2022 on Wednesday, May 18 at 11:50am. Add this presentation to your schedule.

Git is an amazing tool for collaboration — developers can work together to build better software faster. However, the usual Git workflow neglects the database. With Liquibase Data we’re bringing git to the database so you can easily version containerized databases, share changes with team members, store versions in remote locations, and tag versions.

The Vanilla Git Workflow

The standard Git workflow is simple. A developer can git init to create a local repository. Next, after making changes, git commit creates a local version. Then, the developer pushes to a remote branch using git push. Finally, another developer can git pull to see the new code updates.

Liquibase Data Workflow

We created the same Git workflow in Liquibase Data. Using the Liquibase Data extension, Liquibase users can initialize a new database in a Docker container using liquibase data run. Which databases? ALL of them. All it requires is a database Docker image that has a volume mount for the data. Liquibase takes it from there. If you already run your development databases via Docker, you will find that Liquibase Data parallels the docker run command.

Here’s what you’ll be able to do:

Clone from remote repositories
Make changes to the database
Commit and push your changes to share with team members
Tag commits
Easily view the difference between two database commits to identify changes

Our team thinks this will be useful for test data management and supporting developer database workflows.

Just like you commit after changing your code, you can do the same with Liquibase Data. After you add data to your database or change the schema, run liquibase data commit. Commands such as push, remote, and log are also available.

Easily Compare Databases

Determining what has changed in your database schema can be very difficult. Liquibase Data makes it simple to find schema differences between commits using the diff command. With Liquibase Data, the required database starts automatically for you to create the diff.

Watch Liquibase Data Demos

Robert Reeves, CTO of Liquibase, demonstrates how to quickly provision a developer instance of MongoDB, make changes to MongoDB, and then commit the change. You’ll see how easy it is to roll your changes backward and forward.

Check out our other Liquibase Data demos for Oracle and SQL Server! Liquibase Data works with ANY database in a Docker Container.

Try Liquibase Data We think Liquibase Data will be helpful for developers sharing databases among team members. Just imagine — you’ll be able to share datasets you’re working on early in the process and share a separate one later in the process. The distribution of valid test data amongst Dev and QA will speed testing cycles and help find bugs sooner.

Of course, we want to hear from you! Tell us what you would like to see in Liquibase Data and share with us how you are using it. Our Open Beta program is a great way to experience the benefits and give us input to make it work even better. We have a tutorial that will walk you through, step by step, how to use Liquibase Data. Along the way, you will have an opportunity to provide your thoughts.

Finally, all of us at Liquibase thank you for your support over the past 15 years of open source greatness. We could not have done it with you. And, the best is yet to come!

A Quick Guide To Austin For Percona Live 2022 Attendees

David Stokes — Mon, 11 Apr 2022 00:00:00 UTC

Percona Live returns again to Austin May 16th through the 18th will find the city vibrant, charming, and weird. The semi-official motto for the city is ‘Keep Austin Weird’ and during your visit you will indeed see many of the residents working hard to do just that. Not in a bad way. Austin is at the intersection of so many cultural, artistic, and lifestyle modes that there are a fair amount of many different things happening at the same time to ensure that any dull moments you have will have to be an active choice on your part.

The following is a quick guide for those new to Austin or looking for activities for the days before or after the show

What To Wear?

Austin in May averages 86F/30C (which is better than the August 96F/35C) so shorts, t-shirts, and comfortable shoes are a must. Bring sunscreen and water if you plan to spend time outdoors.

What to Eat?

The two main choices are barbeque and Tex-Mex. But you will find any type of cuisine you desire either in the restaurants or the food trucks (mostly on South Congress Street but found throughout the city). You may see many celebrities but remember in Austin that Matthew McConaughey is just another professor at the University of Texas and that Elon Musk builds pickup trucks.

BBQ?

Barbecue is almost considered a religion in Texas and you will find many recommendations on where to go (see https://austin.eater.com/maps/best-barbecue-austin-restaurants) but everyone has their favorite. Major competitions are run each year to determine who is the best. My personal favorite is the Salt Lick has two locations that are sadly out of Austin proper and they are known for moderating the heat of their barbeque pits by using pecans which adds a unique flavor. And they do have a location at the airport too; the food is good but the ambience is lacking with all the flight announcements.

But the other places are pretty good too. Stubb’s, Franklin, Black’s are all excellent. If you do see a line at another place where salivating people are somewhat impatiently waiting to order, then join the queue. And it is okay to salivate too.

Big hint: If you are not used to Texas Sweet Tea start with half sweetened and half unsweetened until your gums and your dentist have time to adjust.

Tex-Mex

This style of food is a tasty combination of Chihuahuan Mexican food and frontier based ingredients with lots of cheese and chili. What started as simple staple foods for settlers on the frontier made from the available commodities has evolved into a tasty treat.
Chuy’s original restaurant is a top pick and features theme rooms. Sadly we have already missed the birthday of Elvis Presley where all who dress like the King or his wife Priscilla dine free in their Elvis room. For the less decor oriented try Matt’s El Rancho.

Big tip: You will generally get big portions, especially if you order fajitas or margaritas.

For breakfast, go to Snooze AM for the pineapple upside pancakes or an omelet.

Museums

The Bob Bullock Museum is at the state capital building and is the state’s official history museum. The Museum of the Weird is just as the name implies and, while not official, provides a look into the odder parts of Austin. Not too far away is the Alamo in San Antonio (nearby the Alamo is the Buckhorn Saloon which actually has two floors of oddities that are weirder than the Museum of the Weird). The Museum of the Pacific War in Fredericksburg is a must for history fans. The Contemporary Austin is great for art fans while the Texas Toy museum will appeal to your inner child.

Outdoors Activity

Swim in spring fed Barton Springs, ride the bike trails, and hike your feet off before you rent a paddleboard to tour Lake Austin. Lots of things for the physically active with appealing hikes and you may actually run into an Armadillo.

You can rent inner tubes to float the nearby Guadalupe or Comal Rivers. Rent another inner tube for your cooler of drinks. Or visit the Schlitterbahn water park. All three are a short drive away and worth an extra day on your trip for time to spend with family or friends.

Big hint: Stay hydrated as the heat is deceiving.

Other Activities

Gruene (pronounced ‘green’) Hall is the oldest dancehall in Texas and is the place where many top stars got their starts. Currently there are no acts scheduled during the time of Percona Live (well, I expect they will be at Percona Live learning about databases!) but fans of ZZ Top, George Strait, Willy Nelson, or Greg Allman will relish the history of the place before heading to the Grist Mill for a meal. The dance hall itself has not changed much since being built in 1878 and they will open the side flaps when the dancers need fresh air.

Sixth Street is the live music capital of Texas and you will find any genre there. This is where Stevie Ray Vaughn rose to fame and where Willie Nelson rebuilt his career after leaving Nashville.Ear plugs recommended but optional.

Yes, the bats do fly out from under the Congress Street bridge at sunset which is amazing when millions of them fly out. The last physical Percona Live they were shy and only a few appeared. I assume that they were intimidated by having so many DBAs nearby.

Austin is an awesome town and not just for Percona Live itself. I have only touched the proverbial iceberg tip on things to see and do there. If you have questions, find me at Percona Live or email me at david.stokes@percona.com and hopefully we can try one of the local craft brews together.

Percona Monitoring and Management 2.27.0 Preview Release

Taras Kozub — Fri, 08 Apr 2022 00:00:00 UTC

Percona Monitoring and Management 2.27.0 Preview Release

Percona Monitoring and Management 2.27.0 is now available as a Preview Release.

PMM team really appreciates your feedback!

Known issues:

PMM-9797 - Wrong Plot on Stat Panels for DB Conns and Disk Reads at Home Dashboard
PMM-9820 - QAN page disappeared after upgrade via UI

Release Notes can be found here

PMM server docker

docker tag:

perconalab/pmm-server:2.27.0-rc

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.27.0 by this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable percona testing

install pmm2-client package for your OS via package manager.

OVA

Artifact: PMM2-Server-2.27.0.ova

AMI

Artifact: ami-05592e370cca655b9

Please also check out our Engineering Monthly Meetings https://percona.community/contribute/engineeringmeetings/ and join us on our journey in OpenSource! Contact us in https://forums.percona.com/.

5 Steps to Improve Performance of Default MySQL Installation

Thu, 27 Jan 2022 00:00:00 UTC

Let’s say you have a fresh MySQL installation. Are there any possible steps to improve performance right away? Yes, there are!

Recently, Marcos Albe (Principal Support Engineer, Percona) did an online tuning on the MySQL Meetup hosted by Matt Yonkovit (Head of Open Source Strategy, Percona). Here are some steps you can consider to make your fresh MySQL installation to run better right from the start.

So, we have a very basic default MySQL installation with some workload. It is connected to PMM, the slow query log is turned on. But it is largely unconfigured. Here is what actions Marcos considered to take to set up a new system to make sure that it’s actually set up from the beginning to reasonable defaults. Do some reactive configuration - go through the workload, observe bottlenecks and then configure to avoid bottlenecks

Step 1. Rate Limit for Slow Queries

Go to MySQL Summary Dashboard In PMM, find your instance and set a rate limit instead of setting low query time to zero. Once we get to the 3000-4000 queries per second, these might start impacting performance in a way that is going to show in the latency papers. The thing is that while it is important to collect as many query details as possible, you don’t want to collect too many because it can impact performance and have the opposite effect of what you’re trying to do.

Step 2. Spikes

Go down and turn the metrics off and on from data series from each of the graphs to be able to see the spikes on each magnitude. It allows you to find bad query patterns, under-dimensional or over-dimensional things.

Think of it as workload being the light, MySQL being the prism and the metrics being the reflection of the light.

Doing this, you can suggest different changes, like trying a slightly larger buffer size or increasing the thread cache. And as you go through the metrics, look at the values in the configuration, at the workload, and the actual work to find out if your hypotheses is correct.

Step 3. Buffer Pool Size

Increase the buffer pool size. It is probably the most used and most recommended setting.

Step 4. Redo Log Size

Increase the redo log size and restart the instance. Make the redo log file as large as reasonably possible. What is reasonable? Reasonable is an amount of time that will allow you to write at that rate for the duration of your big workload. The purpose is to allow more pages during the heavy write periods. The only thing you could fear here is the recovery time. You should do some testing to see if the recovery times are acceptable, just like you do for backups. And then if the time of recovery is unacceptable, you should consider having a HA setup, semi-synchronous or virtually synchronous setup, where you can failover to the next instance when this one crashes. Also, you could get faster drives, or you could try to convince your developers to write less

Step 5. InnoDB IO Capacity

Set InnoDB IO capacity to 200 unless you have proof you need more. Otherwise, you’re just forcing the flushing to happen too early. The thing is that you want to keep dirty pages. Dirty pages are the performance optimization. Imagine that you update the views counter for a popular video 100 times per second. If you have a very high capacity, you will probably write that road 50 times per second to disk. If you have a smaller capacity, you will probably write it once every few seconds. And then you’re actually only doing one write for hundreds of updates because all the rest were in memory and on the redo log.

Conclusion

If you want to watch the video of the meetup and see how Marcos tuned the installation, it is always available on the Community Website. The meetups for MySQL, PostgreSQL, PMM, and MongoDB are regularly live-streamed. Stay tuned to announcements and feel free to join!

PMM 2.26.0 Preview Release

Rasika Chivate — Thu, 27 Jan 2022 00:00:00 UTC

PMM 2.26.0 Preview Release

Percona Monitoring and Management 2.26.0 is now available as a Preview Release.

PMM team really appreciates your feedback!

We encourage you to try this PMM Preview Release in testing environments only, as these packages and images are not fully production-ready. The final version is expected to be released through the standard channels in the coming week.

Release Notes can be found here

PMM server docker

docker tag: perconalab/pmm-server:2.26.0-rc

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.26.0 from this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable original testing

install pmm2-client package for your OS via package manager.

OVA

Artifact: PMM2-Server-2022-01-27-1524.ova

Please also check out our Engineering Monthly Meetings https://percona.community/contribute/engineeringmeetings/ and join us on our journey in OpenSource! Contact us at community-team@percona.com .

2.25.0 Preview Release

Taras Kozub — Fri, 03 Dec 2021 00:00:00 UTC

2.25.0 Preview Release

Percona Monitoring and Management 2.25.0 is now available as a Preview Release.

PMM team really appreciates your feedback!

Release Notes can be found here

PMM server docker

docker tag: perconalab/pmm-server:2.25.0-rc

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.25.0 from this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable original testing

install pmm2-client package for your OS via package manager.

OVA

Artifact: PMM2-Server-2021-12-03-1855.ova

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/server/aws.html

AMI

Artifact id: ami-04ba67eb15e6e089e

Please also check out our Engineering Monthly Meetings https://percona.community/contribute/engineeringmeetings/ and join us on our journey in OpenSource! Contact us at community-team@percona.com .

2.24.0 Preview Release

Taras Kozub — Thu, 11 Nov 2021 00:00:00 UTC

2.24.0 Preview Release

Percona Monitoring and Management 2.24.0 is now available as a Preview Release.

PMM team really appreciates your feedback!

Release Notes can be found here

PMM server docker

docker tag: perconalab/pmm-server:2.24.0-rc

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.24.0 from this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable original testing

install pmm2-client package for your OS via package manager.

OVA

Artifact: PMM2-Server-2021-11-10-1310.ova

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/server/aws.html

AMI

Artifact id: ami-03db8ae0f3ef49618

Please also check out our Engineering Monthly Meetings https://percona.community/contribute/engineeringmeetings/ and join us on our journey in OpenSource! Contact us Contact us at community-team@percona.com .

The Errant GTID

Wayne Leutwyler — Mon, 08 Nov 2021 00:00:00 UTC

Part 1

What is a GTID? Oracle/MySQL define a GTID as "A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the source). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology."

An errant transaction can make promotion of a replica to primary very difficult.

An errant transaction is BAD. Why is it bad? The errant transaction could still be in the replicas binlog so when it becomes the new primary these event will get sent to other replicas causing data corruption or breaking replication.

Its easy to prevent errant transaction.

read_only = ON in the replicas my.cnf
Disable binlogs when you need to perform work on a replica. set session sql_log_bin = 'off'; before your work on replica. set session sql_log_bin = 'on'; when your work is complete.

Find and correct errant transaction

How do you correct an errant transaction? Compare the gtid_executed on the primary and replica. Identify the errant transaction on the replica and then apply that transaction to the primary.

I will show you one method in the steps below.

On the replica run show variables like 'gtid_executed'

You will receive output similar to this:

mysql_replica> show variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
 Value: 858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2
1 row in set (0.00 sec)

Make note of the gtid_executed value. You will need this to check if you have an errant transaction.

On the primary run show variables like 'gtid_executed'

You will receive output similar to this:

mysql_primary> show variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
 Value: 858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2
1 row in set (0.00 sec)

Make note of the gtid_executed value. You will need this to check if you have an errant transaction.

We need to determine if the replica has any errant transaction’s. We will use the function: ‘gtid_subset’ to compare the executed GTID set from replica and primary.

mysql_replica> select gtid_subset('858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2','858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2') as subset;
+--------+
| subset |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

Subset = 1 tells us we have no errant transactions.

Now we need to introduce an errant transaction in to the replica. Let’s do something simple by creating a new database.

mysql_replica> create database community;
Query OK, 1 row affected (0.01 sec)

Lets repeat step 1 from above.

mysql_replica> show variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
 Value: 858d4d54-3fe1-11ec-a7e8-080027ae8b99:1-2,
a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2
1 row in set (0.00 sec)

We will repeat step 3 using the new gtid_executed from the replica and the original gtid_executed from the primary.

mysql_replica> select gtid_subset('858d4d54-3fe1-11ec-a7e8-080027ae8b99:1-2,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2','858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2') as subset;
+--------+
| subset |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)

Subset = 0 tells us that this replica has errant transactions.

Now we need to determine the errant transaction. We will need to subtract the replica executed GTID from the primary executed GTID. To do this we will use the gtid_subtract function.

`mysql_replica> select gtid_subtract('858d4d54-3fe1-11ec-a7e8-080027ae8b99:1-2,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2','858d4d54-3fe1-11ec-a7e8-080027ae8b99:1,
 '> a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2') as errant;
+----------------------------------------+
| errant |
+----------------------------------------+
| 858d4d54-3fe1-11ec-a7e8-080027ae8b99:2 |
+----------------------------------------+
1 row in set (0.00 sec)

Now we have our errant transaction from the replica 858d4d54-3fe1-11ec-a7e8-080027ae8b99:2

Repair the issue

Now let’s move to the primary.

Once on the primary we want to insert a pseudo transaction to resolve the errant transaction from the replica.

mysql_primary> set gtid_next='858d4d54-3fe1-11ec-a7e8-080027ae8b99:2';
Query OK, 0 rows affected (0.00 sec)

mysql_primary> begin;
Query OK, 0 rows affected (0.00 sec)

`mysql_primary> commit;
Query OK, 0 rows affected (0.00 sec)

`mysql_primary> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

We can compare the GTID executed again from the replica and primary.

Primary:

mysql_primary> show variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
 Value: 858d4d54-3fe1-11ec-a7e8-080027ae8b99:1-2,
a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2
1 row in set (0.00 sec)

Replica:

mysql_replica> show variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
 Value: 858d4d54-3fe1-11ec-a7e8-080027ae8b99:1-2,
a6b3751e-3fd3-11ec-a4f5-080027ae8b99:1-2
1 row in set (0.00 sec)`

Note that both values match. We have repaired the errant transaction from the replica to the primary.

Now we need to take care of the replica that had the errant transaction. We need to flush and purge the binary logs. Use the commands below to find the current binary file, and then flush and purge. Remember to be on the replica.

show binary logs;
FLUSH LOGS;
PURGE BINARY LOGS TO 'binlog.00000x';

Thats it. You have fixed the errant transaction. This was a rather simple example of an errant GTID. I will be doing part 2 that will look at more complexed examples.

Referance Information

2.23.0 Preview Release(Updated!)

Taras Kozub — Fri, 15 Oct 2021 00:00:00 UTC

Update

Percona Monitoring and Management 2.23.0 is now available as a Public Release!

Release notes for Percona Monitoring and Management 2.23.0 Public Release can be found here

2.23.0 Preview Release

Percona Monitoring and Management 2.23.0 is released today as a Preview Release.

PMM team really appreciates your feedback!

We encourage you to try this PMM Internal Release in testing environments only, as these packages and images are not fully production-ready. The final version is expected to be released through the standard channels in the coming week.

Known issue:

PMM-8983 - DBaaS: PXC cluster is displayed as active after suspend

Release notes: Release Notes Preview found here

PMM server docker

docker tag: percona/pmm-server:2.23.0

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.23.0 from this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable original testing

install pmm2-client package for your OS via package manager.

OVA

https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/server/aws.html

Artifact: PMM2-Server-2021-10-14-2120.ova

AMI

Artifact id: ami-047173e7a14c3f287

Please also check out our Engineering Monthly Meetings https://percona.community/contribute/engineeringmeetings/ and join us on our journey in OpenSource! Contact us at community-team@percona.com .

2.22.0 Preview Release

Denys Kondratenko — Thu, 16 Sep 2021 00:00:00 UTC

2.20.0 Preview Release

Percona Monitoring and Management 2.22.0 is released today as a Preview Release.

PMM team really appreciates your feedback!

Known issue:

PMM-8829 - “Missing Listen Port” error for external exporters after restart

Release notes: Release Notes Preview found here

PMM server docker

https://hub.docker.com/layers/perconalab/pmm-server/2.22.0-rc/

docker tag: perconalab/pmm-server:2.22.0-rc

PMM client package installation

Download the latest pmm2-client Release Candidate tarball for 2.22.0 from this link.

If you want to install pmm2-client package, please enable testing repository via Percona-release:

percona-release enable original testing

install pmm2-client package for your OS via package manager.

OVA