Re: Performance. - Mailing list pgsql-admin

From matt
Subject Re: Performance.
Date
Msg-id 1053089038.5267.14.camel@gibraltar.mattclark.net
Whole thread Raw
In response to Performance.  ("Hargobind Singh" <hgs@acplexports.com>)
List pgsql-admin
Postgres comes by default tuned to run on a mobile phone (or maybe a
Palm Pilot), not a database server, so you need to increase the amount
of shared memory, semaphores &c available on your system.

Look for 'kernel parameters' in the docs when they come back up.

*Minimally* you want to add the following lines at the top of
/etc/init.d/postgresql:

echo  67108864 > /proc/sys/kernel/shmall
echo  67108864 > /proc/sys/kernel/shmmax
echo "250 32000 32 500" > /proc/sys/kernel/sem

And set

shared_buffers = 4096           # min max_connections*2 or 16, 8KB each

In /var/lib/pgsql/data/postgresql.conf

then do /etc/init.d/postgresql restart

That would give you 32MB of shared buffers for postgres to use, which is
what I have for dev boxen.  For live servers it all depends on the size
of your DB and the amount of RAM available.  As with any DB you really
need enough shared memory to keep pretty much all the active DB tables
in, otherwise you'll be hitting the disk all the time.  My production DB
server has 512MB of shared mem, and I'll be upping that soon...



On Fri, 2003-05-16 at 09:40, Hargobind Singh wrote:
> I have a linux box P4 1.7, 512 RAM, 40GB HDD with 1.2 GB swap partition,
> total 20GB free on the hard disk. RedHat 8.0 with GNome installed, and
> Postgres 7.2 installed.
>
> The performance of Postgres is very very poor. I have this table with 19
> fields, a primary key, and i have indexed it as well. A single select query
> on that filters a  single field takes 9 seconds to execute. THe table has
> 1,40,000 records.
>
> Any place where i can find OPTIMIZATION of PostGres ??
>
> Thanx..
>
> Hargobind Singh
> --------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>


pgsql-admin by date:

Previous
From: lexx@gorodok.net
Date:
Subject: db recovery
Next
From: "Bjoern Metzdorf"
Date:
Subject: Re: db recovery