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
