MySQL :: MySQL 5.5 Reference Manual :: 1.4 What Is New in MySQL 5.5

version 5.5

8.0
5.7 current
5.6

5.6 Japanese

MySQL 5.5 Reference Manual / General Information / What Is New in MySQL 5.5

1.4 What Is New in MySQL 5.5

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

Features Added in MySQL 5.5

The following features have been added to MySQL 5.5:

MySQL Enterprise Thread Pool. 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, MySQL Enterprise Edition distributions 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 5.5.4, “MySQL Enterprise Thread Pool”.
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.5.2, “MySQL Enterprise Audit”.
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, MySQL Enterprise Edition includes 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.5.1.3, “The PAM Authentication Plugin”, and Section 6.5.1.4, “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 Scalability Improvements.
Default storage engine. The default storage engine for new tables is InnoDB rather than MyISAM. See Section 14.1, “Introduction to InnoDB”.
InnoDB I/O subsystem. InnoDB I/O subsystem changes enable more effective use of available I/O capacity. See InnoDB I/O Subsystem Changes.
InnoDB storage engine. MySQL 5.5 includes several InnoDB storage engine enhancements:
- Indexes can be added or dropped without copying the table. See Section 14.16, “InnoDB Fast Index Creation”.
- Tables can be compressed to significantly reduce storage requirements and I/O. See Section 14.12, “InnoDB Table Compression”.
- BLOB, TEXT, and VARCHAR columns can be stored fully off page. See Section 14.14, “InnoDB Row Storage and Row Formats”.
- File format management enhancements protect upward and downward compatibility. See Section 14.13, “InnoDB File-Format Management”.
- INFORMATION_SCHEMA tables provide information about InnoDB compression and locking. See Section 14.18, “InnoDB INFORMATION_SCHEMA Tables”.
- InnoDB performance and scalability enhancements:
  - The InnoDB mutex and read/write lock implementation was improved. Use of Pthreads mutexes was replaced with calls to GCC - atomic builtins.
  - The memory allocator used by InnoDB is configurable. See Section 14.9.3, “Configuring the Memory Allocator for InnoDB”.
  - The extent to which InnoDB performs change buffering is configurable. See Section 14.9.4, “Configuring InnoDB Change Buffering”.
  - The adaptive hash index (AHI) feature makes InnoDB perform more like an in-memory database on systems with appropriate combinations of workload and ample memory for the buffer pool, without sacrificing transactional features or reliability. See Section 14.7.3, “Adaptive Hash Index”.
  - Different techniques can be used to limit the number of concurrently executing operating system threads to minimize context switching. See Section 14.9.5, “Configuring Thread Concurrency for InnoDB”.
  - How InnoDB performs buffer pool read-ahead is configurable. See Section 14.9.2.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
  - The number of background threads that service read and write I/O operations on data pages is configurable. See Section 14.9.6, “Configuring the Number of Background InnoDB I/O Threads”.
  - Asynchronous I/O is supported on Linux systems. See innodb_use_native_aio.
  - The overall I/O capacity available to InnoDB is configurable. See Section 14.9.7, “Configuring the InnoDB Master Thread I/O Rate”.
  - How InnoDB performs buffer pool flushing is configurable. Section 14.9.2.5, “Configuring InnoDB Buffer Pool Flushing”.
  - The maximum delay between checking the availability of a mutex or rw-lock is configurable. See Section 14.9.8, “Configuring Spin Lock Polling”.
  - InnoDB can be configured to minimize the amount of data brought into the buffer pool and never accessed again. See Section 14.9.2.3, “Making the Buffer Pool Scan Resistant”.
  - Crash recovery performance was improved. See Section 8.5.8, “Optimizing InnoDB Configuration Variables”.
  - Certain internal InnoDB operations can be profiled using the Performance Schema feature. See Section 14.19, “InnoDB Integration with MySQL Performance Schema”.
  - The buffer pool can be divided into separate instances to reduce contention between threads that read and write to cached pages. See Section 14.9.2.2, “Configuring Multiple Buffer Pool Instances”.
  - The limit on concurrent data modifying transactions was increased. See Section 14.7.8, “Undo Logs”.
  - InnoDB can be configured to have purge operations performed by a separate thread, rather than by the master thread. See Section 14.9.9, “Configuring InnoDB Purge Scheduling”.
