12

PostgreSQL 9.2.2 on Windows 7 64-bit.

The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting log_statement = 'all' degrade performance? I've read that this significantly reduces performance, but these claims come from older articles, so just how much of a performance loss should I expect relative to log_statement = 'none'?

1 Answer 1

15

Like almost everything else, it depends on your hardware, configuration and workload.

If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference.

If your logs are on the same file system or even just the same disk as the database, the logging will make more difference than if the logs are stored on a separate disk. Even though the logs aren't explicitly fsync()'d and are written sequentially, they're still competing with the main DB for I/O.

More importantly, some file systems (ext3 in particular) will flush all data when any file is fsync()'d, so the log writes become closer to random I/O than sequential I/O and slow down every commit. IIRC this is not an issue on ext4 or xfs.

It'll also depend on whether you have statement timing enabled, the logging method you're using, and more.

The best option is to benchmark it with a simulation of your real workload and see.

2
  • Logging to stderr as plain text file on the same disk. Statement timing is disabled. The logging collector is enabled. The server is occasionally subject to frequent, moderately complex queries.
    – bwDraco
    Dec 11, 2012 at 15:50
  • @DragonLord: statement timing makes a big difference. for CPU load. Dec 12, 2012 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.