By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,512 Members | 791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,512 IT Pros & Developers. It's quick & easy.

Continuous performance regression

P: n/a
Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
What's about reindexing your tables?
I doubt that vacuum takes care about it.
See the SQL command "REINDEX"
And I don't think doing full vacuum makes sense since the
space is not a crucial thing for you.
I would use VACUUM ANALYZE.

"Przemyslaw Mazur" <pm****@post.pl> wrote in message
news:ct**********@atlantis.news.tpi.pl...
Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.


Jul 19 '05 #2

P: n/a
THANK YOU VERY MUCH!

REINDEX was what I needed.

VACUUM FULL was an act of despair...

BR:
P.M.

"Nikolay A Mirin" <ni*****@mail.ru> wrote in message
news:gZ**************@newsread3.news.pas.earthlink .net...
What's about reindexing your tables?
I doubt that vacuum takes care about it.
See the SQL command "REINDEX"
And I don't think doing full vacuum makes sense since the
space is not a crucial thing for you.
I would use VACUUM ANALYZE.

"Przemyslaw Mazur" <pm****@post.pl> wrote in message
news:ct**********@atlantis.news.tpi.pl...
Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.