Thread: Performance
My question is on Postgres performance. I have three systems all running the same database and have the same data feed. One the of the systems is running 7.4.8 and the other two are at 8.1.4 They all run under Linux. The type of machines are not really important to my question, since I am not comparing the performance of each machine. All three machines have been tuned. Logs do not indicate any problems The total size of the database is about 500M+ in size. There are several large tables which are 9M in size. These large tables add and delete about 200K rows per day. The total database size has not varied by much over the past two years. Now my question. The performance of each of these databases is decreasing. I measure the average insert time to the database. This metric has decreased by about 300 percent over the last year. I run vacuum analyze and vacuum analyze full on a daily bases. I also run vacuum 4 time a day. Still the performance continues to drop. Are there some other performance activities I may try?. Are there other monitoring options I can use to indicate where the slow down is occurring? Thanks Don
On Wed, May 23, 2007 at 10:12:09AM -0700, Donald Laurine wrote: > Now my question. The performance of each of these databases is > decreasing. I measure the average insert time to the database. This > metric has decreased by about 300 percent over the last year. I run > vacuum analyze and vacuum analyze full on a daily bases. I also run > vacuum 4 time a day. Still the performance continues to drop. Are there > some other performance activities I may try?. Are there other monitoring > options I can use to indicate where the slow down is occurring? Forget the VACUUM FULL, it's not worth running that often, it tends to fragment indexes. At a guess it's probably index fragmentation thats getting you. Try reindexing some of the larger tables, or just reindex everything. Also, CLUSTER is better at compacting tables than VACUUM FULL. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Donald Laurine wrote:
> My question is on Postgres performance. I have three systems all running
> the same database and have the same data feed. One the of the systems is
> running 7.4.8 and the other two are at 8.1.4 They all run under Linux.
> The type of machines are not really important to my question, since I am
> not comparing the performance of each machine. All three machines have
> been tuned. Logs do not indicate any problems
>
> The total size of the database is about 500M+ in size. There are several
> large tables which are 9M in size. These large tables add and delete
> about 200K rows per day. The total database size has not varied by much
> over the past two years.
>
> Now my question. The performance of each of these databases is
> decreasing. I measure the average insert time to the database. This
> metric has decreased by about 300 percent over the last year. I run
> vacuum analyze and vacuum analyze full on a daily bases. I also run
> vacuum 4 time a day. Still the performance continues to drop. Are there
> some other performance activities I may try?. Are there other monitoring
> options I can use to indicate where the slow down is occurring?
sure... :), default_statistics_target. If it is too low, your estimates
might be off and the planner might be choosing the wrong plan.
Joshua D. Drake
>
> Thanks Don
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On May 23, 2007, at 1:12 PM, Donald Laurine wrote: > Now my question. The performance of each of these databases is > decreasing. I measure the average insert time to the database. This > metric has decreased by about 300 percent over the last year. I run > vacuum analyze and vacuum analyze full on a daily bases. I also run > vacuum 4 time a day. Still the performance continues to drop. Are > there some other performance activities I may try?. Are there other > monitoring options I can use to indicate where the slow down is > occurring? I am of the fragmented index camp. re-index your table that gets the large number of inserts and deletes. compare the index sizes before and after... something like select relpages,relname from pg_class where relname like 'mybigtable%'; should tell you the sizes.
I know this is in the docs somewhere, and it's probably staring me in the face, but I haven't been able to find it: I'm running 8.2.4 through npgsql - how do I log: 1) connections to the database 2) updates, deletes, adds Is this set in the ./configuration? Or in the startup command line? I'm running on Mac OS X server using the Ray Aspeitia/David Wheeler startup script with log rotation turned on (using /usr/sbin/rotatelogs the Apache log rotation utility). Thanks for the help! -Owen
2007/6/3, Owen Hartnett <owen@clipboardinc.com>: > > I know this is in the docs somewhere, and it's probably staring me in > the face, but I haven't been able to find it: > > I'm running 8.2.4 through npgsql - how do I log: > > 1) connections to the database > > 2) updates, deletes, adds > > Hello, look postgresql.conf in your data directory and set log_connection = on log_statement = 'mod' Regards Pavel
Owen Hartnett <owen@clipboardinc.com> wrote: > > > I know this is in the docs somewhere, and it's probably staring me in > the face, but I haven't been able to find it: > > I'm running 8.2.4 through npgsql - how do I log: > > 1) connections to the database > > 2) updates, deletes, adds > > > Is this set in the ./configuration? Or in the startup command line? It can be specified on the startup command line, but it's much easier to put it in postgresql.conf: http://www.postgresql.org/docs/8.2/static/config-setting.html There are parameters for the specific things you want to control: http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html HTH -- Bill Moran http://www.potentialtech.com