本篇内容主要讲解“参数sort_buffer/join_buffer的内存分配时机是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“参数sort_buffer/join_buffer的内存分配时机是什么”吧!

触发分配时机为需要内存排序的时候才按需分配

断点位置Filesort_buffer::alloc_sort_buffer

参数

staticSys_var_ulongSys_sort_buffer("sort_buffer_size","Eachthreadthatneedstodoasortallocatesabufferofthissize",SESSION_VAR(sortbuff_size),CMD_LINE(REQUIRED_ARG),VALID_RANGE(MIN_SORT_MEMORY,ULONG_MAX),DEFAULT(DEFAULT_SORT_MEMORY),BLOCK_SIZE(1));

栈帧如下

#0Filesort_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#10x0000000000f59316inFilesort_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#20x0000000000f50fc7infilesort(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#30x0000000001562667increate_sort_index(thd=0x7fff2c000b70,join=0x7fff2c007490,tab=0x7fff2caad3d0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:3677#40x000000000155f7afinQEP_TAB::sort_table(this=0x7fff2caad3d0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2602#50x000000000155f197injoin_init_read_record(tab=0x7fff2caad3d0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2468#60x000000000155c359insub_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#70x000000000155bcdeindo_select(join=0x7fff2c007490)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944#80x0000000001559bb4inJOIN::exec(this=0x7fff2c007490)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199#90x00000000015f9e7einhandle_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#100x00000000015accddinexecute_sqlcom_select(thd=0x7fff2c000b70,all_tables=0x7fff2c0067f0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391#110x00000000015a52f8inmysql_execute_command(thd=0x7fff2c000b70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#120x00000000015adcaeinmysql_parse(thd=0x7fff2c000b70,parser_state=0x7ffff035b600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#130x00000000015a1b6dindispatch_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#140x00000000015a099eindo_command(thd=0x7fff2c000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#150x00000000016e28f0inhandle_connection(arg=0x68d6da0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#160x0000000001d7a514inpfs_spawn_thread(arg=0x38474d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#170x0000003f74807aa1instart_thread()from/lib64/libpthread.so二、join_buffer

触发分配时机为进行BNL join 的时候才进行分配

断点位置JOIN_CACHE::alloc_buffer

参数

staticSys_var_ulongSys_join_buffer_size("join_buffer_size","Thesizeofthebufferthatisusedforfulljoins",SESSION_VAR(join_buff_size),CMD_LINE(REQUIRED_ARG),VALID_RANGE(128,ULONG_MAX),DEFAULT(256*1024),BLOCK_SIZE(128));

栈帧如下

#0JOIN_CACHE::alloc_buffer(this=0x7fff2caaeda8)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_join_buffer.cc:456#10x00000000017d80ecinJOIN_CACHE_BNL::init(this=0x7fff2caaeda8)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_join_buffer.cc:684#20x00000000015fe9e8inQEP_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#30x00000000015feedeinmake_join_readinfo(join=0x7fff2caadc38,no_jbuf_after=4294967295)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:2173#40x000000000157f635inJOIN::optimize(this=0x7fff2caadc38)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:683#50x00000000015fb6f5inst_select_lex::optimize(this=0x7fff2c005a90,thd=0x7fff2c000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:1009#60x00000000015f9e08inhandle_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#70x00000000015acbb1inexecute_sqlcom_select(thd=0x7fff2c000b70,all_tables=0x7fff2c006c28)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5376#80x00000000015a52f8inmysql_execute_command(thd=0x7fff2c000b70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#90x00000000015adcaeinmysql_parse(thd=0x7fff2c000b70,parser_state=0x7ffff035b600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#100x00000000015a1b6dindispatch_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#110x00000000015a099eindo_command(thd=0x7fff2c000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#120x00000000016e28f0inhandle_connection(arg=0x68d6da0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#130x0000000001d7a514inpfs_spawn_thread(arg=0x38474d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#140x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#150x0000003f740e8bcdinclone()from/lib64/libc.so.6三、binlog_cache_size

触发分配为在进行事物处理的时候才进行分配

断点位置 init_io_cache_ext

参数

staticSys_var_ulongSys_binlog_cache_size("binlog_cache_size","Thesizeofthetransactionalcachefor""updatestotransactionalenginesforthebinarylog.""Ifyouoftenusetransactionscontainingmanystatements,""youcanincreasethistogetmoreperformance",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));

栈帧如下

#0init_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#10x00000000018c8a68ininit_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#20x00000000018c6ab6inopen_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#30x00000000018598d2inTHD::binlog_setup_trx_data(this=0x7fff24000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9641#40x0000000001859bd3inbinlog_start_trans_and_stmt(thd=0x7fff24000b70,start_event=0x7ffff02d7350)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9742#50x0000000001859fc6inTHD::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#60x0000000000f7299finwrite_locked_table_maps(thd=0x7fff24000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8019#70x0000000000f72bf6inbinlog_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,ucharconst*,ucharconst*)>)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8089#80x0000000000f73c39inhandler::ha_delete_row(this=0x7fff2404f8e0,buf=0x7fff2404fe20"\375\001")at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8308#90x00000000017c5451inSql_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#100x00000000017c83dainSql_cmd_delete::execute(this=0x7fff240069c0,thd=0x7fff24000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_delete.cc:1389#110x00000000015a77ecinmysql_execute_command(thd=0x7fff24000b70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3729#120x00000000015adcaeinmysql_parse(thd=0x7fff24000b70,parser_state=0x7ffff02d9600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#130x00000000015a1b6dindispatch_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#140x00000000015a099eindo_command(thd=0x7fff24000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#150x00000000016e28f0inhandle_connection(arg=0x68e2320)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#160x0000000001d7a514inpfs_spawn_thread(arg=0x38474d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#170x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#180x0000003f740e8bcdinclone()from/lib64/libc.so.6四、read_rnd_buff_size

触发为做MRR优化为执行语句需要使用缓存的时候才分配

断点QUICK_RANGE_SELECT::reset

参数

staticSys_var_ulongSys_read_rnd_buff_size("read_rnd_buffer_size","Whenreadingrowsinsortedorderafterasort,therowsareread""throughthisbuffertoavoidadiskseeks",SESSION_VAR(read_rnd_buff_size),CMD_LINE(REQUIRED_ARG),VALID_RANGE(1,INT_MAX32),DEFAULT(256*1024),BLOCK_SIZE(1));

栈帧如下

#0QUICK_RANGE_SELECT::reset(this=0x7fff24083c00)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:10958#10x000000000155f1e1injoin_init_read_record(tab=0x7fff24051798)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2471#20x000000000155c359insub_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#30x000000000155bcdeindo_select(join=0x7fff240511b0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944#40x0000000001559bb4inJOIN::exec(this=0x7fff240511b0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199#50x00000000015f9e7einhandle_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#60x00000000015accddinexecute_sqlcom_select(thd=0x7fff24000b70,all_tables=0x7fff240069e0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391#70x00000000015a52f8inmysql_execute_command(thd=0x7fff24000b70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#80x00000000015adcaeinmysql_parse(thd=0x7fff24000b70,parser_state=0x7ffff02d9600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#90x00000000015a1b6dindispatch_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#100x00000000015a099eindo_command(thd=0x7fff24000b70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#110x00000000016e28f0inhandle_connection(arg=0x68e2320)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#120x0000000001d7a514inpfs_spawn_thread(arg=0x38474d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#130x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#140x0000003f740e8bcdinclone()from/lib64/libc.so.6

到此，相信大家对“参数sort_buffer/join_buffer的内存分配时机是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！