Re: Redesigning checkpoint_segments - Mailing list pgsql-hackers

On Fri, Jan 30, 2015 at 7:58 PM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

On 01/30/2015 04:48 AM, Venkata Balaji N wrote:

I performed series of tests for this patch and would like to share the
results. My comments are in-line.

Thanks for the testing!

*Test 1 :*

In this test, i see removed+recycled segments = 3 (except for the first 3
checkpoint cycles) and has been steady through out until the INSERT
operation completed.

Actual calculation of CheckPointSegments = 3.2 ( is getting rounded up to 3
)

pg_xlog size is 128M and has increased to 160M max during the INSERT
operation.

shared_buffers = 128M
checkpoint_wal_size = 128M
min_recycle_wal_size = 80M
checkpoint_timeout = 5min

Hmm, did I understand correctly that pg_xlog peaked at 160MB, but most of the stayed at 128 MB? That sounds like it's working as designed; checkpoint_wal_size is not a hard limit after all.

Yes, the pg_xlog directory size peaked to 160MB at times and most of the time stayed at 128MB. I did make an observation in an other round of latest test, my observations are below.

b) Are the two GUCs, checkpoint_wal_size, and min_recycle_wal_size,
intuitive to set?

During my tests, I did not observe the significance of min_recycle_wal_size
parameter yet. Ofcourse, i had sufficient disk space for pg_xlog.

I would like to understand more about "min_recycle_wal_size" parameter. In
theory, i only understand from the note in the patch that if the disk space
usage falls below certain threshold, min_recycle_wal_size number of WALs
will be removed to accommodate future pg_xlog segments. I will try to test
this out. Please let me know if there is any specific test to understand
min_recycle_wal_size behaviour.

min_recycle_wal_size comes into play when you have only light load, so that checkpoints are triggered by checkpoint_timeout rather than checkpoint_wal_size. In that scenario, the WAL usage will shrink down to min_recycle_wal_size, but not below that. Did that explanation help? Can you suggest changes to the docs to make it more clear?

Thanks for the explanation. I see the below note from the patch, i think it should also say that minimum wal size on the disk will be "min_recycle_wal_size" during the light load and idle situations.

I think the name of the parameter name "min_recycle_wal_size" implies something slightly different. It does not give an impression that it is the minimum wal size on the disk during light loads. I agree with Josh Berkus that the parameter (min_recycle_wal_size) name must be something like "min_wal_size" which makes more sense.

+   <varname>wal_recycle_min_size</> puts a minimum on the amount of WAL files
+   recycled for future usage; that much WAL is always recycled for future use,
+   even if the system is idle and the WAL usage estimate suggests that little
+   WAL is needed.
+  </para>

Note : in wal.sgml, the parameter name is mentioned as "wal_recycle_min_size". That must be changed to min_recycle_wal_size.

Another round of test : I raised checkpoint_wal_size to 10512 MB which is about 10GB and kept min_recycle_wal_size at 128 MB (with checkpoint_timeout = 5min). The checkpoints timed out and started recycling about 2 GB segments regularly, below are the checkpoint logs -

I started loading the data of size more than 100GB.

TimeStamp=2015-02-05 10:22:40.323 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint complete: wrote 83998 buffers (64.1%); 0 transaction log file(s) added, 0 removed, 135 recycled; write=95.687 s, sync=25.845 s, total=121.866 s; sync files=18, longest=10.306 s, average=1.435 s; distance=2271524 KB, estimate=2300497 KB
TimeStamp=2015-02-05 10:25:38.875 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint starting: time
TimeStamp=2015-02-05 10:27:50.955 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint complete: wrote 83216 buffers (63.5%); 0 transaction log file(s) added, 0 removed, 146 recycled; write=96.951 s, sync=34.814 s, total=132.079 s; sync files=18, longest=9.535 s, average=1.934 s; distance=2229416 KB, estimate=2293388 KB
TimeStamp=2015-02-05 10:30:38.786 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint starting: time
TimeStamp=2015-02-05 10:32:20.332 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint complete: wrote 82409 buffers (62.9%); 0 transaction log file(s) added, 0 removed, 131 recycled; write=94.712 s, sync=6.516 s, total=101.545 s; sync files=18, longest=2.645 s, average=0.362 s; distance=2131805 KB, estimate=2277230 KB
TimeStamp=2015-02-05 10:35:38.788 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint starting: time
TimeStamp=2015-02-05 10:37:35.883 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint complete: wrote 87821 buffers (67.0%); 0 transaction log file(s) added, 0 removed, 134 recycled; write=99.461 s, sync=17.058 s, total=117.094 s; sync files=19, longest=9.022 s, average=0.897 s; distance=2339374 KB, estimate=2339374 KB
TimeStamp=2015-02-05 10:40:38.975 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint starting: time
TimeStamp=2015-02-05 10:42:46.789 GMT-10 DB= SID=54d2af22.65b4 User= LOG:  checkpoint complete: wrote 82975 buffers (63.3%); 0 transaction log file(s) added, 0 removed, 146 recycled; write=94.458 s, sync=33.025 s, total=127.814 s; sync files=19, longest=5.975 s, average=1.738 s; distance=2298657 KB, estimate=2335302 KB

My observations are :

1. As per your explanation, I also see pg_xlog size is not getting reduced to "min_recycled_wal_size" (128M) after the load operation is complete.
    I did a manual checkpoint and also, restarted the database, still, pg_xlog size stays at 7 GB. am i missing something here ?
   

2. checkpoint_wal_size has any upper limit ?

Please share your thoughts.

Regards,

Venkata Balaji N