MySQL memory usage is high

[radirobi97]

Hi community,

I am trying to get a deep insight on how memory usage works in MySQL. My problem is related to Azure Database for MySQL which is a managed service by Microsoft. So unfortunately I can not reach the VM directly which hosts the MySQL server. This MySQL instance has 4vCores and 20GB of RAM, 400GB storage, version is 5.7.32. Only InnoDB storage engine is supported here.

I attach the global_variables, memoryinstruments (unfortunately just a few of them are enabled, and I can not enable any more because this is a managed MySQL service and I have no privilage for that), processlist, global_status, and innodb_status.

What I do not understand:
As I mentioned we have 20GB RAM. We only use 6GB for inno_db_buffer_pool (this buffer pool is full, of course). As you can see in the processlist we have 200-300 connections and almost all of them are idle. As you can see on the chart of the connections we have all the time 200-300 connections. Whenever I check the processlist I can see that most of the connections are idle.
When MySQL starts it allocates memory for the global buffers, but as far as I know the memory usage of each connections are adhoc. Meaning, after there is no activity on a given thread, the thread goes IDLE and the memory is released.

On the memory chart we can see that the memory usage is 90% all the time, maybe some kind of memory leak? This chart is provided by Micrososft and they have the following explanation about the chart. Let me quote them:

“”"
Would like to highlight about Azure database for MySQL Memory Consumptions comes from :

• MySQL Innodb buffer pool: this is expand with workload but will never shrink, which is normal behavior for MySQL Database.
• MySQL Memory tables and connections: those are adhoc memory will be released when the MySQL server don’t have connections or workload.

We have two different memory sets in Azure database for MySQL:

• ‘working set memory’: which is the memory usage you can observe on the chart and it stands for the actual usage for client activities, e.g. queries, connections, etc.
• ‘committed size memory’: which represents the memory portion an application (e.g. MySQL server) asked for OS to reserve including buffer pool size and memory consumed by other processes . Please note that this committed size could be either used fully or never touched by applications (e.g. MySQL) at all.
  Noted that ‘working set memory’ does not include innodb_buffer_pool_free and innodb_buffer_pool_dirty, when server run out of memory (Committed size). There can be a free memory space in buffer pool but this part is already reserved by buffer pool and cannot be used for other purposes.
  “”"

EDIT:
I am a new user here due to this the forum does not allow me to upload attachments. I would be happy to share with you directly.

Chart of the connections:

[radirobi97]

I have found the following bug report about a memory leak. Are we hit by this issue, maybe?

https://bugs.mysql.com/bug.php?id=97935

[rachel_gomez]

Fixing MySQL high memory usage
Fixes for MySQL High Memory usage
MySQL High Memory usage

Depending on what’s causing the memory bottleneck, the solution can vary. Here are the top resolutions for MySQL high memory usage.

1. MySQL settings optimization
   MySQL uses memory in two ways:

Memory permanently reserved for its use – This category of memory known as “Global Buffers” is obtained from the operating system during server boot-up and is not released to any other process.
Memory which is requested dynamically based on requests – MySQL uses “Thread Buffers“, which is memory requested from the operating system as and when a new query is processed. Once the query is executed, this memory is released back to the operating system.
So, you can say the memory usage of MySQL is “Global Buffers + (Thread Buffers x maximum number of allowed connections)”.

This value should always be kept below 90% of server memory for a dedicated database server. If it is a shared server, it should be kept below 50%. Here are a few of the common MySQL settings that determines memory usage, and how you can decide it’s size.

