Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

Amit Kapila <amit.kapila16@gmail.com>

From: Amit Kapila <amit.kapila16@gmail.com>

To: Mahendra Singh Thalor <mahi6run@gmail.com>

Cc: Dilip Kumar <dilipbalaut@gmail.com>, Kuntal Ghosh <kuntalghosh.2007@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>

Date: 2020-05-29T10:22:19Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Tighten the concurrent abort check during decoding.
- 2ce353fc1902 14.0 landed
Improve hash_create()'s API for some added robustness.
- b3817f5f7746 14.0 landed
Use HASH_BLOBS for xidhash.
- a1b8aa1e4eec 14.0 landed
Fix initialization of RelationSyncEntry for streaming transactions.
- 69bd60672af6 14.0 landed
Remove unused function declaration in logicalproto.h.
- ddd5f6d2609b 14.0 landed
Add additional tests to test streaming of in-progress transactions.
- 58b5ae9d62bd 14.0 landed
Fix inline marking introduced in commit 464824323e.
- ac15b499f7f9 14.0 landed
Add support for streaming to built-in logical replication.
- 464824323e57 14.0 landed
Fix the SharedFileSetUnregister API.
- 4ab77697f67a 14.0 landed
Fix comment in procarray.c
- 77c7267c37f7 14.0 cited
Suppress compiler warning in non-cassert builds.
- e942af7b8261 14.0 cited
Extend the BufFile interface.
- 808e13b282ef 14.0 landed
Mark a few logical decoding related variables with PGDLLIMPORT.
- b48cac3b10a0 14.0 landed
Implement streaming mode in ReorderBuffer.
- 7259736a6e5b 14.0 landed
Extend the logical decoding output plugin API with stream methods.
- 45fdc9738b36 14.0 landed
WAL Log invalidations at command end with wal_level=logical.
- c55040ccd017 14.0 landed
Immediately WAL-log subtransaction and top-level XID association.
- 0bead9af484c 14.0 landed
Allow logical replication to transfer data in binary format.
- 9de77b545313 14.0 cited
Only superuser can set sslcert/sslkey in postgres_fdw user mappings
- cebf9d6e6ee1 13.0 cited
Track statistics for spilling of changes from ReorderBuffer.
- 9290ad198b15 13.0 landed
Add logical_decoding_work_mem to limit ReorderBuffer memory usage.
- cec2edfa7859 13.0 landed
logical decoding: process ASSIGNMENT during snapshot build
- bac2fae05c77 13.0 cited
Emit invalidations to standby for transactions without xid.
- c6ff84b06a68 9.6.0 cited

On Wed, May 27, 2020 at 5:19 PM Mahendra Singh Thalor <mahi6run@gmail.com>
wrote:

