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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
