Обсуждение: When will my database crash?
We have a postgres 7.4 database which never vacuum for 4 months. I try to vacuum one time. But my manager can't bear the low performance of website. So I had to kill the vacuum before it finished. Is it ok for a postgres 7.4 database never do vacuuming? We have 10,000 trans every day. That would be 10,000 inserts and 10,000 updates each day. Regards Simon Chen.
After takin a swig o' Arrakan spice grog, sc@eg-1.com ("S. C.") belched out:
> We have a postgres 7.4 database which never vacuum for 4 months. I try to
> vacuum one time. But my manager can't bear the low performance of website. So
> I had to kill the vacuum before it finished. Is it ok for a postgres 7.4
> database never do vacuuming?
>
> We have 10,000 trans every day. That would be 10,000 inserts and 10,000
> updates each day.
No, what you need to do is to _regularly_ vacuum it. That is, _ALL
THE TIME_.
Not vacuuming for four months was a severe error, which means that you
now need to run a really full maintenance cycle. At the next possible
opportunity, you really need to do a VACUUM FULL on the whole
database.
It's a little like running an automobile and not bothering to change
the oil or to do any other maintenance for several years. You'd
discover that you need to take the automobile entirely out of service
for an extended period in order to do _major_ repair work, and the
fact that this inconveniences someone is irrelevant.
_Perhaps_ you can do the maintenance a table at a time, here and
there.
But whenever you kill a vacuum, you are discarding any work it has
done. Don't waste everyone's time by watching it for a while, and
then stopping it; keep vacuuming until the tables are DONE.
And then put in a proper maintenance regimen, perhaps using
pg_autovacuum, or at least doing a vacuum of the whole database at low
periods either daily, or at worst, weekly.
In some of our systems, we have tables that need to get vacuumed every
five minutes so that the cost of vacuuming never gets unbearable.
Vacuuming every five minutes goes in a flash because there are
normally only a few hundred tuples, with only a couple hundred of them
"live." For those tables, vacuuming once an hour would be
unacceptable, and would destroy performance of the systems.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/wp.html
'Mounten' wird fuer drei Dinge benutzt: 'Aufsitzen' auf Pferde,
'einklinken' von Festplatten in Dateisysteme, und, nun, 'besteigen'
beim Sex.
Christopher Browne <cbbrowne@acm.org> writes:
> Not vacuuming for four months was a severe error, which means that you
> now need to run a really full maintenance cycle. At the next possible
> opportunity, you really need to do a VACUUM FULL on the whole
> database.
Dump, initdb, reload would very likely be less painful ...
Something that hasn't ever been very clearly documented is that the
VACUUM FULL mechanism is designed for compacting out relatively small
amounts of dead space, like maybe 10-20%. When you've got a table
that's got so much dead space that VACUUM FULL is going to move nearly
all the live rows, the proposition is a loser. CLUSTER or dump/reload
will be better, because those approaches don't bother to try to maintain
the existing indexes incrementally: they throw away the indexes and
start from scratch.
regards, tom lane
> S. C. > > We have a postgres 7.4 database which never vacuum for 4 months. I try to > vacuum one time. But my manager can't bear the low performance of > website. So > I had to kill the vacuum before it finished. Is it ok for a postgres 7.4 > database never do vacuuming? > > We have 10,000 trans every day. That would be 10,000 inserts and 10,000 > updates each day. > Well, at 10,000 trans per day, you have many years yet before fatal problems occur, but thats not OK. It is possible to run for a long time without vacuuming, if your application performance does not degrade significantly as a result - but that is very dependant upon the exact application/database design. Unfortunately, most designs will degrade. The danger is that your performance grows slightly worse every second, with the risk that some query plans may eventually switch to a different execution path and the viability of your website might come to a sudden halt. Proactive, planned maintenance is more manageable than sudden, grinding failure when you're at home in bed. Make sure your manager is part of the call-out plan, thats usually a good way to make them care about maintenance. For most applications, you should be running VACUUM FULL at least monthly, since any more than that is effectively the same thing as "never", as your case shows. Best Regards, Simon Riggs
simon@2ndquadrant.com ("Simon Riggs") wrote:
> Proactive, planned maintenance is more manageable than sudden,
> grinding failure when you're at home in bed. Make sure your manager
> is part of the call-out plan, thats usually a good way to make them
> care about maintenance.
And regular maintenance allows "maintenance sessions" to become
individually _much_ less costly. If the heavily-updated tables were
vacuumed daily or even hourly, it is quite likely that the issue would
go away, from whence comes the "pg_autovacuum" strategy.
> For most applications, you should be running VACUUM FULL at least
> monthly, since any more than that is effectively the same thing as
> "never", as your case shows.
So long as you vacuum heavily-updated tables often enough, run 'plain
VACUUM ANALYZE' once in a while, to catch the transaction ID rollover
issue, and have enough space in the free space map, VACUUM FULL
shouldn't be necessary.
At Afilias, we _never_ run VACUUM FULL in the production transactional
environment, or at least we haven't needed to since migrating to 7.4.
(On 7.2, we needed to do so periodically, as well as periodically
reindexing some tables.)
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/sap.html
"A good system can't have a weak command language." -- Alan Perlis
[This explains why MS-DOS and Windows can't possibly be good
systems...]
unsubscribe E-Sulat v.0.0.1 ( Manna ) ....................................................................................... An extended module for MUWeb4 Project <http://www.mu.edu.ph> Powered by Horde, Postfix, PostgreSQL & Courier-IMAP
unsubscribe E-Sulat v.0.0.1 ( Manna ) ....................................................................................... An extended module for MUWeb4 Project <http://www.mu.edu.ph> Powered by Horde, Postfix, PostgreSQL & Courier-IMAP