Обсуждение: Postgres is slow

Dear All,

I am using Postgres 7.4 on Fedora 3.0. It is running with the default
configuration.

I have a Java code which is using the JDBC 3.0 implementation and the
connection pooling implementation of the Postgres.

I have some tests running against this implementation. I do inserts of
100,000 records. The primary key of this table is a byte array.

The time required for lookup on this 100,000 records keeps increasing
with subsequent tests even though I am not inserting/updating any
recors.

I also noticed that with every test the size of "base" directory which
is there in my DB keeps increasing. I don't think this is a log
directory, then why should its size increase with just "select"
queries? What is it logging?

Can somebody help on this? I think its some misconfiguration on my part?

-Prajakt

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.

On Tue, 8 Mar 2005, Prajakt Deolasee wrote:

> Dear All,
>
> I am using Postgres 7.4 on Fedora 3.0. It is running with the default
> configuration.
>
> I have a Java code which is using the JDBC 3.0 implementation and the
> connection pooling implementation of the Postgres.
>
> I have some tests running against this implementation. I do inserts of
> 100,000 records. The primary key of this table is a byte array.
>
> The time required for lookup on this 100,000 records keeps increasing
> with subsequent tests even though I am not inserting/updating any
> recors.
>
> I also noticed that with every test the size of "base" directory which
> is there in my DB keeps increasing. I don't think this is a log
> directory, then why should its size increase with just "select"
> queries? What is it logging?
>
> Can somebody help on this? I think its some misconfiguration on my part?
>
> -Prajakt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

 >>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.

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
>

On Tue, 2005-03-08 at 11:35, John R Pierce wrote:

> 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.

The most common cause here was index bloat.  dropping / recreating the
indexes usually fixes the performance problems.  reindex indexname from
the psql command line works well to do this.

note that some usage patterns that cause catalog indexes to bloat can
only be corrected by reindexing while in single user mode.

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

up the buffer size (I don't have the docs in front of me, I'm at a iinternet
cafe on wifi at the moment), set WAL_SYNC_TYPE (or whatevr it is) to fdatasync,
up the wal buffers some too, increase the sort buffer size significantly, yada
yada.   don't go overboard, just reasonable.   I usually use about 10000 main
buffers (80MB).

> 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?

watch your disk IO.  `iostat -x sda -x sdb -x sdc 5` is your friend (replace
the sda b c with whatever your physical drives are called).   keep the await
times under 100mS if you can.  *never* use RAID5 for database volumes, use
raid1 or 10 (striped mirrors).  With postgres 8, you can split your tablespaces
across several physical disk drives, this can help alot if you can balance your
disk IO.   doing this requires some finesse and careful analysis of your
database usage patterns.  postgres stats can help determine which tables are
getting the most insert/update activity, you want to balance these across the
available physical drives.

good database administrators who understand all this sort of tuning are a rare
breed, and in demand.  I have a oracle DBA on staff who knows this stuff really
well for oracle... the same principles apply to postgres, but are less well
understood.

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

Prajakt Deolasee <bugzilla.prajakt@gmail.com> writes:
> 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.

This is surely a matter of dead-row bloat.  Are you vacuuming the tables
between runs?  Do you have the FSM parameters set high enough to keep
track of all the dead space you create by UPDATEs or DELETEs?

I was just looking at the "sql-bench" stuff that MySQL distributes.  One
of the tests that we look particularly awful on goes like this:

    * build table with 300,000 rows.
    * full-table update (UPDATE foo SET col = something)
    * repeat full-table update 10 times

With a VACUUM after each update, things would be fine, but since they
don't have one in the loop the table bloats to 10 times its normal size
(as do its indexes) and performance goes quickly south ... and not only
on that test, but on the subsequent ones using the same table ...

            regards, tom lane