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