From: Gunther <raj@gusw.net>
Subject: Re: Massive parallel queue table causes index deterioration, but
 REINDEX fails with deadlocks.
Cc: postgres performance list <pgsql-performance@postgresql.org>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
 <CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
Message-ID: <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
Date: Sun, 24 Feb 2019 12:45:34 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.1
MIME-Version: 1.0
In-Reply-To: 
 <CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------460B8DAE19F93E7BAEC49097"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------460B8DAE19F93E7BAEC49097
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------460B8DAE19F93E7BAEC49097
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p>Thank you all for responding so far.</p>
    <p>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.<br>
    </p>
    <p>I want to believe that <br>
    </p>
    <p>VACUUM FREEZE Queue;</p>
    <p>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.<br>
    </p>
    <p>Rebuilding the index by building  a new index and removing the
      old, then rename, and vacuum again, is prone to get stuck. <br>
    </p>
    <p>I tried to do it in a transaction. But it says CREATE INDEX can't
      be done in a transaction. <br>
    </p>
    <p>Need to CREATE INDEX CONCURRENTLY ... and I can't even do that in
      a procedure.</p>
    <p>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.</p>
    <p>I also noticed that after I quit from DROP INDEX CONCURRENTLY
      old, that index is shown as INVALID</p>
    <pre>\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
...
</pre>
    <p>and so I keep doing that same routine hands-on, every time that
      the CPU% creeps above 50% I do</p>
    <pre>CREATE UNIQUE INDEX CONCURRENTLY Queue_idx_pending6 ON Queue(jobId, action) WHERE currentlyOpen;
DROP INDEX CONCURRENTLY Queue_idx_pending5;
</pre>
    <p>at which place it hangs, I interrupt the DROP command, which
      leaves the old index behind as "INVALID".</p>
    <pre>VACUUM FREEZE ANALYZE Queue;</pre>
    <p>At this point the db's CPU% dropping below 20% after the new
      index has been built.</p>
    <p>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. <br>
    </p>
    <p>So this isn't sustainable like that (worse because my Internet
      constantly drops).</p>
    <p>What I am most puzzled by is that no matter how long I wait, the
      DROP INDEX CONCURRENTLY never completes. Why is that?</p>
    <p>Also, the REINDEX command always fails with a deadlock because
      there is a row lock and a complete table lock involved. <br>
    </p>
    <p>I consider this ultimately a bug, or at the very least there is
      room for improvement. And I am on version 11.1.<br>
    </p>
    regards,<br>
    -Gunther<br>
  </body>
</html>

--------------460B8DAE19F93E7BAEC49097--