If you’re using MySQL’s Performance Schema you may use “query digests” as IDs to identify specific query patterns in the events_statements_summary_by_digest Performance Schema Table.

You might assume these hashes are stable between different versions, so, for example, when upgrading from MySQL 5.7 to MySQL 8, you can compare the query response time and other execution details for the same hashes as part of your upgrade process and have confidence that queries run the same (or better) after the upgrade.   Unfortunately, you can’t.

For some reason, the hashing algorithm is different between MySQL 5.6,  MySQL 5.7, and MySQL 8 even for the most trivial queries:

MySQL 5.6

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';
+----------------------------------+-------------+
| digest                           | digest_text | …

Percona Server for MySQL 8.0.18 ships all functionality to run Group Replication and InnoDB Cluster setups, so I decided to evaluate how it works and how it compares with Percona XtraDB Cluster in some situations.

For this I planned to use three bare metal nodes, SSD drives, and a 10Gb network available for in-between nodes communication, but later I also added tests on three bare metal nodes with NVMe drives and 2x10Gb network cards.

To simplify deployment, I created simple ansible scripts.

Load Data

The first initial logical step is to load data into an empty cluster, so let’s do this with our sysbench-tpcc …

In a previous post Profiling Software Using perf and Flame Graphs, we took a look at how to generate Flame Graphs from perf outputs. In this short follow-up, we will use Flame Graphs to process outputs from pt-pmp. Flame Graphs were not originally thought of for this purpose, but since pt-pmp outputs are similar to the folded samples (the result of using stackcollapse-perf.pl), we can take advantage of its functionality.

There are mainly two differences in the pt-pmp outputs:

• The first column is the number of threads with that same backtrace, and
• The function names are separated by a comma instead of a semicolon

Additionally, pt-pmp will print the date in the first line, so we’ll need to trim that part, too.

Lastly, before …

In the many years we have used MySQL, we got accustomed to the fact that upgrades from MySQL 5.7.11 to 5.7.12 was a minor event. This meant that if something was going wrong, we could roll back the binaries and be happy again. 

From MySQL 8, this is no longer true. Any upgrade, even minor, is seen as irreversible. (This is valid for Percona Server for MySQL as well.)

Say we have MySQL 8.0.17 and we upgrade to 8.0.18. In our MySQL log at the start, we will have this:

[System] [MY-010116] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld (mysqld 8.0.18) starting as process 13242
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' started.
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' completed.
…
[System] [MY-010931] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld: …

If you are thinking of using ProxySQL in our Percona XtraDB Cluster environment, I’ll explain how to use ProxySQL 2 for failover tasks.

How to Test

ProxySQL uses the “weight” column to define who is the WRITER node. For this example, I’ll use the following list of hostnames and IPs for references:

+-----------+----------------+
| node_name | ip             |
+-----------+----------------+
| pxc1      | 192.168.88.134 |
| pxc2      | 192.168.88.125 |
| pxc3      | 192.168.88.132 |
+-----------+----------------+

My current WRITER node is the “pxc1” node, but how can I see who is the current WRITER? It’s easy, just run the following query:

proxysql> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

This is the output: …

The importance of having periodic backups is a given in Database life. There are different flavors: binary ones (Percona XtraBackup), binlog backups, disk snapshots (lvm, ebs, etc) and the classic ones: logical backups, the ones that you can take with tools like mysqldump, mydumper, or mysqlpump. Each of them with a specific purpose, MTTRs, retention policies, etc.

Another given is the fact that taking backups can be a very slow task as soon as your datadir grows: more data stored, more data to read and backup. But also, another fact is that not only does data grow but also the amount of MySQL instances available in your environment increases (usually). So, why not take advantage of more MySQL instances to take logical backups in an attempt to make this operation faster?

Distributed Backups (or Using all the Slaves Available)

…

Starting with Percona Server for MySQL 8.0.17, Percona Server ships with a data masking plugin, using the same API as the MySQL Enterprise Masking and De-identification feature. This plugin was developed by Francisco Miguel Biete and was submitted to Percona Server as a community contribution. He also wrote an excellent post describing his contribution for Percona’s Community Blog titled Percona Server for MySQL 8.0 – New Data Masking Feature and the full list of functions can be found in the documentation of the MySQL Enterprise plugin. …

This blog discusses a few concepts about Docker and how we can use it to run a MySQL async replication environment. Docker is a tool designed to make it easier for developers and sysadmins to create/develop, configure, and run applications with containers. The container allows us to package all parts of the application it needs, such as libraries, dependencies like code, configurations, and runtime engine. Docker runtime containers are platform-independent so the package created can be shipped one platform to another platform.

Dockerhub is the repository where you can find containerized docker images for applications like MySQL, Percona Server for MySQL, and MariaDB. Using the example below, I will show you how to set up a docker container from the …

In the previous blog post of this series, MySQL Encryption: Talking About Keyrings, I described how keyrings work. In this post, I will talk about how master key encryption works and what the pros and cons are of using envelope encryption such as a master key.

The idea behind envelope encryption is that you use one key to encrypt multiple other keys. In InnoDB, this “one key” is the master encryption key and the “multiple other keys” are the tablespace keys. Those tablespace keys are the ones that are actually used to encrypt tablespaces. Graphically it can be presented like this:

The master key resides in the keyring, while encrypted tablespace keys reside in tablespace headers (written on page 0 of a tablespace). In the picture above:

Table A is encrypted with key 1. Key 1 is encrypted …

Percona Monitoring and Management (PMM) is built upon the shoulder of giants like Prometheus and Grafana. And speaking of Grafana, one of the coolest features that come with it is the ability to customize the experience through 3rd party plugins.

Plugins are an easy way to enhance the ability to have specialized graphs. One case that we saw in Managed Services is the ability to have a throughput graph, that shows QPS vs Threads running. This is different in essence of the “default” graphs that show a metric against time (time being the X-axis) since what we wanted is to show queries per second not during a time (that graph already exists) but for specific values of threads.

One way to achieve that is by using a plugin called Plotly …