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(at)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(at)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(at)postgresql(dot)org/msg00760.html