2

I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby.

We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time.

The standby desynchronizes because the pg_xlog becomes full.

I could just increase the FS but I would like to understand.

I found out that with this version of PostgreSQL, the restartpoints are triggered only because of the checkpoint_timeout (5 min) parameter on the standby.

The primary checkpoints a lot (every 5 sec or so) because of the WAL activity (triggered by checkpoint_segments (10) ). The result is that my standby doesn't recycle its WAL quickly enough and the FS becomes full before the next checkpoint.

Since checkpoints every 5s is "a bit too much", I tried to set checkpoint_segments to 50 on the primary and checkpoint_timeout to 30s in the standby. It's ugly but somehow the checkpoint timing are in synch and the standby doesn't "desync".

My questions are:

  • In 9.1.16 and before. How did you handle restart point when it was triggered only by checkpoint_timeout?

  • Can I do something else PostgreSQL wise than this ugly workaround ?

  • I don't have much experience with pg in real world. my pg_xlog FS is 5G. Is it a normal size or is it way too small ?

PS: Feel free to ask me more info, it's my first post.

PS2: the article I found about checkpoint_timeout on standby http://www.postgresql.org/message-id/AANLkTimpudRqHf8kFfQZxyMHsULYo79qwK5PCXxP21gt@mail.gmail.com

1

To answer your questions:

  1. Set the checkpoint_timeout lower, as you did, to 30 seconds or so. I made sure to enable log_checkpoints in my postgresql.conf and used check_postgres.pl to monitor the number of segments and replication lag. You can also set checkpoint_completion_target=0 to adjust the checkpointing behavior as well. But really, on a replica, checkpoint_timeout is really the only way to keep the pg_xlog directory down in size on a replica. Additional reference here: pgsql bugs #7801

  2. Adjusting checkpoint_timeout is unfortunately the best option. There are some tips on dealing with pg_xlog filling up in this blog post: Solving pg_xlog out of disk space problems, but these mostly apply to a primary that's run out of space, and the panic related to that.

  3. PostgreSQL WAL segments take up approximately this much room, according to the documentation for 9.1 WAL Configuration:

    (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files

    So if you do the math like this for example, with a checkpoint_completion_target=0.9 and checkpoint_segments = 50:

    (2 + 0.9) * 50 + 1 = 146 * 16MB = 2336 MB

    Which is only about 45% of your 5 GB of your pg_xlog filesystem. So long as you aren't getting checkpoint warnings in your logs, and the space isn't in danger of being completely used up, 5 GB should be fine. Using these formulas, you should be able to appropriately size your pg_xlog filesystem for your usage.

    Greg Smith has a lovely chart in PostgreSQL 9.0 High Performance, referenced here: Server Configuration Tuning Practices. I would also recommend the book as a reference, even though it's a bit old, because a lot of the information in it is still quite applicable.

Hope that helps. =)

  • Thank you for the answer. It helps a lot. I looked at the source and found out that the checkpoint_segments is used on the standby too but I am understand the impression that the length of the restant point depends upon the value of checkpoint_timeout. I need to verify this. I Will post again. – Mael Sep 16 '15 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.