Troubleshooting MySQL memory issues

An out-of-memory (OOM) condition occurs when a process attempts to allocate memory and fails because the server has no memory available. This often results in the Linux OOM Killer, a process that the Linux kernel employs when the system is critically low on memory, terminating the database process. 

A database crash due to an OOM condition is not ideal because the database process is terminated using the SIGKILL directive which gives the application no chance of normal MySQL shutdown. It causes the following problems:

• MySQL server is down (while it completes the crash recovery which can take several minutes) impacting the users and business
• MySQL server takes additional time to fully warm up the buffers and caches to reach the peak performance
• Possibility of data corruption due to an unexpected crash

MySQL added memory instrumentation in performance_schema starting in the 5.7 version which helps to understand MySQL memory allocation. This article will help you to troubleshoot MySQL memory issues using performance_schema.

Each Cloud SQL instance is powered by a virtual machine (VM) running on a host Google Cloud server. Each VM operates the database program, such as MySQL Server and service agents that provide supporting services, such as logging and monitoring. Cloud SQL reserves some portion of memory for the operating system (OS), service agents and allocate remaining memory for database programs such as mysqld. 

The following table shows memory allocation for the mysqld process and the default InnoDB buffer pool configuration for each machine type.

For instances that have 10GB RAM and above, mysqld process is limited to use 92% of instance RAM and InnoDB buffer pool is configured in the range of 75-80% of the memory available for the mysqld process. In addition to the InnoDB buffer pool, MySQL allocates several buffers and caches to improve performance. Please refer to this article to understand memory allocation in MySQL.

When mysqld memory usage grows beyond the configured limit of the mysqld process, Linux OOM killer kills the mysqld process as it is the largest consumer of memory on the instance.

performance_schema is a MySQL feature for monitoring server execution at a low level. Cloud SQL for MySQL allows enabling performance_schema on the instances having RAM size 3 GB or above. It is enabled by default in instances with at least 15GB RAM, starting with the MySQL 8.0 version. 

Performance_schema is disabled by default for MySQL 5.6 and 5.7 versions. It can be enabled using database flags. 

Enabling performance_schema involves some performance overhead and it also has memory implications. See The Performance Schema Memory-Allocation Model to understand its memory allocation. 

performance_schema memory instrumentation helps to monitor memory usage in MySQL. Memory instruments have names in the form of memory/code_area/instrument_name, where code_area is a value such as sql or Innodb, and instrument_name is the instrument detail.

MySQL 5.7 enables some memory instruments by default but not all. You can enable them by updating the ENABLED column of the performance_schema.setup_instruments table. 

mysql> select version();

+-------------------+

| version()    |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   70 |

| NO   |   306 |

+---------+----------+

2 rows in set (0.00 sec)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376 Changed: 306 Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   376 |

+---------+----------+

1 row in set (0.00 sec)

Enabling memory instruments at the runtime will only count the memory objects created after enabling the instrumentation. Therefore, it is recommended to wait for the server to collect enough data for troubleshooting purposes.

MySQL 8.0 enables all memory instruments by default.

mysql> select version();

+---------------+

| version()  |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   490 |

+---------+----------+

1 row in set (0.00 sec)

Sys schema contains many views that summarize performance_schema data into easily understandable forms. Most of these views come in pairs. They share the same name and one of them has an x$ prefix. The following views are available in MySQL 5.7 and 8.0 to view the memory allocation at different levels.

mysql> use sys;

Database changed

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)      |

+-------------------------------------+

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_global_by_current_bytes   |

| memory_global_total        |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes  |

| x$memory_global_total       |

+-------------------------------------+

10 rows in set (0.02 sec)

The sys.memory_global_total view summarizes the total memory usage in a readable format.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB   |

+-----------------+

1 row in set (0.44 sec)

The sys.x$memory_global_total displays the same information but in bytes.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|  13351482992 |

+-----------------+

1 row in set (1.39 sec)

The view without the x$ prefix is intended to provide output that is more user friendly and easier to read. The view with the x$ prefix that displays the same values in raw form is intended more for use with other tools that perform their own processing on the data.

The sys.memory_global_by_current_bytes view shows the current memory usage grouped by the allocation type (event_name) for example memory/innodb/buf_buf_pool (InnoDB buffer pool). By default, rows are sorted by descending amount of memory used. 

Before we drill down into each allocation type, it is good to have an overview of memory usage for each code area such as Innodb, sql, performance_schema, and more. 

The following query aggregates the currently allocated memory by the code area. 

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area        | current_alloc |

