Why are my commits after slow SELECT transactions with PostgreSQL?

Question

I have a web application presenting data that is generated by a separate process and stored in PostgreSQL, version 8.4. The backend writes continuously enough, but most views in a web application only execute read-only SELECT queries.

According to the New Relic Python agent, 30% of the processing time for my view was spent completing the completion of COMMIT, and this is especially bad in views that returned a large number of SELECT queries, even if they did not modify any data.

I expected that a read-only transaction should have very little work during the commit phase. What is Postgres doing during COMMIT for these read-only requests?

I know that I can turn off synchronous_commit for these transactions to hide latency from the view, and I certainly don’t care about the read-only transaction longevity, but I don’t understand why this is necessary, and I am worried that it might mask deeper wrong configuration.

Answer 1

There are various cleanup operations that need to be performed in order to keep the database in good shape, and many of them are performed by the first process that stumbled upon this feature, even if this process executes only individual queries.

These cleanup operations can generate WAL records, which then trigger synchronization on commit. Thus, although the selection may be read-only at the visible user level, they do take notes behind the scenes.

It should be possible to detect when all WAL operations performed in a given transaction originate from cleanup operations and then automatically do asynchronously in these cases. But no one has yet managed to implement this function (or even catalog all the calling WAL sites that are in this category).

Answer 2

Comments are too short, so here. Grab several logs while you run the code and guess from the equation.

Update postgresql.conf to have these settings. You will need to restart postgre to get logging_collector. You can and should remove these settings after completion. Therefore, before making any changes, be sure to back up postgresql.conf. Once you have a log file with the captured data, I recommend using it to look at it if the log is larger than on the page or http://dalibo.imtqy.com/pgbadger/ .

 log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 0 client_min_messages = notice log_min_messages = warning log_min_error_statement = error log_min_duration_statement = 0 log_checkpoints = on log_connections = on log_disconnections = on log_duration = off log_error_verbosity = verbose log_hostname = on log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u ' log_lock_waits = on log_statement = 'none' log_temp_files = 0