Re: [PERFORM] Postgres Dump - Creating index never stops

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: [PERFORM] Postgres Dump - Creating index never stops
Дата
Msg-id 24671.1499888488@sss.pgh.pa.us
обсуждение исходный текст
Ответ на [PERFORM] Postgres Dump - Creating index never stops  (Hans Braxmeier <hans.braxmeier@outlook.com>)
Список pgsql-performance
Hans Braxmeier <hans.braxmeier@outlook.com> writes:
> After restarting postgres (even with a new cluster) and creating a new database, postgres is hanging while extracting
thedump: gunzip -c pixabay.gz | psql pixabay 

> The log file shows that the autovacuum task is running (almost) endless...

> 2017-07-12 18:05:52.367 CEST [19586] hans@pixabay LOG:  duration: 11.609 ms  statement: CREATE INDEX
photos_indexphoto_createdON photos_indexphoto USING btree (created); 
> 2017-07-12 20:34:58.943 CEST [19626] ERROR:  canceling autovacuum task
> 2017-07-12 20:34:58.943 CEST [19626] CONTEXT:  automatic analyze of table "pixabay.public.photos_photo"
> 2017-07-12 20:34:59.942 CEST [19586] hans@pixabay LOG:  duration: 8947575.013 ms  statement: CREATE INDEX
photos_photo_approved_by_idON photos_photo USING btree (approved_by_id); 

What that looks like is it took the system an unusually long time to
notice that it needed to cancel the autovacuum to avoid a deadlock
with the CREATE INDEX.  Was either process consuming a noticeable
amount of CPU during that interval?  Do you have deadlock_timeout
set higher than the default 1s?

            regards, tom lane


В списке pgsql-performance по дате отправления:

Предыдущее
От: Hans Braxmeier
Дата:
Сообщение: [PERFORM] Postgres Dump - Creating index never stops
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: [PERFORM] Very poor read performance, query independent