Re: shared-memory based stats collector

[Kyotaro Horiguchi]

At Tue, 16 Mar 2021 16:44:38 +0900 (JST), Kyotaro Horiguchi 
<horikyota....@gmail.com> wrote in 
> At Mon, 15 Mar 2021 19:04:29 -0700, Andres Freund <and...@anarazel.de> wrote 
> in 
> > Do you have some benchmarks that you used to verify performance?
> 
> https://www.postgresql.org/message-id/20201008.160326.2246946707652981235.horikyota.ntt%40gmail.com
> 
> It is using pgbench, with 800 clients with 20 threads.
> 
> Graphs of performance gain/loss from the master for the following
> benchmarks are attached.
> 
> > - Fetching 1 tuple from 1 of 100 tables from 100 to 800 clients.
> > - Fetching 1 tuple from 1 of 10 tables from 100 to 800 clients.
> 
> v36 showed about 60% of degradation (TPS redued to 1/3 of master) at
> 600 clients, but it has been dissapeared as of v39. The graphs are of
> v39. I'm asking for the script that was used for the benchark and will
> send them later.

This is that.

create_tables.sh: creates tables for benchmarking.
simple_use_file_ac_1000.sql: randomly selects 1 tuple from a table.

$ createdb benchdb
$ ./create_tables.sh benchdb 100000    # the case of 100000 tables
$ pgbench benchdb -n -c100 -j20 -T300 -r P 10 -f simple_use_file_ac_1000.sql

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

#! /bin/sh
#
# Usage: create_tables.sh <dbname> <# of tables>

PSQL=`which psql`
CREATEOBJECTLOG='createobjects.log'
DB_NAME=$1
TBL_NUM=$2

create_objects ()
{
        cat << EOF | psql -d ${DB_NAME} >> ${CREATEOBJECTLOG}
\timing on

DO \$\$
DECLARE
        k int;
        tablename text;
BEGIN
        FOR k IN 1 .. ${TBL_NUM} LOOP
                tablename := 'tbl_' || k;
                EXECUTE 'CREATE TABLE ' || tablename || ' (
                        big01 bigint,
                        big02 bigint,
                        big03 bigint)';
        END LOOP;
END;
\$\$ LANGUAGE plpgsql;
EOF
}

time create_objects

        
 

\set cnt1 random(1, 100)

BEGIN;
SELECT big01 FROM tbl_:cnt1 LIMIT 1;
END;