Thread: Speed up COMMIT PREPARED

Hi,


I noticed that COMMIT PREPARED command is slow in the discussion [1].


First, I made the following simple script for pgbench.

``` prepare.pgbench
\set id random(1, 1000000)

BEGIN;
UPDATE test_table SET md5 = md5(clock_timestamp()::text) WHERE id = :id;
PREPARE TRANSACTION 'prep_:client_id';
COMMIT PREPARED 'prep_:client_id';
```

I run the pgbench as follows.

```
pgbench -f prepare.pgbench -c 1 -j 1 -T 60 -d postgres -r
```

The result is following.


<Result in master branch>
tps:    287.259
Latency:
  UPDATE            0.207ms
  PREPARE TRANSACTION    0.212ms
  COMMIT PREPARED        2.982ms


Next, I analyzed the bottleneck using pstack and strace.
I noticed that the open() during COMMIT PREPARED takes 2.7ms.
Furthermore, I noticed that the backend process almost always open the same wal segment file.


When COMMIT PREPARED command, there are two ways to find 2PC state data.
 - If it is stored in wal segment file, open and read wal segment file.
 - If not, read 2PC state file

The above script runs COMMIT PREPARED command just after PREPARE TRANSACTION command.
I think it also won't take long time for XA transaction to run COMMIT PREPARED command after running PREPARE
TRANSACTIONcommand. 
Therefore, I think that the wal segment file which is opened during COMMIT PREPARED probably be the current wal segment
file.


To speed up COMMIT PREPARED command, I made two patches for test.


(1) Hold_xlogreader.patch
Skip closing wal segment file after COMMIT PREPARED command completed.
If the next COMMIT PREPARED command use the same wal segment file, it is fast since the process need not to open wal
segmentfile. 
However, I don't know when we should close the wal segment file.
Moreover, it might not be useful when COMMIT PREPARED command is run not so often and use different wal segment file
eachtime. 

<Result in Hold_xlogreader.patch>
tps:    1750.81
Latency:
  UPDATE            0.156ms
  PREPARE TRANSACTION    0.184ms
  COMMIT PREPARED        0.179ms


(2) Read_from_walbuffer.patch
Read the data from wal buffer if there are still in wal buffer.
If COMMIT PREPARED command is run just after PREPARE TRANSACTION command, the wal may be in the wal buffer.
However, the period which the wal is in the wal buffer is not so long since wal writer recycle the wal buffer soon.
Moreover, it may affect the other performance such as UPDATE since it needs to take lock on wal buffer.

<Result in Read_from_walbuffer.patch>
tps:    446.371
Latency:
  UPDATE            0.187ms
  PREPARE TRANSACTION    0.196ms
  COMMIT PREPARED        1.974ms


Which approach do you think better?


[1] https://www.postgresql.org/message-id/20191206.173215.1818665441859410805.horikyota.ntt%40gmail.com


Regards,
Ryohei Takahashi

I noticed that the previous Read_from_walbuffer.patch has a mistake in xlogreader.c.
Could you please use the attached v2 patch?

The performance result of the previous mail is the result of v2 patch.


Regards,
Ryohei Takahashi

Hi,


I noticed that anti-virus software slow down the open().
I stopped the anti-virus software and re-run the test.
(Average of 10 times)

master: 1924tps
Hold_xlogreader.patch: 1993tps (+3.5%)
Read_from_walbuffer.patch: 1954tps(+1.5%)

Therefore, the effect of my patch is limited.

I'm sorry for the confusion.

Regards,
Ryohei Takahashi