 Please welcome, Mr. Geir Eidelsvik, that will talk about my sequel to the 8th version. Thank you. Yes, can you hear me? Yeah, it works. So, I'm Geir Eidelsvik. I'm living in Norway. I'm Norwegian. I've been working with MySQL for like 12 years. Last 10 years, I've been heading the MySQL development and maintenance for 10 years now. I work for Oracle. So, that's kind of the facts. So, anyone not heard about MySQL whatsoever? Okay, none. Good. That's a good start. So, anyway, I just want to recap the kind of basic MySQL in a nutshell. MySQL is an open source relational database. It's fully transactional with Acid. It's based on or run on top of the EnoDB storage engine, which is a textbook implementation of a relational database storage engine. Historical focus of MySQL has been online transaction processing, transaction with low latency, high throughput, good scaling. That's kind of the sweet spot of MySQL. It's also based on a very strong replication story used for read scale out and for high availability. We like to see ourselves as simple, solid and secure. It's easy to use and it's proven at scale. Users of MySQL are pretty much everyone, but for example, Facebook, Twitter, LinkedIn. We have GitHub here. I saw we have booking.com, YouTube, you name it. So, that's the user base and many more of course. So, like back in 2008, I worked for Sun Microsystems. I was working on the Apache Derby project, another open source database implemented in Java and specifically on the Sun Microsystems distribution called JavaDB. Then Sun Microsystem acquired MySQL, the company MySQL, in 2008 and I start working on MySQL 8. Later Oracle acquired Sun Microsystem and that's roughly around 2010. So, Oracle has been kind of driving MySQL now for roughly 10 years. There has been major investments in re-engineering, features, quality, put out many major releases, 5.5, 5.6, 5.7 and 8.0 in this fine frame. Kind of work context. Then I will dive into specifics about MySQL 8, which is the topic of this talk and just one tweet we had, one that represents a lot of users that they see MySQL 8 as light years away from the 5.x series and hopefully this talk will explain why. So, I have divided this talk into several sections. The first I call the basics, which is SQL, JSON, GIS, characters, escalation, functions and so on, what developers wants and see kind of perspective. So, one of the things that was kind of a little bit messy back 10 years ago with MySQL was the optimizer. So, it was kind of a little bit of a spaghetti. Things happened all over the place and it was a little bit hard to both maintain and to add new features to it. So, over the last 10 years, we have spent significant time in re-engineering the optimizer and we are at least now at the point where we feel that these four main phases of a textbook SQL optimizer have now been established and is stable. So, you have the parsing step where you actually parse the SQL statement. You generate an abstract syntax tree. You have the prepare phase where you resolve types, do simple transformations that are not cost-based. Oops. And you generate the logical plan and then you have the cost-based range optimizer and join optimizer and which generate the physical plan and then you have the execution phase where we just re-engineered just a few like releases ago where we actually changed the execution model in MySQL, consolidated various way of executing plans into one based on the volcano model which is also used by many other database systems and producing the result set. So, this has been a major re-engineering effort over 10 years. There is a hang here. Sorry about that. My computer just hanged. So, I don't know how to get out of this. There we go. So, one example which in the cost-based, the planning phase, we have implemented histograms. This is needed or beneficial to the optimizer to know the distribution on values in various table columns so that the optimizer can make better decisions specifically about join sequences. Today, you need to explicitly generate or write analyze table, update histogram manually. It is fairly lightweight because we have sampling so you go in and sample. You don't need any more to do the complete table scan here. We are also now discussing how to always generate histograms on all tables. So, this information will be always available to the optimizer. Another thing we did, this is more about the iterator executor and the new execution. We implemented this iterator trees based on the volcano model which is an abstraction of any operation and the tree. So, you ask the first, the top-level node, row and it then will do the calls down the hierarchy and return a row for any type of operation. So, this is pretty standard way of doing execution in an SQL interface. So, on this basis, we implemented hash join which is just another iterator in this model and we replacing the historical MySQL block nested loop method with hash join. We do it in memory if possible and we spill to disk if necessary. So, it's used for inner equity joins in 8018 and the next release, we will release a couple of months from now, we use it everywhere where we historically used block nested loop and it will replace block nested loop. The main benefit here is performance in many cases which hash join is much more performance than a block nested loop. In the worst case, they are equal but in most cases hash join is better. On top of that, we implemented explain analyze. So, this we did just by wrapping the execution tree with timing iterators so explain analyze is essentially you in addition to showing the query plan, you also execute the query and you give timing information and count on how much time you spent, how many times you iterated on the various levels. There is an example here down below. Another big thing about 80 is that we moved away from or we changed the default but not only changing the default to 8 4 bytes but we invested a lot of energy in optimizing for now we have to start comparing up to 4 bytes all the time and that is generally slower than comparing 1 byte so we spent a lot of time in optimizing that we have got a very nice and fast implementation of 4 bytes character comparisons. This is generally because we felt that this is where the market is. You need to have support emojis, you need to support Chinese, Japanese, Korean and all these sort of things. We also moved to Unicode 9.0 collations and we support things like accent case and we have a sense of non-sensitivity or not sense any combinations really. So when it comes to major new SQL features in MySQL 8.0 one of the top one is common table expressions so called with clause. So a common table expression is just like a derived table but its declaration is put before the select statement rather after the from clause. There are just few sketches, simple example shown here in red with non-recursive example and a recursive example we support both and the general advantage with common table expression is increased or better readability. You can write your queries in a simpler form doesn't get so convoluted because you have the sub query in one place and you don't have to repeat it and stuff like that. And it can be referenced multiple time. One CTE can reference another CTE and at least in our case we have cases where improved performance as well. Another major SQL feature that we implemented in 8.0 is window functions or the over clause. So window function is essentially similar to the aggregation function like group by and so on but while aggregation functions are kind of merging many rows into one row a window function will keep the existing row in the result set but typically add one or more columns to each row based on the calculation. This is a small example here. If you have a table with name, department ID and salary you can write, you can sum up the total salary for each department for example by using this over partition by department ID and you see that on the right hand side of the column there you get the department total for each of the original node with the sum of the salaries for all the employees that work for that department. So this is typically more in the analytics landscape that you will use this type of functionality. You want to generate complex reports. It's a fairly complete implementation with frames and what not. This is just a very simple basic example of what you can do here. We also added support for lateral derived table. Some people call it the for each equivalent of SQL. So for each kind of row in table T1 you do another query by referencing to the T1 column. It's essentially easier to write statements with lateral. So it kind of simplifies things a little bit. We also added functional indexes. This is kind of plugging a historical hole in my screen. So before that we have generated columns. We had to generate a column and then create an index on the generated column. Now we directly support functional indexes. So there is a simple example here with column 1 plus column 2 so on just to illustrate. Another perhaps even more important motivation for us was our JSON story. So this you can then create functional indexes or index content of your JSON document including arrays. So you can index JSON arrays by a functional index and speeding up your JSON queries. Invisible indexes is just you can toggle the index to be visible or not visible. So in both cases it's still maintained in the background but if you make it invisible the optimizer will not see it so it will make its plans without this index. Two use cases one is that if you want to perhaps you think that this index is not really needed you make it invisible while monitoring the system and everything if everything works as before for a certain period of time then you say okay I can drop this index. If you see that there are problems then you can just make it visible again immediately and the advantage over this approach is that you don't need to perhaps if you drop an index it can take hours to recreate it if you have a large table. So this is instantly toggling the index on and off. Or a stage rollout where you want to create your index but you don't overnight for example and when you come to work the next day you want to put it in production while monitoring your system. So flexibility in that. And also finally added check constraints that have been missing for MySQL which has been a very commonly requested feature for us. We can also use expression as default values another small hole that has been in MySQL over the years. This is useful specifically for types without literal values that you want to initialize adjacent data type for example or a global unique ID or you want to initialize a point or a geometry. These are good use cases for expressions as default values. We also implemented no weight and skip locked. This is kind of more a special purpose thing but have some nice use cases. This is about locking. So one of the performance problems you can hit writing SQL or using a relational database is locking. You're waiting for someone else that holds a lock on a certain row and that can create bottlenecks if there is very many transaction or user queries want to exactly the same row. You generate the bottleneck in the system. So if you for example want to implement a queuing where you potentially just need to pick a job you don't care so much about which job you can do a skip lock for example. You search for the thing and you just get the one that you pass those that are locked by others and it takes the first that is not locked. In this way you avoid contention. Of course can't be used for everything but it is something that people have requested to implement certain special functions in the system. We also have the no weight alternative where I want this row if I can't get it I will just go back. I won't wait for it. I will do something else instead. We also added a few more functions. For example bit operations are now available on all binary data types. There were restrictions on that before. We also extended reg exp support. So on the JSON side the gray ones here are those that are present also in 5.7 MySQL 5.7 and the red ones are new ones in 8.0. So we added some very useful JSON functions specifically the JSON array aggregate and the object aggregate. So an object aggregate can be used to if you have SQL queries and you want to create from the result of an SQL query you want to create the JSON document. So then you wrap your SQL inside the JSON aggregate array aggregate and or a combination of object aggregates and you produce a JSON document this way. We also support JSON table which do the opposite. I will explain that a little bit in the next slide. We also support no overlaps between arrays for example. We have support for adding a JSON schema validation and we have support for JSON array indexes. So we can index JSON array. So the overall JSON story start to be pretty nice. So JSON table the typical kind of thing that you can do is to create a SQL table from a JSON document or from a query. So in this way you move from JSON into SQL and with the aggregate function you can move from SQL to JSON so you can move a little bit back and forth between SQL representation and no SQL or JSON document store representation by using these two set of functions which is quite nice if you want to combine for example a document oriented model with a relational model and you can in this way by using these techniques join two different, use SQL to join two different JSON documents for example. MySQL 8 have also added full geography support that was missing from SQL until now. This is essentially about calculating from longitude latitude coordinates on the surface of the earth. So you can do all sorts of geometry calculations directly by calling MySQL functions. You can calculate distances on the surface of the earth or overlap between areas and so on and so forth. So but MySQL 8 is not only a relational database although it's kind of mainly a relational database but also a document store. So you can decide to use MySQL as a document store and just create your collection. You can get your collection. You can do crowd operations like add, find, modify, remove directly from JavaScript. You don't need to know anything about SQL. You don't need to know anything about schemas or databases in general. You just create a collection, put in a document, get it out, find, modify and remove documents. So we also changed kind of, we added some architectural components. For example, we, from JavaScript and Node.js they interface to XDEB API. There's an abstract interface that we created and we have a Node.js implementation. And this, from this DEV API we also talk over the X protocol which is an alternative protocol to the classic historical MySQL protocol. So we now have two protocols. We kind of expect over some years ahead to slowly more and more use the new protocol because it's asynchronous. It has more nice features but we expect to support the old protocol for the next 10 years. So this will be a slow thing but you can use this today and it will be gradually built out. But, and then on the server side of things we have a plugin, X plugin. So then the client talks to the server through these layers and the X plugin actually translates the incoming CRUD into SQL. So from the server perspective there is nothing special here as it is now. And it works quite nice. Of course, we are then using our own JSON functions a lot on the inside. So the X plugin kind of translates the CRUD over to using JSON functions and SQL functions. But the document store is more than just technology. It's also kind of, documentation. It has its own, you can integrate it in IDEs. You have, yeah, explaining the syntax you can use and so on and so forth. But at the very high level it's kind of very similar to what you can do with MongoDB today. So then I will move over to some operational concerns. So the first part is more kind of seen from the developer perspective. What can I do as a migration developer? What kind of functions? What kind of SQL can I use and so on? Here we put the focus a little bit more over to the DevOps or to the operators of the MySQL database and which are typically interested in topics like securing, monitoring, managing and upgrading the server. So first of all we have a theme that MySQL 8 should be secured by default. So when you download and install MySQL in three minutes you will get an instance that are secured by default for everything. So we worked here to minimise the attack surface, minimise process permission, minimise file permissions, minimise privileges, strong authentication, strong encryption. So this has been a theme. So you should feel safe, do it and you will be secure. A part of this we added a strong default authentication so we switched the default authentication from 5.7 to something much stronger because we also worked a lot to optimise this. So there is this kind of trade between speed and security. So sometimes it requires a lot of work to think about a mechanism to balance right. We think that we have got a good balance here to plug in. In general, MySQL authentication is plug-able so we can plug in other things and you can also integrate with other authentication systems by having a different plug-in. We also have support using the Unix socket or operating system login for the server. We have invested in our password management story. Some of it exists in 5.7 as well but the whole package has now been completed in 80. So we have password rotation policies and enforcement, password history and reuse protection, password strength evaluation and enforcement, password generation. We also have two password per user that can be quite useful in some kind of migration where you want to change the passwords of users and applications. We have brute force attack protection. 80 is also quite different than earlier version on authorization specifically around roles. So we added standard implementation of SQL roles in 80. We also added information schema, standard SQL information schema support for things like applicable role, enable role, role grants and so on. This is just to have a view on what kind of roles and privileges do I have right now. We also did the privilege system much more fine grained so we now can set more specific privileges and control the privilege of the DBA or the operators much more fine grained than earlier. Historically my SQL had this super user privilege that if you got that you could do whatever you want and if you so you had that but then now you can have specific ones for backup and so on. Another change in 80 is that we moved away from the historical kind of wolf SSL and some other things. We found that our users really wanted to use open SSL period and we then took the consequences. We also decided to go with dynamic linking so when you download my SQL it will use the open SSL that you actually your platform support. So it will depend on your platform. There are differences in let's say Red Hat 6, 7, 8 on which open SSL version is used there. We also now have support for FIPS compliance in this sense. You can reconfigure certificates without restarting the server. So in general our users always tell us that we don't want to stop the server for any type of maintenance so please make it dynamic all the time and we are slowly getting the message and this is kind of a theme that we have internally so this is a general guideline for anything we do if we can't do it online don't do it kind of thing because in OLTP transaction system there is no such thing as a maintenance window doesn't exist. It's always up and running period and we added TLS 1.3 support and more stronger encryption for data trust. So on the monitoring side so just in MySQL in general we have two type of metadata information or monitoring information if you want there is the persistent metadata which is stored in the data dictionary also have a transactional data dictionary so we moved away from the old FRM files and these things that happened before. So information schema tables have inodb tables dictionary tables and they have information of things that are persistent like table names, column names and any other things that needs to be persistent and then we have performance schema tables a different storage engine which kind of it's a normal table it's accessed over SQL as the tables but this information is lost if you restart the server so we use this to catch the current activity like statistic performance oriented measurements that doesn't really need to be persistent which also makes it very kind of efficient to use here then we have this schema which is a set of stored routines that are more task oriented that uses information from performance schema and information schema it's a general picture so some examples of what we have added for performance schema in 80 this is server only we have more for application I didn't mention this today so one a few popular things that we implemented is what is the latency distribution for a given SQL statements so we have kind of monitoring over time and give the distribution of the latency of the query so typically if you see that a lot of the query start to use a lot more time you have kind of a problem going on or if more of them are at the high end it's sometimes okay to say one query did spend a lot of time on this 95% percentile thinking because but if this start to happen a lot you probably have a problem that you need to dig into then you have data locks so you can look at what user threads are waiting for which locks and who holds them so we have a full overview on who is locking and who is waiting for locks so by the way I didn't mention that but MySQL is a single process system a multi-traded single process system so MySQL database is one process called MySQL D SQL errors so which errors have been sent back to clients and statistics about those errors this is kind of very useful to monitor for monitoring purpose by DBAs so when did you send an error how often did it go also we can look at configuration variable what is the current value of the configuration value who set it when was it set and on management so a major theme for us in general and we implemented a lot here in 80 is to eliminate the need to access the host machine this is for environments where you typically don't have access to the host machine there can be some cloud environments it can be docker environments and so on but our general goal here is that you should not really need to log in to the machine and you should not need to restart the server these are kind of two things so this has been a lot of focus for us to say that to fix these things and I think we have largely done it we have a few things left but we are working on it still my scale have always had something called set persist sorry set global as you can set the global variable but it was not persistent so if you restart the server the value was lost now we can do set persist a configuration variable value and this will be stored and if you restart the server at some point it will pick up the same you can also restart the server remotely and you can auto-upgrade the system now has logic to understand that I am a new server version reading an old image and based on the version information in the process itself and on disk it makes decision about what to do on the upgrade so try to be fast here my scale is more than kind of the my scale server itself we try to think in terms of one product strategy where we have components that should fit together ship together, work together and so on an important development here is that we have the shell my scale shell so our slogan here is Ada that is the DevOps Ada is smart Ada is using the my scale shell that is kind of how we want to see the my scale shell so the my scale shell is modern it has color for prompt themes auto-completion syntax highlighting context-sensitive help command history pager less more output formats and so on it is flexible you can run it in SQL mode classic mode you can run it in JavaScript mode and you can run it in Python mode so if you are for example a Python developer you can just write whatever you want to do in Python there it can be used as an SQL client it can be used to write document store applications with CRUD it can do cluster admin and replica set admin that we will speak about soon it is extensible so we both ship utility functions developed by us we have a reporting framework where we can do show and watch on SQL statements for example over performance schema you can write your own plugins in JavaScript and or Python moving on to clone so clone is essentially to if I have one master or one server and I want to have a read scale out replica new one that doesn't exist before I can more or less I will show you the steps but more or less I can tell the new I started a new replica and then they set up a stream communication and it transfer all the state from the donor over to the recipient over the network roughly at the speed of the network bandwidth in that case so it is kind of simplify instead of kind of creating a backup moving it over rolling in a backup and so on there are many steps there it kind of gets you fast provisioning the cluster all this is automated so clone directly from a scale I need a new replica I do a install clone I create a use clone user or a clone password and I grant privileges to that I do the same on the recipient the only difference is that I also currently need a donor list this is an additional security step I simply say clone instance from address identified by password I can check status by looking up in performance schema so in progress it started it's been running for four minutes I can also check on each of the stages there is a file copy stage a page copy stage a redo log copy stage file syncing restart and recovery phase to show you inner db cluster or explain what it is so inner db cluster is a high availability solution so we have it based on group replication so there is a group replication implementation then there is a router that can route traffic to the right node in the group and then you have an SQL through the router to the nodes and you have my SQL shell to manage and monitor the whole thing so group replication is kind of a built in solution that covers I would say all things that are needed in the HA setup it's based on Paxos implementation a lot similar to the SQL light talk that were earlier here today in the basic setup so you can do things like initialize a group detect node failure it automatically detects node failure it re-establish a group it elects a new primary if you are a single primary mode you can also run in multi primary mode you recover from failures you can rejoin the group you can grow and shrink the group you can provision new members in the group you have topology information in the group that the router can ask for what is the current topology and so on and so forth the replication technology is much of it centered around this new group replication the new developments in 80 we also have a plier right set so it's the slaves master slave communication it gets the replication stream over to the slave it can execute a lot more in parallel that we could earlier we also we also now based on the global transaction identifiers which have the advantage that there is for each transaction it has a global ID that is valid in a bigger topology so it's not only valid within a single instance it's valid wherever it is the group replication is just built on solid research and papers and published and so on again much of it similar to the previous talk we had so things that are built in here fault detection so it's automatic detection of failed servers in the cluster server fencing so the automatic isolation application and the cluster we have we can configure the data consistency level that also was talked about in a last talk so we can have things like reading your own rights and depends you can configure it in various way it implements distributed recovery so if one node fails it can get information from other nodes to get up and be again there is flow control yeah to just ensure that you don't get replication slag and things it has automatic flow control that kind of slows down things a little bit for a while if things are getting out of hand there are membership services so they know who is the members of the group and can you get group membership yeah I planned I will do this it's five minutes it's okay I will do this mini tutorial in five minutes I think it's should be fairly easy so this is kind of overall I want to show a configure instance create cluster add instance remove instance and rejoin instance everything here is automated and everything is controlled from the MySQL shell we have read write traffic into the router if you are in this here we show single primary mode which is our default so the red box here is the primary the green ones are only for reads so all updates and inserts and delays goes to the red box so what you do you take three machines you install MySQL on each of these three machines and you start it up and running so I say configure instance to each of these three boxes then I point to one of them and I say create cluster foster in 2020 this then forms kind of a group but if I say cluster status status it says your cluster is not fault tolerant I need at least three to be fully fault tolerant because I need to handle also network partition which gets isolated from the others by due to network the other two can form a majority and this is how it works I can add an instance and I automatically can pick up to clone so I just I already started one so the next one will just take the information from the first one by doing a clone the same happens with the third one and now the screen dump just shows the progress report of a cloning activity it doesn't need to worry about that now I can do a cluster status I see that the cluster is online and can tolerate up to one failure then I boot up the router from the shell I start the router I start traffic flowing into the system then just for demo purposes I say kill kill one of the nodes so the node leaves the group and they select it they start electing yeah so they elected a new primary so the second server here is now the primary and there are various things depending on the scenario if there are kind of three major scenarios in faults one is that if I just kill it in Unix kill like I did here it will just recover and it will really know additional action needed but for example if this node there was a network failure and this node has been isolated and it took time to find out and fix the network problem and get it back in it might be outdated so it cannot the group doesn't have enough binary log to be able to catch up the node because it's been gone for several hours or a day or so potentially and in this case we will use the clone when it gets in it will just pick one of the you can even decide which one it will take it from I think but then it will clone itself and then come back to the group and catch up on the latest transaction by the bin logs available in the group and last if and in this case you have to do an explicit rejoin of the third alternative is actually it's broken it will never go come back in you have to replace it give it a new idea and then you take a remove instance of the old instance you add a new instance and then you clone it and come back in okay so what I did here was a rejoin instance so I kind of thought that this was the network isolation problem actually with a kill I didn't have to do it we are back to full tolerance with these simple commands just mentioned that we have a ton of MySQL drivers I also want to mention that we are open source the github the open source is on github just search for MySQL and you will find it there's wide platform coverage we support almost every platform we now currently stick to C++14 standard we use we try to modernize getting more and more newer compilers with more and more modern constructs we are doing a lot of cleaning up we are working on with ASAN and Ubisoft and all this kind of quality tools to get the source code totally clean we have moved to Google C++ style guide and we have online documentation of the source code there are more features in 80 that I keep up to date you can go in here and look at there's roughly like 300 new features in 80 that's it you can reach out on Slack you can reach out on thank you thank you I think that there is the time for one or two questions no does group duplication now support on the synchronous replication or asynchronous as well? so group replication whether it's synchronous or asynchronous it is synchronous in a way in the sense that you guarantee that when your transaction commits you actually the whole group will know that so it's synchronous in that sense but there's kind of a kind of eventual consistency thing going on with Paxos that complicates the picture a little bit but the short my short answer will be yes it's synchronous how hard it is is it to upgrade an existing system an existing cluster to eight so the question is how I upgrade a cluster to from five seven to eight someone else can answer I think that we do have an upgrade path on that I'm pretty sure of that so you I'm not 100% how that so I need to sorry about I can't 100% give you an answer but I'm pretty sure that there is upgrade path from five seven to yeah okay so Fred will talk to you and give you the answer okay one last question there are improvements in terms of storage management meaning for SSD SSD architecture or yeah when it is the improvements for storage management we pretty much assume at least for production system that you run on SSDs and what kind of improvements are you thinking about especially like algorithms or the optimizer to use different things we have logic in eight or or even in five seven I think to or is it eight or only I'm not sure that you will have different costs you will detect that your table is on disk and possible percentage on disk versus how much of it is in memory and take that into account when you generate the plan so in that sense there is some optimizations time is up thank you