本篇内容主要讲解“参数sort_buffer/join_buffer的内存分配时机是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“参数sort_buffer/join_buffer的内存分配时机是什么”吧!
一、sort_buffer
-
触发分配时机为需要内存排序的时候才按需分配
-
断点位置Filesort_buffer::alloc_sort_buffer
-
参数
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));
-
栈帧如下
#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/sql/conn_handler/connection_handler_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
二、join_buffer
-
触发分配时机为进行BNL join 的时候才进行分配
-
断点位置JOIN_CACHE::alloc_buffer
-
参数
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));
-
栈帧如下
#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/sql/conn_handler/connection_handler_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
三、binlog_cache_size
-
触发分配为在进行事物处理的时候才进行分配
-
断点位置 init_io_cache_ext
-
参数
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));
-
栈帧如下
#0 init_io_cache_ext (info=0x7fff2402c998, file=-1, cachesize=32768, type=WRITE_CACHE, seek_offset=0, use_async_io=0 '/000', 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 <Delete_rows_log_event::binlog_row_logging_function(THD*, TABLE*, bool, uchar const*, uchar const*)>) 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/sql/conn_handler/connection_handler_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
四、read_rnd_buff_size
-
触发为做MRR优化为执行语句需要使用缓存的时候才分配
-
断点QUICK_RANGE_SELECT::reset
-
参数
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*1024), BLOCK_SIZE(1));
-
栈帧如下
#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/sql/conn_handler/connection_handler_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
到此，相信大家对“参数sort_buffer/join_buffer的内存分配时机是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/203826.html