In my previous blog post, ProcFS UDF: A Different Approach to Agentless Operating System Observability in Your Database, I wrote about the ProcFS UDF MySQL plugin, which allows you to get operating systems stats, through the MySQL database, without having shell access to the server and any local agent installation.

Some of you wondered whether there is a way to use this goodness in Percona Monitoring and Management (PMM), and this blog post will show you exactly how to do that.

Unfortunately, at this point, Percona Monitoring and Management does not support the ProcFS UDF MySQL plugin out of the box. It is in the backlog, along with many other cool things. However, …

You have your database instance deployed with AWS and you are using AWS RDS for MySQL. All work smoothly in terms of satisfying queries for your application and delivering reliable uptime and performance. Now you need to take care of your backup strategy. Business is defined to have this retention policy:

• 7 daily full backups
• 4 weekly backups
• 12 monthly backups

Plus the ability to do point-in-time recovery (PITR) for the last 24 hours since the last full backup was taken.

The cloud vendor solution

This is a piece of cake. The daily backups: just set the backup retention period to six days. Done. This also has the plus that PITR is already covered since RDS uploads the transaction logs to S3 every five minutes and stores them in the parquet format, making it smaller than the regular text file. All amazing, right?

Now, the weekly and …

A large table is a pain for many reasons as long as it is in a system. And as if that’s not enough, it is also a difficult task to get rid of it. In this post, we will understand why it is a pain to do this operation and what we can do about it. It will be like asking the table “Tell me what happened and I will ease up the eviction”.

So what happened? When a table is dropped (or truncated), InnoDB has to scan the pages throughout the buffer pool and remove all those belonging to that table. For a large buffer pool, this crawling in the buffer pool pages and eviction process will be slower. When we say “scan buffer pool”, it mainly looks for “LRU”, “FLUSH” (Dirty pages), and “AHI” entries.

LRU: Buffer pool pages are stored in a linked list of pages in order of usage. As the data reaches the end of the list, it is evicted to make space for new data. When the room is needed to add …

We often see an int column of a table that needs to be changed to unsigned-int and then unsigned-bigint due to the value being out of range. Sometimes, there may even be blockers that prevent us from directly altering the table or applying pt-online-schema-change on the primary, which requires the rotation solution: apply the change on the replica first, switch over the writes to the replica, and then apply the change on the previous primary. In this case, MySQL will have to replicate unsigned-int to unsigned-bigint for a while.

One might think it is obvious and straightforward that MySQL should be able to replicate unsigned-int to unsigned-bigint because unsigned-bigint has a larger size(8 bytes) which covers unsigned-int(4 bytes). It is partly true, but there are some tricks in practice. This blog will show you those tricks through the scenarios.

Let’s understand the scenarios and issues that one may face when replicating from …

You may have read MyRocks Use Case: Big Dataset and been intrigued enough to want to evaluate RocksDB with InnoDB.  It has several advantages including requiring less storage space.

So how do you start?

On a fresh install of Percona Server for MySQL, install RocksDB by entering sudo apt install percona-server-rocksdb:

percona@DellXPS:~$ sudo apt install percona-server-rocksdb
[sudo] password for percona:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
percona-server-rocksdb
0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded.
Need to get 65.3 MB of archives.
After this operation, 292 MB of additional disk space will be used.
Get:1 …

My last blog, Percona Utilities That Make Major MySQL Version Upgrades Easier, detailed the tools available from the Percona toolkit that assists us with major MySQL version upgrades. The pt-upgrade tool aids in testing application queries and generates reports on how each question performs on servers running various versions of MySQL.

MySQL Shell Upgrade Checker is a utility that helps in compatibility tests between MySQL 5.7 instances and MySQL 8.0 upgrades, which is part of the mysql-shell-utilities. The util.checkForServerUpgrade() function checks whether the MySQL 5.7 instance is ready for the MySQL 8.0 upgrade and generates a report with warnings, errors, and notices for preparing the current MySQL 5.7 setup for upgrading to MySQL 8.0.

We can run this Upgrade Checker Utility in the current MySQL 5.7 …

With the economy slowing down and inflation raging in many parts of the world, your organization will love you if you find ways to reduce the costs of running their MySQL databases. This is especially true if you run MySQL in the cloud, as it often allows you to see the immediate effect of those savings, which is what this article will focus on.

With so many companies announcing layoffs or hiring freezes, optimizing your costs may free enough budget to keep a few team members on or hire folks your team needs so much. 

1. Optimize your schema and queries

While optimizing schema and queries is only going to do so much to help you to save on MySQL costs in the cloud, it is a great thing to start with. Suboptimal …

The title is not entirely true, but ‘FTWRL on MyDumper is not needed anymore for consistent backups’ was a long title. One more time, I wanted to share a new feature in MyDumper. This is related to an important piece: the locking mechanism that mydumper uses to sync all the threads.

MyDumper was born because, at that time, we didn’t have a tool that could take a consistent logical backup using multiple threads. Syncing all the threads was one of the problems, which has been solved using FLUSH TABLE WITH READ LOCK (FTWRL), til all the threads execute START TRANSACTION WITH CONSISTENT SNAPSHOT (STWCS), then we release the FTWRL and all the threads are in sync. We all know that FTWRL is very expensive and difficult to acquire on some database workloads.

I started to think about alternatives to avoid using FTWRL, and my first thought was, why …

One of the questions I am often asked is in what cases I would prefer MyRocks over InnoDB. We have covered MyRocks in our blog previously:

MyRocks Performance – Percona Database Performance Blog

Saving With MyRocks in The Cloud – Percona Database Performance Blog

But it would be good to refresh some materials.

This time I want to take an interesting (and real) data set, which I also covered previously: the Reddit Comments dataset (see Big Dataset: All Reddit Comments – Analyzing with ClickHouse – Percona Database Performance Blog). The dataset is still available for download from …

In Percona Server for MySQL 8.0.29-21, we added one more patch that helps us to build server code on macOS. To be precise here, we still could do this even before this patch but only partially. Now it is possible to build RocksDB Storage Engine as well.

A word of disclaimer here, at the moment, by macOS we still understand macOS for Intel x86_64 architecture (the most recent ARM versions with Apple M1 / M2 processors are out of the scope of this blog post). Moreover, Percona does not provide …