Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 82C9865015C for ; Fri, 29 Aug 2008 01:41:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 05811-01 for ; Fri, 29 Aug 2008 01:41:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id B56DF65037F for ; Fri, 29 Aug 2008 01:31:25 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m7T4VOH7027536; Fri, 29 Aug 2008 00:31:24 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m7T4VNrg027532; Fri, 29 Aug 2008 00:31:23 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Fri, 29 Aug 2008 00:31:23 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Scott Marlowe cc: henk de wit , pgsql-performance@postgresql.org Subject: Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/413 X-Sequence-Number: 31294 On Tue, 26 Aug 2008, Scott Marlowe wrote: > If it is a checkpoint issue then you need more aggresive bgwriter > settings, and possibly more bandwidth on your storage array. Since this is 8.3.1 the main useful thing to do is increase checkpoint_segments and checkpoint_completion_target to spread the I/O over a longer period. Making the background writer more aggressive doesn't really help with What is checkpoint_segments set to on this system? If it's still at the default of 3, you should increase that dramatically. > What does vmstat 10 say during these spikes? If you're running the > sysstate service with data collection then sar can tell you a lot. Henk seemed a bit confused about this suggestion, and the typo doesn't help. You can install the sysstat package with: # apt-get install sysstat This allows collecting system load info data at regular periods, automatically, and sar is the tool you can use to look at it. On Debian, in order to get it to collect that information for you, I believe you just need to do: # dpkg-reconfigure sysstat Then answer "yes" to "Do you want to activate sysstat's cron job?" This will install a crontab file that collects all the data you need for sar to work. You may need to restart the service after that. There's a useful walkthrough for this at http://www.linuxweblog.com/blogs/wizap/20080126/sysstat-ubuntu -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD