MySQL Overview
Home Tutorials Reviews Weblog
Book Review: MySQL is the senior database partner in LAMP
Feature: That gives MySQL tremendous reach as it delivers an Enterprise database

MySQL released its 5.0 database which added such enterprise features as triggers, stored procedures and constraints to its Open Source database engines. Note the plural here. MySQL has a unique capability among database vendors - it fits the underlying database storage engines to the nature and use of each specific database instance. Now all database vendors claim that they do the same; but MySQL is the only vendor with pluggable engines like MyISAM for queries and BI, Innodb and the new Falcon engines for high speed transaction processing, or the Cluster engine for large scale cluster processing. And because MySQL is Open Source users can and do write their own special storage engines for changing routines called depending on whether a database instance is clustered or not, partitioned or not, used for BI with dimensional organization or not, etc. As we shall see this is both a strength and weakness for MySQL.

But there can be no argument about the strength of MySQL's broad and fairly uniform platform reach. It vies well with Oracle in having the broadest set of OS platforms and hardware architectures that it supports including over 2 dozen Linux distributions on i86 +AMD 32 and 64bit plus IA64bit, Solaris on Sparc and i86, HP/UX, AIX, Windows(NT, 2000, XP, 2003, or Vista), MacOS, Qnx, BSD and Netware. However, this range of platform support falls off notably with the MySQL GUI Toolkits for DB Administration, Designs, Migration, and Query. MySQL's own GUI Tools for development and admin are supported on smaller set of OS: Redhat, Fedora, Suse Linux, MacOS, and Windows only. For those DBAs not wedded to the command line utilities the Admin, Migration, and Query tools(see Query Browser above)are powerful and effective. In contrast, the ERD-Entity Relationship Design tool is still buggy and integrates poorly. Finally, MySQL also has a very broad range of connectors and adapters to a wide set of programming languages through: ODBC, JDBC, plus direct C libraries and routines in Java, .NET, PHP, Perl, Ruby, and VB. So MySQL has one of the broadest reaches of the free databases.

But the main line vendors like IBM, Oracle, Sybase and others had claimed that they were better suited to enterprise applications because they could deliver enterprise capabilities while MySQL doesn't deliver basic triggers, stored procedures and other enterprise features. Well with MySQL 5.0 a lot of those arguments fell a good way to the wayside:

  • ACID Transactions to build reliable and secure business critical applications
  • Stored Procedures to improve developer productivity
  • Triggers to enforce complex business rules at the database level
  • Views to gather information from multiple tables in easy to query ways
  • Information Schema to provide easy access to metadata about IT databases
  • Distributed Transactions (XA) to support complex transactions across multiple databases
  • Pluggable Storage Engine Architecture for maximum flexibility
  • Archive Storage Engine for historical and audit logs and data
  • Federated Storage Engine to create a single logical database from many physical servers
  • Enterprise Installer and Configuration Wizard to automate admin
  • Emergency Hot Fix Builds to provide better support and backup services

Now for many users in the know on databases, MySQL still has some shortcomings on such items as stored procedures, triggers, information schema and the Federated Storage Engine relative to whats available in DB2 or Oracle. Also complex and/or large database features such as partitioning, clustering and replication are either served in a primitive, "al dente" fashion or have competitive issues to be addressed. However, the MySQL 5.1 upgrade attacks many of those issues:
Partitioning - useful in BI and warehousing applications where huge tables are split on a key dimension over many different files and/or storage devices transparently to the end users improving speed of access dramatically in many cases. See here for status of partitioning.
Replication improvements - add row-based replication to the already existing statement-base method. Row-based replication uses the newly expanded event logs to replicated data. See here for the pros and cons of each in MySQL.
Event Scheduling - events are triggers whose initiation is determined solely by passage of intervals in time. MySQL now matches other vendors and supports such crontab-like events with its SQL tables and syntax, stored procedures and associated semantics.
Information Schema extensions - new metadata is tracked in MySQL 5.1 including New tables in that database such as FILES, EVENTS, PARTITIONS, PROCESSLIST, ENGINES, and PLUGINS which reflect new features discussed here.
Clustering improvements - clustering is a complex topic as there are many hardware and software designs for clustering including grid engines, master-slave, NUMA, etc. MySQL adds these critical improvements to its clustering capabilities include cluster based replication, improved cluster indexing speed, new disk as well as in-memory clustering modes, and improved cluster backups.
Plugin API - MySQL 5.1 is developing a new, flexible plugin API that enables loading and unloading of various DB components at runtime, without restarting the server. The prototype plugin, full-text parsers allows users to implement their own input filter on indexed text.
Backup and logging - MySQL 5.1 now supports logs as db tables as well as log files, new clustered backup routines, backup of tablespaces, and speeded backup processing.
Admin and utilities - improve XML functions for XPath and XQuery processing; a new load emulator for benchmarking expanded requirements, table type upgrades are now automated.
In sum, MySQL can now compete on the Enterprise level toe to toe with the other major databases vendors like never before. But of even more important is the fact that MySQL's customizable architecture gives it an advantage in innovation and development that IBM, Microsoft, Oracle and other database vendors will be hard pressed to match over the long haul.

