Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.27
-
None
-
mysql Ver 15.1 Distrib 10.2.27-MariaDB, for Linux (x86_64) using readline 5.1
CentOS Linux release 7.7.1908 (Core)
MariaDB-server-10.2.27-1.el7.centos.x86_64
Description
Upon querying information_schema, the memory footprint of the database process increases until it is killed by the OOM handler
Reproducible by:
$ /bin/ps -ylC mysqld | /bin/awk '{x += $8;y += 1} END {print "Memory Usage (MB): "x/1024, "\n\n"}'; sudo -H mysql -e "select sum(data_length+index_length) from information_schema.tables where engine='memory';";/bin/ps -ylC mysqld | /bin/awk '{x += $8;y += 1} END {print "Memory Usage (MB): "x/1024, "\n\n"}';
|
Memory Usage (MB): 3740.43
|
|
|
+-------------------------------+
|
| sum(data_length+index_length) |
|
+-------------------------------+
|
| 0 |
|
+-------------------------------+
|
Memory Usage (MB): 6195.26
|
Affected customer has 227 databases, with ~22 tables each
Attachments
Activity
What probably doesn't help is that the tables have various (complex) triggers and constraints for which I am not sure those can be shared without the customers consent.
- All the constraints seem to use a foreign key
- There are triggers that trigger an insert into another table for logging purposes
- There seems to be a trigger that iterates over rows after an update and uses conditionals to log deltas in another table for logging purposes. some of the logging data is assembled using CONCAT
The table for logging purposes uses InnoDB
Is it possible to get memleak from bcc-tools and use a command like:
memleak -o 16000 -T 10 -p $(pidof mysqld)
|
I do have some concerns that a Centos7 kernel may not support this however is worth a test.
For better debugging purposes the workload was moved of MariaDB to MySQL where performance_schema can be used to inspect memory consumption.
It was observed that memory/sql/sp_head::main_mem_root balloons very quickly. The customer is still adjusting queries and the triggers used in there
Thanks for the feedback.
You may have seen MariaDB-10.5 has introduced the performance schema memory instrumentation.
If we can assist on the MariaDB side let us know. A MariaDB statement based replica of a MySQL master might offer some insight of a production instance without the requirement to be production HA.
We wish you and your customer best of wishes resolving this leak.
It stabilizes for me within several calls:
Memory Usage (MB): 539.969+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 609.176Memory Usage (MB): 609.176+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 624.645Memory Usage (MB): 624.645+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 636.246Memory Usage (MB): 636.246+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 640.887Memory Usage (MB): 640.887+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 640.887Memory Usage (MB): 640.887+-------------------------------+| sum(data_length+index_length) |+-------------------------------+| 0 |+-------------------------------+Memory Usage (MB): 640.887The SQL that I used to create 227 databases, 22 tables each is attached 227x22.sql