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 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. <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
Hold_xlogreader.patch
Description: Hold_xlogreader.patch
Read_from_walbuffer.patch
Description: Read_from_walbuffer.patch
