Thread: Performance.

Performance.

From
"Hargobind Singh"
Date:
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
--------------------------



Re: Performance.

From
matt
Date:
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
>