Success -- MySQL / MyODBC install

[michael d hoskins]

Yep.  Fault tolerance is a problem in MySQL.

The main solution I see here is to force yourself to do all work through
a custom API which forces some kind of "transaction" system:  have
unique keys, some extra programming code, and a log or a buffer for your
transactions that includes some kind of home brew checkpointing.
Everytime you startup, that log should replay from your checkpoint,
inserting, updating, and deleting records, but not allowing dupes, which
is where your unique keys come into play.  Of course, some data
integrity checks would also have to be built in, and some cold and hot
backups would have to take place.

Whew.  Now you just built an Oracle-like fault tolerant system.  :-)  Of
course, you've just reinvented the wheel.

MySQL still does *some* crash recovery and tools to help with this:
mysqldump --tab=/path/to/some/dir --opt --full  # perform a hot
backup
myisamchk -r  # table recovery
SELECT * INTO OUTFILE 'file_name' FROM tbl_name  # "selective
backups," aka "exports"
mysqld [...] --log-update[=file_name]  # logging

Again, all of this is in Chapter 20 of the HTML manual
http://www.mysql.com/Manual_chapter/manual_Common_problems.html

Now, I won't pretend that if you did all of the above that you'd arrive
at an Oracle clone or anywhere on the board...

I also agree with the idea of abstraction.  The RDBMS should put the
programming interface and administration at a much higher level and do
the work for you, in regards to the above.  That's not MySQL's goal.

MySQL had one main design goal.  They wanted to be as SQL compliant as
possible (eventually) while having as much speed as possible.  They're
very close to that goal...  So, transactions, foreign keys, replication,
etc. are gone.

So, you really are sacrificing data integrity for speed and Open Source.
With Postgres you are sacrificing speed.

I don't believe in having extremely important productional data in
MySQL, just as I don't believe in putting extremely important
productional data on 4mm tapes.  I'd totally suggest MySQL to small and
some medium ecommerce sites, provided steps above are taken.  I'd never
suggest that Sprint long distance use it for productional data, although
internal data would usually be OK, especially as a replacement to
Access.

I will say that due to code quality and operating system issues, that
MySQL nukes Access and FMPro for data integrity, if, for no other
reason, the thing crashes less often.  MySQL also does some extremely
lightweight transactions, to play traffic cop, they do perform some
crash recovery, and things are committed very quickly, instead of
buffering, if I remember correctly, so it's still a better solution than
the "little three."

Access has weird compliancy issues.  I can't remember if FMPro finally
got SQL built in, or not.  Is FMPro finally truly relational, allowing
for many-to-one, one-to many, and many-to-many relationships?  My
experiences with FMPro were severe lack of scalability, constant
recovery, lockups, users locking the database into read-only mode, and a
preference for IPX (or Appletalk) instead of IP....  Access, is well,
Access, and it can really freak out.  Although it's totally
unscientific, I have never seen MySQL behave in this way, nor have I
ever heard of those kinds of issues.  I can't comment much about FoxPro.

Oracle is cool, but you do need to make the inve$tment.

"My fear is that a lot of people really don't understand when to use
MySQL, and when to use PostgreSQL.  But I suppose at least they aren't
using something like Access."  Absolutely.

-----Original Message-----
From: watts [mailto:watts at jayhawks.net]
Sent: Thursday, April 06, 2000 6:10 PM
To: kclug
Cc: watts
Subject: RE: kclug - Success -- MySQL / MyODBC install

On Thu, 6 Apr 2000, michael d hoskins wrote:

> I think MySQL is far advanced in database features, scalability,
> programming languages, stability, speed, fault tolerance, etc. to
> Access, Foxpro, and Filemaker Pro, so I don't think it's directly
> comparable to them.

Correct, except the internal structure (the way it actually works) is
much
closer to those three.  It may be somewhat unfair to put MySQL in the
same
category as those three, but from a design perspective that is a more
accurate comparison.  Coincidentally, this is also the perspective that
most businesses take in evaluating DBMSes.

Fault tolerance is the Achilles' heel here -- why do you think that
MySQL
has inherent advantages?  Did I miss something in my analysis of MySQL?

Without transactions, database recovery is questionable after a crash.
All
three databases face the same problems in this regard.  You can
programmatically work around the potential problems with a system crash,
by making your application save its own state and check itself after a
crash, but that is a severe disadvantage considering the real-world
situation where developers come and go, and managers have to worry about
consistency and reliability of the application given many developers.
Usually it's easier to have the database control reliability, and have
the
developers simply write code (a bit of an over-simplication).

The whole point of a DataBase Management System is to abstract the
implementation and maintenance of a database away from the use.  Coding
around an inherent limitation is a costly endeavour, and whomever
decides
to use a solution like MySQL should think well in advance what they are
doing and whether they feel that their website (or whatever) needs ultra
performance at the cost of stability.

My fear is that a lot of people really don't understand when to use
MySQL,
and when to use PostgreSQL.  But I suppose at least they aren't using
something like Access.

> MySQL is ultra fast for small to many medium-sized databases, and
> usually annihilates just about anything else.  Postgres might actually
> be faster for small to medium databases, compared to Oracle and the
> other "big guys."  For really large databases, the big guys (on
> appropriate hardware and with proper tuning,) will almost certainly
> annhiliate MySQL and Postgres.  And they can certainly scale far
> beyond MySQL and Postgres.

That's a very fair analysis, though I believe that the benchmarks run by
the PostgreSQL people show that Oracle performs very similarly, and in
some situations as much 10% faster on small to medium databases.

So, Oracle may be a good choice if you need both performance and
reliability and money is no problem.  :-)

Jeffrey.

o-----------------------------------o
| Jeffrey Watts                     |
| watts at jayhawks.net
o-------------------------------------------o
| Systems Programmer         | "Proprietary system advocates aren't evil
|
| Network Systems Management |  or stupid.  They are the victims.  They
|
| Sprint Communications      |  have a disease and they need help."
|
o----------------------------|  -- Donald B. Marti Jr.
|

o-------------------------------------------o