Re: VACUUM ANALYZE blocking both reads and writes to a table

От: Alvaro Herrera
Тема: Re: VACUUM ANALYZE blocking both reads and writes to a table
Дата: ,
Msg-id: 20080630152515.GF10311@alvh.no-ip.org
(см: обсуждение, исходный текст)
Ответ на: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller)
Ответы: Re: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller)
Re: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller)
Список: pgsql-performance

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

VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller, )
 Re: VACUUM ANALYZE blocking both reads and writes to a table  (Alvaro Herrera, )
  Re: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller, )
  Re: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller, )
   Re: VACUUM ANALYZE blocking both reads and writes to a table  (Alvaro Herrera, )
    Re: VACUUM ANALYZE blocking both reads and writes to a table  (Tom Lane, )
     Re: VACUUM ANALYZE blocking both reads and writes to a table  (Alvaro Herrera, )
      Re: VACUUM ANALYZE blocking both reads and writes to a table  (Peter Schuller, )
    Re: VACUUM ANALYZE blocking both reads and writes to a table  (Alvaro Herrera, )

Peter Schuller wrote:

> Does anyone have input on why this could be happening? The PostgreSQL
> version is 8.2.4[1]. Am I correct in that it *should* not be possible
> for this to happen?

No.  VACUUM takes an exclusive lock at the end of the operation to
truncate empty pages.  (If it cannot get the lock then it'll just skip
this step.)  In 8.2.4 there was a bug that caused it to sleep
according to vacuum_delay during the scan to identify possibly empty
pages.  This was fixed in 8.2.5:

    revision 1.81.2.1
    date: 2007-09-10 13:58:50 -0400;  author: alvherre;  state: Exp;  lines: +6 -2;
    Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
    an exclusive lock on the table at this point, which we want to release as soon
    as possible.  This is called in the phase of lazy vacuum where we truncate the
    empty pages at the end of the table.

    An alternative solution would be to lower the vacuum delay settings before
    starting the truncating phase, but this doesn't work very well in autovacuum
    due to the autobalancing code (which can cause other processes to change our
    cost delay settings).  This case could be considered in the balancing code, but
    it is simpler this way.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


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

От: Mark Roberts
Дата:
Сообщение: Re: Does max size of varchar influence index size
От: John Beaver
Дата:
Сообщение: Re: sequence scan problem