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

On Tue, 26 May 2020 at 16:46, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, May 26, 2020 at 2:44 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Tue, May 26, 2020 at 10:27 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:
> > >
> > > >
> > > > 2. There is a bug fix in handling the stream abort in 0008 (earlier
it
> > > > was 0006).
> > > >
> > >
> > > The code changes look fine but it is not clear what was the exact
> > > issue. Can you explain?
> >
> > Basically, in case of an empty subtransaction, we were reading the
> > subxacts info but when we could not find the subxid in the subxacts
> > info we were not releasing the memory. So on next subxact_info_read
> > it will expect that subxacts should be freed but we did not free it in
> > that !found case.
> >
>
> Okay, on looking at it again, the same code exists in
> subxact_info_write as well. It is better to have a function for it.
> Can we have a structure like SubXactContext for all the variables used
> for subxact? As mentioned earlier I find the allocation/deallocation
> of subxacts a bit ad-hoc, so there will always be a chance that we can
> forget to free it. Having it allocated in memory context which we can
> reset later might reduce that risk. One idea could be that we have a
> special memory context for start and stop messages which can be used
> to allocate the subxacts there. In case of commit/abort, we can allow
> subxacts information to be allocated in ApplyMessageContext which is
> reset at the end of each protocol message.
>
> --
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>
>

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

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

[1]:
https://www.postgresql.org/message-id/CAFiTN-vnnrk580ucZVYnub_UQ-ayROew8fQ2Yn5aFYMeF0U03w%40mail.gmail.com
[2]:
https://docs.google.com/spreadsheets/d/1g11MrSd_I39505OnGoLFVslz3ykbZ1nmfR_gUiE_O9k/edit?usp=sharing

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