On 19.04.2008, at 19:11, Christopher Browne wrote:
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Thomas
Spreng) wrote:
On 16.04.2008, at 17:42, Chris Browne wrote:
What I meant is if there are no INSERT's or UPDATE's going on it
shouldn't affect SELECT queries, or am I wrong?
Yes, that's right. (Caveat: VACUUM would be a form of update, in this
context...)
thanks for pointing that out, at the moment we don't run autovacuum but
VACUUM ANALYZE VERBOSE twice a day.
2. On the other hand, if you're on 8.1 or so, you may be able to
configure the Background Writer to incrementally flush checkpoint
data
earlier, and avoid the condition of 1.
Mind you, you'd have to set BgWr to be pretty aggressive, based on
the
"10s periodicity" that you describe; that may not be a nice
configuration to have all the time :-(.
I've just seen that the daily vacuum tasks didn't run,
apparently. The DB has almost doubled it's size since some days
ago. I guess I'll have to VACUUM FULL (dump/restore might be faster,
though) and check if that helps anything.
If you're locking out users, then it's probably a better idea to use
CLUSTER to reorganize the tables, as that simultaneously eliminates
empty space on tables *and indices.*
In contrast, after running VACUUM FULL, you may discover you need to
reindex tables, because the reorganization of the *table* leads to
bloating of the indexes.
I don't VACUUM FULL but thanks for the hint.
Pre-8.3 (I *think*), there's a transactional issue with CLUSTER where
it doesn't fully follow MVCC, so that "dead, but still accessible, to
certain transactions" tuples go away. That can cause surprises
(e.g. - queries missing data) if applications are accessing the
database concurrently with the CLUSTER. It's safe as long as the DBA
can take over the database and block out applications. And at some
point, the MVCC bug got fixed.
I think I'll upgrade PostgreSQL to the latest 8.3 version in the next
few days anyway, along with a memory upgrade (from 1.5GB to 4GB) and a
new 2x RAID-1 (instead of RAID-5) disk configuration. I hope that this
has already a noticeable impact on the performance.
Note that you should check the output of a VACUUM VERBOSE run, and/or
use the contrib function pgsstattuples() to check how sparse the
storage usage is. There may only be a few tables that are behaving
badly, and cleaning up a few tables will be a lot less intrusive than
cleaning up the whole database.
That surely is the case because about 90% of all data is stored in one
big table and most of the rows are deleted and newly INSERT'ed every
night.
cheers,
tom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
