The Linux kernel 2.6 introduced several new I/O options which a relevant for
database workloads. Here you see how these I/O options are supported from the
Oracle 10g database and how it performs using FCP/SCSI disks.
Initialization parameters used for Oracle I/O option measurements
Miscellaneous settings
Selecting the test scenario
I/O options
Relink Oracle for async I/O
These settings are commonly used for all test scenarios.
| Parameter |
|
Value |
Comment |
| DB_BUFFER_SIZE |
|
0.6 * memory size |
|
|
| DB_FILE_MULTIBLOCK_READ_COUNT |
|
0 |
# |
no read ahead! |
| AQ_TM_PROCESSES |
|
0 |
# |
0 or 1 has no performance impact |
| LOG_BUFFER |
|
54M |
# |
this reduces the throughput if too small, but larger values
do not result in any improvement;
workload dependent |
When the statspack reports 'log file sync' as one of the top 5
wait events then a significant performance improvement is to
- increase the log buffers
- put the redo log files to a separate storage unit, e.g. another rank or
another storage server
The following settings are defined accordingly to the scenario, in addition
to the parameters above:
|
Scenario
|
Description
|
|
plain fs
|
ext2 file system with:
DBWR_IO_SLAVES=20
DISK_ASYNCH_IO=FALSE
|
|
direct_io
|
ext2 file system with direct I/O:
DBWR_IO_SLAVES=20
DISK_ASYNCH_IO=FALSE
FILESYSTEMIO_OPTIONS=DIRECTIO
|
|
async
|
ext2 file system with async I/O:
DB_WRITER_PROCESSES=1
DISK_ASYNCH_IO=TRUE
FILESYSTEMIO_OPTIONS=ASYNCH
Note: for this option a relink of the oracle database
is required |
|
setall
|
ext2 file system with direct I/O and async I/O:
DB_WRITER_PROCESSES=1
DISK_ASYNCH_IO=TRUE
FILESYSTEMIO_OPTIONS=SETALL
Note: for this option a relink of the oracle database
is required |
|
setall ext3
|
ext3 file system with direct I/O and async I/O:
DB_WRITER_PROCESSES=1
DISK_ASYNCH_IO=TRUE
FILESYSTEMIO_OPTIONS=SETALL
Note: for this option a relink of the oracle database
is required |
|
raw
|
raw devices with no further I/O options:
DBWR_IO_SLAVES=20
DISK_ASYNCH_IO=FALSE
DB_WRITER_PROCESSES=1
|
|
raw-async
|
raw devices with async I/O:
DISK_ASYNCH_IO=TRUE
DB_WRITER_PROCESSES=1
|
Note: The option DBWR_IO_SLAVES must not be specified when async I/O is
used!
The use of async I/O improves the throughput in any case. When using a file
system the good result from async I/O can be further improved in combination
with direct I/O (setall).
The file system ext3 has the same performance as ext2 for this workload.
For disk I/O setup optimization, see Hints
& Tips - Recommendations -> Disk I/O.
Test environment
- IBM eServer zSeries 900 (2064-216)
- LPAR with 12 CPUs, 8GB memory
- Novell/SUSE SLES 9 + SP1 for IBM zSeries (64-bit), kernel 2.6.5-7.151
- Oracle 10g (10.1.0.3.0, 64-bit)
- Benchmark Oracle OLTP
- Database data Logical Volume (LV): 4 CHPIDs, 4 host adapters, FCP/SCSI,
ext2/etx3 filesystem or raw devices
For details on async I/O with oracle database server, see notes: 270213.1 and
225751.1 on metalink.oracle.com.
The description how to relink the Oracle database can be found at ftp.novell.com/partners/oracle/docs/10gR1_sles9_install.pdf
in chapter 7.
|
