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

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

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.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
>
> -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