Hello,
We've been dealing with this issue quite recently (even with some
assistance from Dan in #dbmail{,-dev} channels). The below
recommendations are right on par with what I've found for pgsql
maintenance (ie. sufficiently high fsm settings, and frequent
vacuuming), but there also seems to be a bug in postgres that
continues to grow the disk usage without bounds (well, the to the
limits of your hardware :). It supposedly may be fixed in 7.4,
but we haven't gotten to upgrade yet to test that. We're running
7.3.2, and apparently it's been an issue that's been around quite
a while.
We only have about 5.5-6GB of actual data in our dbmail database,
but usage keeps growing over time, eventually filling the 18GB
available disk space. We ended up running periodic vacuum fulls
from cronjobs (twice a month lately), and that does drop the used
disk space down some, but it does continue to grow. As of this
weekend, after running a vacuum full, still had > 16GB taken up for
postgres. Last night we did a dump of all databases, dropped and
recreated them, and are now inserting back in... not done yet, so
I can't give the total end result, but the dump file itsself is
only 4.8GB.
Jesse
---- Original Message ----
From: Sumbry][ <dbmail-dev@dbmail.org>
To: DBMAIL Developers Mailinglist <dbmail-dev@dbmail.org>
Subject: Re: [Dbmail-dev] Re: Postgres vs MySQL performance?
Sent: Tue, 03 Aug 2004 14:07:41 -0700
> Dan Weber wrote:
>
> > There are some major problems with pgsql at the moment. I couldn't
tell
> > you if it was the dbmail driver, or pgsql itself, but I know that it
> > keeps eating disk space like crazy. Sometimes vacuuming it helps, but
> > not really. I think we should have a full review of the dbpgsql driver.
>
> We had this problem initially as well. The reason why diskusage is so
> high has to deal with Postgres' Multi-Version Concurrency Control (MVCC).
>
> Postgres never actually deletes records... when you request that a
> record is to be deleted it is simply marked as deleted, but Postgres
> doesn't actually remove the data from the filesystem or reclaim that
> space. Doing that everytime you deleted a record would be tremendously
> slow as well as cause all kinds of locking problems. Instead, VACUUM
> FULL is what will actually remove the used space (And is also why when
> you run it, the entire table is EXCLUSIVE READ+WRITE locked and the db
> becomes almost useless until it finishes).
>
> Now by default Postgres doesn't VACUUM FULL at all.. even if you use the
> pg_contrib autovacuum module, it's set to just do normal VACUUM ANALYZE
> which may reclaim a little space but not much. If the disk space is
> there and you've got a busy db box, we've found that Postgres will suck
> it all up. A busy DbMail+Postgres mail server is going to probably have
> some pretty aggresive disk usage. The filesystem will seem to fill up
> really quickly, but once it hits around 80-90 or so percent, it'll hover
> there.
>
> An example would be if I have 10 records, and I delete 5 of them, when
> an 11th record gets added it reuses the space of one of the 5 records
> that I had earlier marked as deleted. In most scenarios this is fine,
> but with mail servers we're constantly dealing with thousands of little
> messages (rows) that come in and so this can be a problem w/the default
> Postgres install. So after some digging (and head banging), we found
> the magic postgres tunable:
>
> max_fsm_pages = 3000000 #
>
> That's right, it's set at 3 million. I think it defaults to 40
> thousand. Postgres uses the free space map to track database activity
> and decides when to reclaim space used by deleted rows. A good rule of
> thumb is to estimate how many rows are likely to be updated (in your
> entire db server) between vacuums, and use half of that value for
> max_fsm_pages. Also, max_fsm_relations should be upped to the total
> number of tables that you'll have on your db server. We have ours at
> 1000.
>
> Also - make sure you're doing regular vacuums on the database. Either
> nightly, or even better is to use the pg_autovacuum module in the
> Postgres contrib dir. Looks like it'll be a standard part of the
> install in the next PG release.
>
> -----
> "Any sufficiently advanced bug is indistinguishable
> from a feature." -- Rich Kulawiec
> [EMAIL PROTECTED]
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
-- End Original Message --
--
Jesse Norell
[EMAIL PROTECTED] is not my email address;
change "administrator" to my first name.
--
