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