PostgreSQL. Slow Prepare Transaction and Commit Prepared

Question

I've just encountered a strange problem. I've made a report in pgfourine, and found out that my XA transactions started to work really slow. Prepare transaction and commit prepared combined took 12.55s out of 13.2s. But why?

#####  Overall statistics  #####

Number of unique normalized queries: 175
Number of queries:     268,772
Total query duration:  13m2s


#####  Queries by type  #####

SELECT:    116493     43.3%
INSERT:     15926      5.9%
UPDATE:      7935      3.0%
DELETE:      4923      1.8%


#####  Queries that took up the most time (N)  #####

1) 6m32s - 26,338 - COMMIT PREPARED ''
--
2) 6m23s - 25,972 - PREPARE TRANSACTION ''
--
3) 0.6s - 3,848 - update avatar set lfa_position=NULL where Id=0
.....
7) 0.3s - 21,514 - COMMIT
.....

I have a theory but don't have a proof.. I have slow discs and I turned off synchronous_commit. Maybe PostgreSQL has to make an fsync during "prepare transaction" even if synchronous_commit is off?

fsync = on 
synchronous_commit = off

Any ideas?

UPDATE

Same tests with

fsync = off
synchronous_commit = off


#####  Overall statistics  #####

Number of unique normalized queries: 155
Number of queries:     186,838
Total query duration:  6.6s


#####  Queries by type  #####

SELECT:     84367     45.2%
INSERT:      9197      4.9%
UPDATE:      5486      2.9%
DELETE:      2996      1.6%


#####  Queries that took up the most time (N)  #####

1) 1.8s - 16,972 - PREPARE TRANSACTION ''
--
2) 1.1s - 16,965 - COMMIT PREPARED ''
--
3) 0.4s - 2,904 - update avatar set lfa_position=NULL where Id=0
--
4) 0.2s - 16,031 - COMMIT

Looks like fsync took vast amount of time, but not all the time. 16k commits - 0.2sec, 17k prepare+commit 2.9sec.

Sad story. Looks like XA commit tooks 15 times more time than local commit and doesn't take in account synchronous_commit setting. fsync=off is not safe for production use. So if I want to use XA transactions I have to use it carefully and use a good SSD drive with high IOPS.

That sounds reasonable. It's likely possible to verify it with `perf` or similar. It seems superficially reasonable that `PREPARE TRANSACTION` should honour `synchronous_commit = off`, but there might well be reasons why not. This might be better asked on the pgsql-general mailing list. If you post there, please link back to this post. — Craig Ringer, Oct 03 '12 at 09:34
It sounds very reasonable. The queries mostly execute from memory buffers, all the "real work" is concentrated in the consistency points, which involves forcing stuff to disk (and waiting for it to complete). — wildplasser, Oct 03 '12 at 10:15
But why there is so big difference between commit and commit prepared? — Andrey Frolov, Oct 03 '12 at 10:35

score 1 · Accepted Answer · answered Oct 03 '12 at 12:55

The theory that PREPARE TRANSACTION is immediately sync'ed is correct, and it's mentioned in the doc:

Excerpt from http://www.postgresql.org/docs/9.1/static/wal-async-commit.html :

The commands supporting two-phase commit, such as PREPARE TRANSACTION, are also always synchronous.

PostgreSQL. Slow Prepare Transaction and Commit Prepared

1 Answers1