Bug #48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
Submitted: 12 Nov 2009 14:05 Modified: 14 Oct 2010 13:45
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.0, 5.1, 5.4, 6.0 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: Leak, memory leak

[12 Nov 2009 14:05] Alexey Stroganov
Description:
While I tried to backup db that has a huge number of stored procedures(400M) I observed large grow of memory. Further analysis showed that backup subsystem 
performs following query and this query actually causes problem:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

I rerun the same query with smaller set of procedures like 1M and still observe
the issue.

I've rerun query under valgrind --tool=massif and below are some observations:

1)

   MB
438.5^                                                           ... .,.::..:,
     |                                                 ,....: :@ ::: :#::::::@
     |                                       , ..,:: : @::::: :@ ::: :#::::::@
     |                              ,.. @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     |                  . . .,::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     |        .. .. : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | . ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
     | : ::: ::: :: : @:: : :@::: : @:: @: ::@ ::@:: : @::::: :@ ::: :#::::::@
   0 +----------------------------------------------------------------------->Gi
     0                                                                   25.03

above diagram represents memory grow over time. It starts with 319MB and stops with 438MB.

2) there are two constant allocations:

->99.96% (351,879,761B) 0x8EE031: my_malloc (my_malloc.c:37)
| ->72.78% (256,195,584B) 0x901F69: my_large_malloc (my_largepage.c:64)
| | ->72.78% (256,195,584B) 0x8E7E7F: init_key_cache (mf_keycache.c:439)
| |   ->72.78% (256,195,584B) 0x70890A: ha_init_key_cache (handler.cc:3731)
| |     ->72.78% (256,195,584B) 0x6169D2: process_key_caches(int (*)(char const*, st_key_cache*)) (set_var.cc:4808)
| |       ->72.78% (256,195,584B) 0x5FFDA1: _ZL22init_server_componentsv (mysqld.cc:4232)
| |         ->72.78% (256,195,584B) 0x603013: main (mysqld.cc:4745)
| |
| ->16.58% (58,376,392B) 0x8E7E95: init_key_cache (mf_keycache.c:447)
| | ->16.58% (58,376,392B) 0x70890A: ha_init_key_cache (handler.cc:3731)
| |   ->16.58% (58,376,392B) 0x6169D2: process_key_caches(int (*)(char const*, st_key_cache*)) (set_var.cc:4808)
| |     ->16.58% (58,376,392B) 0x5FFDA1: _ZL22init_server_componentsv (mysqld.cc:4232)
| |       ->16.58% (58,376,392B) 0x603013: main (mysqld.cc:4745)

3) variable parts look like following:

