Hi all,
I am not sure what the current usage percentages are of
mysql/postgresql but we (IC&S) have tried both now; this mail is to
share our findings and to give life to a discussion on which database
works best for dbmail.
First we worked with mysql - all went fine but we needed support for
tables containing > 2GB data (messageblks). So we moved to postgreSQL,
firmly believing that it would be the better option. After a couple of
serious problems on several systems, we decided last thursday to move
back to mysql, now using InnoDB as a backend.
Short list of postgresql problems:
* Speed. Postgresql is way slower than mysql. Way.
* 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..
* 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. We encountered two different problems:
(1) disk full when running a vacuum full --> completely cracked up
postgresql.
(2) disk failure --> the system was so broken that I could execute but
1 query on the messageblks table per connect/disconnect to the pgsql
server. Vacuum analyze did not work; nor did REINDEX issued from the
backend. Some very strange output from the backend follows:
POSTGRES backend interactive interface
$Revision: 1.245.2.3 $ $Date: 2002/09/30 20:47:22 $
backend> reindex index messageblks_pkey;
NOTICE: RelationBuildDesc: can't open messageblks_pkey: No such file
or directory
ERROR: _mdfd_getrelnfd: cannot open relation messageblks_pkey: No such
file or directory
backend> create index messageblks_pkey on messageblks(messageblk_idnr);
ERROR: index named "messageblks_pkey" already exists
backend> drop index messageblks_pkey;
ERROR: cannot open messageblks_pkey: No such file or directory
backend>
I have to admit that we have never had hardware failure with mysql but
i certainly dislike this. But when using innodb, you just get the
message 'disk full' instead of a cracked up database when your disk
runs full.
* 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?
* 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!
Seems that mysql is the better option for dbmail - what's your opinion?
regards roel
