On Wed, May 27, 2020 at 5:19 PM Mahendra Singh Thalor <mahi6...@gmail.com> wrote:
> On Tue, 26 May 2020 at 16:46, Amit Kapila <amit.kapil...@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
