Re: Too many WAL(s) despite low transaction

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Too many WAL(s) despite low transaction
Дата
Msg-id 4D9597CB020000250003C0FE@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: Too many WAL(s) despite low transaction  (Selva manickaraja <mavles78@gmail.com>)
Список pgsql-admin
Selva manickaraja <mavles78@gmail.com> wrote:

> Since the production database is running,  I plan to do now is
> this
>
> 1. Set archive_timeout = 20m (Does the change require db restart
> to take effect?)

You'd better make that '20min'.  Reload should suffice.

> 2. Set  autovacuum=on and track_count=on (Does the change require
> db restart to take effect?)

Reload should suffice.

>     Does that mean we are running autovacuum?

Autovacuum is always there to prevent transaction ID wraparound, but
you really should be using it with at least the default
aggressiveness in almost all environments.

> 3. Run VACUUM FREEZE ANALYZE since bulk loading was done earlier.
> (Can this be done while the db is active and on production?)

It can be done while the server is running, but be prepared for a
massive WAL volume and a likely-noticeable performance impact.  We
usually do our bulk loads with archiving off and run VACUUM FREEZE
ANALYZE before turning archiving on and letting the users in.
Otherwise, since everything in a bulk load will have the same xmin,
or very close to that, autovacuum can kick in and tank performance
and glut the WAL streaming for a long time at some unpredictable
moment -- although that moment is most likely to be in the midst of
high transaction volume.

> Is this a workable action to achieve the result required?

Those are all sound like good ideas, given your situation.

As mentioned elsewhere on the thread, WAL file compression can help
quite a bit.  That's especially true if you filter the WAL through
pg_clearxlogtail or pg_lesslog before compression.  The issue there
is that WAL files are reused without clearing the tail of the file,
so if you don't use one of these programs before compression a file
which is switched out based on time, before it's full, will probably
take just as much space as a full file.  With pg_clearxlogtail an
"empty" WAL file will gzip to 16KB (three orders of magnitude better
than uncompressed).  I haven't used pg_lesslog but it might do even
better.

-Kevin

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

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Two way replication
Следующее
От: Rajendra prasad
Дата:
Сообщение: Re: Index size growing