运维首页 > 数据运维 > Oracle数据库压力测试方法实战

Oracle数据库压力测试方法实战

2024年 7月 4日 62.6k 0

Oracle数据库压力测试方法实战-1

前言

由于生产环境硬件已使用7年，近期打算把核心系统迁移到新购的深信服超融合服务器上。但担心超融合的性能不能满足当前生成环境Oracle 11.2.0.4 RAC的迁移。我们可以先在超融合搭建一套Oracle11g RAC环境，在没有真实业务数据的情况下做一些基本的CPU，IO，事务的压力测试。再根据实际情况做全面的迁移测试。

测试环境

CPU 16c，内存 256G，操作系统版本Oracle Linux Server 7.9，数据库版本11.2.0.4 RAC

[root@cmsdb1 ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 1
Core(s) per socket: 16
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 15
Model name: Intel(R) Core(TM)2 Duo CPU T7700 @ 2.40GHz
Stepping: 11
CPU MHz: 2394.372
BogoMIPS: 4788.74
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 4096K
NUMA node0 CPU(s): 0-15
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx lm constant_tsc rep_good nopl cpuid tsc_known_freq pni ssse3 cx16 pcid sse4_2 x2apic hypervisor lahf_lm cpuid_fault pti
[root@cmsdb1 ~]# free -h
total used free shared buff/cache available
Mem: 251G 1.4G 238G 10G 11G 236G
Swap: 31G 0B 31G
[root@cmsdb1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
[root@cmsdb1 ~]# cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.9"

ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9
[root@cmsdb1 ~]# su - grid
Last login: Wed Jul 3 18:35:05 CST 2024 on pts/3
[grid@cmsdb1 ~]$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 409598 409499 0 409499 0 N ARCH/
MOUNTED EXTERN N 512 4096 1048576 3145725 2745476 0 2745476 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 30717 29791 10239 9776 0 Y OCR/
[grid@cmsdb1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.ARCH.dg ora....up.type ONLINE ONLINE cmsdb1
ora.DATA.dg ora....up.type ONLINE ONLINE cmsdb1
ora....ER.lsnr ora....er.type ONLINE ONLINE cmsdb1
ora....N1.lsnr ora....er.type ONLINE ONLINE cmsdb2
ora.OCR.dg ora....up.type ONLINE ONLINE cmsdb1
ora.asm ora.asm.type ONLINE ONLINE cmsdb1
ora.cmsbj.db ora....se.type ONLINE ONLINE cmsdb1
ora....SM1.asm application ONLINE ONLINE cmsdb1
ora....B1.lsnr application ONLINE ONLINE cmsdb1
ora.cmsdb1.gsd application OFFLINE OFFLINE
ora.cmsdb1.ons application ONLINE ONLINE cmsdb1
ora.cmsdb1.vip ora....t1.type ONLINE ONLINE cmsdb1
ora....SM2.asm application ONLINE ONLINE cmsdb2
ora....B2.lsnr application ONLINE ONLINE cmsdb2
ora.cmsdb2.gsd application OFFLINE OFFLINE
ora.cmsdb2.ons application ONLINE ONLINE cmsdb2
ora.cmsdb2.vip ora....t1.type ONLINE ONLINE cmsdb2
ora.cvu ora.cvu.type ONLINE ONLINE cmsdb2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE cmsdb1
ora.oc4j ora.oc4j.type ONLINE ONLINE cmsdb2
ora.ons ora.ons.type ONLINE ONLINE cmsdb1
ora.scan1.vip ora....ip.type ONLINE ONLINE cmsdb2
[grid@cmsdb1 ~]$ exit
[root@cmsdb1 ~]# su - oracle
Last login: Thu Jul 4 16:31:07 CST 2024 on pts/1
[oracle@cmsdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 4 16:31:47 2024

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select status from gv$instance;

STATUS
------------
OPEN
OPEN

SQL>

数据库参数调整

在压测之前先把数据库的参数做一下调整，针对11g RAC数据库参数需要做如下调整，调整后重启两个节点数据库。

[oracle@cmsdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 1 21:15:38 2024

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 153
Next log sequence to archive 154
Current log sequence 154
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 180G
sga_target big integer 180G
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 20G
SQL>
SQL> alter system set parallel_force_local=true sid='*' scope=spfile;

System altered.

SQL> alter system set "_gc_policy_time"=0 sid='*' scope=spfile;

System altered.

SQL> alter system set "_gc_undo_affinity"=false scope=spfile;

System altered.

SQL> alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;

System altered.

SQL> alter system set deferred_segment_creation=false sid='*' scope=spfile;

System altered.

SQL> alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;

System altered.

SQL> alter system set resource_limit=true sid='*' scope=spfile;

System altered.

SQL> alter system set resource_manager_plan='force:' sid='*' scope=spfile;

System altered.

SQL> alter system set "_undo_autotune"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_null_aware_antijoin"=false sid ='*' scope=spfile;

System altered.

SQL> alter system set "_px_use_large_pool"=true sid ='*' scope=spfile;

System altered.

SQL> alter system set "_partition_large_extents"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_index_partition_large_extents"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile;

System altered.

SQL> alter system set enable_ddl_logging=true sid='*' scope=spfile;

System altered.

SQL> alter system set parallel_max_servers=64 sid='*' scope=spfile;

System altered.

SQL> alter system set sec_case_sensitive_logon=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_b_tree_bitmap_plans"=false sid='*' scope=spfile;

System altered.

SQL> alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED;

Profile altered.

SQL> alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;

Profile altered.

SQL> alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

Profile altered.

SQL> exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_CONFIG_JOB' );

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_STATS_CONFIG_JOB' );

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> alter system set sga_max_size=180G sid='*' scope=spfile;

System altered.

SQL> alter system set sga_target=180G sid='*' scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=20G sid='*' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.9241E+11 bytes
Fixed Size 2264256 bytes
Variable Size 2.0938E+10 bytes
Database Buffers 1.7126E+11 bytes
Redo Buffers 208637952 bytes
Database mounted.
Database opened.
SQL>

下面介绍如下几种用于压力测试的手段，实际操作一下，希望能帮助到各位小伙伴。

  • DBMS_RESOURCE_MANAGER.CALIBRATE_IO
  • Orion
  • Fio
  • Swingbench

DBMS_RESOURCE_MANAGER.CALIBRATE_IO

在Oracle 11g中dbms_resource_manager PL/SQL包提供了calibrate_io过程可以用来对Oracle数据库的IO子系统进行IO能力测试。

timed_statistics参数为true，必须打开异步IO，设置filesystemio_options参数为setall。由于我们本次是ASM DISK本身是异步IO。文件系统的话设置如下：

SQL> show parameter timed_statistics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL> col name for a60
SQL> select d.name,i.asynch_io from v$datafile d,v$iostat_file i where d.file#=i.file_no and i.filetype_name='Data File';

NAME ASYNCH_IO
------------------------------------------------------------ ---------
+DATA/cmsbj/datafile/system.262.1172775281 ASYNC_ON
+DATA/cmsbj/datafile/sysaux.263.1172775283 ASYNC_ON
+DATA/cmsbj/datafile/undotbs1.264.1172775283 ASYNC_ON
+DATA/cmsbj/datafile/undotbs2.266.1172775287 ASYNC_ON
+DATA/cmsbj/datafile/users.267.1172775289 ASYNC_ON

SQL> show parameter filesystemio_options

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> alter system set filesystemio_options=setall sid='*' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.9241E+11 bytes
Fixed Size 2264256 bytes
Variable Size 2.0938E+10 bytes
Database Buffers 1.7126E+11 bytes
Redo Buffers 208637952 bytes
Database mounted.
Database opened.
SQL>

执行DBMS_RESOURCE_MANAGER.CALIBRATE_IO

SQL> SET SERVEROUTPUT ON
DECLARE
SQL> 2 l_latency PLS_INTEGER;
3 l_iops PLS_INTEGER;
4 l_mbps PLS_INTEGER;
5 BEGIN
6 DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 2,
7 max_latency => 15,
8 max_iops => l_iops,
9 max_mbps => l_mbps,
10 actual_latency => l_latency);
11
12 DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops);
13 DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps);
14 DBMS_OUTPUT.put_line('Latency = ' || l_latency);
15 END;
16 /
Max IOPS = 199455
Max MBPS = 1837
Latency = 0

PL/SQL procedure successfully completed.

SQL> SELECT max_iops
, max_mbps
, max_pmbps
, latency
, num_physical_disks
FROM dba_rsrc_io_calibrate; 2 3 4 5 6

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ------------------
199455 1837 834 0 2

SQL>

每秒IO请求次数（max_iops）为199455次，
每秒最大可读取（max_mbps）为1837MB,
单个进程每秒最大可读取（max_pmbps）为834MB，
读取数据块请求出现有0次延迟

Orion

Orion是Oracle提供的IO性能测试工具，11g开始该工具被集成到GI和Database软件中，这次把asmdisk中一块磁盘剔除，来做测试。

[grid@cmsdb1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 2 10:58:56 2024

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set linesize 200
SQL> col name for a40
SQL> col path for a40
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;

GROUP_NUMBER NAME NAME PATH STATE
------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- -----------
1 ARCH ARCH_0001 /dev/oracleasm/disks/ARCH02 MOUNTED
1 ARCH ARCH_0002 /dev/oracleasm/disks/ARCH03 MOUNTED
1 ARCH ARCH_0000 /dev/oracleasm/disks/ARCH01 MOUNTED
2 DATA DATA_0002 /dev/oracleasm/disks/DATA03 MOUNTED
2 DATA DATA_0001 /dev/oracleasm/disks/DATA02 MOUNTED
2 DATA DATA_0000 /dev/oracleasm/disks/DATA01 MOUNTED
3 OCR OCR_0000 /dev/oracleasm/disks/OCR01 MOUNTED
3 OCR OCR_0001 /dev/oracleasm/disks/OCR02 MOUNTED
3 OCR OCR_0002 /dev/oracleasm/disks/OCR03 MOUNTED

9 rows selected.

SQL> alter diskgroup arch drop disk arch_0002;

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;

GROUP_NUMBER NAME NAME PATH STATE
------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- -----------
1 ARCH ARCH_0001 /dev/oracleasm/disks/ARCH02 MOUNTED
1 ARCH ARCH_0002 MOUNTED
1 ARCH ARCH_0000 /dev/oracleasm/disks/ARCH01 MOUNTED
2 DATA DATA_0002 /dev/oracleasm/disks/DATA03 MOUNTED
2 DATA DATA_0001 /dev/oracleasm/disks/DATA02 MOUNTED
2 DATA DATA_0000 /dev/oracleasm/disks/DATA01 MOUNTED
3 OCR OCR_0000 /dev/oracleasm/disks/OCR01 MOUNTED
3 OCR OCR_0001 /dev/oracleasm/disks/OCR02 MOUNTED
3 OCR OCR_0002 /dev/oracleasm/disks/OCR03 MOUNTED

9 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[grid@cmsdb1 ~]$ ll -h /u01/app/11.2.0/grid/bin/orion
-rwxr-x--x 1 grid oinstall 20M Jun 20 14:08 /u01/app/11.2.0/grid/bin/orion
[grid@cmsdb1 ~]$ ll -h /dev/oracleasm/disks/ARCH03
brw-rw---- 1 grid asmadmin 8, 81 Jul 2 10:59 /dev/oracleasm/disks/ARCH03
[grid@cmsdb1 ~]$

用orion跑一个oltp的测试

[grid@cmsdb1 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
[grid@cmsdb1 ~]$ echo "/dev/oracleasm/disks/ARCH03" > orion.lun
[grid@cmsdb1 ~]$ ll
total 4
drwxr-xr-x 3 grid oinstall 18 Jun 20 11:21 oradiag_grid
-rw-r--r-- 1 grid oinstall 28 Jul 3 13:50 orion.lun
[grid@cmsdb1 ~]$ /u01/app/11.2.0/grid/bin/orion -run oltp
ORION: ORacle IO Numbers -- Version 11.2.0.4.0
orion_20240703_1351
Calibration will take approximately 22 minutes.
Using a large value for -cache_size may take longer.

Maximum Small IOPS=1433 @ Small=20 and Large=0
Small Read Latency: avg=13954 us, min=273 us, max=180063 us, std dev=12012 us @ Small=20 and Large=0

Minimum Small Latency=10544 usecs @ Small=2 and Large=0
Small Read Latency: avg=10544 us, min=303 us, max=139400 us, std dev=7202 us @ Small=2 and Large=0
Small Read / Write Latency Histogram @ Small=2 and Large=0
Latency: # of IOs (read) # of IOs (write)
0 - 1 us: 0 0
2 - 4 us: 0 0
4 - 8 us: 0 0
8 - 16 us: 0 0
16 - 32 us: 0 0
32 - 64 us: 0 0
64 - 128 us: 0 0
128 - 256 us: 0 0
256 - 512 us: 320 0
512 - 1024 us: 326 0
1024 - 2048 us: 113 0
2048 - 4096 us: 565 0
4096 - 8192 us: 3494 0
8192 - 16384 us: 4662 0
16384 - 32768 us: 1719 0
32768 - 65536 us: 173 0
65536 - 131072 us: 0 0
131072 - 262144 us: 2 0
262144 - 524288 us: 0 0
524288 - 1048576 us: 0 0
1048576 - 2097152 us: 0 0
2097152 - 4194304 us: 0 0
4194304 - 8388608 us: 0 0
8388608 - 16777216 us: 0 0
16777216 - 33554432 us: 0 0
33554432 - 67108864 us: 0 0
67108864 - 134217728 us: 0 0
134217728 - 268435456 us: 0 0
[grid@cmsdb1 ~]$ ls -lh
total 60K
drwxr-xr-x 3 grid oinstall 18 Jun 20 11:21 oradiag_grid
-rw-r--r-- 1 grid oinstall 19K Jul 3 14:11 orion_20240703_1351_hist.txt
-rw-r--r-- 1 grid oinstall 742 Jul 3 14:11 orion_20240703_1351_iops.csv
-rw-r--r-- 1 grid oinstall 800 Jul 3 14:11 orion_20240703_1351_lat.csv
-rw-r--r-- 1 grid oinstall 570 Jul 3 14:11 orion_20240703_1351_mbps.csv
-rw-r--r-- 1 grid oinstall 1.9K Jul 3 14:11 orion_20240703_1351_summary.txt
-rw-r--r-- 1 grid oinstall 19K Jul 3 14:11 orion_20240703_1351_trace.txt
-rw-r--r-- 1 grid oinstall 28 Jul 3 13:50 orion.lun
[grid@cmsdb1 ~]$

最大IOPS为1433

最小IO延迟为10544

Fio

下载源码安装包fio-3.20.tar.gz，将下载的安装包上传到服务器上。解压包，编译安装

[root@cmsdb1 ~]# tar -zxf fio-3.20.tar.gz
[root@cmsdb1 ~]# cd fio-3.20/
[root@cmsdb1 fio-3.20]# ./configure
Operating system Linux
CPU x86_64
Big endian no
Compiler gcc
Cross compile no

Static build no
Wordsize 64
zlib yes
Linux AIO support yes
Linux AIO over io_uring no
POSIX AIO support yes
POSIX AIO support needs -lrt yes
POSIX AIO fsync yes
POSIX pshared support yes
pthread_condattr_setclock() yes
pthread_sigmask() yes
Solaris AIO support no
__sync_fetch_and_add yes
__sync_synchronize yes
__sync_val_compare_and_swap yes
libverbs no
rdmacm no
asprintf() yes
vasprintf() yes
Linux fallocate yes
POSIX fadvise yes
POSIX fallocate yes
sched_setaffinity(3 arg) yes
sched_setaffinity(2 arg) no
clock_gettime yes
CLOCK_MONOTONIC yes
CLOCK_MONOTONIC_RAW yes
CLOCK_MONOTONIC_PRECISE no
clockid_t yes
gettimeofday yes
fdatasync yes
pipe() yes
pipe2() yes
pread() yes
sync_file_range yes
EXT4 move extent yes
Linux splice(2) yes
GUASI no
libnuma no
strsep yes
strcasestr yes
strlcat no
getopt_long_only() yes
inet_aton yes
socklen_t yes
__thread yes
RUSAGE_THREAD yes
SCHED_IDLE yes
TCP_NODELAY yes
Net engine window_size yes
TCP_MAXSEG yes
RLIMIT_MEMLOCK yes
pwritev/preadv yes
pwritev2/preadv2 no
IPv6 helpers yes
http engine no
Rados engine no
Rados Block Device engine no
setvbuf yes
Gluster API engine no
s390_z196_facilities no
HDFS engine no
MTD yes
libpmem no
libpmemblk no
PMDK pmemblk engine no
PMDK dev-dax engine no
PMDK libpmem engine no
DDN's Infinite Memory Engine no
iscsi engine no
NBD engine no
lex/yacc for arithmetic no
getmntent yes
getmntinfo no
Static Assert yes
bool yes
strndup yes
Valgrind headers no
Zoned block device support no
libzbc engine no
march_armv8_a_crc_crypto no
cuda no
Build march=native yes
CUnit no
__kernel_rwf_t no
-Wimplicit-fallthrough no
MADV_HUGEPAGE yes
gettid no
statx(2)/libc no
statx(2)/syscall no
TCMalloc support no
[root@cmsdb1 fio-3.20]# make
FIO_VERSION = fio-3.20
CC crc/crc16.o
CC crc/crc32.o
CC crc/crc32c-arm64.o
CC crc/crc32c-intel.o
CC crc/crc32c.o
CC crc/crc64.o
CC crc/crc7.o
CC crc/fnv.o
CC crc/md5.o
CC crc/murmur3.o
CC crc/sha1.o
CC crc/sha256.o
CC crc/sha3.o
CC crc/sha512.o
CC crc/test.o
CC crc/xxhash.o
CC lib/axmap.o
CC lib/bloom.o
CC lib/flist_sort.o
CC lib/gauss.o
CC lib/getrusage.o
CC lib/hweight.o
CC lib/ieee754.o
CC lib/lfsr.o
CC lib/memalign.o
CC lib/memcpy.o
CC lib/mountcheck.o
CC lib/num2str.o
CC lib/output_buffer.o
CC lib/pattern.o
CC lib/prio_tree.o
CC lib/rand.o
CC lib/rbtree.o
CC lib/strntol.o
CC lib/zipf.o
CC gettime.o
CC ioengines.o
CC init.o
CC stat.o
CC log.o
CC time.o
CC filesetup.o
CC eta.o
CC verify.o
CC memory.o
CC io_u.o
CC parse.o
CC fio_sem.o
CC rwlock.o
CC pshared.o
CC options.o
CC smalloc.o
CC filehash.o
CC profile.o
CC debug.o
CC engines/cpu.o
CC engines/mmap.o
CC engines/sync.o
CC engines/null.o
CC engines/net.o
CC engines/ftruncate.o
CC engines/filecreate.o
CC engines/filestat.o
CC server.o
CC client.o
CC iolog.o
CC backend.o
CC libfio.o
CC flow.o
CC cconv.o
CC gettime-thread.o
CC helpers.o
CC json.o
CC idletime.o
CC td_error.o
CC profiles/tiobench.o
CC profiles/act.o
CC io_u_queue.o
CC filelock.o
CC workqueue.o
CC rate-submit.o
CC optgroup.o
CC helper_thread.o
CC steadystate.o
CC zone-dist.o
CC zbd.o
CC engines/libaio.o
CC engines/posixaio.o
CC engines/falloc.o
CC engines/e4defrag.o
CC engines/splice.o
CC oslib/asprintf.o
CC oslib/strlcat.o
CC oslib/statx.o
CC engines/mtd.o
CC oslib/libmtd.o
CC oslib/libmtd_legacy.o
CC diskutil.o
CC fifo.o
CC blktrace.o
CC cgroup.o
CC trim.o
CC engines/sg.o
CC oslib/linux-dev-lookup.o
CC engines/io_uring.o
CC fio.o
LINK fio
CC t/log.o
CC t/genzipf.o
CC oslib/strcasestr.o
CC oslib/strndup.o
LINK t/fio-genzipf
CC t/btrace2fio.o
LINK t/fio-btrace2fio
CC t/dedupe.o
CC t/debug.o
CC t/arch.o
LINK t/fio-dedupe
CC t/verify-state.o
LINK t/fio-verify-state
CC t/stest.o
LINK t/stest
CC t/ieee754.o
LINK t/ieee754
CC t/axmap.o
LINK t/axmap
CC t/lfsr-test.o
LINK t/lfsr-test
CC t/gen-rand.o
LINK t/gen-rand
CC t/memlock.o
LINK t/memlock
CC t/read-to-pipe-async.o
LINK t/read-to-pipe-async
CC t/io_uring.o
LINK t/io_uring
[root@cmsdb1 fio-3.20]# make install
install -m 755 -d /usr/local/bin
install fio t/fio-genzipf t/fio-btrace2fio t/fio-dedupe t/fio-verify-state ./tools/fio_generate_plots ./tools/plot/fio2gnuplot ./tools/genfio ./tools/fiologparser.py ./tools/hist/fiologparser_hist.py ./tools/hist/fio-histo-log-pctiles.py ./tools/fio_jsonplus_clat2csv /usr/local/bin
install -m 755 -d /usr/local/man/man1
install -m 644 ./fio.1 /usr/local/man/man1
install -m 644 ./tools/fio_generate_plots.1 /usr/local/man/man1
install -m 644 ./tools/plot/fio2gnuplot.1 /usr/local/man/man1
install -m 644 ./tools/hist/fiologparser_hist.py.1 /usr/local/man/man1
install -m 755 -d /usr/local/share/fio
install -m 644 ./tools/plot/*gpm /usr/local/share/fio/
[root@cmsdb1 fio-3.20]# fio -v
fio-3.20
[root@cmsdb1 fio-3.20]#

32个进程顺序写，随机写，顺序读，随机读30G的文件，写是每秒330MB，IOPS是43200，读是每秒280MB，IOPS是36100

[root@cmsdb1 fio-3.20]# fio -filename=/dev/sdf1 -direct=1 -iodepth=1 -rw=write -ioengine=psync -bs=8k -size=30G -numjobs=32 -runtime=180 -group_reporting -name=write_test
write_test: (g=0): rw=write, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1
...
fio-3.20
Starting 32 processes
Jobs: 32 (f=32): [W(32)][100.0%][w=337MiB/s][w=43.2k IOPS][eta 00m:00s]
write_test: (groupid=0, jobs=32): err= 0: pid=25394: Thu Jul 4 10:45:44 2024
write: IOPS=42.5k, BW=332MiB/s (348MB/s)(58.3GiB/180002msec); 0 zone resets
clat (usec): min=314, max=48593, avg=752.48, stdev=227.50
lat (usec): min=314, max=48593, avg=752.67, stdev=227.50
clat percentiles (usec):
| 1.00th=[ 502], 5.00th=[ 562], 10.00th=[ 594], 20.00th=[ 635],
| 30.00th=[ 676], 40.00th=[ 701], 50.00th=[ 734], 60.00th=[ 766],
| 70.00th=[ 807], 80.00th=[ 848], 90.00th=[ 914], 95.00th=[ 979],
| 99.00th=[ 1139], 99.50th=[ 1287], 99.90th=[ 2311], 99.95th=[ 2769],
| 99.99th=[ 6456]
bw ( KiB/s): min=275712, max=389200, per=100.00%, avg=340264.40, stdev=478.45, samples=11488
iops : min=34464, max=48650, avg=42532.94, stdev=59.81, samples=11488
lat (usec) : 500=0.91%, 750=54.22%, 1000=41.09%
lat (msec) : 2=3.63%, 4=0.14%, 10=0.01%, 20=0.01%, 50=0.01%
cpu : usr=0.26%, sys=0.66%, ctx=7660881, majf=0, minf=483
IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
issued rwts: total=0,7644463,0,0 short=0,0,0,0 dropped=0,0,0,0
latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
WRITE: bw=332MiB/s (348MB/s), 332MiB/s-332MiB/s (348MB/s-348MB/s), io=58.3GiB (62.6GB), run=180002-180002msec

Disk stats (read/write):
sdf: ios=42/7393654, merge=0/212984, ticks=40/5485656, in_queue=5485697, util=100.00%
[root@cmsdb1 fio-3.20]# fio -filename=/dev/sdf1 -direct=1 -iodepth=1 -rw=randwrite -ioengine=psync -bs=8k -size=30G -numjobs=32 -runtime=180 -group_reporting -name=write_test
write_test: (g=0): rw=randwrite, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1
...
fio-3.20
Starting 32 processes
Jobs: 32 (f=32): [w(32)][100.0%][w=324MiB/s][w=41.4k IOPS][eta 00m:00s]
write_test: (groupid=0, jobs=32): err= 0: pid=30019: Thu Jul 4 10:50:14 2024
write: IOPS=41.9k, BW=327MiB/s (343MB/s)(57.5GiB/180002msec); 0 zone resets
clat (usec): min=321, max=34429, avg=763.09, stdev=212.88
lat (usec): min=321, max=34429, avg=763.27, stdev=212.88
clat percentiles (usec):
| 1.00th=[ 506], 5.00th=[ 562], 10.00th=[ 594], 20.00th=[ 644],
| 30.00th=[ 676], 40.00th=[ 709], 50.00th=[ 742], 60.00th=[ 783],
| 70.00th=[ 816], 80.00th=[ 857], 90.00th=[ 938], 95.00th=[ 996],
| 99.00th=[ 1205], 99.50th=[ 1385], 99.90th=[ 2474], 99.95th=[ 3064],
| 99.99th=[ 7439]
bw ( KiB/s): min=222256, max=387264, per=100.00%, avg=335471.82, stdev=471.09, samples=11488
iops : min=27782, max=48408, avg=41933.86, stdev=58.89, samples=11488
lat (usec) : 500=0.87%, 750=51.76%, 1000=42.41%
lat (msec) : 2=4.77%, 4=0.16%, 10=0.03%, 20=0.01%, 50=0.01%
cpu : usr=0.28%, sys=0.65%, ctx=7552454, majf=0, minf=375
IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
issued rwts: total=0,7536796,0,0 short=0,0,0,0 dropped=0,0,0,0
latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
WRITE: bw=327MiB/s (343MB/s), 327MiB/s-327MiB/s (343MB/s-343MB/s), io=57.5GiB (61.7GB), run=180002-180002msec

Disk stats (read/write):
sdf: ios=63/7531905, merge=0/3, ticks=34/5673807, in_queue=5673841, util=100.00%
[root@cmsdb1 fio-3.20]# fio -filename=/dev/sdf1 -direct=1 -iodepth=1 -rw=read -ioengine=psync -bs=8k -size=30G -numjobs=32 -runtime=180 -group_reporting -name=read_test
read_test: (g=0): rw=read, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1
...
fio-3.20
Starting 32 processes
Jobs: 32 (f=32): [R(32)][100.0%][r=37.1MiB/s][r=4744 IOPS][eta 00m:00s]
read_test: (groupid=0, jobs=32): err= 0: pid=2092: Thu Jul 4 10:54:09 2024
read: IOPS=34.1k, BW=266MiB/s (279MB/s)(46.8GiB/180028msec)
clat (usec): min=181, max=226895, avg=937.99, stdev=2754.30
lat (usec): min=181, max=226895, avg=938.09, stdev=2754.31
clat percentiles (usec):
| 1.00th=[ 330], 5.00th=[ 379], 10.00th=[ 400], 20.00th=[ 433],
| 30.00th=[ 457], 40.00th=[ 482], 50.00th=[ 506], 60.00th=[ 537],
| 70.00th=[ 578], 80.00th=[ 652], 90.00th=[ 1057], 95.00th=[ 1844],
| 99.00th=[13960], 99.50th=[21365], 99.90th=[32375], 99.95th=[36963],
| 99.99th=[48497]
bw ( KiB/s): min=13520, max=578720, per=100.00%, avg=273404.05, stdev=6481.03, samples=11488
iops : min= 1690, max=72340, avg=34175.44, stdev=810.13, samples=11488
lat (usec) : 250=0.07%, 500=47.71%, 750=37.11%, 1000=4.41%
lat (msec) : 2=6.10%, 4=2.17%, 10=1.18%, 20=0.66%, 50=0.58%
lat (msec) : 100=0.01%, 250=0.01%
cpu : usr=0.20%, sys=0.49%, ctx=6152183, majf=0, minf=570
IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
issued rwts: total=6135476,0,0,0 short=0,0,0,0 dropped=0,0,0,0
latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
READ: bw=266MiB/s (279MB/s), 266MiB/s-266MiB/s (279MB/s-279MB/s), io=46.8GiB (50.3GB), run=180028-180028msec

Disk stats (read/write):
sdf: ios=5568396/0, merge=484381/0, ticks=5294790/0, in_queue=5294791, util=100.00%
[root@cmsdb1 fio-3.20]# fio -filename=/dev/sdf1 -direct=1 -iodepth=1 -rw=randread -ioengine=psync -bs=8k -size=30G -numjobs=32 -runtime=180 -group_reporting -name=read_test
read_test: (g=0): rw=randread, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1
...
fio-3.20
Starting 32 processes
Jobs: 32 (f=32): [r(32)][100.0%][r=404MiB/s][r=51.7k IOPS][eta 00m:00s]
read_test: (groupid=0, jobs=32): err= 0: pid=6230: Thu Jul 4 10:57:46 2024
read: IOPS=36.1k, BW=282MiB/s (296MB/s)(49.6GiB/180001msec)
clat (usec): min=258, max=222806, avg=884.38, stdev=2107.72
lat (usec): min=258, max=222806, avg=884.47, stdev=2107.72
clat percentiles (usec):
| 1.00th=[ 367], 5.00th=[ 416], 10.00th=[ 449], 20.00th=[ 502],
| 30.00th=[ 545], 40.00th=[ 586], 50.00th=[ 635], 60.00th=[ 701],
| 70.00th=[ 783], 80.00th=[ 898], 90.00th=[ 1106], 95.00th=[ 1352],
| 99.00th=[ 4752], 99.50th=[13304], 99.90th=[26084], 99.95th=[28705],
| 99.99th=[35914]
bw ( KiB/s): min=24752, max=467744, per=100.00%, avg=289413.51, stdev=4302.45, samples=11488
iops : min= 3094, max=58468, avg=36176.57, stdev=537.80, samples=11488
lat (usec) : 500=19.91%, 750=47.52%, 1000=18.41%
lat (msec) : 2=11.01%, 4=1.94%, 10=0.64%, 20=0.29%, 50=0.28%
lat (msec) : 100=0.01%, 250=0.01%
cpu : usr=0.23%, sys=0.52%, ctx=6531551, majf=0, minf=462
IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
issued rwts: total=6505381,0,0,0 short=0,0,0,0 dropped=0,0,0,0
latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
READ: bw=282MiB/s (296MB/s), 282MiB/s-282MiB/s (296MB/s-296MB/s), io=49.6GiB (53.3GB), run=180001-180001msec

Disk stats (read/write):
sdf: ios=6500222/0, merge=4/0, ticks=5687775/0, in_queue=5687776, util=100.00%
[root@cmsdb1 fio-3.20]#

Swingbench

Swingbench是一个免费的负载生成器和基准测试工具，可以生成模拟数据进行测试。

下载安装包

下载java安装包jdk-17_linux-x64_bin.tar.gz，下载安装包swingbench25052023.zip。将下载的安装包上传到服务器上。

Java环境配置

[root@cmsdb1 ~]# tar -zxf jdk-17_linux-x64_bin.tar.gz
[root@cmsdb1 ~]# mv jdk-17.0.11/ /usr/local/java
[root@cmsdb1 ~]# cat >> /etc/profile
>
> export JAVA_HOME=/usr/local/java
> export CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
> export PATH=$JAVA_HOME/bin:$PATH
>
> EOF
[root@cmsdb1 ~]# tail -10 /etc/profile
ulimit -u 131072 -n 1048576
fi
fi

export JAVA_HOME=/usr/local/java
export CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH=$JAVA_HOME/bin:$PATH

[root@cmsdb1 ~]# source /etc/profile
[root@cmsdb1 ~]# java -version
java version "17.0.11" 2024-04-16 LTS
Java(TM) SE Runtime Environment (build 17.0.11+7-LTS-207)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.11+7-LTS-207, mixed mode, sharing)
[root@cmsdb1 ~]#

数据库准备工作

扩建表空间，临时表空间，undo表空间，扩online redo，为生成测试数据做准备

[oracle@cmsdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 3 19:51:48 2024

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter tablespace USERS add datafile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> /

Tablespace altered.

SQL> /

Tablespace altered.

SQL> alter database add logfile thread 1 group 5 ('+DATA') size 1024M;

Database altered.

SQL> alter database add logfile thread 1 group 6 ('+DATA') size 1024M;

Database altered.

SQL> alter database add logfile thread 1 group 7 ('+DATA') size 1024M;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+DATA') size 1024M;

Database altered.

SQL> alter database add logfile thread 2 group 9 ('+DATA') size 1024M;

Database altered.

SQL> alter database add logfile thread 2 group 10 ('+DATA') size 1024M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance cmsbj1 (thread 1) - cannot drop
ORA-00312: online log 1 thread 1:
'+DATA/cmsbj/onlinelog/group_1.258.1172775279'
ORA-00312: online log 1 thread 1:
'+DATA/cmsbj/onlinelog/group_1.259.1172775279'

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance cmsbj2 (thread 2) - cannot drop
ORA-00312: online log 3 thread 2:
'+DATA/cmsbj/onlinelog/group_3.268.1172776481'
ORA-00312: online log 3 thread 2:
'+DATA/cmsbj/onlinelog/group_3.269.1172776481'

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter tablespace UNDOTBS1 add datafile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> alter tablespace UNDOTBS2 add datafile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> alter tablespace TEMP add tempfile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> alter tablespace USERS add datafile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> alter tablespace TEMP add tempfile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL> alter tablespace USERS add datafile '+DATA' size 30g autoextend off;

Tablespace altered.

SQL>

生成数据

解压二进制包，进入swingbench的bin目录，用xshell图形化启动oewizard

[oracle@cmsdb1 ~]$ unzip swingbench25052023.zip
Archive: swingbench25052023.zip
creating: swingbench/
creating: swingbench/launcher/
省略。。。
inflating: swingbench/sql/saleshistory/shdg_indexes_none.sql
inflating: swingbench/sql/saleshistory/shdg_mergepartitions.sql
[oracle@cmsdb1 ~]$ cd swingbench/bin/
[oracle@cmsdb1 bin]$ ls -l
total 60
-rwxr-xr-x 1 oracle oinstall 141 Jul 19 2017 ccwizard
-rwxr-xr-x 1 oracle oinstall 1134 Apr 5 2023 charbench
-rwxr-xr-x 1 oracle oinstall 195 May 6 2022 coordinator
drwxr-xr-x 2 oracle oinstall 4096 Feb 14 2023 data
-rwxr-xr-x 1 oracle oinstall 143 Jul 19 2017 jsonwizard
-rwxr-xr-x 1 oracle oinstall 110 Jul 19 2017 minibench
-rwxr-xr-x 1 oracle oinstall 150 Jan 15 2023 moviewizard
-rwxr-xr-x 1 oracle oinstall 141 Jul 19 2017 oewizard
-rwxr-xr-x 1 oracle oinstall 114 Jul 19 2017 results2pdf
-rwxr-xr-x 1 oracle oinstall 107 Jul 19 2017 sbutil
-rwxr-xr-x 1 oracle oinstall 141 Jul 19 2017 shwizard
-rwxr-xr-x 1 oracle oinstall 121 Nov 3 2016 sqlbuilder
-rwxr-xr-x 1 oracle oinstall 112 Jul 19 2017 swingbench
-rwxr-xr-x 1 oracle oinstall 150 Jul 19 2017 tpcdswizard
-rwxr-xr-x 1 oracle oinstall 147 Mar 14 2022 tpchwizard
[oracle@cmsdb1 bin]$ ./oewizard

下一步

Oracle数据库压力测试方法实战-2

选择Version 2.0

Oracle数据库压力测试方法实战-3

创建订单用户数据

Oracle数据库压力测试方法实战-4

连接填写scanip和db_unique_name，输入密码

Oracle数据库压力测试方法实战-5

修改表空间为USERS

Oracle数据库压力测试方法实战-6

选择普通表空间

Oracle数据库压力测试方法实战-7

创建60G数据

Oracle数据库压力测试方法实战-8

选择16个并发

Oracle数据库压力测试方法实战-9

执行开始导入数据

Oracle数据库压力测试方法实战-10

Oracle数据库压力测试方法实战-11

直到出现如下窗口，代表已经完全导入

Oracle数据库压力测试方法实战-12

注意如果数据库版本是11g有一个ORDERENTRY包会失效，会影响到我们后面的压力测试，我们要把包体里的IF DBMS_DB_VERSION.VER_LE_18改成IF DBMS_DB_VERSION.VER_LE_11重新编译。

Oracle数据库压力测试方法实战-13

最后查看SOE所有对象有没有失效，生成表多少数据量

[oracle@cmsdb1 bin]$ ./sbutil -soe -cs //10.10.30.115/cmsbj -soe -u soe -p soe -val
The Order Entry Schema appears to be valid.
--------------------------------------------------
|Object Type | Valid| Invalid| Missing|
--------------------------------------------------
|Table | 10| 0| 0|
|Index | 26| 0| 0|
|Sequence | 5| 0| 0|
|View | 2| 0| 0|
|Code | 1| 0| 0|
--------------------------------------------------

[oracle@cmsdb1 bin]$ ./sbutil -soe -cs //10.10.30.115/cmsbj -soe -u soe -p soe -tables
Order Entry Schemas Tables
+----------------------+-------------+-----------+---------+-------------+--------------+
| Table Name | Rows | Blocks | Size | Compressed? | Partitioned? |
+----------------------+-------------+-----------+---------+-------------+--------------+
| ORDER_ITEMS | 427,062,417 | 4,014,803 | 31.6GB | Disabled | No |
| ORDERS | 85,787,400 | 1,555,384 | 12.8GB | Disabled | No |
| CUSTOMERS | 60,000,000 | 1,202,580 | 9.3GB | Disabled | No |
| ADDRESSES | 90,000,000 | 1,047,084 | 8.3GB | Disabled | No |
| CARD_DETAILS | 90,000,000 | 726,885 | 5.7GB | Disabled | No |
| LOGON | 142,979,040 | 564,228 | 4.3GB | Disabled | No |
| INVENTORIES | 902,249 | 22,337 | 176.0MB | Disabled | No |
| PRODUCT_DESCRIPTIONS | 1,000 | 35 | 320KB | Disabled | No |
| PRODUCT_INFORMATION | 1,000 | 28 | 256KB | Disabled | No |
| WAREHOUSES | 1,000 | 13 | 128KB | Disabled | No |
| ORDERENTRY_METADATA | 4 | 5 | 64KB | Disabled | No |
+----------------------+-------------+-----------+---------+-------------+--------------+
Total Space 72.2GB
[oracle@cmsdb1 bin]$

压力测试

用xshell图形化启动运行swingbench

[oracle@cmsdb1 bin]$ ./swingbench
Application : Swingbench
Author : Dominic Giles
Version : 2.7.0.1313

选择SOE_Server_Side_V2

Oracle数据库压力测试方法实战-14

设置scanip和db_unique_name，用户名和密码设置soe

Oracle数据库压力测试方法实战-15

设置500并发

Oracle数据库压力测试方法实战-16

默认事务不需要改变

Oracle数据库压力测试方法实战-17

输入主机IP地址用户名root和密码

Oracle数据库压力测试方法实战-18

点击开始，观察主机负载压力，CPU使用率再20%-30%

Oracle数据库压力测试方法实战-19

观察oratop情况

Oracle数据库压力测试方法实战-20

iostat观察，每秒IO请求3000左右，每秒读15MB，每秒写60MB，%iowait的值在5左右。

Oracle数据库压力测试方法实战-21

观察swingbench输出，相应，每秒表事务数5243，相应时间11毫秒

Oracle数据库压力测试方法实战-22

总结

目前深信服超融合硬件上安装Oracle 11.2.0.4 RAC。在500个并发情况下，CPU使用率在30%左右，可提供最大每秒写400MB，最大每秒读280MB，每秒表事务数5243。读性能比之前FCSAN环境的独立存储要差一些。

向阳逐梦
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

向阳逐梦

这个人很懒，什么都没有留下～

文章8411 评论0