Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i've got a Ubuntu Linux system with 12Gb memory most of which (at least 10Gb) can be allocated solely to postgres. the system also has a 6 disk 15k SCSI RAID 10 setup.

The process i'm trying to optimise is twofold.

firstly a single threaded, single connection will do many inserts into 2-4 tables linked by foreign key.

secondly many different complex queries are run against the resulting data, using group by extensively. this part especially needs to be optimised.

i have four of these processes running at once in order to make use of the quad core CPU, therefore there will generally be no more than 5 concurrent connections (1 spare for admin tasks).

what configuration changes to the default Postgres config would you recommend?

I'm looking for the optimum values for things like work_mem, shared_buffers etc.

relevant doco

thanks!

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

I would lower the checkpoint_segments, each one will take 16MB, in total 1GB (64 * 16MB). Writing 1GB to disk, might take too long. If you have a RAID-card with 512MB RAM, just use 50% for your WALL-segments to get very good write performance: checkpoint_segments 16

Also lower the checkpoint_timeout to 2 or 3 minutes, also to get better and more predictable performance.

If you have a lot of INSERT's, use prepared statements, put a lot of INSERT's in a single transaction or even use COPY. COPY will give the best performance for inserts. Every transaction has to fit in a single wall buffer for best performance. You choose 2MB for wall buffers, test this to see if it's fine.

share|improve this answer
    
thanks have set checkpoint_segments to 16 and checkpoint_timeout to 3min. –  pstanton Mar 25 '10 at 8:07
add comment

Here's what I've come up with so far:

  • max_connections 100 -> 8
  • checkpoint_segments 3 -> 64
  • checkpoint_completion_target 0.5 -> 0.8
  • work_mem 1MB -> 64MB
  • maintenance_work_mem 16MB -> 256MB
  • log_temp_files -1 -> 0
  • wal_buffers 64kb -> 2MB
  • random_page_cost 4.0 -> 2.0
  • effective_cache_size 128MB > 9216MB
  • shared_buffers 24MB > 4096MB

hopefully someone can verify that those are reasonable settings or improve on this.

share|improve this answer
1  
Don't turn off autovacuum unless you've proven it's wrong. Push shared_buffers up to 4Gb. If you know you have that few connections, you can probably push work_mem up higher - but verify with log_temp_files if it's necessary. Other than that, you've gone pretty far - hard to say much more without a lot more details about the workload. –  Magnus Hagander Feb 1 '10 at 13:26
    
thanks will do. edited to match. ... or should work_mem be even higher? how do i determine from log_temp_files? if nothing being pushed to temp files it's high enough? –  pstanton Feb 1 '10 at 19:03
add comment

This link could be useful too: Tuning_Your_PostgreSQL_Server

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.