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

>From: Ideriha, Takeshi [mailto:ideriha.takeshi@jp.fujitsu.com]

>>About the new global-size based evicition(2), cache entry creation
>>becomes slow after the total size reached to the limit since every one
>>new entry evicts one or more old (=
>>not-recently-used) entries. Because of not needing knbos for each
>>cache, it become far realistic. So I added documentation of
>"catalog_cache_max_size" in 0005.
>
>Now I'm also trying to benchmark, which will be posted in another email.

According to recent comments by Andres and Bruce
maybe we should address negative cache bloat step by step 
for example by reviewing Tom's patch. 

But at the same time, I did some benchmark with only hard limit option enabled
and time-related option disabled, because the figures of this case are not provided in this thread.
So let me share it.

I did two experiments. One is to show negative cache bloat is suppressed.
This thread originated from the issue that negative cache of pg_statistics 
is bloating as creating and dropping temp table is repeatedly executed.
https://www.postgresql.org/message-id/20161219.201505.11562604.horiguchi.kyotaro%40lab.ntt.co.jp  
Using the script attached the first email in this thread, I repeated create and drop temp table at 10000 times.
(experiment is repeated 5 times. catalog_cache_max_size = 500kB. 
 compared master branch and patch with hard memory limit)

Here are TPS and CacheMemoryContext 'used' memory (total - freespace) calculated by MemoryContextPrintStats()
at 100, 1000, 10000 times of create-and-drop transaction. The result shows cache bloating is suppressed
after exceeding the limit (at 10000) but tps declines regardless of the limit.

number of tx (create and drop)       | 100  |1000    |10000 
-----------------------------------------------------------
used CacheMemoryContext  (master) |610296|2029256 |15909024
used CacheMemoryContext  (patch)  |755176|880552  |880592
-----------------------------------------------------------
TPS (master)                         |414   |407     |399
TPS (patch)                           |242   |225     |220


Another experiment is using Tomas's script posted while ago,
The scenario is do select 1 from multiple tables randomly (uniform distribution).
(experiment is repeated 5 times. catalog_cache_max_size = 10MB. 
 compared master branch and patch with only hard memory limit enabled)

Before doing the benchmark, I checked pruning is happened only at 10000 tables
using debug option. The result shows degradation regardless of before or after pruning. 
I personally still need hard size limitation but I'm surprised that the difference is so significant.

number of tables   | 100  |1000    |10000 
-----------------------------------------------------------
TPS (master)       |10966  |10654 |9099
TPS (patch)        |4491   |2099 |378

Regards,
Takeshi Ideriha