牛刀小试--Oracle Swingbench 压力测试
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路(ID: JiekeXu_IT)
如需转载请联系授权 | (个人微信 ID:JiekeXu_DBA)
大家好,我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来学习 Oracle Swingbench 压力测试,欢迎点击上方蓝字关注我,标星或置顶,更多干货第一时间到达!
1、Swingbench 简介
Swingbench是一个免费的负载生成器和基准测试工具,其支持Oracle数据库(11g、12c、18c、19c)。Swingbench的开发目的主要是展示RAC的负载和测试,也可用于单实例环境。下载地址为 http://www.dominicgiles.com/downloads.html
Swingbench模拟了一套订单业务逻辑,通过创建SOE用户,模拟产品和订单业务,可以自定义数据量的大小,本次测试将生成50GB的业务数据。
2.下载 swingbench 工具
我们使用如下 github 地址下载:https://github.com/domgiles/swingbench-public/releases/tag/production
如果有需要可下载 java JDK https://www.oracle.com/java/technologies/downloads/
安装Swingbench 2.5或2.6,运行环境需要是Java 8及以上版本,Oracle 19c 默认使用 JDK 1.8版本,如果系统 JDK 版本为1.8以下的版本,则需要将JDK版本升级到1.8及以上版本。上传安装包解压即可。
上传后验证文件完整性并解压
cksum swingbenchlatest.zip1679811525 41211958 swingbenchlatest.zipunzip swingbenchlatest.zip
cd swingbench
ls -l
-rw-rw-rw- 1 oracle oinstall 6163 Mar 16 19:01 README.txt
drwxr-xr-x 3 oracle oinstall 283 Mar 15 21:30 bin
drwxr-xr-x 2 oracle oinstall 4096 Mar 16 03:29 configs
drwxr-xr-x 2 oracle oinstall 112 Mar 14 05:14 launcher
drwxr-xr-x 3 oracle oinstall 237 Mar 15 20:17 lib
drwxr-xr-x 2 oracle oinstall 6 Mar 3 2010 log
drwxr-xr-x 3 oracle oinstall 50 Mar 16 18:05 source
drwxr-xr-x 2 oracle oinstall 8192 Mar 15 20:17 sql
drwxr-xr-x 2 oracle oinstall 61 Nov 7 2018 utils
drwx------ 3 oracle oinstall 4096 Mar 14 05:39 winbin
drwxr-xr-x 2 oracle oinstall 155 Mar 14 05:23 wizardconfigs
3.数据生成
进入 swingbench/bin 目录,Linux 系统在 bin 目录执行(需要调出图形化界面),
不然会报错Exception: java.lang.NoClassDefFoundError thrown from the UncaughtExceptionHandler in thread "AWT-EventQueue-0"
使用 xshell
# echo $DISPLAY
localhost:11.0
$ export DISPLAY=localhost:11.0
cd /home/oracle/tmp/swingbench/bin
./oewizard
B. 选择version2.0 ,点击next;
C. 选择创建数据
填写连接串,格式 SCANIP:Port/service_names
可以选择默认用户密码及表空间,也可以选择现有表空间,如OLTP,这里默认,点击 next
为基准测试选择一个预先配置的大小。或者指定你自己的。选项1 = 1GB。根据你的Buffer Cache的大小,我们建议模式大小为 CPU 密集型工作负载为 6.9 GB, I/O 密集型工作负载至少为 322.7GB 数据。
到此处,根据上图去创建需要的表空间,和调整临时表空间大小,并将日志组大小调大,例如每个实例5*10g。
create tablespace SOE datafile '+DATA' size 10240M autoextend on maxsize 30g;
alter tablespace SOE add datafile '+DATA' size 10240M autoextend on maxsize 30g;
alter tablespace SOE add datafile '+DATA' size 10240M autoextend on maxsize 30g;
alter tablespace SOE add datafile '+DATA' size 10240M autoextend on maxsize 30g;
alter tablespace SOE add datafile '+DATA' size 10240M autoextend on maxsize 30g;
alter tablespace SOE add datafile '+DATA' size 10240M autoextend on maxsize 30g;
col FILE_NAME for a89
col TABLESPACE_NAME for a8
select
FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 GB from dba_temp_files;
alter database tempfile 1 resize 32767m;
--Redo 日志组由原来的各两组变成四组。
select GROUP#,THREAD#,BYTES/1024/1024 mb,status from v$log;
ALTER DATABASE ADD LOGFILE THREAD 1 ('+DATA','+DATA') size 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2 ('+DATA','+DATA') size 2048M;
点击完成后则开始创建数据,新建用户,表,序列、索引、约束等对象,并收集统计信息。50G 的数据创建还是比较慢的,还是静静的等待完成吧。
由于是测试虚拟机器,我这里创建 50G 的数据 40 个并行,也大概需要 52 分钟,还是比较慢的。
SQL> col owner for a10
SQL> col table_name for a30
SQL> select
owner,table_name,TABLESPACE_NAME,NUM_ROWS from dba_tables where owner='SOE'
order by num_rows desc;
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
----------
------------------------------ ------------------------------ ----------
SOE ORDER_ITEMS SOE 359864756
SOE LOGON SOE 119149200
SOE ADDRESSES SOE 75000000
SOE CARD_DETAILS SOE 75000000
SOE ORDERS SOE 71489500
SOE CUSTOMERS SOE 50000000
SOE INVENTORIES SOE 901018
SOE PRODUCT_INFORMATION SOE 1000
SOE PRODUCT_DESCRIPTIONS SOE 1000
SOE WAREHOUSES SOE 1000
SOE ORDERENTRY_METADATA SOE 0
col INDEX_NAME for a30
select owner,table_name,INDEX_NAME,INDEX_TYPE,TABLESPACE_NAME from dba_indexes where owner='SOE';
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE TABLESPACE_NAME
----------
------------------------------ ------------------------------
--------------------------- ---------------
SOE CUSTOMERS CUSTOMERS_PK NORMAL/REV SOE
SOE CUSTOMERS CUST_ACCOUNT_MANAGER_IX NORMAL SOE
SOE CUSTOMERS CUST_DOB_IX NORMAL SOE
SOE PRODUCT_DESCRIPTIONS PRD_DESC_PK NORMAL SOE
SOE INVENTORIES INVENTORY_PK NORMAL SOE
SOE ADDRESSES ADDRESS_PK NORMAL/REV SOE
SOE WAREHOUSES WHS_LOCATION_IX NORMAL SOE
SOE ORDERS ORD_SALES_REP_IX NORMAL/REV SOE
SOE CARD_DETAILS CARD_DETAILS_PK NORMAL/REV SOE
SOE INVENTORIES INV_PRODUCT_IX NORMAL SOE
SOE INVENTORIES INV_WAREHOUSE_IX NORMAL SOE
SOE WAREHOUSES WAREHOUSES_PK NORMAL SOE
SOE ORDERS ORD_CUSTOMER_IX NORMAL/REV SOE
SOE ORDER_ITEMS ORDER_ITEMS_PK NORMAL/REV SOE
SOE ORDERS ORD_ORDER_DATE_IX NORMAL/REV SOE
SOE ORDERS ORDER_PK NORMAL/REV SOE
SOE PRODUCT_INFORMATION PRODUCT_INFORMATION_PK NORMAL SOE
SOE CUSTOMERS CUST_FUNC_LOWER_NAME_IX FUNCTION-BASED NORMAL SOE
SOE ORDER_ITEMS ITEM_ORDER_IX NORMAL/REV SOE
SOE PRODUCT_INFORMATION PROD_CATEGORY_IX NORMAL SOE
SOE ORDERS ORD_WAREHOUSE_IX NORMAL SOE
SOE CARD_DETAILS CARDDETAILS_CUST_IX NORMAL SOE
SOE ADDRESSES ADDRESS_CUST_IX NORMAL/REV SOE
SOE PRODUCT_INFORMATION PROD_SUPPLIER_IX NORMAL SOE
SOE ORDER_ITEMS ITEM_PRODUCT_IX NORMAL/REV SOE
SOE CUSTOMERS CUST_EMAIL_IX NORMAL SOE
SOE PRODUCT_DESCRIPTIONS PROD_NAME_IX NORMAL SOE
27 rows selected.
点击 ok,完成数据创建,这样就创建成功了。
接下来调整参数,重启数据库就可以测试了,调整参数如下:
alter system set processes=4000 scope=spfile;
alter system set db_files=2500 scope=spfile;
alter system set standby_file_management='AUTO';
alter system set control_file_record_keep_time=30;
alter system set fast_start_mttr_target=120 scope=spfile;
alter system set open_links_per_instance=10 scope=spfile;
alter system set session_cached_cursors=600 scope=spfile;
alter system set archive_lag_target=1800 scope=spfile;
alter system set open_cursors=1500 scope=spfile;
alter system set open_links=10 scope=spfile;
alter system set undo_retention=10800 scope=spfile;
alter system set lock_sga=FALSE scope=spfile;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set resource_manager_plan='' scope=spfile;
alter system set statistics_level='TYPICAL' scope=spfile;
alter system set timed_statistics=TRUE scope=spfile;
alter system set undo_management='AUTO' scope=spfile;
alter system set db_cache_advice='OFF' scope=spfile;
alter system set audit_trail='NONE' scope=spfile;
alter system set audit_sys_operations=false scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_sort_elimination_cost_ratio"=0 scope=spfile;
alter system set "_use_adaptive_log_file_sync"= FALSE scope=spfile;
alter system set "result_cache_max_size"= 0 scope=spfile;
alter system set use_large_pages=only scope=spfile;
alter system set "_optimizer_use_feedback"=false scope=spfile;
Instance jiekedb1 is running on node jieke19cr1
Instance jiekedb2 is running on node jieke19cr2
3.测试
cd /home/oracle/tmp/swingbench/bin
执行./swingbench
Application : Swingbench
Author : Dominic Giles
Version : 2.6.0.1163
填入要监控的主机信息,对主机进行监控,测试一下是否能连接成功,上述都配置完以后,点击绿色的开始按钮,开始测试,当测试的图形平稳后截图。
4.总结
压测总共使用 23 分钟,500 并发会话连接,大约有 350 活跃会话。我们需要观察 TPS(吞吐量)、IOPS(每秒读写次数)、RT(响应时间)CPU等数据。如下:
好了,这次就先这样吧,欢迎一起来讨论。全文完,希望可以帮到正在阅读的你,如果觉得有帮助,可以分享给你身边的朋友,同事,你关心谁就分享给谁,一起学习共同进步~~~
❤️ 欢迎关注我的视频号,一起学习新知识!!!
————————————————————————————
公众号:JiekeXu DBA之路
墨天轮:https://www.modb.pro/u/4347
CSDN :https://blog.csdn.net/JiekeXu
腾讯云:https://cloud.tencent.com/developer/user/5645107
————————————————————————————