Great news was announced today at the Collaborate 11 conference
today; the first development milestone release of MySQL
5.6.2 is now available to the MySQL Community! This is
significant because 5.6.2 builds on the momentum generated by the
performance, scalability, InnoDB and Replication improvements
that rolled out in version 5.5 just last Dec. It also marks
somewhat of a paradigm shift in how the MySQL Engineering team
delivers new, defined sets of stable, development complete
features (development "milestones") and cutting edge, currently
under development, delivered early and often features via
http://labs.mysql.com/. Both options provide a means for
Community users to begin using new versions and, more
importantly, new features of MySQL very early on ensuring the
final product has undergone the highest degree of testing,
validation and hardening before we deem it production
ready.
Some of the top features found in the 5.6.2 development milestone
include:
Better Performance - Improved Optimizer
The MySQL Optimizer team has done a great job implementing
improvements that make query execution more efficient in
5.6.2. These include:
Index Condition Pushdown (ICP) - ICP enables MySQL to push
indexed part of WHERE condition down to the storage engine
layer. This reduces the number of accesses the storage
engine has to do against the base table(s) being queries and the
number of accesses the MySQL server has to do against the storage
engine.
Multi-Range Read (MRR) - MRR enables data rows to be accessed
sequentially rather than in random order, based on index tuples.
MRR also enables batch processing of requests for key access for
operations that require access to data rows through index tuples,
such as range index scans and equi-joins that use an index for
the join attribute.
File sort optimization - Read intensive operations often have to
do "SELECT ... ORDER BY non_index_column LIMIT X". This feature
speeds up the sort when X * <Row Size> is smaller than
sort_buff_size.
An even Better InnoDB
Calvin Sun and the InnoDB team have been doing some amazing
things with InnoDB in 5.6.2. A quick summary:
Persistent Optimizer Stats - Provides consistency and accuracy of
InnoDB statistics, specifically during re-starts of the MySQL
server. Improves startup and recovery times as optimizer
stats and access paths for InnoDB indexes and table data do not
need to be re-calculated, re-loaded.
New InnoDB specific INFORMATION_SCHEMA TABLES
• Metrics table - Provides a generic and more
comprehensive resource and performance monitoring framework for
InnoDB.
• System Tables - Expose InnoDB internal data
dictionary for data file recovery and other new features (e.g.
transportable tablespaces).
• Buffer Pool Information table - Captures,
displays buffer pool page information for DBA and
developers.
Multi-threaded Purge - InnoDB now has multiple purge threads and
separate flush thread which make purge and flushing operations
more efficient/effective.
As an added bonus, these are also available in Labs.mysql.com
InnoDB now provides "NotonlySQL" access to your InnoDB data via
new New Memcached API to InnoDB tables/data
Benefit: Provides NoSQL option for direct access to InnoDB table
data. You can read all about it in Calvin's InnoDB blog.
And you can grab the current development binary/source by:
Go to http://labs.mysql.com/
Select "mysql-5.6-labs-innodb-memcached" from drop down
menu
Download:
Binary: mysql-5.6-labs-innodb-memcached-linux-x86_64.tar.gz
Source: mysql-5.6-labs-innodb-memcached.tar.gz
Better Replication
Lars Thalmann, Mats Kindahl and the entire Replication team have
listened to users and customers and implemented some of the most
enabling features to date, some are listed here, more are still
in development available on Labs:
Crash-Safe Slaves - Extends the robustness and ease-of-use of
MySQL replication by making the slaves crash-safe when using
transactional storage engines such as InnoDB. The slave can
automatically recover from a failure and resume replicating DML
updates, without the DBA having to access the master.info and
relaylog.info files to manually roll back replication to the last
successfully committed transaction, or to skip
transactions. As a result, data integrity is enhanced and
DBAs can be free to concentrate on more strategic data management
activities.
Replication Event Checksums - Ensures the integrity of data being
replicated to a slave by detecting data corruption and returning
an error, preventing the slave itself from becoming
corrupt. Checksums are implemented in the binary and relay
logs as well as to individual replication events, allowing errors
to be detected whether they are caused by memory, disk or network
failures, or by the database itself. Checksum checking can
be implemented on a per-slave basis, giving maximum flexibility
in how and where it is deployed.
Optimized Row-Based Replication - By only replicating partial
'before' and 'after' images for INSERT, UPDATE and DELETE events
where primary keys and/or explicit columns were set in the SQL
statement, performance can be increased while binary log disk
space, network resources and server memory footprint are
reduced.
Time-Delayed Replication - The user can define a time delay for
events to be replicated from a master to each slave, defined in
millisecond increments up to a maximum of 68 years! Time-Delayed
Replication affords protection against operational errors made on
the master, for example accidently dropping tables, in which
event the slave can be promoted to the new master in order to
restore the database to its previous state. Time-Delayed
Replication can also be useful in testing application behavior by
emulating any instances of replication lag.
Time-Delayed Replication is implemented at the per-slave level
(via holding execution of the SQL_THREAD), so a user could
configure multiple slaves to apply replication events
immediately, and another slave to apply only after a delay of 5
minutes, therefore providing deployment flexibility.
Informational Log Events - Enhances auditing and debugging when
using Row-Based Replication by writing the original query to the
binary log, which is then replicated with its associated
row-based event to the slave.
Remote Binlog Back-up - Enhances operational efficiency by using
the replication channel to create real-time back-ups from the
binary log. By adding a "raw" flag, the binlog is written
out to remote back-up servers, without having a MySQL database
instance translating it into SQL statements, and without the DBA
needing SSH access to each master server.
Server UUIDs - Enhances auto-discovery and analysis of
replication topologies when using the MySQL Enterprise Monitor by
automatically generating a Universally Unique Identifier (UUID)
for each server. This is especially useful in large and highly
dynamic replication environments, making auto-discovery more
reliable and simplifying systems management.
And these replication enhancements are now available in Labs.mysql.com
Multi-Threaded Slaves - Replication performance is improved by
using multiple execution threads to apply replication events to
slave servers. The multi-threaded slave splits work between
worker threads based on the database name, allowing updates to be
applied in parallel rather than sequentially. As a result,
replication throughput is increased and latency is reduced which
minimizes the risk of replication lag, enabling slaves to serve
the freshest updates to the application.
The multi-threaded slave functionality is available now as part
of the snapshot-next-mr-wl5563-labs build which can be downloaded
from labs.mysql.com. Keep your eye on drop down list on
this page as we are working to add menu items and files names
that will simplify identifying and downloading binaries and files
for specific features (like we did with InnoDB + Memcached API
noted above).
Better Instrumentation via Improved PERFORMANCE_SCHEMA
The PERFORMANCE_SCHEMA that was introduced in 5.5 has been
improved in 5.6.2 to provide instrumentation and metrics
on:
Table I/O - Enables DBA to understand/analyze and tune how an
application generates table access related I/O load on the MySQL
database server.
Table Locks - Enables a DBA to identify "hot tables" and other
object-related bottlenecks caused by the data access patterns of
an application.
Session/User Level Load stats -Enables DBA to monitor the load
generated from a specific user or application by selectively
instrumenting specific end user/application connections. This is
valuable to narrow down the monitoring data collected: a) by
thread or session, b) by connections originating from a given
user account, c) by connections originating from a given machine,
d) by connections originating from a given user account on a
given machine. Added bonus: Reducing the amount of
instrumented connections also reduces the runtime overhead of the
instrumentation in production.
Global Performance Metric Summaries - Globally aggregates the
data collected by the PERFORMANCE_SCHEMA by thread / by object /
by instrumentation point. Data is stored in a relational
table and accessible via queries. Enables DBA monitoring
scripts and applications.
Table/Index I/O Summary - Aggregates Table I/O data collected by
the PERFORMANCE_SCHEMA, by index, by table. Aggregation shows
which tables / index are "hot" and most used by an application,
and helps to identify the application data access
patterns. Data is stored in a relational table and
accessible via queries.
Table Lock Wait Summary - Aggregates Table lock data collected by
the PERFORMANCE_SCHEMA by table. This aggregation shows
which "hot" tables are often locked by an application, and helps
to identify the application bottlenecks caused by table
locking. Data is stored in a relational table and
accessible via queries.
You can learn all about what's new in the PERFORMANCE_SCHEMA in
Mark
Leith's excellent blog.
Better Partitioning
Partitioning now provides options for:
Explicit partition selection - which enables users to only
name/use specific sets of partitions, i.e. in queries, explicit
pruning. This speeds up query performance by automatically
pruning non-relevant partitions from data lookup
operations.
Keep you eye for future 5.6.x development milestone releases, as
each will introduce more new, development complete features that
are ready for your testing, development, extensions,
feedback. Also, watch http://labs.mysql.com for features
that still under development, but ready for our "release early,
release often" adopters.
As always, thanks for your continued support of MySQL!
Apr
11
2011