innodb_buffer_pool_size – For InnoDB storage engine, caching is done in the buffer pool. The size of the buffer pool is important for system performance and is assigned a value that is between 50-70% of available RAM. Too small pool size can cause excessive flushing of pages and too large size can cause swapping due to competition for memory.
key_buffer_size – For MyISAM storage engine, this parameter determines the caching and key_buffer_size has to be set according to the RAM, which is around 20% of RAM size.
max_connections – To limit the number of connections possible for MySQL at any instant of time, to avoid a single user from overloading the server, max_connections is used. Each thread uses a portion of the RAM for allotting its buffers and hence limiting the maximum no of connections based on the RAM size is important.
Approx formula, max_connections = (Available RAM – Global Buffers) / Thread Buffers
query_cache_size – The query cache can be useful in an environment where you have tables that do not change very often and for which the web server receives many identical queries such as a blog or record lookup. The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. So this parameter is used only for such application servers or otherwise disabled and set to 0 for other servers. To avoid resource contention, even if it is enabled, the value is set a minimal one of around 10MB.
2. Block resource abusers
When a website is under attack (like DoS, comment spamming, etc.), an abnormally high number of connections could be established in a short time. Use the “PROCESSLIST” in MySQL to identify the top users, and block access to the abusive connections.

3. Fix slow queries
   Identify queries that take a long time to execute – these queries require further optimization for better server performance and are identified from the slow-query log. Slow queries would cause more disk reads which require more memory and CPU usage, which in turn affects the server performance.

Regards,
Rachel Gomez

[rachel_gomez]

wow ok…nice

REgards

[Denis_Subbota]

Hello there,

We usually suggest certain memory parameters such as

• OOM Score = -800 to avoid OOM episodes

The OOM killer checks oom_score_adj to adjust its final calculated score. This file is present in /proc/$pid/oom_score_adj. You can add a large negative score to this file to ensure that your process gets a lower chance of being picked and terminated by OOM killer. The oom_score_adj can vary from -1000 to 1000. If you assign -1000 to it, it can use 100% memory and still avoid getting terminated by OOM killer. On the other hand, if you assign 1000 to it, the Linux kernel will keep killing the process even when it uses minimal memory.

• vm.swappiness = 1 - that means the minimum amount of swapping without disabling it entirely

The default value of vm.swappiness is 60 and represents the percentage of the free memory before activating swap. The lower the value, the less swapping is used and the more memory pages are kept in physical memory.

The value of 60 is a compromise that works well for modern desktop systems. A smaller value is a recommended option for a server system, instead. As the Red Hat Performance Tuning manual points out [8], a smaller swappiness value is recommended for database workloads. For example, for Oracle databases, Red Hat recommends a swappiness value of 10. In contrast, for MariaDB databases, it is recommended to set swappiness to a value of 1 [9].

Changing the value directly influences the performance of the Linux system. These values are defined:

• 0: swap is disable
• 1: minimum amount of swapping without disabling it entirely
• 10: recommended value to improve performance when sufficient memory exists in a system
• 100: aggressive swapping

• Transparent Huge pages = disabled

Firstly, larger pages require more memory usage. The memory allocation function in the kernel will allocate at least the requested page size, and possibly more (rounded up to fit within the available memory). No matter how little memory your application actually requires, at least a full page will be allocated to it.

Secondly, as mentioned earlier, a page must be contiguous in memory and this is true for ‘huge pages’ too. So, if the server cannot find a full page available in a row, it will defragment the memory before allocating it, which can impact performance.

InnoDB is built on a B*-Tree of indices, meaning that its workload will usually have sparse-rather than contiguous-memory access, and, as such, it will likely noticeably perform worse with THP.

Also, if you are using jemalloc with THP, you can eventually end up with the server running out of memory, as it is unable to free unused memory.

It is for this reason we recommend disabling Transparent Huge Pages for database servers

• Jemalloc installed.

The benefits of jemalloc versus glibc memory allocator for use with MySQL have been widely discussed. With jemalloc (along with Transparent Huge Pages disabled) there is less memory fragmentation, and thus more efficient resource management of the server memory.

Regards,
Denis Subbota.
Managed Services, Percona.

[Greg_Bowers]

Hello
The high memory usage you’re observing in Azure Database for MySQL, with a full InnoDB buffer pool, is generally normal behavior. The InnoDB buffer pool is designed to cache frequently used data for performance. Memory usage fluctuates based on your workload. Ensure your queries are optimized, set up monitoring, and consider upgrading MySQL if needed. If concerns persist, consult Azure support.

[Mathijs]

Hey,

I have lots of connections on my MySQL database (1600), most of them being idle. Does this also impact performance in any other way than just RAM? Namely, execution speed, responsiveness, etc.