Bug #59316 Partitioning and index_merge memory leak
Submitted: 5 Jan 2011 20:37 Modified: 26 Apr 2011 14:08
Reporter: Shannon Wade Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.50, 5.5.8 OS:Linux
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: Leak, memory leak
Triage: Triaged: D1 (Critical) / R2 (Low) / E2 (Low)

[5 Jan 2011 20:37] Shannon Wade
Description:
Customer has 1TB db, upgraded to 5.1.50 pluggin + compressed rows + I_S pluggin. Master slave mysqld both suddenly began using all free memory + all swap. Set up a test server with the following results:

compressed rows + I_S plugins = leak (uses all 20G on test + swap 30G VIRT)
no compressed rows + I_S plugins = leak
no compressed rows + NO I_S pluggins = no leak, expected 12G memory usge
compressed rows + NO I_S pluggins = leak

What's odd is without compressed rows but WITH the I_S pluggins it still leaked.

Upgraded to 5.5.8 and same results. pmap on the mysqld process shows two large single memory mappings:

28006: /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/osl-testdb1.err --open-files-limit=8192 --pid-file=/var/lib/mysql/osl-testdb1.pid --socket=/var/lib/mysql/mysql.sock --port=3306
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 9320 3204 0 r-x-- mysqld
0000000000f19000 1112 424 188 rw--- mysqld
000000000102f000 172 172 172 rw--- [ anon ]
000000000122e000 476 4 0 rw--- mysqld
0000000004a0d000 979888 645792 645792 rw--- [ anon ]
000000004070d000 4 0 0 ----- [ anon ]
000000004070e000 10240 8 8 rw--- [ anon ]
000000004125f000 4 0 0 ----- [ anon ]
0000000041260000 10240 8 8 rw--- [ anon ]
0000000041c60000 4 0 0 ----- [ anon ]
0000000041c61000 10240 16 16 rw--- [ anon ]
0000000042661000 4 0 0 ----- [ anon ]
0000000042662000 10240 16 16 rw--- [ anon ]
0000000043062000 4 0 0 ----- [ anon ]
0000000043063000 10240 16 16 rw--- [ anon ]
0000000043a63000 4 0 0 ----- [ anon ]
0000000043a64000 10240 16 16 rw--- [ anon ]
0000000044464000 4 0 0 ----- [ anon ]
0000000044465000 10240 8 8 rw--- [ anon ]
0000000044e65000 4 0 0 ----- [ anon ]
0000000044e66000 10240 8 8 rw--- [ anon ]
0000000045866000 4 0 0 ----- [ anon ]
0000000045867000 10240 8 8 rw--- [ anon ]
0000000046267000 4 0 0 ----- [ anon ]
0000000046268000 10240 8 8 rw--- [ anon ]
0000000046c68000 4 0 0 ----- [ anon ]
0000000046c69000 10240 8 8 rw--- [ anon ]
0000000047669000 4 0 0 ----- [ anon ]
000000004766a000 10240 8 8 rw--- [ anon ]
000000004806a000 4 0 0 ----- [ anon ]
000000004806b000 10240 8 8 rw--- [ anon ]
0000000048a6b000 4 0 0 ----- [ anon ]
0000000048a6c000 10240 8 8 rw--- [ anon ]
000000004946c000 4 0 0 ----- [ anon ]
000000004946d000 10240 20 20 rw--- [ anon ]
0000000049e6d000 4 0 0 ----- [ anon ]
0000000049e6e000 192 8 8 rw--- [ anon ]
0000000049e9e000 4 0 0 ----- [ anon ]
0000000049e9f000 192 16 16 rw--- [ anon ]
0000000049ecf000 4 0 0 ----- [ anon ]
0000000049ed0000 192 60 60 rw--- [ anon ]
0000000049f00000 4 0 0 ----- [ anon ]
0000000049f01000 192 68 68 rw--- [ anon ]
0000000049f31000 4 0 0 ----- [ anon ]
0000000049f32000 192 28 28 rw--- [ anon ]
000000302e200000 112 100 0 r-x-- ld-2.5.so
000000302e41b000 4 4 4 r---- ld-2.5.so
000000302e41c000 4 4 4 rw--- ld-2.5.so
000000302e600000 1336 548 0 r-x-- libc-2.5.so
000000302e74e000 2044 0 0 ----- libc-2.5.so
000000302e94d000 16 16 4 r---- libc-2.5.so
000000302e951000 4 4 4 rw--- libc-2.5.so
000000302e952000 20 20 20 rw--- [ anon ]
000000302ea00000 8 8 0 r-x-- libdl-2.5.so
000000302ea02000 2048 0 0 ----- libdl-2.5.so
000000302ec02000 4 4 4 r---- libdl-2.5.so
000000302ec03000 4 4 4 rw--- libdl-2.5.so
000000302ee00000 520 28 0 r-x-- libm-2.5.so
000000302ee82000 2044 0 0 ----- libm-2.5.so
000000302f081000 4 4 0 r---- libm-2.5.so
000000302f082000 4 4 4 rw--- libm-2.5.so
000000302f200000 88 76 0 r-x-- libpthread-2.5.so
000000302f216000 2044 0 0 ----- libpthread-2.5.so
000000302f415000 4 4 4 r---- libpthread-2.5.so
000000302f416000 4 4 4 rw--- libpthread-2.5.so
000000302f417000 16 4 4 rw--- [ anon ]
0000003030200000 28 16 0 r-x-- librt-2.5.so
0000003030207000 2048 0 0 ----- librt-2.5.so
0000003030407000 4 4 0 r---- librt-2.5.so
0000003030408000 4 4 4 rw--- librt-2.5.so
0000003030600000 52 36 0 r-x-- libgcc_s-4.1.2-20080825.so.1
000000303060d000 2048 0 0 ----- libgcc_s-4.1.2-20080825.so.1
000000303080d000 4 4 4 rw--- libgcc_s-4.1.2-20080825.so.1
0000003030e00000 36 8 0 r-x-- libcrypt-2.5.so
0000003030e09000 2044 0 0 ----- libcrypt-2.5.so
0000003031008000 4 4 4 r---- libcrypt-2.5.so
0000003031009000 4 4 4 rw--- libcrypt-2.5.so
000000303100a000 184 0 0 rw--- [ anon ]
00000033b8000000 4 4 0 r-x-- libaio.so.1.0.1
00000033b8001000 2044 0 0 ----- libaio.so.1.0.1
00000033b8200000 4 4 4 rw--- libaio.so.1.0.1
00002aaaaaab8000 132 132 132 rw--- [ anon ]
00002aaaaaae4000 40 24 0 r-x-- libnss_files-2.5.so
00002aaaaaaee000 2044 0 0 ----- libnss_files-2.5.so
00002aaaaaced000 4 4 4 r---- libnss_files-2.5.so
00002aaaaacee000 4 4 4 rw--- libnss_files-2.5.so
00002aaaaacef000 13185060 9350656 9349864 rw--- [ anon ]
00002aadcf8f8000 212 4 0 r--s- dbBLltKW (deleted)
00002aadd0000000 65508 56148 56148 rw--- [ anon ]
00002aadd3ff9000 28 0 0 ----- [ anon ]
00002aaddbd86000 51880 51860 51860 rw--- [ anon ]
00002aade0000000 65508 39316 39316 rw--- [ anon ]
00002aade3ff9000 28 0 0 ----- [ anon ]
00002aade4000000 65404 49460 49460 rw--- [ anon ]
00002aade7fdf000 132 0 0 ----- [ anon ]
00002aade8000000 65520 44232 44232 rw--- [ anon ]
00002aadebffc000 16 0 0 ----- [ anon ]
00002aadec000000 65360 41416 41416 rw--- [ anon ]
00002aadeffd4000 176 0 0 ----- [ anon ]
00002aadf0000000 65528 40012 40012 rw--- [ anon ]
00002aadf3ffe000 8 0 0 ----- [ anon ]
00002aadf4000000 65256 37632 37632 rw--- [ anon ]
00002aadf7fba000 280 0 0 ----- [ anon ]
00002aadf8000000 65528 36132 36132 rw--- [ anon ]
00002aadfbffe000 8 0 0 ----- [ anon ]
00002aadfc000000 65384 35320 35320 rw--- [ anon ]
00002aadfffda000 152 0 0 ----- [ anon ]
00002aae00000000 65528 34660 34660 rw--- [ anon ]
00002aae03ffe000 8 0 0 ----- [ anon ]
00002aae04000000 65388 28192 28192 rw--- [ anon ]
00002aae07fdb000 148 0 0 ----- [ anon ]
00002aae08000000 65380 29096 29096 rw--- [ anon ]
00002aae0bfd9000 156 0 0 ----- [ anon ]
00002aae0c000000 65396 29444 29444 rw--- [ anon ]
00002aae0ffdd000 140 0 0 ----- [ anon ]
00002aae10000000 16435160 5987968 5987968 rw--- [ anon ]
00002ab8aa63b000 4 4 4 rw--- [ anon ]
00002ab8aa647000 16 16 16 rw--- [ anon ]
00007fffd3842000 84 72 72 rw--- [ stack ]
ffffffffff600000 8192 0 0 ----- [ anon ]
---------------- ------ ------ ------
total kB 31699252 16542668 16537560

