[Zope] MySQL - PostgreSQL - ZODB

Monty Taylor mtaylor@goldridge.net
Mon, 08 May 2000 16:19:22 +0200


This is a multi-part message in MIME format.
--------------BD93FEC16A5F6F13D7B7D094
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Possibilities for flaming aside, I must say that I break from the
pervasive attitude of Zope culture and defend MySQL a bit.

MySQL doesn't implement Foreign Keys. It doesn't implement transactions.
For better or for worse, those are the two things, and the only two
things that really count that would cause you not to use it in
production. If these two things are very important to your personal
sense of well-being, then I would emphaticly join the others in saying
DON'T USE MYSQL. It's not the right choice for you. 

However, if you are indifferent to those, or don't know enough about SQL
or RDBMSs to have an opion yet, please consider the following: (I'm
going to contrast against Oracle, due to current personal involvement,
but only as a convenient source of examples) 

1) MySQL is intended for a different audience. In Oracle, you write code
(stored procedures) that are stored in the database. The schemas are
stored in the database. The development environment is in the database.
It is intended as an almost sub-operating system. You can live your
whole development life and never set foot outside the database. MySQL,
on the other hand, is a place for people to put things and refer to them
later in a relational manner. The intended users are programmers or
other power-users. (Much like, other flame-war aside, VI and EMACS) The
same people who like the extended power for the end user at the expense
of ease of use, would like MySQL. 

2) MySQL doens't do things for you. As mentioned earlier, it expects you
to be a responsible programmer. It will let you do what you want. If you
want to define a column with a logical foreign key to a look-up table,
and then insert into that column an un-defined value, MySQL won't stop
you. (It won't even know) Oracle will throw up an exception and say
Foreign Key Constraint violated. People says this makes things safer,
because it forces the data to be ok They also say that MySQL would be a
pain because you have to write code to make sure you are inserting valid
data each time. Well, you have to do the same in Oracle on the other end
of the stick, because you have to write code to handle the exception the
database will throw if you are careless. The difference here is clean
code rather than a shotgun and a shovel to handle the exceptions.
Nothing is actually gained in data integrity by this, because you also
have to define the constraints, (which would be part of the program
design) and the constraint can just as easily be coded elsewhere.

3) MySQL is smaller. MySQL is faster. Period. If speed or size are a
determing factor, MySQL is for you. Because MySQL treats itself as a
relational storage medium, rather than as a mom for your intentions
towards your data, it can be optimized for speed and size in ways that
aren't possible for Oracle. Our Oracle product directory contains 438M
of stuff. That's not even data.

4) The biggest complait I've heard from the Zope World is that MySQL
doesn't support transactions, and since Zope is highly transactional,
there is an inherent incompatability. This is the one that irks me the
most. Like the foreign key checking from earlier, this just doens't buy
you what you may think it buys you. Zope's undo funciton is wonderful
and quite useful. But the transactions in an RDBMS don't get you that.
You can't un-commit comitted transactions, and as far as the web-world
goes, if you are going between screens with open transactions, I really
feel for where the state of your data is going to be. Once you've
inserted and commited in the method that you posted to, there's a Zope
undo just waiting for you, and you are NOT going to be able to undo
anything in the database. You can shut down the server, trim off some
transactions from the transaction log, and run things back in, but
that's not a real time activity. (You can do that in MySQL, too, I might
add) Transactions in the database world are most useful in stored
procedures to prevent the effects of a program inserting its data
half-way and then dying, leaving corrupt data. As MySQL has no stored
procedures, this is almost a non-issue, and again something that program
design can completely do without. Since MySQL is small, fast, and I
forgot to metion, stable, stuff crapping out mid-transaction is almost
never a problem as far as MySQL itself is concerned. 

5) I must add a fifth (sorry, I actually didn't mean for this to get so
long) To quote dear Mr. Greenspun:
"""
Isolation 
   The results of a transaction are invisible to other transactions
until the transaction is complete. For example, suppose you have a page
to show new users and their photographs. This page is coded in reliance
on the publisher's directive that there will be a mugshot for every user
and will present a broken image if there is not. Jane Newuser is
registering at your site at the same time that Bill Olduser is viewing
the new user page. The script processing Jane's registration does
inserts into several tables: users, mugshots, users_demographics. This
may take some time if Jane's mugshot is large. If Bill's query starts
before Jane's transaction commits, Bill won't see Jane at all on his
new-users page, even if Jane's insertion into some of the tables is
complete.
"""
Case in point for good program design. The program uploads the mugshot
file into memory before inserting it (which should then go quite quickly
if the program runs on a machine in the same LAN) Insert the mug-shot,
then the demographics, then the user key info in the users table,
causing the display of users still not to be bothered with the on going
insert until the key record is there (This would not be possible in the
world of Foreign Keys, mind you, where you would need the user row there
before you could insert the slower data -- the 'deficiencies' work quite
nicely together, no?)

I guess the main thing I'm trying to say is, it isn't better or worse,
necessarily. Nor is it necessarily less stable. Either way can cause
vast amount of crying and gnashing of teeth if you go into it with the
wrong mindset. If you try to program Python like it's C or Perl like
it's Python, you'll probably be fairly disappointed. However, if you
realize that, when viewed under the light in which each was intended, it
all comes down to a matter of style. I mean, Lisp is powerful as hell,
but stylisticly clashes with every bone in my body.

But I'm still not going to claim that it isn't stable or that you
shouldn't run production data on it, because then I'd be joining in the
attitudes of all the MS people who think that running an open-source
scripting-language based system is silly. And I think we can all agree
on the answer to that.

Monty

(Sorry for the ridiculous size)


Gregor Hoffleit wrote:
> 
> On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
> > I think the low down is that while MySQL is very fast it is not as scalable
> > or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since
> > I would never run a production website with that product.
> 
> I guess this has the potential for a mid-sized flamewar, but also have a
> look at Ben Adida's recent article "Why Not MySQL ?"
> (http://openacs.org/why-not-mysql.html) and the ongoing discussion on
> Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml).
> 
>     Gregor
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
 
Gregor Hoffleit wrote:
> 
> On Mon, May 08, 2000 at 08:43:45AM -0400, J. Atwood wrote:
> > I think the low down is that while MySQL is very fast it is not as scalable
> > or as true a RDBMS as PostGreSQL. MS SQL 7.0 I will not comment about since
> > I would never run a production website with that product.
> 
> I guess this has the potential for a mid-sized flamewar, but also have a
> look at Ben Adida's recent article "Why Not MySQL ?"
> (http://openacs.org/why-not-mysql.html) and the ongoing discussion on
> Slashdot (http://slashdot.org/articles/00/05/05/0920247.shtml).
> 
>     Gregor
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
--------------BD93FEC16A5F6F13D7B7D094
Content-Type: text/x-vcard; charset=us-ascii;
 name="mtaylor.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Monty Taylor
Content-Disposition: attachment;
 filename="mtaylor.vcf"

begin:vcard 
n:Taylor;Monty
tel;cell:+31 (0)6 200 17486
tel;fax:+31 (0)20 589 5566
tel;work:+31 (0)20 589 5517
x-mozilla-html:TRUE
url:http://www.goldridge.net
org:Information Innovation
adr:;;Amstelveenseweg 88-90;Amsterdam;;1075 XJ;The Netherlands
version:2.1
email;internet:mtaylor@goldridge.net
title:Information Artist
x-mozilla-cpt:;0
fn:Monty Taylor
end:vcard

--------------BD93FEC16A5F6F13D7B7D094--