1、资源准备
ecs:8c32g 两台,后面会升级成16c64g;4c8g 一台,做为swingbench压测机
ssd云盘:4*200g
2、环境部署
2.1 oracle 软件环境快捷部署
使用袋鼠云oacle11g镜像初始化两台ecs资源
swingbench压测机只要上传swingbench软件安装即可。
2.2 存储环境部署
为提高存储性能,首先使用1块ssd做为redo存储,2块ssd做为data存储,1块做为archivelog的存储。使用lvm,实现方法如下:
创建vg
vgcreate redovg /dev/vdb
vgcreate datavg /dev/vdc /dev/vdd
vgcreate arcvg /dev/vde
创建lv
lvcreate -n redolv1 -L10G redovg
lvcreate -n arclv1 -L50G arcvg
lvcreate -L 100G -n datalv1 -i 2 -I 8 datavg
格式化
mkfs.ext4 /dev/redovg/redolv1
mkfs.ext4 /dev/arcvg /arclv1
mkfs.ext4 /dev/datavg/datalv1
挂载
mount /dev/mapper/redovg-redolv1 /redo
mount /dev/mapper/datavg-datalv1 /data
mount /dev/mapper/arcvg-arclv1 /arc
/data读写测试,95MB/s
time dd if=/dev/mapper/datavg-datalv1 of=/dev/null bs=8k
/arc读写测试,48.4MB/s
为提高存储性能,删除上述vg,改为4盘并条带,redo、data、archivelog均存放在上面。
创建vg
vgcreate datavg /dev/vdb /dev/vdc /dev/vdd /dev/vde
创建lv
lvcreate -L 100G -n datalv1 -i 4 -I 8 datavg
lvcreate -L 10G -n redolv1 -i 4 -I 8 datavg
lvcreate -L 50G -n arclv1 -i 4 -I 8 datavg
格式化
mkfs.ext4 /dev/datavg/redolv1
mkfs.ext4 /dev/datavg/arclv1
mkfs.ext4 /dev/datavg/datalv1
挂载
mount /dev/mapper/datavg-redolv1 /redo
mount /dev/mapper/datavg-datalv1 /data
mount /dev/mapper/datavg-arclv1 /arc
dd测试169MB/S
time dd if=/dev/mapper/datavg-datalv2 of=test.sdf bs=8k
只读380MB/S
time dd if=/dev/mapper/datavg-datalv2 of=/dev/null bs=8k
只写300MB/S
time dd if= of=/dev/zero /dev/mapper/datavg-datalv2 bs=8k
总结:
如果用文件系统管理,4块盘lvm做条带,读写性能不错,吞吐率可以达到169MB/s
--
mkdir redo
mkdir data1
mkdir arc
mount /dev/datavg/redolv1 /redo
mount /dev/datavg/datalv1 /data1
mount /dev/datavg/arclv1 /arc
chown -R oracle:oinstall /redo
chown -R oracle:oinstall /data1
chown -R oracle:oinstall /arc
3. DG搭建
搭建备库的步骤不再这里详细描述,这里直接通过拷贝的方式进行
主库
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstd)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arc/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_2='service=orclstd lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orclstd' scope=both;
alter system set fal_client='orclstd' scope=both sid='*';
alter system set fal_server='orcl' scope=both sid='*';
alter system set log_archive_dest_state_2='DEFER' scope=both sid='*';
alter system set memory_max_target=0 scope=spfile;
alter system set memory_target=0 scope=spfile;
alter system set sga_max_size=20G scope=spfile;
alter system set sga_target=20G scope=spfile;
alter system set pga_aggregate_target=2G scope=spfile;
alter system set processes=2000 scope=spfile;
备库
*.control_files='/data1/stdcotrl1.ctl', '/dat1/stdcotrl2.ctl'
*.db_name=orcl
*.db_unique_name=orclstd
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstd)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd'
*.log_archive_dest_2='service=orcl lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.fal_client='orclstd'
*.fal_server='orcl'
alter database create standby controlfile as '/home/oracle/control_std.bak';
ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 ('/redo/standby_redo_1_01.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 2 ('/redo/standby_redo_1_02.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/redo/standby_redo_1_03.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/redo/standby_redo_1_04.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/redo/standby_redo_1_05.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/redo/standby_redo_1_06.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/redo/standby_redo_1_07.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('/redo/standby_redo_1_08.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/redo/standby_redo_1_09.log') SIZE 256M ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/redo/standby_redo_1_10.log') SIZE 256M ;
dg开启命令
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database parallel 16 disconnect from session;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile disconnect from session;
4. 使用swingbench进行压测
4.1 灌入数据
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arc' scope=both;
alter database rename file '/u01/app/oracle/oradata/ORCL/system01.dbf' to '/data1/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' to '/data1/sysaux01.dbf‘;
alter database rename file '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' to 'undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORCL/users01.dbf' to 'users01.dbf’;
4.2 基准测试
吞吐率247MB/s
4.3 同步测试
8c32g
并发 | log模式 | 磁盘模式 | tps | rt | 带宽占用 |
100 | lgwr+sync | redo*1、data*2、arc*1 | 1054 | 23 | 34Mb/s |
200 | lgwr+sync | redo*1、data*2、arc*1 | 2456 | 30 | 65Mb/s |
300 | lgwr+sync | redo*1、data*2、arc*1 | 2400 | 46 | 80Mb/s |
400 | lgwr+sync | redo*1、data*2、arc*1 | 2500 | 51 | 110Mb/s |
400 | lgwr+async | redo*4、data*4、arc*4 | 3000 | 51 | 111Mb/s |
500 | lgwr+async | redo*4、data*4、arc*4 | 3400 | 62 | 130Mb/s |
16c64g
并发 | log模式 | 磁盘模式 | tps | rt | 带宽占用 |
400 | lgwr+async | redo*4、data*4、arc*4 | 3800 | 8 | 111Mb/s |
500 | lgwr+async | redo*4、data*4、arc*4 | 5300 | 26 | 130Mb/s |
700 | lgwr+async | redo*4、data*4、arc*4 | 6280 | 51 | 180Mb/s |
5. 总结
8c32g可以支持3400的tps,500的并发,16c64g可以支持6000以上的tps,700以上的并发,ecsvpc环境内网带宽可达200Mb/s,同步归档时网络不是瓶颈。
在400并发的时候,数据库等待严重,日志切换形成较大压力,达到平均11万ms,此时采取的措施是加redolog组,并把原来分盘存储的redo、arch、data都改为4块盘的条带化;lgwr+sync模式改为lgwr+async模式,log file switch切换等待在top10 中消失。此时可以继续进行500并发的测试。在没有特殊要求的情况下,不要求最大保护模式,建议Dataguard使用最大性能模式。
LGWR还分为LGWR ASYNC(异步)和LGWR SYNC(同步)两种。
最大保护 | 最大可用 | 最大性能 | |
进程 | LGWR | LGWR | LGWR或ARCH |
网络传输模式 | SYNC | SYNC | LGWR时设置ASYNC |
磁盘写操作 | AFFIRM | AFFIRM | NOAFFIRM |
备用日志 | YES | 物理备用需要 | LGWR和物理备用时需要 |
备用库类型 | 物理Standby | 物理或逻辑 | 物理或逻辑 |