MySQL Myths Debunked

By Michael Bacarella, March 24th, 2002

Abstract

Open source is about bringing control back to the users. Even if you yourself never look at the code, you have benefited from open development and massive peer review. You are free from the mercy of a single vendor who has its own best interests in mind. Open source succeeds by its merits, not by the any one company's talented marketing department.

If you're a proprietary software vendor, how do you respond? Your lucrative business model is at stake. Your customers no longer have to be miserable. How can you compete? You can try to make a superior product. This effectively means that you have to go open source. Many vendors have done this already. You can't beat a global community of open source developers, so the next best thing is to join them. IBM is one shining example.

If you must keep your systems proprietary, open source is a threat to you. It's impossible to compete on pure technical grounds, so what can you do? Spread Fear. Uncertainty. Doubt. This is so common that it's better known by the acronym "FUD".

I made this document for anti-FUD purposes. I would also like to thank those who have already shared their stories. The response from the MySQL community has been very humbling.

The Myths

MYTH:	MySQL is unsupported
FACT:	Aside from its excellent support mailing list, MySQL is commercially supported by a number of companies, including its developers, MySQL AB. See the support section for a full list.
MYTH:	MySQL can't handle real workloads.
FACT:	MySQL is fast. I wouldn't use it if it was slow. Don't just take my word on it though. Try it for yourself. If you believe in that sort of thing, we even have benchmarks. For example: On our P3-800 w/ 256MB RAM FreeBSD,the peak load that I have seen MySQL handle is in the neighborhood of 1160 queries/sec on a dataset that can fit mostly in RAM. The average load is currently about 40% of the peak load (460 queries/sec). Others have written in with their own stats as well. Pat Wendorf <beholder@unios.dhs.org> writes.. We handle 680 users doing production tracking. During peak load, and have clocked the machine [P3-667 w/ 256MB RAM FreeBSD] (using the same method you do) at well over 500 transactions per second for more than 5 minutes. Jeremy Zawodny <jzawodn@yahoo-inc.com> writes.. Another data point for performance. One of my machines is a FreeBSD box with 2GB of RAM and a 1GHz CPU. I had it up to about 900 queries per second before the WAN bandwidth became the bottleneck. A 2 CPU Linux box (850MHz CPUs) with 1GB of RAM was doing roughly 2.5 times as much work in the same sort of test. Oh, and did I mention that those were almost all UPDATE queries. If you have anything to share, please feel free to write in.
MYTH:	MySQL can't use tables larger than 2GB.
FACT:	This is an OS restriction rather than a MySQL restriction. If MySQL is compiled properly on a system that supports 64-bit file offsets, it can use tables larger than 2GB. Some table types (such as InnoDB) can support it even with the 2GB file size limit.
MYTH:	MySQL is unstable
FACT:	The longest running mysqld process I can find as of this writing has a 77 day uptime. Despite being up for 2.5 months straight, the process hasn't crashed. This is actually pretty normal. I can't remember the last time mysql itself has crashed. See the testimonials section for uptime reports.
MYTH:	MySQL doesn't support transactions or row-level locking. How could anyone take seriously a DBMS that doesn't support these important features?
FACT:	The default MySQL table type, MyISAM, does not support transactions. If the story ended here, then yes, MySQL does not support transactions. Fortunately, MySQL can be extended. At current count, MySQL has support for MyISAM, BDB, and InnoDB table types.* For example: BDB tables support transactions. InnoDB tables support transactions, row-level locking, foreign key constraints, and you can create tables of arbitrary size on systems with 2GB file size limits. This extensibility allows anyone to customize MySQL to meet just their needs. See the support section for more information * I left out some internal MySQL table types, but you get the point
MYTH:	No one uses MySQL
FACT:	MySQL is reportedly used by: Yahoo!, Slashdot, Freshmeat, LinuxToday and others. There's a huge list of users who deploy MySQL for their web sites.
MYTH:	MySQL is only good for web sites
FACT:	Mike C <mike@eagleriver.com> writes.. Our company, GCI, uses MySQL as the hub of several software systems related to being a local telephone service provider. We write a lot of software in Perl that interacts with telecomm devices like Lucent 5ESS & DEX switches, SS7 equipment, satellite modems, etc. over TCP/IP sockets. We use MySQL for centralized logging that all those interface programs can write to.
MYTH:	Sure, MySQL is fast at SELECTs, but it performs horribly on INSERT/UPDATE
FACT:	In the old days, there was more truth to this statement. MySQL databases now smoothly handle high frequencies of data updates. Good database design is still key, but MySQL is no different than other database systems in this respect.
MYTH:	MySQL is insecure
FACT:	Like any other piece of mission critical software, MySQL is only as secure as the administrator is competent. I'm currently not aware of any catastrophic MySQL-related security breaches.
MYTH:	It's hard to find good documentation on MySQL -- just like every other open source project
FACT:	MySQL's online manual at http://mysql.com/ is comprehensive, updated frequently, and contains user contributions. Plenty of dead-tree form literature exists for MySQL as well. Much of it is very high quality. For example: MySQL by Paul Dubois, as of this writing, 47 reviewers on Amazon.com gave it an average rating of 4.8 out of a possible 5.
MYTH:	MySQL is UNIX only. No one uses that arcane system to get real work done!
MYTH:	MySQL interoperates poorly with my existing systems
FACT:	Letting the UNIX comment slide, MySQL runs on non-UNIX systems. Currently, you can download binaries for Win9x, NT, and OS/2. MySQL source reportedly compiles under a wide range of UNIX and non-UNIX systems. MySQL databases can be accessed via C, C++, ODBC (ie, Microsoft Access), Java, Perl, Python, PHP, TCL, Delphi, Ruby, Smalltalk, Pike, Eiffel, ADA, and many more
MYTH:	No service providers support MySQL
FACT:	Here is a list of ISPs that support MySQL. The ISP that used to host this document also supports MySQL.

