The latest from the MySQL community

1. What it is about

This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful.

Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, the CPU architecture, the number of cores, the amount of RAM, the storage capacity and speed. On paper the hardware looks like it can handle the workload. But in reality, things rarely go exactly as planned. So, conducting a thorough stress test is the next thing to do.

2. Realities of stress testing

You configure your MySQL server setting the innodb_buffer_pool_size to 70-80% of your available RAM. This creates a large fast buffer for your data and indexes, reducing the need for slower disk input/output.

After …

This article is also available in Chinese: 中文版. Browse all English articles.

Memory leaks and high memory usage are common problems in MySQL, and diagnosing them depends heavily on good memory-monitoring data. MySQL’s Performance Schema provides memory monitoring, but the granularity of PFS’s memory statistics is coarse, which makes it hard to pinpoint the exact code responsible.

The figure above shows the memory information monitored by PFS. Although the monitoring shows that a lot of memory is allocated on String::value and thd::main_mem_root, these are basic objects used in many places throughout MySQL, so we cannot infer from this which operation actually caused the memory usage. In addition, PFS itself has relatively high memory overhead and …

ProxySQL is a very useful tool for gaining high availability, load balancing, query routing, query caching, query rewriting, multiplexing, and data masking. It is a proven tool and is used largely in production. There can be an instance of ProxySQL using a lot of memory on the server. This post is related to one of the causes that can use memory on the ProxySQL server and how we handle it.

Explanation

How do we know where the memory is getting used?

Proxysql has a stats database which we can see with an admin login. This database contains metrics gathered by ProxySQL concerning its internal functioning.

This post mostly talks about stats in MySQL query digest in ProxySQL.

Memory used by query digest can grow unlimited, and you can query the memory usage with the below query with an admin login. The memory usage is shown in bytes. 

MySQL [(none)]> select * from …

The Question Recently, a customer asked us:

What would cause a node switch to fail in a Tungsten Cluster?

For example, we saw the following during a recent session where a switch failed:

cctrl> switch to db3 

SELECTED SLAVE: db3@alpha 
SET POLICY: MAINTENANCE => MAINTENANCE 
PURGE REMAINING ACTIVE SESSIONS ON CURRENT MASTER 'db1@alpha' 
PURGED A TOTAL OF 0 ACTIVE SESSIONS ON MASTER 'db1@alpha' 
FLUSH TRANSACTIONS ON CURRENT MASTER 'db1@alpha' 
Exception encountered during SWITCH. 
Failed while setting the replicator 'db1' role to 'slave' 
ClusterManagerException: Exception while executing command 'replicatorStatus' on manager 'db1' 
Exception=Failed to execute '/alpha/db1/manager/ClusterManagementHelper/replicatorStatus alpha db3' 
Reason= 
CLUSTER_MEMBER(true) 
STATUS(FAIL) 
+----------------------------------------------------------------------------+ 
|alpha | …

When users experience memory usage issues with any software, including MySQL®, their first response is to think that it’s a symptom of a memory leak. As this story will show, this is not always the case.

This story is about a bug.

All Percona Support customers are eligible for bug fixes, but their options vary. For example, Advanced+ customers are offered a HotFix build prior to the public release of software with the patch. Premium customers do not even have to use Percona software: we may port our patches to upstream for them. But for Percona products all Support levels have the right to have a fix.

Even so, this does not mean we will fix every unexpected behavior, even if we accept that behavior to be a valid bug. One of the reasons …

In this post we will review the most important Linux settings to adjust for performance tuning and optimization of a MySQL database server. We’ll note how some of the Linux parameter settings used OS tuning may vary according to different system types: physical, virtual or cloud. Other posts have addressed MySQL parameters, like Alexander’s blog MySQL 5.7 Performance Tuning Immediately After Installation. That post remains highly relevant for the latest versions of MySQL, 5.7 and 8.0. Here we will focus more on the Linux operating system parameters that can affect database performance.

Server and Operating System

Here are some Linux parameters that you should check and consider modifying if you need to improve database performance.

…

One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue documented by Jeremy Cole back in 2010. As such, it’s usually pretty easy to resolve these issues and keep MySQL out of swap space. Recently, however, we had tried all of the usual tricks but had an issue where MySQL was still swapping.

The server with the issue was a VM running with a single CPU socket (multiple cores), so we knew it wasn’t NUMA. Swappiness and MySQL were both configured correctly and when you checked the output of free -m it showed 4735M of memory available.

[sylvester@host~]$ free -m
              total        used        free      shared  buff/cache   available …

Troubleshooting crashes is never a fun task, especially if MySQL does not report the cause of the crash. For example, when MySQL runs out of memory. Peter Zaitsev wrote a blog post in 2012: Troubleshooting MySQL Memory Usage with a lots of useful tips. With the new versions of MySQL (5.7+) and performance_schema we have the ability to troubleshoot MySQL memory allocation much more easily.

In this blog post I will show you how to use it.

First of all, there are 3 major cases when MySQL will crash due to running out of memory:

1. MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix
2. There is some other process(es) on the server that allocates RAM. It can be the application …

Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic and if it is incorrectly configured, could lead to undesired situations.

Let’s see first how innodb_buffer_pool_size , innodb_buffer_pool_instances  and innodb_buffer_pool_chunk_size interact:

The buffer pool can hold several instances and each instance is divided into chunks. There is some information that we need to take into account: the number of instances can go from 1 to 64 and the total amount of chunks should not exceed 1000.

So, for a server with 3GB RAM, a buffer pool of 2GB with 8 instances and chunks at default value (128MB) we are going to get 2 chunks per instance:

This means that there will be 16 chunks.

…

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.

I was recently working on a high memory usage issue for one of our clients, and made some interesting discoveries: high memory usage with no bounds. It was really tricky to diagnose.

Below, I am going to show you how to identify that having thread_statistics enabled causes high memory usage on busy systems with many threads.

Part 1: Issue Background

I had a server with 55.0G of available memory. Percona Server for MySQL version:

Version | 5.6.35-80.0-log Percona Server (GPL), Release 80.0, Revision f113994f31
                 Built On | debian-linux-gnu x86_64

We have …