Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
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. –  DragonLord Dec 11 '12 at 15:50
    
@DragonLord: statement timing makes a big difference. for CPU load. –  Erwin Brandstetter Dec 12 '12 at 0:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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