On Tue, 26 May 2020 at 16:46, Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Tue, May 26, 2020 at 2:44 PM Dilip Kumar <dilipbal...@gmail.com> wrote: > > > > On Tue, May 26, 2020 at 10:27 AM Amit Kapila <amit.kapil...@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 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
