Re: [GENERAL] Benchmarks - Mailing list pgsql-general

On Thu, 6 Jan 2000, The Hermit Hacker wrote:

# > d) Postgres manual recommends a nightly vacuum. I read this also a bit
# > late. This is equivalent of rebuild database. While this is in
# > progress all other clients wait for vacuum release locks. This is
# > really a handicap for a 24x7 app.
#
# rebuild?  first off, vacuum is a 'cleanup tool'...it goes through and
# removes unwanted/junk/deleted records from the database...if you do
# alot of update/delete operations, then, yes, do a vacuum reasonably
# often.  nightly though?  depends on how much your data changes.  a
# vacuum gives you nothing really on a database that is purely insert
# data.

    Untrue, vacuum is *extremely* important for updating statistics.
If you have a lot of data in a table, and you have never vacuumed, you
might as well not have any indices.  It'd be nice if you could seperate
the stat update from the storage reclaim.  Actually, it'd be nice if you
could reuse storage, so that an actual vacuum wouldn't be necessary unless
you just wanted to free up disk space you might end up using again anyway.

    The vacuum also doesn't seem to be very efficient.  In one of my
databases, a vacuum could take in excess of 24 hours, while I've written a
small SQL script that does a select rename and a insert into select from
that will do the same job in about ten minutes.  This is a database that
cannot lock for more than a few minutes.

# actually, there has been talk about, and work started on, getting rid
# of the 'locking' issue...with the MVCC code, the lock *shouldn't* be
# required anymore, but Tom Lane(?) did some preliminary work on
# removing this for v7.0 and ran into a few roadblocks, especially where
# there was the risk of simultaneous vacuum's happening...wasn't a
# pretty sight, if memory serves...

    Couldn't you just keep the pg_vlock and only have it honored by
vacuum?  That would be neat if other stuff could happen during a vacuum.

# I love these two works "mission critical"...I run several applications
# that clients consider to be mission critical, the least of which are
# two accounting systems for dial-up ISPs...about the only time their
# databases go down is when we upgrade or have to reboot the physical
# machine...

    Well, he's right that it makes a difference how the app is
written, though.  I've got a mission critical app that does web
monitoring, hitting a bunch of web pages and recording everything it does.
When it fails a regex match on a web page, it also records the entire web
page so you can examine it later.  I move a lot of data through that, and
it's fine.

    Another app I've got is an event processing system that can
regularly cause postgres to stop functioning when it starts getting a lot
of events coming through.

    The only real difference between the two is that one is written in
Java and uses database pooling, and the other is written in perl and makes
a database connection each time it has a batch of inserts.  I've not seen
the actual behavior when the event processing system breaks the database,
but it's usually followed by a ton of inserts trying to make their way in.

--
dustin sallings                            The world is watching America,
http://2852210114/~dustin/                 and America is watching TV.