MySQL's Storage Engine/Plugins Advantage

Finally, the MySQL 6.0 upgrade appears to be targeted towards finally replacing the Innodb engine owned by rival Oracle with a MySQL storage engine named Falcon. This engine and system takes advantage of the plug and play storage engine architecture that MySQL has opened up to 3rd parties. The new plugin component architecture expands MySQL's 3rd part customizations. This is an innovation that IBM, Oracle and other database vendors will have to pay attention to. For example, Nitro EDB has become a MySQL pluggable storage engine giving MySQL+NitroEDB instant access to the super-embedded database market. This is the same type of 3rd party add-ons that have made Visual Basic, Apex, and Zend PHP/Pear driving forces in their respective markets.

MySQL describes in detail the design framework for its storage engine/table type architecture and corresponding Plugin framework. The result is that MySQL is almost in the unique position of taking advantage of its Open Source roots. Customers as much as partners can and are acting as an additional R&D arm bringing market matching if not leading innovations to MySQL's doorsteps allowing them to keep pace with the torrid pace of current database development. It will also give MySQL a possible leg up in moving toward the versioning, semi-stuctured info, highly interoperable, consolidated co-operation/collaboration data world that is rapidly emerging as organizations work beyond the superficial and chimerical Windows presentation layer to the complexities of the underlying storage, messaging and secure collaborative processing that is required to provide true "information at your finger-tips".

Finally, MySQL has emphatically put to rest the Ballmer/Gates notion that Open Source cannot possibly compete against the profit-driven software vendors like themselves. Open Source can be service and support plus customer/partner led innovative - leading to a model of profitable Open Source software. This is in stark contrast to the too often Eula + Monopoly-market coercive regime offered by many packaged software vendors.


MySQL has cachet where the other database vendors would really like to be - preferred Open Source database where users can and do download and deploy MySQL for free and use in literally millions of small to medium scale applications. Second, as senior database in LAMP(despite all the efforts of PostgreSQL database community)- MySQL's Linux/Apache/MySQL/PHP/Python/Perl position gives it access to over 2/3rds of Web database servers. I addition, as noted there is an army of third party tool vendors developing tools and applications that utilize MySQL database services as their point of departure. Finally, MySQL comes with just about every Linux distribution, and is readily installed on Windows, Mac and Solaris for both server and client operations. This is tremendous breadth often at a very attractive TCO.

But MySQL also has problems. Its very rapid pace of development has left 3.x and 4.x users with major upgrade tasks. MySQL until version 5.1 has had to play junior Enterprise database to Oracle, DB2 and others in essential cluster, partitioning and replication features. Now spatial data, Web Services and native XML support are the new hurdles MySQL has to overcome.

Finally, even as an Open Source database MySQL is open to monopolistic attack. Because MySQL is so dependent on service, support, and education for its revenues, it is vulnerable to a number of attacks from database vendors who charge per seat/per server for their products as well as for service and support. Oracle has revealed one such vulnerability by charging half-price for Red Hat Linux support - thus damaging that Open Source vendors primary revenue stream. Microsoft has reportedly struck deals where the database and support are given away for free in a package with Windows SQL Server 2003 picking up a per seat and super support tab less than MySQL+Windows Server 2003 seat + support. In effect, big enterprise vendors like IBM, Oracle and Microsoft can and do use private contract giveaways to tilt the database playing field in their favor. Finally, in technical standards and innovation, all the other major vendors have much bigger patent portfolios and standards committee presence than MySQL, again potentially jeopardizing MySQL's ability to shape the direction and intent of standards and innovations.

In sum, MySQL is a dramatic case of an Open Source software vendor which adopted a hybrid business model and has succeeded remarkably well to date. But the market is fraught with change and very tough competitors who now consider MySQL as major threat. As innovations continue to percolate rapidly through the database field it will be interesting to see how MySQL the database system evolves and hopefully prospers.

Top of Page  Home  Tutorials 

Developers Weblog  Graphics Reviews and Tips