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
