Strengths and Weakness of MySQL
The primary question I will try to answer for you in this book is: “Will MySQL solve my needs, and if so, how?” The first step in answering this question is to examine MySQL’s strengths and weaknesses; with a more complete understanding of MySQL’s functionality, you will be able to decide whether MySQL is a good solution for your needs.
- Low system resource requirements
- Platform diversity
- Support for a large number of host languages
- ODBC support
- Free or low-cost licensing
- Inexpensive commercial support
- Strong user community backing
- Availability of the source code Weaknesses:
- Lack of certain SQL features
- Lack of thorough testing on certain platforms
- Difficulty of working with the source code
This list is not comprehensive. I have selected the most common factors in the decision making based on my experiences in working with MySQL users. Let’s now discuss each of the strengths and weaknesses in more detail to help you understand its implications for your particular problem.
Following is a discussion of MySQL’s strengths.
The core MySQL code was written from the ground up with excellent performance as a primary goal. In fact, Monty Widenius, the original creator of MySQL, was frustrated with the relatively slow speed of databases available on the market at the time, so he wrote MySQL. In my role of enterprise support at MySQL AB, I have often had to deal with the concern on behalf of a potential user that MySQL might not be fast enough for what the user needs, or it will not be able to handle the load. The MySQL support team response is always, “Why don’t you write a benchmark that will simulate a part of your application and see it for yourself?” The results of these benchmarks often stun customers and make them converts on the spot.
Many production database systems run a load of 1000 to 2000 queries per second on commodity x86 hardware (dual Pentium 800 with 1-2GB of RAM). I have taken MySQL “for a spin” on several occasions and was able to get 13,000 queries per second on a quad Pentium 700 selecting one record on a key from a table with one million records. So the answer to the question “Will MySQL be fast enough for me?” in most cases is “Yes, and probably much faster than you will ever need it to go.”.
A word of caution: Like any database product, MySQL can be slow in some cases if you are not careful when writing your queries. You can avoid this problem by understanding how the server works. One of the goals of this book is to give you the information you need to write efficient queries and keep MySQL running at top speed.
MySQL has earned a reputation for being able to run unattended for days— even months—after initial setup. Here and there, of course, various issues arise and various bugs are discovered, just like in any other database server, but
overall it is very uncommon for MySQL to go down—and when it does, it is usually able to recover gracefully from the crash. This reputation for reliability got MySQL noticed by a number of enterprise users, who decided it was a great product for their needs. The list includes Yahoo! Finance, Cisco, Texas Instruments, the United States Census Bureau, NASA, Novell, Blue World Communications, Motorola, and many others.
The development team members are extremely focused on making MySQL reliable; they are obsessed (at least by industry standards) with ridding betas of bugs. I have seen MySQL releases postponed in numerous instances just because a single and rather insignificant bug had not yet been resolved. The discovery of one serious bug is reason to build a whole new release and issue a public apology.
Low System Resource Requirements
MySQL is able to make the best of the resources you give it. Of course, the greater the resources, the better the performance you can expect, but minimal resources will not put MySQL out of commission as it does some other database servers. I have successfully run MySQL on a 32MB RAM, 166MHz Pentium system that is not fully dedicated to MySQL. There have been reports of running MySQL under even smaller configurations. The footprint of a MySQL process with a “lowfat diet” configuration is 2MB to 3MB, so it is theoretically possible to run MySQL with as little as 4MB of RAM on the system.
Practical experience shows that MySQL scales well on systems with up to four CPUs, and up to 4GB of RAM, fully taking advantage of the system resources. It is known to work well with tables containing several billion records and has been reported to handle up to 1500 concurrent users without a notable performance degradation.
It is very possible that the true limits of MySQL scalability have not yet been fully explored. At the time of this writing, the MySQL development team has not yet been able to find the time or the hardware to create tests that will do so. They do rely to a great extent on user reports to learn how MySQL performs under load.
If your scalability needs ever exceed the capabilities of a single server, you can use the internal replication capabilities of MySQL to create a cluster of systems and distribute the load by directing the writes to the master host and sending the reads to the slaves.
MySQL runs on a wide variety of architectures and operating systems. Among the most frequently used are Linux, Windows, Solaris, and FreeBSD. MySQL also runs on Irix, HP-UX, AIX, SCO, Tru64, OpenBSD, NetBSD, and Mac OS X.
Support for a Large Number of Host Languages
When you’re developing a database application, one of your primary concerns is the ability to interface with the database server using a particular programming language, which is often referred to as the host language. This is another area of strength for MySQL; programmers can communicate with MySQL using C/C++, PHP, Perl, Java, Python, TCL, Ruby, and Eiffel.
In addition to multiple host language support, MySQL includes an ODBC driver. This gives the programmer the ability to write vendor-independent database applications using the Open Database Connectivity (ODBC) standard.
ODBC connectivity support also allows MySQL to be used with a large number of data management ODBC-capable applications, such as Microsoft Access, Microsoft Excel, Crystal Reports, and many others. ODBC support allows MySQL to be used in Visual Basic and Delphi applications; in ASP (Active Server Pages); as well as with ColdFusion, Borland Builder, and many other development tools and environments.
Free or Low-Cost Licensing
MySQL is distributed under the terms of the General Public License (GPL) created by the Free Software Foundation (FSF). This license allows you to use the software free of charge for both commercial and noncommercial purposes on the condition that any derived product must be distributed with its entire source code under the terms of the same license. More information about GPL, including the full text of the license agreement can be found at www.gnu.org/licenses/gpl.html. For MySQL, the terms of the license mean that in most cases—except when MySQL is included as part of a proprietary product that the vendor is distributing to its customers—MySQL can be used free of charge.
In the case when the license is required, or when the organization policies do not permit the use of a GPL-licensed product, a license can be purchased from MySQL AB at reasonably low cost. At the time of this writing, the price for a single license is $200, but drops dramatically as the number of licenses increases, all the way to $20 per copy for 10,000 or more licenses. The license is issued per server, and does not restrict the number of users.
You can find more information on MySQL licensing at www.mysql.com/sup- port/arrangements.html.
Inexpensive Commercial Support
For those planning to run MySQL in a mission-critical environment, the issue of high-quality commercial support is very important. Even if no problems develop, the CTO needs to know that there is a competent source to turn to if any questions or problems arise. Not having this kind of support available could be a serious concern in making the decision to adopt the use of a product.
MySQL AB provides a wide range of commercial support at a reasonable price, including 24x7 telephone support. The prices range from $1500 per year for entry level to $48,000 per year for deluxe. The core developers participate in handling support requests—this means you don’t have to jump through several hoops before you start talking with the person who wrote the code that has something to do with your issue.
Strong User Community Backing
As mentioned earlier, MySQL is more than just a database. MySQL’s founders have always focused on giving the community more than they take from it. The community has responded with loyalty, hard work, and camaraderie.
How does this strong user community affect a potential enterprise user? The most obvious effect is that it is possible to get free support from the community in addition to the support provided by MySQL. If you post to a newsgroup or a mailing list, or get on IRC and ask a question, it is likely that you will get an answer. Unlike with commercial support, the answer is not guaranteed by MySQL AB, of course, but there are many experts on these lists who give excellent advice. Consider this support option as going fishing—if you know how to fish, you can get free fish from a lake. If you do not know, or do not feel like driving out to the lake, you can simply go to the store and buy some.
Another aspect of a strong user community is that it is relatively easy to find a dedicated MySQL expert who will work for you. What does this mean for an enterprise manager? In addition to the natural objective strengths of MySQL, you will get what one might call “the self-fulfilling prophecy effect.” We usually think of self-fulfilling prophecies as something negative, but in this case it is a positive force and is exactly what an enterprise manager would want. The developer who knows the capabilities of MySQL and likes to work with it predicts that MySQL will do the job and will then go to work, and for the exact same amount of pay, will put forth the kind of effort it takes to make it happen. You rarely find this kind of enthusiasm and commitment to other database products.
One other aspect of community support worth mentioning here is the “community insurance.” A typical concern about the products of small software companies is the future of the product in case the company itself fails. Even if MySQL AB stopped selling support tomorrow and went back to being solely a group of dedicated developers, the community of users, the source code, and the open source process that built MySQL would still be alive and well.
Availability of the Source Code
Access to MySQL source code is an important advantage for businesses that employ experienced C/C++ programmers. It provides an opportunity for various customizations, improvements, extensions, and bug fixes to be done without having to wait for the vendor to do it.
Another advantage of having the source code available is increased peer scrutiny, which tends to lead to higher code quality. The driving factor in this process is the MySQL developer’s sense of professional honor and reputation. When the source is going to be seen only by few coworkers, there is a temptation to start cutting corners—for example, failing to check for rather uncommon error conditions, avoiding security issues, or writing inefficient code in some places, hoping that the customer hardware is fast enough anyway. However, if the source is going to be seen by a large number of competent programmers across the globe, the attitude is totally different.
Following is a discussion of MySQL’s known weaknesses.
Lack of Certain SQL Features
The most serious weakness of MySQL is that it currently does not support subqueries, views, stored procedures, triggers, and foreign-key enforcement. This presents a number of issues, perhaps the most important of which is porting existing applications to MySQL. If your database application contains any of the features not supported by MySQL, you will need to rewrite those portions before porting it to MySQL. In some cases, this can be a daunting task.
There exists a strong school of thought in the IT world that some of the features not yet supported by MySQL are an absolute must. Many programmers have learned to depend on those features, and it has become an essential part of their
programming repertoire. It is little surprise, therefore, that this group is somewhat wary of using MySQL, and in some cases might argue quite strongly against it. Their reaction is perhaps somewhat similar to that of a person who has used automatic transmission his entire life and who is now asked to learn to drive a stick-shift.
Avid MySQL users, on the other hand, have learned to live without those features and even enjoy the challenge of having to get around them. Pushed by the challenge, they manage to find elegant solutions that they would have otherwise missed. They learn to emphasize MySQL strengths in their code and work around the weakness when necessary, and in their hands MySQL is able to perform just about any job they set out to accomplish. They tend to argue strongly for the use of MySQL. Using the car metaphor again, their skill allows them to shift gears to get the car to top speed, which they could not have done using automatic transmission.
Because of this difference of opinion, you will frequently encounter a division among computer professionals on whether MySQL would be an appropriate choice for a particular application. If you are a decision maker who has to trust the opinion of your experts and they disagree, then you are in a difficult situation. Can MySQL really meet your needs?
The reality is that in most cases (with the exception of porting some existing applications) MySQL will meet your needs for building database applications, given a good combination of skill, creativity, and motivation on the part of your developers. The problem is that if your database programmers are strongly affiliated with the “true RDBMS religion,” they would be reluctant to use MySQL. Because of this reluctance, they will not put forth as much effort in providing a MySQL-based solution as they would have if you chose another database that they find more suitable; as a result, it will cost you more. If this is the case in your organization, forcing people to use MySQL before you get them to like it would not be a good idea from the pure economic standpoint. However, if you have some folks who are excited about working with MySQL, they would quickly find a way to make things work without the “absolutely necessary” features of the enterprise-level RDBMS.
It should also be mentioned that although MySQL lacks the above-mentioned features, they are currently in development. The goal of MySQL AB for the next two years is to implement all of the missing features and become fully SQL- compliant.
Lack of Thorough Testing on Some Platforms
To understand this weakness, you need to first understand a quality assurance phenomenon that is probably unique to MySQL. MySQL AB has strict coding standards and a set of thorough testing procedures, but this level of quality assurance can go only so far. The next stage of testing happens when a new version is downloaded from the Internet at the rate of about 20,000 a day, gets installed on a very large number of systems, and is exposed to various combinations and sequences of queries on a rich variety of systems. This process will expose various bugs and system issues that could not have possibly been discovered even in the most rigorous in-house testing. Bugs are reported, and usually quickly fixed. MySQL AB depends on the field testing done by the users for quality assurance.
As a result of this process, it is apparent that the stability and performance of MySQL on a particular platform will be greatly influenced by the size of the install base. The larger the install base, the less of a chance that a critical bug could hide for long periods of time.
Although MySQL AB puts forth a valiant effort to be as cross-platform as possible and not favor one platform above another, the skew in the install base distribution causes some platforms to be much better tested than others. Although it is difficult to know the precise MySQL usage patterns, based on the download statistics, survey results, and a feel of commercial support and public mailing list traffic, I estimate that about 40 percent of installations are on x86 Linux, 25 percent on Microsoft Windows, 15 percent on FreeBSD, 15 percent on SPARC Solaris, and 5 percent on all other platforms.
Due to the quality assurance phenomenon, the remaining 5 percent will always be in the role of poor cousin compared to the dominant platforms, unless they manage to get a larger share of the market in terms of the number of units sold. This is not to say that MySQL is somehow broken on those platforms. It runs very well, and quite a number of people report success in using them, which is due in a great measure to the high focus on portability issues in the code: well- written code has a higher chance of running problem-free on a not-so- well-tested platform. However, a smaller install base will translate into a higher chance of running into a surprise, and you must be aware of that when choosing the platform to run MySQL on.
This situation will change in the future as MySQL AB continues to grow and is able to obtain resources to perform more in-depth internal testing of the platforms that are not extremely popular in the community, such as AIX and HP- UX. This disadvantage may very well be gone in a year or so from the time of this writing.
Difficulty of Working with Server Source Code
We have already discussed the value of having MySQL source code available. Having the source code in your hands definitely gives you a lot of flexibility. The caveat of working with somebody else’s source is that you have to understand it in order for it to be of any practical value. The MySQL server (not client) source is fairly difficult to get into, even for a skilled and experienced C/C++ programmer. Relatively few people dare.
I think there are two major reasons for this difficulty. First, it is a database server, which means it has to have code to efficiently organize data on the disk, cache it in memory to minimize disk access, parse queries, and select a strategy for resolving a particular query. This kind of code by its very nature would be complex, regardless of how clearly it is written and how well it is documented.
The second reason is what one could describe as the “genius code effect.” The core code has been written by Monty Widenius, who in my opinion deserves to be called “the Mozart of computer programming.” A programmer with a sense of taste will have an experience similar to listening to a beautiful piece of music while studying Monty’s code. While this beauty is nice and wonderful, the challenge is that even when that beauty is documented, it requires the kind of inspiration that Monty had when he wrote it to understand it and to be able to add on to it without breaking the server. Experience shows that even the best programmers will find this challenging.
The challenge, though, is manageable. MySQL development team members somehow managed to master it, as well as several users who have contributed various patches to the code.