MySQL Discussion

[markw-FJ05HQ0HCKaWd6l5hS35sQ at public.gmane.org (markw-FJ05HQ0HCKaWd6l5hS35sQ at public.gmane.org)]

>
> Now back to Linux...  I've been biting my tongue watching the flamefest
> about
> MySQL.  I happen to be consulting for a company that handles a whole lotta
> webhits with its MySQL servers.  Haven't tried to quantify performance at
> this
> point, though.  Today a coworker had me run a script to post a couple Kb
> to
> each of 570,000 records, it took over 7 hours on a Dell 2950 (16Gb RAM,
> 2.66GHz quad Xeon)--I suspect the script could be more efficient, I doubt
> that
> swapping out the database would make that much difference.  But like most
> of
> the others here flaming on this topic:  I'm not a DB guru.

The hardest part of this database debate is, given the nature of the
people we are, something we probably already know:

How often have we found ourselves in the situation where we have a wealth
of knowledge about an issue or topic, and someone comes along, without
really knowing the basics, and comes up with something they say is "good
enough." Without having any real concept about what "good" and "enough"
mean in the context?

I'm sure we all have our horror stories. One of my recurring issues is
databases. When a person says they don't need ACID, transactions, or
multiversion concurrency, without really understanding what they are in
the first place, it is frustrating.

Except for very limited cases, almost every application that uses
databases NEED transactions and transaction isolation to function
properly. In fact, if your system doesn't support transactions, it is
usually simple luck or lack of concurrency that it works at all.

While not exactly the same thing, there is a similarity between
transactions and "mutexes." How many programs have we fixed because the
original developer didn't think they needed a mutex (or critical section)
around some code and that it was "good enough?"

MySQL,using MyISAM tables, is probably the worst offender of scalability
in the face of concurrency. Not only does it lock tables during an update,
did you also know that it locks tables while it is READING data from it?
Your network latency affects the scalability of your database!

The InnoDB tables are better, but much slower, and any speed you think you
would be getting from MySQL is lost at the expense of trying to perform
correctly.

Try dropping an index from a large table in MySQL. The index are integral
with the data storage. So, you lock up the table (even with InnoDB) and
have to rewrite the table simply for dropping an index. (Create as well!)

One of my last problems with MySQL, besides a very poor query parser and
access planner, is the limited sub-set of SQL and many non-SQL extensions.
For those with little SQL experience, they tend to learn "the wrong way"
to solve a problem in MySQL and typically create very bad general
solutions when multiple database support is needed.

I have had MySQL developers look in wonder as I reduce their queries to
sub-selects and functions, that improve the performance by at least an
order of magnitude. Saying "I didn't know you can do that." My response is
"You can on most real SQL databases." (but can't on MySQL)

Better databases generally (but not always) perform better and provide the
tools you don't even know you need.

I know I come off as a hot head, but pet-peeves are those seemingly little
things that get under our skin, and MySQL is a pet peeve of mine. It's
like a recurring nightmare because it is bad, but to explain why it is
bad, one has to explain why certain algorithms and techniques are good and
needed. The audience says "Lots of people use MySQL, it can't be that bad"
and stops listening, but the fact remains there are lots of reasons why it
is really bad, but they are not reasons easily put in a powerpoint bullet.

Its like trying to explain why Linux is better to a Windows user. "But
everyone use's Windows." Well, not everyone, there are plenty of people
who know better.

For anyone interested, I strongly suggest some research. Knowing about
databases is just as important as knowing about things like compilers,
threading, shared libraries, etc. Once you get a handle on the subject
matter, and play around with an Oracle or PostreSQL, you will NEVER again
look at MySQL the same way.


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.