The 12G is the configured buffer pool, the 16G is presumably the leak. Other servers BP have been reduced from larger sizes with same effects so BP size seems unrelated.

flushed hosts,logs, query cache, privs, status and tables did not free memory. Memory is returned back to the OS on mysqld shutdown. There are _no_ memory tables, no prepared statements, all other buffers are small. Without compression _and_  I_S pluggins, memory usage is stable as expected as 12G.
No other connections use the test slave (only replication).

How to repeat:
Have not duplicated yet, the DB is large but am trying to see if I can come up with a simplified test case.  Since it seems 1 connection + DML alone will trigger it, not sure if the amount of data and or compression is related. It's happening on his master, slave, as well as the test slave we setup to test the different configurations.

Not sure if will occur on other OS, presumably so? These are 2.6.18-194.11.3.el5.centos.plus #1 SMP Tue Aug 31 07:03:50 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

Have not been able to get valgrind due to exiting everytime either before or at a clean mysqladmin shutdown with:

0:aspacem  Valgrind: FATAL: VG_N_SEGMENTS is too low.
30063:0:aspacem    Increase it and rebuild.  Exiting now.

Am going to try --undef-value-errors=no as well as reducing the size of the buffer pool significantly so there are less segments to track.
[6 Jan 2011 5:06] Shane Bester
how about using a memory profiler to check what uses the memory?
http://goog-perftools.sourceforge.net/doc/heap_profiler.html
[11 Jan 2011 20:53] Shannon Wade
using libtcmalloc and a 10G buffer pool, pprof output is:

