pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate Index CONCURRENTLY Hangs Indefinitely.
5+ messages / 4 participants
[nested] [flat]
* Create Index CONCURRENTLY Hangs Indefinitely.
@ 2015-05-19 10:30 Wei Shan <weishan.ang@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Wei Shan @ 2015-05-19 10:30 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Index CONCURRENTLY Hangs Indefinitely.
@ 2015-05-19 10:42 Adarsh Sharma <eddy.adarsh@gmail.com>
parent: Wei Shan <weishan.ang@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Adarsh Sharma @ 2015-05-19 10:42 UTC (permalink / raw)
To: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Index CONCURRENTLY Hangs Indefinitely.
@ 2015-05-19 10:45 Jan Lentfer <Jan.Lentfer@web.de>
parent: Wei Shan <weishan.ang@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jan Lentfer @ 2015-05-19 10:45 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Index CONCURRENTLY Hangs Indefinitely.
@ 2015-05-19 12:53 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Wei Shan <weishan.ang@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Matheus de Oliveira @ 2015-05-19 12:53 UTC (permalink / raw)
To: Wei Shan <weishan.ang@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Index CONCURRENTLY Hangs Indefinitely.
@ 2015-05-20 02:48 Wei Shan <weishan.ang@gmail.com>
parent: Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Wei Shan @ 2015-05-20 02:48 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-05-20 02:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-19 10:30 Create Index CONCURRENTLY Hangs Indefinitely. Wei Shan <weishan.ang@gmail.com>
2015-05-19 10:42 ` Adarsh Sharma <eddy.adarsh@gmail.com>
2015-05-19 10:45 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-05-19 12:53 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2015-05-20 02:48 ` Wei Shan <weishan.ang@gmail.com>
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