Re: Redesigning checkpoint_segments

Missed adding "pgsql-hackers" group while replying.

Regards,
Venkata Balaji N

On Thu, Feb 5, 2015 at 12:48 PM, Venkata Balaji N <nag1010(at)gmail(dot)com> wrote:

>
>
> On Fri, Jan 30, 2015 at 7:58 PM, Heikki Linnakangas <
> hlinnakangas(at)vmware(dot)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
>
>