> Have you optimized your queries to max ?
>
> Often one or two of the queries take most of resources and starve
> others.
I did log a good number of queries and analyze them, and 69% of the
queries issued are from one particular application, and they consume 78%
of the total "cost". The developper is looking into optimizations, but it
doesn't look like there's going to be any low-hanging fruit. It's simply
a complicated and frequently-used app.
> Could there be some unnecessary trashing between OS and PG caches ?
> How could this be detected ?
The machine generally has a minimum of a hundred megs free, unused
memory, so I'm not terribly worried about memory thrashing. I've
increased the various tuneable parameters (buffer blocks, sort mem, etc.)
to the point where performance increases stopped, then I doubled them all
for good measure. I've already decided that the next machine will have at
least 4 gigs of RAM, just because RAM's cheap, and having too much is a
Good Thing.
> Do you have some triggers on updates - I have occasionally found them to
> be real performance killers.
There are a few triggers, but not many - and the number of updates is
extremely low relative to the number of inserts.
> Yes, it's BAD if your business grows faster than Moores law ;-p
.. unfortunately, that's been the case. Each year we've done slightly
more than double the traffic of the previous year - and at the same time,
as we unify all of our various data sources, the new applications that we
develop tend to make greater and greater demands on the database server.
There is always the option of the "big iron", but your
cost-per-transaction shoots through the roof. Paying a 10x premium can
really hurt. : )
> How big is the dataset ? What kinds of queries ?
our ~postgres/data/base is currently 3.4 gigs.
> I could perhaps run some quick tests on quad Xeon 1.40GHz , 2GB before
> this box goes to production sometime early next week. It is a RedHat
> AS2.1 box with rh-postgresql-7.2.3-1_as21.
I'd appreciate that!
steve