List:General Discussion« Previous MessageNext Message »
From:Frederik Lindberg Date:October 29 1999 5:22pm
Subject:Re: mysql is a pain in the ass
View as plain text  
On Thu, Oct 28, 1999 at 11:24:00PM -1000, Mike Schwartz wrote:

> While it is not my desire to argue with you, it is my hope that I will learn
> a few things and maybe you will too :)

Same here, just a few points:
 
> I have just a few complaints about how MySQL supports the SQL standard.  Most 
> of these complaints are addressed by your TODO list.  I also feel that you 
> have many extensions to the standard which are of great benefit (REPLACE being 
> one of them).  The downside is if you use them and then want to move to a 
> different database, you may have to find every REPLACE and do a 
> LOCK+DELETE+INSERT+UNLOCK. You may be smart enough to isolate this code in a 
> single subroutine ahead of time so making this change would be simplest.

This is a problem with any non-standard extension. Also, if it is an extension
of Oracle.
 
> My hunch is that people who have access to Oracle don't use MySQL very much :/
> This would limit your complaints.  However, today alone, I have seen a few
> complaints on the list.  Specifically lack of Views, lack of Triggers, lack of
> Snapshots, etc.

The type of user is different. If you have afforded the cost in soft- and
hardware to use Oracle, and are proficient in using it, it is an added cost
to also use MySQL. OTOH - Oracle may be used in your organization, but you
may not have access to it for your purpose (WWW access logging, etc). Clearly,
MySQL does not want to be a global alternative to Oracle.

> My experience is that you don't have to be so clever with Oracle.  I've been
> using MySQL for well over a year, and Oracle for several months.  When I first
> started using Oracle, I was amazed at how little effort I had to do to make the
> database work fast without having to work around performance problems.
> 
> I posted about how Oracle does a Fast Full Index Scan for select clauses with
> "LIKE '%foo%'" which are 3 seconds on a 333MHz Celeron and 6 minutes under
> MySQL on a 400MHz celeron (with more memory, too!).

This is the same as the discussion on LIMIT optimization. In this case, Oracle
had optimized ways to deal with LIKE '%...' and MySQL does not. Seems low
priority in initial development, and I'd expect MySQL will get it at some
stage. When in the life cycle of Oracle was this added?
 
> I am sure I can make MySQL go faster than 6 minutes by creating many tables
> instead of one with proper oracle indexes.

It might be easier to pay for support at the right level and tell the
developers that this type of optimization is your top priority. I have no
doubt at all that MySQL will do it as fast as Oracle, once built in.
 
> Again, you are suggesting workarounds for things that Oracle does very well. 
> If someone uses triggers, they use triggers.  It is a feature of Oracle (and
> other databases).  If they use them, they should know the penalty and pay the
> price.  At least it should be an option. 
> 
> Foreign keys are likewise important if you use them and want to switch to
> MySQL.  You're asking people to make potentially radical changes to working
> systems of code.

IMHO, MySQL is not a replacement for Oracle. Once it is, it will have the same
drawbacks: big footprint (disk, memory, CPU, admin, cost), and slower
performance for really simple things.

> The amount of EFFORT to make this machine using MySQL do 250K pages per DAY is
> huge!  It is doable.  The EFFORT to make this machine using Oracle do 250K
> pages per HOUR was minimal.  (And I claim to be no genius at this stuff :)

This reminds me of both crash-me and the NT - linux comparison. In both
cases, the good developers learned from it and are improving their products.
The key is to analyze the type of case you describe not only from the
application point of view, but also from the RDBMS, i.e. what can be changed
in _MySQL_ to make this faster. The LIKE '%..' optimization is an example
of a potential huge gain. Of course, a MySQL developer would have designed
the database/application to avoid the use of that particular construct.

> Berkely DBM can handle big databases too.  And perform reasonably fast.  MySQL
> is much faster, no doubt.  I don't doubt that MySQL is stable, although your
> own MySQL manual is scary - it talks about alpha, beta, gamma code, stuff that
> doesn't sound too stable :)

MySQL is slower on read, but of course much faster on INSERT/UPDATE. DBM also
AFAIK works with a single key. Again, they are different and for some
applications, DBM is better than Oracle/MySQL.

alpha/beta/gamma is (as you know) due to a slightly different nomenclature in
free software than in commercial stuff. Windows 1.0 was release-quality
commercial software. With free software, Windows 3.11 might have been called
something better than beta. I think it's nice to know in a developing
product which features have been there for a while and thoroughly production
tested by many users, and which are new and might have bugs under odd
circumstances. Again, Oracle is much older and therefore more tested.
 
> One HUGE difference between Oracle and MySQL is that Oracle does a large amount
> of system accounting.  I haven't specifically counted the number of system
> tables that Oracle maintains transparent to the users, but I'd approximate in
> the hundreds.  I'd approximate MySQL does about 5 (I'm counting the mysql
> database tables as 3 of the 5).  Oracle does all this without any noticable
> penalty for speed.

An age and type-of-use question. The amount of info maintained by MySQL has
increased over releases. The ISAM layer does some as well. This is something
that IMHO develops mainly late in a product life cycle. After LIKE '%...' ;-)

> For this, you get a huge community of people using it, lots of free beta
> testing, and lots of e-mail on your mailing lists :)))
> 
> > - Good support (many people have said that our support is much more
> >   better than they were able to get from a much more expensive DB
> >   provider with much more expensive support)
> > 
> 
> I wouldn't argue this one.  Though I will say there are people outside of
> Oracle proper who do similar kind of support as MySQL gets.  This is through
> users posting WWW pages (i.e. "How to Install Oracle 8.0.5 under RedHat 6.0"),
> and to various message boards and newsgroups.
> 
> One of the very simplest tuning tricks for oracle is the Analyze command, which
> does a histogram of a database and the histogram is used by the SQL expression
> optimizer for great performance gains.

All things I'd expect to see in MySQL, just not yet.

> I know several places where both are used.  People who are really good at
> Oracle think of MySQL as a toy and they say it is far slower.  I cannot say
> that this is true.  Maybe Oracle guys are snobs.  I am not so picky nor
> religous about such things.

I'm used to MySQL. Did some PostgreSQL. Terribly slow. A postgres developer
made it much faster in a few min (still slower than MySQL). Not fair to
ask an Oracle guy about the comparison. MySQL is a toy in comparison: much
less expensive, smaller, easier, nimbler.

> Let me ask you a pointed question.  How does MySQL do with a database with
> 80,000 tables?  I know from experience ;-)))  It's awful.  MySQL makes 160,000
> files (at least) in one subdir and unix doesn't like that!  I'd point out that
> with TABLESPACE, you can put 80,000 tables in one or two files...

This is due to the roots of MySQL. Again, as the LIKE '%...' optimization.
This is very easy to fix by a simple directory hash structure. For instance
with a 256-way split you'd have directories with an average of 312 files, easy
for any OS. I'd expect to see that soon, if several _paying_ users need
databases with >> a few hundred tables.

> This is another area where Oracle seems to be far better than MySQL.  Oracle
> has excellent mechanisms for backing up a live database.  The MySQL list has
> contained a number of "maybe you can try this" kinds of emails to recommend
> ways to do a live backup on a live system doing lots of update transactions.

This is clearly needed, since it is part of "robustness" also for small
systems. With the current level of MySQL maturity, I'd exepect to see this
relatively soon.

-Sincerely, Fred

Fred Lindberg, Inf. Dis., WashU, St. Louis, MO, USA
Thread
mysql is a pain in the assMark O. Davis26 Oct
  • Re: mysql is a pain in the assEd Carp26 Oct
  • Re: mysql is a pain in the assLindsay Davies26 Oct
  • Re: mysql is a pain in the assfooler26 Oct
  • Re: mysql is a pain in the assBogdan Paduraru26 Oct
  • RE: mysql is a pain in the assDavid Moreno S.26 Oct
  • Re: mysql is a pain in the assJeremy Cole26 Oct
  • Re: mysql is a pain in the assMark Russell26 Oct
  • Re: mysql is a pain in the assMark Russell26 Oct
  • Re: mysql is a pain in the assRuben I Safir26 Oct
  • Re: mysql is a pain in the assRuben I Safir26 Oct
  • Re: mysql is a pain in the assTonu Samuel26 Oct
  • Re: mysql is a pain in the ass(Paul D. Smith)26 Oct
  • Re: mysql is a pain in the assfooler27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
    • Re: mysql is a pain in the assEd Carp27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
    • Re: mysql is a pain in the assEd Carp27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • RE: mysql is a pain in the assOzlinx)27 Oct
        • Re: mysql is a pain in the assEd Carp27 Oct
          • Re: mysql is a pain in the assRuben Safir27 Oct
      • Re: mysql is a pain in the assJay J27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
        • Re: mysql is a pain in the assDan Nelson27 Oct
        • Re: mysql is a pain in the assDan Nelson27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • Re: mysql is a pain in the assJani Tolonen27 Oct
          • Priorities on the TODO list [was Re: mysql is a pain in the ass]Bob Kline27 Oct
            • MySQL and ZendStephen J. Taranto27 Oct
              • Re: MySQL and Zend(Sean McKenna)28 Oct
        • Full commercial products vs. open source [was Re: mysql is a painin the ass]Bob Kline27 Oct
        • RE: mysql is a pain in the assOzlinx)28 Oct
      • Re: mysql is a pain in the assJay J27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • Re: mysql is a pain in the assBob Kline27 Oct
        • Re: mysql is a pain in the assRuben Safir27 Oct
        • Re: mysql is a pain in the assMike Schwartz28 Oct
          • Re: mysql is a pain in the assJani Tolonen28 Oct
            • Re: mysql is a pain in the assMike Schwartz29 Oct
              • Re: mysql is a pain in the assFrederik Lindberg29 Oct
                • Re: mysql is a pain in the assMike Schwartz29 Oct
                  • Re: mysql is a pain in the assFrederik Lindberg29 Oct
                    • Re: mysql is a pain in the assMichael Widenius14 Nov
                      • LIKE '%foo%' (was: mysql is a ...)Frederik Lindberg14 Nov
                  • Re: mysql is a pain in the assMichael Widenius14 Nov
                    • Re: mysql is a pain in the assBob Kline14 Nov
      • Re: mysql is a pain in the assTonu Samuel28 Oct
      • Re: mysql is a pain in the assfooler29 Oct
        • Hardware vs Software and the speedJani Tolonen29 Oct
      • Re: Hardware vs Software and the speedfooler29 Oct
        • Re: Hardware vs Software and the speedJani Tolonen29 Oct
      • Re: mysql is a pain in the asssasha29 Oct
      • Re: Hardware vs Software and the speedfooler30 Oct
        • Re: Hardware vs Software and the speedJani Tolonen1 Nov
      • Re: mysql is a pain in the assDuncan Kinder30 Oct
  • Re: mysql is a pain in the assSabat27 Oct
  • Re: mysql is a pain in the ass(Paul D. Smith)27 Oct
Re: mysql is a pain in the assOzlinx)26 Oct
RE: mysql is a pain in the assJohn Foley26 Oct
  • RE: mysql is a pain in the assBob Kline26 Oct
  • mysql GUI ClientBronco26 Oct
    • Re: mysql GUI Clientjurban26 Oct
Re: Hardware vs Software and the speedfooler30 Oct
  • Re: Hardware vs Software and the speedsinisa30 Oct
Re: mysql is a pain in the assJoseph R. Junkin30 Oct
  • Re: mysql is a pain in the assLarry Rufo30 Oct
    • Re: mysql is a pain in the assBlain Nelson1 Nov
  • Re: mysql is a pain in the assMike Schwartz1 Nov
Re: Hardware vs Software and the speedEd Carp1 Nov
Re: mysql is a pain in the assEd Carp1 Nov
  • Re: mysql is a pain in the assLarry Rufo2 Nov
Re: mysql is a pain in the assDejan Lekic1 Nov
Re: mysql is a pain in the assRaul Miller1 Nov
  • Re: mysql is a pain in the asssinisa2 Nov
    • Re: mysql is a pain in the assMatthias Urlichs2 Nov
    • Re: mysql is a pain in the assRaul Miller2 Nov
      • Re: mysql is a pain in the assPatrick Greenwell2 Nov
        • Re: mysql is a pain in the assRaul Miller2 Nov
          • Re: mysql is a pain in the asssinisa3 Nov
            • Re: mysql is a pain in the assRaul Miller3 Nov
              • Re: mysql is a pain in the asssinisa4 Nov
                • Re: mysql is a pain in the assRaul Miller4 Nov
              • Re: mysql is a pain in the assMichael Widenius14 Nov
                • Re: mysql is a pain in the assRaul Miller14 Nov
      • Re: mysql is a pain in the asssinisa3 Nov
        • Re: mysql is a pain in the assRaul Miller3 Nov
          • Re: mysql is a pain in the assMichael Widenius14 Nov
  • Re: mysql is a pain in the assDave Hodgkinson2 Nov
    • Re: mysql is a pain in the assRaul Miller2 Nov
Re: mysql is a pain in the assEd Carp3 Nov
  • Re: mysql is a pain in the assRaul Miller3 Nov
    • Re: mysql is a pain in the assMike Wexler3 Nov
      • Re: mysql is a pain in the assMatthias Urlichs7 Nov
        • Re: mysql is a pain in the assRaul Miller8 Nov
        • Re: mysql is a pain in the assMichael Widenius28 Nov
      • Re: mysql is a pain in the assMichael Widenius28 Nov
  • MySQL 3.23.7? (was)Re: mysql is a pain in the assVan28 Nov
    • Re: MySQL 3.23.7? (was)Re: mysql is a pain in the asssinisa28 Nov
  • Re: MySQL 3.23.7? (was)Re: mysql is a pain in the assTonu Samuel28 Nov