Quick PostgreSQL server optimization for development (or integration server)
I’ve made RSpec test suite for my Ruby on Rails application 18% faster by following a Non-durable PostgreSQL configuration guide.
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.
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
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.
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'
( 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.
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.