Re: Massive performance issues

At 04:25 PM 9/1/2005, Tom Lane wrote:
>Ron <rjpeace(at)earthlink(dot)net> writes:
> > ... Your target is to have each row take <= 512B.
>
>Ron, are you assuming that the varchar fields are blank-padded or
>something? I think it's highly unlikely that he's got more than a
>couple hundred bytes per row right now --- at least if the data is
>what it sounds like.

As it stands, each row will take 55B - 748B and each field is
variable in size up to the maximums given in the OP's schema. Since
pg uses an underlying OS FS, and not a native one, there will be
extra FS overhead no matter what we do, particularly to accommodate
such flexibility... The goal is to minimize overhead and maximize
regularity in layout. The recipe I know for HD IO speed is in
keeping the data small, regular, and as simple as possible.

Even better, if the table(s) can be made RAM resident, then searches,
even random ones, can be very fast. He wants a 1000x performance
improvement. Going from disk resident to RAM resident should help
greatly in attaining that goal.

In addition, by replacing as many variable sized text strings as
possible with ints, the actual compare functions he used as examples
should run faster as well.

>The upthread comment about strcoll() set off some alarm bells in my
>head. If the database wasn't initdb'd in C locale already, try
>making it so. Also, use a single-byte encoding if you can (LatinX
>is fine, Unicode not).

Good thoughts I hadn't had.

> > Upgrade pg to 8.0.3 and make sure you have enough RAM for your real
> > day to day load.
>
>Newer PG definitely better. Some attention to the configuration
>parameters might also be called for. I fear though that these
>things are probably just chipping at the margins ...

I don't expect 8.0.3 to be a major performance improvement. I do
expect it to be a major _maintenance_ improvement for both him and
those of us trying to help him ;-)

The performance difference between not having the working set of the
DB fit into RAM during ordinary operation vs having it be so (or
better, having the whole DB fit into RAM during ordinary operation)
has been considerably more effective than "chipping at the margins"
IME. Especially so if the HD IO subsystem is wimpy.

Ron Peacetree