Videos / Other Resources

No attachments were found.

Advanced PostgreSQL Tuning

Overview:

Is there anything I can do to speed up my PostgreSQL?

 

Note:  Advanced PC Users only!!!

 

 


Solution:

We came across the following post on 2+2 about how to speed up PostgreSQL.

Note: (Use at your own risk)

 

  • Find your postgresql.conf file and open in notepad. Mine was in C:\Program Files\PostgreSQL\8.2\data 
  • Save a copy of this file to your \Desktop before you make any changes.  This way if you have any problems after making the edits to the file you can recover the original file.

 

  • Find the 'shared_buffers' value. The default value is comically low, but a good value is 1/4 of your total memory. I set mine to 768MB as I have 4GB of RAM (of which a little more than 3GB actually can be used in non 64-bit systems). 

 

  • Find the 'effective_cache_size' value. The default for this is also very low, but can be set to upward of 1/2 of your total memory. I set mine to 1536MB. 

 

  • Find the 'redirect_stderr' value and change it to 'off', unless you have a specific reason for looking at log files. If you're like me, the HEM queries result it tons of log data being written out. I had 10MB being written out every 20 minutes. Not only does this eat up tons of disk space, but this will cause your system to be wasting time writing to disk for logs you'll likely never look at.

 




| More

Related Articles


User Opinions (5 votes)

100% thumbs up 0% thumbs down

How would you rate this answer?



Thank you for rating this answer.

Visitor Comments

No visitor comments posted. Post a comment

Post a comment

To post a comment for this article, simply complete the form below. Fields marked with an asterisk are required.
   Name:
   Email:
* Comment:
* Enter the code below:
 

Continue