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 …

This blog post will discuss the newest feature available in MySQL 8.0.30: dynamic redo log sizing. After the InnoDB buffer pool size, we can say that having a proper size for the redo logs is crucial for MySQL performance. There are numerous blog posts about how to calculate a good redo log size. One of our classic blog posts is this one from Baron: How to calculate a good InnoDB log file size (That blog post is from 2008 and is still a valid formula to use.)

Now, which problem is this feature trying to solve?

In MySQL, the buffer pool and redo log size are settings that do not change frequently. Usually, these settings are set during database installation; after that, they are forgotten until an issue arises. The problem is that they used to be static, which means that you had to restart MySQL so the changes …

In this blog, I will cover important aspects which you need to test when benchmarking replication setup. MySQL has great tools that could be used to test its performance. They include:

sysbench – https://github.com/akopytov/sysbench

BMK-kit – http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

mysqlslap – https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

LinkBench – https://github.com/facebookarchive/linkbench

I will not describe how to use them here, as you can find instructions on the provided links or in the Percona blog by browsing tags …