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, 100)
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.
tps:287.259
Latency:
UPDATE0.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 TRANSACTION command.
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 segment file.
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 each time.
tps:1750.81
Latency:
UPDATE0.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.
tps:446.371
Latency:
UPDATE0.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
Hold_xlogreader.patch
Description: Hold_xlogreader.patch
Read_from_walbuffer.patch
Description: Read_from_walbuffer.patch
