Quick PostgreSQL server optimization for development (or integration server)

TL;DR

I’ve made RSpec test suite for my Ruby on Rails application 18% faster by following a Non-durable PostgreSQL configuration guide.

Intro

When I’m developing (usually Ruby on Rails) applications using PostgreSQL database server for persistence, I don’t need high durability of my development database (or integration server). What I care the most is it’s speed. I realize, however, that database server, probably, is not the bottle neck of the typical application test suite.

Here I show how switching few options in the PostgreSQL config file increased performance of the test suite on my development machine (by reducing durability).

Disclaimer: I’m not performance testing ninja and all my performance tests are very subjective. I test only what I care the most – execution time of the test suite for my current Ruby on Rails application.

Environment

Hardware: Intel i7-3720QM CPU @ 2.60GHz, 16 Gb RAM OS: Ubuntu 12.04.3 LTS Software: Ruby on Rails 3.2.15, Ruby 2.0.0p247, Postgres 9.2.5

About the application

I have a monolithic Ruby on Rails application, with 18064/19132 (code/test) lines of code, with test suite of 1977 RSpec examples, some of which are javascript-enabled feature specs. I’m using database_cleaner gem for cleaning database between tests, with transaction strategy for non-javascript specs and truncation strategy for javascript specs. I’m running my test suite with wonderful parallel_tests gem.

Default PostgreSQL 9.2.5 configuration

Here are results of running full test suite on the clean PostgreSQL installation:

( bin/stubs/parallel_rspec spec; )  1002.48s user 51.83s system 359% cpu 4:53.35 total
( bin/stubs/parallel_rspec spec; )  1006.46s user 54.12s system 360% cpu 4:54.02 total
( bin/stubs/parallel_rspec spec; )  1004.78s user 54.74s system 356% cpu 4:57.18 total

Or about 295 seconds in average.

Decreasing durability

According to the official Non-durable configuration guide, I modified my /etc/postgresql/9.2/main/postgresql.conf in the following way:

# Turn off fsync; there is no need to flush data to disk.
fsync = 'off'

# Turn off full_page_writes;
# there is no need to guard against partial page writes.
full_page_writes = 'off'

# Increase checkpoint_segments and checkpoint_timeout ;
# this reduces the frequency of checkpoints,
# but increases the storage requirements of /pg_xlog.
checkpoint_segments = '100'
checkpoint_timeout = '45min'

# Turn off synchronous_commit;
# there might be no need to write the WAL to disk on every commit.
# This setting does risk transaction loss (though not data corruption)
# in case of a crash of the database alone.
synchronous_commit = 'off'

Results

( bin/stubs/parallel_rspec spec; )  1134.99s user 69.70s system 505% cpu 3:58.12 total
( bin/stubs/parallel_rspec spec; )  1144.00s user 68.01s system 492% cpu 4:05.93 total
( bin/stubs/parallel_rspec spec; )  1145.39s user 68.29s system 493% cpu 4:05.83 total

Or about 243 seconds in average, with more intensive CPU usage.

Conclusion

As we can see, very simple configuration change made execution of my test suite ~52 seconds (or ~18%) faster, which is pretty nice, I think.