Oracle压测工具 —— SLOB[通俗易懂]

Oracle压测工具 —— SLOB[通俗易懂]一、简介1.功能定位SLOB全称叫SillyLittleOracleBenchmark,是一个在避免应用争用的情况下(锁、latch等),通过SQL模拟IO负载的工具。定位介于Orion、CALIBRATE_IO与全功能事务性压测工具之间。它是一个shell脚本工具,如果有兴趣有需求,可以按实际改写脚本。SLOBisnotadatabasebenchmark.SLOBisanOracleI/Oworkloadgenerationtoolkit..

一、 简介

1. 功能定位

SLOB全称叫 Silly Little Oracle Benchmark,是一个在避免应用争用的情况下(锁、latch等),通过SQL模拟IO负载的工具。定位介于Orion、CALIBRATE_IO与全功能事务性压测工具 之间。

它是一个shell脚本工具,如果有兴趣有需求,可以按实际改写脚本。

SLOB is not a database benchmark. SLOB is an Oracle I/O workload generation tool kit.
 SLOB aims to fill the gap between Orion and CALIBRATE_IO and full-function transactional benchmarks

The SLOB Method aims to test platforms without application contention. 

2. 实现原理

slob的数据设计方式很巧妙:每个oracle block(8KB)中,只存储一行数据(约2KB)。因此可以非常方便地测试单块读、随机读写,轻松控制要访问多少个块。但是注意它不能用于Oracle压缩测试,因为这严重不符合业务数据实际分布情况。

另外slob的update只会更新无索引的字段,避免索引维护开销。

Oracle压测工具 —— SLOB[通俗易懂]
 

二、 压测前准备

1. 操作系统与Oracle层准备

这些操作不是必须的,但能减少干扰因素,帮助测试结果更符合预期

  • 准备足够的磁盘空间
  • 关闭archive
  • redo大小至少1G以上,设置至少6组以上的redo logfile
  • 启用异步IO,设置 filesystemio_options=setall
  • 设置 DB_WRITER_PROCESSES 至少为CPU数除以4
  • 调整最大连接数至2000(根据load并发数定,避免打爆连接数)

2. 安装 SLOB

超级简单,直接解压。另外要进到wait_kit目录编译一下文件

[oracle@erpdb wait_kit]$ make
#输出内容
rm -fr *.o mywait trigger create_sem
cc -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o
只听到从架构师办公室传来架构君的声音:
但愿长如此,躬耕非所叹。有谁来对上联或下联?

3. load数据前准备

  • 创建专用的tablespace及temp tablespace 用于slob测试

注意准备压测的数据量,不要建太小,否则load过程中会报错

此代码由Java架构师必看网-架构君整理
create tablespace erpdata datafile size 2g autoextend on next 100m maxsize 30g, ... size 2g autoextend on next 100m maxsize 30g; create temporary tablespace erptmp tempfile size 2g autoextend on next 100m maxsize 30g, ... size 2g autoextend on next 100m maxsize 30g; alter database default temporary tablespace erptmp;
  • 确认/tmp目录是所有数据库用户都能读写
create directory mydir as '/tmp';
grant read,write on directory mydir to public;
  • load参数设置(slob.conf)
  1. SCALE:指定每个schema数据量
  2. LOAD_PARALLEL_DEGREE:设置load并行度,建议为2倍CPU数
  3. DBA_PRIV_USER,SYSDBA_PASSWD 对应使用的数据库用户和密码,如果不想用默认的,要记得修改

4. load数据

运行setup.sh脚本,变量1是表空间名,变量2是要创建的schema数。schema数*SCALE参数值,即是生成的数据量

此代码由Java架构师必看网-架构君整理
./setup.sh erpdata 64

日志输出

[ora@erpdb SLOB]$ ./setup.sh erpdata 64
NOTIFY  : 2021.07.20-15:05:45 : Begin SLOB 2.5.4.0 setup.
NOTIFY  : 2021.07.20-15:05:45 : ADMIN_CONNECT_STRING: "system/manager"
NOTIFY  : 2021.07.20-15:05:45 : Load parameters from slob.conf: 
 
SCALE: 8G (1048576 blocks)
SCAN_TABLE_SZ: 1M (128 blocks)
LOAD_PARALLEL_DEGREE: 128
ADMIN_SQLNET_SERVICE: ""
SYSDBA_PASSWD: "manager"
DBA_PRIV_USER: "system"

Note: setup.sh will use the following connect strings as per slob.conf:
        Admin Connect String: "system/manager"
        Non-Admin Connect String: " "