- InnoDB flexibility, ease of use, and reliability enhancements:
  - The innodb_file_per_table, innodb_stats_on_metadata, innodb_lock_wait_timeout, and innodb_adaptive_hash_index options can be set at runtime using a SET statement.
  - Operating system disk space can be reclaimed when truncating an InnoDB table. See Section 14.15.5, “Reclaiming Disk Space with TRUNCATE TABLE”.
  - InnoDB can be run in strict mode. See the innodb_strict_mode parameter documentation.
  - InnoDB provides greater control over the quality of optimizer statistics estimates. See Section 14.9.10, “Configuring Optimizer Statistics for InnoDB”.
  - SHOW ENGINE INNODB MUTEX output is more compact. See Section 13.7.5.16, “SHOW ENGINE Syntax”.
  - SHOW ENGINE INNODB STATUS output displays counter information for the Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted global status variables, which you can use to fine-tune the innodb_random_read_ahead setting and evaluate the effectiveness of the read-ahead algorithm.
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 Diagnostic and Monitoring Capabilities.
Solaris. Several modifications improve operation of MySQL Server on Solaris. See Enhanced Solaris Support.
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 18, MySQL NDB Cluster 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 Reference Manual.
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 17.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.9, “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 19.2.1, “RANGE Partitioning”, and Section 19.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.11.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.9, “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.

Features Deprecated in MySQL 5.5

The following features are deprecated in MySQL 5.5 and may be or will be removed in a future series. Where alternatives are shown, applications should be updated to use them.

Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.
The YEAR(2) data type. YEAR(2) columns in existing tables are treated as before, but YEAR(2) in new or altered tables are converted to YEAR(4). For more information, see Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.
The SHOW AUTHORS and SHOW CONTRIBUTORS statements.
The --ignore-builtin-innodb server option. It does nothing and has no effect.
The --language server option. Use the lc_messages_dir and lc_messages sytem variables instead.
The ALWAYS value for the --base64-output option for mysqlbinlog.
The --config-file option for mysqld_multi. Use --defaults-extra-file instead.
Use of unambigious option prefixes. If an unambiguous prefix is given, a warning occurs to provide feedback. Option prefixes are no longer supported in MySQL 5.7; only full options are accepted.
The engine_condition_pushdown system variable. Use the engine_condition_pushdown flag of the optimizer_switch variable instead.
The timed_mutexes system variable. It does nothing and has no effect.
The storage_engine system variable. Use default_storage_engine instead.
Use of the data directory as the location for my.cnf.

Features Removed in MySQL 5.5

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

The language system variable (use lc_messages_dir and lc_messages).
The log_bin_trust_routine_creators system variable (use log_bin_trust_function_creators).
The myisam_max_extra_sort_file_size system variable.
The record_buffer system variable (use read_buffer_size).
The sql_log_update system variable.
The Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq status variables (use Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted).
The table_lock_wait_timeout system variable.
The table_type system variable (use default_storage_engine).
The FRAC_SECOND modifier for the TIMESTAMPADD() function (use MICROSECOND).
The TYPE table option to specify the storage engine for CREATE TABLE or ALTER TABLE (use ENGINE).
The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).
The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements (use SHOW ENGINE INNODB STATUS and SHOW ENGINE INNODB MUTEX).
The SHOW PLUGIN SQL statement (use SHOW PLUGINS).
The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).
The BACKUP TABLE and RESTORE TABLE SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).
TIMESTAMP(N) data type: The ability to specify a display width of N (use without N).
The --default-character-set and --default-collation server options (use --character-set-server and --collation-server).
The --default-table-type server option (use --default-storage-engine).
The --delay-key-write-for-all-tables server option (use --delay-key-write=ALL).
The --enable-locking and --skip-locking server options (use --external-locking and --skip-external-locking).
The --log-bin-trust-routine-creators server option (use --log-bin-trust-function-creators).
The --log-long-format server option.
The --log-update server option.
The --master-xxx server options to set replication parameters (use the CHANGE MASTER TO statement instead): --master-host, --master-user, --master-password, --master-port, --master-connect-retry, --master-ssl, --master-ssl-ca, --master-ssl-capath, --master-ssl-cert, --master-ssl-cipher, --master-ssl-key.
The --safe-show-database server option.
The --skip-symlink and --use-symbolic-links server options (use --skip-symbolic-links and --symbolic-links).
The --sql-bin-update-same server option.
The --warnings server option (use --log-warnings).
The --no-named-commands option for mysql (use --skip-named-commands).
The --no-pager option for mysql (use --skip-pager).
The --no-tee option for mysql (use --skip-tee).
The --position option for mysqlbinlog (use --start-position).
The --all option for mysqldump (use --create-options).
The --first-slave option for mysqldump (use --lock-all-tables).
The --config-file option for mysqld_multi (use --defaults-extra-file).
The --set-variable=var_name=value and -O var_name=value general-purpose options for setting program variables (use --var_name=value).
The --with-pstack option for configure and the --enable-pstack option for mysqld.

Scalability Improvements

MySQL 5.5 modifications improve performance on SMP systems to increase scalability on multi-core systems. The changes affect InnoDB locking and memory management.

