Oracle压测工具 —— SLOB

一、 简介

1. 功能定位

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

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

The SLOB Method aims to test platforms without application contention. 

2. 实现原理

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

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

二、 压测前准备

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过程中会报错

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参数值，即是生成的数据量

./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 ( 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 ----------------------------------------- .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.. 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 ( 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./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.).

四、 结果输出

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

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

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

参考

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的企业级专业社群。技术大咖、原创干货，每天精品原创文章推送，每周线上技术分享，每月线下技术沙龙。