In my previous post I described how I could not explain why I got better db file parallel read wait times in a test on Linux than I got running the same test on HP-UX. I have discovered that the Linux wait times were better because Linux cached the data in the filesystem cache and HP-UX did not.
Neither system used direct I/O for the tests so both could cache data in the filesystem cache. Evidently Linux does this faster than HP-UX. I figured this out by repeatedly running the query flushing the buffer cache before each run. Flushing the buffer cache prevented the table and index from being cached within the database. On Linux the query ran for the same amount of time for all 5 executions. On HP-UX it ran much faster after running it for the first time. Apparently Linux cached the table and index before the first run and HP-UX cached them after the first run.
Here is how I ran the query:
alter system flush buffer_cache; select /*+ index(test testi) */ sum(blocks) from test; alter system flush buffer_cache; select /*+ index(test testi) */ sum(blocks) from test; alter system flush buffer_cache; select /*+ index(test testi) */ sum(blocks) from test; alter system flush buffer_cache; select /*+ index(test testi) */ sum(blocks) from test; alter system flush buffer_cache; select /*+ index(test testi) */ sum(blocks) from test;
Here are the elapsed times for the query on Linux:
Elapsed: 00:00:09.16 Elapsed: 00:00:09.17 Elapsed: 00:00:09.28 Elapsed: 00:00:09.18 Elapsed: 00:00:09.20
Here is the same thing on HP-UX:
Elapsed: 00:01:03.27 Elapsed: 00:00:19.23 Elapsed: 00:00:19.28 Elapsed: 00:00:19.35 Elapsed: 00:00:19.43
It’s not surprising that the HP-UX times with the data cached are twice that of Linux. An earlier post found the processor that I am evaluating on Linux was about twice as fast as the one I’m using on HP-UX.
Just to double-check that the caching was really at the filesystem level I turned direct I/O on for the Linux system using this parameter:
alter system set filesystemio_options=DIRECTIO scope=spfile;
I ran the test again after bouncing the database to make the parameter take effect and the run times were comparable to the slow first run on HP-UX:
Elapsed: 00:01:12.03 Elapsed: 00:01:06.69 Elapsed: 00:01:12.98 Elapsed: 00:01:10.14 Elapsed: 00:01:07.21
So, it seems that without the filesystem cache this query takes about 1 minute to run on either system. With caching the query runs under 20 seconds on both systems.
In some ways I think that these results are not important. Who cares if Linux caches things on the first attempt and HP-UX on the second?
The lesson I get from this test is that HP-UX and Linux are different in subtle ways and that when we migrate a database from HP-UX to Linux we may see performance differences that we do not expect.
Here is a zip of my script and its logs: zip
– Bobby
