From: Gunther <raj@gusw.net>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
Date: Sun, 24 Feb 2019 12:45:34 -0500
Message-ID: <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net> (raw)
In-Reply-To: <CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
<CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
Thank you all for responding so far.
David Rowley and Justin Pryzby suggested things about autovacuum. But I
don't think autovacuum has any helpful role here. I am explicitly doing
a vacuum on that table. And it doesn't help at all. Almost not at all.
I want to believe that
VACUUM FREEZE Queue;
will push the database CPU% down again once it is climbing up, and I can
do this may be 3 to 4 times, but ultimately I will always have to
rebuild the index. But also, none of these vaccuum operations I do takes
very long at all. It is just not efficacious at all.
Rebuilding the index by building a new index and removing the old, then
rename, and vacuum again, is prone to get stuck.
I tried to do it in a transaction. But it says CREATE INDEX can't be
done in a transaction.
Need to CREATE INDEX CONCURRENTLY ... and I can't even do that in a
procedure.
If I do it manually by issuing first CREATE INDEX CONCURRENTLY new and
then DROP INDEX CONCURRENTLY old, it might work once, but usually it
just gets stuck with two indexes. Although I noticed that it would
actually put CPU back down and improve transaction throughput.
I also noticed that after I quit from DROP INDEX CONCURRENTLY old, that
index is shown as INVALID
\d Queue
...
Indexes:
"queue_idx_pending" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending2" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending3" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending4" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending5" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending6" UNIQUE, btree (jobId, action) WHERE pending
...
and so I keep doing that same routine hands-on, every time that the CPU%
creeps above 50% I do
CREATE UNIQUE INDEX CONCURRENTLY Queue_idx_pending6 ON Queue(jobId, action) WHERE currentlyOpen;
DROP INDEX CONCURRENTLY Queue_idx_pending5;
at which place it hangs, I interrupt the DROP command, which leaves the
old index behind as "INVALID".
VACUUM FREEZE ANALYZE Queue;
At this point the db's CPU% dropping below 20% after the new index has
been built.
Unfortunately this is totally hands on approach I have to do this every
5 minutes or so. And possibly the time between these necessities
decreases. It also leads to inefficiency over time, even despite the CPU
seemingly recovering.
So this isn't sustainable like that (worse because my Internet
constantly drops).
What I am most puzzled by is that no matter how long I wait, the DROP
INDEX CONCURRENTLY never completes. Why is that?
Also, the REINDEX command always fails with a deadlock because there is
a row lock and a complete table lock involved.
I consider this ultimately a bug, or at the very least there is room for
improvement. And I am on version 11.1.
regards,
-Gunther
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: raj@gusw.net
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
In-Reply-To: <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
* 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