執行緒相關記憶體引數sort_buffer/join_buffer等的記憶體分配時機

gaopengtttt發表於2017-12-26

原文網址 : http://blog.itpub.net/7728585/viewspace-2149331/

今天一個朋友問我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

僅此作為備忘記錄
作者微信：

執行緒相關記憶體引數sort_buffer/join_buffer等的記憶體分配時機

微信.jpg

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2149331/，如需轉載，請註明出處，否則將追究法律責任。

JVM完整詳解：記憶體分配+執行原理+回收演算法+GC引數等
2022-01-18
JVM記憶體演算法GC
【Java 虛擬機器筆記】記憶體分配策略相關整理
2019-02-27
Java虛擬機筆記記憶體
深入理解Java的堆記憶體和執行緒記憶體
2019-01-03
Java記憶體執行緒
Java 執行緒記憶體模型
2018-08-07
Java執行緒記憶體模型
linux記憶體管理（一）實體記憶體的組織和記憶體分配
2024-06-07
Linux記憶體
Java多執行緒記憶體模型
2019-03-22
Java執行緒記憶體模型
記憶體的分配與釋放，記憶體洩漏
2019-05-12
記憶體
Python如何管理記憶體？記憶體分配機制是什麼？
2024-01-11
Python記憶體
jvm：記憶體模型、記憶體分配及GC垃圾回收機制
2019-08-03
JVM記憶體模型GC
垃圾收集器與記憶體分配策略_記憶體分配策略
2018-03-31
記憶體
從記憶體洩露、記憶體溢位和堆外記憶體，JVM優化引數配置引數
2020-12-07
記憶體洩露記憶體溢位JVM優化
Java多執行緒之記憶體模型
2020-06-21
Java執行緒記憶體模型
記憶體優化相關
2019-07-23
記憶體優化
Java 執行時的記憶體劃分
2018-08-29
Java記憶體
動態記憶體分配
2024-08-16
記憶體
JVM記憶體引數配置
2022-02-20
JVM記憶體
Java程式執行記憶體機制
2020-09-26
Java記憶體
【記憶體管理】頁面分配機制
2024-06-23
記憶體
1、多執行緒同步——CPU、core核、執行緒、記憶體
2024-07-14
執行緒記憶體
MySQL記憶體執行緒獨享使用的方法
2021-08-29
MySql記憶體執行緒
golang的記憶體相關內容
2020-12-03
Golang記憶體
C語言的記憶體分配
2019-05-11
C語言記憶體
C中的記憶體分配模型
2024-08-22
記憶體模型
project中的堆疊記憶體，記憶體地址引用，gc相關問題
2018-10-31
Project記憶體GC
java-方法記憶體分配
2020-10-17
Java記憶體
go記憶體分配器
2020-10-18
Go記憶體
java基礎-記憶體分配
2020-04-05
Java記憶體
hadoop 記憶體分配規則
2019-05-11
Hadoop記憶體
C語言-記憶體分配
2019-05-13
C語言記憶體
記憶體分配策略學習
2018-04-15
記憶體
深度理解glibc記憶體分配
2022-02-09
記憶體
Java記憶體區域（執行時資料區域）和記憶體模型（JMM）
2019-07-03
Java記憶體模型
垃圾收集機制與記憶體分配策略
2019-02-26
記憶體
多執行緒之Java記憶體模型(JMM)(一)
2021-09-09
執行緒Java記憶體模型
【Java併發】1. Java執行緒記憶體模型JMM及volatile相關知識
2021-04-16
Java執行緒記憶體模型
Java記憶體區域與記憶體溢位異常 - 執行時資料區
2024-07-31
Java記憶體溢位
MySQL記憶體管理，記憶體分配器和作業系統
2020-10-14
MySql記憶體作業系統
Java虛擬機器08——Java記憶體模型與執行緒
2019-04-18
Java虛擬機記憶體模型執行緒
windows memeory 記憶體相關術語
2024-08-24
Windows記憶體

執行緒相關記憶體引數sort_buffer/join_buffer等的記憶體分配時機

一、sort_buffer

二、join_buffer

三、binlog_cache_size

四、read_rnd_buff_size

相關文章