Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyNTe-0004M7-0V for pgsql-performance@arkaria.postgresql.org; Mon, 25 Feb 2019 21:07:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyNTc-0004we-N6 for pgsql-performance@arkaria.postgresql.org; Mon, 25 Feb 2019 21:07:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyNTc-0004wX-CG for pgsql-performance@lists.postgresql.org; Mon, 25 Feb 2019 21:07:52 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyNTZ-00082F-Dt for pgsql-performance@postgresql.org; Mon, 25 Feb 2019 21:07:51 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LiBGf-1hLME30RDX-00nQvi; Mon, 25 Feb 2019 22:07:32 +0100 Message-ID: <5C745912.1080200@sqlexec.com> Date: Mon, 25 Feb 2019 16:07:30 -0500 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Jeff Janes CC: Gunther , "pgsql-performa." Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks. References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------080301020808060204070201" X-Provags-ID: V03:K1:cRg+1mwBsMGpgrc0iJTS+Z4i/QnlG/1q5xFuS5GgQjfqO3wH5v5 svAvTMu3/2x2/NmDQt3MT6Yy26azJqqAiFd1wu6lHXAtL+8r+9clLEp13c4iv4u7Gs4Ke4Q fInw2phDGc0sLNvSdMiSfJ5lD4Iy57sX1NQlr17wwRWRqkFnKW8UPhvoawrdyj2b/43AxIO d+VxQk7/utiWax0EAbjVQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:Y6Qxc22zj3Y=:HuqgS48jMgZGWb6vmZRlQ7 uhvtRfi6wFsisj56PMaw1YynoRkVpTN5+PNhG7v26j2tyvEQWNg7Gugeh7+/G+RIscHaxPCpD 5lK9k7G7a3nENASu5zDNBxcRt76h0CX1pNFAXdZU3msVOE3go8RNGqMcFsLvekSqvp9PNzxMb m3ou2ZN2+KaUsDKvkxXbSEXmk4XDT8YURBceBpwIaW71nocAOn9rRSDdwLvVP/6tYiEMPO/gG AuJnJOofdSfWUQZJdwwImHmNJnPwmR4SV4MBDVTR5xIW1HrRm8g/tv7IvknbInRuWMXLTJa7c Vh5Gl3OLwfoPcTscIe4FP1cUFoucjCKwBPrEzBIF/tbz8tgJv2Noda8GhQ+2g0u0ucgtnE32j ftQ3D//UNB5/+OE5s8Ljf1ixm00Z6zT/9vAGnS+d+R1NbVaix+YArrdpAvk97yH+KC/OlJQo+ 7EVcOzM8qQRmglbX6kBN1BC11dDsI6VOnMMi6wHmzLvWAFOCl7BJ4vGhHtD59PeWi4oMm/29B M169i6qWK2+M5xvOKOxRELnGoy0ztx+vA3v5flbBGwi/Oa6i4rwQh8axOvt3KOnoqQVZzyJ/+ /4gb5Kf+P+XDg3ipwKKQpBPq/R4GvWRAZReC9KWTFgb11T67Wzfs/8JNUwCyv5+pemAMjmY2h xKGN2IPBU6QE+b8pH7Nj09cT0DPrE9y72b2TVTG0p6i0Mjv0xeIfyldVpwvKscMUkwVMls5sv bP78wiiP/o31jeRO/9uNrlDrMRaQI3XuIw26fTr3U+zHdA3ufy8w3tSAHCQ= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------080301020808060204070201 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 > Monday, February 25, 2019 3:30 PM > On Sat, Feb 23, 2019 at 4:06 PM Gunther > 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 --------------080301020808060204070201 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

Monday, February 25, 2019 3:30 PM
On Sat, Feb 23, 2019 at 4:06 PM Gunther <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

--------------080301020808060204070201--