Tuning Postgres config for new Database Server

Question

I have finally gotten a Purpose Built Database machine for a project i'm working on.

The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives.

I have just got the database moved over to the new machine; the performance with the same code are worse than when it was running on a VM.

I'm looking for suggestions on what settings to adjust to what values.

Currently, I've upped shared_buffers to 60GB and the kernel settings needed to make that change.

temp_buffers is 32MB

work_mem is 5MB

I'm working on doing some stuff I'd like to get loaded in quickly so I have synchronous_commit set to off.

Can anyone point me in the right direction as to how to improve the speed? I had it running quite a bit faster on a slower machine with much less memory and drives shared with the machines that were making calls to it, so I'm not really sure what the issues are.

Update: 2013-03-06 Performance is falling off a cliff shortly after a run starts. Not sure what to do. Settings

shared_buffers = 12GB
temp_buffers=32MB
work_mem = 5MB
maintenance_work_mem = 256MB
fsync = off
synchronous_commit = off
wal_buffers = 32MB
checkpoint_segments = 256
checkpoint_completion_target = .9
effective_cache_size 50GB
auto_vacuum = on
autovacuum_naptime = 1min

The task is a long script that's taking data from a copied in table and normalizing it into the database. So big reads occasionally to pick up 1000 rows or more, then lots of little reads to de-duplicate the record and find IDs etc, then some inserts along the way that are needed, and finally lots of inserts at the end. Then Repeat.

Any Suggestions? or ideas what's falling off? This is one of my slower queries, I'd love ideas of how to speed it up.

EXPLAIN (ANALYZE, BUFFERS) select provider_id, count(list_alias.name_part_id)
from list_alias
where provider_id in (1,4,5,6,7,8)
and name_part_id in (5,7,8,3,111)
group by provider_id
order by count(list_alias.name_part_id) desc
limit(1)

The output.

"Limit  (cost=31.62..31.62 rows=1 width=8) (actual time=0.157..0.157 rows=0 loops=1)"
"  Buffers: shared hit=17 read=1"
"  ->  Sort  (cost=31.62..31.62 rows=1 width=8) (actual time=0.153..0.153 rows=0 loops=1)"
"        Sort Key: (count(name_part_id))"
"        Sort Method: quicksort  Memory: 25kB"
"        Buffers: shared hit=17 read=1"
"        ->  GroupAggregate  (cost=0.00..31.61 rows=1 width=8) (actual time=0.147..0.147 rows=0 loops=1)"
"              Buffers: shared hit=17 read=1"
"              ->  Index Scan using "list_alias provider_id" on list_alias  (cost=0.00..31.59 rows=1 width=8) (actual time=0.146..0.146 rows=0 loops=1)"
"                    Index Cond: (provider_id = ANY ('{1,4,5,6,7,8}'::integer[]))"
"                    Filter: (name_part_id = ANY ('{5,7,8,3,111}'::integer[]))"
"                    Buffers: shared hit=17 read=1"
"Total runtime: 0.238 ms"

Edit2: More Info:

"application_name";"pgAdmin III - Query Tool";"client"
"autovacuum";"on";"configuration file"
"autovacuum_naptime";"1min";"configuration file"
"checkpoint_completion_target";"0.9";"configuration file"
"checkpoint_segments";"256";"configuration file"
"DateStyle";"ISO, MDY";"configuration file"
"default_text_search_config";"pg_catalog.english";"configuration file"
"effective_cache_size";"50GB";"configuration file"
"external_pid_file";"/var/run/postgresql/9.2-main.pid";"configuration file"
"fsync";"off";"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"
"listen_addresses";"*";"configuration file"
"log_line_prefix";"%t ";"configuration file"
"maintenance_work_mem";"256MB";"configuration file"
"max_connections";"100";"configuration file"
"max_stack_depth";"2MB";"environment variable"
"port";"5432";"configuration file"
"shared_buffers";"12GB";"configuration file"
"ssl";"off";"configuration file"
"ssl_cert_file";"/etc/ssl/certs/ssl-cert-snakeoil.pem";"configuration file"
"ssl_key_file";"/etc/ssl/private/ssl-cert-snakeoil.key";"configuration file"
"synchronous_commit";"off";"configuration file"
"temp_buffers";"32MB";"configuration file"
"unix_socket_directory";"/var/run/postgresql";"configuration file"
"wal_buffers";"32MB";"configuration file"
"work_mem";"5MB";"configuration file"

Any ideas on why the database would be fine for a very short period of time when a process starts, then fall off the cliff very quickly? It's almost like it's moving to disk, but it doesn't seem to be when these queries are EXPLAIN BUFFERS 'd.

Answer 1

If this is a single purpose machine, I would drop shared_buffers to a small fraction of what you have and increase effective_cache_size to 60GB. My reasoning is that the PostgreSQL caching is very featureful but it is also slower than the OS cache. On a multi-purpose machine, the Pg cache can be seen as reserved memory for Pg caching and that's quite helpful, but on a single purpose machine, there are significant tradeoffs between Pg cache and OS cache and they don't go all one way. The best use of the PostgreSQL cache is to keep the most commonly used data so that it never expires from the OS disk cache. Keep in mind that maintaining a buffer pool is expensive comparatively speaking and so getting the Pg buffer pool cache to the right size (not necessarily the highest size you can!) is critical.

A good place to start is actually with your old VM's PostgreSQL settings but upping effective_cache_size appropriately. This ensures you aren't having unexpected tradeoffs and can more easily compare your existing use cases. From there you need to look at your use cases and determine what values to tweak (this applies to work_mem also. If work_mem was sufficient on the old instance, there is very little to be gained from increasing it here).

Finally it would be helpful if you could run EXPLAIN (ANALYZE, BUFFERS) [query] on both systems if that is still not up to speed, so we can compare plans and offer better recommendations.