From: Scott Marlowe <scott.marlowe@gmail.com>
To: Eduardo Piombino <drakorg@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: a heavy duty operation on an "unused" table kills my server
Date: Wed, 13 Jan 2010 09:52:23 -0700
Message-ID: <dcc563d11001130852k30b89980j77942338f1cc4e76@mail.gmail.com> (raw)
In-Reply-To: <e24c1d9d1001122059ic5d4721o98ccf85033b1fead@mail.gmail.com>
References: <e24c1d9d1001122059ic5d4721o98ccf85033b1fead@mail.gmail.com>
On Tue, Jan 12, 2010 at 9:59 PM, Eduardo Piombino <drakorg@gmail.com> wrote:
...
> Now, with this experience, I tried a simple workaround.
> Created an empty version of "a" named "a_empty", identical in every sense.
> renamed "a" to "a_full", and "a_empty" to "a". This procedure costed me like
> 0 seconds of downtime, and everything kept working smoothly. Maybe a cpl of
> operations could have failed if they tried to write in the very second that
> there was actually no table named "a", but since the operation was
> transactional, the worst scenario was that if the operation should have
> failed, the client application would just inform of the error and ask the
> user for a retry. No big deal.
>
> Now, this table, that is totally unattached to the system in every way (no
> one references this table, its like a dumpster for old records), is not
> begin accessed by no other thread in the system, so an ALTER table on it, to
> turn a char(255) to char(250), should have no effect on the system.
>
> So, with this in mind, I tried the ALTER TABLE this time on the "a_full"
> (totally unrelated) table.
> The system went non-responsive again, and this time it had nothing to do
> with threads waiting for the alter table to complete. The pgAdmin GUI went
> non-responsive, as well as the application's server GUI, whose threads kept
> working on the background, but starting to take more and more time for every
> clients request (up to 25 seconds, which are just ridiculous and completely
> unacceptable in normal conditions).
OK, I'm not entirely sure this table is not still locking something
else. If you make a copy by doing something like:
select * into test_table from a;
and then alter test_table do you still get the same problems? If so,
then it is an IO issue, most likely. If not, then there is some
client connection still referencing this table or something and that
could cause this type of behaviour as well.
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: scott.marlowe@gmail.com, drakorg@gmail.com
Subject: Re: a heavy duty operation on an "unused" table kills my server
In-Reply-To: <dcc563d11001130852k30b89980j77942338f1cc4e76@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