Re: Protect syscache from bloating with negative cache entries

At Mon, 01 Apr 2019 11:05:32 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20190401(dot)110532(dot)102998353(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> 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)
> >
> > 3_1. Same to 3_0 except that catalog_cache_prune_min_age is 0,
> > which means turning off.
..
> I had another.. unstable.. result.

dlist_move_head is used every time an entry is accessed. It moves
the accessed element to the top of bucket expecting that
subsequent access become faster - a kind of LRU maintenance. But
the mean length of a bucket is 2 so dlist_move_head is too
complex than following one step of link. So I removed it in
pruning patch.

I understand I cannot get rid of noise a far as I'm poking the
feature from client via communication and SQL layer.

The attached extension surgically exercises
SearchSysCache3(STATRELATTINH) in the almost pattern with the
benchmarks taken last week. I believe that that gives far
reliable numbers. But still the number fluctuates by up to about
10% every trial, and the difference among the methods is under
the fluctulation. I'm tired.. But this still looks somewhat wrong.

ratio in the following table is the percentage to the master for
the same test. master2 is a version that removed the
dlink_move_head from master.

binary | test | count | avg | stddev | ratio
---------+------+-------+---------+--------+--------
master | 1_0 | 5 | 7841.42 | 6.91
master | 2_0 | 5 | 3810.10 | 8.51
master | 3_0 | 5 | 7826.17 | 11.98
master | 1_1 | 5 | 7905.73 | 5.69
master | 2_1 | 5 | 3827.15 | 5.55
master | 3_1 | 5 | 7822.67 | 13.75
---------+------+-------+---------+--------+--------
master2 | 1_0 | 5 | 7538.05 | 16.65 | 96.13
master2 | 2_0 | 5 | 3927.05 | 11.58 | 103.07
master2 | 3_0 | 5 | 7455.47 | 12.03 | 95.26
master2 | 1_1 | 5 | 7485.60 | 9.38 | 94.69
master2 | 2_1 | 5 | 3870.81 | 5.54 | 101.14
master2 | 3_1 | 5 | 7437.35 | 9.91 | 95.74
---------+------+-------+---------+--------+--------
LRU | 1_0 | 5 | 7633.57 | 9.00 | 97.35
LRU | 2_0 | 5 | 4062.43 | 5.90 | 106.62
LRU | 3_0 | 5 | 8340.51 | 6.12 | 106.57
LRU | 1_1 | 5 | 7645.87 | 13.29 | 96.71
LRU | 2_1 | 5 | 4026.60 | 7.56 | 105.21
LRU | 3_1 | 5 | 8400.10 | 19.07 | 107.38
---------+------+-------+---------+--------+--------
Full | 1_0 | 5 | 7481.61 | 6.70 | 95.41
Full | 2_0 | 5 | 4084.46 | 14.50 | 107.20
Full | 3_0 | 5 | 8166.23 | 14.80 | 104.35
Full | 1_1 | 5 | 7447.20 | 10.93 | 94.20
Full | 2_1 | 5 | 4016.88 | 8.53 | 104.96
Full | 3_1 | 5 | 8258.80 | 7.91 | 105.58
---------+------+-------+---------+--------+--------
FullMod | 1_0 | 5 | 7291.80 | 14.03 | 92.99
FullMod | 2_0 | 5 | 4006.36 | 7.64 | 105.15
FullMod | 3_0 | 5 | 8143.60 | 9.26 | 104.06
FullMod | 1_1 | 5 | 7270.66 | 6.24 | 91.97
FullMod | 2_1 | 5 | 3996.20 | 13.00 | 104.42
FullMod | 3_1 | 5 | 8012.55 | 7.09 | 102 43

So "Full (scan) Mod" wins again, or the diffence is under error.

I don't think this level of difference can be a reason to reject
this kind of resource saving mechanism. LRU version doesn't seem
particularly slow but also doesn't seem particularly fast for the
complexity. FullMod version doesn't look differently.

So it seems to me that the simplest "Full" version wins. The
attached is rebsaed version. dlist_move_head(entry) is removed as
mentioned above in that patch.

The third and fourth attached are a set of script I used.

$ perl gen_tbl.pl | psql postgres
$ run.sh > log.txt

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center