Alvaro Herrera wrote:
Karl Wright wrote:
Alvaro Herrera wrote:
Karl Wright wrote:
This particular run lasted four days before a VACUUM became essential.
The symptom that indicates that VACUUM is needed seems to be that the
CPU usage of any given postgresql query skyrockets. Is this essentially
correct?
Are you saying you weren't used to run VACUUM all the time? If so,
that's where the problem lies.
Postgresql 7.4 VACUUM runs for so long that starting it with a cron job
even every 24 hours caused multiple instances of VACUUM to eventually be
running in my case. So I tried to find a VACUUM schedule that permitted
each individual vacuum to finish before the next one started. A vacuum
seemed to require 4-5 days with this particular database - or at least
it did for 7.4. So I had the VACUUM schedule set to run every six days.
How large is the database? I must admit I have never seen a database
that took 4 days to vacuum. This could mean that your database is
humongous, or that the vacuum strategy is wrong for some reason.
The database is humongus, and the machine is under intense load. On the
instance where this long vacuum occurred, there were several large
tables - one with 7,000,000 rows, one with 14,000,000, one with
140,000,000, and one with 250,000,000.
You know that you can run vacuum on particular tables, right? It would
be probably a good idea to run vacuum on the most updated tables, and
leave alone those that are not or little updated (hopefully the biggest;
this would mean that an almost-complete vacuum run would take much less
than a whole day).
Yeah, sorry, that doesn't apply here.
Or maybe vacuum was stuck waiting on a lock somewhere.
I will be experimenting with 8.1 to see how long it takes to complete a
vacuum under load conditions tonight.
You can also turn autovacuum on in 8.1, which might help quite a bit
with finding a good vacuum schedule (you would need a bit of tuning it
though, of course).
In any case, if you are struggling for performance you are strongly
adviced to upgrade to 8.2.
Ok - that's something I should be able to do once we can go to debian's
etch release. There's a backport of 8.2 available there. (The one for
sarge is still considered 'experimental').
Karl
