pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Adler <adler@pobox.com>
To: Martin Foster <martin@ethereal-realms.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Bottleneck
Date: Wed, 4 Aug 2004 11:36:24 -0400
Message-ID: <20040804153624.GA27551@pobox.com> (raw)
In-Reply-To: <X0ZPc.23543$yT2.23053@clgrps13>
References: <ktQPc.23297$yT2.16983@clgrps13>
	<410FDC53.5080509@bigfoot.com>
	<X0ZPc.23543$yT2.23053@clgrps13>

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




view thread (38+ messages)  latest in thread

Message-ID: <20040804153624.GA27551@pobox.com>
Permalink:  ../20040804153624.GA27551@pobox.com/
Also on:    postgresql.org/message-id/20040804153624.GA27551@pobox.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: adler@pobox.com, martin@ethereal-realms.org
  Subject: Re: Performance Bottleneck
  In-Reply-To: <20040804153624.GA27551@pobox.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox