Hello, and welcome to the webinar, What's New in MySQL 57. This is Karel Hedalsvik speaking. You can ask questions during the presentation, but there will also be a Q and A session at the end of the presentation. First, safe harbor statement. The following is intended to outline a general product direction.
Move on to Slide number 3. So we just announced Slide Scale 5.7 GA a couple of weeks back, and there has been a lot of progress and big investments going on for 2, 3 years in Maerskrel. The focus areas being performance and and scalability and manageability. We have we are showing a much faster and scalable MySQL. We have enhanced InnoDB online in France.
We have more online operations, faster load, etcetera. 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 Misesc 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 multicore CPUs. And the graphs here shows scaling up to 72 cores, with MySQL 5.7, 3x faster than MySQL 5.6 and 4x faster than MySQL 55 and 55 and a record of 1,600,000 queries per second. These are SQL Point Select or primary key lookups from the Sysbench benchmark. And then 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. And we have seen that MySQL 57 can handle 82% more connects disconnects per second than 5, 6. And we can now deal with about 100,000 disconnects per second. Similar benchmark for this is called the mixed OLTP read only. So this is still the CISSPENCH benchmark, but with the OLTP read only mix.
And similar graphs, as we saw before, 3 times faster than MySQL 5, 6, 6 times faster than MySQL 5. And on the readwrite front, similar same benchmark, we also have improvements. We can see that it's 1.5x faster than MySQL 56 and 3x faster than MySQL 55. Then on Slide 8. We have done a lot of investments in the parser and the optimizer during the last 2, 3 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. So 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. And we want to, for example, introduce things like better prepared statements, parallel queries and things like that. So this is preparatory work for things to come in the future.
This is Slide number 9, more details on the partial refactoring. We are we have had a very historically, a very complex parser, hard to add new syntax. We have now rewritten in an incremental way the whole parser, create an internal parse tree bottom up, create an abstract syntax tree from the parse tree and the use of 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. And then we have on Slide number 10, optimizer overview. So 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, which what type of tables and what indexes exist on various tables. And then we're using kind of a cost model and statistics from the storage engines that says kind of cardinality and how many roles are there with 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. So in this case, it for example, has defined join orders and which access methods to use.
Yes. On Slide 11, we have optimizer improvements in 5.7. I already mentioned the optimizer and parcel 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 Jason explained, which now outputs also raw 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 plan, optimize the 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 the InnoDB statistics used by the optimizer. Both things will improve performance drastically in certain cases. We have implemented generated columns in Five 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 SQL 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 oil.
So 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 optimized set to new hardware requirements, SSDs, larger memory size, improved caches, etcetera. This means that we want to choose different cost constants depending on whether the table is stored in memory on an SSD or disk, etcetera.
So we have done work in this direction in 57, and we have separate out hardcoded 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 there is we have implemented APIs for determining whether data resides on disk or in cash. Move on to Slide 13. So the next slides show improvements caused by cost model improvements.
This first slide show improvements due to the condition filtering. The example is taken from DBT3 benchmark, size factor 10, SAPU bound. And we see 5 queries here: Q3, Q7, Q8, Q9 and Q12. And these are queries that are actually benefiting from the condition filtering. And condition filtering causes improved join ordering.
So actually, the effect here is that you choose a different table to start with when you join several tables in sequence. And 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 EnerDB statistics, the accuracy of the statistics. And we see still the DVT-three, 5 Factor-ten. And we see 1 query 2 and query 18.
We see both queries in 2 different scenario. 1 is the CPU bound scenario and the other is the disk bound scenario. So for the CPU bound scenario comparing 5.7 to 5.6, we see that there is an 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 improvement is up to 50x, 5.7 over 5.6. So moving over next slide to Slide.
In MySQL 5.7, we also implemented the query rewrite plug in and framework. We have added new pre- and post pass query rewrite APIs, and users can write their own plug ins. We also provide the post parse query plug in 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 3rd party applications, etcetera, where you don't really control the application. And this functionality can eliminate many legacy use cases for proxies.
Next slide, Slide 14. So we have added cost information to the Jason explained. So the improved Jason explained now includes all available cost information, such as raw estimates and total cost. And also, there is a visual explain in MySQL Workbench, which graphically show this new information. Next slide, Slide in.
So in MySQL 5.7, perhaps the big news is that we had 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 ease integration of document data with your SQL queries.
Indexing of documents using generated columns. So we now InnoDB supports no index is both on stored and virtual generated columns. And the optimizer has a new expression analyzer that automatically can use the best functional index available. We also added new in line syntax for ECSQL integration. Slide 18.
Adjacent data type. We support the UTF-eight MB-four character sets only. It's optimized for read intensive workloads. It parse and validate the JSON tape on its 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, and 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 objects and arrays. We extended it with date, time, date, time and time stamp and some others.
Slide 19. This is an overview of the various functions, adjacent functions. So we support functions to create, search, modify and return JSON values. Moving on to Slide 20. So this is a comparison between JSON and text data type in terms of search speed.
So on the left hand side, we have the JSON type. We have an undindexed traversal of 206,000 documents. And we are simply looking for the attribute value feature in all these documents, and we want the various 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.
If we do the similar thing with the text type, same information, same number of documents, it takes 12 point 85 seconds. So this is roughly 10x more. So it roughly shows that the JSON data type is 10x more efficient in search than in for the text type. Moving on to Slide 21, functional indexes. So in addition to be faster to search and straightforward, We can also add indexes to JSON data types.
The technique in use here is that we then create a new column, a virtual column, using the JSONEXTRUCT function and then create an index on that column. So the first line here shows Alta Table feature, ad feature type, Var Karl 30 as JSON extract. So this is a metadata change only, so kind of immediate. It does not need to touch the table at all. And then we create an index, alter table features add index on feature type.
This creates the index only. And here, the actual future type will be materialized in the index, and the index is materialized. So 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. And then if we run the same query as before, the equivalent query, select distinct feature type from features, you see that now we are down to 0.6 seconds.
That is a speedup of roughly 50x over a non indexed case. Move on to Slide 22. So in 5.7, we have continued our kind of efforts in and investments in Performance Schema that started out in MySQL 5.5 and continued in MySQL 5.6. In MySQL 57, 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, etcetera. And it can be grouped by looked at by this specific trend or specific account or user host.
The attributes include memory used in bytes, operation counts and highlow 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. And in addition to that, we now have performance schema information for replication slave status, MDR lock instrumentation, status and variables per thread, server stage tracking. We can track long running Alta Table.
And we have improved overall the configuration and ease of use of performance schema tables, and we have reduced the total footprint and overhead. So on the monitoring, in addition to actually the increased instrumentation by performance schema, we have added a new layer called the Sys Schema. And 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 DVA or developer or DevOps. So it helps simplifying DVA and ops task, 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. And overall, this is similar to what Oracle call the dollar catalog views and Microsoft SQL dynamic management views.
And both Syschema and performance schema is something we will continue investing in, in the future. MySQL 57, 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 kind of replacing the math part, we have implemented spatial index support in INNODB in terms of R3. We have full asset MVCC and transactional support. And we use kind of index records containing minimum bounding box. We also implemented import and export function in geohash format and geojson formats and some helper functions such as distance fair and make envelope.
Moving over to Slide 25. This shows more detail of how the index the spatial indexes in INODB works. It's ARP 3 based, full transactional support, private load locking to prevent phantoms, record containing minimum bounding box, small and compact, currently only support 2 d data. We would like to add 3 d support in the future. Supports historical spatial index DDL syntax.
Moving on to Slide So 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. And we also implemented transportable table space support for partition tables.
And in addition to native partitioning, we improved the native full tech search by including full Chinese, Japanese and Korean support, both with pluggable parses and providing plug ins for engram and makeup 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 32 ks 64 ks pages. This can be utilized to, for example, get higher compression ratios.
We also implemented general tablespace support. So we you can now store multiple tables in user defined shared tablespaces. So in addition to have kind of single table per tablespace, you can now choose to have more than 1 user table in the same tablespace. We added some support for Myscale Group replication with internal high priority transactions, improved support for cash preloading. So when you kind of shut down and start up your server, you kind of store the some page IDs that you will refresh the bulletproof with when you start up.
So the improvement here is that you now can specify the percentage you want. So for example, only the 25% hottest pages when you do a start up. We have improvements in storage footprint. So we can change the fill factor of a table. So for example, if you have a table that is more in archiving mode, then you can fill it up.
If you don't kind of think that it will be a lot of inserts or updates to that table, then it can be a good advantage to fill all the pages as much as you can. We also improved boost data load performance, for example, typically used by CREIT index on an existing table. Moving on to Slide So 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 table spaces, separate table spaces.
So in 5, 6 and default in 5, 7, they only kind of in the system table space. And the advantage by having it in a separate table space is that you can do automatic online truncation of tablespaces. So this is actually a recommended mode going forward. It is, however, not the default in 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 empty table space. So you cannot actually be enabled when you have existing data.
This is a limitation we will look to look at, try to eliminating going forward. And we have added some more online operation to Alta table. We can now enlarge fare cars dynamically, and we can rename the index dynamically. We also, in general, are working towards making settings more dynamically configurable, And we want to use this as kind of a design principle for new features and settings. Slide 28, NODB bulkload for index creation.
Much faster index creation and bulkloads, 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 2 to 3 plants performance improvement for add and create index operations, up to 500x improvement with larger inner DB sort buffer size values, 2% to 5% improvement for standard insert operations. Then in the DB temporary tables, We have implemented a new table space for temporary tables.
This improves great drop performance. The DDL changes are transient, which eliminate some disk IO. We have optimized the DML operations, so 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 MVCs semantics.
These are lightweight and ultrafast, great for internal intermediate credit execution operations. And we also made then InnoDB as the default storage engine for disk based temporary tables. This is optimizers switched from Myesam to InnoDB for internal tables due to being more scalable and faster. Slide 30, InnoDB Filtext CGK support. So we have now 2 new Full Text parser plug ins.
We have Ngram parser support for Chinese, Japanese and Korean, support all high geographic languages that do not use the word delimiters. We have MiCAP partial support for Japanese. This is native Japanese focused language support. It is easily customized on token size, stop words, etcetera. And it supports advanced searches of rule and mode, natural language mode with ranking, etcetera.
Slide 31 on immunodb compression. We have been working on transparent page level compression. So the compression happens transparently in background threads. It's managed entirely within the IO layer. It uses sparse file and hole punching support in OS kernels and file systems.
It reduces IO, improves MySQL overall performance, improves storage efficiency, reduces write cycles, thus increasing SSD lifespan. It applies to all InnoDB data, including the system table space and under 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 a native Syslog facility. We have added a startup server configuration options, and we can change it dynamically in the running server with system variables called logsyslog.
On the security front, we have done a lot of things. We have now AES256 encryption as the default in MySQL 5, 7. This is kind of superseding the old DES encryption. We have implemented parcel 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 passwords set on install. We remove anonymous accounts, and we have deployments without test account, schema or demo files. We have easier instance initialization and setup with an option to MySQL D called Initialize. And we have new detection and support for System D. 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, let's say, to prevent insecure communications by saying require secure transport.
And we added SSL support to binary log clients. We also extended the proxy user support. We have added built in authentication plug in support for proxy users. This allows multiple users to share a single set of managed privileges, some similarities with roles. Then on Slide 35, we have improved user level locks per connection.
This is a contribution from Konstantin Ositov. The user level locks can be used to organize mutual exclusion when accessing some resource when the 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 1 based on the MDL lock manager, deadlocks between different connections acquiring user server locks, metadata locks and those waiting for table flushes are properly detected and reported as errors. We also improved Lock free Hash now uses normal hash library. Lock free Hash now uses normal hash library.
If you move bottleneck around DML access to a single table, And this kind of increases performance in a scaling environment. Slide 37. We have also implemented server server side statement timeouts based on the contribution by David. The server side statement timeouts can be set either globally for the server per session or individually per statement. And what happens is that when certain number of time units have elapsed, then if the query is not finished, it will be aborted.
So over to replication improvements. On the replication side, we have 5 major enhancements. We have the global transaction ID enhancements. Global transaction IDs were introduced in MySQL 56. But in MySQL 57, we make it easier to deploy global transaction IDs without stopping the system.
This year to deploy global transaction IDs without stopping the system. So we have implemented a procedure to be able to do online phased deployment of global transaction IDs. And we also have binary logging on slave, no optional. So in some cases, you don't really need the binary logging on the slave, and this can be now turned on turned off. We have also enhanced our semi synchronous replication.
We have the Bright is now guaranteed to be received by SLADE before being observed by clients of the master. Master. And we have an option to wait for more acknowledgment from multiple slaves before we before the master externalized 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 SLA filters dynamically so that what binary log events will actually be propagated to slaves cannot be changed dynamically. In addition, we have implemented or improved the parallel replication. So on the slide side, we are able to process a lot more transactions in parallel, while in Five6, we only could do that per schema. So essentially, while in Five-six, we could process operation on each schema in parallel, we can also now process the 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. And there is a way out in all kind of failure scenarios. You can either roll back or roll forward. It starts by the first phase is check compatibility, server reports any incompatible operations. It starts generating global transaction IDs.
All servers generate global transaction IDs but don't depend upon them. Then there is a brief wait, allow all legacy events to work through all servers. Then 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.
So more on the replication improvements. We have multisource replication illustrated on the left side here. So multisource application can be used to get the consolidated updates from multiple masters into one slave, for example, to have a consolidated view of all shards or a centralized point of backup. The multisource replication is compatible with semi sync replication and enhanced multithreaded slaves. Other improvements, performance schema tables for monitoring slaves.
And 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 system, where server is part of it. We have implemented support for tracking fashion 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 We implemented a new data migration tool called MySQL Pump. This improves data migration and sharding operation between nodes. We have improved replication options in group, improved slave performance with clock based parallelization, lossless semi sync replication plug in, supporting multi node acknowledgment. And we implemented or we are in the process of implementing synchronous replication with the group replication plugging all in labs.
Then we have a new version of the MySQL fabric, 1.6 out in EBITDA. So 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. And we have server provisioning based on OpenStack support for Nova and Neutron APIs. So MySQL group replication is in labs, not yet G8.
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 kind of 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, etcetera. Maerskall router is new and GA of 2 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 transparent sharding, transparent support for MySQL group application clusters and transparent support for custom clusters and set ups. It's easily extendable using plug in APIs.
Many new plug ins to come. Aggregation, binary log, load balancing, etcetera. So we would like input from community on what kind of plug ins 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 plug in approach to thinking.
And the idea is to extend it with more plug ins for things like aggregation, binary load balancing, etcetera. Moving on to Slide number 14. This shows kind of more an overall picture, the future of MySQL scaling, and charting. So it shows that there is a fabric component or a controller that kind of has the knowledge about the environment. And it shows supporting SQL queries towards systems like with both group replication and normal sharding systems and things like that.
Another lab release product, the HTTP plug in for MySQL. So if you try out that plug in, you can see that we can have an HTTP as an endpoint to MySQL. So actually, for example, the browser communicating directly with the server instead of implementing a layer in between. So this is kind of an interesting approach to investigate for the future. And Slide number 48.
Just mentioned 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 migration wizard. We have MySQL for Visual Studio integrated, MySQL for XL integrated, MySQL Notifier, MySQLconnector.net and MySQLconnectorodbc.
And I have some links with additional information that you can check various blog posts at myscaleserverteam.com and also to look at the manual at devmyscale.com. Thank you very much. And then over to Q and A.