Thread: postgres 9.4.5 - pg_serial not decreasing

postgres 9.4.5 - pg_serial not decreasing

From
Alexandre Garcia
Date:
Hello,

Few weeks ago our customer experienced some spikes on his Postgres 9.4.5 database. We've been alerted of a significant disk usage increase and pg_serial seemed to be the culprit. 

We noticed a lot of those errors in the logs 

" could not truncate directory "pg_serial": apparent wraparound "

No more errors now but the pg_serial is still 8G big and doesn't seem to decrease.
The only thing I could find related to this was an old bug from 2011 which might be fixed now.

We wonder what is responsible for cleaning up the old transactions in pg_serial and how it could dysfunction in this case.

Would you have any clue ?

Cheers,

Re: postgres 9.4.5 - pg_serial not decreasing

From
Kevin Grittner
Date:
On Tue, Jun 7, 2016 at 1:23 PM, Alexandre Garcia <alexandre@vmfarms.com> wrote:

> Few weeks ago our customer experienced some spikes on his Postgres 9.4.5
> database. We've been alerted of a significant disk usage increase and
> pg_serial seemed to be the culprit.
>
> We noticed a lot of those errors in the logs
>
> " could not truncate directory "pg_serial": apparent wraparound "
>
> No more errors now but the pg_serial is still 8G big and doesn't seem to
> decrease.
> The only thing I could find related to this was an old bug from 2011 which
> might be fixed now.
>
> We wonder what is responsible for cleaning up the old transactions in
> pg_serial and how it could dysfunction in this case.

Could you check your logs to see whether you got a warning like this?:

  memory for serializable conflict tracking is nearly exhausted

With a hint like this?:

  There might be an idle transaction or a forgotten prepared
transaction causing this.

Please check pg_stat_activity and pg_prepared_xacts for very old
transactions.  You should not see the errors you are reporting nor
the warning I mentioned unless a serializable transaction remains
active long enough for about 1 billion transaction IDs to be
consumed.

Hm.  WARNING is probably not the right level for that, since that
might not show in the log.  It should probably be changed to LOG.
Even better, now that filenames can go past 4 hex digits, we should
eliminate the 1 billion limit for this.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: postgres 9.4.5 - pg_serial not decreasing

From
Alexandre Garcia
Date:
Hey Kevin,

Thanks for your answer.
I could not find the warning you mentioned either from the old logs when this occurred (3 weeks ago) or in the logs from the last few days.

No old transactions are sitting in pg_stat_activity nor pg_prepared_xacts. But since it was few weeks ago, it might have been the case before.
But that doesn't explain why that directory is still so big.

Cheers,





On Tue, Jun 7, 2016 at 4:01 PM Kevin Grittner <kgrittn@gmail.com> wrote:
On Tue, Jun 7, 2016 at 1:23 PM, Alexandre Garcia <alexandre@vmfarms.com> wrote:

> Few weeks ago our customer experienced some spikes on his Postgres 9.4.5
> database. We've been alerted of a significant disk usage increase and
> pg_serial seemed to be the culprit.
>
> We noticed a lot of those errors in the logs
>
> " could not truncate directory "pg_serial": apparent wraparound "
>
> No more errors now but the pg_serial is still 8G big and doesn't seem to
> decrease.
> The only thing I could find related to this was an old bug from 2011 which
> might be fixed now.
>
> We wonder what is responsible for cleaning up the old transactions in
> pg_serial and how it could dysfunction in this case.

Could you check your logs to see whether you got a warning like this?:

  memory for serializable conflict tracking is nearly exhausted

With a hint like this?:

  There might be an idle transaction or a forgotten prepared
transaction causing this.

Please check pg_stat_activity and pg_prepared_xacts for very old
transactions.  You should not see the errors you are reporting nor
the warning I mentioned unless a serializable transaction remains
active long enough for about 1 billion transaction IDs to be
consumed.

Hm.  WARNING is probably not the right level for that, since that
might not show in the log.  It should probably be changed to LOG.
Even better, now that filenames can go past 4 hex digits, we should
eliminate the 1 billion limit for this.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: postgres 9.4.5 - pg_serial not decreasing

From
Kevin Grittner
Date:
On Tue, Jun 7, 2016 at 3:24 PM, Alexandre Garcia <alexandre@vmfarms.com> wrote:

> No old transactions are sitting in pg_stat_activity nor pg_prepared_xacts.
> But since it was few weeks ago, it might have been the case before.
> But that doesn't explain why that directory is still so big.

When the SERIALIZABLE transaction isolation level was upgraded to
truly serializable behavior in release 9.1, the committer at the
time insisted on leaving the files in the pg_serial directory on
shutdown and startup in case anyone wanted to dig through them for
debugging purposes -- they have no operational value across
restarts.  If you reached wraparound because of a very old
transaction, the directory should slowly shrink again as
transaction IDs advance, and the safe thing to do (after confirming
this behavior) would be to just let that happen.  Theoretically,
deleting the files in that directory WHILE THE DATABASE SERVICE IS
STOPPED should be safe, but I have never done that, and would not
recommend it unless the disk space issue is critical because of it
and you make a backup of the directory first.  Direct modification
of any internal files like that should always be considered high
risk, and that risk carefully balanced against the expected
benefit.

Hopefully you can see a gap in the file numbers, and see that it is
expanding over time, and can just let the problem sort itself out.

You may want to add some sort of monitoring for old prepared
transactions and idle transactions to prevent a recurrence.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company