New features in MySQL 5.1

Michael Kofler

With new functions MySQL 5.1 hopes more than ever to be THE large database for business.

Since the big leap forward to MySQL 5.0, it's taken the MySQL development team three years, during which there have been a whole string of pre-release versions[1], to release the new version 5.1[2] of the popular database. MySQL 5.1.30 (General Availability) is available to download from various mirrors[3].

The most significant new features in MySQL 5.1 are the partitioning functions. These allow large tables to be distributed across multiple physical files and hard drives. MySQL supports what is known as horizontal partitioning, in which records are selected on the basis of a set criterion and saved in different locations. DATE columns are particularly suitable as criteria for this type of partitioning. Vertical partitioning, i.e. separate storage of different columns, is not supported, but can be implemented relatively easily simply by using a second table – with, for example, the rarely-used BLOG fields.

In the past, database partitioning was frequently used to get around hard drive size limits. In the age of RAID, LVM and terabyte hard drives, the reasons for partitioning have changed – now, it's used because it can significantly speed up database queries. If, for example, a table containing records from 1980 to 2010 is divided into six sub-tables by date, a query such as "SELECT ... WHERE date BETWEEN 2003 AND 2004" need consult only a single sub-table. Rather than processing millions of records, less than 200,000 records need be considered. Theoretically, it is also possible to divide queries using special functions (e.g. SUM and COUNT) across partitioned tables across multiple threads. MySQL does not yet, however, support this form of optimisation. There are also various other restrictions which apply to partitioned tables, concerning issues such as the formulation of partitioning functions or the use of foreign key rules.

Events and record-based replication

Also new is the event scheduler. This component deals with performing predefined SQL commands at regular intervals. The MySQL manual compares these functions with cron-jobs under Unix.

