| 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: | |
| 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 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.
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.