测试环境是一套实时主备达梦数据库,并准备测试脚本如下:
#清除主库归档
disql -s SYSDBA/SYSDBA <<EOF
SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE);
EOF
#清除备库归档
disql -s SYSDBA/SYSDBA@192.168.100.101 <<EOF
SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE);
EOF
#定义函数检查主库的时间、实例名、日志压缩级别、当前LSN、REDO产生量、归档产生量
primary_db_check()
{
disql -s SYSDBA/SYSDBA <<EOF
set linesize 150
set lineshow off
column instance_name format a10
column para_value format a1
select sysdate,instance_name,para_value COMPRESS_LEVEL,cur_lsn,
BYTES_FLUSHED/1024/1024 REDO_SIZE,ARCH_SIZE
from v\$rlog_pkg_stat,v\$instance,v\$rlog,v\$dm_ini,(select sum(free)/1024/1024 ARCH_SIZE from v\$arch_file )
where para_name='RLOG_COMPRESS_LEVEL';
exit;
EOF
}
#定义函数检查主库的时间、实例名、日志压缩级别、当前LSN、REDO产生量、归档产生量
standby_db_check()
{
disql -s SYSDBA/SYSDBA@192.168.100.101 <<eof
set linesize 150
set lineshow off
column instance_name format a10
column para_value format a1
select sysdate,instance_name,para_value COMPRESS_LEVEL,cur_lsn,
BYTES_FLUSHED/1024/1024 REDO_SIZE,ARCH_SIZE,${1}-cur_lsn LSN_DIFF
from v\$rlog_pkg_stat,v\$instance,v\$rlog,
v\$dm_ini,(select sum(free)/1024/1024 ARCH_SIZE from v\$arch_file )
where para_name='RLOG_COMPRESS_LEVEL';
exit;
eof
}
#定义函数查询主库当前LSN号
cur_lsn()
{
disql -s SYSDBA/SYSDBA <<EOF
set linesize 150
set lineshow off
set heading off
select cur_lsn from v\$rlog;
exit;
EOF
}
#检查主库
primary_db_check
#检查主库当前LSN号
CUR_LSN=`cur_lsn | egrep -v '^$|used time'`
#检查备库,把主库当前LSN号作为参数传入计算主备LSN的偏离值
standby_db_check $CUR_LSN
#更新t1表,该表已提前插入了1000000行数据。
disql -s SYSDBA/SYSDBA <<EOF
update t1 set a=1;
commit;
exit;
EOF
#检查主库
primary_db_check
#检查主库当前LSN号
CUR_LSN=`cur_lsn | egrep -v '^$|used time'`
#循环检查备库。备库有可能需要一些时间追齐。
while :
do
standby_db_check $CUR_LSN
sleep 1
done1}-cur_lsn LSN_DIFF
from v\$rlog_pkg_stat,v\$instance,v\$rlog,
v\$dm_ini,(select sum(free)/1024/1024 ARCH_SIZE from v\$arch_file )
where para_name='RLOG_COMPRESS_LEVEL';
exit;
eof
}
每次测试前分别重启主备库
测试一、
主库设置RLOG_COMPRESS_LEVEL=9,备库设置为0。
结果如下:
[dmdba@node1 ~]$ sh test.sh
DMSQL executed successfully
used time: 0.528(ms). Execute id is 1100.
DMSQL executed successfully
used time: 2.877(ms). Execute id is 400.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:38:57 TEST1 9 426480057 0 0
used time: 12.010(ms). Execute id is 1200.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:38:58 TEST2 0 426480051 0 0 9
used time: 14.825(ms). Execute id is 500.
affect rows 10000000
used time: 00:00:48.016. Execute id is 1400.
executed successfully
used time: 00:02:04.507. Execute id is 1401.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:41:51 TEST1 9 436633172 211 212
used time: 00:00:01.074. Execute id is 1500.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:41:51 TEST2 0 436633172 1037 212 3
used time: 17.034(ms). Execute id is 600.
REDO产生量:
主库211M,备库1037M。
归档日志产生量:
主库212M,备库212M。
耗时:3分钟。
测试二
主备库均设置为0。
[dmdba@node1 ~]$ sh test.sh
DMSQL executed successfully
used time: 20.072(ms). Execute id is 500.
DMSQL executed successfully
used time: 18.787(ms). Execute id is 0.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:50:15 TEST1 0 436695977 0 0
used time: 22.238(ms). Execute id is 600.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:50:15 TEST2 0 436695971 0 0 9
used time: 20.289(ms). Execute id is 100.
affect rows 10000000
used time: 00:00:56.190. Execute id is 800.
executed successfully
used time: 00:00:02.074. Execute id is 801.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:51:14 TEST1 0 446767375 1033 1033
used time: 486.070(ms). Execute id is 900.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:51:14 TEST2 0 446767376 1035 1033 3
used time: 51.121(ms). Execute id is 200.
REDO产生量:
主库1033M,备库1035M。
归档日志产生量:
主库1033M,备库1033M。
耗时:1分钟。
测试三
主库设置RLOG_COMPRESS_LEVEL=0,备库设置为9。
[dmdba@node1 ~]$ sh test.sh
DMSQL executed successfully
used time: 71.204(ms). Execute id is 500.
DMSQL executed successfully
used time: 71.038(ms). Execute id is 0.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:54:24 TEST1 0 446864028 0 0
used time: 20.478(ms). Execute id is 600.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:54:24 TEST2 9 446864021 0 0 10
used time: 15.102(ms). Execute id is 100.
affect rows 10000000
used time: 00:01:16.967. Execute id is 800.
executed successfully
used time: 99.064(ms). Execute id is 801.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 12:55:41 TEST1 0 457078362 1033 1033
used time: 133.918(ms). Execute id is 900.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 12:56:57 TEST2 9 457078355 213 1033 10
REDO产生量:
主库1033M,备库213M。
归档日志产生量:
主库1033M,备库1033M。
耗时:3分钟。
测试四
主备库均设置为9。
[dmdba@node1 ~]$ sh test.sh
DMSQL executed successfully
used time: 0.810(ms). Execute id is 800.
DMSQL executed successfully
used time: 3.396(ms). Execute id is 200.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 13:09:06 TEST1 9 457080017 0 0
used time: 10.892(ms). Execute id is 900.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 13:09:06 TEST2 9 457080017 0 0 3
used time: 10.638(ms). Execute id is 300.
affect rows 10000000
used time: 00:00:48.675. Execute id is 1100.
executed successfully
used time: 00:01:50.208. Execute id is 1101.
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE
------------------- ------------- -------------- -------------------- -------------------- --------------------
2024-05-26 13:11:47 TEST1 9 467233126 225 212
SYSDATE INSTANCE_NAME COMPRESS_LEVEL CUR_LSN REDO_SIZE ARCH_SIZE LSN_DIFF
------------------- ------------- -------------- -------------------- -------------------- -------------------- --------------------
2024-05-26 13:13:53 TEST2 9 467274131 213 213 5
REDO产生量:
主库225M,备库213M。
归档日志产生量:
主库212M,备库213M。
耗时:3分钟。
结论
REDO的产生量取决于RLOG_COMPRESS_LEVEL参数的设置。主备库的归档日志产生量均与主库REDO保持一致。