mysql 一个事务中形成binlog大于当前线程缓存大小的执行流程

本文详细解析了MySQL中二进制日志的写入过程,从IO_CACHE的字节写入到最终刷新至磁盘的具体实现。通过跟踪源代码,展示了在执行插入操作时如何触发二进制日志的记录,并介绍了与之相关的函数调用流程。
(gdb) where
#0  _my_b_write (info=0x7ffe6c24d910, Buffer=0x7ffe6c0b1a00 "", Count=51358) at /root/mysql-5.7.27_edit_malloc_log/mysys/mf_iocache.c:1313
#1  0x000000000188764c in my_b_safe_write (info=0x7ffe6c24d910, Buffer=0x7ffe6c0b1a00 "", Count=51358) at /root/mysql-5.7.27_edit_malloc_log/mysys/mf_iocache.c:1437
#2  0x00000000017c9f01 in Log_event::wrapper_my_b_safe_write (this=0x7ffe6c26d7a0, file=0x7ffe6c24d910, buf=0x7ffe6c0b1a00 "", size=51358) at /root/mysql-5.7.27_edit_malloc_log/sql/log_event.cc:
1009
#3  0x00000000017e6c45 in Rows_log_event::write_data_body (this=0x7ffe6c26d7a0, file=0x7ffe6c24d910) at /root/mysql-5.7.27_edit_malloc_log/sql/log_event.cc:11647
#4  0x00000000017f15c3 in Log_event::write (this=0x7ffe6c26d7a0, file=0x7ffe6c24d910) at /root/mysql-5.7.27_edit_malloc_log/sql/log_event.h:830
#5  0x00000000018010b8 in binlog_cache_data::write_event (this=0x7ffe6c24d908, thd=0x7ffe6c014fd0, ev=0x7ffe6c26d7a0) at /root/mysql-5.7.27_edit_malloc_log/sql/binlog.cc:1340
#6  0x000000000180efad in MYSQL_BIN_LOG::flush_and_set_pending_rows_event (this=0x2d9af00 <mysql_bin_log>, thd=0x7ffe6c014fd0, event=0x0, is_transactional=true) at /root/mysql-5.7.27_edit_malloc
_log/sql/binlog.cc:7398
#7  0x000000000181914e in THD::binlog_flush_pending_rows_event (this=0x7ffe6c014fd0, stmt_end=true, is_transactional=true) at /root/mysql-5.7.27_edit_malloc_log/sql/binlog.cc:11823
#8  0x00000000018198a1 in THD::binlog_query (this=0x7ffe6c014fd0, qtype=THD::ROW_QUERY_TYPE, query_arg=0x7ffe6c077970 "insert into mytest04(msisdn,fav_brand,fav_big_type,fav_type,flag_code,fav_o
rder,fav_period,free_value,start_time,end_time,group_id,free_value2,product_code,end_time2,marktmp,marktmp2,marktmp3)values(?"..., query_len=302, is_trans=true, direct=false, suppress_use=false,
 errcode=0) at /root/mysql-5.7.27_edit_malloc_log/sql/binlog.cc:12097
#9  0x000000000178a2ef in Sql_cmd_insert::mysql_insert (this=0x7ffe6c094410, thd=0x7ffe6c014fd0, table_list=0x7ffe6c071da8) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_insert.cc:859
#10 0x0000000001790862 in Sql_cmd_insert::execute (this=0x7ffe6c094410, thd=0x7ffe6c014fd0) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_insert.cc:3127
#11 0x000000000156d9db in mysql_execute_command (thd=0x7ffe6c014fd0, first_level=true) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_parse.cc:3596
#12 0x00000000015a9fd4 in Prepared_statement::execute (this=0x7ffe6c00b480, expanded_query=0x7fffec4b7500, open_cursor=false) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_prepare.cc:3977
#13 0x00000000015a8dfb in Prepared_statement::execute_loop (this=0x7ffe6c00b480, expanded_query=0x7fffec4b7500, open_cursor=false, packet=0x7ffe6c27070a "", packet_end=0x7ffe6c27cf6f "") at /roo
t/mysql-5.7.27_edit_malloc_log/sql/sql_prepare.cc:3585
#14 0x00000000015a6784 in mysqld_stmt_execute (thd=0x7ffe6c014fd0, stmt_id=1, flags=0, params=0x7ffe6c27070a "", params_length=51301) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_prepare.cc:257
3
#15 0x00000000015686b6 in dispatch_command (thd=0x7ffe6c014fd0, com_data=0x7fffec4b7e10, command=COM_STMT_EXECUTE) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_parse.cc:1418
#16 0x00000000015678a4 in do_command (thd=0x7ffe6c014fd0) at /root/mysql-5.7.27_edit_malloc_log/sql/sql_parse.cc:1025
#17 0x00000000016976a5 in handle_connection (arg=0xfce9a80) at /root/mysql-5.7.27_edit_malloc_log/sql/conn_handler/connection_handler_per_thread.cc:306
#18 0x0000000001d37250 in pfs_spawn_thread (arg=0xfcc9db0) at /root/mysql-5.7.27_edit_malloc_log/storage/perfschema/pfs.cc:2190
#19 0x00007ffff7bc6e65 in start_thread () from /lib64/libpthread.so.0
#20 0x00007ffff660788d in clone () from /lib64/libc.so.6
(gdb)


