Re: pg_xlog Concern

Поиск
Список
Период
Сортировка
От Koray Eyidoğan
Тема Re: pg_xlog Concern
Дата
Msg-id CAOrEZuhvfS5jJa3Wgmoo-wzjeaNzaNF3MLWUGLMBwPeYKgQc8Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: pg_xlog Concern  (Torsten Förtsch <torsten.foertsch@gmx.net>)
Ответы Re: pg_xlog Concern
Список pgsql-general
Hi,

Any kind of slowness on your archive directory may cause the archiving process fall behind thus accumulating segment files in your cluster's pg_xlog directory.

I assume that you are on PostgreSQL 9.4. Could you please check your archiver status by typing "select * from pg_catalog.pg_stat_archiver;" in psql ? If the last_archived_wal column's value is not so close to your current xlog location, then it probably means a slow write speed on your archive path compared to your pg_xlog path.

You can check your current xlog file by typing "select pg_xlogfile_name(pg_current_xlog_location());" in psql.

If you are not on PostgreSQL 9.4, you can check your archiver status by typing "ps -ef | grep archiver" in your shell.

I've also assumed that you are not using replication slots.

As far as I know, long running transactions (just sitting in idle) won't affect pg_xlog directory size. Correct me if I'm wrong.

Hope that helps.

Have a nice day.

Koray




On Mon, May 18, 2015 at 5:00 PM, Torsten Förtsch <torsten.foertsch@gmx.net> wrote:
On 18/05/15 13:44, Sachin Srivastava wrote:
> But currently my pg_xlog size is 60 GB and there are 3740 WAL file in
> this folder and in Last week this was 400 GB(pg_xlog folder) and WAL
> file were approx. 30000. Due to archiving pg_xlog folder size is
> decreasing now but it’s taking one week to come in normal size.

Any chance you have unfinished transactions running for a week?

pg_stat_activity should be able to tell you that.

Torsten


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Optimizing a read-only database
Следующее
От: François Battail
Дата:
Сообщение: Re: Optimizing a read-only database