From: Robert Milkowski Date: January 9 2010 3:12am Subject: myisam_use_mmap = 1 but still lots of [p]read()s List-Archive: http://lists.mysql.com/internals/37624 Message-Id: <4B47F40F.5010704@task.gda.pl> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, Solaris 10 64bit x86, MySQL 5.1.41 Despite having myisam_use_mmap = 1 in my.cnf there are still lots of pread(), read() syscalls to myisam data files. I can see that mysql detected and honored (at least to some extend) the myisam_use_mmap setting as if I do: pmap PID_of_mysql I can see myisam files being mmaped like: FFFFFD7F08600000 10104K rw-s- dev:181,65577 ino:5544 FFFFFD7F09000000 6532K rw-s- dev:181,65577 ino:5532 FFFFFD7F09800000 6652K rw-s- dev:181,65577 ino:5529 FFFFFD7F0A000000 10996K rw-s- dev:181,65577 ino:5502 FFFFFD7F0AC00000 7600K rw-s- dev:181,65577 ino:5499 FFFFFD7F0B400000 109604K rw-s- dev:181,65577 ino:5496 FFFFFD7F12000000 2856K rw-s- dev:181,65577 ino:5481 FFFFFD7F12400000 52780K rw-s- dev:181,65577 ino:5451 The ustack for most of reads is: dtrace -n syscall::*read*:entry'/pid==27777/{@[ustack()]=count();}' -n tick-5s'{trunc(@,3);printa(@);exit(0);}' dtrace: description 'syscall::*read*:entry' matched 5 probes dtrace: description 'tick-5s' matched 1 probe CPU ID FUNCTION:NAME 14 96850 :tick-5s libc.so.1`_read+0xa mysqld`my_read+0x54 mysqld`_mi_get_block_info+0x3c mysqld`_mi_read_dynamic_record+0xbd mysqld`mi_rprev+0x28d mysqld`__1cJha_myisamKindex_prev6MpC_i_+0x32 mysqld`__1cOjoin_read_prev6FpnLREAD_RECORD__i_+0x23 mysqld`__1cKsub_select6FpnEJOIN_pnNst_join_table_b_nWenum_nested_loop_state__+0xcc mysqld`__1cJdo_select6FpnEJOIN_pnEList4nEItem___pnIst_table_pnJProcedure__i_+0x207 mysqld`__1cEJOINEexec6M_v_+0x13e3 mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABLE_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselect_result_pnSst_select_lex_unit_pnNst_select_lex__b_+0x5eb mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNselect_result_L_b_+0x103 mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE_LIST__b_+0x15c mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x409 mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130 mysqld`__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0x988 mysqld`__1cKdo_command6FpnDTHD__b_+0xe2 mysqld`handle_one_connection+0xe1 libc.so.1`_thr_setup+0x5b libc.so.1`_lwp_start 19339 libc.so.1`_read+0xa mysqld`my_read+0x54 mysqld`_mi_read_rnd_dynamic_record+0x365 mysqld`mi_scan+0x26 mysqld`__1cJha_myisamIrnd_next6MpC_i_+0x2a mysqld`__1cNfind_all_keys6FpnNst_sort_param_pnKSQdDL_SELECT_ppCpnLst_io_cache_77_X_+0x2e4 mysqld`__1cIfilesort6FpnDTHD_pnIst_table_pnNst_sort_field_IpnKSQdDL_SELECT_XbpX_X_+0x449 mysqld`__1cRcreate_sort_index6FpnDTHD_pnEJOIN_pnIst_order_XXb_i_+0x282 mysqld`__1cEJOINEexec6M_v_+0x1299 mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABLE_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselect_result_pnSst_select_lex_unit_pnNst_select_lex__b_+0x5eb mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNselect_result_L_b_+0x103 mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE_LIST__b_+0x15c mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x409 mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130 mysqld`__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0x988 mysqld`__1cKdo_command6FpnDTHD__b_+0xe2 mysqld`handle_one_connection+0xe1 libc.so.1`_thr_setup+0x5b libc.so.1`_lwp_start 590519 libc.so.1`_read+0xa mysqld`my_read+0x54 mysqld`_mi_get_block_info+0x3c mysqld`_mi_read_rnd_dynamic_record+0x19d mysqld`mi_scan+0x26 mysqld`__1cJha_myisamIrnd_next6MpC_i_+0x2a mysqld`__1cNfind_all_keys6FpnNst_sort_param_pnKSQdDL_SELECT_ppCpnLst_io_cache_77_X_+0x2e4 mysqld`__1cIfilesort6FpnDTHD_pnIst_table_pnNst_sort_field_IpnKSQdDL_SELECT_XbpX_X_+0x449 mysqld`__1cRcreate_sort_index6FpnDTHD_pnEJOIN_pnIst_order_XXb_i_+0x282 mysqld`__1cEJOINEexec6M_v_+0x1299 mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABLE_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselect_result_pnSst_select_lex_unit_pnNst_select_lex__b_+0x5eb mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNselect_result_L_b_+0x103 mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE_LIST__b_+0x15c mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x409 mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130 mysqld`__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0x988 mysqld`__1cKdo_command6FpnDTHD__b_+0xe2 mysqld`handle_one_connection+0xe1 libc.so.1`_thr_setup+0x5b libc.so.1`_lwp_start 591268 By looking at _mi_get_block_info() i can see at the beginning of the function that it is calling my_read() which in turn is calling read() syscall. It's a similar story with _mi_read_rnd_dynamic_record() function. I haven't look closely at the code but it looks like changing the discussed two functions to do memcpy() from a mmaped region or even better set a pointer to mmap'ed region (assuming no modifications are done) shouldn't be that hard. During peak hours I can see several hundreds read()/s, sometimes even more, and getting rid of all these syscalls would probably improve performance. ps. please include me on Cc as I'm not subscribed to the list -- Robert Milkowski http://milek.blogspot.com