/* 
   Write a byte buffer to IO_CACHE and flush to disk
   if IO_CACHE is full.

   RETURN VALUE
    1 On error on write
    0 On success
   -1 On error; my_errno contains error code.
*/

int _my_b_write(IO_CACHE *info, const uchar *Buffer, size_t Count)
{
  size_t rest_length,length;
  my_off_t pos_in_file= info->pos_in_file;

  DBUG_EXECUTE_IF("simulate_huge_load_data_file",
                  {
                    pos_in_file=(my_off_t)(5000000000ULL);
                  });
  if (pos_in_file+info->buffer_length > info->end_of_file)
  {
    errno=EFBIG;
    set_my_errno(EFBIG);
    return info->error = -1;
  }

  rest_length= (size_t) (info->write_end - info->write_pos);
  memcpy(info->write_pos,Buffer,(size_t) rest_length);
  Buffer+=rest_length;
  Count-=rest_length;
  info->write_pos+=rest_length;

  if (my_b_flush_io_cache(info,1))
    return 1;
  if (Count >= IO_SIZE)
  {					/* Fill first intern buffer */
    length=Count & (size_t) ~(IO_SIZE-1);
    if (info->seek_not_done)
    {
      /*
        Whenever a function which operates on IO_CACHE flushes/writes
        some part of the IO_CACHE to disk it will set the property
        "seek_not_done" to indicate this to other functions operating
        on the IO_CACHE.
      */
      if (mysql_file_seek(info->file, info->pos_in_file, MY_SEEK_SET, MYF(0)))
      {
        info->error= -1;
        return (1);
      }
      info->seek_not_done=0;
    }
    if (mysql_file_write(info->file, Buffer, length, info->myflags | MY_NABP))
      return info->error= -1;

    /*
      In case of a shared I/O cache with a writer we normally do direct
      write cache to read cache copy. Simulate this here by direct
      caller buffer to read cache copy. Do it after the write so that
      the cache readers actions on the flushed part can go in parallel
      with the write of the extra stuff. copy_to_read_buffer()
      synchronizes writer and readers so that after this call the
      readers can act on the extra stuff while the writer can go ahead
      and prepare the next output. copy_to_read_buffer() relies on
      info->pos_in_file.
    */
    if (info->share)
      copy_to_read_buffer(info, Buffer, length);

    Count-=length;
    Buffer+=length;
    info->pos_in_file+=length;
  }
  memcpy(info->write_pos,Buffer,(size_t) Count);
  info->write_pos+=Count;
  return 0;
}

### MySQL连接最少内存占用分析 在MySQL中,每个客户端连接都会消耗一定的内存资源。这部分内存主要用于存储连接相关的上下文信息、线程栈以及可能的临时缓冲区等。具体的最小内存占用取决于多个因素,包括MySQL版本、配置参数以及操作系统的限制。 #### 1. 线程大小 MySQL为每个连接创建一个线程,而每个线程都有一个固定的栈大小。栈大小可以通过`thread_stack`参数进行配置,默认值通常为256KB或更高。这个值是每个连接的基本开销之一[^1]。 #### 2. 连接上下文 每个连接都需要维护一些元数据,例如用户信息、权限信息、当前数据库、字符集设置等。这些信息会占用一定的内存,具体大小因配置和环境而异,但通常不会超过几十KB[^1]。 #### 3. 缓冲区分配 MySQL为每个连接分配了一些缓冲区,例如: - **Net Buffer**: 用于存储查询和结果集的数据,默认初始大小为8KB,最大可以扩展到`max_allowed_packet`的值。 - **Sort Buffer**: 如果查询涉及排序操作,MySQL会为每个连接分配一个排序缓冲区,其大小由`sort_buffer_size`决定。 - **Join Buffer**: 对于没有使用索引的联接操作,MySQL会分配一个联接缓冲区,其大小由`join_buffer_size`决定。 这些缓冲区只有在需要时才会分配,因此在空闲连接中,它们通常不会增加额外的内存开销[^1]。 #### 4. 其他开销 除了上述基本开销外,还可能存在其他内存消耗,例如: - **临时表**: 如果查询需要创建内存中的临时表,MySQL会为其分配一定空间。 - **二进制日志缓存**: 对于启用了二进制日志的服务器,每个会话可能会分配一个二进制日志缓存大小由`binlog_cache_size`决定。 #### 总结 在一个最简单的场景下(即没有执行复杂查询、没有临时表、没有排序或联接操作),一个MySQL连接的最小内存占用主要由以下部分组成: - 线程栈:`thread_stack`(默认256KB) - 连接上下文:几十KB - Net Buffer:8KB(初始大小) 综合来看,一个空闲连接的最小内存占用大约在**300KB到500KB**之间,具体值取决于配置参数和系统实现。 ```sql SHOW VARIABLES LIKE 'thread_stack'; SHOW VARIABLES LIKE 'net_buffer_length'; SHOW VARIABLES LIKE 'max_allowed_packet'; ``` 以上命令可以帮助确认相关配置参数的值。 ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值