pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Eduardo Piombino <drakorg@gmail.com>
To: Greg Smith <greg@2ndquadrant.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 05:27:01 -0300
Message-ID: <e24c1d9d1001130027u463daa4cy2f687e1d27556b79@mail.gmail.com> (raw)
In-Reply-To: <4B4D78B2.30600@2ndquadrant.com>
References: <e24c1d9d1001122059ic5d4721o98ccf85033b1fead@mail.gmail.com>
<4B4D5698.8040808@emolecules.com>
<e24c1d9d1001122147l1c0e2111kce25dc2c7f393087@mail.gmail.com>
<4B4D78B2.30600@2ndquadrant.com>
Yes, one of the things I will do asap is to migrate to the latest version.
On other occasion I went through the checkpoint parameters you mentioned,
but left them untouched since they seemed logical.
I'm a little reluctant of changing the checkpoint configuration just to let
me do a -once in a lifetime- ALTER.
The checkpoints would then remain too far away in time (or in traffic).
And thinking of touching it and retouching it every time I need to do sthing
different bugs me a little. But if there is no other option I will
definitely give it a try.
Are you sure, for instance, that the ALTER command (and the internal data it
may require to handle, lets say 1.8 million records * 1024 bytes/record
(aprox)) goes to RAM, then to disk, and gets logged in the WAL during the
whole process? Maybe it does not get logged at all until the ALTER is
completed? Since the original table can be left untouched until this copy of
the table gets updated ... Just guessing here.
On Wed, Jan 13, 2010 at 4:39 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> 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<http://www.westnet.com/%7Egsmit...;: 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
>
>
view thread (25+ messages) latest in thread
Message-ID: <e24c1d9d1001130027u463daa4cy2f687e1d27556b79@mail.gmail.com> Permalink: ../e24c1d9d1001130027u463daa4cy2f687e1d27556b79@mail.gmail.com/ Also on: postgresql.org/message-id/e24c1d9d1001130027u463daa4cy2f687e1d27556b79@mail.gmail.com ·
reply
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: drakorg@gmail.com, greg@2ndquadrant.com, craig_james@emolecules.com Subject: Re: a heavy duty operation on an "unused" table kills my server In-Reply-To: <e24c1d9d1001130027u463daa4cy2f687e1d27556b79@mail.gmail.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