Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the timing of memory allocation for parameter sort_buffer/join_buffer". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the timing of memory allocation for the parameter sort_buffer/join_buffer?"

1. Sort_buffer

Trigger allocation time to allocate on demand only when memory sorting is needed.

Breakpoint location Filesort_buffer::alloc_sort_buffer

Parameters.

Static Sys_var_ulong Sys_sort_buffer ("sort_buffer_size", "Each thread that needs to do a sort allocates a buffer of this size", SESSION_VAR (sortbuff_size), CMD_LINE (REQUIRED_ARG), VALID_RANGE (MIN_SORT_MEMORY, ULONG_MAX), DEFAULT (DEFAULT_SORT_MEMORY), BLOCK_SIZE (1))

The stack frames are as follows

# 0 Filesort_buffer::alloc_sort_buffer (this=0x7ffff0359550, num_records=1310, record_length=70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort_utils.cc:103#1 0x0000000000f59316 in Filesort_info::alloc_sort_buffer (this=0x7ffff0359550, num_records=1310, record_length=70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_sort.h:509#2 0x0000000000f50fc7 in filesort (thd=0x7fff2c000b70, filesort=0x7fff2caad6c0, sort_positions=false, examined_rows=0x7ffff03598a0 Found_rows=0x7ffff0359898, returned_rows=0x7ffff0359890) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/filesort.cc:394#3 0x0000000001562667 in create_sort_index (thd=0x7fff2c000b70, join=0x7fff2c007490 Tab=0x7fff2caad3d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:3677#4 0x000000000155f7af in QEP_TAB::sort_table (this=0x7fff2caad3d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2602#5 0x000000000155f197 in join_init_read_record (tab=0x7fff2caad3d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql _ executor.cc:2468#6 0x000000000155c359 in sub_select (join=0x7fff2c007490 Qep_tab=0x7fff2caad3d0 End_of_records=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271#7 0x000000000155bcde in do_select (join=0x7fff2c007490) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944#8 0x0000000001559bb4 in JOIN::exec (this=0x7fff2c007490) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199#9 0x00000000015f9e7e in handle_query (thd=0x7fff2c000b70 Lex=0x7fff2c003150, result=0x7fff2c006f58, added_options=0, removed_options=0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:184#10 0x00000000015accdd in execute_sqlcom_select (thd=0x7fff2c000b70, all_tables=0x7fff2c0067f0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391#11 0x00000000015a52f8 in mysql_execute_command (thd=0x7fff2c000b70 First_level=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#12 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#13 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70 Command=COM_QUERY) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#14 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#15 0x00000000016e28f0 in handle_connection (arg=0x68d6da0) at / root/mysql5.7.14/percona-server-5.7.14-7 up sqlbank Per_thread.cc:312#16 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38474d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#17 0x0000003f74807aa1 in start_thread () from / lib64/libpthread.so II, Join_buffer

When the timing of trigger allocation is BNL join, the allocation is carried out.

Breakpoint location JOIN_CACHE::alloc_buffer

Parameters.

Static Sys_var_ulong Sys_join_buffer_size ("join_buffer_size", "The size of the buffer that is used for full joins", SESSION_VAR (join_buff_size), CMD_LINE (REQUIRED_ARG), VALID_RANGE (128, ULONG_MAX), DEFAULT (256 * 1024), BLOCK_SIZE (128))

The stack frames are as follows

# 0 JOIN_CACHE::alloc_buffer (this=0x7fff2caaeda8) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_join_buffer.cc:456#1 0x00000000017d80ec in JOIN_CACHE_BNL::init (this=0x7fff2caaeda8) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_join_buffer.cc:684#2 0x00000000015fe9e8 in QEP_TAB::init_join_cache (this=0x7fff2caaec30 Join_tab=0x7fff2caae268) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:2060#3 0x00000000015feede in make_join_readinfo (join=0x7fff2caadc38 No_jbuf_after=4294967295) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:2173#4 0x000000000157f635 in JOIN::optimize (this=0x7fff2caadc38) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:683#5 0x00000000015fb6f5 in st_select_lex::optimize (this=0x7fff2c005a90 Thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:1009#6 0x00000000015f9e08 in handle_query (thd=0x7fff2c000b70, lex=0x7fff2c003150, result=0x7fff2c0079b0, added_options=0, removed_options=0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:164#7 0x00000000015acbb1 in execute_sqlcom_select (thd=0x7fff2c000b70 All_tables=0x7fff2c006c28) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5376#8 0x00000000015a52f8 in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#9 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70 Parser_state=0x7ffff035b600) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#10 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70) Command=COM_QUERY) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#11 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#12 0x00000000016e28f0 in handle_connection (arg=0x68d6da0) at / root/mysql5.7.14/percona-server-5.7.14-7 up sqlbank Per_thread.cc:312#13 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38474d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#14 0x0000003f74807aa1 in start_thread () from / lib64/libpthread.so.0#15 0x0000003f740e8bcd in clone () from / lib64/libc.so.6 3. Binlog_cache_size

Trigger allocation is allocated only when transaction processing is carried out.

Breakpoint location init_io_cache_ext

Parameters.

