Re: Index Scans become Seq Scans after VACUUM ANALYSE - Mailing list pgsql-hackers

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
randomread 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@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@wgops.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