From: Greg Smith <greg@2ndquadrant.com>
To: Eduardo Piombino <drakorg@gmail.com>
Cc: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: a heavy duty operation on an "unused" table kills my server
Date: Wed, 13 Jan 2010 02:39:30 -0500
Message-ID: <4B4D78B2.30600@2ndquadrant.com> (raw)
In-Reply-To: <e24c1d9d1001122147l1c0e2111kce25dc2c7f393087@mail.gmail.com>
References: <e24c1d9d1001122059ic5d4721o98ccf85033b1fead@mail.gmail.com>
<4B4D5698.8040808@emolecules.com>
<e24c1d9d1001122147l1c0e2111kce25dc2c7f393087@mail.gmail.com>
Eduardo Piombino wrote:
> Postgres version: 8.2.4, with all defaults, except DateStyle and TimeZone.
Ugh...there are several features in PostgreSQL 8.3 and later
specifically to address the sort of issue you're running into. If you
want to get good write performance out of this system, you may need to
upgrade to at least that version. It's impossible to resolve several of
the common problems in write operations being too intense using any 8.2
version.
> The final effect was that the server went non-responsive, for all
> matters, not even the TaskManager would come up when i hit
> CTRL-ALT-DEL, and of course, every client would suffer horrific (+20
> secs) for the simplest operations like SELECT NOW();
The thing that you have to realize is that altering a table is basically
making a new copy of that table, which is a really heavy amount of
writing. It's quite easy for an I/O heavy operation like that to fill
up a lot of RAM with data to be written out, and when the database
periodically needs to force all that data out to disk the whole system
grinds to a halt when it happens. There's no way I'm aware of to
throttle that writing down to a reasonable amount under Windows either,
to achieve your goal of just making the ALTER run using less resources.
Some reading:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server goes over
basic tuning of the database server. If you haven't already increased
the checkpoint_segments parameters of your system, that's the first
thing to try--increase it *a lot* (32 or more, default is 3) because it
can really help with this problem. A moderate increase to
shared_buffers is in order too; since you're on Windows, increasing it
to 256MB is a reasonable change. The rest of the changes in there
aren't likely to help out with this specific problem.
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm :
covers the most likely cause of the issue you're running into.
Unfortunately, most of the solutions you'll see there are things changed
in 8.3.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, drakorg@gmail.com, craig_james@emolecules.com
Subject: Re: a heavy duty operation on an "unused" table kills my server
In-Reply-To: <4B4D78B2.30600@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox