Subject: Re: MySQL usage?
From: Arthur Corliss (corliss@odinicfoundation.org)
Date: Sat Oct 20 2001 - 16:31:08 AKDT
On Tue, 16 Oct 2001, Christopher E. Brown wrote:
> We are not talking benchmarks or single threads here. We are talking
> about multiple conflicting actions to the same dataset at the same
> time.
Of course, but how a system benchmarks at low connection loads and how it
benchmarks at high connection loads makes a *world* of difference. Again:
this *is* real world speed issues, if you're in a high traffic/connection
environment.
> Oracle and other take the "replicate the data" approach to things,
> where a change to the dataset actually generates a new dataset, other
> threads keed using the old dataset until such time as the update
> thread compleates. No problem when you *need* to do this, but a slow
> waste when you do not.
MySQL does the same thing during some complex *selection* queries. It's a
cheat, but it's effective, and it works.
> Say I have an athlon 1200 w/ 1024M ram and a good scsi array,
> doing 1800 queries/sec for 1300 threads across 200 tables. Everything
> is happy, until all of the sudden I run a sweeping update against a
> table. And of course, for the 10 - 15 seconds than it takes to update
> 40 million records read access blocks to that table... And?
>
>
> Now, Oracle on the same box, same sweeping update on a large table.
> Instead, Oracle slams the IO subsystem trying to generate a
> multiversioned dataset, ends up containing copies of both pre and post
> update data, which then has to be flushed, and generally beats the
> hell outta the machine. No 10 - 15 second stop, but things get *slow*
> for 3 - 5 min. And?
You're omitting a critical difference in this comparison (Oracle's methodology
is deliberately intended to support rock-solid transacation rollbacks):
Oracle strives for complete ACID compliance. The last that I knew, MySQL was
far from achieving that.
In addition to that, you're also getting a higher resistance to data
corruption. MySQL's MyISAM tables are rather easy to corrupt (I don't know
about the newer table formats that it supports).
> If you *must* be able to have non-blocking DB queries then you pay the
> price in overhead and do it, if you dont, you don't.
This goes right back to choosing the right database for the job. If you don't
need all of the extra features of Oracle, then by all means, don't use it.
> I am not talking about benchmarks or any other such bull here, just
> using the right tool for the task. Oracle/PostgreSQL/DB2/etc
> are written with the idea than the ability to handle long running
> updates/etc without blocking constant select access, is a very good
> thing. MySQL is not written this way, and does not have any of the
> overhead involved.
Perhaps I read your previous e-mail wrong, but I took it as promoting MySQL's
operation benchmarks as superior to the true RDBMS's like Oracle and Postgres.
I just wanted to be sure we were on the same sheet of music. Some of the
reports I've read illustrated that MySQL was fast as hell, but you could
exceed a threshold in which it bogged down tremendously. Oracle and others
handle loads above that threshold much better than MySQL, and hence,
outperform it.
> The question is not "Is my truck better than the car?", the
> question is "What kind of load to I have today?"
Which is what I've said all along. . .
--Arthur Corliss
Bolverk's Lair -- http://www.odinicfoundation.org/arthur/
"Live Free or Die, the Only Way to Live" -- NH State Motto
This archive was generated by hypermail 2a23 : Sat Oct 20 2001 - 16:35:49 AKDT