Horton

Re: remove spurious CREATE INDEX CONCURRENTLY wait

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>, James Coleman <jtc331@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2020-11-16T20:35:54Z
Lists: pgsql-hackers 
On 2020-Nov-16, Dmitry Dolgov wrote:

> The same with reindex without locks:
> 
>      nsecs               : count     distribution
>        512 -> 1023       : 0        |                                        |
>       1024 -> 2047       : 111345   |                                        |
>       2048 -> 4095       : 6997627  |****************************************|
>       4096 -> 8191       : 18575    |                                        |
>       8192 -> 16383      : 586      |                                        |
>      16384 -> 32767      : 312      |                                        |
>      32768 -> 65535      : 18       |                                        |
> 
> The same with reindex with locks:
> 
>      nsecs               : count     distribution
>        512 -> 1023       : 0        |                                        |
>       1024 -> 2047       : 59438    |                                        |
>       2048 -> 4095       : 6901187  |****************************************|
>       4096 -> 8191       : 18584    |                                        |
>       8192 -> 16383      : 581      |                                        |
>      16384 -> 32767      : 280      |                                        |
>      32768 -> 65535      : 84       |                                        |
> 
> Looks like with reindex without locks is indeed faster (there are mode
> samples in lower time section), but not particularly significant to the
> whole distribution, especially taking into account extremity of the
> test.

I didn't analyze these numbers super carefully, but yeah it doesn't look
significant.

I'm looking at these patches now, with intention to push.

Commits

  1. Restore lock level to set vacuum flags

  2. Restore lock level to update statusFlags

  3. Avoid spurious waits in concurrent indexing

  4. Centralize logic for skipping useless ereport/elog calls.

  5. Don't hold ProcArrayLock longer than needed in rare cases

  6. Relax lock level for setting PGPROC->statusFlags

  7. Rename PGPROC->vacuumFlags to statusFlags

  8. snapshot scalability: Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags.

  9. Allow an autovacuum worker to be interrupted automatically when it is found