A small thing that provides a huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well… more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot …

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 …

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 …

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 …

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 …

One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:

ERROR 1040 (00000): Too many connections

What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections.  Until connections are closed, no new connection will be accepted by the server.

I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.

Accurately Tune the max_connections Parameter

This setting defines the maximum number of connections that a MySQL instance will accept.  Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns.  Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further …

In this blog post, we will briefly explore the OS tool strace. It is not widely used due to its performance impacts, and we don’t recommend using it in production. Still, it is amazing at helping you understand some things that happen in MySQL, where the OS is involved, and as a last case resource for troubleshooting.

The strace tool intercepts and records any system calls (a.k.a.  syscalls) performed and any signals received by a traced process. It is excellent for complex troubleshooting, but beware, as it has a high-performance impact for the traced process.

We start our exploration with a simple question: what are the files opened in the OS when you issue FLUSH LOGS in MySQL? We could look at the documentation, but we decided to find out using strace.

For that, we started a MySQL lab instance and …

While there is no magic bullet for MySQL tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

Initial MySQL performance tuning can be broken down to the following categories:

• Tuning for your hardware
• Tuning for best performance / best practices
• Tuning for your workload

Tuning MySQL for Your Hardware

Depending on the hardware you have installed MySQL on, some …

Last year, I made the first post in a small series, which aimed to highlight unique features of Percona Server for MySQL, by discussing binlog_space_limit option.

Today, I am going to discuss another important type of log available in MySQL that is enhanced in Percona Server for MySQL – the slow query log. The reason why I am doing this is that although this extension has existed since the very early times of versions 5.1 (over 10 years ago!), many people are still unaware of it, which I see from time to time when working with Support customers.

Default Slow Log Inadequacy

How many times have you been wondering why, whilst reviewing slow query logs, the very same query occasionally runs way slower than usual? There may be many reasons for that, but the standard slow …