From the initial release, one of the biggest complaints I had about Group Replication is that it allowed “stale” reads and there was no way to prevent them or to even know that you read “stale” data. That was a huge limitation. Thankfully, Oracle released features to control the consistency levels, and it was exactly a year ago! I don’t know about you, but I personally was confused by naming it group_replication_consistency=’AFTER’ or ‘BEFORE’.

So now I want to try to make sense of it and share my understanding (even if it is one year later).

Setup:

• 3 Node Percona Server for MySQL 8.0.18 running in …

As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune it correctly. This post is a bit long and complex as it goes very deep into some InnoDB internals.

InnoDB internally handles flush operations in the background to remove dirty pages from the buffer pool. A dirty page is a page that is modified in memory but not yet flushed to disk. This is done to lower the write load and the latency of the transactions. Let’s explore the various sources of flushing inside InnoDB.

Idle Flushing

We already discussed the idle flushing in the previous post …

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 …