X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9846BD1B298 for ; Wed, 4 Aug 2004 12:36:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74454-08 for ; Wed, 4 Aug 2004 15:36:27 +0000 (GMT) Received: from mail3.panix.com (mail3.panix.com [166.84.1.74]) by svr1.postgresql.org (Postfix) with ESMTP id A479DD1B1C8 for ; Wed, 4 Aug 2004 12:36:25 -0300 (ADT) Received: from panix2.panix.com (panix2.panix.com [166.84.1.2]) by mail3.panix.com (Postfix) with ESMTP id A01E0982CC; Wed, 4 Aug 2004 11:36:24 -0400 (EDT) Received: (from adler@localhost) by panix2.panix.com (8.11.6p2-a/8.8.8/PanixN1.1) id i74FaO311313; Wed, 4 Aug 2004 11:36:24 -0400 (EDT) Date: Wed, 4 Aug 2004 11:36:24 -0400 From: Michael Adler To: Martin Foster Cc: pgsql-performance@postgresql.org Subject: Re: Performance Bottleneck Message-ID: <20040804153624.GA27551@pobox.com> References: <410FDC53.5080509@bigfoot.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/54 X-Sequence-Number: 7690 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