Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions - Mailing list pgsql-hackers
| From | Amit Kapila |
|---|---|
| Subject | Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions |
| Date | |
| Msg-id | CAA4eK1LUrRNQmHs=pFe4R_9R68gJ_54ZXUm23T5vg=JmRpqf6Q@mail.gmail.com Whole thread Raw |
| In response to | Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions (Mahendra Singh Thalor <mahi6run@gmail.com>) |
| Responses |
Re: PATCH: logical_work_mem and logical streaming of largein-progress transactions
|
| List | pgsql-hackers |
On Wed, May 27, 2020 at 5:19 PM Mahendra Singh Thalor <mahi6run@gmail.com> wrote:
On Tue, 26 May 2020 at 16:46, Amit Kapila <amit.kapila16@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 DDL without patch 17728 0.89116 976 0.764393 33904 0.80044 with patch 18016 0.804868 1088 0.763602 34856 0.787108 2 DDL without patch 19872 0.860348 1632 0.763199 34560 0.806086 with patch 20416 0.839065 1856 0.733147 35624 0.829281 3 DDL without patch 22016 0.894891 2288 0.776871 35216 0.803493 with patch 22816 0.828028 2624 0.737177 36392 0.800194 4 DDL without patch 24160 0.901686 2944 0.768445 35872 0.77489 with patch 25240 0.887143 3392 0.768382 37160 0.82777 5 DDL without patch 26328 0.901686 3600 0.751879 36528 0.817928 with patch 27640 0.914078 4160 0.74709 37928 0.820621 6 DDL without patch 28472 0.936385 4256 0.745179 37184 0.797043 with patch 30040 0.958226 4928 0.725321 38696 0.814535 8 DDL without patch 32760 1.0022203 5568 0.757468 38496 0.83207 with patch 34864 0.966777 6464 0.769072 40232 0.903604 11 DDL without patch 50296 1.0022203 7536 0.748332 40464 0.822266 with patch 53144 0.966777 8792 0.750553 42560 0.797133 15 DDL without patch 58896 1.267253 10184 0.776875 43112 0.821916 with patch 62768 1.27234 11864 0.746844 45632 0.812567 1 DDL & 3 DML without patch 18240 0.812551 1192 0.771993 34120 0.849467 with patch 18536 0.819089 1312 0.785117 35080 0.855456 3 DDL & 5 DML without patch 23656 0.926616 2656 0.758029 35584 0.829377 with patch 24480 0.915517 3016 0.797206 36784 0.839176 10 DDL & 5 DML without patch 52760 1.101005 7288 0.763065 40216 0.837843 with patch 55376 1.105241 8456 0.779257 42224 0.835206 10 DML without patch 1008 0.791091 1008 0.81105 1008 0.78817 with patch 1072 0.807875 1072 0.771113 1072 0.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.
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.
pgsql-hackers by date: