Jim C. Nasby wrote:
On Wed, Dec 14, 2005 at 08:28:56PM +1300, Mark Kirkwood wrote:
Another interesting thing to try is rebuilding the database ufs
filesystem(s) with 32K blocks and 4K frags (as opposed to 8K/1K or
16K/2K - can't recall the default on 4.x). I found this to give a factor
of 2 speedup on random disk access (specifically queries doing indexed
joins).
Even if you're doing a lot of random IO? I would think that random IO
would perform better if you use smaller (8K) blocks, since there's less
data being read in and then just thrown away that way.
Yeah, that's what I would have expected too! but the particular queries
I tested do a ton of random IO (correlation of 0.013 on the join column
for the big table). I did wonder if the gain has something to do with
the underlying RAID stripe size (64K or 256K in my case), as I have only
tested the 32K vs 8K/16K on RAIDed systems.
I guess for a system where the number of concurrent users give rise to
memory pressure, it will cause more thrashing of the file buffer cache,
much could be a net loss.
Still worth trying out I think, you will know soon enough if it is a win
or lose!
Note that I did *not* alter Postgres page/block size (BLCKSZ) from 8K,
so no dump/reload is required to test this out.
cheers
Mark
