Linux 2.6 Kernel I/O Schedulers for Oracle Data Warehousing: Part II

Here are some preliminary test results for a single disk query.

The disk is a Seagate Cheetah ST318404LC, of 18.37Gb and 10,000 rpm. Manufacturer’s read seek times are 5.2ms (average), 0.6ms (single track), 10ms (max full seek).

A single tablespace was created on the disk, with a single table create and populated as in this script.

create tablespace sandbox_one_disk
datafile '/opt/d1/sandbox__one_disk.dbf' size 4g
extent management local uniform size 256M
segment space management manual
/

create table t2
   (col1 varchar2(1000))
tablespace sandbox_one_disk
pctfree 99 pctused 0
noparallel
nologging
nocompress
nomonitoring
storage (minextents 8)
/
insert /*+ append */ into t2
select lpad(rownum,100)
from   dual
connect by level <= 250000
/

begin
   dbms_stats.gather_table_stats
      (ownname          => user,
       tabname          => 'T2',
       estimate_percent => 1,
       block_sample     => true,
       method_opt       => 'for all columns size 1');
end;
/

This gave a table size of just less than 2Gb to be queried as follows:

set timing on echo on heading off
select /*+ noparallel(t2) */ count(*) from t2;
select /*+ parallel(t2 2) */ count(*) from t2;
select /*+ parallel(t2 3) */ count(*) from t2;
select /*+ parallel(t2 4) */ count(*) from t2;
select /*+ parallel(t2 5) */ count(*) from t2;
select /*+ parallel(t2 6) */ count(*) from t2;
select /*+ parallel(t2 7) */ count(*) from t2;
select /*+ parallel(t2 8) */ count(*) from t2;
select /*+ parallel(t2 20) */ count(*) from t2;
select /*+ parallel(t2 40) */ count(*) from t2;
select /*+ parallel(t2 80) */ count(*) from t2;

The wall clock time for each query was noted and graphed.

Each test run was executed with the server rebooted and reconfigured to use a different i/o scheduler each time. The scheduler in use was verified using:

dmesg | grep scheduler

All four available schedulers were tested with the following results:

In the above results a parallelism of “1” represents nonparallel query without direct serial reads being used.

I shall pause here for comments … :)

Advertisements