Oracle数据库压力测试

Oracle日常运维 专栏收录该内容
82 篇文章 1 订阅

1 文档描述
公司新采购的X86架构硬件服务器,在ORACLE RAC安装完成之后,根据ORACLE集群上线前相关测试要求,借助于Orion和SwingBench等工具对IO、TPM、TPS等相关性能指标进行了充分的测试以及相关日志记录,我们根据测试的步骤及日志整理汇总此文档。
2 测试内容
2.1 ORION测试
2.1.1 ORION工具介绍
ORION是Oracle提供的IO性能测试工具,该工具不需要安装Oracle Database软件或创建数据库。它可用模拟Oracle数据库的IO负载,也可以用来仿真ASM的条带化功能。测试随机或顺序访问指定大小快的IO性能,性能指标包括:IOPS、MBPS、Latency。
2.1.2 ORION测试步骤
2.1.2.1 检查ASM磁盘路径

SQL> select a.group_number,b.name,a.name,a.path,b.state from v$asm_disk a,v$asm_diskgroup b where a.group_number=b.group_number order by 1,2;

GROUP_NUMBER NAME			    NAME			   PATH 			  STATE
------------ ------------------------------ ------------------------------ ------------------------------ -----------
	   1 OCR			    OCR_0000			   /dev/asm-ocr1		  MOUNTED
	   1 OCR			    OCR_0002			   /dev/asm-ocr3		  MOUNTED
	   1 OCR			    OCR_0001			   /dev/asm-ocr2		  MOUNTED
	   2 DATA			    DATA_0001			   /dev/asm-data2		  MOUNTED
	   2 DATA			    DATA_0000			   /dev/asm-data1		  MOUNTED
	   2 DATA			    DATA_0002			   /dev/asm-data3		  MOUNTED
	   3 ARCH			    ARCH_0002			   /dev/asm-arch3		  MOUNTED
	   3 ARCH			    ARCH_0000			   /dev/asm-arch1		  MOUNTED
	   3 ARCH			    ARCH_0001			   /dev/asm-arch2		  MOUNTED
	   3 ARCH			    ARCH_0003			   /dev/asm-arch4		  MOUNTED

10 rows selected.

计划踢除ARCH_0003磁盘,测试此块磁盘的IO性能。
2.1.2.2 踢除磁盘
在系统中检查/dev/asm-arch4所对应的磁盘路径
SQL> !ls -l /dev/asm-arch4
lrwxrwxrwx 1 root root 4 Apr 3 10:29 /dev/asm-arch4 -> dm-7
从+ARCH磁盘组中踢除磁盘ARCH_0003
SQL> alter diskgroup arch drop disk arch_0003;

Diskgroup altered.
检查arch_0003已从+ARCH磁盘组中踢除

SQL>  select a.group_number,b.name,a.name,a.path,b.state from v$asm_disk a,v$asm_diskgroup b where a.group_number=b.group_number order by 1,2;

GROUP_NUMBER NAME			    NAME			   PATH 			  STATE
------------ ------------------------------ ------------------------------ ------------------------------ -----------
	   1 OCR			    OCR_0002			   /dev/asm-ocr3		  MOUNTED
	   1 OCR			    OCR_0001			   /dev/asm-ocr2		  MOUNTED
	   1 OCR			    OCR_0000			   /dev/asm-ocr1		  MOUNTED
	   2 DATA			    DATA_0002			   /dev/asm-data3		  MOUNTED
	   2 DATA			    DATA_0000			   /dev/asm-data1		  MOUNTED
	   2 DATA			    DATA_0001			   /dev/asm-data2		  MOUNTED
	   3 ARCH			    ARCH_0000			   /dev/asm-arch1		  MOUNTED
	   3 ARCH			    ARCH_0002			   /dev/asm-arch3		  MOUNTED
	   3 ARCH			    ARCH_0001			   /dev/asm-arch2		  MOUNTED

