Re: WIP: Avoid creation of the free space map for small tables

On Fri, Jan 11, 2019 at 3:54 AM John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
wrote:
>
> On Wed, Jan 9, 2019 at 10:50 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)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

So for large table tests do not show any considerable performance variance
from base code!

On Fri, Jan 11, 2019 at 3:54 AM John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
wrote:

> On Wed, Jan 9, 2019 at 10:50 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)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
>
> -handled a possible stat() call failure during pg_upgrade
> -added one more assertion
> -moved the new README material into a separate paragraph
> -added a comment to FSMClearLocalMap() about transaction abort
> -corrected an outdated comment that erroneously referred to extension
> rather than creation
> -fleshed out the draft commit messages
>

--
Thanks and Regards
Mithun Chicklore Yogendra
EnterpriseDB: http://www.enterprisedb.com