RE: Protect syscache from bloating with negative cache entries - Mailing list pgsql-hackers

Hello, thank you for updating the patch.


>From: Kyotaro HORIGUCHI [mailto:horiguchi.kyotaro@lab.ntt.co.jp]
>At Thu, 4 Oct 2018 04:27:04 +0000, "Ideriha, Takeshi"
><ideriha.takeshi@jp.fujitsu.com> wrote in
><4E72940DA2BF16479384A86D54D0988A6F1BCB6F@G01JPEXMBKW04>
>> >As a *PoC*, in the attached patch (which applies to current master),
>> >size of CTups are counted as the catcache size.
>> >
>> >It also provides pg_catcache_size system view just to give a rough
>> >idea of how such view looks. I'll consider more on that but do you have any opinion
>on this?
>> >
>...
>> Great! I like this view.
>> One of the extreme idea would be adding all the members printed by
>> CatCachePrintStats(), which is only enabled with -DCATCACHE_STATS at this
>moment.
>> All of the members seems too much for customers who tries to change
>> the cache limit size But it may be some of the members are useful
>> because for example cc_hits would indicate that current cache limit size is too small.
>
>The attached introduces four features below. (But the features on relcache and
>plancache are omitted).
I haven't looked into the code but I'm going to do it later.

Right now It seems to me that focusing on catalog cache invalidation and its stats a quick route
to commit this feature. 

>1. syscache stats collector (in 0002)
>
>Records syscache status consists of the same columns above and "ageclass"
>information. We could somehow triggering a stats report with signal but we don't want
>take/send/write the statistics in signal handler. Instead, it is turned on by setting
>track_syscache_usage_interval to a positive number in milliseconds.

I agreed. Agecalss is important to tweak the prune_min_age. 
Collecting stats is heavy at every stats change

>2. pg_stat_syscache view.  (in 0002)
>
>This view shows catcache statistics. Statistics is taken only on the backends where
>syscache tracking is active.
>
>>  pid  | application_name |    relname     |            cache_name
>|   size   |        ageclass         |         nentries
>>
>------+------------------+----------------+-----------------------------------+----------
>+-------------------------+---------------------------
>>  9984 | psql             | pg_statistic   | pg_statistic_relid_att_inh_index  |
>12676096 | {30,60,600,1200,1800,0} | {17660,17310,55870,0,0,0}
>
>Age class is the basis of catcache truncation mechanism and shows the distribution
>based on elapsed time since last access. As I didn't came up an appropriate way, it is
>represented as two arrays.  Ageclass stores maximum age for each class in seconds.
>Nentries holds entry numbers correnponding to the same element in ageclass. In the
>above example,
>
>     age class  : # of entries in the cache
>   up to   30s  : 17660
>   up to   60s  : 17310
>   up to  600s  : 55870
>   up to 1200s  : 0
>   up to 1800s  : 0
>   more longer  : 0
>
> The ageclass is {0, 0.05, 0.1, 1, 2, 3}th multiples of  cache_prune_min_age on the
>backend.

I just thought that the pair of ageclass and nentries can be represented as
json or multi-dimensional array but in virtual they are all same and can be converted each other
using some functions. So I'm not sure which representaion is better one.

>3. non-transactional GUC setting (in 0003)
>
>It allows setting GUC variable set by the action GUC_ACTION_NONXACT(the name
>requires condieration) survive beyond rollback. It is required by remote guc setting to
>work sanely. Without the feature a remote-set value within a trasction will disappear
>involved in rollback. The only local interface for the NONXACT action is
>set_config(name, value, is_local=false, is_nonxact = true). pg_set_backend_guc()
>below works on this feature.

TBH, I'm not familiar with around this and I may be missing something.
In order to change the other backend's GUC value,
is ignoring transactional behevior always necessary? When transaction of GUC setting
is failed and rollbacked, if the error message is supposeed to be reported I thought 
just trying the transaction again is enough.

>4. pg_set_backend_guc() function.
>
>Of course syscache statistics recording consumes significant amount of time so it
>cannot be turned on usually. On the other hand since this feature is turned on by GUC,
>it is needed to grab the active client connection to turn on/off the feature(but we
>cannot). Instead, I provided a means to change GUC variables in another backend.
>
>pg_set_backend_guc(pid, name, value) sets the GUC variable "name"
>on the backend "pid" to "value".
>
>
>
>With the above tools, we can inspect catcache statistics of seemingly bloated process.
>
>A. Find a bloated process pid using ps or something.
>
>B. Turn on syscache stats on the process.
>  =# select pg_set_backend_guc(9984, 'track_syscache_usage_interval', '10000');
>
>C. Examine the statitics.
>
>=# select pid, relname, cache_name, size from pg_stat_syscache order by size desc
>limit 3;
> pid  |   relname    |            cache_name            |   size
>------+--------------+----------------------------------+----------
> 9984 | pg_statistic | pg_statistic_relid_att_inh_index | 32154112
> 9984 | pg_cast      | pg_cast_source_target_index      |     4096
> 9984 | pg_operator  | pg_operator_oprname_l_r_n_index  |     4096
>
>
>=# select * from pg_stat_syscache where cache_name =
>'pg_statistic_relid_att_inh_index'::regclass;
>-[ RECORD 1 ]---------------------------------
>pid         | 9984
>relname     | pg_statistic
>cache_name  | pg_statistic_relid_att_inh_index
>size        | 11026176
>ntuples     | 77950
>searches    | 77950
>hits        | 0
>neg_hits    | 0
>ageclass    | {30,60,600,1200,1800,0}
>nentries    | {17630,16950,43370,0,0,0}
>last_update | 2018-10-17 15:58:19.738164+09

The output of this view seems good to me.

I can imagine this use case. Does the use case of setting GUC locally never happen? 
I mean can the setting be locally changed?

Regards,
Takeshi Ideriha