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

Re: Postgres is slow

From: John R Pierce <pierce(at)hogranch(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Postgres is slow
Date: 2005-03-08 17:35:45
Message-ID: 422DE271.5010701@hogranch.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
 >>I am using Postgres 7.4 on Fedora 3.0. It is running with the default
 >>configuration.
....
> Probably this is not the right list for this.
> Did you run vacuum after inserting the records?
> Check your statement with explain analyze, it will show you why is it
> slow.

also, check serverlog in the data directory (/var/pgsql/data is the 
Redhat/Fedora default, I believe).  If you're logging lots of errors, that file 
will grow, and they don't manage it.

also, the defaults in $PGDATA/postgresql.conf in 7.4 are *VERY* inefficient. 
there are a few key parameters that can make a significant difference in 
overall performance.

and, crontab (from the postgres user account) a vacuumdb every hour or so if 
you're doing lots of activity.   once a day or week, do a vacuumdb -z to 
reindex stuff.

and, shut postgres down, mv the $PGDATA directory to a different dedicated 
physical disk drive, and symlink it, then restart postgres.

I've found that 7.x pgsql databases periodically need to be pg_dump'd, dropdb, 
then reloaded from the dump to continue to perform well...  on a heavy use web 
server database which includes a busy user forum, I find doing this about 2-3 
times a year keeps things smooth.  there may be a better way to clean them up, 
but I've not found it.

In response to

Responses

pgsql-jdbc by date

Next:From: Prajakt DeolaseeDate: 2005-03-08 17:51:08
Subject: Re: Postgres is slow
Previous:From: Laszlo HornyakDate: 2005-03-08 14:07:02
Subject: Re: Postgres is slow

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