On Fri, 29 May 2020 at 15:52, Amit Kapila <amit.kapil...@gmail.com> wrote: > > 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 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
