Re: Protect syscache from bloating with negative cache entries

At Fri, 29 Mar 2019 17:24:40 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20190329(dot)172440(dot)199616830(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> I ran three artificial test cases. The database is created by
> gen_tbl.pl. Numbers are the average of the fastest five runs in
> successive 15 runs.
>
> Test cases are listed below.
>
> 1_0. About 3,000,000 negative entries are created in pg_statstic
> cache by scanning that many distinct columns. It is 3000 tables
> * 1001 columns. Pruning scans happen several times while a run
> but no entries are removed. This emulates the bloating phase of
> cache. catalog_cache_prune_min_age is default (300s).
> (access_tbl1.pl)
>
> 1_1. Same to 1_0 except that catalog_cache_prune_min_age is 0,
> which means turning off.
>
> 2_0. Repeatedly access 1001 of the 3,000,000 entries 6000
> times. This emulates the stable cache case without having
> pruning. catalog_cache_prune_min_age is default (300s).
> (access_tbl2.pl)
>
> 2_1. Same to 2_0 except that catalog_cache_prune_min_age is 0,
> which means turning off.
>
> 3_0. Scan over the 3,000,000 entries twice with setting prune_age
> to 10s. A run takes about 18 seconds on my box so fair amount
> of old entries are removed. This emulates the stable case with
> continuous pruning. (access_tbl3.pl)
>
> 2_1. Same to 3_0 except that catalog_cache_prune_min_age is 0,
> which means turning off.
>
>
> The result follows.
>
> | master | LRU | Full |Full-mod|
> -----|--------+--------+--------+--------+
> 1_0 | 17.287 | 17.370 | 17.255 | 16.623 |
> 1_1 | 17.287 | 17.063 | 16.336 | 17.192 |
> 2_0 | 15.695 | 18.769 | 18.563 | 15.527 |
> 2_1 | 15.695 | 18.603 | 18.498 | 18.487 |
> 3_0 | 26.576 | 33.817 | 34.384 | 34.971 |
> 3_1 | 26.576 | 27.462 | 26.202 | 26.368 |
>
> The result of 2_0 and 2_1 seems strange, but I show you the
> numbers at the present.
>
> - Full-scan seems to have the smallest impact when turned off.
>
> - Full-scan-mod seems to perform best in total. (as far as
> Full-mod-2_0 is wrong value..)
>
> - LRU doesn't seem to outperform full scanning.

I had another.. unstable.. result.

| master | LRU | Full |Full-mod|
-----|--------+--------+--------+--------+
1_0 | 16.312 | 16.540 | 16.482 | 16.348 |
1_1 | 16.312 | 16.454 | 16.335 | 16.232 |
2_0 | 16.710 | 16.954 | 17.873 | 17.345 |
2_1 | 16.710 | 17.373 | 18.499 | 17.563 |
3_0 | 25.010 | 33.031 | 33.452 | 33.937 |
3_1 | 25.010 | 24.784 | 24.570 | 25.453 |

Normalizing on master's result and rounding off to 1.0%, it looks
as:

| master | LRU | Full |Full-mod| Test description
-----|--------+--------+--------+--------+-----------------------------------
1_0 | 100 | 101 | 101 | 100 | bloating. pruning enabled.
1_1 | 100 | 101 | 100 | 100 | bloating. pruning disabled.
2_0 | 100 | 101 | 107 | 104 | normal access. pruning enabled.
2_1 | 100 | 104 | 111 | 105 | normal access. pruning disabled.
3_0 | 100 | 132 | 134 | 136 | pruning continuously running.
3_1 | 100 | 99 | 98 | 102 | pruning disabled.

I'm not sure why the 2_1 is slower than 2_0, but LRU impacts
least if the numbers are right.

I will investigate the strange behavior using profiler.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center