Total: 2582.2 MB
  2028.1  78.5%  78.5%   2028.1  78.5% my_malloc
   499.7  19.4%  97.9%    499.7  19.4% ut_malloc_low
    47.8   1.8%  99.7%     47.8   1.8% mem_area_alloc
     6.4   0.2% 100.0%      6.4   0.2% my_realloc
     0.1   0.0% 100.0%      0.1   0.0% my_once_alloc
     0.0   0.0% 100.0%      0.0   0.0% __new_exitfn
     0.0   0.0% 100.0%      0.0   0.0% init_slave
     0.0   0.0% 100.0%      0.0   0.0% _dl_allocate_tls
     0.0   0.0% 100.0%      0.0   0.0% fdopen@@GLIBC_2.2.5
     0.0   0.0% 100.0%      0.0   0.0% LOGGER::init_base
     0.0   0.0% 100.0%      0.0   0.0% __tzfile_read
     0.0   0.0% 100.0%      0.0   0.0% MDL_lock (inline)
     0.0   0.0% 100.0%      0.0   0.0% my_thread_init
     0.0   0.0% 100.0%      0.0   0.0% my_regex_init
     0.0   0.0% 100.0%      0.1   0.0% init_common_variables
     0.0   0.0% 100.0%      0.0   0.0% MDL_lock::create (inline)
     0.0   0.0% 100.0%      0.0   0.0% get_quick_select
     0.0   0.0% 100.0%      0.0   0.0% Events::init
     0.0   0.0% 100.0%      0.1   0.0% acl_init
     0.0   0.0% 100.0%      0.0   0.0% hostname_cache_init
