Bug #42705 Reduce malloc/free during query lifecycle.

• Submitted: 9 Feb 2009 16:37
• Modified: 23 Mar 2009 16:12
• Reporter: Neel Nadgir
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.30
• OS: Any
• CPU Architecture: Any
• Tags: malloc, performance

[9 Feb 2009 16:37] Neel Nadgir

Description:
I took a quick look at memory/free done by mysql server for a simple query (select id from table where id=x, id is indexed), and observed atleast 5 mallocs and free. The use of a scalable memory allocator will greatly improve performance by reducing contention around malloc, however we can do better.

The mallocs are originating from the following callstacks. I am sure storage engines call malloc, but I am not including them here..

               
              libmtmalloc.so.1`malloc
              mysqld`my_malloc+0x32
              mysqld`_Z13get_lock_dataP3THDPP8st_tablejjS3_+0x8a
              mysqld`_Z17mysql_lock_tablesP3THDPP8st_tablejjPb+0x7f
              mysqld`_Z11lock_tablesP3THDP10TABLE_LISTjPb+0x2c4
              mysqld`_Z28open_and_lock_tables_derivedP3THDP10TABLE_LISTb+0x42
              mysqld`_Z21execute_sqlcom_selectP3THDP10TABLE_LIST+0x5d
              mysqld`_Z21mysql_execute_commandP3THD+0x29a2
              mysqld`_Z11mysql_parseP3THDPKcjPS2_+0x212
              mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0xc80
              mysqld`_Z10do_commandP3THD+0xf1
              mysqld`handle_one_connection+0x71e
              libc.so.1`_thrp_setup+0x8d
              libc.so.1`_lwp_start

              libmtmalloc.so.1`malloc
              mysqld`my_malloc+0x32
              mysqld`_Z13get_lock_dataP3THDPP8st_tablejjS3_+0x8a
              mysqld`_Z24mysql_unlock_some_tablesP3THDPP8st_tablej+0x1a
              mysqld`_ZN4JOIN8optimizeEv+0x1829              mysqld`_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8c
              mysqld`_Z13handle_selectP3THDP6st_lexP13select_resultm+0x186
              mysqld`_Z21execute_sqlcom_selectP3THDP10TABLE_LIST+0xa0
              mysqld`_Z21mysql_execute_commandP3THD+0x29a2
              mysqld`_Z11mysql_parseP3THDPKcjPS2_+0x212
              mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0xc80
              mysqld`_Z10do_commandP3THD+0xf1
              mysqld`handle_one_connection+0x71e
              libc.so.1`_thrp_setup+0x8d
              libc.so.1`_lwp_start

              libmtmalloc.so.1`malloc
              mysqld`my_malloc+0x32
              mysqld`init_dynamic_array2+0x82              mysqld`_Z20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array+0x613
              mysqld`_ZN4JOIN8optimizeEv+0x459              mysqld`_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8c
              mysqld`_Z13handle_selectP3THDP6st_lexP13select_resultm+0x186
              mysqld`_Z21execute_sqlcom_selectP3THDP10TABLE_LIST+0xa0
              mysqld`_Z21mysql_execute_commandP3THD+0x29a2
              mysqld`_Z11mysql_parseP3THDPKcjPS2_+0x212
              mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0xc80
              mysqld`_Z10do_commandP3THD+0xf1
              mysqld`handle_one_connection+0x71e
              libc.so.1`_thrp_setup+0x8d
              libc.so.1`_lwp_start

              libmtmalloc.so.1`malloc
              mysqld`my_malloc+0x32
              mysqld`init_alloc_root+0x6e
              mysqld`_Z14init_sql_allocP11st_mem_rootjj+0x15
              mysqld`_Z11open_tablesP3THDPP10TABLE_LISTPjj+0x43
              mysqld`_Z28open_and_lock_tables_derivedP3THDP10TABLE_LISTb+0x67
              mysqld`_Z21execute_sqlcom_selectP3THDP10TABLE_LIST+0x5d
              mysqld`_Z21mysql_execute_commandP3THD+0x29a2
              mysqld`_Z11mysql_parseP3THDPKcjPS2_+0x212
              mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0xc80
              mysqld`_Z10do_commandP3THD+0xf1
              mysqld`handle_one_connection+0x71e
              libc.so.1`_thrp_setup+0x8d
              libc.so.1`_lwp_start

              libmtmalloc.so.1`malloc
              mysqld`my_malloc+0x32
              mysqld`alloc_root+0xa2              mysqld`_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x21c
              mysqld`_Z13handle_selectP3THDP6st_lexP13select_resultm+0x186
              mysqld`_Z21execute_sqlcom_selectP3THDP10TABLE_LIST+0xa0
              mysqld`_Z21mysql_execute_commandP3THD+0x29a2
              mysqld`_Z11mysql_parseP3THDPKcjPS2_+0x212
              mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0xc80
              mysqld`_Z10do_commandP3THD+0xf1
              mysqld`handle_one_connection+0x71e
              libc.so.1`_thrp_setup+0x8d
              libc.so.1`_lwp_start
                

How to repeat:
Run the following before executing a simple query, then hit control-c to stop dtracing. (replace XXX with the mysqld pid)

dtrace -n pidXXX::malloc:entry'{@[arg0,ustack()]=count()}'