Re: [Sqlgrey-users] [Semi OT] PostgreSQL vs. MySQL

[Michel B. <mi...@bo...>]

Le Jeudi 30 Juin 2005 10:25, Lionel Bouton a =E9crit :
> Michel Bouissou wrote the following on 30.06.2005 09:08 :
> >Le Mardi 28 Juin 2005 16:05, Lionel Bouton a =E9crit :
> >>>BTW, why state that MySQL is a poor choice ?
> >>
> >>It's often buggy and doesn't follow the standard ?
> >
> >I didn't use MySQL until a few weeks ago, so I couldn't tell for older
> >versions, but MySQL 4.1.12 seems to work perfectly for me.
> >
> >And it's 15 times faster (!!!) than PostgreSQL for the very DB-intensi=
ve
> > DSPAM (this nice crowd of bugs ;-)
>
> DSPAM is a special case. I've had a quick look into its backend drivers=
:
> they use proprietary features of MySQL to speed things up. Their
> PostgreSQL driver is known to be less worked on too.

That's true. But in my case, I had to focus on the actual result ;-)

It also seems that the "proprietary features of MySQL" that allows to spe=
ed=20
things up are missing in PostgreSQL.

> For pure *transactional* (MySQL's MyISAM tables aren't in the same
> competitive field) SQL, MySQL and PostgreSQL are roughly at the same
> performance level although PostgreSQL is often reported to scale better
> under concurrent accesses.

I'm using "InnoDB" tables in MySQL, which are transactional (although I=20
configured MySQL not in "completely atomic" mode, as I didn't need full=20
transactional features, and to speed things up). In this configuration, a=
nd=20
on the same machine, DSPAM still is 15 times faster using MySQL than usin=
g=20
PostgreSQL...

> On the stability front, of all the database admins I've had personnal
> contacts with, only the MySQL ones witnessed crashes (and chronic ones
> at that) and data loss not related to hardware failures.

InnoDB tables are reported to be very safe and robust (and that's why I=20
choosed to use them).

> But for 6 years I've personnaly worked with PostgreSQL in various kinds
> of loads and queries, I've *never* had a crash.

I don't have any doubt that PostgreSQL is very good and stable. I've used=
 it=20
for years as well, and have never lost data, nor have I seen any PostgreS=
QL=20
crash.

Only this speed issue with DSPAM made me shift from Pg to MySQL.

> In the quirkiness department, in SQLgrey, setting aside the now()
> function declaration for SQLite, although I originally developped
> SQLgrey with MySQL, all the special cases are for it, from the top of m=
y
> head:
> - the "I update the timestamp because I think it's better for you" bugf=
ix,
> - the need to tell the client to autoreconnect because the server drops
> the connection without being told to,
> - the heavy SQL involved with timestamp differences.
>
> The first two introduced bugs in SQLgrey, the last one made me spent
> quite some time looking for a working SQL syntax...

Well, I don't know for "the server drops the connection without being tol=
d=20
to", but the fact that 2 different SQL servers behave differently on some=
=20
details isn't surprising, and wouldn't make me say that one is better tha=
n=20
the other, as long as this is documented -- which is the case for timesta=
mp=20
fields for example.


> <OT> Yep. In my case, the lack of performance is one reason I consider
> evaluating other statistical filters. Even with MySQL, DSPAM doesn't
> seem to scale well enough for thousands of users. Too bad, DSPAM comes
> with a nice web interface out of the box. I'll have to do some php/perl
> web hacking and I lack time for this :-(</OT>

The things that mostly pisses me off with DSPAM is the number of bugs it=20
contains, the fact that long reported bugs aren't fixed (as the developpe=
r=20
prefers adding new features rather than fixing known bugs, and quite ofte=
n=20
seems not to want to hear that his software may have bugs...), plus the f=
act=20
that half of the times the fixing of a bug introduces another new bug.

I have more than serious doubts about the quality of this software and it=
s=20
development.

OTOH, it "mostly works" (surprisingly enough ;-) and when it works, it us=
ually=20
works very good.

But for example I've noticed than on my system, since I've upgraded to th=
e=20
latest (supposedly stable) DSPAM 3.4.8, retraining missed spams and false=
=20
positives doesn't work anymore :-((

DSPAM says it has done it and doesn't complain, but the tokens in the DB=20
aren't actually reversed :-((
</OT>


--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E