Sun MySQL 5.1 Upgrades Partitioning, Replication

Sun has launched the 5.1 release of the MySQL database with improved indexing, replication, and ease of administration.

The release includes several performance enhancing changes, including new methods of indexing and horizontal table partitioning. The system is available for download from Sun's MySQL Web site.

MySQL database administrators may now partition both tables and indexes by range, an approach that's often used to isolate sales results by month or implement other distinct time frames or sets of numbers. With range partitioning, a query asking for October sales results would go directly to the October partition of the index and sales data table. It could then retrieve results with greater speed than the previously required full table scan, said Robin Schumacher, director of MySQL product management, in an interview.

Another form of partitioning now available is hash partitioning, where the data table has been evenly divided up into segments and a query may be directed to the right segment.

A list partition would divide data into relevant lists, such as listing customer data by the state in which they're located. A key partition would use a primary key or unique identifier of a row of data in the table, as the basis for segmenting the table. A composite partition allows a second partition to occur within an already partitioned segment of the table, yielding a subset or subpartition.

Having the option to partition tables five ways puts MySQL on more of an equal footing with commercial systems, especially those used in assembling large data sets in data warehouses or data marts. Without the advanced partitioning, MySQL was forced to do full table scans to answer a data warehouse query, Schumacher said.

MySQL has added more sophisticated replication as well. It typically is set up as a master MySQL database replicating to "slave" or subsystems that await synchronization by the master. If a function is executed on the master system, then the same function gets executed on the slave systems afterward to maintain data integrity.

In a few cases, this approach doesn't result in database synchronization, however, explained Schumacher. If the original function was to generate a random number, then a different number is likely to result on the slave systems. So MySQL has added row-based replication, which duplicates the original number generated, rather than the function.

MySQL 5.1 also has automated analysis or a "no-brainer" setting to tell it whether to do function or row-based replication, if the administrator doesn't know which is appropriate, Schumacher said.

It now includes Query Analyzer, which give visibility into query performance and identifies resource hogs in database use. Query Analyzer became available before the general availability of MySQL 5.1 but has now been incorporated into the system.