[MDEV-20933] Querying information_schema leads to memory leak Created: 2019-10-31  Updated: 2020-10-01

Status: Open
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.2.27
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Proserve (operations) Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Memory_leak, bug, information_schema
Environment:

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

Attachments: File 227x22.sql    

 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

 Comments   
Comment by Elena Stepanova [ 2019-11-05 ]

It stabilizes for me within several calls:

Memory Usage (MB): 539.969 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 609.176 


Memory Usage (MB): 609.176 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 624.645 


Memory Usage (MB): 624.645 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 636.246 


Memory Usage (MB): 636.246 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 640.887 


Memory Usage (MB): 640.887 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 640.887 


Memory Usage (MB): 640.887 
 
 
 
 
+-------------------------------+
 
| sum(data_length+index_length) |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
Memory Usage (MB): 640.887

The SQL that I used to create 227 databases, 22 tables each is attached 227x22.sql

Comment by Proserve (operations) [ 2020-05-18 ]

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

Comment by Daniel Black [ 2020-09-30 ]

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.

Comment by Proserve (operations) [ 2020-09-30 ]

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

Comment by Daniel Black [ 2020-10-01 ]

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.

Generated at Thu Feb 08 09:03:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.