469,575 Members | 1,498 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

Continuous performance regression

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

Similar topics

19 posts views Thread by Nicolas Pernetty | last post: by
3 posts views Thread by Hans Karman | last post: by
8 posts views Thread by Michael van der Veeke | last post: by
11 posts views Thread by Doug Bell | last post: by
12 posts views Thread by Lars Schouw | last post: by
8 posts views Thread by Dave Potts | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.