Re: performance tuning

I have to apologize to the assembled crowd and congratulate the postgres
team at the same time. After several days of posting on lists (this one
primarily) and talking with people on IRC it finally occured to me to
get metrics on a per statement basis in my code (I hadn't thought to do
this because I thought the problem was elsewhere). Examining them showed
several missing indices and also some 'like' queries which didn't use the
index. Adding the indices and running code without the 'like' resulted in
a _huge_ performance improviement. Rather than the factor of 10 speed
decrease I had seen when my database went to 20k record tables and no
concurrency performance improvements, I saw a performance shoot back up.

To be fair (since I was using it as a point of reference before), I would
like to presents some stats.

On my pentium III 1 Ghz with a single IDE disk running both my
multi-threaded app and the database I am getting 300 transactions per
minute (50 queries, 17 inserts).

On a modified version of the app running on oracle were my app is running
on a seperate dual processor box and oracle is running on a four-way sun
box I get only 144 transactions per minute.

This problem really nagged me since I was convinced postgres was better
and really enjoyed its features and interface (Oracle can be such a
pain). I had just never used a large system and was concerned that it
didn't scale ... I'm glad to see that is not a problem.

Thanks,
Sheer

On Sat, 27 Oct 2001, Keary Suska wrote:

> If I had a guess I would say the problem isn't with postgres, if your system
> CPU usage is 50%. I haven't had a system go over 20%, even under extreme
> duress. Are you by chance using Linux with a 2.4 kernel? The swap mechanism
> in 2.4 was terrible (it may have been fixed by now). What is you memory and
> virtual memory performance like? Extensive swapping could be at least
> partially responsible.
>
> Keary Suska
> Esoteritech, Inc.
> "Leveraging Open Source for a better Internet"
>
> > From: Sheer El-Showk <sheer(at)saraf(dot)com>
> > Date: Sat, 27 Oct 2001 13:58:42 -0400 (EDT)
> > To: <pgsql-general(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
> > Subject: [GENERAL] performance tuning
> >
> > Does anyone have a good reference for Postgres performance tuning. The
> > first thing I'm looking for is a way to determine my bottle-neck. My
> > system is currently running with 5-6 processes in the runqueue and 100%
> > cpu utilization (50% in the kernel). I'm guessing I'm cpu-bound but I'd
> > like to find out why (I'm doing mostly inserts, but I have a lot of
> > foreign keys). I know postgres has a lot of pg_stat_* tables but I'd like
> > to learn how to use these and other performance analysis tools.
> >
> > Thanks,
> > Sheer
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>