Re: Postgres is slow

scaling to 1000 inserts/second is a factor of hardware and tuning, not postgres in general

osdl is currently seeing 5000 transactions per seconds.

indexing on the byte array can definately be one of the issues.

Dave

Prajakt Deolasee wrote:

Thanks guys.. I will try of the suggested steps. But one thing that I
do not understand that why should the performance deteriorate with
exactly same data with subsequent tests. And its juts gets worse as I
keep running 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.   

John, do you have any idea which parameters to change?

I tried similar tests also against PSQL 8.0 as well. But the results
are exactly same.

My final usage is going to be exteremly high. I would need something
like 1000 inserts/updates per sec. Do you think PSQL will scale?

Can the indexing on the byte array be one of the issue?

-Prajakt


On Tue, 08 Mar 2005 09:35:45 -0800, John R Pierce <pierce@hogranch.com> wrote: 

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
   

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq

 

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561