shells and bells

[niall at kst.com (Niall Kavanagh)]

NOTE: I'm typing this from a wireless keyboard that's about 10"x4", so
forgive glaring typos. ;)

On Wed, 3 May 2000, Mike Bilow wrote:

> On-line backup means that you do not have to stop the database server and
> deny access to it for the duration of a backup using standard tools such
> as tar.  All production SQL servers -- PostgreSQL, Oracle, DB2, and so on
> -- must be able to dump a snapshot of the database to disk and have that
> serve as a conherent backup.  If the database does not support this,
> simply backing up its files using something like tar is not guaranteed to
> give you anything that can be restored into a coherent database.  Further,
> no database which lacks transaction support could ever do on-line backup.
>

Hmm (btw, thanks for the detailed explanation!), in mysql-land I would
have to read lock the tables before doing this.

This, as I see it, it the major advantedge to transactions. 
 
> 
> Transaction support is absolutely essential.  Without it, things like ODBC
> access to the data will not work reliably.  Locking will not work
> reliably.  What you get, in fact, without transaction support is a
> database suitable for reading but not writing.  If this is what you want,
> then more power to you, but this is not what I consider a database server.
>

It certainly is not. There are other methods of backing up a database, and
other ways of ensuring data integrity.

Mysql uses a locking method they call "atomic operations" (I'm not
sure if this is a mysql-ism?) which results in
a database server which is 2-4 times faster than a finely tuned
transactional setup.

Instead of using commit/rollback to ensure integritu=y, mysql relies on
simple checks before updates, and tools that check the database for
inconsistancies. This combined with an update log means you can fix any
problems quite easily.

>From the mysql manual, a snippet you may find interesting:

"Lastly, in situations where integrity is of highest importance,
   MySQL's current features allow for transaction-level or better
   reliability and integrity. If you lock tables with LOCK TABLES, all
   updates will stall until any integrity checks are made. If you only
   obtain a read lock (as opposed to a write lock), then reads and
   inserts are still allowed to happen. The new inserted records will not
   be seen by any of the clients that have a READ lock until they
   relaease their read locks. With INSERT DELAYED you can queue inserts
   into a local queue, until the locks are released, without having to
   have the client wait for the insert to complete."

YMMV. I for one am very pleased with mysql.

> I simply do not believe that MySQL is suitable for any kind of production
> use.  Compared to PostgreSQL, it is a far less complete package, and its
> licensing limitations are often its fatal flaw.
>

I won't argue that point ;). The license is indeed far away from free.
Again, it suits my needs and the needs of MANY others. We're not all
clueless, we're aware of transactions, and we still pick mysql.
 
> Fair enough, but we have extensive experience with database serving on
> Linux, and I cannot caution you enough when I say that MySQL is not what
> most people would consider a database server suitable for serious
> use.  PostgreSQL is the only free database server in the class of DB2 or
> Oracle, and it should be understood that this involves quite a lot.
> 

I really don't see anything damning about mysql besides the
license. Just because it uses a different paradigm for integrity doesn't
mean it's inferior. It may not suit your needs, but based on the
arguements you're presenting I don't see why not. Data gets screwed up on
transactional setups too. No system is perfect. My main point is "try 'em
both and decide for yourself based on the facts." Lucky for the original
poster we've got a lot of knowledgable folks in this group with a lot of
facts. ;)

Database servers are like sexual positions; having someone explain them to
you is no fun. You have to try 'em all out yourself and find out which
one(s) work(s) for you. Some people like fast, some like traditional, and
some are best left undiscussed (MS SQL SERVER 6.5 BABE!).

BTW, this is the best conversation I've had in a while. I *do* hope you
reply, I'm thoroughly enjoying myself! Who knows, I might even give
postgreSQL another whirl! Derek has been harping it at work.

--
Niall Kavanagh, niall at kst.com
News, articles, and resources for web professionals and developers:
http://www.kst.com

-
Subcription/unsubscription/info requests: send e-mail with
"subscribe", "unsubscribe", or "info" on the first line of the
message body to discuss-request at blu.org (Subject line is ignored).