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.

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:

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

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:

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 …

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 …

We’ve been learning for many years how to run Linux for
databases, but over time we realized that many of our lessons
learned apply to many other server workloads. Generally, server
process will have to interact with network clients, access
memory, do some storage operations and do some processing work –
all under supervision of the kernel.

Unfortunately, from what I learned, there’re
various problems in pretty much every area of server
operation. By keeping the operational knowledge in narrow camps
we did not help others. Finding out about these problems requires
quite intimate understanding of how things work and slightly more
than beginner kernel knowledge.

Many different choices could be made by doing empiric tests,
sometimes with outcomes that guide or misguide direction for many
years. In our work we try to understand the reasons behind
differences that we observe in random poking at a problem.

However, I have seen many people assuming that because
default_tmp_storage_engine has the value “InnoDB”,
all temporary tables are created in InnoDB format in
5.6. This is not true: first, because implicit
temporary tables are still being created in memory using …

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.