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.
fsync=off
on the old host? Or was the old host a slower CPU with less ram but with an SSD, perhaps?