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 20080630185848.GB18252@alvh.no-ip.org
обсуждение исходный текст
Ответ на Re: VACUUM ANALYZE blocking both reads and writes to a table  (Alvaro Herrera)
Список 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, )
Alvaro Herrera wrote:
> Peter Schuller wrote:
> > Actually, while on the topic:
> >
> > >     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.
> >
> > Even with the fix the lock is held. Is the operation expected to be
> > "fast" (for some definition of "fast") and in-memory, or is this
> > something that causes significant disk I/O and/or scales badly with
> > table size or similar?
>
> It is fast.

To elaborate: it scans the relation backwards and makes note of how many
are unused.  As soon as it finds a non-empty one, it stops scanning.
Typically this should be quick.  It is not impossible that there are a
lot of empty blocks at the end though, but I have never heard a problem
report about this.

It could definitely cause I/O though.

> > I.e., is this enough that, even without the .4 bug, one should not
> > really consider VACUUM ANALYZE non-blocking with respect to other
> > transactions?
>
> You should consider it non-blocking.

The lock in conditionally acquired: as I said earlier, the code would
rather skip this part than block.  So if there's some other operation
going on, there's no lock held at all.  If this grabs the lock, then
other operations are going to block behind it, but the time holding the
lock should be short.  Note, however, that sleeping for 20ms or more
because of vacuum_delay (the bug fixed above) clearly falls out of this
category, and easily explains the behavior you're seeing with 8.2.4.

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

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

Предыдущее
От: John Beaver
Дата:
Сообщение: Re: sequence scan problem
Следующее
От: "Emiliano Leporati"
Дата:
Сообщение: un-understood index performance behaviour