MySQL Bugs: #61829: Increasing memory usage

Bug #61829	Increasing memory usage
Submitted:	12 Jul 2011 8:47	Modified:	18 Dec 2011 15:58
Reporter:	Thomas Parrott	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.5.14	OS:	Linux (x86_64 Centos 5.6)
Assigned to:		CPU Architecture:	Any
Tags:	memory leak

Description:
Since installing MySQL 5.5.8 on the server in January I have seen steadily increasing memory usage far beyond the setting of the innodb_buffer_pool size.

During that period we have been adding roughly 4 partitions a day to 100 databases.

After a restart of the MySQL server the memory usage starts low and then over the course of an active day of inserts and select queries the memory usage returns to its previous amount and then continues growing.

It seems to increase when a new database and tables are opened and then does not go down after that.

How to repeat:
Create tables with partitions and compression enabled (barracuda innodb).
Insert data and select data

Suggested fix:
Unknown, I have tried the fixes suggested here http://bugs.mysql.com/bug.php?id=59316 but they dont seem to worj.

It would be nice to get 3 sets of output of:

show global status;
show engine innodb status\G

and also

free
vmstat 1 10

Linux commands, with say, 1 hour period in between (if you see notable leak during this period).

Command output at 2011-07-12 16:11:00

Attachment: 16_11.txt (text/plain), 31.86 KiB.

Hi,

No Problem I have added a file for 16:11 and will add another at 17:11.

Thanks

Command output at 2011-07-12 17:11:00

Attachment: 17_11.txt (text/plain), 32.74 KiB.

Daily memory growth

Attachment: mysql_mem_daily.png (image/png, text), 21.12 KiB.

Yearly memory growth

Attachment: mysql_mem_yearly.png (image/png, text), 24.28 KiB.

I attached some cacti graphs showing memory usage over time. You can see clearly that near the end of January the memory usage began to grow. On January 18th I installed MySQL 5.5.8 on this server and enabled compression using the barracuda engine.

How many InnoDB tables do you have in total? Do you use partitioned tables? See bug #57480.

Yes we use partitioned tables, 1 per day on multiple tables in about 110 databases.

I'll get the full list shortly.

I have also tried the fixes suggested in #59316, and this has not helped.

if you are familiar with tcmalloc, the easiest option would be to check what uses the memory.

http://google-perftools.googlecode.com/svn/trunk/doc/heapprofile.html

Partitions list

Attachment: PARTITIONS.zip (application/zip, text), 424.49 KiB.

I am not familiar with tmalloc, does it require recompiling MySQL? I am running the official Oracle RPM binaries.

Hi,

Is there any update on this issue?

Thanks

Hi Thomas,

Could you collect two pmap outputs for mysqld process with interval on which memory usage increased significantly?

Did you try FLUSH TABLES command - does it give any (temporary) improve on memory usage?

Current PMAP output

Attachment: pmap-2011-08-03.txt (text/plain), 19.99 KiB.

The flush tables command didn't help, however over the last week or so the memory growth has stopped, and it has levelled off at 2GB. We have recently started deleting some older daily partitions, however there are still thousands of partitions present, and we still create new partitions daily across a hunderd or so databases.

memory usage levelling off

Attachment: memory_usage.png (image/png, text), 23.72 KiB.

PMAP before MySQL restarted, memory usage 2GB.

Attachment: pmap-2011-08-15-a.txt (text/plain), 19.36 KiB.

contents of my.cnf:

[mysqld]
datadir=/var/lib/mysql
user=mysql
symbolic-links=0
init-file=/var/lib/mysql/init.sql
local-infile=0
innodb_file_per_table=1
max_connections=1000
innodb_buffer_pool_size=128M
innodb_file_format=Barracuda

[mysqld_safe]
syslog

PMAP after restart and upgrade to MySQL 5.5.15, using 1.2G and rising.

Attachment: pmap-2011-08-15-b.txt (text/plain), 14.24 KiB.

Hi,

Any update on this? The server has stopped growing now as we are deleting partitions by day, but it is still using large amounts of memory.

I am thinking it is caused by having many tables (partitions), and is similiar to other bugs with innodb's data dictionary.

Hi,

I restarted the server yesterday and the memory has shot up now higher than it has ever been. We currently have around 55,000 tables (including partitions), and 140 databases.

Attached is the latest pmap from that server, where MySQL has been running for less than 24 hours.

pmap output

Attachment: pmap-2011-09-15.txt (text/plain), 26.03 KiB.

The output of show engine innodb status shows some memory usage stats:

Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 533000669

Is this a problem caused because innodb does not free up it's data dictionary?

Probably a duplicate of http://bugs.mysql.com/57480 , in part because the allocations stabilise when no more partitions are being created/loaded.

Let's assume this is a duplicate of Bug #57480, at least until it is fixed and we will get a problem in the version with fix.