Although how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this.

What problem does this variable cause if it is not properly sized? Let’s find out with an example!

Given the following table definition:

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1

With ~2M rows

select count(*) from joinit ;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+

With the default value of  range_optimizer_max_mem_size = 8388608, the …

The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂

Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command.

In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA.

“SHOW PROCESSLIST” Using Thread …

It has been some time since I have posted updates to my plugins.  After the initial version, I decided to split the plugins into categories of metrics.  This will allow users to choose whether they want to install all of the plugins or only select ones they care about.

Since the installation process is unfamiliar to many users, I also expanded the instructions to make it a little easier to follow.  Moreover, I added a Makefile.

I have also reformatted the output of some plugins to be either horizontal or vertical in orientation.  There is still more work to do in this area as well.

Where to Get The MySQL Plugins

You can get the plugins from GitHub at https://github.com/toritejutsu/osmetrics but they will have to be compiled from source.  As mentioned above, you can choose whether to install all of them or one by …

Mysqlpump is a client program that was released with MySQL 5.7.8 and is used to perform logical backups in a better way. Mysqlpump supports parallelism and it has the capability of creating compressed output. Pablo already wrote a blog about this utility (The mysqlpump Utility), and in this blog, I am going to explore the available compression techniques in the Mysqlpump utility.

Overview

Mysqlpump has three options to perform the compression backup.

–compress: Used to compress all the information sent between client and server.

–compression-algorithm: It was added in MySQL 8.0.18. Used to define the compression algorithm for all incoming connections to the server. (available options: zlib, zstd, uncompressed )

–compress-output: Used to define the …

In my talk on Percona Live (download the presentation), I spoke about how we can use Percona Kubernetes Operators to deploy our own Database-as-Service, based on fully OpenSource components and independent from any particular cloud provider.

Today I want to mention an important tool that I use to test our Operators: ChaosMesh, which actually is part of CNCF and recently became GA version 1.0.

ChaosMesh seeks to deploy chaos engineering experiments in Kubernetes deployments which allows it to test how deployment is resilient against different kinds of failures.

Obviously, this tool is important for Kubernetes Database …

This blog discusses new features about the persisted system variables and how we can use it to make variable changes persistent. The MySQL server maintains system variables that control its operations. The dynamic variables used prior to the MySQL 8 release are not persistent and are reset upon restart. These variables can be changed at runtime using the SET statement to affect the operation of the current server instance but we have to manually update my.cnf config file to make them persistent. In many cases, updating my.cnf from the server-side is not a convenient option, and leaving the variable just updated dynamically reverts on the subsequent restart without any history.

Persisted system variables are one of the useful features introduced in MySQL 8. The new functionality helps DBAs update the variables dynamically and register them without touching the configuration files from the server-side.

How to Persist the Global …

MySQL 8.0.22 was released on Oct 19, 2020, and came with nice features and a lot of bug fixes. Now, you can configure your async replica to choose the new source in case the existing source connection (IO thread) fails. In this blog, I am going to explain the entire process involved in this configuration with a use case.

Overview

This feature is very helpful to keep your replica server in sync in case of current source fails. 

To activate asynchronous connection failover, we need to set the “SOURCE_CONNECTION_AUTO_FAILOVER=1” on the “CHANGE MASTER” statement.

Once the IO connection fails, it will try to connect the existing source based on the “MASTER_RETRY_COUNT, MASTER_CONNECT_RETRY”. Then only it will do the failover. 

The feature will only work when the IO connection is failed, maybe the source crashed or stopped, or any network failures. This will not work if the replica is …

Hello everyone, in this little post we will review a new feature in MySQL 8.

What is “CHECK Constraint”?

This is a new feature to specify a condition to check the value before INSERT or UPDATE into a row. The constraint could return an error if the result of a search condition is FALSE for any row of the table (but not if the result is UNKNOWN or TRUE).

This feature starts working on MySQL 8.0.16, and in previous versions, we could create it, but it doesn’t work, meaning the syntax is supported but it is not working,

There are some rules to keep in mind…

– AUTO_INCREMENT columns are not permitted
– Refer to another column in another table is not permitted
– Stored functions and user-defined functions are not permitted (you can not call a function or any user-defined functions)
– Stored procedure and function parameters are not permitted (you cannot call …

Maybe one of the more “obscure” operations when dealing with replica lag, or, in general, when one needs to control writes to the database, is the Rate limit. It’s also lately one of the most popular conversations around the community.

But what is it? In plain words: holding up queries for a while, giving air to the replicas to breath and catch up. Something similar to the Galera’s Flow Control mechanism, although flow control, when it kicks in, stops all the writes while the nodes catch up. With a throttle no write is stopped, just delayed.

There are several ways to do this. A popular tool is Freno but this is also something that can be achieved with ProxySQL. Let’s see how.

Delay

ProxySQL has a variable called …

One of the questions you often will be faced with operating a Linux-based system is managing memory budget. If a program uses more memory than available you may get swapping to happen, oftentimes with a terrible performance impact, or have Out of Memory (OOM) Killer activated, killing process altogether.

Before adjusting memory usage, either by configuration, optimization, or just managing the load, it helps to know how much memory a given program really uses.

If your system runs essentially a single user program (there is always a bunch of system processes) it is easy.  For example, if I run a dedicated MySQL server on a system with 128GB of RAM I can use “used” as a good proxy of what is used and “available” as what can still be used.

root@rocky:/mnt/data2/mysql# free -h …