Postgresql – Configuring PostgreSQL to match server configuration

performancepostgresqlpostgresql-performance

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

Best Answer

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.