[pgpool-hackers: 3267] Re: [pgpool-committers: 5498] pgpool: Add new 'enable_shared_relcache' parameter.

[Takuma Hoshiai]

Ishii-san,

On Fri, 08 Mar 2019 17:54:01 +0900 (JST)
Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Hoshiai-san,
> 
> I have evaluated the performance with enable_shared_relcache =
> on/off. In short, I got up to x2 speedup with enable_shared_relcache =
> on. Conjurations!  Attached is a graph (performance.png) comparing
> enable_shared_relcache = off and on (speedup is normalized based on
> enable_shared_relcache = off). As you can see the speedup is maximum
> (x 2.056) at the number of tables = 32.

Thank you for your test. The result is more than I thought.

> Above table number 32, the speedup is decreasing. I initially expected
> the more tables, the more speedup. But reality is not as what I
> thought. I maybe interesting to know why this happened.

I think, it is possible that shmem_lock's waittime increase.
The number of tables accessed in one transaction increases, so the probability of shmem lock conflicted is increasing.
I will check it using flamegraph.

best regards,

-- 
Takuma Hoshiai <hoshiai at sraoss.co.jp> 

> Here are details how I did the benchmark.
> 
> - PostgreSQL 11.2 and Pgpool-II master branch head are used.
> - streaming replication is set up by pgpool_setup. All parameters are not changed.
> - standard pgbench is used.
> - changing number of tables from 1 to 256.
> - all tables are empty.
> - scripts SELECTs specified number of tables are generated and used by pgbench.
> - arguments for pgbench are: -C -T 30 -c 30 -n -f 
> - each pgbench run for 30 seconds, repeats for 3 times and the average TPS is used.
> - the script to run the benchmark is attached (bench.sh).
> 
> Also I attached another graph (raw-numbers.png), where raw TPS is
> shown along with without Pgpool-II (meaning pgbench directly connected
> to PostgreSQL). In this case Pgpool-II outperform PostgreSQL, due to
> connection pooling.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
> > Hi all,
> > 
> > I committed a new feature of shared relation cache on monday.
> > This feature is that relcache is stored in query cache.
> > 
> > relcache is the result of query executed internally by pgpool to refer to system catalog. 
> > pgpool store relcache at local cache in order not to execute query many times. 
> > But It stored at local cache only, so each child process must accessed system catalog using same query.
> > 
> > if enable_shared_relcache added by this feature is on, relcache store at query cache too.
> > when one child process executed query to refer to system catalog, 
> > other child processes can use this result from query cache.
> > 
> > This feature enables even if 'memory_cache_enabled' is off.
> > And basic setting of query cache is used existing parameters, 
> > but only relcache expiration on query cache use relation_expire.
> > It means that we can set different expiration on query cache 
> > both query that executed by user and relcache.
> > 
> > this feature is effective, for example, 
> > when num_init_children is large, child_max_connections is setting, postgres has many tables, etc.
> > 
> > Best regards,
> > 
> > On Mon, 25 Feb 2019 07:37:35 +0000
> > Takuma Hoshiai <hoshiai at sraoss.co.jp> wrote:
> > 
> >> Add new 'enable_shared_relcache' parameter.
> >> 
> >> The relation cache were stored in local cache of child processes, so all child processes executed same query to get relation cache.
> >> If enable_shared_relcache is on, the relation cache is stored in memory cache and all child process share it.
> >> It will expect to reduce the load that same query is executed.
> >> 
> >> Branch
> >> ------
> >> master
> >> 
> >> Details
> >> -------
> >> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=46917d5458a82c75e98b247a74b7ee1827666159
> >> 
> >> Modified Files
> >> --------------
> >> doc.ja/src/sgml/misc-config.sgml                   |  48 ++++++
> >> doc/src/sgml/misc-config.sgml                      |  29 ++++
> >> src/config/pool_config_variables.c                 |  10 ++
> >> src/include/pool_config.h                          |   3 +-
> >> src/include/query_cache/pool_memqcache.h           |   7 +-
> >> src/main/pgpool_main.c                             |   4 +-
> >> src/protocol/child.c                               |   5 +-
> >> src/protocol/pool_process_query.c                  |   3 +-
> >> src/query_cache/pool_memqcache.c                   | 151 +++++++++++++++--
> >> src/sample/pgpool.conf.sample                      |   5 +
> >> src/sample/pgpool.conf.sample-logical              |   5 +
> >> src/sample/pgpool.conf.sample-master-slave         |   5 +
> >> src/sample/pgpool.conf.sample-replication          |   5 +
> >> src/sample/pgpool.conf.sample-stream               |   5 +
> >> .../tests/025.enable_shared_relcache/test.sh       |  69 ++++++++
> >> src/utils/pool_process_reporting.c                 |   7 +-
> >> src/utils/pool_relcache.c                          | 186 ++++++++++++++++++++-
> >> 17 files changed, 516 insertions(+), 31 deletions(-)
> >> 
> > 
> > 
> > -- 
> > Takuma Hoshiai <hoshiai at sraoss.co.jp>
> > 
> > _______________________________________________
> > pgpool-hackers mailing list
> > pgpool-hackers at pgpool.net
> > http://www.pgpool.net/mailman/listinfo/pgpool-hackers