NOTIFY  : 2021.07.20-15:05:45 : Testing Admin connect using "sqlplus -L system/manager"
NOTIFY  : 2021.07.20-15:05:45 : Dropping prior SLOB schemas. This may take a while if there is a large number of old schemas.
NOTIFY  : 2021.07.20-15:05:53 : Deleted 127 SLOB schema(s).
NOTIFY  : 2021.07.20-15:05:53 : Previous SLOB schemas have been removed
NOTIFY  : 2021.07.20-15:05:53 : Preparing to load 64 schema(s) into tablespace: erpdata
NOTIFY  : 2021.07.20-15:05:53 : Loading user1 schema
NOTIFY  : 2021.07.20-15:06:24 : Finished loading and indexing user1 schema in 31 seconds
NOTIFY  : 2021.07.20-15:06:24 : Commencing multiple, concurrent schema creation and loading
NOTIFY  : 2021.07.20-15:06:33 : Waiting for background batch 1. Loading up to user64
hyhyNOTIFY  : 2021.07.20-15:20:16 : Completed concurrent data loading phase: 832 seconds
NOTIFY  : 2021.07.20-15:20:16 : Creating SLOB UPDATE procedure
NOTIFY  : 2021.07.20-15:20:17 : SLOB UPDATE procedure (./misc/procedure.sql) created.
NOTIFY  : 2021.07.20-15:20:17 : Row and block counts for SLOB table(s) reported in ./slob_data_load_summary.txt
NOTIFY  : 2021.07.20-15:20:17 : Please examine ./slob_data_load_summary.txt for any possible errors
NOTIFY  : 2021.07.20-15:20:17 : 
NOTIFY  : 2021.07.20-15:20:17 : NOTE: No errors detected but if ./slob_data_load_summary.txt shows errors then
NOTIFY  : 2021.07.20-15:20:17 : examine /data/SLOB_2.5.4-main/SLOB/cr_tab_and_load.out

NOTIFY  : 2021.07.20-15:20:17 : SLOB setup complete. Total setup time:  (872 seconds)

查看数据量大小

select sum(BYTES/1024/1024)||'M' MB  from dba_extents;

MB
-----------------------------------------
531345.5625M

三、 进行压测

1. 主要参数配置

  • SCAN_PCT:控制short table(小表)全表扫描占整个select的比例
  • SCAN_TABLE_SZ:要创建的short table的大小,默认1MB
  • UPDATE_PCT:读写比例,如果不设SCAN_PCT,读指的是通过索引访问而不是全表扫描

例如设置UPDATE_PCT=20,SCAN_PCT=40:表示写操作占20%,读占80%(其中小表全表扫描占40%,通过索引访问占60%)

  • THREADS_PER_SCHEMA:指定每个schema会有多少个会话去访问
  • RUN_TIME=300:执行时间(秒)
  • WORK_LOOP=0:循环执行次数
  • WORK_UNIT=64:每个slob操作要读/写的数据块数量,例如64表示每个select、update都操作64个数据块
  • REDO_STRESS:设为HEAVY,会产生大量redo记录,模拟大量压力。设为其他值(默认为LITE),则按正常生成redo日志。
  • DATABASE_STATISTICS_TYPE:使用statspack还是awr收集数据库信息,需要改为awr

2. 压测

./runit.sh 64

日志输出

[ora@erpdb-test SLOB]$ ./runit.sh 64
NOTIFY  : 2021.07.20-17:17:23 : For security purposes all file and directory creation and deletions
NOTIFY  : 2021.07.20-17:17:23 : performed by ./runit.sh are logged in: /data/SLOB_2.5.4-main/SLOB/.file_operations_audit_trail.out.
NOTIFY  : 2021.07.20-17:17:23 : SLOB TEMPDIR is /tmp/.SLOB.2021.07.20.171723. SLOB will delete this directory at the end of this execution.
NOTIFY  : 2021.07.20-17:17:23 : Sourcing in slob.conf
NOTIFY  : 2021.07.20-17:17:23 : Performing initial slob.conf sanity check...
NOTIFY  : 2021.07.20-17:17:23 : 
NOTIFY  : 2021.07.20-17:17:23 : SQLNET_SERVICE_BASE is not set. Users will connect via bequeth connections (not SQL*Net).
NOTIFY  : 2021.07.20-17:17:23 : Connecting to the instance to validate slob.conf->SCALE setting.
 
UPDATE_PCT: 0
SCAN_PCT: 0
RUN_TIME: 300
WORK_LOOP: 0
SCALE: 8G (524288 blocks)
WORK_UNIT: 64
REDO_STRESS: LITE
HOT_SCHEMA_FREQUENCY: 0
HOTSPOT_MB: 8
HOTSPOT_OFFSET_MB: 16
HOTSPOT_FREQUENCY: 3
THINK_TM_FREQUENCY: 0
THINK_TM_MIN: .1
THINK_TM_MAX: .5
DATABASE_STATISTICS_TYPE: awr
SYSDBA_PASSWD: "manager"
DBA_PRIV_USER: "system"
ADMIN_SQLNET_SERVICE: ""
  LNET_SERVICE_BASE: ""
SQLNET_SERVICE_MAX: ""

EXTERNAL_SCRIPT: ""


