pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedshould i expected performance degradation over time
5+ messages / 4 participants
[nested] [flat]
* should i expected performance degradation over time
@ 2011-09-10 16:55 Anibal David Acosta <aa@devshock.com>
0 siblings, 2 replies; 5+ messages in thread
From: Anibal David Acosta @ 2011-09-10 16:55 UTC (permalink / raw)
To: pgsql-performance
Sometimes I read that postgres performance is degraded over the time and
something people talk about backup and restore database solve the problem.
It is really true?
I have postgres 9.0 on a windows machine with The autovacuum is ON
I have some configuration tables
And a couple of transactional table.
Transactional table has about 4 millions of rows inserted per day.
In the midnight all rows are moved to a historical table and in the
historical table rows are about 2 months, any transaction older than 2
months are deleted daily.
So, my question is, if Should I expect same performance over time (example:
after 1 year) or should I expect a degradation and must implements come
technics like backup restore every certain time?
Thanks!!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: should i expected performance degradation over time
@ 2011-09-10 17:20 Andy Colson <andy@squeakycode.net>
parent: Anibal David Acosta <aa@devshock.com>
1 sibling, 0 replies; 5+ messages in thread
From: Andy Colson @ 2011-09-10 17:20 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance
On 09/10/2011 11:55 AM, Anibal David Acosta wrote:
> Sometimes I read that postgres performance is degraded over the time and something people talk about backup and restore database solve the problem.
>
> It is really true?
>
> I have postgres 9.0 on a windows machine with The autovacuum is ON
>
> I have some configuration tables
>
> And a couple of transactional table.
>
> Transactional table has about 4 millions of rows inserted per day.
>
> In the midnight all rows are moved to a historical table and in the historical table rows are about 2 months, any transaction older than 2 months are deleted daily.
>
> So, my question is, if Should I expect same performance over time (example: after 1 year) or should I expect a degradation and must implements come technics like backup restore every certain time?
>
> Thanks!!
>
Yes. And no. Things have changed over that last few versions. In older version of PG I recall hearing about table bloat problems that were really bad, and backup/restore would fix it. (Vacuum full would probably also have fixed it).
"Vacuum full", in older versions was a last chance, bring a gun to a knife fight, nothing else has worked, fix table bloat solution. Its not dis-similar from backup/restore.
In newer versions of PG, autovacuum, vacuum and vacuum full are all much nicer and work better. I really doubt you'll need to resort to backup/restore to fix problems.
Just remember: the harder you hit a table, the less chance autovacuum will have to clean it up. So you might need manual vacuum. autovacuum will cancel itself if the table is getting hit, where-as manual vacuum wont.
Keeping on top of vacuum will keep your tables slim and trim. If things get out of hand, they'll balloon into problems. Vacuum full at that point should clean it up. But, if you ignore the problem for two years, and have super really bad table bloat, well, maybe backup/restore is best.
-Andy
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: should i expected performance degradation over time
@ 2011-09-10 18:30 Scott Marlowe <scott.marlowe@gmail.com>
parent: Anibal David Acosta <aa@devshock.com>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2011-09-10 18:30 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance
On Sat, Sep 10, 2011 at 10:55 AM, Anibal David Acosta <aa@devshock.com> wrote:
> Sometimes I read that postgres performance is degraded over the time and
> something people talk about backup and restore database solve the problem.
>
> It is really true?
Yes and no. If you let things get out of hand, a backup and restore
may be your best choice.
> I have postgres 9.0 on a windows machine with The autovacuum is ON
Good start
> Transactional table has about 4 millions of rows inserted per day.
>
> In the midnight all rows are moved to a historical table and in the
> historical table rows are about 2 months, any transaction older than 2
> months are deleted daily.
You should look into table partitioning then. but as long as vacuum
keeps up you're probably still ok. Look at the check_postgresql.pl
script by the same guy who wrote Bucardo. It'll keep you advised of
how much bloat your tables have.
> So, my question is, if Should I expect same performance over time (example:
> after 1 year) or should I expect a degradation and must implements come
> technics like backup restore every certain time?
If you maintain your db properly, performance should stay good. If
you ignore bloat issues you might have some issues.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: should i expected performance degradation over time
@ 2011-10-11 18:20 Anibal David Acosta <aa@devshock.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Anibal David Acosta @ 2011-10-11 18:20 UTC (permalink / raw)
To: 'Scott Marlowe' <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Do you know if check_postgresql.pl can run on windows (with perl installed)?
Because our postgres installation is running on a Windows 2008 R2 server but
can't find any tool like this for windows :(
Thanks!
-----Mensaje original-----
De: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Enviado el: sábado, 10 de septiembre de 2011 02:30 p.m.
Para: Anibal David Acosta
CC: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] should i expected performance degradation over time
On Sat, Sep 10, 2011 at 10:55 AM, Anibal David Acosta <aa@devshock.com>
wrote:
> Sometimes I read that postgres performance is degraded over the time
> and something people talk about backup and restore database solve the
problem.
>
> It is really true?
Yes and no. If you let things get out of hand, a backup and restore may be
your best choice.
> I have postgres 9.0 on a windows machine with The autovacuum is ON
Good start
> Transactional table has about 4 millions of rows inserted per day.
>
> In the midnight all rows are moved to a historical table and in the
> historical table rows are about 2 months, any transaction older than 2
> months are deleted daily.
You should look into table partitioning then. but as long as vacuum keeps
up you're probably still ok. Look at the check_postgresql.pl script by the
same guy who wrote Bucardo. It'll keep you advised of how much bloat your
tables have.
> So, my question is, if Should I expect same performance over time
(example:
> after 1 year) or should I expect a degradation and must implements
> come technics like backup restore every certain time?
If you maintain your db properly, performance should stay good. If you
ignore bloat issues you might have some issues.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: should i expected performance degradation over time
@ 2011-10-28 19:03 Robert Haas <robertmhaas@gmail.com>
parent: Anibal David Acosta <aa@devshock.com>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Haas @ 2011-10-28 19:03 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On Tue, Oct 11, 2011 at 2:20 PM, Anibal David Acosta <aa@devshock.com> wrote:
> Do you know if check_postgresql.pl can run on windows (with perl installed)?
>
> Because our postgres installation is running on a Windows 2008 R2 server but
> can't find any tool like this for windows :(
>
> Thanks!
It's written in Perl, so I would think you could get it to work. But
if not, you can always extract the big ol' query that it runs from the
script and run it some other way.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-10-28 19:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-10 16:55 should i expected performance degradation over time Anibal David Acosta <aa@devshock.com>
2011-09-10 17:20 ` Andy Colson <andy@squeakycode.net>
2011-09-10 18:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-10-11 18:20 ` Anibal David Acosta <aa@devshock.com>
2011-10-28 19:03 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox