I try to benchmark the random read IOPS capacity on one of Exadata X3-2 db node with SLOB, Here comes some figure.
Disable prefecting and avoid multiple block read by adding below 3 init parameters.
_db_block_prefetch_limit 0 _db_block_prefetch_quota 0 _db_file_noncontig_mblock_rea 0
Block size is 8K, db_cache_siz is set to 128M(seems this the lowest db_cache_size I can set 18.104.22.168)
cpu_count 32 db_block_size 8192 db_cache_size 134217728
2. Segment size and sql
prepare 128 users, each have the table CF1 which size is around 8G(with scale=1000000 in slob.conf)
setup.sh IOPS 128
The loading of 8K block random random reads is triggered by below query.
SELECT COUNT(C2) FROM CF1 WHERE CUSTID > ( :B1 - :B2 ) AND (CUSTID < :B1 )
The Max IOPS is around 140K, when the concurrent process is 96. The avg wait time of ‘db file sequential read’ increase when the concurrent process beyond 64, and CPU utilization drops when the processes number beyond 96, seem the system is overloading and the performance degrades.
Update on 2013-Feb-09:
After below two change, I can gain 250K IOPS with 64 sessions on single db node(almost twice IOPS than before the change), when the cpu is 90%.
Change 1. Comment out the below parameters to enable db block prefect.
--*._db_block_prefetch_limit = 0 --*._db_block_prefetch_quota = 0 --*._db_file_noncontig_mblock_read_count = 0
Change 2. update the WORK_UNIT=16
--slob.conf UPDATE_PCT=0 RUN_TIME=300 WORK_LOOP=0 SCALE=1000000 WORK_UNIT=16 REDO_STRESS=HEAVY LOAD_PARALLEL_DEGREE=8 SHARED_DATA_MODULUS=0
The awr number of single block read and physical read, with 32/64/96/128 slob sessions.
[oracle@demo01db01 awr]$ grep "cell single block physical rea" * | sort -t_ -k1 -n 32_awr.txt:cell single block physical rea 9,257,339 3,778 0 39.3 User I/O 64_awr.txt:cell single block physical rea 15,161,265 8,301 1 43.2 User I/O 96_awr.txt:cell single block physical rea 19,802,040 13,751 1 47.6 User I/O 128_awr.txt:cell single block physical rea 17,269,328 15,964 1 41.4 User I/O [oracle@demo01db01 awr]$ grep "Physical reads:" * | sort -t_ -k1 -n 32_awr.txt: Physical reads: 197,068.2 59,670,688.0 64_awr.txt: Physical reads: 249,996.2 3,594,314.9 96_awr.txt: Physical reads: 253,909.9 3,669,155.5 128_awr.txt: Physical reads: 255,982.8 3,733,850.4