This section summarizes what has been added to, deprecated in, and removed from 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.3, “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.4.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.usertable. 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.2.10, “Pluggable Authentication”, and Section 6.2.11, “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 operating system 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 188.8.131.52, “PAM Pluggable Authentication”, and Section 184.108.40.206, “Windows Pluggable Authentication”.
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
MyISAM. See Section 14.1, “Introduction to InnoDB”.
InnoDB storage engine. MySQL 5.5 includes several
InnoDBstorage 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”.
VARCHARcolumns can be stored fully off page. See Section 14.14, “InnoDB Row Formats”.
File format management enhancements protect upward and downward compatibility. See Section 14.13, “InnoDB File-Format Management”.
INFORMATION_SCHEMAtables provide information about
InnoDBcompression and locking. See Section 14.18, “InnoDB INFORMATION_SCHEMA Tables”.
InnoDBperformance and scalability enhancements:
InnoDBmutex and read/write lock implementation was improved. Use of Pthreads mutexes was replaced with calls to GCC - atomic builtins.
The memory allocator used by
InnoDBis configurable. See Section 14.11.3, “Configuring the Memory Allocator for InnoDB”.
The extent to which
InnoDBperforms change buffering is configurable. See Configuring Change Buffering.
The adaptive hash index (AHI) feature makes
InnoDBperform 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.8.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.11.4, “Configuring Thread Concurrency for InnoDB”.
InnoDBperforms buffer pool read-ahead is configurable. See Section 220.127.116.11, “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.11.5, “Configuring the Number of Background InnoDB I/O Threads”.
Asynchronous I/O is supported on Linux systems. See
The overall I/O capacity available to
InnoDBis configurable. See Section 14.11.7, “Configuring the InnoDB Master Thread I/O Rate”.
InnoDBperforms buffer pool flushing is configurable. Section 18.104.22.168, “Configuring InnoDB Buffer Pool Flushing”.
The maximum delay between checking the availability of a mutex or rw-lock is configurable. See Section 14.11.8, “Configuring Spin Lock Polling”.
InnoDBcan be configured to minimize the amount of data brought into the buffer pool and never accessed again. See Section 22.214.171.124, “Making the Buffer Pool Scan Resistant”.
Crash recovery performance was improved. See Section 8.5.8, “Optimizing InnoDB Configuration Variables”.
InnoDBoperations 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 126.96.36.199, “Configuring Multiple Buffer Pool Instances”.
The limit on concurrent read-write transactions was increased. See Section 14.9.7, “Undo Logs”.
InnoDBcan be configured to have purge operations performed by a separate thread, rather than by the master thread. See Section 14.11.9, “Configuring InnoDB Purge Scheduling”.
As of MySQL 5.5.62, the zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.
If you use
InnoDBcompressed tables, see Section 2.11.3, “Changes in MySQL 5.5” for related upgrade implications.
InnoDBflexibility, ease of use, and reliability enhancements:
Operating system disk space can be reclaimed when truncating an
InnoDBtable. See Section 14.15.5, “Reclaiming Disk Space with TRUNCATE TABLE”.
InnoDBcan be run in strict mode. See the
InnoDBprovides greater control over the quality of optimizer statistics estimates. See Section 14.11.10, “Configuring Optimizer Statistics for InnoDB”.
SHOW ENGINE INNODB MUTEXoutput is more compact. See Section 188.8.131.52, “SHOW ENGINE Syntax”.
SHOW ENGINE INNODB STATUSoutput displays counter information for the
Innodb_buffer_pool_read_ahead_evictedglobal status variables, which you can use to fine-tune the
innodb_random_read_aheadsetting 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 STATUSoutput, 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 NDB Cluster. MySQL NDB Cluster is released as a separate product, with version 7.2 of the
NDBstorage engine being based on MySQL 5.5. Clustering support is not available in mainline MySQL Server 5.5 releases. For more information about MySQL NDB Cluster 7.2, see Chapter 18, MySQL NDB Cluster 7.2.
NDB Cluster releases are identified by a 3-part NDB version number. NDB Cluster 7.5, now available as a General Availability (GA) release beginning with version 7.5.4, incorporates version 7.5 of the
NDBstorage engine. Previous GA releases still available for production, NDB Cluster 7.3 and NDB Cluster 7.4, incorporate
NDBversions 7.3 and 7.4, respectively. For information about NDB Cluster 7.5, see MySQL NDB Cluster 7.5 and NDB Cluster 7.6. For more information about NDB Cluster 7.4 and NDB Cluster 7.3, see MySQL NDB Cluster 7.3 and NDB Cluster 7.4.
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:
utf8mb4. See Section 10.9, “Unicode Support”.
Partitioning. Enhancements to table partitioning:
Two new types of user-defined partitioning are supported:
RANGE COLUMNSpartitioning is an extension to
LIST COLUMNSpartitioning is an extension to
LISTpartitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:
You can also define ranges or lists based on multiple column values when partitioning tables by
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
WHEREconditions that use multiple comparisons between (different) column values and constants, such as
a = 10 AND b > 5or
a < "2005-11-25" AND b = 10 AND c = 50.
It is now possible to delete all rows from one or more partitions of a partitioned table using the
ALTER TABLE ... TRUNCATE PARTITIONstatement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the
TRUNCATE PARTITIONclause do not have to be contiguous.
Key caches are now supported for indexes on partitioned
MyISAMtables, using the
LOAD INDEX INTO CACHEstatements. 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.
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.
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.11, “IPv6 Support”.
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.
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.
For applications that use features deprecated in MySQL 5.5 that have been removed in a higher MySQL series, statements may fail when replicated from a MySQL 5.5 master to a higher-series slave, or may have different effects on master and slave. To avoid such problems, applications that use features deprecated in 5.5 should be revised to avoid them and use alternatives when possible.
Relying on implicit
GROUP BYsorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit
GROUP BYsorting 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.
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)”.
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.
timed_mutexessystem variable. It does nothing and has no effect.
Use of the data directory as the location for
The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.
For MySQL 5.1 applications that use features removed in MySQL 5.5, statements may fail when replicated from a MySQL 5.1 master to a MySQL 5.5 slave, or may have different effects on master and slave. To avoid such problems, applications that use features removed in MySQL 5.5 should be revised to avoid them and use alternatives when possible.
log_bin_trust_routine_creatorssystem variable (use
record_buffersystem variable (use
table_typesystem variable (use
FRAC_SECONDmodifier for the
SHOW TABLE TYPESSQL statement (use
SHOW PLUGINSQL statement (use
TIMESTAMP(data type: The ability to specify a display width of
--default-table-typeserver option (use
--delay-key-write-for-all-tablesserver option (set the
delay_key_writesystem variable to
--log-bin-trust-routine-creatorsserver option (use
--master-server options to set replication parameters (use the
CHANGE MASTER TOstatement instead):
--warningsserver option (use
--no-named-commandsoption for mysql (use
--no-pageroption for mysql (use
--no-teeoption for mysql (use
--positionoption for mysqlbinlog (use
--alloption for mysqldump (use
--first-slaveoption for mysqldump (use
--config-fileoption for mysqld_multi (use
-Ogeneral-purpose options for setting program variables (use
--with-pstackoption for configure and the
--enable-pstackoption for mysqld.
MySQL 5.5 modifications improve performance on SMP
systems to increase scalability on multi-core systems. The
InnoDB locking and
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
allocation to be disabled and replaced by the normal
malloc library, or by a different library
malloc such as
tcmalloc on Linux or
mtalloc on Solaris.
The reimplementation of RW-locks requires atomic instructions. A
shows whether the server was built with atomic instructions.
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:
the number of threads to use for read prefetch requests.
controls the number of threads to use for writing dirty pages
from the buffer cache to disk. The default for both variables is
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
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
MySQL 5.5 exposes this I/O rate parameter as a
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
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.
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 is a feature for monitoring MySQL Server execution at a low level. See Chapter 22, MySQL Performance Schema.
The DTrace probes work on Solaris, OS X, and FreeBSD. For information on using DTrace in MySQL, see Section 5.8, “Tracing mysqld Using DTrace”.
SHOW ENGINE INNODB
The output from
SHOW ENGINE INNODB
STATUS includes more information due to changes made
InnoDB Plugin. A description of revisions
to statement output follows.
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
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
The Debug Sync facility provides synchronization points for debugging, see MySQL Internals: Test Synchronization.
New Status Variable
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
Inline handling for
InnoDBand processor instruction prefetching support, previously not enabled for builds created using Sun Studio, now are supported for that build environment.