Karl Wright wrote:
Scott Marlowe wrote:
Karl Wright wrote:
Shaun Thomas wrote:
On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote:
I am afraid that I did answer this. My largest tables
are the ones continually being updated. The smaller
ones are updated only infrequently.
You know, it actually sounds like you're getting whacked by the
same problem that got us a while back. It sounds like you weren't
vacuuming frequently enough initially, and then tried vacuuming
later, only after you noticed performance degrade.
Unfortunately what that means, is for several weeks or months,
Postgres has not been reusing rows on your (admittedly) active and
large tables; it just appends at the end, and lets old rows slowly
bloat that table larger and larger. Indexes too, will suffer from
dead pages. As frightening/sickening as this sounds, you may need
to dump/restore the really huge table, or vacuum-full to put it on
a crash diet, and then maintain a strict daily or bi-daily vacuum
schedule to keep it under control.
A nice try, but I had just completed a VACUUM on this database three
hours prior to starting the VACUUM that I gave up on after 27
hours. So I don't see how much more frequently I could do it. (The
one I did earlier finished in six hours - but to accomplish that I
had to shut down EVERYTHING else that machine was doing.)
So, have you ever run vacuum full or reindex on this database?
No. However, this database has only existed since last Thursday
afternoon.
Well, a couple of dozen update statements with no where clause on large
tables could bloat it right up.
It's not about age so much as update / delete patterns.
You are aware of the difference between how vacuum and vacuum full
work, right?
vacuum := mark deleted tuples as available, leave in table
vacuum full := compact tables to remove deleted tuples.
While you should generally avoid vacuum full, if you've let your
database get so bloated that the majority of space in your tables is
now empty / deleted tuples, you likely need to vacuuum full / reindex
it.
If the database is continually growing, should VACUUM FULL be necessary?
If it's only growing, with no deletes or updates, then no. Generally,
on a properly vacuumed database, vacuum full should never be needed.
For instance, on my tiny little 31 Gigabyte reporting database, the
main table takes up about 17 Gigs. This query gives you some idea
how many bytes each row is taking on average:
select relname, relpages::float*8192 as size, reltuples,
(relpages::double precision*8192)/reltuples::double precision as
bytes_per_row from pg_class where relname = 'businessrequestsummary';
relname | size | reltuples | bytes_per_row
------------------------+-------------+-------------+-----------------
businessrequestsummary | 17560944640 | 5.49438e+07 | 319.61656229454
Note that these numbers are updated by running analyze...
What does it say about your DB?
I wish I could tell you. Like I said, I had to abandon this project
to test out an upgrade procedure involving pg_dump and pg_restore.
(The upgrade also seems to take a very long time - over 6 hours so
far.) When it is back online I can provide further information.
Well, let us know. I would definitely recommend getting more / faster
disks. Right now I've got a simple 4 disk RAID10 on the way to replace
the single SATA drive I'm running on right now. I can't wait.
