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

Mahendra Singh Thalor <mahi6run@gmail.com>

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

To: Amit Kapila <amit.kapila16@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-06-04T11:35:50Z

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 Fri, 29 May 2020 at 15:52, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> 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 operationsAdd col int(date) operationsAdd col text
operations
>> SN.operation nameLSN diff (in bytes)time (in sec)% LSN changeLSN diff
(in bytes)time (in sec)% LSN changeLSN diff (in bytes)time (in sec)% LSN
change
>> 1
>> 1 DDL without patch177280.89116
>> 1.624548
>> 9760.764393
>> 11.475409
>> 339040.80044
>> 2.80792
>> with patch180160.80486810880.763602348560.787108
>> 2
>> 2 DDL without patch198720.860348
>> 2.73752
>> 16320.763199
>> 13.7254902
>> 345600.806086
>> 3.078703
>> with patch204160.83906518560.733147356240.829281
>> 3
>> 3 DDL without patch220160.894891
>> 3.63372093
>> 2 2880.776871
>> 14.685314
>> 352160.803493
>> 3.339391186
>> with patch228160.82802826240.737177363920.800194
>> 4
>> 4 DDL without patch241600.901686
>> 4.4701986
>> 29440.768445
>> 15.217391
>> 358720.77489
>> 3.590544
>> with patch252400.88714333920.768382371600.82777
>> 5
>> 5 DDL without patch263280.901686
>> 4.9832877
>> 36000.751879
>> 15.555555
>> 365280.817928
>> 3.832676
>> with patch276400.91407841600.74709379280.820621
>> 6
>> 6 DDL without patch284720.936385
>> 5.5071649
>> 42560.745179
>> 15.78947368
>> 371840.797043
>> 4.066265
>> with patch300400.95822649280.725321386960.814535
>> 7
>> 8 DDL without patch327601.0022203
>> 6.422466
>> 55680.757468
>> 16.091954
>> 384960.83207
>> 4.509559
>> with patch348640.96677764640.769072402320.903604
>> 8
>> 11 DDL without patch502961.0022203
>> 5.662478
>> 75360.748332
>> 16.666666
>> 404640.822266
>> 5.179913
>> with patch531440.96677787920.750553425600.797133
>> 9
>> 15 DDL without patch588961.267253
>> 5.662478
>> 101840.776875
>> 16.496465
>> 431120.821916
>> 5.84524
>> with patch627681.27234118640.746844456320.812567
>> 10
>> 1 DDL & 3 DML without patch182400.812551
>> 1.6228
>> 11920.771993
>> 10.067114
>> 341200.849467
>> 2.8113599
>> with patch185360.81908913120.785117350800.855456
>> 11
>> 3 DDL & 5 DML without patch236560.926616
>> 3.4832606
>> 26560.758029
>> 13.55421687
>> 355840.829377
>> 3.372302
>> with patch244800.91551730160.797206367840.839176
>> 12
>> 10 DDL & 5 DML without patch527601.101005
>> 4.958301744
>> 72880.763065
>> 16.02634468
>> 402160.837843
>> 4.993037
>> with patch553761.10524184560.779257422240.835206
>> 13
>> 10 DML without patch10080.791091
>> 6.349206
>> 10080.81105
>> 6.349206
>> 10080.78817
>> 6.349206
>> with patch10720.80787510720.77111310720.759789
>>
>> To see all operations, please see[2] test_results
>>
>
> 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.
>

Thanks Amit for reviewing results.

Yes, you are correct.  I used savepoints in DML so it was showing
additional wal.

As suggested above, I did testing for DML's, DDL's and savepoints. Below is
the test results:

*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 <#gid=0&range=B2> 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 <#gid=0&range=B3> 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 <#gid=0&range=B4> 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 <#gid=0&range=B5> 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 <#gid=0&range=B6> 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 <#gid=0&range=B7> 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 <#gid=0&range=B8> 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 <#gid=0&range=B9> 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 <#gid=0&range=B10> 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 <#gid=0&range=E2> 18224 0.865753
1.58033362
1176 0.78074
9.523809
34104 0.857664
2.7914614
with patch 18512 0.854788 1288 0.767758 35056 0.877604

