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 …

Every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and, given so, it is subject to possible sniffing with all the possible related consequences.

Given that, it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted.

At the same time, it is a best practice to not store connection credentials in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password on a sticky note on your desk. Not a good idea.

Instead, the main options are either transforming the passwords to be less identifiable via hashing or storing the information in an external centralized vault.

In MySQL, the passwords are transformed to not be clear text, and several different plugins …

MySQL 8.0.31 added INTERSECT and EXCEPT to augment the long-lived UNION operator. That is the good news. The bad news is that you have to be careful using the EXCEPT operator as there is a trick.

Let’s start with some simple tables and load some simple data.

SQL > create table a (id int, nbr int);
Query OK, 0 rows affected (0.0180 sec)
SQL > create table b (id int, nbr int);
Query OK, 0 rows affected (0.0199 sec)
SQL > insert into a (id,nbr) values (1,10),(3,30),(5,50),(7,70);
Query OK, 4 rows affected (0.0076 sec)

Records: 4  Duplicates: 0  Warnings: 0
SQL > insert into b (id,nbr) values (1,10),(2,20),(3,30),(4,40);
Query OK, 4 rows affected (0.0159 sec)

Records: 4  Duplicates: 0  Warnings: 0

So each table has four rows of data with two rows – (1,10) and (3,30) – appearing in both.

SQL > select id,nbr from a;
+----+-----+
| id | nbr |
+----+-----+
|  1 |  10 |
|  3 |  30 |
|  5 |  50 |
|  7 |  70 | …

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 …

Are you familiar with the UNION statement for your SQL queries? Most likely, you are. It has been supported for a long time.

In case you are not familiar with UNION, don’t worry, I’m going to show you how it works with simple examples.

Considering “Set Theory”, other than the UNION, starting from the newly deployed MySQL Community 8.0.31, a couple of new clauses are available: INTERSECT and EXCEPT.

From now on you can rely on more powerful options to write your queries and deploy some Set Theory logic.

In this article, I’m going to present how the three options work.

The traditional UNION

UNION combines the results from two or multiple SELECT statements into a single result set. Let’s suppose you have two queries, A and B, they return the same number of columns with the same data types and you need to merge all rows from both queries into a single result set. You can …

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 …