Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions - Mailing list pgsql-hackers

From Mahendra Singh Thalor
Subject Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions
Date
Msg-id CAKYtNAof+vVNQApsedWYQ2orvwWknmresB9=6-pNwHX60cjxEQ@mail.gmail.com
Whole thread Raw
In response to Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions  (Amit Kapila <amit.kapila16@gmail.com>)
Responses Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions
List pgsql-hackers
On Tue, 26 May 2020 at 16:46, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Tue, May 26, 2020 at 2:44 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Tue, May 26, 2020 at 10:27 AM Amit Kapila <amit.kapila16@gmail.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 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
22880.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

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

pgsql-hackers by date:

Previous
From: Amit Khandekar
Date:
Subject: Re: Inlining of couple of functions in pl_exec.c improves performance
Next
From: Robert Haas
Date:
Subject: Re: tablespace_map code cleanup