MySQL 5.1 and Beyond

Sun has announced the general availability release of MySQL version 5.1 and Dr. Dobb's contributing editor Eric Bruno spoke with Robin Schumacher, Director of Product Management for MySQL at Sun, about this release and what's in store for MySQL in the future.

What's New?

The MySQL 5.1 release, over two years in making, includes enhancements in four main areas:

• Data warehousing and business intelligence
• High availability
• Manageability
• Performance enhancements. This includes diagnostics and techniques, problem troubleshooting, and assistance with load testing and benchmarking.

Although this release includes multitudes of enhancements, there are three individual features that have been asked for, and used in the preview releases, the most:

Table and index partitioning. Especially useful in the area of data warehousing, this feature allows you to horizontally slice tables and indexes. By doing this, your queries can be structured to scan only portions of a single table, as opposed to the entire table; this provides potentially large performance and administrative advantages. Although you cannot yet partition across physical servers, you can arrange the partitions to span multiple physical disk spindles, further enhancing performance.

Sun decided to implement all five common forms of partitioning: Range-based (such as by date); hash-based (evenly across partitions); key-based (similar to hash, but it uses the primary key); list-based (where you choose the column as the partition key); and composite, where you can choose a combination (i.e. partition by range first, and then by hash).

Replication. This was probably MySQL's most popular, and most requested, feature. In the past, MySQL offered statement-based replication, where each SQL statement sent to a server was replicated to one or more standby servers and executed there. However, this doesn't always work well with deterministic queries, such as those that generate keys, random numbers, and so on. To solve this, MySQL version 5.1 now offers row-based replication, where the actual row data (the result from the query on the executed server, not the statement) is replicated to the other servers. This helps to avoid the many out-of-sync errors that resulted before this feature was offered. Also, to help customers who aren't sure which replication type to choose, MySQL 5.1 offers mixed replication where the server decides for you, on the fly.

Database task scheduler. This is essentially a SQL job scheduler in DB, where you don't have to rely on external, OS-specific, schedulers. Instead, you express the job in SQL, and MySQL handles the scheduling and execution of the job(s). The result is platform independence for database-driven jobs, although they're limited to those that can be expressed in SQL.

Another area of enhancement is around tools: MySQL 5.1 offers new utilities to help find problems and issues with MySQL applications. These include the following:

New SQL Profiler. this tool goes deep into the query plan and beyond. It shows function calls, CPU and IO utilization for each part of a given query, all the way down to the internal MySQL source files. This is an ad-hoc, interactive, command-line tool that's meant to aid developers who are building and tuning the best queries for their applications.

Query Analyzer is more global, and big picture in nature. It's built to analyze an entire farm of deployed MySQL servers to provide insight into the performance of queries throughout an enterprise application.

MSQL Slap. This tool performs application benchmarking with simulated load testing. It works to simulate higher user-driven query load, over time, with enhanced reporting.

MySQL: The Future Vision

Of course, after MySQL 5.1 comes MySQL 6.0, details of which are available online already (see http://dev.mysql.com/downloads/mysql/6.0.html). With this release, there are currently two main areas of focus:

• The Falcon transaction storage engine. An alternative to innoDB that offers crash recovery, row-level locking, and so on.
• A backup utility. Faster, easier to use, online backups of ISAM tables. With this utility, you can backup databases while they're online being read from, and updated.

Beyond this, I asked Robin about Sun's grand vision for MySQL in the future. He summed it up quite elegantly, saying that MySQL's focus will be moving away from database management, and more towards data management in the future. After all, the business value is in the data, and that's where Sun believes it should focus MySQL's development efforts. This statement struck me as an excellent strategy, and one that other vendors might risk missing if they concentrate on nothing but features and performance. Sun, it seems, sees the bigger picture with MySQL.

Data Warehousing and Business Intelligence

Sure, Sun is doing the expected, such as bundling MySQL with Glassfish and OpenSolaris. However, MySQL is becoming a large part of Sun's strategy for cloud computing. In fact, according to Robin, MySQL is the #1 database in Amazon's cloud offerings. One area that will be more pronounced in MySQL's future is data warehousing, and in data marts.

Moving on from there, Sun has seen that business intelligence is the fifth most common use case for MySQL rollouts. In fact, it was discovered that over 50 percent of customers surveyed couldn't roll out their business intelligence initiative (based on other database engines) because of cost. This is one problem that Sun and MySQL plan to solve for its customers.

Current versions of MySQL haven't handled mulit-terabyte databases well on single servers. Sun is working to resolve this by working with partners such as Infobright and KickFire. The Infobright data engine takes a meta-data approach to solve problems with large data sets, while KickFire builds a SQL chip for query acceleration to handle larger database sizes.

Look at Sun and MySQL to expand more broadly into business intelligence with data warehouse solutions. They plan to do this with more than just market price disruption; they're innovating in ways you'd expect a nimble web 2.0 open-source vendor to.