Re: 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