2.1.2.3 创建lun文件
[root@orcl1 orion]# vi orcl.lun
添加如下内容:
/dev/dm-7
2.1.2.4 验证磁盘路径可访问
[root@orcl1 ~]# dd if=/dev/dm-7 of=/dev/null bs=32k count=1024
1024+0 records in
1024+0 records out
33554432 bytes (34 MB) copied, 0.0565731 s, 593 MB/s
2.1.2.5 OLTP测试
数据库OLTP类型,IO类型为8K随机操作,自动加压,从小到大,一直到存储压力极限。
[root@orcl1 orion]# ./orion_linux_x86-64 -run advanced -testname zjrac -num_disks 1 -size_small 8 -size_large 8 -type rand &
[1] 210086
[root@orcl1 orion]# ORION: ORacle IO Numbers – Version 11.1.0.7.0
zjrac_20200403_1056
Test will take approximately 9 minutes --大约需要9分钟,orion时长不支持手工控制
Larger caches may take longer
测试过程中及完成后会生成如下文件:

 orcl_20200403_1056_iops.csv --IOPS性能数据
 orcl_20200403_1056_lat.csv --Latency性能数据
 orcl_20200403_1056_mbps.csv --MBPS吞吐量性能数据
 orcl_20200403_1056_summary.txt --测试过程的总体概要
 orcl_20200403_1056_trace.txt --测试过程的详细信息

  1. orcl_20200403_1056_trace.txt内容如下:
    TEST START

