The MySQL 5.7.2 Milestone Release_MySQL

Aspromised, here is the belated post covering the MySQL 5.7.2 Milestone Release, released on September 21, 2013. You can find the full list of changes and bug fixes in the5.7.2 Release Notes. Enjoy!

InnoDB Read-Only Scalability

One of the goals of 5.7 is to improve performance for Read-Only (RO) and Read-Mostly workloads. In 5.7.2 Sunny Bains made several improvements (see below) and we reached 500K QPS performance levels in the OLTP_RO Point-Selects 8-tables benchmark.  See also Dimitri Kravtchuk’sblog post.

Do Not Allocate trx id for Read-Only Transactions(WL#6047) — This work by Sunny Bains speeds up READ ONLY transactions under heavy load. InnoDB now auto-detects READ ONLY transactions, and thus removes the need to be running in autocommit mode or using START TRANSACTION READ ONLY to benefit from this optimization.

Reduce lock_sys_t::mutex Contention When Converting Implicit Locks to Explicit Locks (WL#6899) — This work by Sunny Bains significantly increases scalability for RO transactions in 5.7 by avoiding scanning the trx_sys_t::rw_trx_list for trx_id_t.

Improve Locality of Reference by Allocating trx_t in Blocks(WL#6906) — This work by Sunny Bains allocates memory for trx_t instances in configurable sized blocks that are a multiple of sizeof(trx_t). This reduces the cost when iterating over the transactions. This work contributes to the impressive scalability of RO transactions in 5.7.

Optimize Read View Creation(WL#6578) — This work by Sunny Bains reduces the cost of read view creation. The multi-version concurrency control (MVCC) in InnoDB requires that each MVCC-using transaction be assigned a read view. The read view is created by traversing the trx_sys->rw_trx_list, which is a linked list of active read-write transactions. This change is required to improve InnoDB performance both for RO and RW workloads.

InnoDB Read-Write Scalability

One of the goals of 5.7 is to also improve the performance of Read-Write (RW) workloads. In 5.7.2 Yasufumi Kinoshita removed the index->lock contention (see below).

Implement SX-lock(WL#6363) — This work by Yasufumi Kinoshita introduces a new rw-lock called SX-lock (shared exclusive) for accessing internal InnoDB resources.  This new lock paves the way for additional concurrency and scalability improvements.

Fix index->lock Contention  (WL#6326) — This work by Yasufumi Kinoshita removes index->lock contention and improves scalability of RW workloads. The index->lock that was used to protect the entire index tree structure is now replaced by more fine grained block->locks in the tree.

InnoDB Faster Flushing

Optimize Buffer Pool List Scans and Related Batch Processing Code(WL#7047) — This work by Inaam Rana reduces the number of pages scanned when doing flush list batches, speeding up page flushing. The time complexity of a scan is reduced from O(n*n) to O(n).

InnoDB Temporary Table Performance

One of the goals of 5.7 is to optimize InnoDB temp tables for better performance.  With the following work done in 5.7.2, it is about 11x faster for CREATE/DROP temp table statements and about 2-4x faster for INSERT/DELETE/UPDATE on temp tables, as compared with 5.6.

Optimize DML for Temp Tables(WL#6470) — This work by Krunal Bauskar improves InnoDB temp table DML performance (INSERT/UPDATE/DELETE). This is achieved by removing unnecessary UNDO and REDO logging, change buffering, and locks.

UNDO Logs for Temp Tables Should Reside in a Separate Temp Tablespace(WL#6915) — This work by Krunal Bauskar adds an additional type of UNDO log, one that is not REDO logged and resides in a new separate temp tablespace. These non-redo-logged UNDO logs are not required during recovery and are only used for rollback operations.

Speeding up Connection Handling

In some application scenarios (e.g. PHP applications) client connections have very short life spans, perhaps only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance. In 5.7.2 (WL#6606) we improved the number of connect/disconnect cycles handled per second from 21K to 35K (+63%), as measured by using mysql-bench with 25 concurrent client threads executing a loop of connect/disconnect cycles for 100,000 iterations each.

Offload THD Initialization and Network Initialization to Worker Thread(WL#6606) — This work by Thayumanavar Sachithanantha is maximizing the number of connections that can be handled per unit of time. Initialization of a THD and its vio/net previously happened in the acceptor thread that accepted the connection. THD and network initialization involves acquiring locks, memory allocation of various structures, and system calls which are compute-bound, as well as tasks that may block. The acceptor thread is now basically an event loop that waits for new connection events from clients. To maximize the number of connections that can be handled per unit of time, the acceptor thread now spends as much of its time listening for new connections, and the THD initialization is offloaded from the acceptor thread and delegated to worker threads that then handle the initialization and resulting client connections. See also Jon Olav Hauglid’sblog post.

Update_time For InnoDB Tables

Implement Update_time for InnoDB Tables(WL#6658) — This work by Vasil Dimov implements in-memory maintenance of update_time for InnoDB tables. For now, this work has the limitation that the values will be lost when the server is restarted or the table evicted from the InnoDB data dictionary cache. We would like to make update_time persistent as follow-up work, but the timeline for this has not been decided. The column INFORMATION_SCHEMA.TABLES.update_time will now contain the timestamps of the last update (or insert or delete). This functionality was previously missing in InnoDB and people have been asking for it, seeBug#2681reported by Phil Sladen.

InnoDB TRUNCATE TABLE Statement Becomes Atomic

Make Internal InnoDB TRUNCATE TABLE Statement Atomic for Single Table Tablespaces (WL#6501) — This work by Krunal Bauskar makes the internal InnoDB TRUNCATE TABLE statement atomic by reinitializing the original tablespace header with the same space id and then physically truncating its .ibd file during the truncation of a single table tablespace.

InnoDB Buffer Pool Dump and Load Enhancements

InnoDB Buffer Pool Dump and Load Enhancements(WL#6504) — This work by Vasil Dimov improves both dump and load scenarios. It is now possible to dump only the hottest N% of the pages from each buffer pool. This is controlled by a new variableinnodb_buffer_pool_dump_pct, an integer from 1 to 100 with a default value of 100 (dump everything). The load operation is also made less disruptive to user payload, because the load now happens in the background while serving clients; while also attempting not to be too aggressive and keeping too much IO capacity away from servicing new clients requests.

Innochecksum Tool

Improve Innochecksum(WL#6045) — This work by Anil Toshniwal significantly extends the innochecksum utility’s functionality. It is now possible to specify the checksum algorithm (innodb/crc32/none), rewrite the current checksum using the specified algorithm, rewrite the checksum even if the current checksum is invalid, and specify the maximum checksum mismatch allowed before terminating the program. Innochecksum can also now operate on multiple tablespace files and on multiple files in the same tablespace.

MySQL Client

Client Side Protocol Tracing(WL#6226) — This work by Rafal Somla creates hooks inside the client library code which allows tracing of protocol events such as sending packets to the server, receiving server replies, and authentication handshakes. This provides a mechanism for collecting performance data about the client-server connections, from the clients perspective.

Error Reporting

Most Statements Should Clear the Diagnostic Area(WL#5928) — This work by Tatjana Nurnberg makes MySQL follow the SQL standard with respect to clearing the diagnostic area. This means that 1) We clear the diagnostic area for non-diagnostic statements that do not use any tables 2) Clearing the diagnostic area is done at a single well-defined point during statement execution. This work fixes bugs likeBug#35296,Bug#43012, andBug#49634.

Error Logging

Allow Control of Verbosity(WL#6661) — This work by Tatjana Nurnberg gives the DBA control of how “verbose” the MySQL Server should be (error/warning/note) when writing to the error log. This work also changes the timestamp format printed to a more standard format (syslog) and converts the existing fprintf(stderr, …) in the server layer to use the new internal error logging API.

Triggers

Multiple Triggers Per Table(WL#3253) — This work by Dimitry Shulga provides the ability to have more than one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). This is in line with the SQL standard. See also Dimitry Shulga’sblog post.

Semi-sync Replication

Externalize Transactions Only after ACK is Received(WL#6355) — This work by Libing Song allows for true lossless failovers when using semi-sync replication. If the master crashes, the slave is still ensured to be up to date. The implementation makes the master wait for the ACK after preparing within the storage engine and writing to the binary log, but before committing to the storage engine. See also Libing Song’sblog post.

Multi-threaded Slaves (MTS)

Intra-schema Parallel Slave Execution(WL#6314) — This work by Rohit Kalhans implements intra-schema multi-threaded slaves. With this implementation the slave will be able to apply transactions in parallel, even within a single database or schema, as long as they have a disjoint read and write set. See also Rohit’sblog post.

Performance Schema

Instrument Memory Usage(WL#3249) — This work by Marc Alff instruments memory usage in the server layer. Instrumentation is added for more than 200 memory types. Memory usage statistics are aggregated by type of memory used (caches, internal buffers, etc.) and by the thread/account/user/host indirectly performing the memory operation. Attributes include memory used (bytes), operations counts, and high/low water marks.

Stored Program Instrumentation(WL#5766) — This work by Mayank Prasad extends the statement instrumentation to cover Stored Programs which completes the picture with COM Commands and SQL Queries done in 5.6. New instruments are added for “statement/sp” cclose/cfetch/copen and for “statement/scheduler” event. The new object types are EVENT, FUNCTION, PROCEDURE, and TRIGGER. A new summary table “events_statements_summary_by_program” has been added.

Performance Schema table for SHOW SLAVE STATUS(WL#3656) — This work by Shivji Jha implements Performance Schema tables for the information returned by SHOW SLAVE STATUS. This approach has the following benefits: 1) The information on slave status can be searched with ad hoc queries 2) Pieces of information can be assigned to variables, and thus used in stored procedures 3) We can get rid of a few old fields to present only relevant information and avoid exposing too many internals 4) We can structure the information better, to align it with multi-source replication and multi-threaded slaves. See alsoBug#50316,Bug#56414, andBug#35994.

Security

Convert mysql_secure_installation Script to C(WL#6441) — This work by Vamsikrishna Bhagi converts the script to a C++ program, so that the program can connect to the server directly and execute the specified commands using the C API (libmysql). This removes the need for storing the user supplied password in a temporary option file.

Make Plugin Column in mysql.user Non-NULL(WL#6982) — This work by Vamsikrishna Bhagi ensures that the same rules apply to the native authentication methods as to the other authentication methods. In 5.5 we introduced a backward compatible layer to handle pre-4.1 password hashing. The pre-4.1 password hash has since been deprecated in 5.6, and this work simplifies the authentication code by removing special cases.

Optimizer

Explain For Other Thread(WL#6369) — This work by Evgeny Potemkin implements EXPLAIN for running queries. For example, if you are running a statement in one session that is taking a long time to complete, using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay and help you optimize the statement. connection_id is the connection identifier, as obtained from the INFORMATION_SCHEMA PROCESSLIST table or the SHOW PROCESSLIST statement. If you have the PROCESS privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections. (Syntax: EXPLAIN [options] FOR CONNECTION connection_id;).

Print Costs and Amount of Data Processed in JSON based EXPLAIN(WL#6510) — This work by Evgeny Potemkin enhances the JSON EXPLAIN output by printing the total query cost, the cost per table, and the amount of data processed. This will make it easier for a user to see the difference between good and bad plans.

Refactoring

InnoDB: Policy-based Mutex(WL#6044) — This work by Sunny Bains refactors the InnoDB mutex implementation and makes it easy to add new policies and to customize mutex usage.

InnoDB: Use DBUG_PRINT for Tracing(WL#6947) — This work by Marko Mäkelä replaces compilation time flags with DBUG labels. This enables the use of the DBUG facility within InnoDB, so that the printouts from various InnoDB subsystems can be enabled by the –debug command line parameter.

InnoDB: Get Rid of the IB_OFFSETOF() Macro and Allow Non-POD Types in UT_LIST_*(WL#6456) — This work by Sunny Bains is pure internal code refactoring, adapting related InnoDB code to C++.

New Source File Structure for Authentication and Authorization Code(WL#6960) — This work by Harin Vadodaria simplifies existing code maintenance by splitting the large sql_acl.cc file into smaller files, grouped by their logical operations. No functional changes are introduced.

Replication: Refactor the Replication Dump Thread(WL#5721) — This work by Libing Song simplifies the design of the dump thread. The dump thread now only holds a lock for a very short time, while reading the position up to which the binary log has been written.

Optimizer: Improve Building of Classes That Represent DML Statements(WL#4772) — This work by Roy Lyseng refactors objects in the Item class hierarchy. DML statements (SELECT/UPDATE/DELETE/INSERT) are represented by objects from the Item class hierarchy and by st_select_lex and st_select_lex_unit objects. This WL fixes a problem regarding how these object hierarchies were built.

Optimizer: Move LooseScan Out of best_access_path()(WL#6739) — This work by Guilhem Bichot removes the cost estimation for LooseScan from best_access_path() and calls it only when we know we have a potential LooseScan for the first table. The benefit of this is a clean separation of concerns, a lighter best_access_path() implementation (less code and less structures on its stack, which is important as this function is called frequently), and code that’s easier to read and maintain.

Test Suite Improvements

Additional Concurrency Tests for BGC(WL#6969) — This work by Daogang Qu adds concurrency tests to verify that there are no deadlocks in group commit, non-group commit, and semi-sync areas.

Migrate Perfschema MTR Test Suite to Run with the InnoDB Storage Engine(WL#6733) — This work by Akhila Maddukuri adapts the performance schema test suite to be run with InnoDB as the default storage engine.

Migrate Main Test Suite to Run with the InnoDB Storage Engine(WL#6934) — This work by Vinay Fisrekar makes it possible to suppress result differences due to engine value alone, thus allowing us to run with a default engine of InnoDB or MyISAM.

Migrate funcs_2 Suite to Run with InnoDB(WL#6983) — This work by Viswanatham Gudipati makes all the testcases in the suite “funcs_2″ run with InnoDB engine.

Migrate jp,large_tests and Stress Suite Run with InnoDB(WL#6992) — This work by Viswanatham Gudipati makes all the testcases in the suites “jp”, “large_test”, and “stress” run with InnoDB engine.

Migrate funcs_1 Suite Run with the InnoDB Engine(WL#6924) — This work by Matthias Leich makes the “funcs_1″ suite run with InnoDB engine instead of the MyISAM engine in the cases where MyISAM specific properties are not the focus of the test.

Migrate opt_trace Suite to Run with InnoDB(WL#6925) — This work by Viswanatham Gudipati migrates the “opt_trace” suite to run with the InnoDB engine.

Fix ctype_* Tests to Run with a Default Engine of InnoDB(WL#7122) — This work by Vinay Fisrekar migrates tests which can be fixed easily as they have a result difference and/or warning which are expected (and can be suppressed by adding commands) due to engine differences.

Migrate Federated Test Suite to Run with InnoDB(WL#7112) — This work by Akhila Maddukuri migrates the tests in the “federated” suite to run with InnoDB.

For the migration of MTR test suites to InnoDB, see also Anitha’sblog post.

Deprecation & Removal

Deprecate ENCODE()/DECODE() in 5.7(WL#6984) — This work by Georgi Kodinov triggers a warning (ER_WARN_DEPRECATED_SYNTAX) upon the execution of the ENCODE() or DECODE() functions.

Remove Unused –basedir and –datadir Options from mysql_upgrade(WL#7010) — This work by Georgi Kodinov removes these unused options.

Deprecate Unique Option Prefixes(WL#6978) — This work by Georgi Kodinov deprecates unique option prefixes and only accepts full option names. The old behavior has been found to be confusing and non-future proof. For example, the--compressoption could be given to mysqldump or--compr, but not as--compbecause the latter was ambiguous.

Deprecate the INFORMATION_SCHEMA.PROFILING Table(WL#6802) — This work by Georgi Kodinov completes the deprecation of PROFILING. In 5.6 we deprecated SHOW PROFILE in favor of the Performance Schema instrumentation. See alsoBug#67525, reported by Peter Laursen.