MySQL Bugs: #80645: Server RSS increases during trigger execution and does not decrease after

Bug #80645	Server RSS increases during trigger execution and does not decrease after
Submitted:	7 Mar 2016 20:59	Modified:	5 Dec 2016 23:24
Reporter:	Geoff Montee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.6.29	OS:	Red Hat (RHEL 5, RHEL 6)
Assigned to:		CPU Architecture:	Any

Description:
A user has noticed that MySQL's memory usage (RSS) increases during trigger execution, and then the memory usage does not decrease again. When observed under Valgrind, the heap does not seem to be unusually large, so I do not know what is using so much memory.

I've attached a few things:

* trigger-create - the script that is used during the tests described below to reproduce this issue.
* my.cnf - the server's configuration file.
* variables.out - the server's global variables from SHOW GLOBAL VARIABLES.
* pidstat.out - the results of pidstat during the pidstat run (see below).
* massif_graph.out - the results of the valgrind run (see below).

In the attached pidstat.out, you can see that RSS grows to almost 1 GB, even though innodb_buffer_pool_size is only 128MB.

How to repeat:
Pidstat run:

1.) Drop the schema, if necessary:

trigger-create -d | mysql -u root

2.) Set up the schema:

trigger-create -i | mysql -u root

3.) Restart the server:

service mysqld restart

4.) Also run pidstat to see how RSS grows:

pidstat -r -p `pidof mysqld` 60 > /tmp/pidstat.out

4.) Run a script that creates the triggers and inserts tuples:

./trigger-create -c -r | mysql -u root

5.) Sleep for some time, so we can see if memory is reclaimed:

mysql -u root --execute="SELECT SLEEP(600)"

6.) When the above is complete, shut down the server:

mysqladmin -u root shutdown

Valgrind run:

1.) Drop the schema, if necessary:

trigger-create -d | mysql -u root

2.) Set up the schema:

trigger-create -i | mysql -u root

3.) Stop the server:

service mysqld stop

4.) Start the server with valgrind:

valgrind --tool=massif --massif-out-file=/tmp/massif.out /usr/sbin/mysqld-debug --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

4.) Run a script that creates the triggers and inserts tuples:

./trigger-create -c -r | mysql -u root

5.) Sleep for some time, so we can see if memory is reclaimed:

mysql -u root --execute="SELECT SLEEP(600)"

5.) When the above is complete, shut down the server:

mysqladmin -u root shutdown

6.) Graph the memory usage:

sudo ms_print /tmp/massif.out > /tmp/massif_graph.out

Suggested fix:
Determine if this memory usage is normal.

Server's configuration file.

Attachment: my.cnf (application/octet-stream, text), 1.04 KiB.

The script that is used during the tests described below to reproduce this issue.

Attachment: trigger-create (application/octet-stream, text), 6.68 KiB.

The server's global variables from SHOW GLOBAL VARIABLES.

Attachment: variables.out (application/octet-stream, text), 12.04 KiB.

The results of pidstat during the pidstat run.

Attachment: pidstat.out (application/octet-stream, text), 1.55 KiB.

The results of the valgrind run.

Attachment: massif_graph.out (application/octet-stream, text), 210.81 KiB.

believe it or not, but 10K tables and triggers does require significant memory. Suggest to use P_S in 5.7 to prove this.  and FLUSH TABLES could help?

Hello Geoff Montee,

Thank you for the report and test case.

Thanks,
Umesh

massif graph

Attachment: massif_graph.out (application/octet-stream, text), 194.42 KiB.

test results

Attachment: 80645.txt (text/plain), 7.74 KiB.

This memory usage is very problematic for some users who use a lot of triggers. Do you have plans to investigate and/or fix this soon?

Posted by developer:
 
Hi Geoff,

We are investigating this issue now, but too early for any feedback yet.

The behavior reported can be divided into two categories:
1. Reason for RSS size growth.
2. Reason for RSS not decreasing.

They are explained below:

1.) The bug report states that the problem is observed
only in 5.6. However, we see that this behavior exists
in all versions of MySQL. The reason it is found on 5.6
is because the value of system variables
'table_open_cache_instances' and 'table_open_cache' is
different in 5.6 when compared to other versions
(5.5/5.7/8.0). E.g., with same settings for above system
variables, the behavior is observed in 5.5/5.7/8.0 also.
The memory growth is relative to the cache settings.

- If user is interested to control the RSS memory usage in
5.6 or any other versions, it is recommended to decrease
the cache setting values accordingly.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cac...

2.) The RSS value depends on how malloc/free works to use
brk()/sbrk() system calls.

https://www.gnu.org/software/libc/manual/html_node/Freeing-after-Malloc.html#Freeing-after...

mentions "Occasionally, free can actually return memory to
the operating system and make the process smaller. Usually
all it can do is allow a later call to malloc to reuse the
space. In the meantime, the space remains in your program
as part of a free-list used internally by malloc."
The way RSS size varies is controlled by OS.

Based on the above analysis, will be closing this as
'not a bug'. Please feel free to share you opinion on the
same.

Hi Karthik,

The user that reported this problem said that the RSS of his mysqld instance has grown as high as 6 GB with only a 128 MB buffer pool. With RSS that high, it sounds unlikely to me that the cause is the table cache. Would you expect that kind of memory usage from the table cache?

Hi Geoff,

Yes. The memory growth is relative to the table cache
configuration.

We have tried the following using the test case specified
in the report:

1. table_open_cache= 2000, table_open_cache_instances= 1
   results in RSS increase by ~450MB.

2. table_open_cache= 2000, table_open_cache_instances= 16
   results in RSS increase by ~150MB.

3. table_open_cache= 400, table_open_cache_instances= 1
   results in RSS increase by ~190MB.

This behavior can be observed in all versions of MySQL.