Improve the concurency of vacuum full table and select statement on the same relation

Поиск
Список
Период
Сортировка
Искать
От
Jinyu
Тема
Improve the concurency of vacuum full table and select statement on the same relation
Дата
Msg-id
225e0396.1247d.150525e2523.Coremail.call_jinyu@126.com
Список
Дерево обсуждения
Improve the concurency of vacuum full table and select statement on the same relation Jinyu <call_jinyu@126.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Tom Lane <tgl@sss.pgh.pa.us>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jinyu <call_jinyu@126.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jim Nasby <Jim.Nasby@BlueTreble.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jinyu <call_jinyu@126.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jim Nasby <Jim.Nasby@BlueTreble.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Robert Haas <robertmhaas@gmail.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jim Nasby <Jim.Nasby@BlueTreble.com>
Re: Improve the concurency of vacuum full table and select statement on the same relation Jinyu <call_jinyu@126.com>
Now vacuum full table takes an AccessExclusiveLock on relation at start and select statement takes an AccessShareLock on relation. So 'vacuum full table' blocks select statement on the same table until it is committed and select statement block 'vacuum full table' until it is finished. The concurency is very very bad.

Proposal:  vacuum full table takes an ExclusiveLock on relation instead of AccessExclusiveLock at start. It can' block select statement before call function "finish_heap_swap". and select statement is safe because vacuum full table  copys tuples from old relation to new relation before calling function "finish_heap_swap". But it must take an AccessExclusiveLock on relation when call function "finish_heap_swap" in order to block select statement on the same relation.

This solution can improve the concurency. the following shows the reasons.
1. The Function 'copy_heap_data' which copys tuples from old relation to new relation takes most elapsed time of vacuum full table. And it takes an ExclusiveLock on relation when call function "copy_heap_data". So select statement on the same relation can't be blocked in the most elapsed time of vacuum full table.
2. The elapsed time of "finish_heap_swap" is very short, So the blocking time window is very short.

This proposal can also improve the concurency of cluster table and select statement. Because the execution steps of cluster table is similar to vacuum full table. The select statement is safe before cluster table call function "finish_heap_swap".

Please let me know if I miss something.

Jinyu Zhang
thanks


 

В списке pgsql-hackers по дате отправления
От: Tom Lane
Дата:
От: Dmitry Vasilyev
Дата:
FAQ