I wrote this post on MyRocks because I believe it is the most interesting new MySQL storage engine to have appeared over the last few years. Although MyRocks is very efficient for writes, I chose a more generic workload that will provide a different MyRocks use case.

The use case is the TPC-C benchmark but executed not on a high-end server but on a lower-spec virtual machine that is I/O limited like for example, with AWS EBS volumes. I decided to use a virtual machine with two CPU cores, four GB of memory, and storage limited to a maximum of 1000 IOPs of 16KB. The storage device has performance characteristics pretty similar to an AWS gp2 EBS volume of about 330 GB in size. I emulated these limits using the KVM iotune settings in my lab.

<iotune>
     <total_iops_sec>1000</total_iops_sec>
     <total_bytes_sec>16384000</total_bytes_sec> …

Table modifications are a common task for database administrators. In this blog, I’ll explain how to alter tables online in a controlled manner that does not disrupt application users or cause application downtime.

One of the tools in Percona Toolkit is pt-online-schema-change, a utility that alters the structure of a table without interfering with the reads or writes. The tool creates an empty copy of the table to alter and modify as desired before copying the rows from the original table into the new one.

When the copying is finished, it removes the original table and replaces it with the new one. Any changes made to data in the original tables during the copy process will be reflected in the new table as the tool creates triggers on …

In this blog, I’m going to discuss how to easily skip the replication errors in GTID (Global Transaction Identifier)-based replication.

In the MySQL world, if replication is broken we all use the famous SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; to skip the replication error. It always works if it’s a traditional binlogs events-based replication and is helpful to quickly resume the replication. But does this same method work for GTID-based replication? Let’s see.

In GTID-based replication, skipping a replication error in a broken replica is not as simple as binlogs-based replication. We have one old blog post — Repair MySQL 5.6 GTID replication by injecting empty transactions —where one of our colleagues showed us one way to skip and fix the replication.

Let’s get into action and …

Percona Server for MySQL 8.0.30-22 introduces several improvements for the LDAP plugin: SASL support, support for fallback servers, and support for LDAP-based authorization. All improvements are in technical preview.

A description of the existing plugin features and usage instructions are available in the earlier blog post introducing the LDAP simple plugin, and in the Percona Server for MySQL documentation.

SASL connections

We introduced a new plugin similar to the MySQL …

What is a Delay Replica and how does it help?

MySQL Replication is useful, easy to set up, and used for very different purposes. For example:

• split reads and writes
• run data mining or reporting processes on them
• disaster recovery

To learn more, check out How Does MySQL Replication Work?

It’s important to mention that a replication server is not a backup by itself. A mistake on the source, for example, a DROP DATABASE or an UPDATE without a WHERE clause will be replicated nearly instantly to all replica servers. Simply having a replica is not going to be helpful here. How can we avoid that kind of mistake? By having a replica server that intentionally lags behind.

We can never avoid human error in database infrastructure operations. But …

The need to audit who is using a database and their activities is crucial in many environments. This functionality is available in the free, open source Audit Plugin that is included in Percona Server for MySQL.

The prerequisite for installing Percona’s Audit Log Plugin is to first install Percona Server for MySQL. Percona Server for MySQL is a drop-in replacement for MySQL.

The Audit Log Plugin itself is easy to install requiring only a shared object library to be loaded from a MySQL client program with INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;.

stoker@testbox:~/Downloads$ mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL …

If you are using MyDumper as your Logical Backup solution and you store your backups on S3, you need to take a local backup and then upload it to S3. But what if there is not enough space to hold the backup on the server where we are taking the backup? Even if we have enough disk space, we will need to wait until the end to start to upload the files, making the whole process longer.

MyDumper implemented stream backup in v0.11.3 and we have been polishing the code since then. We also implemented two ways of executing external commands:

−−exec-per-thread: The worker that is getting the data from the database will write and redirect to the STDIN of the external command. It will be similar to execute cat FILE | command per every written and closed file.

−−exec: In this case, the worker writes in the local storage and when the file is closed, the filename is enqueued. The exec …

While the LAMP stack – Linux/Apache/MySQL/PHP – is not the dominant platform it was a decade ago, there are still many websites that depend on it. One of the pillars of this quartet had a milestone last week when PHP 8.0 passed into End Of Life status. While it will still have limited security issue support for another year, 8.0 should be phased out of your environments.

By the way, 8.1 has another year until it reaches End of Life status.

So please update your PHP 8.0 as soon as you can, double check the connector you are using to access your database is updated too, and make sure you are ready for 8.1’s future.

And make sure that if you are currently running MySQL 5.7 that you upgrade by October 2023 when it too reaches End of Life.

We all know that data is important, and some businesses need historical data to be available all the time. The problem is that queries on large tables perform poorly if they are not properly optimized. We get many customer requests in Managed Services to purge/archive large tables, and to achieve it, we use pt-archiver.

Recently, we received a request to archive a large table, and the customer was worried about the downtime and performance issues during the archival.

We proposed a solution to the customer to archive the table using pt-archive. The idea is to archive old data to other tables and keep the latest data on the current table with minimal performance issues. All of the data will remain available and can be queried anytime.

In the blog, I will …

Earlier in the year, I was working on an issue where one of my clients had reported a massive influx in connection on their hosts after enabling Percona Monitoring and Management (PMM). This was something I had not seen before and after researching for a couple of days I discovered that if you monitor a MySQL instance with PMM configured to collect table statistics, and if the tables that it’s gathering statistics from are Federated, it will generate a connection on the remote host for the Federated tables, one for each Federated table in the instance. Let’s go over the details and provide some examples so we can understand this a bit better.

First, I’ll offer a reminder that a Federated table is simply a table that you can put in your MySQL instance that is empty locally and uses a network connection to get the data from …