Re: Performance Bottleneck

On Wed, Aug 04, 2004 at 03:49:11AM +0000, Martin Foster wrote:
> Also note that some of these scripts run for longer durations even if
> they are web based.    Some run as long as 30 minutes, making queries to
> the database from periods of wait from five seconds to twenty-five
> seconds.     Under high duress the timeouts should back out, based on
> the time needed for the query to respond, normally averaging 0.008 seconds.

I would start by EXPLAIN ANALYZE'ing those 30 minute queries.

> martin@io ~$ vmstat
>  procs      memory      page                    disks     faults      cpu
>  r b w     avm    fre  flt  re  pi  po  fr  sr ad4 ad6   in   sy  cs us sy id
>  0 0 0  498532 122848 3306   0   0   0 740   0   0   0  788    0 1675 16 21 63
>

vmstat without a "delay" argument (e.g. 'vmstat 1') gives you a
cumulative or average since boot. You'd probably get better
information by doing a real-time sampling of stats during normal and
heavy load.

> martin@io ~$ ps -uax
> USER       PID %CPU %MEM   VSZ  RSS  TT  STAT STARTED      TIME COMMAND
> postgres 32084  0.0  0.2 91616 3764  p0- R    Mon12PM   4:08.99 /usr/local/bin/postmaster -D /var/postgres (postgres)
> postgres 80333  0.0  2.1 94620 44372  ??  S     8:57PM   0:01.00 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80599  0.0  2.1 94652 44780  ??  S     8:59PM   0:00.97 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80616  0.0  2.4 94424 50396  ??  S     8:59PM   0:00.89 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80715  0.0  2.2 94444 46804  ??  S     9:00PM   0:00.68 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80788  0.0  2.1 94424 43944  ??  S     9:00PM   0:00.93 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80811  0.0  2.1 94424 43884  ??  S     9:00PM   0:00.94 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80902  0.0  2.1 94424 43380  ??  S     9:01PM   0:00.76 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 80949  0.0  2.2 94424 45248  ??  S     9:01PM   0:00.67 postmaster: ethereal ethereal 192.168.1.6 idle in
trans
> postgres 81020  0.0  2.1 94424 42924  ??  S     9:02PM   0:00.74 postmaster: ethereal ethereal 192.168.1.6 idle in
trans

All the connections in your email are idle. You may benefit from using
pgpool instead of Apache::DBI (I've never tried).

http://www.mail-archive.com/pgsql-announce@postgresql.org/msg00760.html