MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Samuel Ziegler Date:January 25 2007 5:08pm
Subject:Re: Unbounded memory growth in 5.0.24/27
View as plain text  
Thanks for the feedback, Dmitri!  I will try out 5.0.33 first thing this
morning.

A couple of questions for you:
- I assume that copies of the stored procedures are kept due to the
overhead of preparing them for use, but why are the kept on a per
connection basis currently?
- Is there any reason that each connection can't have an upper limit on
the number of stored procedures it keeps around?
- Where is the collection of per connection stored procedures
internally?  (class/member variable)

Thanks again for your feedback!  Hopefully this will unblock me,
  - Sam

On Thu, 2007-01-25 at 12:21 +0300, Dmitri Lenev wrote:
> 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