On Tue, Feb 1, 2022 at 12:07 PM Peter Smith <smithpb2...@gmail.com> wrote:
>
> On Sat, Jan 29, 2022 at 11:31 AM Andres Freund <and...@anarazel.de> wrote:
> >
> > Hi,
> >
> > Are there any recent performance evaluations of the overhead of row
> > filters? I
> > think it'd be good to get some numbers comparing:
> >
> > 1) $workload with master
> > 2) $workload with patch, but no row filters
> > 3) $workload with patch, row filter matching everything
> > 4) $workload with patch, row filter matching few rows
> >
> > For workload I think it'd be worth testing:
> > a) bulk COPY/INSERT into one table
> > b) Many transactions doing small modifications to one table
> > c) Many transactions targetting many different tables
> > d) Interspersed DDL + small changes to a table
> >
>
> I have gathered performance data for the workload case (a):
>
> HEAD 46743.75
> v74 no filters 46929.15
> v74 allow 100% 46926.09
> v74 allow 75% 40617.74
> v74 allow 50% 35744.17
> v74 allow 25% 29468.93
> v74 allow 0% 22540.58
>
> PSA.
>
> This was tested using patch v74 and synchronous pub/sub. There are 1M
> INSERTS for publications using differing amounts of row filtering (or
> none).
>
> Observations:
> - There seems insignificant row-filter overheads (e.g. viz no filter
> and 100% allowed versus HEAD).
> - The elapsed time decreases linearly as there is less data getting
> replicated.
>
FYI - attached are the test steps I used in case anyone wants to try
to reproduce these results.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
TEST STEPS - Workload case a
1. Run initdb pub and sub and start both postgres instances (use the nosync
postgresql.conf)
2. Run psql for both instances and create tables
CREATE TABLE test (key int, value text, data jsonb, PRIMARY KEY(key, value));
3. create the PUBLISHER on pub instance (e.g. choose from below depending on
filter)
CREATE PUBLICATION pub_1 FOR TABLE test;
-- 100% (no filter)
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 0); -- 100%
allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 250000); -- 75% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 500000); -- 50% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 750000); -- 25% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 1000000); -- 0% allowed
4. create the SUBSCRIBER on sub instance
CREATE SUBSCRIPTION sync_sub CONNECTION 'host=127.0.0.1 port=5432
dbname=postgres application_name=sync_sub' PUBLICATION pub_1;
5. On pub side modify the postgresql.conf on the publisher side and restart
\q quite psql
edit synchronous_standby_names = 'sync_sub'
restart the pub instance
6. Run psql (pub side) and perform the test run.
\timing
INSERT INTO test SELECT i, i::text, row_to_json(row(i)) FROM
generate_series(1,1000001)i;
select count(*) from test;
TRUNCATE test;
select count(*) from test;
repeat 6 for each test run.
~~
Repeat from step 1 for different filter case.
