At 04:23 PM 2/12/2002 +0100, Roel Rozendaal - IC&S wrote:
* Speed. Postgresql is way slower than mysql. Way.
Given that you staunchly refused to do a vacuum until the DB grew to 5GB,
that's hardly surprising.
Most benchmarks show that MySQL is substantially superior for one or a few
clients, but that for high concurrency/high load PG wins. This of course
assumes that both databases were configured and tuned properly...
And for anyone who wants to start a 'MyDB is better than YourDB'
discussion, please take it elsewhere. The choice is a complex one driven by
a diversity of business needs; in this case mySQL may be better for IC&S -
they certainly seem to have more local expertise in mySQL, for example.
* Dump/Restore if you create a dump using pg_dump, pg_restore will not
accept it - it complains about NULL values for fields that are constrained
to be 'NOT NULL'. Could be, but the fields have never been NULL in the
first place as the field is constrained to be 'NOT NULL'. It is in fact
very annoying to find out the dump/restore utilities do not work when
trying to recover a crashed system..
You should report this as a bug; I have never seen this. As one of the
people who regularly works on pg_dump/restore, I would be very interested
in getting a sample dump file.
* Stablility Postgresql gets _real_ bad when hardware
failures occur. It cannot recover it's own stuff, claiming that it has
recovered when in fact it has not.
Did you report these? I have had hardware failure with PG, and only once
had to revert to backups. In the worst cases the mailing lists have been
extremely helpful. I think the ability to recover depends a great deal on
what *was* written to disk before it died. As you suggest, it is unfair to
compare without having a hardware failure on mySQL.
Some very strange output from the backend follows:
Did you report this? Recovering from a hardware failure may be impossible.
Do you know what was trashed?
* Vacuum The system turns completely irresponsive for 10-20
minutes when doing a vacuum analyze on the messageblks table - and that is
on a not-so-gigantic database (ca. 5 GB). Could be done by night, but what
if your mailserver has to be responsive 24/7?
Not surprising if you run it for the first time when the database has grown
to 5GB. You would be well advised to dump/restore the db, then run vacuum
regularly. Or take it offline and run a VACUUM FULL, then vacuum regularly.
Based on my own experience with DBMail, you probably need to change some
other parameters to get the best performance - it depends on how much data
is being updated/deleted.
* Disk Usage It seems that pgsql is eating up all disk space in time -
and no vacuum full/analyze can help this. I still have to try a 'REINDEX'
but - as it is marked on postgresql.org - that is intended for corrupted
indices. The indices aren't corrupt, the database is just too big!
Again, this is almost certainly tuning parameters. I had the same problem
(growth of about 140MB per day), until I fixed some settings.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