11
3 DDL & 5 DML without patch <#gid=0&range=E3> 23632 0.954274
3.385203
2632 0.785501
12.765957
35560 0.87744
3.3070866
with patch 24432 0.927245 2968 0.857528 36736 0.867555

12
3 DDL & 10 DML without patch <#gid=0&range=E4> 25088 0.941534
3.316326
3040 0.812123
11.052631
35968 0.877769
3.269579
with patch 25920 0.898643 3376 0.804943 37144 0.879752

13
3 DDL & 15 DML without patch <#gid=0&range=E5> 26400 0.949599
3.151515
3392 0.818491
9.90566037
36320 0.859353
3.2378854
with patch 27232 0.892505 3728 0.789752 37320 0.812386

14
5 DDL & 15 DML without patch <#gid=0&range=E5> 31904 0.994223
4.287863
4704 0.838091
11.904761
37632 0.867281
3.720238095
with patch 33272 0.968122 5264 0.816922 39032 0.876364

15
1 DML without patch <#gid=0&range=E5> 328 0.817988
0






with patch 328 0.794927





16
3 DML without patch <#gid=0&range=E5> 464 0.791229
0






with patch 464 0.806211





17
5 DML without patch <#gid=0&range=E5> 608 0.794258
0






with patch 608 0.802001





18
10 DML without patch <#gid=0&range=E5> 968 0.831733
0






with patch 968 0.852777






*Results for savepoints:*
SN. Operation name Operation LSN diff (in bytes) time (in sec) % LSN change

1
1 savepoint without patch
begin;
insert into perftest values (1);
savepoint s1;
update perftest set c1 = 5 where c1 = 1;
commit;
408 0.805615
1.960784
with patch 416 0.823121

2
2 savepoint without patch
begin;
insert into perftest values (1);
savepoint s1;
update perftest set c1 = 5 where c1 = 1;
savepoint s2;
update perftest set c1 = 6 where c1 = 5;
commit;
488 0.827147
3.278688
with patch 504 0.819165

3
3 savepoint without patch
begin;
insert into perftest values (1);
savepoint s1;
update perftest set c1 = 2 where c1 = 1;
savepoint s2;
update perftest set c1 = 3 where c1 = 2;
savepoint s3;
update perftest set c1 = 4 where c1 = 3;
commit;
560 0.806441
4.28571428
with patch 584 0.821316

4
5 savepoint without patch
712 0.823774
5.617977528
with patch 752 0.800037

5
7 savepoint without patch
864 0.829136
6.48148148
with patch 920 0.793751

6
10 savepoint without patch
1096 0.77946
7.29927007
with patch 1176 0.78711


To see all the operations(DDL's and DML's), please see test_results
<https://docs.google.com/spreadsheets/d/1g11MrSd_I39505OnGoLFVslz3ykbZ1nmfR_gUiE_O9k/edit?usp=sharing>

*Testing summary:*
Basically, we are writing per command invalidation message and for testing
that I have tested with different combinations of the DDL and DML
operation.  I have not observed any performance degradation with the patch.
For "create index" DDL's, %change in wal is 1-7% for 1-15 DDL's. For "add
col int/date" DDL's, it is 11-17% for 1-15 DDL's and for "add col text"
DDL's, it is 2-6% for 1-15 DDL's. For mix (DDL & DML), it is 2-10%.

why are we seeing 11-13 % of the extra wall, basically,  the amount of
extra WAL is not very high but the amount of WAL generated with add column
int/date is just ~1000 bytes so additional 100 bytes will be around 10% and
for add column text it is  ~35000 bytes so % is less. For text, these
~35000 bytes are due to toast
There is no change in wal size for *DML operations*. For savepoints, we are
getting max 8 bytes per savepoint wal increment (basically for
Sub-transaction, we are adding 5 bytes to store xid but due to padding, it
is 8 bytes and some times if wal is already aligned, then we are getting 0
bytes increment)

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com