Whether or not to use MySQL
CIO Magazine has a pretty superficial back-and-forth about whether or not to use MySQL in enterprises. For example, one of the strongest claims in the pro-MySQL article is the not-so-staggering observation (italics theirs)
One way MySQL achieves this scalability is through a popular feature called stored procedures, mini, precompiled routines that reside outside of the application.
And the anti-MySQL article doesn’t have much in the way of crunchiness except for the fairly well-reasoned
Most of the required features for an RDBMS are firmly in place with the release of MySQL 5.0, but we can legitimately consider the maturity of some of these features as a possible reason to shy away from MySQL. For example, the lack of views, triggers and stored procedures has historically been the major criticism of MySQL. These have all been supported by MySQL for a year or so now, but by comparison, they have been features for about 10 years in most competing RDBMSes.
This article pair got Slashdotted, and some interesting byplay ensued. The general theme was along the lines of
“MySQL is terribly deficient out of the box.”
“Yes, but if you use this new, lightly-documented add-in, that specific problem is now solved.”
Comments
2 Responses to “Whether or not to use MySQL”
Leave a Reply
Hi!
CM: For example, one of the strongest claims in the pro-MySQL article is the not-so-staggering observation (italics theirs)
|quoted|One way MySQL achieves this scalability is through a popular feature called /stored procedures/, mini, precompiled routines that reside outside of the application.|/quoted|
Actually, stored procedures do *not* lead to scalability with MySQL in many environments; quite the opposite for a number of reasons. Don’t be misled to thinking that MySQL’s stored procedures are similar to any of the other RDBMS you write about here — they are quite different. Unlike most RDBMS, MySQL caches the stored procedure execution plan *on the connection thread*, not in a global store, and so applications written in languages like PHP (which actually gains much of its scaling ability from being stateless and shared-nothing) get little to no benefit (and even a performance regression) by using stored procedures. If you open a connection to MySQL in a PHP page request, issue a stored procedure request to get back some data, then close that connection, you just wasted a bunch of CPU cycles compiling a procedure plan for no benefit whatsoever since the cache will be destroyed when the connection is closed.
Furthermore, using stored procedures actually goes *against* more modern scalable application design, for the following reason: by putting stored procedures on the database server, the application is being designed to rely more heavily on the database server itself to do the heavy lifting of the application. This is in direct opposition to modern scalability designs of web applications, where scalability is achieved via having distributed application servers performing application functionality with as little (or no) reliance or dependency on a central server or even each other. Meaning: no single point of failure, no “big box” to handle all the lifting.
Stored procedures, IMHO, represent the days of mainframe and behemoth boxes and client-server architectures of the past, when the main point was to isolate the dumb user and clients/terminals and put everything on a central, huge server. This strategy served the purposes of the big database vendors quite well, as it played nicely into their relationships with hardware vendors and their own pricing models.
MySQL doesn’t represent this old way of thinking, and our pricing and internal design don’t play to the big box architecture, but rather the new commodity scale-out architecture of modern systems…
Just my two cents.
Jay Pipes
Just wondering the advantage/disadvantage with simple query like (select a,b,c from d where a = ‘xyz’) on MySql5 using normal select statement and using stored procedure; and using Dotnet/Asp.Net. With the select statement, MySql5 will still have to go thru the parsing/compilation steps, my guess is that it’s not too different with the stored procedure. What I am not too sure is the “extra” thing that needs to be done with stored procedure and whether it is more than enough offset by the no more need to parse/compile on subsequent runs. Did some tests but unconclusive, perhaps due to server settings, etc. Hoping those that know more of MySql5 internals could provide some light.
PS: I too would avoid writing 50 lines stored procedure that defines cursors, transactions, etc.