Обсуждение: [ADMIN] blocking issue when create index concurrently
Hello,
I was trying to create 2 indexes concurrently(on different table) in same database. I didn't expect the first create index operation would block the other one since they were running on different table.
However, it is not true. I found one creating index command was blocking the other one.
so I run the lock dependency query which I find from https://wiki.postgresql.org/wiki/Lock_dependency_information,
xu jian <jamesxu@outlook.com> writes:
> I was trying to create 2 indexes concurrently(on different table) in same database. I didn't expect the
firstcreate index operation would block the other one since they were running on different table.
> However, it is not true. I found one creating index command was blocking the other one.
There are (IIRC) two separate points in CREATE INDEX CONCURRENTLY where it
has to wait for all other transactions to exit. That would include a
transaction running another CREATE INDEX (CONCURRENTLY), even if it's for
a different table. I think running two at once would end in a deadlock
failure, since they would likely end up waiting for each other.
Usually, if you're using C.I.C., you're trying to minimize the impact
on the rest of the system, so running two at once seems a bit
counterproductive anyway.
regards, tom lane
Thanks Tom for your reply. the reason we want to run create index on different table concurrently is , we find the create index is single thread, rebuild index one by one tasks long time,
and our storage io has enough bandwidth , so cpu is bottleneck is this case. we don't want to hit the server hardly, however, running 2 recreate index concurrently on different table is still manageable for our system.
for your mention "CREATE INDEX CONCURRENTLY where it has to wait for all other transactions to exit", does it mean if there is user open translation, recreate index concurrently will be blocked? no matter which table the transaction is on ? thanks
James
发送时间: 2016年12月15日 12:33:37
收件人: xu jian
抄送: pgsql-admin@postgresql.org
主题: Re: [ADMIN] blocking issue when create index concurrently
> I was trying to create 2 indexes concurrently(on different table) in same database. I didn't expect the first create index operation would block the other one since they were running on different table.
> However, it is not true. I found one creating index command was blocking the other one.
There are (IIRC) two separate points in CREATE INDEX CONCURRENTLY where it
has to wait for all other transactions to exit. That would include a
transaction running another CREATE INDEX (CONCURRENTLY), even if it's for
a different table. I think running two at once would end in a deadlock
failure, since they would likely end up waiting for each other.
Usually, if you're using C.I.C., you're trying to minimize the impact
on the rest of the system, so running two at once seems a bit
counterproductive anyway.
regards, tom lane