0

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!

3 Answers 3

1

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.

1
  • thanks have set checkpoint_segments to 16 and checkpoint_timeout to 3min.
    – pstanton
    Mar 25, 2010 at 8:07
0

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.

2
  • 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. Feb 1, 2010 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, 2010 at 19:03
0

This link could be useful too: Tuning_Your_PostgreSQL_Server

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .