Re: WIP: Avoid creation of the free space map for small tables - Mailing list pgsql-hackers

On Sat, Dec 1, 2018 at 12:42 PM John Naylor <jcnaylor@gmail.com> wrote:
>
> On 11/29/18, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > On Thu, Nov 29, 2018 at 3:07 PM John Naylor <jcnaylor@gmail.com> wrote:
> >> Done. I tried adding it to several schedules, but for some reason
> >> vacuuming an empty table failed to truncate the heap to 0 blocks.
> >> Putting the test in its own group fixed the problem, but that doesn't
> >> seem ideal.
> >>
> >
> > It might be because it fails the should_attempt_truncation() check.
> > See below code:
> >
> > if (should_attempt_truncation(vacrelstats))
> > lazy_truncate_heap(onerel, vacrelstats, vac_strategy);
>
> I see. I think truncating the FSM is not essential to show either the
> old or new behavior -- I could skip that portion to enable running the
> test in a parallel group.
>
> >> Can you please repeat the copy test you have done above with
> >> fillfactor as 20 and 30?
> >
> > I will send the results in a separate email soon.
>
> I ran the attached scripts which populates 100 tables with either 4 or
> 8 blocks. The test tables were pre-filled with one tuple and vacuumed
> so that the FSMs were already created when testing the master branch.
> The patch branch was compiled with a threshold of 8, but testing
> inserts of 4 pages effectively simulates a threshold of 4. Config was
> stock, except for fsync = off. I took the average of 40 runs (2
> complete tests of 20 runs each) after removing the 10% highest and
> lowest:
>
> fillfactor=20
> # blocks        master          patch
> 4                       19.1ms          17.5ms
> 8                       33.4ms          30.9ms
>
> fillfactor=30
> # blocks        master          patch
> 4                       20.1ms          19.7ms
> 8                       34.7ms          34.9ms
>
> It seems the patch might be a bit faster with fillfactor=20, but I'm
> at a loss as to why that would be.
>

I see that in your previous tests also with patch, the performance was
slightly better.  One probable reason could be that for small tables
the total number of pages accessed via shared buffers is more without
the patch (probably 3 FSM pages + 4 relation).  With the patch, you
need to only access 4 relation pages.  The other overhead of patch
(retrying each page) seems to be compensated with FSM search.  I think
you can once check perf profiles to confirm the same.

> Previous testing with a higher
> threshold showed a significant performance penalty starting around 10
> blocks [1], but that used truncation rather than deletion, and had a
> fill-factor of 10.
>

Can you check whether the number of pages after test are the same with
and without a patch in this setup?




-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com