MySQL 5.5 incorporates changes in InnoDB that improve the performance of RW-locks by using atomic CPU instructions (on platforms where they are available), rather than less scalable mutexes. It is also possible for InnoDB memory allocation to be disabled and replaced by the normal malloc library, or by a different library that implements malloc such as tcmalloc on Linux or mtalloc on Solaris.

The reimplementation of RW-locks requires atomic instructions. A status variable, Innodb_have_atomic_builtins, shows whether the server was built with atomic instructions.

InnoDB I/O Subsystem Changes

MySQL 5.5 changes to the InnoDB I/O subsystem enable more effective use of available I/O capacity. The changes also provide more control over configuration of the I/O subsystem.

Background I/O Threads

InnoDB uses background threads to perform I/O for several kinds of activities, two of which are prefetching disk blocks and flushing dirty pages. Previously, InnoDB used only one thread each to perform these activities, but that can underutilize server capacity. MySQL 5.5 enables use of multiple background read and write threads, making it possible to read and write pages faster.

The patch makes the number of background I/O threads configurable using system variables: innodb_read_io_threads controls the number of threads to use for read prefetch requests. innodb_write_io_threads controls the number of threads to use for writing dirty pages from the buffer cache to disk. The default for both variables is 4.

The ability to increase the number of I/O threads can benefit systems that use multiple disks for InnoDB. However, the type of I/O being done should be considered. On systems that use buffered writes rather than direct writes, increasing the write thread count higher than 1 might yield little benefit because writes will be quick already.

Adjustable I/O Rate

Previously, the number of input/output operations per second (IOPS) that InnoDB will perform was a compile-time parameter. The rate was chosen to prevent background I/O from exhausting server capacity and the compiled-in value of 100 reflected an assumption that the server can perform 100 IOPS. However, many modern systems can exceed this, so the value is low and unnecessarily restricts I/O utilization.

MySQL 5.5 exposes this I/O rate parameter as a system variable, innodb_io_capacity. This variable can be set at server startup, which enables higher values to be selected for systems capable of higher I/O rates. Having a higher I/O rate can help the server handle a higher rate of row changes because it may be able to increase dirty-page flushing, deleted-row removal, and application of changes in the change buffer. The default value of innodb_io_capacity is 200. In general, you can increase the value as a function of the number of drives used for InnoDB I/O.

The ability to raise the I/O limit should be especially beneficial on platforms that support many IOPS. For example, systems that use multiple disks or solid-state disks for InnoDB are likely to benefit from the ability to control this parameter.

Diagnostic and Monitoring Capabilities

MySQL 5.5 provides improved access to execution and performance information. Diagnostic improvements include Performance Schema, Dtrace probes, expanded SHOW ENGINE INNODB STATUS output, Debug Sync, and a new status variable.

Performance Schema

Performance Schema is a feature for monitoring MySQL Server execution at a low level. See Chapter 22, MySQL Performance Schema.

DTrace Support

The DTrace probes work on Solaris, OS X, and FreeBSD. For information on using DTrace in MySQL, see Section 5.7, “Tracing mysqld Using DTrace”.

Enhanced SHOW ENGINE INNODB STATUS Output

The output from SHOW ENGINE INNODB STATUS includes more information due to changes made for InnoDB Plugin. A description of revisions to statement output follows.

A new BACKGROUND THREAD section has srv_master_thread lines that show work done by the main background thread.

----------
BACKGROUND THREAD
----------
srv_master_thread loops: 53 1_second, 44 sleeps, 5 10_second, 7 background,
  7 flush
srv_master_thread log flush and writes: 48

The SEMAPHORES section includes a line to show the number of spinlock rounds per OS wait for a mutex.

----------
SEMAPHORES
----------
...
Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl

Debug Sync

The Debug Sync facility provides synchronization points for debugging, see MySQL Internals: Test Synchronization.

New Status Variable

The Innodb_have_atomic_builtins status variable provides information about availability of atomic instructions; see Scalability Improvements.

Enhanced Solaris Support

MySQL 5.5 incorporates several modifications for improved operation of MySQL Server on Solaris:

DTrace support for execution monitoring. See Diagnostic and Monitoring Capabilities.
Atomic instructions, which are needed for the improvements to RW-locking described in Scalability Improvements. Atomic instructions now are supported for Sun Studio on SPARC and x86 platforms. This extends their previous availability (supported for gcc 4.1 and up on all platforms).
The SMP improvements described in Scalability Improvements, were originally intended for x86 platforms. In MySQL 5.5, these also work on SPARC platforms. Also, Solaris optimizations have been implemented.
Large page support is enhanced for recent SPARC platforms. Standard use of large pages in MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages” feature enables uses of pages up to 256MB. This feature can be enabled or disabled by using the --super-large-pages or --skip-super-large-pages option.
Inline handling for InnoDB and processor instruction prefetching support, previously not enabled for builds created using Sun Studio, now are supported for that build environment.

PREV HOME UP NEXT

User Comments