Taking a MySQL backup using Percona XtraBackup (PXB) consists of basically two steps: 1) take the backup and 2) prepare the backup.

Briefly speaking, taking a backup means that PXB will copy all of the files from your instance and transfer them to another location. While it does the copy, it spawns a thread that will monitor the InnoDB redo log (WAL/transaction log) and store a copy of all the new redo log entries generated by the server during the backup.

Before restoring the backup into a new instance, users have to prepare the backup. This operation is the same as the crash recovery steps that the MySQL server does after a server crash.

It consists of reading all the redo log entries into memory, categorizing them by space id and page id, reading the relevant pages into memory, and checking the LSN number on the page and on …

Having a backup of your database is like insurance, you have to pay a monthly price to ensure you have a service available when you need to. When talking about backups, the storage required to keep your backups is what comes into factor when talking about price, the bigger your backup, or the bigger the retention period, the more it will cost.

Compressing your backups is a common practice to reduce this cost. Currently, Percona XtraBackup (PXB) has support for two compression algorithms: quicklz (which is an abandoned project and will soon be deprecated in PXB) and LZ4.

Today we are glad to introduce support for a new compression algorithm in Percona XtraBackup 8.0.30 – …

At Percona Managed Services, sometimes clients’ applications face deadlock situations and need all historic deadlock information for application tuning.

We could get the LATEST DETECTED DEADLOCK from SHOW ENGINE INNODB STATUS\G:

….
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 163 page no 3 n bits 72 index GEN_CLUST_INDEX of table `deadlock_test`.`t` trx id 78507 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 78508, ACTIVE 155 sec starting index read
mysql tables in use 1, locked 1
….

But how could we view all past deadlock information?

We could enable innodb_print_all_deadlocks,  and all deadlocks in InnoDB user transactions will be recorded in the MySQL error log.

Let‘s start the test.

Create the test database …

Raspberry PI is a small single-board computer (SBCs) developed by the Raspberry Pi Foundation in association with Broadcom. This tiny computer is extremely popular and widely used in many areas. Thanks to its size, low cost, and low energy requirements, it can be used to collect data in remote locations or from sensor devices. We often need to be able to store large amounts of data efficiently on these devices.

MyRocks is a MySQL engine that uses RocksDB to store data. It is space efficient and able to handle writes quite efficiently.

First things first

Building and installing Percona Server for MySQL with MyRocks engine enabled is easy but requires some time. It is essential to make sure that you have all the ingredients and meet all the requirements:

• Raspberry PI 3, 4, 400, or superior.
• SD Card with …

For testing purposes, especially if you’re working on a project that uses any database technology to store information, you may need data to try out your project. In that case, you have two options:

• Find a good dataset (Kaggle) or,
• Use a library like Faker

Through this blog post, you will learn how to generate test data for MySQL using Faker.

Requirements Dependencies

Make sure all the dependencies are installed before creating the Python script that will generate the data for your project.

You can create a requirements.txt file with the following content:

pandas
sqlalchemy
PyMySQL
tqdm
faker

Once you have created this file, run the following command:

pip install -r requirements.txt

Or if you’re using Anaconda, create an environment.yml file:

name: percona
dependencies:
  - python=3.10
  - pandas
  - sqlalchemy …

To follow up on my previous Kubernetes articles:

• Installed in part one (see Using Percona Kubernetes Operators With K3s Part 1: Installation) and
• Have Percona Server for MySQL running (Using Percona Kubernetes Operators With K3s Part 2: Percona Server for MySQL Operator)
• Monitoring with Percona Monitoring and Management (PMM): Using Percona Kubernetes Operators With K3s Part 3: Monitoring and PMM

I would like to introduce a project which brings Kubernetes observability to the next …

Not long ago, we found a long-running query in one of our clients on Managed Services. While reviewing this long-running query, we found it was generated by a pt-archiver operation. That doesn’t sound good, right?

I was able to reproduce the issue on my test environment. In this article, I will show you how to use pt-archiver to purge data from a table using a secondary index. First, we need to have the full picture before going any deeper. Here is a representation of the table structure:

Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4000001 DEFAULT …

This week is KubeCon in Detroit, and in preparation for attending I have been polishing up my Kubernetes skills. This big rush to put software in containers and have Kubernetes run everything is getting a lot of push in the industry. Many software applications run perfectly well in ready-made packages from a container made for ephemeral consumption much like a can of Campell’s tomato soup. But generally, relational databases like permanence, stability, and a consistent presence. Databases like to run to stock caches and build statistics, so they are not great at running well after being started. But this article is the first in a series for ‘us’ database folks to learn how to keep our databases happy in a containerized world.

With the trip to the Motor City on my calendar, it was time to answer the question “What is the minimalist …

Oracle releases updates to MySQL on a quarterly basis and the Release Notes for 8.0.31 arrived just before the software.  This time around there are some very interesting new features that will be handy including SQL standards support, as well as over 130 bug fixes.

The TL;DR:  Some nice new features but nothing spectacular.

• FULL is now a reserved word.
• InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded.
• The OpenSSL library for MySQL Server has been updated to version 1.1.1q.
• The optimizer has been improved so that the old ER_NOT_SUPORTED_YET is not thrown when you try something like this:
  

      ((SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2) ORDER …

As we have Kubernetes installed in part one (see Using Percona Kubernetes Operators With K3s Part 1: Installation), now we will install Percona Server for MySQL Operator into the running cluster.

I will copy some ideas from Peter’s Minukube tutorial (see Exploring MySQL on Kubernetes with Minkube).

In this case, I will use not Percona XtraDB Cluster Operator but a regular Percona Server for MySQL with Asynchronous replication.

We have recently released version 0.3.0 and it is still in the technical preview state, so we are actively looking for more feedback!

If we go with all …