Note: runit.sh will use the following connect strings as per slob.conf settings:
        Admin Connect String: "system/manager"

NOTIFY  : 2021.07.20-17:17:23 : Clearing temporary SLOB output files from previous SLOB testing.
NOTIFY  : 2021.07.20-17:17:23 : Testing admin connectivity to the instance to validate slob.conf settings.
NOTIFY  : 2021.07.20-17:17:23 : Testing connectivity. Command: "sqlplus -L system/manager".
NOTIFY  : 2021.07.20-17:17:23 : Next, testing 64 user (non-admin) connections...
NOTIFY  : 2021.07.20-17:17:23 : Testing connectivity. Command: "sqlplus -L user1/user1".
NOTIFY  : 2021.07.20-17:17:24 : Testing connectivity. Command: "sqlplus -L user64/user64".
NOTIFY  : 2021.07.20-17:17:24 : Performing redo log switch.
NOTIFY  : 2021.07.20-17:17:36 : Redo log switch complete. Setting up trigger mechanism.
NOTIFY  : 2021.07.20-17:17:46 : Running iostat, vmstat and mpstat on current host--in background.
NOTIFY  : 2021.07.20-17:17:46 : Connecting 1 (THREADS_PER_SCHEMA) session(s) to 64 schema(s) ...
NOTIFY  : 2021.07.20-17:17:47 : Saved pids of monitored sqlplus processes in: /tmp/.SLOB.2021.07.20.171723/sqlplus_pids.txt
NOTIFY  : 2021.07.20-17:17:47 : Pausing for 5 seconds before triggering the test.
NOTIFY  : 2021.07.20-17:17:52 : Executing awr "before snap" procedure. Command: "sqlplus -S -L system/manager".
NOTIFY  : 2021.07.20-17:17:53 : Before awr snap ID is 39
NOTIFY  : 2021.07.20-17:17:53 : Test has been triggered.
NOTIFY  : 2021.07.20-17:18:03 : Waiting for 290 seconds before monitoring running processes (for exit).
hyNOTIFY  : 2021.07.20-17:22:53 : Entering process monitoring loop.
NOTIFY  : 2021.07.20-17:22:55 : Run time 302 seconds.
NOTIFY  : 2021.07.20-17:22:55 : Executing awr "after snap" procedure. Command: "sqlplus -S -L system/manager".
NOTIFY  : 2021.07.20-17:22:56 : After awr snap ID is 40
NOTIFY  : 2021.07.20-17:22:56 : Terminating background data collectors.
./runit.sh: line 119: 29703 Killed                  ( vmstat -t 3 > vmstat.out 2>&1 )
./runit.sh: line 1547: 29702 Killed                  ( iostat -t -xm 3 > iostat.out 2>&1 )
./runit.sh: line 1547: 29704 Killed                  ( mpstat -P ALL 3 > mpstat.out 2>&1 )
NOTIFY  : 2021.07.20-17:23:07 : 
NOTIFY  : 2021.07.20-17:23:07 : SLOB test is complete.
NOTIFY  : 2021.07.20-17:23:07 : Cleaning up SLOB temporary directory (/tmp/.SLOB.2021.07.20.171723).

四、 结果输出

生成文件包括 iostat.out, vmstat.out, mpstat.out 以及 AWR reports,如果不需要操作系统数据收集,可以设置NO_OS_PERF_DATA=TRUE。

Oracle压测工具 —— SLOB[通俗易懂]

 slob 附带了一个awr_info.sh脚本,用于解析awr报告,slob官档有每个解析项含义。

Oracle压测工具 —— SLOB[通俗易懂]

./awr_info.sh awr.txt
#可以一次解析多个,例如
./awr_info.sh awr.txt awr02.txt

Oracle压测工具 —— SLOB[通俗易懂]

参考

 https://kevinclosson.net/slob/
https://kevinclosson.net/2014/08/04/slob-deployment-a-picture-tutorial/
https://kevinclosson.net/2014/08/06/slob-data-loading-case-studies-part-i-a-simple-concurrent-parallel-example/
https://gruffdba.wordpress.com/2017/03/04/testing-emc-unity-storage-performance-with-slob/

Step-By-Step SLOB Installation and Quick Test Guide for Amazon RDS for Oracle. | Kevin Closson's Blog: Platforms, Databases and Storage

Oracle IO压测工具SLOB - 墨天轮

http://fariddba.blogspot.com/2017/04/oracle-12c-load-testing-withthe-silly.html

EMC Unity Storage Performance testing with Oracle ASM and SLOB | the gruffdba

Oracle IO性能测试工具Orion详解 - ORACLE - dbaplus社群:围绕Data、Blockchain、AiOps的企业级专业社群。技术大咖、原创干货,每天精品原创文章推送,每周线上技术分享,每月线下技术沙龙。

本文来源Hehuyi_In,由架构君转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/212260
0

发表评论