Point 1 (small=0, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 229100 Avg Lat = 0.26
ran (small): nio=229100 nior=229100 niow=0 req w%=0 act w%=0
ran (small): my 1 oth 0 iops 3818 size 8 K lat 0.26 ms bw = 29.84 MBps dur 59.99 s READ

Point 2 (small=1, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 232158 Avg Lat = 0.26
ran (small): Index = 1 Count = 231907 Avg Lat = 0.26
ran (small): nio=464065 nior=464065 niow=0 req w%=0 act w%=0
ran (small): my 2 oth 0 iops 7734 size 8 K lat 0.26 ms bw = 60.43 MBps dur 60.00 s READ

Point 3 (small=2, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 227079 Avg Lat = 0.26
ran (small): Index = 1 Count = 227008 Avg Lat = 0.26
ran (small): Index = 2 Count = 227324 Avg Lat = 0.26
ran (small): nio=681411 nior=681411 niow=0 req w%=0 act w%=0
ran (small): my 3 oth 0 iops 11356 size 8 K lat 0.26 ms bw = 88.73 MBps dur 60.00 s READ

Point 4 (small=3, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 246656 Avg Lat = 0.24
ran (small): Index = 1 Count = 247531 Avg Lat = 0.24
ran (small): Index = 2 Count = 247215 Avg Lat = 0.24
ran (small): Index = 3 Count = 246968 Avg Lat = 0.24
ran (small): nio=988370 nior=988370 niow=0 req w%=0 act w%=0
ran (small): my 4 oth 0 iops 16475 size 8 K lat 0.24 ms bw = 128.72 MBps dur 59.99 s READ

Point 5 (small=4, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 236742 Avg Lat = 0.25
ran (small): Index = 1 Count = 236749 Avg Lat = 0.25
ran (small): Index = 2 Count = 236509 Avg Lat = 0.25
ran (small): Index = 3 Count = 236568 Avg Lat = 0.25
ran (small): Index = 4 Count = 236710 Avg Lat = 0.25
ran (small): nio=1183278 nior=1183278 niow=0 req w%=0 act w%=0
ran (small): my 5 oth 0 iops 19721 size 8 K lat 0.25 ms bw = 154.07 MBps dur 60.00 s READ

Point 6 (small=5, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (large): VLun = 0 Size = 536870912000
ran (large): Index = 0 Count = 248137 Avg Lat = 0.24
ran (large): nio=248137 nior=248137 niow=0 req w%=0 act w%=0
ran (large): my 1 oth 0 iops 4135 size 8 K lat 0.24 ms bw = 32.31 MBps dur 60.00 s READ

Point 7 (small=0, large=1) of 8
Valid small 1 Valid large 1
Valid

ran (large): VLun = 0 Size = 536870912000
ran (large): Index = 0 Count = 244086 Avg Lat = 0.25
ran (large): Index = 1 Count = 243854 Avg Lat = 0.25
ran (large): nio=487940 nior=487940 niow=0 req w%=0 act w%=0
ran (large): my 2 oth 0 iops 8132 size 8 K lat 0.25 ms bw = 63.53 MBps dur 60.00 s READ

Point 8 (small=0, large=2) of 8
Valid small 1 Valid large 1
Valid

TEST END

  1. orcl_20200403_1056_summary.txt内容如下
    ORION VERSION 11.1.0.7.0

Commandline:
-run advanced -testname zjrac -num_disks 1 -size_small 8 -size_large 8 -type rand

This maps to this test:
Test: zjrac
Small IO size: 8 KB
Large IO size: 8 KB
IO Types: Small Random IOs, Large Random IOs
Simulated Array Type: CONCAT
Write: 0%
Cache Size: Not Entered
Duration for each Data Point: 60 seconds
Small Columns:, 0
Large Columns:, 0, 1, 2
Total Data Points: 8

Name: /dev/dm-7 Size: 536870912000
1 FILEs found.

Maximum Large MBPS=63.53 @ Small=0 and Large=2
Maximum Small IOPS=19721 @ Small=5 and Large=0
Minimum Small Latency=0.24 @ Small=4 and Large=0
 最大MBPS为633.53
 最大IOPS为19721
 最小Latency为0.24ms
2.1.2.6 吞吐量测试
数据库吞吐量测试,假定IO全部是1M的序列性IO。
[root@orcl1 orion]# ./orion_linux_x86-64 -run advanced -testname zjrac -num_disks 1 -size_small 1024 -size_large 1024 -type seq &
[1] 218116
[root@orcl1 orion]# ORION: ORacle IO Numbers – Version 11.1.0.7.0
zjrac_20200403_1117
Test will take approximately 15 minutes
Larger caches may take longer

  1. zjrac_20200403_1117_trace.txt内容如下:
    TEST START

Point 1 (small=0, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 27090 Avg Lat = 2.21
ran (small): nio=27090 nior=27090 niow=0 req w%=0 act w%=0
ran (small): my 1 oth 0 iops 451 size 1024 K lat 2.21 ms bw = 451.58 MBps dur 59.99 s READ

Point 2 (small=1, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 19919 Avg Lat = 3.01
ran (small): Index = 1 Count = 19946 Avg Lat = 3.01
ran (small): nio=39865 nior=39865 niow=0 req w%=0 act w%=0
ran (small): my 2 oth 0 iops 664 size 1024 K lat 3.01 ms bw = 664.53 MBps dur 59.99 s READ

Point 3 (small=2, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 15348 Avg Lat = 3.91
ran (small): Index = 1 Count = 15366 Avg Lat = 3.90
ran (small): Index = 2 Count = 15401 Avg Lat = 3.89
ran (small): nio=46115 nior=46115 niow=0 req w%=0 act w%=0
ran (small): my 3 oth 0 iops 768 size 1024 K lat 3.90 ms bw = 768.84 MBps dur 59.98 s READ

Point 4 (small=3, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 11752 Avg Lat = 5.10
ran (small): Index = 1 Count = 11771 Avg Lat = 5.09
ran (small): Index = 2 Count = 11780 Avg Lat = 5.09
ran (small): Index = 3 Count = 11762 Avg Lat = 5.10
ran (small): nio=47065 nior=47065 niow=0 req w%=0 act w%=0
ran (small): my 4 oth 0 iops 784 size 1024 K lat 5.10 ms bw = 784.68 MBps dur 59.98 s READ

Point 5 (small=4, large=0) of 8
Valid small 1 Valid large 1
Valid

ran (small): VLun = 0 Size = 536870912000
ran (small): Index = 0 Count = 9418 Avg Lat = 6.37
ran (small): Index = 1 Count = 9427 Avg Lat = 6.36
ran (small): Index = 2 Count = 9433 Avg Lat = 6.36
ran (small): Index = 3 Count = 9435 Avg Lat = 6.36
ran (small): Index = 4 Count = 9423 Avg Lat = 6.36
ran (small): nio=47136 nior=47136 niow=0 req w%=0 act w%=0
ran (small): my 5 oth 0 iops 785 size 1024 K lat 6.36 ms bw = 785.86 MBps dur 59.98 s READ

Point 6 (small=5, large=0) of 8
Valid small 1 Valid large 1
Valid

seq (large): VLun = 0 Size = 536870912000
seq (large): Index = 0 Count = 11785 Avg Lat = 5.09
seq (large): Index = 1 Count = 11784 Avg Lat = 5.09
seq (large): Index = 2 Count = 11784 Avg Lat = 5.09
seq (large): Index = 3 Count = 11784 Avg Lat = 5.09
seq (large): Stream = 0 VLun = 0 Start = 0 End = 49443504128
seq (large): Stream = 0 CIO = 4 NIO Count = 47137 Avg Lat = 5.09
seq (large): nio=47137 nior=47137 niow=0 req w%=0 act w%=0
seq (large): my 1 oth 0 iops 785 size 1024 K lat 5.09 ms bw = 785.88 MBps dur 59.98 s READ

Point 7 (small=0, large=1) of 8
Valid small 1 Valid large 1
Valid

seq (large): VLun = 0 Size = 536870912000
seq (large): Index = 0 Count = 5891 Avg Lat = 10.18
seq (large): Index = 1 Count = 5890 Avg Lat = 10.18
seq (large): Index = 2 Count = 5891 Avg Lat = 10.17
seq (large): Index = 3 Count = 5891 Avg Lat = 10.18
seq (large): Index = 4 Count = 5891 Avg Lat = 10.18
seq (large): Index = 5 Count = 5891 Avg Lat = 10.17
seq (large): Index = 6 Count = 5890 Avg Lat = 10.18
seq (large): Index = 7 Count = 5891 Avg Lat = 10.18
seq (large): Stream = 0 VLun = 0 Start = 0 End = 24724373504
seq (large): Stream = 0 CIO = 4 NIO Count = 23563 Avg Lat = 10.18
seq (large): Stream = 1 VLun = 0 Start = 268435456000 End = 293159829504
seq (large): Stream = 1 CIO = 4 NIO Count = 23563 Avg Lat = 10.18
seq (large): nio=47126 nior=47126 niow=0 req w%=0 act w%=0
seq (large): my 2 oth 0 iops 785 size 1024 K lat 10.18 ms bw = 785.83 MBps dur 59.97 s READ

Point 8 (small=0, large=2) of 8
Valid small 1 Valid large 1
Valid

TEST END
2. orcl_20200403_1117_summary.txt内容如下:
ORION VERSION 11.1.0.7.0

Commandline:
-run advanced -testname zjrac -num_disks 1 -size_small 1024 -size_large 1024 -type seq

This maps to this test:
Test:orcl
Small IO size: 1024 KB
Large IO size: 1024 KB
IO Types: Small Random IOs, Large Sequential Streams
Number of Concurrent IOs Per Stream: 4
Force streams to separate disks: No
Simulated Array Type: CONCAT
Write: 0%
Cache Size: Not Entered
Duration for each Data Point: 60 seconds
Small Columns:, 0
Large Columns:, 0, 1, 2
Total Data Points: 8

Name: /dev/dm-7 Size: 536870912000
1 FILEs found.

Maximum Large MBPS=785.88 @ Small=0 and Large=1
Maximum Small IOPS=785 @ Small=5 and Large=0
Minimum Small Latency=2.21 @ Small=1 and Large=0
 最大MBPS为785.88
 最大IOPS为785
 最小延迟为2.21ms
2.1.2.7 添加磁盘到磁盘组
测试完成后,将/dev/asm-arch4添加回磁盘组。

SQL> alter diskgroup arch add disk '/dev/asm-arch4';

Diskgroup altered.

SQL> select a.group_number,b.name,a.name,a.path,b.state from v$asm_disk a,v$asm_diskgroup b where a.group_number=b.group_number order by 1,2;

GROUP_NUMBER NAME                           NAME                           PATH            STATE
------------ ------------------------------ ------------------------------ --------------- -----------
           1 OCR                            OCR_0000                       /dev/asm-ocr1   MOUNTED
           1 OCR                            OCR_0002                       /dev/asm-ocr3   MOUNTED
           1 OCR                            OCR_0001                       /dev/asm-ocr2   MOUNTED
           2 DATA                           DATA_0001                      /dev/asm-data2  MOUNTED
           2 DATA                           DATA_0000                      /dev/asm-data1  MOUNTED
           2 DATA                           DATA_0002                      /dev/asm-data3  MOUNTED
           3 ARCH                           ARCH_0002                      /dev/asm-arch3  MOUNTED
           3 ARCH                           ARCH_0000                      /dev/asm-arch1  MOUNTED
           3 ARCH                           ARCH_0001                      /dev/asm-arch2  MOUNTED
           3 ARCH                           ARCH_0003                      /dev/asm-arch4  MOUNTED

10 rows selected.

SQL> select * from v$asm_operation;

no rows selected

2.2 SwingBench测试
2.2.1 SwinBench工具介绍
SwingBench可以执行4种不同的标准测试,拥有三种前端展示方式SwingBench、Charbench、Minibench,其中Charbench是字符模式,另外两种是GUI模式。另外还可通过ClusterOverview可以聚合显示所有的结果。SwingBench主要用来展示RAC的负载和测试,也可用于单实例环境。在2.3版本开始支持TimesTen内存数据库。
SwingBench下载地址:http://www.dominicgiles.com/downloads.html。
2.2.2 SwingBench测试步骤
2.2.2.1 创建表空间

SQL> create tablespace swingbench datafile '+data';

Tablespace created.

SQL> alter tablespace swingbench add datafile '+data';

Tablespace altered.

2.2.2.2 生成swingbench配置文件
[oracle@orcl1 ~]$ cd /swingbench/
[oracle@orcl1 swingbench]$ unzip swingbench240845.zip
[oracle@orcl1 swingbench]$ cd swingbench/bin/
[oracle@orcl1 bin]$ ./oewizard

在这里插入图片描述

弹出配置界面,点击下一步。

在这里插入图片描述

由于是新环境,选择创建新环境。

在这里插入图片描述

填写数据库连接信息。格式为//数据库所在主机IP/数据库名,此处由于压测RAC,填写的是scan ip和service name。
在这里插入图片描述

填写需要创建的用户名,以及用户默认表空间。

在这里插入图片描述

选择需要造的数据量大小。

在这里插入图片描述

选择并行度,默认2cpu count情况下288,由于此环境中4cpu count,因此此处修改并行度为400。
在这里插入图片描述
在这里插入图片描述

等待数据创建完成。
在这里插入图片描述

测试数据创建完成。
2.2.2.3 swingbench测试
[oracle@orcl1 ~]$ cd /swingbench/swingbench/bin/
[oracle@orcl1 bin]$ ./swingbench
在这里插入图片描述

测试场景中,模拟用户并发数为1500,模拟业务场景组成为:
insert --15%
select --40%
update --30%
delete --10

在这里插入图片描述

上图为120分钟的测试数据,测试结果如图所示,最大TPM为758432,平均TPM为650037;最大TPS为24346,平均TPS为10879。
3 测试结果汇总
3.1 ORION测试总结
在这里插入图片描述

3.2 SwingBench测试总结
在这里插入图片描述

4 新旧环境性能对比
旧环境2020年03月30日(周一)早上09:00 - 11:00(早高峰)的120分钟与新环境2020年04月06日swingbench压测120分钟性能数据对比。
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页

打赏作者

徽州吴彦祖

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值