+---------------------------+---------------+

| memory/innodb      | 11.28 GiB  |

| memory/performance_schema | 520.02 MiB  |

| memory/temptable     | 514.00 MiB  |

| memory/sql        | 106.12 MiB  |

| memory/mysys       | 43.25 MiB  |

| memory/vio        | 372.15 KiB  |

| memory/myisam      | 696 bytes  |

| memory/csv        | 88 bytes  |

| memory/blackhole     | 88 bytes  |

+---------------------------+---------------+

9 rows in set (1.02 sec)

This output shows that innodb is the largest consumer of memory followed by performance_schema and temporary tables.

Now, querying the sys.memory_global_by_current_bytes directly shows the memory usage for MySQL internal structures as described in the event_name column. If you’re troubleshooting the abnormal increase in memory, you can identify the source by looking at the output of this view.

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name          | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool  | 10.72 GiB  | 10.72 GiB |

| memory/temptable/physical_ram | 514.00 MiB  | 515.00 MiB |

| memory/innodb/hash0hash   | 292.69 MiB  | 292.69 MiB |

| memory/innodb/memory     | 191.77 MiB  | 197.94 MiB |

| memory/sql/TABLE       | 60.39 MiB  | 62.35 MiB |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

Below are the details of each allocation type.

This view summarizes the memory usage grouped by the user, if a user has multiple connections, it sums the memory usage for all the instances of the user.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user      | current_allocated |

+-----------------+-------------------+

| sbtest     | 60.30 MiB    |

| background   | 2.68 MiB     |

| root      | 1.52 MiB     |

| event_scheduler | 16.38 KiB    |

+-----------------+-------------------+

4 rows in set (1.16 sec)

If you use a different user for each application/job (recommended), this view can help to identify if a particular application or job is consuming high memory.

This view summarizes the memory usage grouped by the hostname.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host   | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB    |

| background | 2.70 MiB     |

| 127.0.0.1 | 311.01 KiB    |

| localhost | 16.38 KiB    |

+------------+-------------------+

4 rows in set (1.58 sec)

This view is useful for identifying memory usage per client host. Having multiple client/application servers is very common these days, this view will help to identify if any particular client or workload is causing high memory usage. You can also deploy the change on one client/application server first, monitor its impact on memory (by comparing memory usage to other client hosts) and make informed decisions.

This view summarizes memory use, grouped by thread, it helps to find the threads which take most of the memory.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user       | current_allocated |

+-----------+-------------------+-------------------+

|  12999 | sbtest@10.128.0.8 | 3.80 MiB     |

|  12888 | sbtest@10.128.0.8 | 3.75 MiB     |

|  12760 | sbtest@10.128.0.8 | 3.68 MiB     |

|  13128 | sbtest@10.128.0.8 | 3.67 MiB     |

|  13221 | sbtest@10.128.0.8 | 3.47 MiB     |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

If you find a particular thread is consuming high memory, you can filter the MySQL slow query logs or general logs using the thread_id and identify the session/queries which led to the memory growth.

Troubleshooting high memory usage is a challenging task. Thankfully, performance_schema memory instrumentation helps users to understand how memory is used by internal MySQL structures. The sys schema views make it easier to interpret the information. If you run into memory related issues, this feature will help you to find the source of the problem and you can fix the same by making the configuration/query changes. 

For example:

1. If internal temporary tables require a high amount of memory, you can either limit the size of temporary tables using tmp_table_size or reduce the innodb_buffer_pool_size to make room for per-session buffers
2. If a particular thread is consuming high memory, you can filter slow query logs using thread_id to identify the problematic queries and tune them to reduce the memory footprint for that thread/session

If you run into a situation where a MySQL instance is using the memory higher than it should and memory usage is increasing continuously until the server gets out of memory, and the performance_schema either shows a gap in total versus instrumented memory or doesn’t tell where memory is going, it could be a symptom of a memory leak. If you suspect a memory leak, you can take the following steps:

• Review the MySQL configuration carefully and confirm that no buffers/caches are over-allocated. This article will help you to understand how MySQL allocates memory for different buffers/caches.
• Identify the feature/configuration/query causing the memory issue, try to reproduce the issue.
• Review the MySQL release notes of the next minor versions in the same series to check if any bug(s) with similar behavior were fixed. 
• Upgrade MySQL to the latest minor version to see if that fixes the memory issue.
• Search through the MySQL bugs database to check if any similar issue was reported by other community users.
• Create a new bug with a reproducible test case if there is no existing bug for the same issue.