Re: Curioser and Curiouser

From: Mladen Gogala <mgogala_at_allegientsystems.com>
Date: Wed, 12 Jan 2005 10:14:03 -0500
Message-ID: <41E53EBB.3070502@allegientsystems.com>

Comments inline

Niall Litchfield wrote:

>I'm - not for the first time bemused - I wonder if anyone can (after
>fighting past the screwed up formatting that blogger gave me) shed
>light on the results that I have on my site for running identical SQL
>on identical hardware (the same machine actually) under Windows XP sp2
>and Fedora Core 3 linux. (incidentally FC3 passed the certified os
>checks on install).
>
>

Yes. That probably is because FC3 has direct I/O and async I/O in the kernel, the two facilities that Oracle checks when checking prerequisites. Other then that, welcome to the Dark Side. Experience the power of the farce.

>briefly the test is
>
>create a table with 20k rows.
>insert into the table all the rows from itself 6 times
>
>tune based on the response time (I've used tkprof to generate the
>response time profile).
>
>My linux box seems to be stuck waiting on log_buffer space and whilst
>throwing memory at it has helped (I've gone as high as 8mb log buffer
>and to my surprise got some marginal improvement over the 1mb I've
>documented) it is still half the speed of the same hardware running
>windows. some mistake surely.
>
>

No, no mistake there. Async I/O and direct I/O are enabled by default on Win2k and not on
FC3. You have to get libaio, as shown below:

$ rpm -qa|grep libaio
libaio-0.3.102-1
libaio-devel-0.3.102-1

$
That is best achieved by executing "yum install libaio libaio-devel", as root, of course.

>My kernel parameters are
>
>kernel.shmall = 2097152
>kernel.shmmax = 2147483648
>kernel.shmmni = 4096
>kernel.sem = 250 32000 100 128
>fs.file-max = 65536
>net.ipv4.ip_local_port_range = 1024 65000
>
>
>
>because I followed John Smiley's article for the install
>
>(http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html)
>
>
>

John and Werner give you the default install, which is useless, as such. If you want a speed daemon, you should

a) Relink your executable with async option
b) Create your data files on a decent file system (like JFS)
c) Turn on direct I/O and async I/O by using filesystemio_options parameter

in init.ora (or spfile.ora)

-- 
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 09:18:17 CST