> On Tue, 26 May 2020 at 16:46, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> Hi all,
> On the top of v16 patch set [1], I did some testing for DDL's and DML's to
> test wal size and performance. Below is the testing summary;
>
> *Test parameters:*
> wal_level= 'logical
> max_connections = '150'
> wal_receiver_timeout = '600s'
> max_wal_size = '2GB'
> min_wal_size = '2GB'
> autovacuum= 'off'
> checkpoint_timeout= '1d'
>
> *Test results:*
>
> CREATE index operations Add col int(date) operations Add col text
> operations
> SN. operation name LSN diff (in bytes) time (in sec) % LSN change LSN
> diff (in bytes) time (in sec) % LSN change LSN diff (in bytes) time (in
> sec) % LSN change
>
> 1
> 1 DDL without patch 17728 0.89116
> 1.624548
> 976 0.764393
> 11.475409
> 33904 0.80044
> 2.80792
> with patch 18016 0.804868 1088 0.763602 34856 0.787108
>
> 2
> 2 DDL without patch 19872 0.860348
> 2.73752
> 1632 0.763199
> 13.7254902
> 34560 0.806086
> 3.078703
> with patch 20416 0.839065 1856 0.733147 35624 0.829281
>
> 3
> 3 DDL without patch 22016 0.894891
> 3.63372093
> 2288 0.776871
> 14.685314
> 35216 0.803493
> 3.339391186
> with patch 22816 0.828028 2624 0.737177 36392 0.800194
>
> 4
> 4 DDL without patch 24160 0.901686
> 4.4701986
> 2944 0.768445
> 15.217391
> 35872 0.77489
> 3.590544
> with patch 25240 0.887143 3392 0.768382 37160 0.82777
>
> 5
> 5 DDL without patch 26328 0.901686
> 4.9832877
> 3600 0.751879
> 15.555555
> 36528 0.817928
> 3.832676
> with patch 27640 0.914078 4160 0.74709 37928 0.820621
>
> 6
> 6 DDL without patch 28472 0.936385
> 5.5071649
> 4256 0.745179
> 15.78947368
> 37184 0.797043
> 4.066265
> with patch 30040 0.958226 4928 0.725321 38696 0.814535
>
> 7
> 8 DDL without patch 32760 1.0022203
> 6.422466
> 5568 0.757468
> 16.091954
> 38496 0.83207
> 4.509559
> with patch 34864 0.966777 6464 0.769072 40232 0.903604
>
> 8
> 11 DDL without patch 50296 1.0022203
> 5.662478
> 7536 0.748332
> 16.666666
> 40464 0.822266
> 5.179913
> with patch 53144 0.966777 8792 0.750553 42560 0.797133
>
> 9
> 15 DDL without patch <#m_-5189706345613774249_gid=2095312519&range=B9>
> 58896 1.267253
> 5.662478
> 10184 0.776875
> 16.496465
> 43112 0.821916
> 5.84524
> with patch 62768 1.27234 11864 0.746844 45632 0.812567
>
> 10
> 1 DDL & 3 DML without patch 18240 0.812551
> 1.6228
> 1192 0.771993
> 10.067114
> 34120 0.849467
> 2.8113599
> with patch 18536 0.819089 1312 0.785117 35080 0.855456
>
> 11
> 3 DDL & 5 DML without patch 23656 0.926616
> 3.4832606
> 2656 0.758029
> 13.55421687
> 35584 0.829377
> 3.372302
> with patch 24480 0.915517 3016 0.797206 36784 0.839176
>
> 12
> 10 DDL & 5 DML without patch 52760 1.101005
> 4.958301744
> 7288 0.763065
> 16.02634468
> 40216 0.837843
> 4.993037
> with patch 55376 1.105241 8456 0.779257 42224 0.835206
>
> 13
> 10 DML without patch 1008 0.791091
> 6.349206
> 1008 0.81105
> 6.349206
> 1008 0.78817
> 6.349206
> with patch 1072 0.807875 1072 0.771113 1072 0.759789
>
> To see all operations, please see[2] test_results
> <https://docs.google.com/spreadsheets/d/1g11MrSd_I39505OnGoLFVslz3ykbZ1nmfR_gUiE_O9k/edit?usp=sharing>
>
>
Why are you seeing any additional WAL in case-13 (10 DML) where there is no
DDL?  I think it is because you have used savepoints in that case which
will add some additional WAL.  You seems to have 9 savepoints in that test
which should ideally generate 36 bytes of additional WAL (4-byte per
transaction id for each subtransaction).  Also, in other cases where you
took data for DDL and DML, you have also used savepoints in those tests. I
suggest for savepoints, let's do separate tests as you have done in case-13
but we can do it 3,5,7,10 savepoints and probably each transaction can
update a row of 200 bytes or so.

I think you can take data for somewhat more realistic cases of DDL and DML
combination like 3 DDL's with 10 DML and 3 DDL's with 15 DML operations.
In general, I think we will see many more DML's per DDL.  It is good to see
the worst-case WAL and performance overhead as you have done.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com