 Good morning everyone our afternoon or evening or wherever you are. Please let me know if you can't hear me we're Doing a lot of tricks behind the scene My name is Dave Stokes. I'll introduce myself more in a minute I'm very sad that we didn't actually have a chance to get together in Austin if you've never been to Austin it is a beautiful little city and it's a great place has a little bit of everything for everybody and and Hopefully next year we'll be able to To do all meet together face-to-face So if you have any questions, please pop them up. I'll get to them as quickly as I can and let me Advance the slides Which I'm trying to do Here we go So this is a talk on my SQL 8 new features and a peek what I think it's going to happen in 2020 2021 and there on I Will have to advise you that I Do not have perfect knowledge of the future. That's why Oracle requires me to put in this lowly safe harbor statement If it's not an already established product out there for download Take anything I say about it with a grain of salt now Talking about enemy think everything I'm talking about today in the community and the Enterprise Edition is already out there But on the Q&A we might go off on a tangent to something that isn't out there So if I say something blue you're thinking sky blue. I'm thinking this color blue and it ends up being blue cheese Okay, a little bit about me. I started using my skill or first became available I've used it many products or projects. I My lot of my career was used based on open source because I was hired to get things out the door When the money's already been spent and therefore the only thing available to me was open source software Many years ago. I joined my school a B is a PHP programmer in the certification team I've gone through the my SQL a B To Sun Microsystems to Oracle transition. It's been a little bit over 10 years now I left for a little bit for go to a start company called Cal pot Which had a column or storage engine called infinity B and then came back with this job that I have now on the community I opened up. I have both the 80 dba and dev Servications, I have a lot of previous servications for that my skill and a whole bunch of stuff And I live in Texas well north of Austin and you'll probably see one of my hound dogs wandering wide background in a little bit So one of the things we've been doing with my SQL is Trying to make things a little bit easier for you in the past if you're running my school 5 6 You're using dot net connector 3.4 with enterprise backup 6.2. I get rather crazy. So With the launch of 80 Two years ago this past April we decide to put out everything in moss So if you're going to run my skill 8-0, which is our latest version Please run 80 server with 808 020 shell 820 zero workbench router and all the connectors Makes it a little bit easier for you to find out which version you're supposed to be using Also, we test everything together and they all pass to a together. Let's see if I can there we go So once again, I'm gonna we're gonna look mainly at the community server. Yes, it's still free under the gpl version 2 You can go out there and download it for your favorite platform all the Lin I out there. Also, if we don't support your current platform source code is still available You need to download a tar ball or got to get hub clicking on the next slide I'm gonna go through features that have been released Kind of going backwards through time So if you bear with me, there's some stuff that I'll I'll backfill now last April 802 8020 came out We improved a lot on hash joins. I'll go into that in a bit Added more versions of that hash joins are In many ways faster in most cases in the traditional nested loop join that we've done. You'll see some some stats on that Also added some binary log transaction compression and we improved the contention to where transaction scheduling algorithm cats is kind of interesting now, this isn't the the the player musical that was turned into movie recently This was a academic paper from Michigan where they figured out that if you have hot rows or hot columns how to better handle them to boil down a very intricate Academic work down to a one sentence is that if you have hot rows or hot columns feed the greediest resource taker first and that gets some out of you here and Iterate down to you have malting care of this which is on at a certain system load and it automatically Spots when you have contention for various resources and this can greatly speed up what you've been doing a Binary log transaction compression if you're doing a replication traditional nice core application as you make a change to the database and The command that made that change is saved off to a file that files transferred to a replica the replica takes that file and applies it to its copy of the data and Updates it so the two sets of data should be the same now part of the problem is In the past things had to be compressed sent over uncompressed and then applied that no longer happens everything sent over in a compressed format and the Replicant knows how to take care of that so this will save a lot of time In transfer over the network should save a lot of displace Now back in January 8019 came out and had some very interesting features. By the way, I'm just skimming four or five From each release to show off what's what's up there? The one that I thought was kind of interesting for a lot of folks right now is too many login attempts We updated the use of the table statement to be more in line with the SQL standard limits and common table expressions and alias on duplicate keys so the failed login attempts if you have an account that you Really don't want someone messing with one of the things you do is when you create the user or alter the user is you can say okay, they get so many login attempts before you lock the account and As you can see in the bottom one You can either if they fail that just lock it period In the top one you see the password lock time is three which stands for three days By the way, if you have different granularity you need other than the days Please let me know and we'll just take that to the engineers as a feature request Now why is as this important? Well, your bosses are doing a lot of stuff in the cloud, but they're really paying a lot more attention to security So your bosses are talking cloud, but they are buying security tables and rows We have two sets of statements here that are equivalent Table T is the shorthand for select star from T Not really a big change, but if you're Trying to do better with the SQL standards or reporting code from another database. This may help you out Now in the bottom one You'll see that For the value statement on you're putting in the keyword row Traditionally, you're used to seeing the bottom one there. We're doing insert into T1 values 1 2 3 4 5 6 7 8 9 But now we have the row keyword The next slide There we go alias unduplicated key statements In the past It was kind of a pain in the rear You have to follow the bottom example where you had to use the value keyword to get the information So to keep duplicates from getting in there You had to do some weird imagination The top example we have the new keyword And this is more like Something like you find in a trigger and I think this is a little easier to understand for for most folks Hopefully this will give you Some extra presence of mind there. Okay way back in October We had a lot of new additions including random passwords talking in the detail explain analyze, which is very exciting It was our first hash join that we gave out there We had a ZTSD compression We're in the library and the enterprise edition started supporting the hash heap corp vault for at rest disk encryption random passwords kind of interesting because For years my skills have the ability to be able to Let you determine what level of password Complexity wanted how long how short the number of special characters number of uppercase characters How long they last before they time out? All sorts of other stuff Well, now we have the ability to generate random passwords Once again security is what your bosses are really looking at and this is a very handy way to create or alter use is to get a random password hash joins the lovely blue stripes you see there are our normal branch Joins where you take one table and you branch off one at a time With a hash you take the two tables Throw them together in a big box and start running As you can see they are well blue lines are actually the hashes the orange lines are the the branch and loop In a lot of cases if you're doing aqua joints, which is where you're going join table X to table Y where x dot a is equal to Y dot a You should see a lot of big performance games there now for those of you who are used to Trying to optimize your queries are used to explain command explain is prepended on a query And it tells you traditionally what the optimizer thinks it's going to do to run the query most modern databases has have a cost-based Optimizer which tries to find the cheapest way to return all the data and cheap is usually Measured in number of disc reads you have to do Disc reads are very expensive. They are a hundred thousand times slower than memory read and all this information is Like GPS in your car and it's based on historical information Like when we get done, I'll probably go out to lunch and I turn left down the driveway right at the first stop sign Left on to the the main road That's the way I've been doing it for years It works great, but I may not know that they're doing construction down the end of the street The other street might be closed for repaving might be washed out So historical information is good, but it's not great Well explain analyze actually takes your query and runs it You'll see some information in there where it says actual time So it actually knows how long it's going to take to run your query way back a year ago We had in multi-valued indexes JSON document validation dual password clone and a Faster character set correlation for some of you that might be able to take advantage of that Now multi-valued indexes are mainly for JSON arrays my skill does not have a native array type and a lot of people have been using the JSON arrays in blue of that now in the past if you had an index there was a one-to-one correlation between an entry and a table and The index so if you have one table entry you have one index entry Multi-value indexes let you have more entries in the index than you have rows If you're playing with this in JSON data once you hit around in my testing This is that kind of see the past once you get past about 17 million rows It really really speeds things up By the way, if you have questions make sure to ask them because I'd rather As answer them as they come up then Then have you worry about not getting it done? Okay, JSON document validation my skill added a JSON data type way back in Version 5 7 almost five years ago and as an old-fashioned DBA. I really like the ability just to store JSON data However, there's some problems with it. There is no way to check the integrity of your data There's no rigor applied to it. So if you want to do something like a range check or type check or have a required field traditionally you couldn't do that with With the the JSON data type so what you can do is you set up a example document as you see here and As you can see in red we have a minimum number and a maximum number and required and This is basically in a table definition and it says okay. We're going to take this JSON document and we're going to Make sure that has an object named my age and it's going to be a type number Well, why is that important? Well, it's a lot easier to keep bad data out of your database then trying to go back and fix it later it How much data can manage my skill efficiently depends on your your hardware and your and your And you're the way your data set up and more on you I know people who run many petabytes worth of information. This was a first question came through so let's see if I get the Get the This to advance Anyway, I have an example in here. Whoops. Let me go back Well, if you download my slides later, you'll actually see Some magic where it may actually test that and how it fails Don't passwords. That's my trouble for doing animation. Don't passwords are a weird idea The first time I saw it I couldn't figure out why you'd want this Well, imagine your manager comes to you and said hey of the 800 applications we have we need to change the passwords on all of them For some reason we need to do that as quickly as possible I just up fire up emacs star dot PHP and go through and change all that So what you do is you alter the user to have a secondary pack password and you retain the current password You go through and you update all the Applications at your will and test them and when you're finally done as you see down there on the bottom there You can discard the old password and you've done a very easy transition from the old password to the new password Constraint checks you saw a little bit of example of that later before this version of my SQL we Would check the syntax, but we didn't actually enforce them now we have several Things that depend on that so they're out there in the wild This is a very simple table. We're creating a cable called t1 first check we're going to do is make sure that c1 Not equal to c2 Make sure the second check there is that c1 is greater than 10 C2 please take a look at that the constraint on that is c2 is positive We'll be checked at c2 is greater than positive now notice we named it c2 underscore positive if you're going to use constraint checks Please please please name them they make it a lot easier to track them down Otherwise you get second constraint check failed, which is not a great message at three o'clock in the morning and you're running out of caffeine okay, going back to Two years two years in two months almost three months when eight o came out our first GA version was 8011 We've done a lot of work The first big thing was the data dictionary in the past if you've gone under var lib mysql You've seen a whole bunch of files my eyes f yd's frm's that was the metadata and 25 years ago When mysql started up. That's a good way to do it The only trouble is those files tend to attract the eyes of junior DBAs and they tended to get cleaned up or Just chew up I notes But it was better to for us to put everything in the database Why well the nodb storage engine is pretty great for point-in-time recovery So if something catastrophic happens, it knows how to rebuild itself So now you have all that information within the database itself Side effect is that you can now have millions of tables within a database And the problem might be that you have millions of tables within a database Something else we had was histograms Indexes are great to find particular records that you watch The only trouble there's overhead every time you do an insert a delete or update The index table has to be updated that overhead can add up Histograms are kind of like when in school where they had all the a People who's last thing started with a and one column People started with B in the secondary column next row with people's last thing started with C Being sorry me having a lasting means of s. I was always near the tail end of the crown Histograms give the optimizer a better way to figure out where the data is and get to it quicker resource groups resource groups let you Define certain virtual CPUs on your system for certain classes of data so like you assigned to CPUs for batch input in your SQL statement you have a comment said Resource group equals batch and the optimizer sees this and knows how to steer it just to those CPUs I mentioned cats earlier. They can tension Intentional we're transaction scheduler. We've done a whole lot with Jason support. We're the only open source database right now with Jason table We have a whole bunch of other stuff out there like You saw with the document validation Other big changes everything's now optimized around UTF 8 and before This is in part to support multinational Unicode data sets and also because so many folks actually use emojis in their data We've improved in or to be cluster. You'll see a little bit more of that in a minute We improved our xdev API. Let's let's use my SQL to no sequel document store Jason data Much better temporary table engine in the past if you're if you were doing something with a temporary table and hit a certain Predetermined size limit it would stop and copy everything over to N. ODB and then restart now stop copy restart was very expensive and In general you can finally have a lot better performance now all this since we've gone the CI CD bandwagon is Every four months or so Every three or four months you're gonna see a new version of my SQL and we're giving you a better SQL We've added windowing functions for analytics common table expressions if you're running subqueries Please look at CTE so much easier to use write understand and modify derived tables The check and transmission earlier The better no SQL support. I'm not really going into the no SQL much today. You'll see a little bit in just a moment That with the JSON validation The better JSON support and using JSON table for taking your no SQL data Which is in JSON and casting it as an SQL table for processing with things like we're doing functions and common table expressions In order to be cluster if you haven't seen this before this is the way we'd kind of like you to architect your data and the upper left-hand corner you see that Blue box, which is your client application running the mysql connector and on that Same machine running a copy of mysql router. This is a level 4 router that knows which machines in the underlying cluster are Are doing what into whom it can also do some load balancing for you and some round robinning the basic idea is this is a level 4 proxy and if you're running in One read write machine and multiple read only machines the router knows which machines are At least loaded and move your your request there if you're running it in multi-master mode there's It makes sure that all that gets committed to the right way and up in the right hand corner You see mysql shell, which is our our new shell. It is our admin tool has a lot of other really neat features now We've also upgraded our standard replication to use a lot of features from Group replication and we now call that inner to be replica set So if you don't need the high availability and the fault tolerant in the automatic handover and you really do the failover by hand mysql In no DB replica set is a very handy tool oops Now the shell Has a lot of interesting features. It's an advanced client and code editor that has command completion The extent it has very extensive health support. It speaks three modes SQL Python and JavaScript. So we have libraries that feature all those you can use those to work with your data It's our admin tool for an inner to be cluster and replica set It's also a way to check your five seven instances before you upgrade to 80 for any pops and global problems And also it has a very very fast parallel bulk loader So if you have comma separated tab separated or JSON data, you want to get your database very quickly I can do that all from the shell and parallel. I think it goes up to eight or nine threads And really choose a potato. Now if you fire up our new shell You'll notice that it's no longer monochromatic There you see the little yellow bricks that say JS on them. That means we're in JavaScript mode and if you look A Little bit over half way down the page You'll see SSL cipher and use a ES 256 by default return on encryption As I mentioned earlier bosses really want to see that if you don't want the overhead or you're in a Situation where you're kind of off in a fair day cage. You don't want to worry about it You can turn it off but by default we're gonna make it as secure as we can and if you notice a little bit further Down all the character sets in use are by default utf 8 and b4 Loading JSON tables JSON files The shell first came out. We had some very clever Python and JavaScript Scripts that would go out there and scrape a line at a time and put it into the database one of our engineers thought it'd be a better for just make that more sensible and create a utility and and It was a very easy way to do stuff however They looked at so well, let's do this in parallel in here. You can see that I'm pulling some data from the command line and Very very quick to load a lot of data so Talking about this year 809 and 8020s come out 8021 is on the horizon So hopefully very shortly. You'll be seeing that 8022 a little bit later in the year and hopefully 8023 So I'd love to hear from you all what you would want to see in other future releases Let's see Fabio last we share the slides of presentation afterwards. Yes, I believe the slides are going to be available I'll put them up on my slideshare. I'll tweet that out there a little bit So if you have features you want to see in newer releases, please let me know If I was In Austin with you and we were sitting at a bar having a margarita or a shiner and you asked me What do you what do you Dave think it's going to pop up for the list of 2020 and 2021? These are kind of my unofficial Views of what's going to happen The Enterprise Edition has been really working a lot on security and it's become very very popular At rest encryption data masking Firewalls that learn your queries and something that falls out of the patterns is not allowed Hold much other neat features and we're going to be you're going to see a lot more pushing on that You're gonna see further and enhancements in JSON support and GIS in the past if you're doing something geographical based I had urged you to use post GIS Since 5.7 we moved over to using the boost libraries These folks write some very great libraries and we contribute to them In 5.7. It was a flat world and 8.0. It's an ellipsoidal world. So you have full wraparound Very very fast very quick support like 8,233 different geographic systems You're gonna see more functionally added to in ODB cluster Especially The clone plug-in which I should have mentioned a little earlier the clone plug-in is really interesting Isn't it copies table spaces between servers? Let's see. I have been asked to upload our slides to sked. I'll make sure that gets out there the You're gonna see more features in the X of API and the new shell There's still a few mute Texas to eliminate for better performance our performance engineer. He's a very nice gentleman based in in France He runs these wonderful charts and we pinpoint where things are bogging down We make changes and he has to start all over again, but he's been very proactive in hunting down all these problems Think you're gonna see more emphasis on kubernetes ish deployments to spot despite That type of rule not being great for databases databases are not designed to work in an ephemeral world and spinning up large databases for a few milliseconds and then Getting rid of them tends not to do great things for databases Also, I have a fear that we're DVAs are gonna be harder to find They're already pretty hard to find in most markets And I also think the scope of the job is going to widen a whole lot and by the way, uh people that are still suffering from bad data architecture Will really see that compound their problems instead of having a Data lake they're going to have a digital data landfill by the way, if you are using the json data type I wrote a book on my skill and json Practical guide to using json my skill if you've looked at the documentation. It's not exactly written for Beginners in mind start with this book with plenty of examples and code snippets to get you up and running I am Tendably working on a second edition because we've added a lot of stuff in the past two years and with that I'd like to thank you all so far. We've just had questions from Fabiola and luis If you have any others, I'll stick around as long as I can and If you Are too shy to ask on the little q&a thing here Please contact me david dot stokes at oracle.com Twitter's at stoker I have a couple blogs out there. You can track me down And with that that's enough for me rambling please If you have any questions put them in the q&a or contact me otherwise and with that Thank you very much participating. Hopefully next year will be someplace And face-to-face again, and I hope you have a great day And please keep yourself safe. Thank you