Re: Index Scans become Seq Scans after VACUUM ANALYSE

Got some numbers now... You'll notice the Random reads are *really*
slow. The reason for htis is the particular read sizes that are ebing
used are the absolute worst-case for my particular configuration. (wiht
a 32kb or 64kb block size I generally achieve much higher performance
even on random I/O) Sequential I/O is most likely being limited atleast
in part by the CPU power available...

Sequential tests:

2147483648 bytes read in 39.716158 seconds 54070780.16 bytes/sec
2147483648 bytes read in 37.836187 seconds 56757401.27 bytes/sec
2147483648 bytes read in 38.081452 seconds 56391853.13 bytes/sec
2147483648 bytes read in 38.122105 seconds 56331717.46 bytes/sec
2147483648 bytes read in 38.303999 seconds 56064215.33 bytes/sec

Total: 192.059901 seconds 279615967.4 (mumble)
Ave: 38.4119802 seconds 55923193.47 bytes/sec

Random tests:

2147483648 bytes read in 1744.002332 seconds 1231353.656 bytes/sec
2147483648 bytes read in 1744.797705 seconds 1230792.339 bytes/sec
2147483648 bytes read in 1741.577362 seconds 1233068.191 bytes/sec
2147483648 bytes read in 1741.497690 seconds 1233124.603 bytes/sec
2147483648 bytes read in 1739.773354 seconds 1234346.786 bytes/sec

Total: 8711.648443 seconds 6162685.575
Ave: 1742.329689 seconds 1232537.115 bytes/sec

So on this machine at that block I/O level (8kb block I believe it was)
I have a ~55MB/sec Sequential Read rate and ~12MB/sec Random Read rate.
Like I said though I'm fairly certain the random read rates were worst
case because of the particular block size in the configuration this
system uses. But I feel that the results are respectable and valid
nonetheless.

Note how the random reads kept getting better... The ICP and drive
caching firmware were starting to 'catch on' that this 2gb file was a
hot spot so were preferring to cache things a little longer and
pre-fetch in a different order than normal. I estimate that it would
have dropped as low as 1700 if allowed to keep going.

RAW output from my script...

mloftis(at)free:/mnt/rz01/ml01/rndtst$ sh PAGECOST2GB.sh
CREATING FILE
Thu Apr 18 09:11:55 PDT 2002
Creating test file 2gb.test of 2048 mb
176.23 real 22.75 user 34.72 sys
BEGINNING SEQUENTIAL TESTS
Thu Apr 18 09:14:51 PDT 2002
Sequential read test of 2gb.test
2147483648 bytes read in 39.716158 seconds
39.73 real 1.52 user 23.87 sys
Sequential read test of 2gb.test
2147483648 bytes read in 37.836187 seconds
37.83 real 1.44 user 23.68 sys
Sequential read test of 2gb.test
2147483648 bytes read in 38.081452 seconds
38.08 real 1.62 user 23.51 sys
Sequential read test of 2gb.test
2147483648 bytes read in 38.122105 seconds
38.12 real 1.63 user 23.50 sys
Sequential read test of 2gb.test
2147483648 bytes read in 38.303999 seconds
38.30 real 1.32 user 23.83 sys
Thu Apr 18 09:18:03 PDT 2002
BEGINNING RANDOM READ TESTS
Random read test of 2gb.test for 2048 mb
2147483648 bytes read in 1744.002332 seconds
1744.01 real 4.33 user 36.47 sys
Random read test of 2gb.test for 2048 mb
2147483648 bytes read in 1744.797705 seconds
1744.81 real 4.38 user 36.56 sys
Random read test of 2gb.test for 2048 mb
2147483648 bytes read in 1741.577362 seconds
1741.58 real 4.58 user 36.18 sys
Random read test of 2gb.test for 2048 mb
2147483648 bytes read in 1741.497690 seconds
1741.50 real 4.17 user 36.57 sys
Random read test of 2gb.test for 2048 mb
2147483648 bytes read in 1739.773354 seconds
1739.78 real 4.41 user 36.36 sys
TESTS COMPLETED
Thu Apr 18 11:43:15 PDT 2002

Michael Loftis wrote:

> Numbers being run on a BSD box now...
>
> FreeBSD 4.3-p27 512MB RAM 2xPiii600 Xeon ona 4 disk RAID 5 ARRAY on a
> dedicated ICP Vortex card. Sorry no single drives on this box, I have
> an outboard Silicon Gear Mercury on a motherboard based Adaptec
> controller I can test as well. I'll post when the tests on the Vortex
> are done. I'm using 2Gb files ATM, I'll look at the code and see if
> it can be made to work with large files. Atleast for FreeBSD the
> change will be mostly taking doing s/fseek/fseeko/g s/size_t/off_t/g
> or something similar. FreeBSD seems ot prefer teh Open Unix standard
> in this regard...
>
> This will make it usable for much larger test files.
>
> Tom Lane wrote:
>
>> Michael Loftis <mloftis(at)wgops(dot)com> writes:
>>
>>> Somethings wrong with the random numbers from the sun... re-run
>>> them, that first sample is insane.... Caching looks like it's
>>> affecctign your results alot...
>>>
>>
>> Yeah; it looks like the test case is not large enough to swamp out
>> caching effects on the Sun box. It is on the Linux box, evidently,
>> since the 10:1 ratio appears very repeatable.
>>
>> regards, tom lane
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org