| ->15.88% (58,376,392B) 0x8E7E95: init_key_cache (mf_keycache.c:447)
| | ->15.88% (58,376,392B) 0x70890A: ha_init_key_cache (handler.cc:3731)
| |   ->15.88% (58,376,392B) 0x6169D2: process_key_caches(int (*)(char const*, st_key_cache*)) (set_var.cc:4808)
| |     ->15.88% (58,376,392B) 0x5FFDA1: _ZL22init_server_componentsv (mysqld.cc:4232)
| |       ->15.88% (58,376,392B) 0x603013: main (mysqld.cc:4745)
| |
| ->14.13% (51,950,448B) 0x8EE8D0: alloc_root (my_alloc.c:201)
| | ->14.02% (51,569,552B) 0x8EE9B1: strmake_root (my_alloc.c:405)
| | | ->14.02% (51,569,552B) 0x654F49: get_field(st_mem_root*, Field*, String*) (table.cc:2761)
| | |   ->03.22% (11,836,704B) 0x7353E0: store_schema_proc(THD*, TABLE*, TABLE*, char const*, bool, char const*) (sql_show.c
| | |   | ->03.22% (11,836,704B) 0x73E346: fill_schema_proc(THD*, TABLE_LIST*, Item*) (sql_show.cc:4805)
| | |   |   ->03.22% (11,836,704B) 0x73375F: get_schema_tables_result(JOIN*, enum_schema_table_state) (sql_show.cc:6639)
| | |   |     ->03.22% (11,836,704B) 0x68943C: JOIN::exec() (sql_select.cc:2434)
| | |   |       ->03.22% (11,836,704B) 0x685399: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsi
| | |   |         ->03.22% (11,836,704B) 0x68B1D3: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:3
| | |   |           ->03.22% (11,836,704B) 0x60AF56: _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST (sql_parse.cc:4969)
| | |   |             ->03.22% (11,836,704B) 0x60F6FA: mysql_execute_command(THD*) (sql_parse.cc:2158)
| | |   |               ->03.22% (11,836,704B) 0x6136F4: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.c
| | |   |                 ->03.22% (11,836,704B) 0x6145F3: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql
| | |   |                   ->03.22% (11,836,704B) 0x607BE4: handle_one_connection (sql_connect.cc:1164)
| | |   |                     ->03.22% (11,836,704B) 0x4C30142: start_thread (in /lib64/libpthread-2.4.so)
| | |   |                       ->03.22% (11,836,704B) 0x54A674C: clone (in /lib64/libc-2.4.so)

How to repeat:
- Start server
- create N stored procedures
- run query: SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
- check memory usage

Will provide simplified test case for the issue a bit later.
[12 Mar 2010 14:58] 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/103110

3968 Sergey Glukhov	2010-03-12
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S tables for
      ROUTINES, PARAMETERS, EVENTS we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      The fix is to avoid use of get_field().
     @ sql/event_db_repository.cc
        added new parameter
     @ sql/sql_show.cc
        Functions store_schema_params(), store_schema_proc(),
        copy_event_to_schema_table() are changed to avoid
        use of get_field() function.
     @ sql/sql_show.h
        added new parameter
[16 Mar 2010 18:20] Sveta Smirnova
Bug #52086 was marked as duplicate of this one.
[6 May 2010 10:25] 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/107626

4057 Sergey Glukhov	2010-05-06
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S tables for
      ROUTINES, PARAMETERS, EVENTS we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      The fix is to avoid use of get_field().
     @ sql/sql_show.cc
        Functions store_schema_params(), store_schema_proc(),
        copy_event_to_schema_table() are changed to avoid
        use of get_field() function.
[18 May 2010 8:31] 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/108481

4067 Sergey Glukhov	2010-05-18
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S tables for
      ROUTINES, PARAMETERS we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      The fix is to avoid use of get_field().
     @ sql/sql_show.cc
        Functions store_schema_params(), store_schema_proc()
        are changed to avoid use of get_field() function.
[18 May 2010 9:08] 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/108484

3386 Sergey Glukhov	2010-05-18
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S table
      ROUTINES we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      the fix is to avoid use of get_field().
     @ sql/sql_show.cc
        Functions store_schema_proc() are changed
        to avoid use of get_field() function.
[18 May 2010 9:28] 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/108488

3386 Sergey Glukhov	2010-05-18
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S table
      ROUTINES we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      the fix is to avoid use of get_field().
     @ sql/sql_show.cc
        Functions store_schema_proc() are changed
        to avoid use of get_field() function.
[18 May 2010 9:33] 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/108492

4067 Sergey Glukhov	2010-05-18 [merge]
      Bug#48729 SELECT ... FROM INFORMATION_SCHEMA.ROUTINES causes memory to grow
      Analysis showed that in case of accessing I_S tables for
      ROUTINES, PARAMETERS we perform unnecessary allocations
      with get_field() function for every processed row that
      in their turn causes significant memory growth.
      The fix is to avoid use of get_field().
     @ sql/sql_show.cc
        Functions store_schema_params(), store_schema_proc()
        are changed to avoid use of get_field() function.
[28 May 2010 5:58] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (pib:16)
[28 May 2010 6:27] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100524190409-5w4l7mje1wk1c90l) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:55] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100523204118-0tl3goawu658rxh6) (merge vers: 5.5.5-m3) (pib:16)
[2 Jun 2010 8:50] Bugs System
Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:sergey.glukhov@sun.com-20100518082821-yajhvbv1ghmlpu1n) (merge vers: 5.1.47) (pib:16)
[8 Jun 2010 18:42] Paul Dubois
Noted in 5.1.48, 5.5.5, 6.0.14 changelogs.

Selecting from INFORMATION_SCHEMA.ROUTINES or
INFORMATION_SCHEMA.PARAMETERS resulted in a memory leak.
[6 Jul 2010 19:00] Paul Dubois
Noted in 5.1.46sp1 changelog.
[8 Jul 2010 18:53] Bugs System
Pushed into 5.1.49 (revid:sunanda.menon@sun.com-20100708184626-16el4v8gjjci6m1r) (version source revid:sunanda.menon@sun.com-20100708184626-16el4v8gjjci6m1r) (merge vers: 5.1.49) (pib:16)
[4 Aug 2010 7:52] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)
[4 Aug 2010 8:10] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:26] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 9:05] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[4 Aug 2010 23:06] Paul Dubois
Already fixed in 5.1.x, 5.5.x.
Bug does not appear in any released 5.6.x version.
[14 Oct 2010 8:38] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:53] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:10] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 13:45] Jon Stephens
Already documented in the 5.1.48 changelog; no additional changelog entries required. Set back to Closed state.