21:53

Always Do This #3: Log Lock Waits and Temp Files

4 January 2016

How much and what to log in PostgreSQL is something that doesn’t really admit a single solution. Logging every connection can swamp a server, as can too low a setting of log_min_statement_duration. But there are two settings I always turn on: log_lock_waits, and log_temp_files (with logtempfiles being set to 0).

log_lock_waits will log any lock wait that goes on longer than the deadlock_timeout setting (the same process that checks for deadlocks also emits the log message). By default, this is one second, and if my database has a lock on which a process is waiting for that long, I want to know about it.

log_temp_files is a size over which a log message will be generated for the temp file creation. Zero is everything. Temp files are (almost always) bad; any time the system needs to do something on disk instead of in memory, I want to know about it.

So, just turn these on, and feed the results through pgbadger. If you are getting so many lock waits or temp files that you are getting excessive logging, the problem is the waits or the temp files, not the logging!

There are no comments yet.