Skip navigation links
MySQL 5.5 Reference Manual :: 1 General Information :: 1.4 What Is New in MySQL 5.5
1.4.1 Scalability Improvements »
Section Navigation      [Toggle]

1.4. What Is New in MySQL 5.5

[+/-]

1.4.1. Scalability Improvements
1.4.2. InnoDB I/O Subsystem Changes
1.4.3. Diagnostic and Monitoring Capabilities
1.4.4. Enhanced Solaris Support

This section summarizes what has been added to and removed from MySQL 5.5.

Added Features

The following features have been added to MySQL 5.5:

  • Thread pooling.  The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. As of MySQL 5.5.16, commercial distributions of MySQL include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. For more information, see Section 8.11.6, “The Thread Pool Plugin”.

  • MySQL Enterprise Audit.  MySQL Enterprise Edition now includes MySQL Enterprise Audit, implemented using a server plugin named audit_log. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines. When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access. For more information, see Section 6.3.10, “MySQL Enterprise Audit Log Plugin”.

  • Pluggable authentication.  MySQL authentication supports two new capabilities, pluggable authentication and proxy users. With pluggable authentication, the server can use plugins to authenticate incoming client connections, and clients can load an authentication plugin that interacts properly with the corresponding server plugin. This capability enables clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than the built-in MySQL authentication based on native MySQL passwords stored in the mysql.user table. For example, plugins can be created to use external authentication methods such as LDAP, Kerberos, PAM, or Windows login IDs. Proxy user capability enables a client who connects and authenticates as one user to be treated, for purposes of access control while connected, as having the privileges of a different user. In effect, one user impersonates another. Proxy capability depends on pluggable authentication because it is based on having an authentication plugin return to the server the user name that the connecting user impersonates. See Section 6.3.6, “Pluggable Authentication”, and Section 6.3.7, “Proxy Users”.

    As of MySQL 5.5.16, commercial distributions of MySQL include two plugins that enable MySQL Server to use external authentication methods to authenticate MySQL users:

    • PAM (Pluggable Authentication Modules) enables a system to access various kinds of authentication methods through a standard interface. A PAM authentication plugin enables MySQL Server to use PAM to authenticate MySQL users.

    • Distributions of MySQL for Windows include an authentication plugin that enables MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.

    These authentication plugins enable MySQL Server to accept connections from users defined outside the MySQL grant tables. They also support the MySQL proxy-user capability. Each plugin can return to MySQL a user name different from the login user, which means that the plugin can return the MySQL user that defines the privileges the externally authenticated user should have.

    For more information, see Section 6.3.6.2, “The PAM Authentication Plugin”, and Section 6.3.6.3, “The Windows Native Authentication Plugin”.

  • Multi-core scalability.  Scalability on multi-core CPUs is improved. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. MySQL 5.5 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores. The focus has been on InnoDB, especially locking and memory management. See Section 1.4.1, “Scalability Improvements”.

  • InnoDB I/O subsystem.  InnoDB I/O subsystem changes enable more effective use of available I/O capacity. See Section 1.4.2, “InnoDB I/O Subsystem Changes”.

  • Diagnostic improvements.  There is better access to execution and performance information. Diagnostic improvements include Performance Schema (a feature for monitoring MySQL Server execution at a low level), DTrace probes, expanded SHOW ENGINE INNODB STATUS output, Debug Sync, and a new status variable. See Section 1.4.3, “Diagnostic and Monitoring Capabilities”.

  • Solaris.  Several modifications improve operation of MySQL Server on Solaris. See Section 1.4.4, “Enhanced Solaris Support”.

  • Default storage engine.  The default storage engine for new tables is InnoDB rather than MyISAM. See Section 14.3.1, “InnoDB as the Default MySQL Storage Engine”.

  • MySQL Cluster.  MySQL Cluster is released as a separate product, with new development for version 7.2 of the NDB storage engine being based on MySQL 5.5. Clustering support is not available in mainline MySQL Server 5.5 releases. For more information about MySQL Cluster NDB 7.2, see Chapter 17, MySQL Cluster NDB 7.2.

    MySQL Cluster releases are identified by a 3-part NDB version number. Currently, MySQL Cluster NDB 7.1 is the most recent GA release series. MySQL Cluster NDB 6.3 and MySQL Cluster NDB 7.0 are also still available. These versions of MySQL Cluster are based on MySQL Server 5.1 and documented in the MySQL 5.1 Manual. See MySQL Cluster NDB 6.1 - 7.1.

  • Semisynchronous replication.  A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 16.3.8, “Semisynchronous Replication”

  • Unicode.  Support for supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP). These new Unicode character sets include supplementary characters: utf16, utf32, and utf8mb4. See Section 10.1.10, “Unicode Support”.

  • Partitioning.  Enhancements to table partitioning:

    • Two new types of user-defined partitioning are supported: RANGE COLUMNS partitioning is an extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:

      • It is possible to define partitioning ranges or lists based on DATE, DATETIME, or string values (such as CHAR or VARCHAR).

        You can also define ranges or lists based on multiple column values when partitioning tables by RANGE COLUMNS or LIST COLUMNS, respectively. Such a range or list may refer to up to 16 columns.

      • For tables defined using these partitioning types, partition pruning can now optimize queries with WHERE conditions that use multiple comparisons between (different) column values and constants, such as a = 10 AND b > 5 or a < "2005-11-25" AND b = 10 AND c = 50.

      See Section 18.2.1, “RANGE Partitioning”, and Section 18.2.2, “LIST Partitioning”.

    • It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE ... TRUNCATE PARTITION statement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the TRUNCATE PARTITION clause do not have to be contiguous.

    • Key caches are now supported for indexes on partitioned MyISAM tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements. In addition, a key cache can be defined for and loaded with indexes from an entire partitioned table, or for one or more partitions. In the latter case, the partitions are not required to be contiguous.

    • The new TO_SECONDS() function converts a date or datetime expression to a number of seconds since the year 0. This is a general-purpose function, but is useful for partitioning. You may use it in partitioning expressions, and partition pruning is supported for tables defined using such expressions.

  • SIGNAL and RESIGNAL.  Support for the SQL standard SIGNAL and RESIGNAL statements. See Section 13.6.7, “Condition Handling”.

  • Metadata locking.  The server now prevents DDL statements from compromising transaction serializibility by using a new class of locks called metadata locks. See Section 8.10.4, “Metadata Locking”.

  • IPv6 support.  MySQL Server can accept TCP/IP connections from clients connecting over IPv6. See Section 5.1.9, “IPv6 Support”.

  • XML.  Enhancements to XML functionality, including a new LOAD XML INFILE statement. See Section 13.2.7, “LOAD XML Syntax”.

  • Build configuration.  MySQL releases are now built using CMake rather than the GNU autotools. Accordingly, the instructions for installing MySQL from source have been updated to discuss how to build MySQL using CMake. See Section 2.10, “Installing MySQL from Source”.

    The build process is now similar enough on all platforms, including Windows, that there are no longer sections dedicated to notes for specific platforms.

Removed Features

The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices