Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL Tweaking

From: "renier" <renier(at)vvconsult(dot)co(dot)za>
To: "'Corrie Strydom'" <corrie206(at)gmail(dot)com>
Cc: <jnbpug(at)postgresql(dot)org>
Subject: Re: PostgreSQL Tweaking
Date: 2010-04-09 10:57:11
Message-ID: 293d25cb-0b22-479d-8756-2a5ca8358723@vvconsult.co.za (view raw or flat)
Thread:
Lists: jnbpug
Thanks a lot Corrie.  I will have a look at the links you posted in the next hour or two, but it seems that there could be some valuable info!
 
From: jnbpug-owner(at)postgresql(dot)org [mailto:jnbpug-owner(at)postgresql(dot)org] On Behalf Of Corrie Strydom
Sent: 09 April 2010 12:32 PM
To: renier
Cc: Alastair Turner; jnbpug(at)postgresql(dot)org
Subject: Re: [jnbpug] PostgreSQL Tweaking
 
On Fri, Apr 9, 2010 at 12:13 PM, renier <renier(at)vvconsult(dot)co(dot)za> wrote:
Thanks Bell.  One of the users on the group already responded, conversation attached which will give some more details, maybe more users can benefit from this conversation


-----------------Corrie reply---------------
Hi Renier, you'll have to be bit more specific regarding the setup there?

1) What version of postgres?
2)  What type of application will be using it? Website/intranet site/custom app ect?
3) How many connections are we talking here?
4) Maybe post your current config files to the web somewhere, where people can have a look?

More information you can give, the easier people can help you guys?

Corrie


-----------------My reply---------------

Hi Corrie.

Thanks for the reply.  I did not want to give too much info and bore the people that will not be interested to help.   But now that you mention it, I will probably also have to send out all these details that I’m sending you, to all willing participants.

We are running a PostgresPlus Standard Server, the latest stable 8.4 release with all updates. After the install (Whether we select Dedicated Server/Mixed) we need to replace the original config file as the service will not start up. I think it has to do with the amount of shared memory allocated.
The server runs Windows Server 2008 x64.  It will be mainly used for internal development (for now), and we also plan to set up a VM on the machine for hosting some ASP applications, mainly for demo purposes at clients, as it takes time to configure IIS on individual laptops all the time.  We will probably never have more than 10 connections, say 20 to provide a bit for the future. We are only 4 developers that will use it on a day to day basis.  The DB will mainly serve our own desktop applications.  And then the occasional demo or 2 at a client.

At the moment we are using the default config file as our tweaking does not seem to serve any purpose. Most of the time the service won’t even start (usually complaining about not having enough shared memory or something similar).  Running our “benchmark query”, (735000 spatial records), the CPU does not go above 1 or 2 % usage, but the query takes 3 ½ minutes to complete on the localhost.  Running it over the network, it runs in 48seconds, the same as on our dektop test pc.  Kinda makes us wonder why we just forked out 100k for a server?
This server also acts as a secondary domain controller, and we run VMWare on it (this will be for our Web environment).  We could dedicate up to 16GB of RAM for postgres

Thanks!
Renier
-----Original Message-----
From: Alastair Turner [mailto:bell(at)ctrlf5(dot)co(dot)za]
Sent: 09 April 2010 12:09 PM
To: renier
Cc: jnbpug(at)postgresql(dot)org
Subject: Re: [jnbpug] PostgreSQL Tweaking

On Fri, Apr 9, 2010 at 10:27 AM, renier <renier(at)vvconsult(dot)co(dot)za> wrote:
> Hi There.  Is there anyone in the SA PostgreSQL community that is willing to
> assist us.
>
>
>
> We started trials on PostgreSQL just over a year ago, so we used on old
> desktop lying around as our database server, which served fine for testing
> purposes. We eventually bought a new server (2x2.93GHZ Quad core Xeon, 32GB
> RAM, 6x450GB SAS 15000RPM drives etc), quite a decent machine, but the
> performance on this machine is the same, if not worse, than it is on our old
> “server”.  We tried tweaking the postgres.conf file but we do not really
> have enough knowledge of this file and performance does not increase, or the
> service wont start.
>
>
>
> Is there anyone out there with enough experience in postgreSQL tweaking that
> might be willing to assist in some db tuning, and help us learn?
>
> Regards,
>
> Renier
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature
> database 5012 (20100409) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
Hi Renier

Firstly, thanks for turning to the mailing list for assistance, it's
great to be getting some traffic.

You say that the server won't start after some of the config changes.
If these are to the shared memory settings then you're going to have
to tweak the kernel's shm_max and shm_all settings (assuming that this
is on a Linux system). The doc at
http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html
gives some good tips on the memory settings.

Exactly which of the bits you'll benefit from tweaking does depend
very much on your workload. Are you doing a lot of sorts, are you
doing a lot of inserts/updates ... ?

Regards

Bell.


--
Sent via jnbpug mailing list (jnbpug(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/jnbpug
 
 
 
Hi Renier, I'm not sure on what you have read up in the internet regarding postgres performance boosting, see http://ashleyangell.com/2009/10/postgresql-performance-optimization/ , I've used this a while back, and it helped.
 
the "work_mem" parameter in the .conf has given me trouble, if you make it to big, performance takes a huge knock, example on our 8Gb Win2003 server, I use 512mb.
 
Look at the sections describing "sharred_buffers".
 
Also, check your windows user "postgres" I remember there was a setting on the 2003 server I changed to help as well (not a great answer, but have a poke around)
 
also have a look at :
http://www.varlena.com/GeneralBits/Tidbits/perf.html#shbuf
http://www.linuxjournal.com/article/4791
 
A excellent cheet sheat is available at http://www.alberton.info/postgresql_cheat_sheet.html , see the queries against the INFORMATION_SCHEMA, helped in the tuning.
 
Remember, to tune and tweak the postgres server takes a few reboots and changes, there isn't a quick solution sorry. But in doing this, you'll learn so much more on how it works.
 
Corrie
 


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5012 (20100409) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

jnbpug by date

Next:From: Alastair TurnerDate: 2010-04-09 11:13:26
Subject: Re: PostgreSQL Tweaking
Previous:From: renierDate: 2010-04-09 10:54:25
Subject: Re: PostgreSQL Tweaking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group