Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD