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 …

Introduction

A couple of months ago I decided to give myself a refresher on the mechanics of InnoDB. Having a high level understanding of what’s going on under the hood can help provide the context needed in order to resolve issues you may encounter as well as assist you in ensuring that your MySQL instance is running efficiently. Everyone can stand to go back to basics every now and then, as it can help you pick up concepts that you may have missed the last time you researched the topic.

This time around I decided to give myself a refresher by re-reading the MySQL 5.6 reference manual, chapter 14, which covers the InnoDB engine. Despite having a wealth of documented knowledge and insights, I found that a lot of the points in the documentations were unclear, leading me to do more research and experimentation in order to get a bit of clarity on some of the specifics that I felt were missing. In order to help make the information …

In this blog, I provide answers to the Q & A for the Troubleshooting hardware resource usage webinar.

First, I want to thank everybody who attended the May 26 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: How did you find the memory IO LEAK?

A: Do you mean the replication bug I was talking about in the webinar? I wrote about this bug …