Configuring PostgreSQL to match server configuration

Question

We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram is free. PostgresSQL takes only 600Mb while webserver processes take 7Gb. Average CPU load is 25%.

Software is Ubuntu 12.04 and Postgres 9.1. Database size is 15Gb.

As load increased our application response time increased from 230ms to 450ms over last few months. Application takes 40% share while DB takes 60% of response time.

We cached a lot of things, but was wondering if we could get something by tweaking Postgres configuration. After bit of researched we found that default PostgreSQL configuration (https://gist.github.com/darkofabijan/9453c793ceec1ac6274d) is really conservative and that we should definitely tweak our configuration.

After running pgtune we got following recommended values.

maintenance_work_mem = 1GB 
checkpoint_completion_target = 0.7 
effective_cache_size = 44GB 
work_mem = 288MB 
wal_buffers = 4MB 
checkpoint_segments = 8 
shared_buffers = 15GB 
max_connections = 200 

Once we started running our PostgreSQL server with recommended values we got somehow regular spikes when application response time jumped to 2000ms+ while increase was in database response time. After running it for couple of hours we reverted to original Ubuntu 12.04/PostgreSQL 9.1 configuration.

Obviously we don't have much experience with running DBs. Both concrete recommendations regarding pgtune suggested values and pointers to good resources would be much appreciated.

Edit 1:

 name | current_setting | source
----------------------------+----------------------------------+----------------------
 application_name | psql | client
 client_encoding | UTF8 | client
 DateStyle | ISO, MDY | configuration file
 default_text_search_config | pg_catalog.english | configuration file
 external_pid_file | /var/run/postgresql/9.1-main.pid | configuration file
 lc_messages | en_US.UTF-8 | configuration file
 lc_monetary | en_US.UTF-8 | configuration file
 lc_numeric | en_US.UTF-8 | configuration file
 lc_time | en_US.UTF-8 | configuration file
 log_line_prefix | %t | configuration file
 log_timezone | localtime | environment variable
 max_connections | 100 | configuration file
 max_stack_depth | 2MB | environment variable
 port | 5432 | configuration file
 shared_buffers | 24MB | configuration file
 ssl | on | configuration file
 TimeZone | localtime | environment variable
 unix_socket_directory | /var/run/postgresql | configuration file

Answer 1

This is a pretty broad topic. I suggest you pick up a copy of Greg Smith's excellent book "PostgreSQL 9.0 High Performance" and work through it. http://www.packtpub.com/postgresql-90-high-performance/book

Can you show the output of the following query:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Things to test:

wal_buffers = 16MB
checkpoint_segments = 32 #(or higher)
shared_buffers = 6GB
random_page_cost = 2.0 # not the first choice of things to tune

What are your logging settings? Are there obvious slow queries? If so, you can run them through pgbadger to give you a good idea of queries to target.

Here are some settings you can test, given your current settings and what you have mentioned about your system.

log_line_prefix = '%t [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] '
shared_buffers = '6GB' # requires cluster restart
checkpoint_completion_target = 0.8
checkpoint_segments = 32
effective_io_concurrency = <lesser of cpus and disks/channels>
log_destination = stderr # This and the log_directory are what I use,
log_directory = pg_log # it is up to you whether or not you want to change them
logging_collector = 'on'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = '2s' # Set this as necessary, set lower per role
random_page_cost = 2.5 # If faster disks, drop to 2.0
track_activity_query_size = 4096
work_mem = '50MB' # Adjust this as necessary.
 # Be aware this is a per-action setting,
 # eg. aggregation, sorts, etc can each consume up to
 # this amount of RAM

You'll need to restart the database for some of those settings to take effect.

• darkofabijan: I have updated question with output from your settings query. Logging of slow queries is not configured. I will test your suggested configuration. Tnx!