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.
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