In addition to the recently announced 5.7.5 DMR, I am happy to draw your attention to some of our work-in-progress. This work has not yet reached production quality, but we want to share it with you in the form of four Labs releases being made available for MySQL Central @ OpenWorld 2014.
New Data Dictionary
We are excited to demonstrate the new Data Dictionary! With this labs release you will be able to run MySQL with the new Data Dictionary in action. MySQL meta-data has been consolidated in a common database schema and stored within transactional, crash-safe, InnoDB tables. Information Schema is also now implemented as standard SQL VIEWs on these tables, which will greatly improve the overall performance of Information Schema. MySQL can also now bootstrap itself without the need for any external scripts (mysql_install_db, etc.). This is the first milestone for us on the new Data Dictionary project: No more FRM files, improved Information Schema performance, and no more scripts required to start the database! Enjoy, and please stay tuned for even more work on this new project!
For some additional background information, see Morgan Tocker’s blog post “Beyond the FRM: ideas for a native MySQL Data Dictionary“.
MySQL Optimizer/InnoDB/Replication Preview
Optimizer: Computed Virtual Columns
We showcase Virtual Columns. Virtual Columns can be be used in several ways. The non-stored virtual columns can be used as a way to simplify and unify queries. Stored (or persisted) Virtual Columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly. Another use for stored Virtual Columns is as a substitute for true functional indexes (stay tuned on that topic!); the user would create a stored Virtual Column using the required functional expression, and then define a secondary index on it.
Virtual Columns (WL#411) are based on a community contribution by Andrey Zhakov. Thank you, Andrey!
Optimizer: Query Rewrite Plugins
We showcase our Query Rewrite Plugin, as well as a framework (APIs) for people to write their own query rewrite plugins. A query rewrite plugin specifies how certain queries that arrive at the server should be rewritten before they are processed and executed by the server. One usage is to enforce/prevent a certain query plan by adding hints to a query. Our query rewrite plugin is a superb tool for handling problematic queries when users cannot rewrite the query within the application itself, e.g. because it originates from an external tool (like an Object Relational Mapping tool such as Hibernate) or from some other 3rd party application.
InnoDB: Memcached
We showcase support for multiple get and range searches using the Memcached protocol. InnoDB Memcached’s read-only queries in 5.7 already achieved a remarkable 1.1 million QPS record. Now, the read query bottleneck shifts to the memcached client itself. Thus anything that can batch the queries helps, and this is what multiple get gives you. In addition, “range searches” extend the Memcached protocol to allow users to fetch multiple keys within a range (e.g. get @<100), this gives you additional benefits that standard memcached isn’t able to provide.
InnoDB: Transparent Page Compression
We showcase transparent page level compression within the IO layer. The benefit of this approach over the historical row level compression is that it is faster, along with being much simpler. The idea comes from FusionIO, but the implementation is not limited to their NVMFS implemention (DirectFS). Customers want to reduce their costs by simultaneously compressing data in order to make better use of the available space (fewer devices are needed), while also reducing device write cycles in order to increase the lifespan of the devices. See also Sunny Bains’ blog post “InnoDB Transparent PageIO Compression“.
InnoDB: Support Page 32k and 64k Page Sizes
We showcase support for 32k and 64k page sizes (the default remains 16k). Bigger page sizes will help to improve compression ratios (the bigger the page size, the more redundant bits), thus offering much improved data compression in conjunction with the new transparent page compression. Furthermore, bigger page sizes allow more “on page” or “inline” storage of BLOBs and large VARCHAR/TEXT fields, thus improving I/O performance when such fields are used.
InnoDB: Native Partitioning
We showcase InnoDB native partitioning. Native partitioning paves the way for better overall partitioning. That would include things such as parallel query processing, foreign key support, and full-text searches on partitioned tables.
InnoDB: General Tablespaces
We showcase CREATE TABLESPACE for general use. This will give the user freedom to choose the mapping between tables and tablespaces, i.e. which tablespaces to create and what tables they should contain. This allows for doing things such as grouping all tables for a given user or customer within a single tablespace, and thus having all of their data within a single file on the filesystem.
InnoDB: High Priority Transactions
We showcase high priority transactions within InnoDB, i.e. transactions that shall never be chosen to be aborted in a deadlock scenario. The motivation is to better support MySQL Group Replication, where a transaction cannot abort in one replica and be committed in another.
Replication: Multi-Source Replication
We showcase a refreshed preview of our Multi-Source Replication implementation. It allows a slave to replicate from multiple sources/masters, but without any additional conflict detection and resolution. One use case is to commission a “fan in slave” for data aggregation or backup. This version takes into consideration the great feedback we got from the community when we first released this on labs some time ago.
Replication: Improved Multi-Threaded Slave
We showcase an improved Multi Threaded Slave (MTS) implementation, using a more precise algorithm to determine which transactions are non-conflicting. This allows more transactions to execute in parallel, thus improving the overall slave performance.
MySQL Group Replication
All servers in the group act as peers and an application can safely send writes to any one of them. Coping with server failure becomes simple as the application can simply switch to another server without the need for database failover. This marks the beginning of official MySQL support for true multi-master replication clusters, offering easy to setup and administer active/active HA solutions.
HTTP Plugin
The HTTP Plugin allows MySQL to listen for HTTP requests and to communicate in JSON. This early development version features three APIs: SQL, CRUD, and DOCUMENT. The SQL endpoint exists for rich query capabilities, whereas the CRUD and DOCUMENT endpoints follow key-document store semantics. Nested JSON objects can be managed through the DOCUMENT API.