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

Michael Adler wrote:

> 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. 
> 

The Apache process will run for 30 minutes at a time, not the query 
itself.  Essentially, while that process is running it will check for 
new records in the table at varying intervals, since it will increase 
timeouts based on load or lack of activity in order to reduce load to 
the database.

> 
>>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
> 

I will take a look into pgpool and see if it will serve as the solution 
I need.   The pre-pooling of children sounds like a good choice, however 
since overhead is already a point of worry I almost wonder if I can host 
it on another server in order to drop that overhead on the servers directly.

Anyone have experience with this on running it on the same machine or a 
different machine then the database proper?   Of course, if this works 
as it should, I could easily put an older database server back into 
operation provided pgpool does weighted load balancing.

	Martin Foster
	Creator/Designer Ethereal Realms
	martin@ethereal-realms.org





view thread (38+ messages)  latest in thread

Message-ID: <41110D06.5090900@ethereal-realms.org>
Permalink:  ../41110D06.5090900@ethereal-realms.org/
Also on:    postgresql.org/message-id/41110D06.5090900@ethereal-realms.org

 · 

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: martin@ethereal-realms.org, adler@pobox.com
  Subject: Re: Performance Bottleneck
  In-Reply-To: <41110D06.5090900@ethereal-realms.org>

* 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