Re: WIP: Avoid creation of the free space map for small tables - Mailing list pgsql-hackers

On Fri, Jan 11, 2019 at 3:54 AM John Naylor <john.naylor@2ndquadrant.com> wrote:
>
> On Wed, Jan 9, 2019 at 10:50 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > Thanks, Mithun for performance testing, it really helps us to choose
> > the right strategy here.  Once John provides next version, it would be
> > good to see the results of regular pgbench (read-write) runs (say at
> > 50 and 300 scale factor) and the results of large copy.  I don't think
> > there will be any problem, but we should just double check that.
>
> Attached is v12 using the alternating-page strategy. I've updated the
> comments and README as needed. In addition, I've

Below are my performance tests and numbers
Machine : cthulhu

Tests and setups
Server settings:
max_connections = 200
shared_buffers=8GB
checkpoint_timeout =15min
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
min_wal_size=15GB and max_wal_size=20GB.

pgbench settings:
-----------------------
read-write settings (TPCB like tests)
./pgbench -c $threads -j $threads -T $time_for_reading -M prepared postgres

scale factor 50 -- median of 3 TPS
clients       v12-patch          base patch                % diff
1               826.081588       834.328238           -0.9884179421
16             10805.807081   10800.662805        0.0476292621
32             19722.277019   19641.546628        0.4110185034
64             30232.681889   30263.616073       -0.1022157561

scale factor 300 -- median of 3 TPS
clients       v12-patch          base patch                % diff
1              813.646062       822.18648              -1.038744641
16           11379.028702    11277.05586            0.9042505709
32           21688.084093    21613.044463          0.3471960192
64           36288.85711      36348.6178             -0.1644098005


Copy command
Test: setup
 ./psql -d postgres -c "COPY pgbench_accounts TO '/mnt/data-mag/mithun.cy/fsmbin/bin/dump.out' WITH csv"
 ./psql -d postgres -c "CREATE UNLOGGED TABLE pgbench_accounts_ulg (LIKE pgbench_accounts) WITH (fillfactor = 100);"
Test run:
TRUNCATE TABLE pgbench_accounts_ulg;
\timing
COPY pgbench_accounts_ulg FROM '/mnt/data-mag/mithun.cy/fsmbin/bin/dump.out' WITH csv;
\timing

execution time in ms. (scale factor indicates size of pgbench_accounts)
scale factor       v12-patch        base patch       % diff
300                   77166.407       77862.041     -0.8934186557
50                     13329.233      13284.583       0.3361038882