Appendix A - Support

MySQL AB produced MySQL, which they naturally also support. They offer a variety of support options here: http://www.mysql.com/support/index.html
AbriaSoft also provides MySQL solutions
Innobase has many features, along with benchmarks to back up their claims. See the web site for more information.
Netgraft Corp provides support for both MySQL and applications developed on MySQL. Shameless plug alert: The company is operated by the maintainer of this document.

Appendix B - Benchmarks

DISCLAIMER: Proprietary database vendors often have a clause in their End User License Agreements (EULA) that prohibit benchmarking their software without their consent. Obviously, vendors will never publish benchmarks that are not in their favor. This further calls into question just how reliable benchmarks really are. My ``professional advice'' is to try the product for yourself. Benchmarks are fun to discuss, but rarely reflect actual usage patterns.

MySQL AB has performed its own benchmarks for MySQL. You can find them at this address. The tools they used to perform these benchmarks are available for download by anyone.

If you happen to have independent MySQL benchmarks, please clue me in.

Appendix C - Testimonials

Jeremy Zawodny <jzawodn@yahoo-inc.com> writes...

(2) Another data point for performance. One of my machines is a FreeBSD box with 2GB of RAM and a 1GHz CPU. I had it up to about 900 queries per second before the WAN bandwidth became the bottleneck.

A 2 CPU Linux box (850MHz CPUs) with 1GB of RAM was doing roughly 2.5 times as much work in the same sort of test.

Oh, and did I mention that those were almost all UPDATE queries.

(4) I have a server here running 3.23.29-gamma with a 100 day uptime and just under half a billion queries under its belt (right around 465 million currently).

Pat Wendorf <beholder@unios.dhs.org> writes...

P3-667, 256M Ram, 9.0G ATA-66 HDD (Dell Optiplex GX110) running FreeBSD 4.1.1-STABLE and MySQL 3.23.26-beta (need to upgrade, but I can't it's a 24/7 box :)

We handle 680 users doing production tracking. During peak load, and have clocked the machine (using the same method you do) at well over 500 transactions per second for more than 5 minutes. And it takes *that* much to peak out the CPU, otherwise at our regular load 1-5 TPS we're sitting at less than 1% CPU utilization!!! This is with 12 different productions systems on 12 separate databases. On top of that, this is the most stable production system we have!

Another win for the Open Source world: Our NT/MSSQL 7 server, though I imagine badly setup (it's not mine :), will take up to 8 seconds to process an ASP insert transaction on a similar machine with 120 users!!! It's sick! :)

Chris Harshman <charshman@sonypictures.com> writes...

Sony Pictures Digital Entertainment uses MySQL running as the content engine and registration collection point for a variety of high-traffic properties, including ricki.com, marsvenustv.com, sheenatv.com, judgehatchett.com, kingofqueens.com, and of course, www.sony.com/weddingplanner and www.sony.com/spiderman

We are about to migrate all of our movie site registrations to MySQL with a PHP front end.

Currently we are running 3.22.32, and the uptime is low because we had to migrate from a dual PIII 500MHz Xeon (also hosting mb.smashingpumpkins.com, which has a ton of Perl scripts that routinely choke the CPUs) to a dual PIII 933MHz Dell PowerEdge 2450 (1GB RAM, Ultra 160 RAID-1 for the database and another for the system partitions, ext2 filesystem).

We use MySQL for its speed, stability, cost ;), and ease of management.

Mike C <mike@eagleriver.com> writes...

Our company, GCI, uses MySQL as the hub of several software systems related to being a local telephone service provider. We write alot of software in Perl that interacts with telecomm devices like Lucent 5ESS & DEX switches, SS7 equipment, satellite modems, etc. over TCP/IP sockets. We use MySQL for centralized logging that all those interface programs can write to.

It's also the eternal swiss army database, you need custom reports? migrating huge amounts of phone lines or trunks? We can whip up quick perl apps that interact with those network elements, parse out results of huge reports and stuff those results into MySQL tables.

Appendix D - Links Of Interest

MySQL - The source of much of this information plus up to date MySQL news and information. The portals section of the site has many useful links as well.
AbriaSoft - Also provides MySQL solutions
Builder.com - Regular features on using MySQL for web development.

Verbatim copies of this document may be made without limitation provided that it is not changed and this copyright notice remains. The authoritative copy of this document may always be found at http://m.bacarella.com/projects/mysqlmyths/.

Comments and Testimonials are owned by the senders. They have all individually granted me permission to use their comments in this document.