[Discuss] Rob Conery's critique of MySQL?

[markw at mohawksoft.com (Mark Woodward)]

On 07/31/2012 02:03 PM, Derek Atkins wrote:
> Mark Woodward <markw at mohawksoft.com> writes:
>
>> On 07/30/2012 05:28 PM, Derek Atkins wrote:
>>> Sure, and there's a lot to be said for using tools with which you
>>> are comfortable. Like everything, it's a tool. The key is using the
>>> right tool for the job. Just because you need an RDBMS does NOT
>>> imply that PG is *the* right tool. It is *a* right tool. There are
>>> other choices, and those other choices *are* valid. It all depends
>>> on the requirements. Without knowing the requirements all other
>>> discussion is purely rhetorical or religious, neither of which
>>> belong on a technical list.
>> As a start, off the top of my head, I can describe one MySQL problem
>> that absolutely eliminates it from consideration for a production
>> database.
>>
>> Suppose you have the "street map" database of the USA or some other
>> very very large table, millions of rows. In production, your query
>> performance is poor. You do some analysis and work out an index that
>> betters your query performance substantially. You want to deploy that
>> new index WITHOUT bringing down the site. 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. When they are done, presto! your
>> query is faster. Neat, huh?
>>
>> That is just one problem that I consider a show stopper. You should
>> watch the first 15 minutes of the video that started this message
>> chain. In fact, I would wager, if you watched the whole thing, you'd
>> never consider MySQL again.
> It's a show stopper if you have an application that needs that large a
> piece of data.  However if you only need a half-dozen tables with a few
> hundred or maybe a few thousand lines, then this isn't an issue.
You are sort of missing the point. It locks tables. That's bad. The 
video that started this chain had a VERY good explanation of why this is 
bad. Much better than I can do in text.
> Sure, PG is "technically" better in that it doesn't have this drawback,
> but in the real-world example of a low-end application you just never
> hit those cases where PG really shows its strengths.
That isn't true AT ALL. The feature that makes this possible is MVCC. It 
helps you in a lot of ways that are not obvious until you get bitten. 
The index locking up is a problem, I've had this issue on every MySQL 
project I've on, but MVCC helps you far beyond that. With PostgreSQL, 
you can control the visibility of concurrent transactions, which means 
two active transactions do not interfere with each other.

>
> -derek
>