Google Cloud’s Cloud SQL for MySQL is a fully managed database service that helps you set up, maintain, manage, and administer your MySQL, PostgreSQL, or SQL Server database on Google Cloud Platform. Cloud SQL runs a large number of MySQL databases in its fleet and it is common to see some MySQL instances consuming high memory and crashing because of out-of-memory (OOM) error. This article will help you to troubleshoot 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 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.
Machine type | Instance RAM GIB (Bytes) | mysqld memory (GB) | InnoDB Buffer Pool GiB (Bytes) | Buffer Pool (% of Instance RAM) | Buffer Pool (% of mysqld memory) |
db-g1-small | 1.7(1825361101) | 1.11 | 0.625(671088640) | 37% | 56% |
db-custom-1-3840 | 3.75(4026531840) | 2.97 | 1.375(1476395008) | 37% | 46% |
db-custom-2-7680 | 7.5(8053063680) | 6.72 | 4(4294967296) | 53% | 60% |
db-custom-2-13312 | 13(13958643712) | 12 | 9(9663676416) | 69% | 75% |
db-custom-4-15360 | 15(16106127360) | 14 | 10.5(11274289152) | 70% | 76% |
db-custom-4-26624 | 26(27917287424) | 24 | 19(20401094656) | 73% | 79% |
db-custom-8-30720 | 30(32212254720) | 28 | 22(23622320128) | 73% | 80% |
db-custom-8-53248 | 52(55834574848) | 48 | 38(40802189312) | 73% | 79% |
db-custom-16-61440 | 60(64424509440) | 55 | 44(47244640256) | 73% | 80% |
db-custom-16-106496 | 104(111669149696) | 96 | 75(80530636800) | 72% | 78% |
db-custom-32-122880 | 120(128849018880) | 110 | 87(93415538688) | 73% | 79% |
db-custom-32-212992 | 208(223338299392) | 191 | 150(161061273600) | 72% | 78% |
db-custom-64-245760 | 240(257698037760) | 221 | 173(185757335552) | 72% | 78% |
db-custom-96-368640 | 360(386547056640) | 331 | 260(279172874240) | 72% | 79% |
db-custom-64-425984 | 416(446676598784) | 383 | 300(322122547200) | 72% | 78% |
db-custom-96-638976 | 624(670014898176) | 574 | 450(483183820800) | 72% | 78% |
Machine type
Instance RAM GIB (Bytes)
mysqld memory (GB)
InnoDB Buffer Pool GiB (Bytes)
Buffer Pool (% of Instance RAM)
Buffer Pool (% of mysqld memory)
db-g1-small
1.7(1825361101)
1.11
0.625(671088640)
37%
56%
db-custom-1-3840
3.75(4026531840)
2.97
1.375(1476395008)
37%
46%
db-custom-2-7680
7.5(8053063680)
6.72
4(4294967296)
53%
60%
db-custom-2-13312
13(13958643712)
12
9(9663676416)
69%
75%
db-custom-4-15360
15(16106127360)
14
10.5(11274289152)
70%
76%
db-custom-4-26624
26(27917287424)
24
19(20401094656)
73%
79%
db-custom-8-30720
30(32212254720)
28
22(23622320128)
73%
80%
db-custom-8-53248
52(55834574848)
48
38(40802189312)
73%
79%
db-custom-16-61440
60(64424509440)
55
44(47244640256)
73%
80%
db-custom-16-106496
104(111669149696)
96
75(80530636800)
72%
78%
db-custom-32-122880
120(128849018880)
110
87(93415538688)
73%
79%
db-custom-32-212992
208(223338299392)
191
150(161061273600)
72%
78%
db-custom-64-245760
240(257698037760)
221
173(185757335552)
72%
78%
db-custom-96-368640
360(386547056640)
331
260(279172874240)
72%
79%
db-custom-64-425984
416(446676598784)
383
300(322122547200)
72%
78%
db-custom-96-638976
624(670014898176)
574
450(483183820800)
72%
78%
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.
Allocation Type | Details |
memory/innodb/buf_buf_pool | Memory used by InnoDB Buffer Pool |
memory/temptable/physical_ram | Memory used by internal temporary tables |
memory/innodb/hash0hash | Memory used by InnoDB hash table |
memory/innodb/memory | Memory used by various InnoDB buffers |
memory/sql/TABLE | Memory used by TABLE objects and their mem root |
Allocation Type
Details
memory/innodb/buf_buf_pool
Memory used by InnoDB Buffer Pool
memory/temptable/physical_ram
Memory used by internal temporary tables
memory/innodb/hash0hash
Memory used by InnoDB hash table
memory/innodb/memory
Memory used by various InnoDB buffers
memory/sql/TABLE
Memory used by TABLE objects and their mem root
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:
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:
Start building on Google Cloud with $300 in free credits and 20+ always free products.