Thread: Create Index CONCURRENTLY Hangs Indefinitely.

Create Index CONCURRENTLY Hangs Indefinitely.

From
Wei Shan
Date:
Hi all,

Today I tried to create an index online, however it took far too long that I cancelled the query.

1. psql version => 9.2.4
2. Table structure as follows. (7.5GB in size)
evocsp=# \d certificatedata
       Table "public.certificatedata"
        Column        |  Type   | Modifiers
----------------------+---------+-----------
 fingerprint          | text    | not null
 base64cert           | text    |
 cafingerprint        | text    |
 certificateprofileid | integer | not null
 expiredate           | bigint  | not null
 issuerdn             | text    | not null
 revocationdate       | bigint  | not null
 revocationreason     | integer | not null
 rowprotection        | text    |
 rowversion           | integer | not null
 serialnumber         | text    | not null
 status               | integer | not null
 subjectdn            | text    | not null
 subjectkeyid         | text    |
 tag                  | text    |
 type                 | integer | not null
 updatetime           | bigint  | not null
 username             | text    |
Indexes:
    "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)
    "certificatedata_idx7" btree (certificateprofileid)

3. The query ran was => CREATE INDEX CONCURRENTLY certificatedata_idx2 ON CertificateData (username) tablespace tablespace_index;
4. After it took more than 2hrs+, I went to terminate the session via Ctrl-C, it returned the following error.

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
^CCancel request sent
ERROR:  canceling statement due to user request

Any input or ideas are welcome.

Thanks!

--
Regards,
Ang Wei Shan

Re: Create Index CONCURRENTLY Hangs Indefinitely.

From
Adarsh Sharma
Date:
Hi Wei,

There are many aspects that comes into picture :

1. Did you check that your create index concurrently command is in waiting or running state in pg_stat_activity.
2. Terminate the session by Ctrl+C results into INVALID index. You might want to drop that as well.
3. Increase the work_mem to ~ 3-4 GB before creating the index.
4. Create Index Concurrently usually takes longer time as compared with Create Index Command.

thanks



On Tue, 19 May 2015 at 16:01 Wei Shan <weishan.ang@gmail.com> wrote:
Hi all,

Today I tried to create an index online, however it took far too long that I cancelled the query.

1. psql version => 9.2.4
2. Table structure as follows. (7.5GB in size)
evocsp=# \d certificatedata
       Table "public.certificatedata"
        Column        |  Type   | Modifiers
----------------------+---------+-----------
 fingerprint          | text    | not null
 base64cert           | text    |
 cafingerprint        | text    |
 certificateprofileid | integer | not null
 expiredate           | bigint  | not null
 issuerdn             | text    | not null
 revocationdate       | bigint  | not null
 revocationreason     | integer | not null
 rowprotection        | text    |
 rowversion           | integer | not null
 serialnumber         | text    | not null
 status               | integer | not null
 subjectdn            | text    | not null
 subjectkeyid         | text    |
 tag                  | text    |
 type                 | integer | not null
 updatetime           | bigint  | not null
 username             | text    |
Indexes:
    "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)
    "certificatedata_idx7" btree (certificateprofileid)

3. The query ran was => CREATE INDEX CONCURRENTLY certificatedata_idx2 ON CertificateData (username) tablespace tablespace_index;
4. After it took more than 2hrs+, I went to terminate the session via Ctrl-C, it returned the following error.

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
^CCancel request sent
ERROR:  canceling statement due to user request

Any input or ideas are welcome.

Thanks!

--
Regards,
Ang Wei Shan

Re: Create Index CONCURRENTLY Hangs Indefinitely.

From
Jan Lentfer
Date:
Am 2015-05-19 12:30, schrieb Wei Shan:
> Hi all,
>
> Today I tried to create an index online, however it took far too long
> that I cancelled the query.
>
> 1. psql version => 9.2.4
> 2. Table structure as follows. (7.5GB in size)
>
> evocsp=# d certificatedata
>        Table "public.certificatedata"
>         Column        |  Type   | Modifiers
> ----------------------+---------+-----------
>  fingerprint          | text    | not null
>  base64cert           | text    |
>  cafingerprint        | text    |
>  certificateprofileid | integer | not null
>  expiredate           | bigint  | not null
>  issuerdn             | text    | not null
>  revocationdate       | bigint  | not null
>  revocationreason     | integer | not null
>  rowprotection        | text    |
>  rowversion           | integer | not null
>  serialnumber         | text    | not null
>  status               | integer | not null
>  subjectdn            | text    | not null
>  subjectkeyid         | text    |
>  tag                  | text    |
>  type                 | integer | not null
>  updatetime           | bigint  | not null
>  username             | text    |
> Indexes:
>     "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)
>     "certificatedata_idx7" btree (certificateprofileid)
>
> 3. The query ran was => CREATE INDEX CONCURRENTLY
> certificatedata_idx2
> ON CertificateData (username) tablespace tablespace_index;
> 4. After it took more than 2hrs+, I went to terminate the session via
> Ctrl-C, it returned the following error.
>
> WARNING:  canceling wait for synchronous replication due to user
> request
> DETAIL:  The transaction has already committed locally, but might not
> have been replicated to the standby.
> ^CCancel request sent
> ERROR:  canceling statement due to user request
>
> Any input or ideas are welcome.


Was your SR slave actually online consuming changes? What does "select
* from pg_stat_replication" tell you?

Jan


Re: Create Index CONCURRENTLY Hangs Indefinitely.

From
Matheus de Oliveira
Date:

On Tue, May 19, 2015 at 7:30 AM, Wei Shan <weishan.ang@gmail.com> wrote:
4. After it took more than 2hrs+, I went to terminate the session via Ctrl-C, it returned the following error.

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
^CCancel request sent
ERROR:  canceling statement due to user request

The index has been created already, probably for a long time, but it was waiting to be sent to your standby server. Seems that you have synchronous replication configured and your standby is either delayed, not connected or out of sync. That is bad, really bad, means that any writes to your primary might be stalling now.

You should check your replication.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: Create Index CONCURRENTLY Hangs Indefinitely.

From
Wei Shan
Date:
Hi all,

Thanks a lot for the replies. I really appreciate it.

I have found the root cause for this issue.

1. I created a new tablespace called tablespace_index pointing to  '/data/tablespace' on master but I forgot to create the directory on slave.
2. Once the tablespace was created on the master, the slave could not replicate the changes over because there was not directory to create the index!
3. As such, the subsequence queries started failing.

It was a good learning experience for me. I didn't foresee this to happen.

Cheers!

On 19 May 2015 at 20:53, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:

On Tue, May 19, 2015 at 7:30 AM, Wei Shan <weishan.ang@gmail.com> wrote:
4. After it took more than 2hrs+, I went to terminate the session via Ctrl-C, it returned the following error.

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
^CCancel request sent
ERROR:  canceling statement due to user request

The index has been created already, probably for a long time, but it was waiting to be sent to your standby server. Seems that you have synchronous replication configured and your standby is either delayed, not connected or out of sync. That is bad, really bad, means that any writes to your primary might be stalling now.

You should check your replication.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres




--
Regards,
Ang Wei Shan