Missed adding "pgsql-hackers" group while replying. Regards, Venkata Balaji N
On Thu, Feb 5, 2015 at 12:48 PM, Venkata Balaji N <nag1...@gmail.com> wrote: > > > On Fri, Jan 30, 2015 at 7:58 PM, Heikki Linnakangas < > hlinnakan...@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 > >
