On Tue, 1 Jul 2008, Kathirvel, Jeevanandam wrote:
> We are seeing system hang-up issue when we do continuous update on table
> ( 2-3 records/sec) within 10-12 hours. Memory parameter Inact_dirty(
> shown in /proc/meminfo) is increasing continuously and causing the
> system to hang-up(not responding state).
When you update a row, what it does is write a new version of that row out
to disk and then mark the old version dead afterwards. That process
generates disk writes, which show up as Inact_dirty data while they're in
memory. Eventually your system should be writing those out to disk. The
most helpful thing you could post here to narrow down what's going on is a
snippet of the output from "vmstat 1" during a period where things are
running slowly.
Dirty memory growing continuously suggests you're updating faster than
your disk(s) can keep up. The main thing you can usefully do in
PostgreSQL 7.4.3 to lower how much I/O is going on during updates is to
increase the checkpoint_segments parameters in your postgresql.conf file.
A modest increase there, say going from the default of 3 to 10, may reduce
the slowdowns you're seeing. Note that this will cause the database to
get larger and it will take longer to recover from a crash.
Given how old the versions of all the software you're using are, it's
quite possible what you're actually running into is a Linux kernel bug or
even a PostgreSQL bug. If this problem is getting annoying enough to
disrupt your operations you should be considering an upgrade of your whole
software stack. Start with going from PostgreSQL 7.4.3 to 7.4.21, try and
add more RAM to the server, look into whether you can re-install on a more
modern Linux, and try to get onto PostgreSQL 8.3 one day.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD