[Discuss] Rob Conery's critique of MySQL?

[markw at mohawksoft.com (Mark Woodward)]

On 07/31/2012 01:34 PM, Rich Braun wrote:
> Mark Woodward <markw at mohawksoft.com> wrote:
>> Well, with MySQL, "create
>> index" and "drop index" LOCK the tables as they are operating. LOCK THE
>> TABLES. Think about that. In PostgreSQL, Oracle, and any "real"
>> database, "create index" and "drop index" only impact performance in as
>> much as any other transaction.
> True of older versions, less true of 5.5.  NDBCLUSTER storage engine works
> around this by propagating the update to cluster members one at a time, taking
> each offline.  InnoDB does a table copy.
Provided you have a cluster of course, but it still raises the issue of 
effectively removing a machine from the cluster.
>
> You're right that MySQL's rivals had a better design for this operation;
> future versions of MySQL could replace this logic.  But as far as it being a
> showstopper for production, that depends.
It hardly depends. It is indicative of a bad design. For any active 
site, this kind of behavior problematic, you have to be able to admit that.

Here is where I have a problem with these types technical debates. This 
is not a subjective point. With another "real" database this is not a 
problem. Side by side, PostgreSQL and MySQL are both free and comparable 
in many ways. This is absolutely one clear reason to choose PostgreSQL 
over MySQL. Is it a "show stopper" for you, perhaps not, but I tell you 
this one little gem has caused me a great deal of actual grief.

If there are design deficiencies in a tool and a better designed tool, 
at the same price, is available why would you not choose the better 
designed tool? It make no sense. I am not "pro PostgreSQL" so much as 
"anti bad databases." It is only the fact that PostgreSQL embodies so 
much of what a good database does that it is a defacto example. I would 
use Oracle in this discussion, maybe even DB2, but those are not open 
source and very expensive, the obvious counter argument would be that 
"MySQL is free."


>
> In my case, I'll be having to deal with large table sizes, but there will
> rarely be changes to indices and the nature of the business permits taking the
> DB offline for maintenance (unlike a public site).  So this is only one of
> many criteria for choosing a system.  (Note also that even with a public site
> like at my last employer, we had some solid workarounds using read-only slaves
> which enabled us to update indices easily enough without major production
> impact.)
OK, if you are going to have large tables, forget MySQL. A number of 
years ago I contracted at Yahoo. I wrote a data collection system that 
would query snmp info from every system they had, as well as dmesg and 
other performance and configuration information. The idea was that they 
could analyze infrastructure to calculate the proper compute 
requirements for each business unit. The premise was that eight 5 year 
old computers could be replaced with two or three new ones and save 
space and power usage, or that a business unit with 6 servers, even at 
peak, only needed 4, and so on. On the first run, we isolated enough 
"dead-wood" that the estimated savings would over a million dollars in 
power.


At the start of the project, I said MySQL would not work and that I 
would need either Oracle or PostgreSQL. I offered that I could go with 
PostgreSQL while they were working on the Oracle license. As I was 
transferring the project to their IT, a "new" IT director came on. 
Dictated MySQL, his words were "We have invested a lot of money into 
MySQL and we have MySQL experts here, it can work on MySQL." Sigh, 
people who don't understand databases should not choose databases. This 
edict had nothing to do with any understanding of what we were doing.

Well, working with a MySQL contributor and a well regarded yahoo 
internal expert. A query that took a painful minute and a half on 
PostgreSQL could not be made any faster than 20 minutes on MySQL. To 
compound the problem, the indexing issue made life painful. To try a new 
index, we would have to turn off the system and let it re-index before 
we could do ANYTHING with it. It was a terrible experience.
>
> This kind of technical comparison is exactly what I'm looking for.  If I had a
> list of the top-10 things that PostgreSQL does better than MySQL then I'd
> probably have a case.  One or two won't be enough.
Well, I strongly suggest watching the video. The stuff about MySQL is at 
the beginning so you don't have to watch it all. Also, seriously, start 
with PostgreSQL and post any questions on BLU. I bet you could get some 
great advice. I can only say that if you are used to MySQL, you will 
find yourself initially saying PostgreSQL does that?!?! Really?! The 
light will shine and you'll never go back.
>
> -rich
>
>
> _______________________________________________
> Discuss mailing list
> Discuss at blu.org
> http://lists.blu.org/mailman/listinfo/discuss