[MYSQL] binlog_cache_size 设置多大合适呢?
原创[MYSQL] binlog_cache_size 设置多大合适呢?
原创
介绍
binlog_cache_size 是在事务处理期间用于保存binlog的内存大小, 可以当作是事务的大小. 每个会话独享.
默认是32KB, 取值是4KB的整数倍.
官网介绍: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_cache_size
通常默认32KB是足够使用的, 但连接数多了之后, 占用的内存还是不少的, 但设置太小了的话, 又会使用到磁盘, 对性能又有影响. 这时就需要优化这部分内存了.
我们可以使用如下SQL查看 是否因为binlog太小导致使用了磁盘
show global status like '%Binlog_cache%_use%';
Binlog_cache_disk_use 表示使用了磁盘的binlog缓存次数.
Binlog_cache_use 表示使用了binlog缓存的次数. 如果binlog都是开启了的话, 可以当作是事务数量.
如果 Binlog_cache_disk_use 太大的话, 就需要上调binlog_cache_size 的值. 如果一直是0, 或者很小的话, 可以考虑下调 binlog_cache_size 的值.
设置为多少合适呢?
我们可以解析binlog得到事务的大小. 然后取能容纳99.9%的事务的大小即可. 对于个别大事务, 是不需要考虑的.
演示
本脚本使用python编写的, 无依赖包, 支持Python2和python3. 直接执行即可. 后面跟上要解析的binlog文件. 支持正则表达式
例子
python binlog_trx_4kf.py /data/mysql_3314/mysqllog/binlog/m3314.00010*
比如我这个环境, 事务基本上都是4KB一下. 那么我就可以设置 binlog_cache_size 为4KB. 以节省更多的内存.
取值要求是满足99.9%的业务, 但对于性能要求不高的任务, 比如一些定时更新之类的任务, 则可以不用考虑进去. 当然, 如果内存充足的话, 是建议尽量都包含进去(除了特大事务)
附脚本
github地址: https://github.com/ddcw/ddcw/blob/master/python/binlog_trx_4kf.py
源码:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#解析binlog得到事务大小, python2和python3 均适用
# binlog_cache_size 对4KB取整
# python binlog_trx_4kf.py mysql-bin.000002
import argparse,glob,struct,datetime,time
import sys,os
def _argparse():
parser = argparse.ArgumentParser(add_help=True, description='解析binlog/relay log 获取事务大小')
parser.add_argument('--version', '-v', '-V', action='store_true', dest="VERSION", default=False, help='Show version')
parser.add_argument("files", nargs="*", help="binlog/relay log list. support RE")
if parser.parse_args().VERSION:
print('VERSION: v0.2')
sys.exit(0)
return parser.parse_args()
def gettrxfrombinlog(filename,TRX,MAX_TRX):
offset = 0
with open(filename,'rb') as f:
if f.read(4) != b'\xfebin':
f.seek(0,0)
while True:
bdata = f.read(19)
if bdata == b'':
break
timestamp, event_type, server_id, event_size, log_pos, flags = struct.unpack("<LBLLLh",bdata[0:19])
f.seek(event_size-19,1) #数据就不读了
if event_type == 33: #BEGIN GTID_EVENT
offset = f.tell()
elif event_type == 16: #commit XID_EVENT
size = f.tell() - offset
size = int(size/4096)
if size > MAX_TRX:
TRX[MAX_TRX] += 1
else:
TRX[size] += 1
return TRX
if __name__ == '__main__':
parser = _argparse()
filelist = []
for pattern in parser.files:
filelist += glob.glob(pattern)
fileset = set(filelist)
if len(fileset) == 0:
print('At least one binlog file')
sys.exit(1)
MAX_TRX = 1*256 #256 * 4KB
TRX = [ 0 for x in range(MAX_TRX) ] #初始化事务大小列表. 对1KB取整
TRX.append(0) #添加超过范围的计数
for filename in fileset:
TRX = gettrxfrombinlog(filename,TRX,MAX_TRX)
for idx,val in enumerate(TRX):
if val>0:
print(str((idx+1)*4) + " KB以内的事务数量: " + str(val) if idx < MAX_TRX else "超过 " + str(idx*4) + " KB的事务数量: " + str(val)) #python2如果收到的是元组的话, 就不支持中文原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。