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_+0x2 3

mysqld`__1cKsub_select6FpnEJOIN_pnNst_join_table_b _nWenum_nested_loop_state__+0xcc

mysqld`__1cJdo_select6FpnEJOIN_pnEList4nEItem___pn Ist_table_pnJProcedure__i_+0x207
mysqld`__1cEJOINEexec6M_v_+0x13e3

mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABL E_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselec t_result_pnSst_select_lex_unit_pnNst_select_lex__b _+0x5eb

mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNsel ect_result_L_b_+0x103

mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE _LIST__b_+0x15c
mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x40 9
mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130

mysqld`__1cQdispatch_command6FnTenum_server_comman d_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_pnKSQd DL_SELECT_ppCpnLst_io_cache_77_X_+0x2e4

mysqld`__1cIfilesort6FpnDTHD_pnIst_table_pnNst_sor t_field_IpnKSQdDL_SELECT_XbpX_X_+0x449

mysqld`__1cRcreate_sort_index6FpnDTHD_pnEJOIN_pnIs t_order_XXb_i_+0x282
mysqld`__1cEJOINEexec6M_v_+0x1299

mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABL E_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselec t_result_pnSst_select_lex_unit_pnNst_select_lex__b _+0x5eb

mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNsel ect_result_L_b_+0x103

mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE _LIST__b_+0x15c
mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x40 9
mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130

mysqld`__1cQdispatch_command6FnTenum_server_comman d_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_pnKSQd DL_SELECT_ppCpnLst_io_cache_77_X_+0x2e4

mysqld`__1cIfilesort6FpnDTHD_pnIst_table_pnNst_sor t_field_IpnKSQdDL_SELECT_XbpX_X_+0x449

mysqld`__1cRcreate_sort_index6FpnDTHD_pnEJOIN_pnIs t_order_XXb_i_+0x282
mysqld`__1cEJOINEexec6M_v_+0x1299

mysqld`__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABL E_LIST_IrnEList4n0B___3IpnIst_order_9C39CXpnNselec t_result_pnSst_select_lex_unit_pnNst_select_lex__b _+0x5eb

mysqld`__1cNhandle_select6FpnDTHD_pnGst_lex_pnNsel ect_result_L_b_+0x103

mysqld`__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE _LIST__b_+0x15c
mysqld`__1cVmysql_execute_command6FpnDTHD__i_+0x40 9
mysqld`__1cLmysql_parse6FpnDTHD_pkcIp3_v_+0x130

mysqld`__1cQdispatch_command6FnTenum_server_comman d_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


--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Hi!

Robert> Hi,
Robert> Solaris 10 64bit x86, MySQL 5.1.41

Robert> Despite having myisam_use_mmap = 1 in my.cnf there are still lots of
Robert> pread(), read() syscalls to myisam data files. I can see that mysql
Robert> detected and honored (at least to some extend) the myisam_use_mmap
Robert> setting as if I do: pmap PID_of_mysql I can see myisam files being
Robert> mmaped like:

<cut>

Robert> By looking at _mi_get_block_info() i can see at the beginning of the
Robert> function that it is calling my_read() which in turn is calling read()
Robert> syscall. It's a similar story with _mi_read_rnd_dynamic_record() function.
Robert> I haven't look closely at the code but it looks like changing the
Robert> discussed two functions to do memcpy() from a mmaped region or even
Robert> better set a pointer to mmap'ed region (assuming no modifications are
Robert> done) shouldn't be that hard.

Robert> During peak hours I can see several hundreds read()/s, sometimes even
Robert> more, and getting rid of all these syscalls would probably improve
Robert> performance.

You are right; Currently the myisam_use_mmap is not fully
implemented.

Looking quickly at the mi_dynrec.c code, it looks like the following
functions needs to be fixed to get read of the reads:

_mi_read_cache()
_mi_read_rnd_dynamic_record()
_mi_get_block_info()

Should not be that much work.

_mi_cmp_buffer() doesn't have to be changed as this is not used
by MySQL.
_mi_write_part_record() doens't have to be changed as the write calls
are only used when writing to a buffer that will be written to end of
file.

Regards,
Monty
Creator of MySQL

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

On 11/01/2010 00:13, Michael Widenius wrote:
If I would like to contribute the changes (assuming I would find enough
free time) how should I go about it?


Also the comment for send_results_to_client() function is wrong as the
return code of 1 actually means that a query was cached while 0 means
that it wasn't. The description of return codes 0 and 1 should be swapped.

This is how it looks at 5.1.x codebase:

/*
Check if the query is in the cache. If it was cached, send it
to the user.

RESULTS
1 Query was not cached.
0 The query was cached and user was sent the result.
-1 The query was cached but we didn't have rights to use it.
No error is sent to the client yet.

NOTE
This method requires that sql points to allocated memory of size:
tot_length= query_length + thd->db_length + 1 + QUERY_CACHE_FLAGS_SIZE;
*/

int
Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
{


--
Robert Milkowski
http://milek.blogspot.com


--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Hi!

<cut>

Robert> During peak hours I can see several hundreds read()/s, sometimes even
Robert> more, and getting rid of all these syscalls would probably improve
Robert> performance.
Robert> If I would like to contribute the changes (assuming I would find enough
Robert> free time) how should I go about it?

To contribute to Sun and MySQL, you need to sign Sun's contributor agreement
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

If you in addition want to also contribute to MariaDB, you need to
either sign our contributor agreement:
http://askmonty.org/wiki/index.php/MCA
or submit the patch to maria-developers (AT) lists (DOT) launchpad.net under BSD.

I am happy to help you with any questions regarding doing the patch.
We can discuss this ether on this list, the maria-developers list
or in the #maria channel on Freenode.

Robert> Also the comment for send_results_to_client() function is wrong as the
Robert> return code of 1 actually means that a query was cached while 0 means
Robert> that it wasn't. The description of return codes 0 and 1 should be swapped.

<cut>

Thanks; Now fixed in MariaDB 5.1

Regards,
Monty
Creator of MySQL

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw