[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3341
  • Last Modified:

How to optimize PostgreSQL for a 16GB RAM Quad-Core machine?

Hi,
I would like to optimize postgres for the best possible performance for a single query. I am not going to use it in a multi-user environment, but performance of a single query is most important.
Could someone suggest correct config settings for PostgreSQL 8.2 for the following hardware:
Dell PowerEdge 2900, 1 Xeon 2.6GHz Quad-Core processor, 16GB memory, 4 RAID0 15000RPM SAS hard-disks, Windows 2003 R2 64Bit Server Edition (default configuration).
When I just use the default config file, I have a feeling postgres is not even using a 0.1% of the given resources, especially in terms of memory.
0
mersis
Asked:
mersis
  • 2
  • 2
1 Solution
 
adrpoCommented:

First of all, you have Windows 2003 R2 64Bit Server Edition (default configuration), which means
that you run PostgreSQL via Cygwin. I belive some of the performance will be lost in the Cygwin layer.

That beeing said, I don't think that anyone will just give you such configuration for your server, because is quite new and because there aren't so many people running PostgreSQL in top of Cygwin in an Windows environment.

To find the best you have to try several posibilities and see which one is the best.
I would start with running EXPLAIN on your query and see how the query is planned, maybe you
get some hints from there on what is going on in the server and what you can improve.

Some pointers to what I would try first:
1. Operating System Environment
    http://www.postgresql.org/docs/8.2/static/kernel-resources.html
    You can play with some of these setting if they are available in Cygwin.
2. Server Configuration
    http://www.postgresql.org/docs/8.2/static/runtime-config.html
    Start with: Resource Consumption
    http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html
    - increase shared_buffers, temp_buffers
    - i would definitely increase work_mem and maintenance_work_mem
      if you say that you only have one query
3. Play with WAL settings:
    http://www.postgresql.org/docs/8.2/static/wal-configuration.html

You can use the runtime statistics to see which configuration is better:
http://www.postgresql.org/docs/8.2/static/runtime-config-statistics.html

Also, some tips on performance are here:
http://www.postgresql.org/docs/8.2/static/performance-tips.html

Cheers,
za-k/
0
 
mersisAuthor Commented:
Thanks adrpo. No, I am not using cygwin to run PostgreSQL. Why would I need it?
I have just downloaded the windows installer for postgreSQL and used it. It worked fine and the database works well too.
0
 
adrpoCommented:

My bad. I sow now that they made a MinGW-based version.
Sorry about that. However, you can still play with points 2 and 3 from my last post.

Cheers,
za-k/
0
 
mersisAuthor Commented:
Ok thanks adrpo, will have a look at those links and see if it makes things any better.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now