Re: Hash join on int takes 8..114 seconds

От: Andrus
Тема: Re: Hash join on int takes 8..114 seconds
Дата: ,
Msg-id: 8D7F64CF7F614E13AC554D6CF86F39B2@andrusnotebook
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join on int takes 8..114 seconds  (PFC)
Ответы: Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera)
Список: pgsql-performance

Скрыть дерево обсуждения

Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (PFC, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (PFC, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
     Re: Hash join on int takes 8..114 seconds  (, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
       Re: Hash join on int takes 8..114 seconds  (Alan Hodgson, )
       Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
     Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
     Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  (Tom Lane, )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
         Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (, )
              Re: Hash join on int takes 8..114 seconds  ("Andrus", )

>> How to vacuum full pg_shdepend automatically so that other users can
>> work at same time ?
>
> Your table is horribly bloated.
> You must use VACUUM FULL + REINDEX (as superuser) on it, however
> unfortunately, it is blocking.
> Therefore, you should wait for sunday night to do this, when noone will
> notice.

Shops are closed late night for a short time, including sunday night.
This time may be shorter than time required to complete VACUUM command.

I discovered vacuum_cost_delay=2000 option. Will this remove blocking issue
and allow vacuum full to work ?

> Meanwhile, you can always VACUUM it (as superuser) and REINDEX it.

I expect that autovacuum does this automatically.

> And while you're at it, VACUUM FULL + reindex the entire database.
> To avoid such annoyances in the future, you should ensure that autovacuum
> runs properly ; you should investigate this. If you use a cron'ed VACUUM
> that does not run as superuser, then it will not be able to VACUUM the
> system catalogs, and the problem will come back.

autovacuum is turned on in postgresql.conf file
log file shows a lot of messages every day that database is vacuumed.
I assume that it is running as user postgres.

I do'nt understand how autovacuum can avoid this: it does not perform vacuum
full so pg_shdepend ja my tables become
bloated again and again.

Andrus.



В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds
От: Glyn Astill
Дата:
Сообщение: Perc 3 DC