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

Fri Mar 8 17:54:01 JST 2019

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.

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.

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: performance.png
Type: image/png
Size: 35078 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20190308/c699deee/attachment-0002.png>
-------------- next part --------------
export PGPORT=11000
#export PGPORT=11002
export PGDATABASE="test"
export sfile="select.sql"
export duration=30

grep "enable_shared_relcache" testdata/etc/pgpool.conf > tps.log

for n in 1 2 4 8 16 32 64 128 256
do
    (cd testdata;./startall)
    sleep 2
    pgbench -i
    cnt=1
    cp /dev/null $sfile

    while [ $cnt -le $n ]
    do
	psql -c "DROP TABLE t$cnt"
	psql -c "CREATE TABLE t$cnt (i int);"
	echo "SELECT * FROM t$cnt;" >> $sfile
	cnt=$(expr $cnt + 1)
    done

    echo "==== n=$n ====" >> tps.log

    for i in 1 2 3
    do
	pgbench  -C -T $duration -c 30 -n -f $sfile |grep excluding
    done | awk '{sum += $3} END {print sum/NR}' >> tps.log

    (cd testdata;./shutdownall)
done

-------------- next part --------------
A non-text attachment was scrubbed...
Name: raw-numbers.png
Type: image/png
Size: 60781 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20190308/c699deee/attachment-0003.png>