Re: Slow count(*) again...

On 10/11/2010 10:02 PM, Scott Carey wrote:
> Did you tune the linux FS read-ahead first? You can get large gains by doing that if you are on ext3.
> blockdev --setra 2048<device>

Actually, I have blockdev --setra 32768

> would give you a 1MB read-ahead. Also, consider XFS and its built-in defragmentation. I have found that a longer lived postgres DB will get extreme
> file fragmentation over time and sequential scans end up mostly random. On-line file defrag helps tremendously.

I agree, but I am afraid that after the demise of SGI, XFS isn't being
developed. The company adopted the policy of using only the plain
vanilla Ext3, which is unfortunate, but I can't do much about it. There
is a lesson to be learned from the story of ReiserFS. I am aware of the
fact that Ext3 is rather basic, block oriented file system which doesn't
perform well when compared to HPFS, VxFS or JFS2 and has no notion of
extents, but I believe that I am stuck with it, until the advent of
Ext4. BTW, there is no defragmenter for Ext4, not even on Ubuntu, which
is rather bleeding edge distribution.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com