[postgis-users] postgis tuning parameters

[Michael Smedberg]

I don't THINK you have to recompile- I think Mark was on the right
track.

You can reset shmmax on Linux using the instructions in the "Linux"
section of
http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html,
and then you can bump up the Postgres shared_buffers setting.

Basically, the OS isn't allowing Postgres to start because Postgres is
asking for too much memory.  You need to tell the OS to allow processes
to grab lots of memory, then tell Postgres to grab it.

I did this recently on a CentOS machine.  It was pretty easy and
improved performance greatly.

Good luck!

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Webb
Sprague
Sent: Wednesday, September 26, 2007 9:32 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] postgis tuning parameters

> > When performing complex queries, with thousands of records and
> > geometries I am noticing that the postmaster process never exceeds
1%
> > of my system's memory (2Gb). I have tried setting the shared_buffers
> > parameter to values > 2000 ... but this only leads to an error when
the
> > postgres server is restarted: cannot allocate this much mem, check
the
> > SHMMAX kernel parameter.

It sounds to me like you need to recompile Postgres with new values
for SHMAX (and maybe other parameters too - I would ask on the
Postgres General list).  Building postgres is trivial, and if it is
for a production machine it might be quite worthwhile -- there might
be options for stripping debug symbols etc .

-W

> >
> > Perhaps some answers on are this page:
> >
http://www.redhat.com/docs/manuals/database/RHDB-2.1-Manual/admin_user/
> > kernel-resources.html
> >
> > or in the minds of people on the list. The machine in question is
> > mostly used for such queries and I would like postgis to be able to
> > better utilize the resources available.
> >
> > Cheers,
> >
> > Dylan
>
>
> Hi Dylan,
>
> Indeed, everything you need to know should be on that page - here is
the
> most recent version:
> http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html.
> Experiment with different settings, but most people tend to recommend
> about 25% of your available RAM (~60,000 buffers for 2Gb) for a
> dedicated database server.
>
>
> HTH,
>
> Mark.
>
> --
> ILande - Open Source Consultancy
> http://www.ilande.co.uk
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users