线程相关内存参数sort_buffer/join_buffer等的内存分配时机

今天一个朋友问我sort_buffer/join_buffer/binlog_cache_size/read_rnd_buff_size 等线程相关参数是什么时候分配内存的。我就特意做了gdb调试，得出结论这些参数都是在需要的时候才分配的，并不是建立线程就分配了，这里只是简单的给出我的结论并且给出栈帧以备后面学习和调试。水平有限有误请共同讨论。

一、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

仅此作为备忘记录

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,406评论 5赞 475
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,976评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,302评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,366评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,372评论 5赞 363
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,457评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,872评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,521评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,717评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,523评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,590评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,299评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,859评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,883评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,127评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,760评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,290评论 2赞 342

推荐阅读更多精彩内容