I would say that innodb_rollback_on_timeout is a very important parameter. In this blog, I am going to explain “innodb_rollback_on_timeout” and how it affects the transaction behavior at the MySQL level. I describe two scenarios with practical tests, as it would be helpful to understand this parameter better.

What is innodb_rollback_on_timeout?

The parameter Innodb_rollback_on_timeout will control the behavior of the transaction when a failure occurs with timeout.

• If –innodb-rollback-on-timeout=OFF ( default ) is specified, InnoDB rollbacks only the last statement on a transaction timeout.
• If –innodb-rollback-on-timeout=ON is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

Let’s conduct the test with the …

On July 21, 2020, Percona delivered an updated version of our Percona Kubernetes Operator for Percona XtraDB Cluster (PXC) focused on easing deployment and operations management of a clustered MySQL environment. Included in the Percona Distribution for MySQL, our Operator is based on the best practices for MySQL cluster configuration and setup in Kubernetes. This update adds a variety of important new features including:

Smart Update to Safely and Reliably Upgrade your PXC Environment Automatically
We implemented a new update strategy called Smart Update. Smart Update is aware of the context of your environment and minimizes the number of failover events that need to occur to fully upgrade a …

On Monday, July 13, 2020, Oracle released MySQL 8.0.21.  This release contained a few new changes that cause issues with Percona XtraBackup.

First, this release introduced the ability to temporarily disable InnoDB redo logging (see the work log and documentation).  If you love your data, this feature should ONLY be used to speed up an initial logical data import and should never be used under a production workload.

When used, this new feature creates some interesting complications for Percona XtraBackup that you need to be aware of.  The core requirement for XtraBackup to be able to make consistent hot backups is to have some form of redo or write ahead log to copy and read from.  Disabling the InnoDB redo log will prevent XtraBackup from making a …

The Percona Kubernetes Operator for Percona XtraDB Cluster can send backups to Amazon S3 or S3-compatible storage. And every now and then at Support, we are asked how to send backups to Google Cloud Storage.

Google Cloud Storage offers an “interoperability mode” which is S3-compatible. However, there are a few details to take care of when using it.

Google Cloud Storage Configuration

First, select “Settings” under “Storage” in the Navigation Menu. Under Settings, select the Interoperability tab. If Interoperability is not yet enabled, click Enable Interoperability Access. This turns on the S3-compatible interface to Google Cloud Storage.

After enabling S3-compatible storage, an access key needs to be generated. There are two options: Access keys can be tied to Service accounts or User accounts. For …

When I speak about MySQL performance troubleshooting (or frankly any other database), I tend to speak about four primary resources which typically end up being a bottleneck and limiting system performance: CPU, Memory, Disk, and Network.

It would be great if when seeing what resource is a bottleneck, we could also easily see what queries contribute the most to its usage and optimize or eliminate them. Unfortunately, it is not as easy as it may seem.

First, MySQL does not really provide very good instrumentation in those terms, and it is not easy to get information on how much CPU usage, Disk IO, or Memory a given query caused.  Second, direct attribution is not even possible in a lot of cases. For example, disk writes from flushing data from the InnoDB buffer pool in the …

You got yourself a Kubernetes cluster and are now testing our Percona Kubernetes Operator for Percona XtraDB Cluster. Everything is working great and you decided that you want to increase the number of Percona XtraDB Cluster (PXC) pods from the default 3, to let’s say, 5 pods.

It’s just a matter of running the following command:

kubectl patch pxc cluster1 --type='json' -p='[{"op": "replace", "path": "/spec/pxc/size", "value": 5 }]'

Good, you run the command without issues and now you will have 5 pxc pods! Right? Let’s check out how the pods are being replicated:

kubectl get pods | grep pxc
cluster1-pxc-0                                     1/1     Running   0          25m
cluster1-pxc-1                                     1/1     Running   0          23m
cluster1-pxc-2                                     1/1     Running …

Ever wanted to extend MySQL and add some feature you think it is missing?  With MySQL plugins, you can do exactly that.  One thing that has bothered me for several years is that you cannot easily retrieve system metrics from within MySQL.  Whether I am connecting via a remote connection or looking to add features to monitoring without the need for another interface with the server, I have wanted to retrieve system metrics without leaving the MySQL interface.

So, I started a Proof of Concept for this.  My goal was to get metrics such as RAM (total, used, free), system load, CPU utilization, disk utilization for the file system containing the datadir, and more.  My objective was to do this as efficiently within MySQL as possible.  For this, I chose to utilize standard C libraries in as few lines of code as possible without having to scrape system files or run commands to get the data.  The …

Why another article on this, Marco?

MySQL deadlocks is a topic covered many times, including here at Percona. I suggest you review the reference section at the end of this post for articles on how to identify deadlocks and from where they are generated.

So why another article?

The answer is that messages we receive like the following are still very common:

User (John): “Marco, our MySQL is having problems”
Marco: “Ok John what problems? Can you be a bit more specific?”
John: “Our log scraper is collecting that MySQL has a lot of errors”
Marco: “Ok can you share the MySQL log so I can review it?”
John: “Errors are in the application log, will share one application log”

Marco reviews the log and in it he finds:

“ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction”

Marco’s reaction is: “Oh …

Join Peter Zaitsev, Percona CEO, as he discusses MySQL 8 Observability.

Broken MySQL means broken application, so maintaining insights in MySQL operational performance is critical. Thankfully, MySQL 8 offers a lot in terms of observability to resolve problems quickly and get great insights into opportunities for optimization. In this talk, we will cover the most important observability improvements in MySQL 8 ranging from Performance Schema and Information Schema to enhanced error logging and optimizer trace. If you are a Developer or DBA passionate about Observability, or just want to be empowered to resolve MySQL problems quickly and efficiently, you should attend.

Please join Peter Zaitsev on Wednesday, July 15 at 1 pm EDT for his webinar “MySQL 8 Observability“.

Watch the …

When trying to do some Linux tuning for MySQL, there are a few options that will greatly influence the speed of MySQL.  Below are some of the most important of these settings to help you get started.

Swappiness

The first thing to look at is what swappiness is set to.  This will determine the tendency of the kernel to swap out memory pages.  In may cases, you will want to set this to “1” to keep the swapping to a minimum.  A value of “0” will disable it entirely.

You can determine the current value with the following command:

cat /proc/sys/vm/swappiness

If this is not set to “1”, you should consider making the change by using one of the following options:

# Make sure you are root and set swappiness to 1 
echo 1 > /proc/sys/vm/swappiness

# Or, you can use sysctl to do the same sysctl 
vm.swappiness vm.swappiness = 1

If the change helps, you will want to …