Static Sys_var_ulong Sys_binlog_cache_size ("binlog_cache_size", "The size of the transactional cache for"updates to transactional engines for the binary log." "If you often use transactions containing many statements," you can increase this to get more performance ", GLOBAL_VAR (binlog_cache_size), CMD_LINE (REQUIRED_ARG), VALID_RANGE (IO_SIZE, ULONG_MAX), DEFAULT (32768), BLOCK_SIZE (IO_SIZE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK (0), ON_UPDATE (fix_binlog_cache_size))

The stack frames are as follows

# 0 init_io_cache_ext (info=0x7fff2402c998, file=-1, cachesize=32768, type=WRITE_CACHE, seek_offset=0, use_async_io=0'\ 000mm, cache_myflags=20, file_key=10) at / root/mysql5.7.14/percona-server-5.7.14-7/mysys/mf_iocache.c:154#1 0x00000000018c8a68 in init_io_cache (info=0x7fff2402c998, file=-1, cachesize=32768, type=WRITE_CACHE, seek_offset=0, use_async_io=0'\ 000' Cache_myflags=20) at / root/mysql5.7.14/percona-server-5.7.14-7/mysys/mf_iocache.c:299#2 0x00000000018c6ab6 in open_cached_file (cache=0x7fff2402c998, dir=0x2f9ed70 "/ root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1", prefix=0x2275fce "ML", cache_size=32768 Cache_myflags=16) at / root/mysql5.7.14/percona-server-5.7.14-7/mysys/mf_cache.c:60#3 0x00000000018598d2 in THD::binlog_setup_trx_data (this=0x7fff24000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9641#4 0x0000000001859bd3 in binlog_start_trans_and_stmt (thd=0x7fff24000b70 Start_event=0x7ffff02d7350) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9742#5 0x0000000001859fc6 in THD::binlog_write_table_map (this=0x7fff24000b70, table=0x7fff2404ef00, is_transactional=true Binlog_rows_query=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9835#6 0x0000000000f7299f in write_locked_table_maps (thd=0x7fff24000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8019#7 0x0000000000f72bf6 in binlog_log_row (table=0x7fff2404ef00, before_record=0x7fff2404fe20 "\ 375\ 001", after_record=0x0 Log_func=0xf77f7d) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8089#8 0x0000000000f73c39 in handler::ha_delete_row (this=0x7fff2404f8e0, buf=0x7fff2404fe20 "\ 375\ 001") at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8308#9 0x00000000017c5451 in Sql_cmd_delete::mysql_delete (this=0x7fff240069c0, thd=0x7fff24000b70 Limit=18446744073709551615) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_delete.cc:471#10 0x00000000017c83da in Sql_cmd_delete::execute (this=0x7fff240069c0, thd=0x7fff24000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_delete.cc:1389#11 0x00000000015a77ec in mysql_execute_command (thd=0x7fff24000b70 First_level=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3729#12 0x00000000015adcae in mysql_parse (thd=0x7fff24000b70, parser_state=0x7ffff02d9600) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#13 0x00000000015a1b6d in dispatch_command (thd=0x7fff24000b70, com_data=0x7ffff02d9d70 Command=COM_QUERY) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#14 0x00000000015a099e in do_command (thd=0x7fff24000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#15 0x00000000016e28f0 in handle_connection (arg=0x68e2320) at / root/mysql5.7.14/percona-server-5.7.14-7 up sqlbank Per_thread.cc:312#16 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38474d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#17 0x0000003f74807aa1 in start_thread () from / lib64/libpthread.so.0#18 0x0000003f740e8bcd in clone () from / lib64/libc.so.6 IV. Read_rnd_buff_size

Trigger allocation only when caching is required for MRR optimization to execute statements

Breakpoint QUICK_RANGE_SELECT::reset

Parameters.

Static Sys_var_ulong Sys_read_rnd_buff_size ("read_rnd_buffer_size", "When reading rows in sorted order after a sort, the rows are read"through this buffer to avoid a disk seeks", SESSION_VAR (read_rnd_buff_size), CMD_LINE (REQUIRED_ARG), VALID_RANGE (1, INT_MAX32), DEFAULT (256 to 1024), BLOCK_SIZE (1))

The stack frames are as follows

# 0 QUICK_RANGE_SELECT::reset (this=0x7fff24083c00) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:10958#1 0x000000000155f1e1 in join_init_read_record (tab=0x7fff24051798) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2471#2 0x000000000155c359 in sub_select (join=0x7fff240511b0, qep_tab=0x7fff24051798 End_of_records=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271#3 0x000000000155bcde in do_select (join=0x7fff240511b0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944#4 0x0000000001559bb4 in JOIN::exec (this=0x7fff240511b0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199#5 0x00000000015f9e7e in handle_query (thd=0x7fff24000b70 Lex=0x7fff24003150, result=0x7fff240072f0, added_options=0, removed_options=0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:184#6 0x00000000015accdd in execute_sqlcom_select (thd=0x7fff24000b70, all_tables=0x7fff240069e0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391#7 0x00000000015a52f8 in mysql_execute_command (thd=0x7fff24000b70 First_level=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#8 0x00000000015adcae in mysql_parse (thd=0x7fff24000b70, parser_state=0x7ffff02d9600) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#9 0x00000000015a1b6d in dispatch_command (thd=0x7fff24000b70, com_data=0x7ffff02d9d70 Command=COM_QUERY) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#10 0x00000000015a099e in do_command (thd=0x7fff24000b70) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#11 0x00000000016e28f0 in handle_connection (arg=0x68e2320) at / root/mysql5.7.14/percona-server-5.7.14-7 up sqlbank Per_thread.cc:312#12 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38474d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#13 0x0000003f74807aa1 in start_thread () from / lib64/libpthread.so.0#14 0x0000003f740e8bcd in clone () from / lib64/libc.so.6 so far I believe you have a deeper understanding of "what is the timing of memory allocation for the parameter sort_buffer/join_buffer", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.