List:Internals« Previous MessageNext Message »
From:Robert Milkowski Date:January 9 2010 3:12am
Subject:myisam_use_mmap = 1 but still lots of [p]read()s
View as plain text  
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

Thread
myisam_use_mmap = 1 but still lots of [p]read()sRobert Milkowski9 Jan
  • re: myisam_use_mmap = 1 but still lots of [p]read()sMichael Widenius11 Jan
    • Re: myisam_use_mmap = 1 but still lots of [p]read()sRobert Milkowski12 Jan
      • Re: myisam_use_mmap = 1 but still lots of [p]read()sMichael Widenius12 Jan