<snip> rest as it's all 0.0
[24 Jan 2011 15:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129458

3271 Mattias Jonsson	2011-01-24
      Bug#59316: Partitioning and index_merge memory leak
      
      When executing row-ordered-retrieval index merge,
      the handler was cloned, but it used the wrong
      memory root, so instead of allocating memory
      on the thread/query's mem_root, it used the table's
      mem_root, resulting in non released memory in the
      table object, and was not freed until the table was
      closed.
      
      Solution was to ensure that memory used during cloning
      of a handler was allocated from the correct memory root.
     @ sql/ha_partition.cc
        Created a new ha_partition constructor for use when
        cloning.
        Also set the partitions handlers to be clones of the
        original tables partitions.
        Moved code from get_from_handler_file to a new function
        read_handler_file.
     @ sql/ha_partition.h
        Moved is_clone variable to handler.h.
        Added helper function get_handler_for_partition.
     @ sql/handler.cc
        Moved allocation of ref to ha_open, also for
        clones.
     @ sql/handler.h
        Added is_clone_of and clone_mem_root.
        For more generic handling of clones
     @ storage/heap/ha_heap.cc
        Removed ha_heap::clone(), using handler::clone() instead.
        Moved clone specific code to ::open() instead.
     @ storage/heap/ha_heap.h
        Removed clone, added helper function hp_info().
     @ storage/myisam/ha_myisam.cc
        Removed clone(), using handler::clone() instead.
        Moved clone specific code to ::open() instead.
     @ storage/myisam/ha_myisam.h
        removed clone().
     @ storage/myisammrg/ha_myisammrg.cc
        Moved is_cloned variable to handler::is_clone_of.
        Removed ::clone().
        Moved clone specific code to ::open().
     @ storage/myisammrg/ha_myisammrg.h
        Moved is_cloned to handler::is_clone_of.
        removed ::clone(), using handler::clone instead.
[24 Jan 2011 17:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129472

3565 Mattias Jonsson	2011-01-24
      Bug#59316: Partitioning and index_merge memory leak
      
      Backport of patch from 5.5 to 5.1.
[24 Jan 2011 18:03] Mattias Jonsson
Note that there are no tests included, we need someway to monitor allocated memory, preferable per table/handler/thread etc. to support tests for this kind of bug. A similar mem leak was bug#27732. Would be good if something like bug#43237 was implemented (or similar in Performance_Schema).

I have verified the fix in both 5.1 and 5.5 by monitoring the mysqld process, as well as using heap profile (from google-perftools).
[5 Apr 2011 21:43] James Day
The workaround for this is:

set global optimizer_switch='index_merge=off'

You can try to prove it's the cause using FLUSH TBLES but that might not have a visible effect sometimes.
[26 Apr 2011 14:08] Jon Stephens
Documented bugfix in the 5.1.58, 5.5.13, and 5.6.3 changelogs, as follows:

      When executing a row-ordered retrieval index merge,
      the partitioning handler used memory from from that allocated 
      for the table, rather than that allocated to the query, causing 
      table object memory not to be freed until the table was closed. 

Closed.
[11 Jul 2011 14:38] Thomas Parrott
Hi,

I am seeing increasing memory usage with MySQL server when using partitioned tables, it keeps growing well beyond the limits of the innodb_buffer_pool setting.

I am running 5.5.14 on x86_64 CentOS 5.6
[12 Jul 2011 18:20] Shannon Wade
Thomas,

Try:

optimizer_switch=index_merge=off

In the my.cnf, restart mysqld see if memory usage increases. Likely it is the result of something else and not related to this bug.

Next when mysqld memory usage is provide:

mysql> SELECT SUM(DATA_LENGTH+INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MEMORY';

Then run:
mysql> flush hosts;
mysql> flush logs;
mysql> flush query cache;
mysql> flush privileges;
mysql> flush status;
mysql> flush tables;

And make sure memory isn't free'd.

Then when mysqld memory is unusually high post my.cnf and output from OS of:

shell> top -b -n 1
shell> free -m
[12 Sep 2011 23:32] Ryan Hendrickson
RedHat 4 update 8.. x86_64
MySQL 5.5.15

I'm seeing the anon's grow 
size         type       count
27816      other     55
21980520   anon   14,284

total size: 22008336

When I say "Other" as a type above: I mean the libraries, mysqld, and any item other than the anon's.

The anon's appear to grow my virtual memory size, and eventually I assume they consume my resident memory as it is growing very large as well.

mysqld is now at 21G virtual and 15G resident.

Ryan