I've been experimenting with Matthew T. O'Connor's pg_avd (auto vacuum
daemon), which is actually quite an interesting piece of software that
so far seems to function pretty well.

I've ported it to PG 7.2.4, which is the version I'm running on my
system, and experimenting with it has uncovered what I can only
consider a potentially serious bug in PG.

When a heavy INSERT or UPDATE load on a table is occurring (lots of
quick INSERTs or UPDATEs within a single transaction), a VACUUM
ANALYZE (or just straight VACUUM) has a really good chance (10% or so)
of causing either the INSERT/UPDATE or the VACUUM to fail with a
"deadlock detected" error. Usually the INSERT/UPDATE is what fails.
I have verified this with manual VACUUM and VACUUM ANALYZE commands
issued on the command line while a perl script is generating a heavy
load of inserts and/or updates, so this isn't pg_avd's fault.

A straight ANALYZE on the table doesn't seem to trigger the problem,
while a straight VACUUM does.

Statistics collection is enabled -- it's required for pg_avd to run --
but I suspect that this is irrelevant (what does a straight VACUUM do
with the statistics tables, if anything?).

Even dropping the table and all of its indexes and recreating it does
nothing to solve this problem, so I don't have any reason to suspect
corruption of the table itself. Corruption elsewhere is a
possibility, I suppose, but I haven't noticed any strangeness
elsewhere.

The only other thing of note is that a REALLY LONG running INSERT
... SELECT (with the source being a temporary table) is running
concurrently on a different table in a different database. That, too,
is running within a transaction, but I don't have reason to believe
that it's having an effect. When it completes I'll do more testing to
determine if it really did have an effect.

I've searched the archives for references to this and have found
nothing relevant (all references I found are to older implementations
in which VACUUM locks the table). Have I managed to uncover something
new here?

I don't have 7.3.x (or later) installed so I can't test this myself on
that or CVS tip, but I may have to if nobody else has the time.


Anyone have any suggestions on what to do about this? This problem
pretty much kills pg_avd's usefulness, at least on 7.2.x. :-(


I'll be happy to supply the Perl script I'm using to do the inserts,
if that'll help people track this down...



--
Kevin Brown kevin@sysexperts.com

Search Discussions

  • Tom Lane at Mar 27, 2003 at 2:29 pm

    Kevin Brown writes:
    When a heavy INSERT or UPDATE load on a table is occurring (lots of
    quick INSERTs or UPDATEs within a single transaction), a VACUUM
    ANALYZE (or just straight VACUUM) has a really good chance (10% or so)
    of causing either the INSERT/UPDATE or the VACUUM to fail with a
    "deadlock detected" error.
    I was unable to replicate this in CVS tip, using "pgbench -c 10 -t 1000"
    as the load generator.

    regards, tom lane
  • Kevin Brown at Mar 27, 2003 at 10:50 pm

    Tom Lane wrote:
    Kevin Brown <kevin@sysexperts.com> writes:
    When a heavy INSERT or UPDATE load on a table is occurring (lots of
    quick INSERTs or UPDATEs within a single transaction), a VACUUM
    ANALYZE (or just straight VACUUM) has a really good chance (10% or so)
    of causing either the INSERT/UPDATE or the VACUUM to fail with a
    "deadlock detected" error.
    I was unable to replicate this in CVS tip, using "pgbench -c 10 -t 1000"
    as the load generator.
    I guess I should mention that I was doing VACUUM on the individual
    tables, not the entire database. I have no idea if that makes any
    difference.

    I'll see about compiling and installing CVS tip for testing. I'll
    also see about doing some testing on 7.3.2.


    Can anyone here independently verify what I've found on 7.2.4? You'll
    probably have to run a process that does continuous INSERTs on a
    table, and another that does continuous VACUUMs on the same table at
    the same time.



    --
    Kevin Brown kevin@sysexperts.com
  • Kevin Brown at Apr 4, 2003 at 10:46 pm

    Tom Lane wrote:
    Kevin Brown <kevin@sysexperts.com> writes:
    When a heavy INSERT or UPDATE load on a table is occurring (lots of
    quick INSERTs or UPDATEs within a single transaction), a VACUUM
    ANALYZE (or just straight VACUUM) has a really good chance (10% or so)
    of causing either the INSERT/UPDATE or the VACUUM to fail with a
    "deadlock detected" error.
    I was unable to replicate this in CVS tip, using "pgbench -c 10 -t 1000"
    as the load generator.
    I finally got 7.3.2 installed, and confirmed that the problem does not
    exist on that version. So this is something that's limited to the
    7.2.x tree. Which, I guess, means that it's not going to get fixed
    for that tree (I assume that 7.2.x is effectively end-of-lifed)...

    On 7.3.2, a concurrent VACUUM appears to slow inserts down A LOT, but
    it won't deadlock them.


    --
    Kevin Brown kevin@sysexperts.com
  • Tom Lane at Apr 4, 2003 at 11:27 pm

    Kevin Brown writes:
    I finally got 7.3.2 installed, and confirmed that the problem does not
    exist on that version. So this is something that's limited to the
    7.2.x tree. Which, I guess, means that it's not going to get fixed
    for that tree (I assume that 7.2.x is effectively end-of-lifed)...
    I dug through the CVS logs, and could not find any entry between 7.2 and
    7.3 that seemed like it might fix such a problem. If we knew what the
    problem was, maybe we could put together a back-patch. I'm not
    personally eager to spend more time on 7.2.*, though.

    regards, tom lane
  • Thomas T. Thai at Mar 27, 2003 at 5:51 pm

    Even dropping the table and all of its indexes and recreating it does
    nothing to solve this problem, so I don't have any reason to suspect
    corruption of the table itself. Corruption elsewhere is a
    possibility, I suppose, but I haven't noticed any strangeness
    elsewhere.
    This sounds very similiar to problems I was having under the thread:

    Re: [HACKERS] BUG: Vacuum Analyze - datumGetSize: Invalid typLen 0

    Dropping the table, indexes, and recreating it didn't help me either. I
    had to drop the database!

    What kind of platform are you on? 64-bit?

    I've noticed that 7.4-snapshot fixed a huge amount of bugs, but the vacuum
    analyze bug is still there.

    --
    Thomas

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 27, '03 at 11:32a
activeApr 4, '03 at 11:27p
posts6
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase