MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Dmitri Lenev Date:January 25 2007 9:21am
Subject:Re: Unbounded memory growth in 5.0.24/27
View as plain text  
Hello, Samuel!

* Samuel Ziegler <mysql@stripped> [07/01/25 09:11]:
> I'm seeing a problem with MySQL 5.0.24 & 5.0.27 where the mysqld
> process grows in memory quickly, hits the 3G process limit, reports
> out of memory then the process shuts down.  Sometimes cleanly, sometimes
> not so cleanly.

...

> I abnormally terminated the mysql process under valgrind when it was close
> to the 3G limit and got a good trace of the active memory.

...

> Frist stored proc entry:
> ==2281== 69,383,952 bytes in 8,478 blocks are still reachable in loss
> record 398 of 399
> ==2281==    at 0x401A812: malloc (vg_replace_malloc.c:149)
> ==2281==    by 0x8384721: my_malloc (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x838A39F: init_dynamic_array (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x838A192: _hash_init (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x8103D8D: Query_tables_list::reset_query_tables_list(bool)
> (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81042A0: st_lex::st_lex() (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x826A3FF: sp_head::reset_lex(THD*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x8197FBC: MYSQLparse(void*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x82711A9: db_load_routine(THD*, int, sp_name*, sp_head**,
> unsigned long, char const*, char const*, char const*, st_sp_chistics&,
> char const*, long long, long long) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x8270D54: db_find_routine(THD*, int, sp_name*, sp_head**)
> (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x82724E9: sp_cache_routines_and_add_tables_aux(THD*,
> st_lex*, Sroutine_hash_entry*, bool, bool*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x8270648: sp_cache_routines_and_add_tables(THD*, st_lex*,
> bool, bool*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> 
> Note the large memory usage & 8k of blocks.  There are several more
> entires from the stored procedure code which total ~50k individual
> malloc blocks.

In the first place I think that you should try version 5.0.33 as it
contains fix which significantly decreases amount of memory allocation
in exactly this place.

See http://lists.mysql.com/commits/14671 for more info.

Secondly, keep in mind that at this point caches of stored routines
in MySQL are connection specific rather than global. So each connection
has its own copy of each stored routine it has ever used.
And each copy can hog rather big chunk of memory. :(

Indeed in the case when one has big number of connections and/or each
connection uses big number of stored routines during its lifetime such
caches can consume noticiable amounts of memory.

There are plans to solve both these problems, not very immediate tough.

> 
> Prepared statement snippet:
> ==2281== 61,809,024 bytes in 318 blocks are still reachable in loss record
> 396 of 399
> ==2281==    at 0x401A812: malloc (vg_replace_malloc.c:149)
> ==2281==    by 0x8384721: my_malloc (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x8384DDB: alloc_root (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81B296B: openfrm(THD*, char const*, char const*,
> unsigned, unsigned, unsigned, st_table*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81B0418: open_unireg_entry(THD*, st_table*, char const*,
> char const*, char const*, st_table_list*, st_mem_root*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81AC225: open_table(THD*, st_table_list*, st_mem_root*,
> bool*, unsigned) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81AD1D2: open_tables(THD*, st_table_list**, unsigned*,
> unsigned) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81AD5DD: open_and_lock_tables(THD*, st_table_list*) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81DAC3B: mysql_test_select(Prepared_statement*,
> st_table_list*, bool) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81DA01F: check_prepared_statement(Prepared_statement*,
> bool) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81D950D: Prepared_statement::prepare(char const*,
> unsigned) (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> ==2281==    by 0x81D7DF7: mysql_stmt_prepare(THD*, char const*, unsigned)
> (in
>
> /local/apollo/package/local_1/Generic/A2ZEB-SDS-MySQL/A2ZEB-SDS-MySQL-2.0-0/mysql/bin/mysqld)
> 
> Bigger than I would have guessed, but in only 318 blocks.

Actually above does not look like something specific to prepared
statements, but more like an ordinary memory allocation which
happens during table opening (and preparation of statement just
happens to be the first place where we open these table).

AFAIU amount of memory allocated for each table being open highly
depends on its structure... I wonder how big is the typical/maximum
(as defined) size of the row in your tables?

> Another interesting point is that the memory reported in use by
> valgrind was only ~730MB.
> 
> My current theory is that the large number of mallocs from the stored
> procedure code is causing significant memory fragmentation which in
> turn is making the address space go bye-bye.

Memory fragmentation because of large number of 8K blocks allocated
and then released? It is possible but I don't really think it is 
very probable...

> I have not yet tried the latest 5.0.33 build, this problem may be
> resolved in there.
> 
> So... Questions:

> - Is this behavior expected/known?
  
  At least the first problem that you have pointed out is known
  and has been already partially solved.

> - Is my theory the likely cause?

  It is possible but not very probable IMO. 

> - How can I fix it?
  
  Please try out version 5.0.33 and let us know about the results !!! 

Best regards,

-- 
Dmitri Lenev, Software Developer
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification
Thread
Unbounded memory growth in 5.0.24/27Samuel Ziegler25 Jan
  • Re: Unbounded memory growth in 5.0.24/27Dmitri Lenev25 Jan
    • Re: Unbounded memory growth in 5.0.24/27Samuel Ziegler25 Jan
      • Re: Unbounded memory growth in 5.0.24/27Dmitri Lenev30 Jan
        • Re: Unbounded memory growth in 5.0.24/27Samuel Ziegler1 Feb