Hello, welcome to the webinar, What's New in MySQL 5.7. This is Geir Høydalsvik speaking. You can ask questions during the presentation, but there will also be a Q&A session at the end of the presentation. First, safe harbor statement. The following is intended to outline our general product direction. It's intended for information purposes only and may not be incorporated into any contract. Move on to slide number three. We just announced MySQL 5.7 GA a couple of weeks back, and there has been a lot of progress and big investments going on for two, three years in MySQL. The focus areas being performance and scalability and manageability. We are showing a much faster and scalable MySQL. We have enhanced InnoDB on many fronts. We have more online operations, faster load, et cetera. We have a lot of replication improvements, including multi-source, multi-threaded slaves.
We have invested a lot in the optimizer cost model with better user control and better query performance. On the manageability front, we have made a lot of new implementations in the Performance Schema, and we have also introduced the MySQL sys schema. We have improved security, safer initialization, setup, and management, and we have added the native JSON support. On the performance side, we have focused a lot on scalability so that the MySQL database is now able to utilize much more multi-core CPUs. The graphs here shows scaling up to 72 cores, with MySQL 5.7 three times faster than MySQL 5.6 and four times faster than MySQL 5.5, and the record of 1.6 million queries per second. These are SQL point select or primary key lookups from the Sysbench benchmark. We have also improved the connect-disconnect performance.
Connect-disconnect speed can be very important in, for example, PHP environments, where you typically don't have open connections for a long time. You just open a connection, do a query, and close the connection. We here see that MySQL 5.7 can handle about 82% more connects, disconnects per second than 5.6. We can now deal with about 100,000 connect disconnects per second. Similar benchmark for this is called the Mixed OLTP Read Only. This is still the Sysbench benchmark, but with the OLTP read-only mix. Similar graphs as we saw before, three times faster than MySQL 5.6, six times faster than MySQL 5.5. On the read-write front, same benchmark, we also have improvements. We can see that it's 1.5 times faster than MySQL 5.6 and three times faster than MySQL 5.5.
On slide eight, we have done a lot of investments in the parser and the optimizer during the last two, three years. We have started work to rewrite both the parser and the optimizer, and the purpose is to make the parser and optimizer more readable, more maintainable, and more extendable. We want to have a clear separation between parsing, optimizing, and execution stages, and to allow for easier feature additions with lessened risk later on. We want to, for example, introduce things like better prepared statements, parallel queries, and things like that. This is preparatory work for things to come in the future. This is slide number nine, more details on the parser refactoring. We have had historically, a very complex parser, hard to add new syntax. We have now rewritten in an incremental way the whole parser.
We create an internal parse tree bottom up, create an abstract syntax tree from the parse tree and the usage context. Have syntax rules that are more precisely defined and are closer to the SQL standard, more precise error messages, better support for larger syntax rules in the future. We have slide 10, optimizer overview. This kind of illustrates the various elements of the optimizer. On the left-hand side, we have a SQL query coming in, and then we have the optimizer as a box that has kind of data dictionary information available, what tables and what indexes exist on various tables. And then we use a cost model and statistics from the storage engines that says cardinality and how many rows are there for each index and things like that.
This optimizer then utilizes the cost-based optimization and the heuristics to create a plan that is illustrated on the left-hand side. In this case, it, for example, has defined join orders and which access methods to use. On slide 11, we have optimizer improvements in 5.7. I already mentioned the optimizer and parser refactoring earlier. We also added a new hint framework. You can put hints more places than before, easier to manage hints. We also have supported a range of new hints. We have improved JSON EXPLAIN, which now outputs also row estimates and cost estimates. We can do EXPLAIN for a running thread. That can be useful if you have a long-running thread and want to know the optimizer plan for what that thread is doing. We have improved the cost-based optimizer.
Specifically, we have improved it with condition filtering that helps deciding the correct join ordering, and we have improved the accuracy of InnoDB statistics used by the optimizer. Both things will improve performance drastically in certain cases. We have implemented generated columns in 5.7. We have added support for InnoDB-based internal temp tables. This means that the optimizer now uses InnoDB for temp table, even for intermediate internal use temp tables. For example, when the optimizer needs to store an intermediate result for sorting or something. We have implemented the only full GROUP BY mode, which kind of makes the GROUP BY functionality in MySQL SQL compliant, and also much better with tracking real functional dependencies on the various attributes. We have improved support for GIS and InnoDB in this space, and we have also a range of more specific new optimization, for example, on UNION ALL.
Move on to slide 12. This is more on the optimizer cost model. We have added more accurate cost estimates. Better decision by the optimizer will improve query performance in many cases. We are working on adapting the optimizer to new hardware requirements, SSDs, larger memory size, improved caches, et cetera. This means that we want to choose different cost constants depending on whether the table is stored in memory on an SSD or disk, et cetera. We have done work in this direction in 5.7, and we have separated out hard-coded cost constants and refactoring out these things from the old cost model code, and we have now made these constants configurable and tunable. They are fit in normal tables called MySQL server cost and MySQL engine cost tables, and we have implemented APIs for determining whether data resides on disk or in cache.
Move on to slide 13. The next slides show improvements caused by cost model improvements. This first slide shows improvements due to the condition filtering. The example is taken from DBT3 benchmark, size factor 10, CPU bound. We see five queries here, Q3, Q7, Q8, Q9, and Q12. These are queries that are actually benefiting from the condition filtering. Condition filtering causes improved join ordering. Actually, the effect here is that you choose a different table to start with when you join several tables in sequence. We see that improvements varies here from like 10% for Q7 and up to 80, 90% for Q8. On slide 14, we see similar improvements, but attributed to the improved InnoDB statistics, the accuracy of the statistics. We see still, the DBT3, size factor 10, and we see one, query two and query 18.
We see both queries in two different scenario. One is the CPU-bound scenario, and the other is the disk-bound scenario. For the CPU-bound scenario, comparing MySQL 5.7 to MySQL 5.6, we see that there is improvement in query number 18 of roughly 50%, and, for Q2, about 75%. The same two queries in the disk-bound case, where most of the data is on disk, see that the improvements is up to 50 times, for MySQL 5.7 over MySQL 5.6. In MySQL 5.7, we also implemented the query rewrite plugin and framework. We have added new pre- and post-parse query rewrite APIs, and users can write their own plugins. We also provide a post-parse query plugin with the server, which can be used to rewrite problematic queries without the need to make application changes. For example, to add hints, to modify join order, and many more.
A typical use would be to improve problematic queries from third-party applications, et cetera, where you don't really control the application. This functionality can eliminate many legacy use cases for proxies. Next slide 14. We have added cost information to the JSON Explain. The improved JSON Explain now includes all available cost information, such as row estimates and total cost. Also, there is a visual explain in MySQL Workbench, which graphically show this new information. Next slide. Slide 18. In MySQL 5.7, perhaps the big news is that we have added support for JSON and the JSON data type. We have implemented a native JSON data type. There is a native internal binary format for efficient processing and storage. There are built-in JSON functions allowing you to efficiently store, search, update, and manipulate documents.
We have implemented JSON comparator, allows for easy integration of document data with your SQL queries, indexing of documents using generated columns. InnoDB supports now indexes both on stored and virtual generated columns. The optimizer has a new expression analyzer that automatically can use the best functional index available. We also added new inline syntax for easy SQL integration. Slide 18. The JSON data type, we support UTF-8 and MB4 character sets only. It's optimized for read-intensive workloads. It parse and validate the JSON type on insert only. There is a dictionary stored in the beginning of the JSON data type or the blob. This dictionary contains sorted object keys. This gives fast access to array cells by index. There is an internal binary format for efficient storage, retrieval, and manipulation. It supports all native JSON types, numbers, strings, and bool. It supports object and arrays.
We extended it with date, time, datetime, and timestamp, and some others. Slide 19, this is an overview of the various JSON functions. We support functions to create, search, modify, and return JSON values. Moving on to slide 20. This is a comparison between JSON and text data type in terms of search speed. On the left-hand side, we have the JSON type. We have an unindexed traversal of 206,000 documents. We are simply looking for the attribute value feature in all these documents. We want a list of all the various values of this attribute value. This means actually that we just need to traverse all documents, and with the JSON data type, we see that this takes 1.25 seconds with the query.
We do the similar thing with the text type, same information, same number of documents, it takes 12.85 seconds. This is roughly 10 times more. It roughly shows that the JSON data type is 10 times more efficient in search than for the text type. Moving on to slide 21, functional indexes. In addition to being faster to search, straightforward, we can also add indexes to JSON data types. The technique in use here is that we then create new column, a virtual column, using the JSON_EXTRACT function, and then create an index on that column. The first line here shows alter table feature, add feature type VARCHAR (30) as JSON_EXTRACT. This is a metadata change only. Kind of immediate, does not need to touch the table at all.
We create an index, alter table features, add index on feature type. This creates the index only, and here the actual feature type will be materialized in the index, and the index is materialized. Actually it takes some time to build that index, but it's done in a non-blocking way, so it doesn't block the base table, the feature table, while building the index. If we run the same query as before, the equivalent query, select distinct feature type from features, we see that now we are down to 0.6 seconds. That is a speed up of roughly 50 times over a non-indexed case. Move on to slide 22. In 5.7, we have continued our kind of efforts and investments in Performance Schema that started out in MySQL 5.5 and continued in MySQL 5.6.
MySQL 5.7, we have added a lot of memory instrumentation, so we can now look at all the memory allocations done by the server, type of memory used, caches, internal buffers, et cetera. It can be GROUP BY, looked at by a specific thread or specific account or user host. The attributes include memory used in bytes, operation counts, and high/low watermarks. In addition to memory instrumentation, we have done a lot of statement instrumentation, so we now instrument stored procedures, stored function, prepared statements, and transactions. In addition to that, we now have Performance Schema information for replication slave status and their lock instrumentation, status and variables per thread, server stage tracking. We can track long-running transaction and measure progress information for online or alter table.
We have improved overall the configuration and ease of use of Performance Schema tables. We have reduced the total footprint and overhead. On the monitoring, in addition to actually the increased instrumentation by Performance Schema, we have added a new layer called the Sys schema. The Sys schema is essentially a set of stored procedures and views that uses the underlying Performance Schema table, and kind of package functionality typically needed by DBAs together in a more easy-to-use, straightforward way for the DBA or developer or DevOps. It helps simplifying DBA and ops tasks, monitoring server health, user host statistics, spot diagnose and tune performance issues. Easy to understand views with insight into IO hotspots, locking, costly SQL statements, schema table and index statistics. Overall, Sys is similar to what Oracle call v$ catalog views and Microsoft SQL Dynamic Management Views.
Both Sys schema and Performance Schema is something that we will continue investing in the future. In MySQL 5.7, we have improved our GIS implementation. We have replaced the custom code with code from the Boost.Geometry Library. The consequence is that we now have a much more fully OGC compliant implementation. We support more things, and the overall implementation is of higher quality, and it's also a good basis, so we can build upon in the future. In addition to the replacing the math part, we have implemented spatial index support in InnoDB, in terms of R-Tree. We have full ACID, MVCC, and transactional support, and we use index records containing minimum bounding box. We also implemented import and export function, GeoHash format and GeoJSON formats, and some helpful helper functions such as distance_sphere and make_envelope.
Moving over to slide 25, this shows more detail of how the spatial indexes in InnoDB works. It's R-Tree-based, full transactional support, predicate locking to prevent phantoms, records containing minimum bounding box, small and compact, currently only support 2D data. We would like to add 3D support in the future. Supports historical spatial index DDL syntax. Moving on to slide 26. Over to other InnoDB improvements. In 5.7, we implemented native InnoDB partitioning. This helps us to eliminate limitations around partition tables. It eliminates resource usage problems we had around partition tables. We also implemented transportable tablespace support for partition tables. In addition to native partitioning, we improved the native full-text search by including full Chinese, Japanese, and Korean support, both with pluggable parsers and providing plugins for n-gram and MeCab parsing of the text. We also implemented native spatial indexes as described on the previous slide.
We implemented a new compression technique called Transparent Page Compression that comes in addition to our historical compression support. We also now have added support for 32K and 64K pages. This can be utilized to, for example, get higher compression ratios. We also implement the general tablespace support, so you can now store multiple tables in user-defined shared tablespaces. In addition to have single table per tablespace, you can now choose to have more than one user table in the same tablespace. We added some support for MySQL Group Replication with internal high-priority transactions. Improved support for cache preloading, so when you shut down and start up your server, you store some page IDs that you will refresh the buffer pool with when you start up. The improvement here is that you now can specify the percentage you want.
For example, only the 25% hottest pages when you do the start-up. We have improvements in storage footprint, so we can change the fill factor of a table. For example, if you have a table that is more in archiving mode, then you can fill it up. If you don't think that it will be a lot of inserts or updates to that table, then it can be of a good advantage to fill all the pages as much as you can. We also improved bulk data load performance, for example, typically used by create index on an existing table. Moving on to slide 20. InnoDB always online, additional InnoDB improvements. We can now resize the InnoDB buffer pool online, so you can actually increase and decrease the size of the buffer pool without any downtime.
We have a possibility to actually create undo tablespaces as separate tablespaces, as of in MySQL 5.6 and default in MySQL 5.7, they are only kind of in the system tablespace. The advantage by having it in a separate tablespace is that you can do automatic online truncation of tablespaces. This is actually a recommended mode going forward. It is, however, not the default in MySQL 5.7 due to some upgrade issues that you need to be aware of, because in order to utilize this feature, you need to start from an empty tablespace. It cannot actually be enabled when you have existing data. This is a limitation we will look at to try to eliminating going forward. We have added some more online operation to alter table. We can now enlarge VARCHAR dynamically, we can rename the index dynamically.
We also, in general, are working towards making settings more dynamically configurable, we want to use this as kind of the design principle for new features and settings. Slide 28. InnoDB bulk load for index creation, much faster index creation and bulk loads. Sorted index builds done from bottom up, improves the speed by increasing locality and decreasing node splitting. Pages are compressed only when full. New InnoDB fill factor option control free space left in each page. Performance results show two to three times performance improvement for add and create index operations. Up to 500 times improvement with larger InnoDB sort buffer size values. 2%-5% improvement for standard insert operations. InnoDB temporary tables. We have implemented a new tablespace for temporary tables. This improves the create/drop performance. The DDL changes are transient, which eliminates some disk I/O.
We have optimized the DML operation, we do no redo logging, no change buffering, less locking. We also implemented new intrinsic temporary tables. These are specialized temporary tables with tailored ACID and MVCC semantics. These are lightweight and ultra-fast, great for internal intermediate query execution operations. We also made an InnoDB as the default storage engine for disk-based temporary tables. This is optimizer switched from MyISAM to InnoDB for internal table due to being more scalable and faster. Slide 30, InnoDB full text CJK support. We have now two new full text parser plugins. We have n-gram parser support for Chinese, Japanese, and Korean. Support all ideographic languages that do not use word delimiters. We have MeCab parser support for Japanese. This is native Japanese-focused language support.
It is easily customized on token size, stop words, et cetera, it supports advanced searches or Boolean mode, natural language mode with ranking, et cetera. Slide 31 on InnoDB compression. We have been working on Transparent Page Compression. The compression happens transparently in background threads. It's managed entirely within the I/O layer. It uses sparse file and hole punching support in OS kernels and file systems. It reduces I/O, improves MySQL overall performance, improves storage efficiency, reduces write cycles, thus increasing SSD lifespan. It applies to all InnoDB data, including the system tablespace and undo logs. We have added Syslog support for Linux and Unix platforms. We have added native support for Syslog. Simple option to redirect a log output to native Syslog facility. We have added a startup server configuration options, we can change it dynamically in the running server with system variables called log Syslog.
On the security front, we have done a lot of things. We have now AES-256 encryption as the default in MySQL 5.7. This is superseding the old DES encryption. We have implemented password rotation policies, can be set globally and at the user level. On the deployment side, we have enabled secure unintended install by default. We have random password set on install. We remove anonymous accounts, we have deployments without test account schema or demo files. We have easier instance initialization and setup with an option to MySQL be called initialize, we have new detection and support for systemd. In addition, we have done a lot of things on the SSL side. We have enabled SSL by default. We have implemented auto-detection of existing keys and certificates. We have also auto-generation of keys and certificates when needed.
We have a new helper utility that helps to set up SSL. We have also new options to prevent insecure communications by saying require secure transport, we added SSL support to binary log clients. We also extended the proxy user support. We have added built-in authentication plugin support for proxy users. This allows multiple users to share a single set of managed privileges. Some similarities with roles. On slide 35, we have improved user level locks per connection. This is a contribution from Konstantin Osipov. The user level locks can be used to organize mutual exclusion when accessing some resource when a table or row level lock are not appropriate. It requests multiple locks by issuing a series of get lock statements, replaces custom user level lock implementation with one based on the MDL lock manager.
Deadlocks between different connections requiring user server locks, metadata locks, and those waiting for table flushes are properly detected and reported as errors. We also improved the MDL locking. We have a fast path for DML locks. We have lock-free DML lock acquisition. Lock-free hash now uses MurmurHash library. Removes bottleneck around DML access to a single table. This increases performance in a scaling environment. Slide 37. We have also implemented server-side statement timeouts based on the contribution by Davi. The server-side statement timeout can be set either globally for the server per session or individually per statement. What happens is that when certain number of time units have elapsed, if the query is not finished, it will be aborted. Over to replication improvements. On the replication side, we have five major enhancements. We have the global transaction ID enhancements.
Global transaction IDs were introduced in MySQL 5.6. In MySQL 5.7, we make it easier to deploy global transaction IDs without stopping the system. We have implemented a procedure to be able to do online phased deployment of global transaction IDs. We also have binary logging on slave, now optional. In some cases, you don't really need the binary logging on the slave, and this can be now turn off. We have also enhanced our semi-synchronous replication. The write is now guaranteed to be received by slave before being observed by clients of the master. We have an option to wait for more acknowledgement from multiple slaves before the master externalize the transaction to the clients. We have also implemented multi-source replication. This feature consolidate updates from multiple master into one slave, for example, for backup purposes or on data analysis purposes.
We have also added the capability to be able to set slave filters dynamically, so that what binary log events will actually be propagated to slaves can now be changed dynamically. In addition, we have implemented or improved the parallel replication. On the slave side, we are able to process a lot more transactions in parallel, while in 5.6, we only could do that per schema. Essentially, while in 5.6, we could process operation on each schema in parallel, we can also now process transactions within a single schema in parallel. There is a procedure for enabling the global transaction IDs online. The important thing here is that we can actually do this without stopping servers. There is a way out in all kind of failure scenarios. You can either roll back or roll forward.
The first phase is check compatibility, server reports any incompatible operations. It start generating global transaction IDs. All servers generate global transaction ID, but don't depend upon them. There is a brief wait, allow all legacy events to work through all servers. There is the global transaction IDs enabled. All servers generate and expect global transaction IDs only. This is the phase where we get full benefits of the global transaction IDs. More on the replication improvements. We have multi-source replication illustrated on the left side here. Multi-source replication can be used to consolidate updates from multiple masters into one slave. For example, to have a consolidated view of all shards or a centralized point of backup. The multi-source replication is compatible with semi-sync replication and enhanced multi-threaded slaves.
Other improvements, Performance Schema tables for monitoring slaves. We have these online operations, dynamic replication filters, and switch masters are now online operations. We have also implemented some high availability improvements in the server. These are features that kind of help others or surrounding components to create a more HA system, where server is a part of it. We have implemented support for tracking transaction state. This offer better support for load balancing across nodes because a load balancer would have more information on what state the server or the transaction is in at any point in time. We implemented server version tokens. This provides a mechanism for comparing a client state with a server state and take actions. We implemented a new data migration tool called mysqlpump. This improves data migration and sharding operation between nodes.
We have improved replication options in HA group, improved slave performance with clock-based parallelization, lossless semi-sync replication plugin, supporting multi-node acknowledgment. We implemented or we are in the process of implementing synchronous replication with the MySQL Group Replication plugin now in labs. We have a new version of MySQL Fabric 1.6 out in beta. This is our kind of high availability plus sharding-based scale-out system. It provides high availability, server monitoring with auto promotion and transparent application failover. No single point of failure. Optionally scale-out through sharding. Application provides shard key, tools for shard management, global updates and tables. Connection options, Fabric-aware connectors, and the new MySQL Router. We have server provisioning based on OpenStack, support for Nova and Neutron APIs. MySQL Group Replication is in labs, not yet GA. MySQL Group Replication is an active to active update anywhere solution.
It has built-in conflict detection and resolution. It has automatic group membership management and failure detection. There's no need for server failover. There is elastic scale out/in, no single point of failure, and there is automatic reconfiguration. It's well integrated in the overall MySQL architecture with InnoDB global transaction ID based replication with Performance Schema, et cetera. MySQL Router is new in GA of two weeks back. This is connection and transaction routing from client to servers. It's intended to be used in kind of a transparent MySQL Fabric support, transparent HA, transparent sharding, transparent support for MySQL Group Replication clusters, and transparent support for custom clusters and HA setups. It's easily extendable using plugin APIs. Many new plugins to come. Aggregation, binary log, load balancing, et cetera. We would like input from community on what kind of plugins people would like to see.
On slide 45, more on the MySQL Router, kind of underlying the extensible architecture and the MySQL Router is kind of a microkernel with plugin approach to thinking. The idea is to extend it with more plugins for things like aggregation, binary log, load balancing, et cetera. Moving on to slide number. This shows more an overall picture of the future of MySQL scaling, HA, and sharding. It shows that there is a Fabric component or a controller that has the knowledge about the environment. It shows a connector layer, supporting SQL queries, towards systems, like with both Group Replication and normal sharding systems and things like that. Slide number 47 mentions another lab release product, HTTP plugin for MySQL. If you try out that plugin, you can see that we can have an HTTP as an endpoint to MySQL.
Actually, for example, the browser communicating directly with the server instead of implementing a layer in between. This is a kind of interesting approach to investigate for the future. Slide number 48 just mentions there that we still have a very strong Windows story. We have a nice MySQL installer for Windows. We have MySQL Workbench. We have the MySQL Workbench Migration Wizard. We have MySQL for Visual Studio integrated, MySQL for Excel integrated, MySQL Notifier, MySQL Connector/NET, and MySQL Connector/ODBC. I have some links with additional information that you can check various blog posts at mysqlserverteam.com, and also to look at the manual at dev.mysql.com. Thank you very much, over to Q&A.