 Mic working? Looks like it. Okay. Fantastic. So, we'll talk about MySQL 8 today. And because there is a shorter version of my usual presentation, we will just scrap all the stuff which are for developers and only focus on ops. Now, MySQL 8 has been making a great strides in getting close to a GA, and it's now a release candidate, but it's still not GA, and things are still being changed, right? So, this is kind of based on, well, anyway. Just know what things are subject to change. And so, you know, to give a creative and a creative view, I borrowed a lot of very nice pictures from Oracle team blogs, presentations, documentation, and so on. Okay. So, what are nice things in MySQL 8 for ops people? Well, there are, I think, a lot of great stuff which are in MySQL 8 in the areas of stability, high-vibability, performance, security, availability, and manageability. The first thing which excites me a lot is the native data dictionary, right? And this is something that is very internal, but also very important, and I would say is about 10 years overdue at this point, right? What is all about, right? And one focus on operations would care. Now, if you look at MySQL, before MySQL 8, it would store table information in what so-called FRM files, right? One file per table. And then, if you use in a DB table, it would also store a copy of information in a system table space, right? The problem with such setup is what this data can be getting out of sync. For example, if you would have MySQL to crash in just a wrong time when you were doing alter table, you could have this data to be out of sync, and you have a lot of, you know, fun, money-recovering, right? It also would not be quite atomic, right? If you do the single drop table or a name table, right, that would either really succeed or fail unless you crash. But if you have some sort of more complicated scenarios, for example, if you have, you know, dropping several tables one after another, before MySQL 8, that would not be atomic statement. You could have two FAM drops successfully, then one FAM forward to be non-existent, you get an error, right? Boom. A half-done statement that is not really what you would expect from other statements, and MySQL 8 fixes that. We also have, through that, much faster information schema because now it can read from those real data dictionary instead of having to scan a lot of FAM files, and also know more MySum system table requires. And that is another reason why and how you can, could potentially have non-transactional behavior in the future, right? You would be creating stored procedure which goes in MySum tables, boom, you crash it at exactly that time, MySum tables corrupted what the hell you're going to get there instead of stored procedure, well, no one knows. Information schema. That is a great example here, and we can see this query with a join on information schema, which used to be pretty much unusable, right? Even with relatively modest amount of tables, it could take many minutes, and if you would go to a million table in this case, it would not complete even 40 hours, right? Now MySQL 8 is able to handle that quite efficiently. And even if you have a million table, well, it would take a bit of time, but 78 seconds, that's still usable, right? With such a large amount of tables. UTF 8, an area where MySQL 8 got a lot of focus, and why that is important, especially right now, right? Because even if you are using some of the western languages and generally do not, or did not meet UTF 8 beforehand, then now we have in all the applications, we have what? Emojis, right? And emojis require a lot of really UTF 8. So if you are having kind of millennial friendly applications, then you need to use UTF 8 wherever you actually need support for languages which don't feel in Latin 1. And in MySQL 8, UTF 8 and before becomes default, and also a lot of performance work has been done to make MySQL much, much faster than it comes to UTF 8. I would also be talking about the case that MySQL 8 actually becomes slower, but that is typically when you use the Latin 1 character set. Now a lot of work has been done a lot about security as well in MySQL 8. We support for roles. Now we have many different privileges instead of super privilege, right? Where you can, for example, give somebody access to restart the instance, but not necessary to do some maintenance, some other maintenance stuff. There is a password history, so you can say, hey, we don't allow you to set one of your previous passwords. Authentication in this case became much more secure with SHA2 authentication, which actually used many, many rounds of SHA2, right, for password hushings to make the brute force attack much more complicated. But it also has a very specially optimized cached version, so it can authenticate much very quickly at the same time. Open SSL is now used both of community and enterprise edition, right? So there is no this kind of confusing differences in behavior and available options. Another thing I like is what now skip grants block remote connections. In the past we see many, many users would get into security incident because they need to reset the password. They start MySQL skip grants and they forget about that and what they get. MySQL open to all the internet and now accepting connections with no password, right? Now that's not going to work. If you're resetting the password, it will be only MySQL accessible locally for until you recover the password and restart it back in a normal way. Encryption is also improved. In MySQL 5.7 there is a great start in the encryption which does encrypt energy with table spaces, but that would not encrypt things as your redo logs and undo logs stored in the system table space. In MySQL it fixes that, so all the information stored in the database is being encrypted. Another thing, persistent after increment, another thing I would say about 10 years overdue, right? So now in this case you would not have a case where you after increment value can be reset to something else in some scenarios, right? And you would not have a chance to get duplicate IDs and stuff like that. After manage and do table space. That is another area where I think a lot of folks involved in MySQL operations had a lot of problem through years, right? What would happen before is if you have some expensive update queries or just a select query which was running for a long time and other queries created a lot of undo space in the DB, you could have that space being sort of let's say stuck forever in the system table space and the only way to get it back would be to recreate your database. In MySQL 8 you have undo table space, those are stored in a separate table space by default and then it's also automatically purged. So if you need it for some space for a period of time, use it, then all those workload completed, it will automatically shift back. Self-tuning for in a DB also I think is very important especially in the cloud. Now if you enable this option in a DB dedicated server then you may be able to set a MySQL and it will automatically scale if you instant size in the cloud for example by setting at least three most important variables appropriately. It is not complete and I think there could be a lot more stuff than with automatic tuning that has a great start. Now partial update of JSON, that is also another very important performance thing if you are storing docs, especially large documents in the MySQL actually even in something like MongoDB. Because if you are just updating one field, maybe there's a counter, maybe that's some time you need to update it, you don't want a whole document to be kind of completely written in the database, that is very expensive. MySQL 8 can now just update that field inside the document without fetching that and storing it back, just you know we have a very small point update and also it can handle that efficiently through a replication where you would also only log the change in a row-based replication for JSON field, not for a whole big row if you choose so. Invisible indexes. Invisible indexes, that's a feature I filed in many years ago and the problem of indexes is they are easy to add but they are hard to drop because it's hard to be quite sure what that index is really not needed by your application. And if you drop the index which you turned out you actually need then you often would have queries run so slow it will cause you downtime and then it may take you many hours to go ahead and rebuild that index back because that's expensive. Now what you can do with MySQL is instead make that index invisible so it will be still maintained but not used by the optimizer and if you spot what there are some queries you still have which need it, you can make it visible back instantly and if it's actually not needed you can drop it in a week or a month or wherever you think is appropriate. Teampeatable storage engine, that is actually one of the things how UTF-8 was optimized. It's much more efficient storage engine for internal temporary tables which are used for many complicated group-by queries. And you can also use and what it provides is much more efficient storage for this temporary data especially if you have VARCAR and VAR binary columns. Now right now it doesn't deal with Globe and Text columns they will still would require in ADB temporary table but those are to be fixed as I understand. Backup logs, that's another neat feature which allows us to prevent inconsistent backups result sometimes. Right, so now you can set the special log for backup which would ensure the backup can be taken successfully and it would only prevent some only rare operations. Your normal traffic would not be blocked by this log instance for backup. Optimizer histograms, that gives us a detailed statistics on columns not just indexes and it really allows optimizer to do much better choices in many cases. And what is interesting in this case I provide as an example it is the statistics is stored as JSON which makes that much more extensible and I hope that will allow my school optimizer team to roll in more statistics or different format of those histogram information without needing to redesign it completely from scratch and wait for many years for the new release. Another fix or improvement in the optimizer is improve cost model which allows for example to keep an account how much data is cached versus is not which you have to fetch from a disk. Here is a link with some blog post about more information but in general that can be very important to get the good execution or better execution plans for some workloads. In my school optimizer I'm actually some other helpful changes. I don't have a time to cover them all so here is a link for you. An official my school guide it has some more information about my school optimizers, my school eight optimizers specifically as well as some other features. Performance schema is getting much faster as well and partly that is through having indexes on performance schema. The interesting thing about the indexes on performance schema is what we actually fake indexes and internally information schema just pretends as it has indexes and just because it's in memory and can scan the data so quickly that provides there much better execution plan in the works quite neatly. It also has error instrumentations and response time histograms both global as well as per query digest and also includes query examples in summary by digest so you can actually take the query example for some slow query and run explain for you to see why is it slow. Here is example of a performance schema performance looking through the build in session table which was designed as a sort of better show process list but unfortunately in five seven it wasn't usable with large number of sessions so like thousand sessions would take like 30, 40 seconds that's not as much as you probably would want to wait for show process list. Now you can see it takes a second or two even if there is a large number of sessions available. Remote management features I think the speaker before me mentioned and shown action restart command which I think is fantastic to restart the index and another one is set persist which is another very great command which allows us to set a variable to be persisted when the instant restart because there are so many MySQL users would set the global variable to some value right and just not modify MyCNF keep MySQL running for a few months when restart and boom it behaves differently is much slower or wherever else you may have changed and nobody really understands why because that changes long forgotten. If this persists you can ensure what those things don't happen. MySQL also assumes defaults is SSD right now which is great not a lot of things are done for that as of yet but that is good to change in direction at least. MySQL is also prepared to be able to ask as a replication master by default bin log is enabled, log slave updates enabled and log expire by default right so you don't need to do anything right to prepare that to be used for configuration. Query cache is removed right so if you need query cache then you probably should be looking at proxy SQL right or you may have some other caching solution in place already. Native partitioning so generic partition has removed in MySQL 8 it was kind of substantially slower than native partitioning that means if you're using storage engines like MySum which does not have a native partitioning support then you would have to either remove partitioning from that or convert it able to in a dv before upgrading. Resource groups that is another cool stuff where you can essentially map some of the queries in your workload to specific CPU cores which can help you to reduce the contention but then B it also allows you to kind of to jail some of you expensive queries right so for example maybe you have some analytical queries which can be very expensive and if you let your analytics team to run while they'll consume all the server resources you can set it so they only are able to use no more than let's say 2 or 4 CPU cores and that's it. We also get the better performance at scale right you can see with MySQL again if you have a really big Rn and really large number of concurrently active threads it can be the previous MySQL versions. We also like what a lot of stuff we can use in our Percona monitoring management solution in MySQL 8 especially much faster performance and information schema and more information available in performance schema is great and allows us to build a lot more features out there. Okay now things which are not so great, I'll call them feature requests. The single thread performance still sucks and we don't have a parallel query support as of yet right and hopefully that will get to roadmap sometime sometime soon. Here is the graph right from the champion of MySQL single thread performance Mark Calligan right and actually I chair picked one with the worst regression right guilt charge charge but you can see what for some cases we have less than half performance of in MySQL 8 as we had in MySQL 5 right so I think that is something we would like to see some reverse right and it doesn't have to be this way. There is another database which is called SQLite which is able to both add the new features in the new releases as well as have single thread performance being better release after release. And the last thing if you guys are interested much more information about MySQL 8 and MySQL have open source database in general we welcome you to come to Santa Clara in the United States. We will have a Percona live conference out there just want to let you know and that's it and I think I still have a whole three minutes for questions right Fred? No questions yes so you mean information schema well yes pretty much instead of internal data detection right and the FRM files now we will have just one data dictionary. Any other questions? Thoughts? What? Memory engine what about memory engine? Well so the question is is memory engine going to be replaced right so now we have this TMP table engine which is designed for in memory temporary tables and those tables have a very specific workload which is needed to resolve the queries. Now wherever that engine is going to be at the future time exposed for general purpose use I honestly don't know.