Re: WAL partition overloaded--by autovacuum?

От: Kevin Grittner
Тема: Re: WAL partition overloaded--by autovacuum?
Дата: ,
Msg-id: 4C34259102000025000330B6@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: WAL partition overloaded--by autovacuum?  (Richard Yen)
Список: pgsql-performance

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

WAL partition overloaded--by autovacuum?  (Richard Yen, )
 Re: WAL partition overloaded--by autovacuum?  (Scott Marlowe, )
  Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
   Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Robert Haas, )
    Re: [Slony1-general] WAL partition overloaded--by autovacuum?  ("Kevin Grittner", )
     Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
      Re: [Slony1-general] WAL partition overloaded--by autovacuum?  ("Kevin Grittner", )
       Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
        Re: [Slony1-general] WAL partition overloaded--by autovacuum?  ("Kevin Grittner", )
      Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Tom Lane, )
       Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
     Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Tom Lane, )
      Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
   Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Greg Smith, )
 Re: WAL partition overloaded--by autovacuum?  (Scott Marlowe, )
 Re: WAL partition overloaded--by autovacuum?  (Scott Marlowe, )
 Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Mark Kirkwood, )
  Re: [Slony1-general] WAL partition overloaded--by autovacuum?  (Richard Yen, )
 Re: WAL partition overloaded--by autovacuum?  ("Kevin Grittner", )

Richard Yen wrote:

> the pg_xlog partition filled up (33GB)

> checkpoint_segments=16

> a sudden flurry of write activity

Was this database bulk-loaded in some way (like by restoring the
output of pg_dump, for example)?  If so, all rows inserted into all
tables would have the same (or very nearly the same) xmin value. At
some later time, virtually all tuples would need to be rewritten to
freeze them. This would be a logged operation, so WAL files would be
getting created rapidly.  If you have WAL archiving turned on, and
you can't copy the files out as fast as they're being created, this
might happen.

To avoid such a crushing mass freeze at an unpredictable time, we
always run VACUUM FREEZE ANALYZE on a bulk-loaded database before
turning on WAL archiving.

Of course, if this database wasn't bulk-loaded, this can't be your
problem....

-Kevin



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

От: Bruce Momjian
Дата:
Сообщение: Re: Weird XFS WAL problem
От: Richard Yen
Дата:
Сообщение: Re: [Slony1-general] WAL partition overloaded--by autovacuum?