From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Gunther <raj@gusw.net>
Cc: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
Date: Mon, 25 Feb 2019 16:07:30 -0500
Message-ID: <5C745912.1080200@sqlexec.com> (raw)
In-Reply-To: <CAMkU=1y-DYP-kXw0117UaLJcU9TkmkPPEBPBnP7Fzi9fGrp=ig@mail.gmail.com>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
<CAMkU=1y-DYP-kXw0117UaLJcU9TkmkPPEBPBnP7Fzi9fGrp=ig@mail.gmail.com>
Was wondering when that would come up, taking queuing logic outside the
database. Can be overly painful architecting queuing logic in
relational databases. imho.
Regards,
Michael Vitale
> Jeff Janes <mailto:jeff.janes@gmail.com>
> Monday, February 25, 2019 3:30 PM
> On Sat, Feb 23, 2019 at 4:06 PM Gunther <raj@gusw.net
> <mailto:raj@gusw.net>> wrote:
>
> Hi,
>
> I am using an SQL queue for distributing work to massively
> parallel workers.
>
> You should look into specialized queueing software.
>
> ...
>
> I figured I might just pause all workers briefly to schedule the
> REINDEX Queue command, but the problem with this is that while the
> transaction volume is large, some jobs may take minutes to
> process, and in that case we need to wait minutes to quiet the
> database with then 47 workers sitting as idle capacity waiting for
> the 48th to finish so that the index can be rebuilt!
>
> The jobs that take minutes are themselves the problem. They prevent
> tuples from being cleaned up, meaning all the other jobs needs to
> grovel through the detritus every time they need to claim a new row.
> If you got those long running jobs to end, you probably wouldn't even
> need to reindex--the problem would go away on its own as the
> dead-to-all tuples get cleaned up.
>
> Locking a tuple and leaving the transaction open for minutes is going
> to cause no end of trouble on a highly active system. You should look
> at a three-state method where the tuple can be
> pending/claimed/finished, rather than pending/locked/finished. That
> way the process commits immediately after claiming the tuple, and then
> records the outcome in another transaction once it is done
> processing. You will need a way to detect processes that failed after
> claiming a row but before finishing, but implementing that is going to
> be easier than all of this re-indexing stuff you are trying to do
> now. You would claim the row by updating a field in it to have
> something distinctive about the process, like its hostname and pid, so
> you can figure out if it is still running when it comes time to clean
> up apparently forgotten entries.
>
> Cheers,
>
> Jeff
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: MichaelDBA@sqlexec.com, jeff.janes@gmail.com, raj@gusw.net
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
In-Reply-To: <5C745912.1080200@sqlexec.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