Many applications of this function are possible – regular logging of database status, automatic backup or synchronisation services (although MySQL's replication functions are perhaps more suitable for this), regular generation of tables with frequently used query results (e.g. top 10 lists which are generated hourly or daily and are then instantly available) and so on. One serious disadvantage of such events is that they are an internal MySQL development and are not covered by any SQL standards.

The replication function for synchronising a database across multiple MySQL servers has also been extended. Previously the replication slave simply re-executed all the SQL commands executed on the replication master. The new record-based replication system allows changed data to be transferred directly. This is in some cases more efficient and also allows replication where non-deterministic functions such as UUID() are used. The default is now a mixed mode, in which record-based replication is only used where it is required or where it promises to be faster.

Since MySQL 5.0, the virtual information_schema database has allowed queries by various items of meta-information – what databases and tables exist, what characteristics their columns have, what users, triggers, stored procedures and views are defined, etc. The scope of this database has been significantly extended in MySQL 5.1 and it is an important aid for a variety of administrative tasks.

Plugins, XML, PHP

One useful feature of MySQL is that there are different types of table which can be implemented by different database engines, such as MyISAM and InnoDB. A new feature in MySQL 5.1 is a plugin API, which allows engines to be added and removed retrospectively. The API still appears to be something of a work in progress; each plugin must be recompiled for each subversion of MySQL. Table plugins are currently more of an aid to developers than a user-friendly extension. In the long term, however, table plugins could create an interesting market for suppliers offering MySQL plugins optimised for specific tasks.

A more obviously useful idea is being able to write logs to tables, rather than normal files. MySQL 5.1 offers this option for the general query log and the slow query log (but not for the binary log, which is required for replication). However, it turns out that this function can slow down[4] the MySQL server in some applications. This significantly reduces the usefulness of this new feature, especially as no bug-fix is expected prior to version 6.0.

Users who save XML data in MySQL tables will appreciate two new functions. ExtractValue applies an XPath expression to an XML string, returning a new string as the result. UpdateXML replaces a substring within an XML string selected using an XPath expression with another substring. The two XML functions are highly useful in some circumstances, but are still a long way from making MySQL an XML database.

As far as MySQL programming is concerned, there is an interesting new feature for PHP developers. Starting with PHP 5.3, the mysqlnd library is used as the new backend for the PHP extensions mysql, mysqli and probably also for PDO/mysql. The mysqlnd library thus replaces libmysql, designed for C programmers. The mysqlnd library, which has been created by the MySQL development team, is not only more efficient than libmysql, it can also be directly integrated into PHP code.

The mysqlnd development team have promised that the new backend will not change anything with regard to syntax or use of the mysql, mysqli and PDO/mysql PHP interfaces. This is not entirely accurate however – mysqlnd only supports the secure authentication procedure introduced in MySQL 4.1. Due to compatibility issues, some Linux distributions are still not using this procedure even today (old_passwords=1 in my.cnf). This suggests that there may be login problems when switching to PHP 5.3 which can only be resolved by regenerating all MySQL passwords.

---

GUIs

There are a number of administration tools with graphical user interfaces for MySQL. Older program such as MySQL Query Browser and MySQL Administrator have been supplemented by newer GUIs such as MySQL Workbench, which allows database schemas to be designed visually. The program is currently only available for Windows, but a Linux version is in the alpha testing phase.

The Java-based, and thus platform-independent, MySQL Migration Toolkit is useful for migrating databases from other database systems to MySQL. This works well for the actual tables and their content. It is not, however, possible to import stored procedures and triggers, as almost all database systems use a different syntax for SPs. It is also annoying that SQL Server Authentication is required in order to establish a connection with Microsoft SQL Server. Windows "alias integrated security" authentication is not supported.

The administration tools listed have been created by the MySQL development team and, like MySQL, are available free of charge. For enterprise customers, MySQL Workbench offers a number of additional functions. Enterprise users can also utilise the new Enterprise Monitor[5] for monitoring the status of one or more MySQL servers and searching for optimisation options.

First and second class users

MySQL makes its database available under a dual licensing model[6]. As well as the GPL version, there is also a paid-for version for commercial applications which does not compel users to conform to the GPL, and in particular, does not require them to disclose additional development. In autumn 2006, MySQL went one step further – now, compiled installation packages for the GPL-licensed community version are only released every six months. Subscribers to the paid-for enterprise version, in addition to support and the Enterprise Monitor, also receive monthly updates. The community version of MySQL 5.0 often lags some way behind the enterprise edition.

This is completely in accord with the rules of the GPL – the latest source code is available on a daily basis from the MySQL Bazaar[7], and commercial customers are not prevented from passing-on the current enterprise version.

Nevertheless, many open source users have been irritated[8] by this strategy. Community edition users who contribute to MySQL development with bug reports, patches, etc. reap the fruits of their labour later than enterprise customers. The long wait for the next community edition is also unsatisfactory from the point of view of security-related updates.

The situation is not ideal even for paying customers, as the enterprise version is used and tested by a significantly smaller group of users, with the result that it can take longer for bugs to be uncovered and fixed. Other suppliers who offer both a free community version and a paid-for enterprise version, such as Red Hat, tend to follow the converse strategy – the latest Fedora distribution is available for free, but Red Hat Enterprise Linux offers stability and long release intervals.

Nonetheless, following the $1Bn takeover by Sun[9] in January 2008, MySQL appears to have backed away from the idea of offering specific MySQL features to enterprise customers only[10]. The entire MySQL Server code is still subject without restrictions to the GPL. This does not, however, apply to additional external software such as Enterprise Monitor.

So what's next?

In April 2007, long before completion of MySQL 5.1, MySQL released the first public test version of MySQL 6[11] – albeit with no release schedule. The two most important new features in MySQL 6 are the transactional table types "Falcon" and "Maria". Falcon[12] is optimised for use on computers with lots of RAM and a large number of CPUs. However the main Falcon developer[13], Jim Starkey[14], retired from MySQL in June 2008 after two and a half years with the company. Maria[15], in turn, is optimised for large databases and is intended eventually to replace the MyISAM table type.

Falcon and Maria are MySQL's (belated) response to Oracle[16]'s acquisition of Innobase[17]. Innobase's InnoDB database engine is preferred by many MySQL users due to its transaction support and greater speed. Although InnoDB continues to be developed in conformity with the GPL and only the Hot Backup program is paid-for, the dependency on Oracle raises doubts about the long-term future of the InnoDB table type. When and whether the two new table types will be able to match InnoDB for performance remains to be seen.

A little disappointing is the fact that a number of long-term deficits of MySQL have not been addressed in version 6. These include debugging functions for stored procedures, foreign key rules (which are currently only implemented specifically for InnoDB) and hot backups (backups during ongoing operation), which are currently only available as a paid-for InnoDB add on.

A talk by MySQL co-founder Michael 'Monty' Widenius[18] gives a list of internal deficits in MySQL Server – poor scaling to eight CPUs/cores, non-optimal RAM usage, no external user authentication (such as via LDAP), inefficient internal SQL parser, poor community involvement, unclear release policies, etc. The talk is disarmingly honest, but makes it clear just how much work MySQL still has to do. This suggests that we may be in for a long wait before MySQL 6.0 finally hits the streets.

Until then, the biggest breath of fresh air in the MySQL world is Project Drizzle[19]. This is a minimal MySQL fork based on version 6.0[20]. Many of the extensions added over the last few years, including views, stored procedures and various exotic table types, have been removed from the code. The Windows version has also fallen victim to this zest for tying up loose ends. The goal is a kind of MySQL Lite – GPL only, no commercial version – with clean, tidy code. As well as MySQL fans, MySQL staff are also – with Sun's blessing – involved with Drizzle.

Summary

Typical LAMP developers dealing with small to medium-sized websites will see little benefit from the upgrade to version 5.1. It begs the question of whether the once-lean database system isn't already overloaded with overhead and whether SQLite (or someday Drizzle) mightn't do the job.

For larger commercial customers, the verdict is likely to be far more positive. The new features in MySQL 5.1 are likely to be of more interest to this group and promise greater efficiency – especially for larger databases. MySQL still offers fewer function than Oracle, SQL Server and the like, but scores points for greater speed and offers unbeatable value.

It remains to be seen whether the strict separation between the community and enterprise version is of long-term benefit. It has certainly deterred many open source fans from working on the project.

(lghp[21])

URL of this article:
http://www.heise-online.co.uk/open/features/112097

Links in this article:
  [1] http://monty-says.blogspot.com/2008/07/bugs-life.html
  [2] http://dev.mysql.com/tech-resources/articles/mysql-5.1-recap.html
  [3] http://dev.mysql.com/downloads/mirrors.html
  [4] http://bugs.mysql.com/bug.php?id=30414
  [5] http://www.mysql.com/products/enterprise/monitor.html
  [6] http://www.mysql.com/about/legal/licensing/oem/
  [7] http://dev.mysql.com/tech-resources/articles/getting-started-with-bazaar-for-mysql.html
  [8] http://www.mysqlperformanceblog.com/2008/07/01/should-we-proclaim-mysql-community-edition-dead/
  [9] http://www.mysql.com/news-and-events/sun-to-acquire-mysql.html
  [10] http://www.heise-online.co.uk/news/MySQL-drops-features-from-GPL-version--/110564
  [11] http://dev.mysql.com/downloads/mysql/6.0.html
  [12] http://dev.mysql.com/tech-resources/articles/falcon-in-depth.html
  [13] http://www.theopenforce.com/2008/06/falcon-and-jim.html
  [14] http://en.wikipedia.org/wiki/Jim_Starkey
  [15] http://monty-says.blogspot.com/2008/01/maria-engine-is-released.html
  [16] http://www.oracle.com/index.html
  [17] http://www.oracle.com/corporate/press/2005_oct/inno.html
  [18] http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project
  [19] https://launchpad.net/drizzle
  [20] http://openquery.com.au/products/about-drizzle
  [21] mailto:lghp@heise-online.co.uk

Copyright © 2008 Heise Media UK Ltd. Privacy Policy Contact us