Re: vacuum issues under load?

On Mon, Jan 18, 2010 at 6:35 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Mon, Jan 18, 2010 at 12:28 AM, Martijn van Oosterhout
> <kleptog(at)svana(dot)org> wrote:
>> On Fri, Jan 15, 2010 at 08:41:38AM -0700, Scott Marlowe wrote:
>>> With slony 2.0.3 or so, I had occasional complete lockups of my
>>> database that I didn't have time to troubleshoot as it was a live
>>> cluster and I had to restart slony and the databases to get them back
>>> up and running.
>>
>> With slony 2.0.2 I had similar problems. A CREATE INDEX on the slave
>> caused slony on the slave to block while inserting into the table which
>> eventually blocked the server during the log switch (TRUNCATE) which
>> eventually blocked everything else.
>
> You could have used create index interactively.
>
>> It occurs to me that the slony daemon should be able to get the
>> TRUNCATE command to abort if it takes too long.
>
> No, I don't want it stopping my truncates.
>
> The problem I ran into was on a db with no creating indexes, no
> truncates nothing like that going on.  It runs fine for a week or two,
> then hangs hard.  No updates, no selects, nothing works.  There are no
> locks in the way that I can see, just hung database.

Wait a minute, I'm describing the scenario we had with 2.0.3, where it
would just hang.

The vacuum issue is different. Vacuum starts, slony goes to lock the
table for ddl, waits on lock from vacuum, then everything backs up
behind slony. With large numbers of tables, the problem gets much
worse much faster.