He lost his laptop last night and does not have any slides for the next talk. No, it's indicative of me running into the staff and seeing several bald gentlemen standing outside and not wanting to hear a little, you know, baking like a frying on the top of their head. There you go. So, we're here today talking about MySQL 8. But before we go too much further into that, because I'm talking about a product that's not an official product yet, still the development, I have to read the Oracle Safe Harbor agreement. A lot of stuff I'm talking about is just off the drawing board and it's in flux. So take it with a grain of salt or anything. I'll talk about MySQL 8 or some of our other future products. For those of you who don't know me, I'm Dave Stokes. I'm a MySQL community manager. That means I hit about 30 to 35 events like this every year all around the world. I've been started off on old CPM machines, moved the top to 10, top to 20, wrist to C, back to BMS, and I got tired of deck killing off my operating systems. I went over to UNIX and then Linux. I've been using MySQL for about 20-some odd years. There's ways you can contact me. I'll have some contact information how to download the slides at the end of this. So first question I get when I talk about the new version of MySQL is why 8? Well, 5.7's been out since October of 2015. It's been amazingly well received. The JSON data type made a lot of ISPs upgrade their databases releases from MySQL 5.0 or 5.1 because so many people want the JSON data type. The cluster product went to a 7-numbering scheme about 4 or 5 years ago. And there was a MySQL 6 in the pre-sundays. There's a couple of people I see it in here, see it nodding their heads or wincing. It had a lot of neat features, but between being bought by Sun and some personality changes, it never really got past the initial alpha slash beta release. I wouldn't even call it a beta release. The really cool stuff, except when the backup tools got into the main release. Anyway, with enough of the changes came through, our engineers fought to have it upgraded to number 8. The big, big, big change, which is something behind the scenes. So if you're not a DBA or you're not a hardcore DevOps fan, it probably won't make a lot of sense to you. We're going to have a true data dictionary. If you've ever gone out to your user local MySQL directory and looked underneath one of your database directories, you'd notice there's a whole lot of files out there. .frms, myd's, myis, opt, a whole bunch of them. 20-some-odd years ago, when things started, it was easier to keep the metadata in these little files. 20 years ago, the Linux file system is not what it is now. So you learn to run FSDK and MyISAM check and a whole bunch of other things to fix that. Well, as we learned with replication, it's easier to keep all the metadata in an NODV table. NODV is very reliable, it's fairly crash-shaped. If it does crash, it knows how to rebuild itself to a set point in time. So for the past four years, you've had a project to come up with a true data dictionary. So we're getting rid of all those little files that crupped up your data directory. So we've been playing it, it's very rugged, and one of the nice things about this is you'll be able to do transactional alter-table. So rather than doing alter-table and waiting for the rest of the weekend for it to finish, you'll do something like start transaction alter-table, test it to make sure you want and then hit commit, and then it will be there. The good news about this is it's going to be a lot easier on your file system. The maybe-good stuff is that you'll have the ability to have millions of tables within a schema or database. A schema's proper term database is what MySQL is sort of appropriate for. So you'll be able to have millions of tables on a database. The bad news is that you will be able to have millions of tables within a database. How many of you folks in here are strictly coders? Okay. Don't need to call any folks, don't need to single you out. The only trouble is if there's a loophole that a coder can get into, this will be it. I'm sure the folks who do backups and all that will end up having a lot more gray hair or pulling their hair out, but it's another option for you. Something else we've had a lot of requests for the past five or six years are CTEs and windowing functions. A lot of other databases had them, we didn't have them, and our juniors looked awfully hard at what other folks were doing. If you're not used to CTEs, it's basically a sub-select, but you can make them recursive and re-entrant, I believe, and use a whole bunch of neat functions. Yes, sir? Sure. Okay. By the way, these fly about as good as Donald Trump, so... Most databases have a storage engine that's built in, but you don't see it as separate. MySQL is one of the few databases to have several storage engines. Memory, NODB, MyISAM, Federated, Blackhole. It's like my kids never grew up on the stick ship. They just know you put it in the gear. They never knew what that lever on the left was for some cars. If you grew up without it, you never missed it, but if you grew up with it, you're aware of it. Our main storage engines that we really push hard is NODB. That's where Oracle puts the majority of their money. It's transactional. It's fairly crash-shaped. It is crash-shaped, safe, and it's probably the best overall storage engine for use with MySQL. It's closer to what the other databases, DB2, Postgres, and big Oracle do for all that sort of stuff. It's easier just to think NODB, MySQL, and kind of forget the others unless you really have an edge case. Okay. Now, windowing functions are, for those of you who do a little bit of analytics, and this lets you iterate over certain groups or windows of queries and do some batches. I know someone uses these for pivot tables. They have a whole bunch of interesting functions. Anyway, we haven't had them for years and years and years, but now we finally have them. And I'll explain how to get those. They're a little bit different than the other. Fortunately, it's washed out. I forgot this was going to be a lighter room than I wanted. The developers who are working on this have two wonderful blog posts on this. You can either search for them on planet.mysql.com or download this later. A lot of interesting functionality for windowing and comment table expressions. Other big changes. Optimizer and parser. The parser takes a look at your SQL statement, make sure it's valid, SQL, and then sends it on to the optimizer. The optimizer is like a GPS. It knows you want to get from here to the farmer's market in LA. It knows what the freeways are. It knows some of the routes. I may not know all the side streets, but it knows nine times out of ten this is the best way to get to where you want to get. And to make that really efficient for a whole variety of queries takes a lot of engineering talent and a lot of smarts. And we're now including things like descending indexes, better output trace, better smarts about file sort operations. Sometimes it doesn't have to do file sorts anymore because it knows how to do it off the key. And we also can give hints in your SQL code for the optimizer.  Also for the old timers, we did finally get away the backslash capital N as a synonym for null. It might be, yeah. I know they've been working. Yeah, I know I just saw the contribution. I handled the OCA for that. I'm not sure what quite got in there. I know a lot of the stuff that Monty's team has been working on is just three levels past my pay grade so I don't have to worry about that. Roles. How many of you have ever had to set up a MySQL user account by hand on a command line? Okay, you're going to love this. From now on, if you have a generic user, you just assign them to this role, to a role for that generic user. You don't have to go through and do the update user set. Select underscore priv equal single quote uppercase y, single quote comma, update underscore priv. You just have a rule that has all that predefined. You don't have to go through and do all that typing anymore. Character sets. This is something I really want to warn you about. This may bite you. 20 years ago, I used to optimize MySQL servers because I wanted only the Latin one character set. I didn't care about anything else. I had no reason to have a studio or an umlot or anything like that in my data sets. Well, in this modern age where people have to have the poop emoji somewhere in everything they do, we're now making sure that MySQL's default will be UTF-8 and B4. Now this does cause some upgrade problems if you've been using lower versions of UTF-8, but we have some guides in the manual on how to do that. That should say three bytes, not three bytes. I think they've increased it. I have to go back and double check. I know that's in one of the workflows. Now the question was, does this affect the key length? Also, you get the full multi-legal plane. Also, if you're doing Chinese, Japanese, or Korean idiographs, we have extensions for that. MySQL is also the only database that supports the GB-18030 character set, so if you're doing things in extended Mandarin, we're going to have to be your database of choice because no one else will support you. Also, in the old days when we started playing with character sets, we had two types of character sets generally. The case sensitive and the case insensitive. Well, now you're also going to end up with an accent sensitive and an accent insensitive. The counterparts that I have in Europe and Asia tell me this works very well. Unfortunately, living in Texas don't have much demand for it. Do you watch out for this? This may be an upgrade issue for you, so try a couple of dumps from your main data and see if they convert it over properly. It shouldn't. White or black. Invisible indexes. Every software you have a query or the index it's using is actually valuable. So the best way to test it used to be, get rid of that index. Test it out and away you go. Unfortunately, rebuilding that index if you needed it back could be incredibly expensive. Now we have it so that you can actually alter that index and make it invisible, run your explain on your query, and it will tell you whether it's actually using that index helps. Yes, sir. It should be with a new data dictionary. So, white or black? White. Okay, if you're like the... if you're like everyone's supposed to be core in the info world, you're all in the cloud. You've thrown away all your servers. You don't have any piece of hardware except for a laptop and maybe an iPad and an iPhone. And you have machines that you never, ever, ever touch because they're going to make your steps, 724. And the Amazon fam's not here after earlier this week. Well, we had a lot of folks who are off doing things in the cloud, including the Moscow version of the Oracle cloud. And they want to be able to make changes on the fly, but have those changes persist. So if you have a set persist, here's an example we're changing in order to do buffer size. If you have a machine you don't actually touch it, you don't actually have access to the config files, or you just want to make sure that whatever changes you make on the fly get put out there. You type set persist, and it'll actually create a mysqld.autocns file for you to keep those out there. So if something happens and it does reboot or you clone it and you can grab those settings, this will let you keep your changes forever and ever and ever. Yes, sir? There should be. The question is, they do a lot of still LinkedIn, right? Still LinkedIn. They do a lot of stuff on the command line, like to be able to PS and CL the options. I haven't played with it. There should be some way to do that, but I'm not sure what it would be. Yes, sir? Yeah, sick bubbles are going to still be there. Do you want to, why do I have to go to the black counter? I think it's going to be the standard where the last one executed is the one that takes persistence. That's very good. I think it's going to hit the my not cnf and then this and then whatever other file you have. I think it's just going to change through however you have it set up. I think it's going to hit the my default, the my cnf and then this and then whatever else you have set up, but I'm not 100% sure. I believe so, but I have to test it. By the way, Peter probably doesn't want a hat since he works for Petona. Yes, sir? Your first. White or black? Instored procedures. Should be able to use CTEs and windowing functions within stored procedures. This, I don't know if you'd want to use it in a stored procedure unless it was something for management. Invisible indexes, I guess you could do that in a stored procedure and be kind of odd, but should work. Yeah, well, traditionally, excuse me. I've been putting a code for the past month and it's just now trying to leave my local code. Traditionally, MySQL hasn't had a lot of business since in the database.  So, at me, pre-fixing, I've never heard of this, pre-fixing stuff in the config files with the word loose. It'll ignore it if it doesn't know it. I have not tried it, I don't know. I'll try it next week if I get a chance if not, I'll ask the folks in the Santa Clara show in a couple weeks. They'll have a better idea than I do. I've never heard of this before, so that's another little... Okay, cool. First, black or white? Are there permission controls on that? I'm guessing you're going to have to have some fairly stiff permissions on your account, privileges on your account. Probably this purpose. Yeah. By the way, I usually recommend don't hand out any privileges if you have to do it. It's easier to apologize than to rebuild your server three or four times.  But I don't really see an option. There's probably several good ones out there I just don't know. The other thing we've come to realize over the past couple years is we're putting a lot of great features on the new releases, but it's like eating an elephant. It's kind of a big male to have to swallow all at once. So we've been doing things like the new data to do document store, group replication, some other features out there. We're making a plug-in so that we don't have to be tied to the server GA release cycle. You only install a shared object on your server once and away you go. The document store is rather nice because if you have programmers who don't know SQL and sadly very few programmers are actually being taught structured query language or set theory or relational calculus in school but they still want to read the database so you can now give them a way to do all the crud, create, replace, update, delete without having to know SQL. So if you have folks who are running Node.js, Python, C++, Java hopefully PHP pretty soon in a couple of other languages you can point them a database and it does all the SQL magic behind the scenes works really neatly. Group replication come to the table I have a couple of handouts on this. This is a way to do active multi master master replication. So if you want to have servers that you can dynamically bring in and out for load and make sure they all have same copies of everything it's just another shared object and it's really easy to set up. But like I say we're trying to decouple a lot of the new features from the general release. Open source plugins. We're also working on architecture and if you're interested we're going to have a storage engine meeting next month. So if you do work for a company that's thinking about doing some sort of storage engine type thing or your own plugin let me know I'll get you on the list. We're trying to make the troubles in the past the documentation for inserting a new storage engine was kind of big and misleading at best not there on average. So we're trying to clean that up. 3D geometry. With 5.7 we started working on the boost.geometry folks to to get in a better GIS implementation that we had. And right now we have a flat world. With MySQL 8 you can have a flat or a fertile world. The coordinate system will wrap around. We're working hard with boost geometry and open GID. For those of you who are Californians or you're a governor there's a company down the road for me that promised me a beer as soon as we had something that would work as good or better than post GIS and they paid off when we got 5.7 now they're having to track things in 3D and I'm going to sit down with them in two weeks and see if I can get them running on MySQL 8. If you're doing GIS stuff right now it's a very hot area and it's absolutely fascinating because I never took geometry in high school and it's absolutely hitting life. That's it. I think it's built into NODB. I have to check. I know Manny's been doing a lot of work on that so I'm not sure if it's in there right now or still coming on the Cree table channel. I know they've been hammering that into NODB I'm not quite sure what the release status is at. So, if you want to play with us today you can go to devmask.com downloads minus QL the one exception if you want to play with CTEs it's only a lab release for right now and it's listed as MySQL Optimizer but if you want to go out and download it for Windows, Macintosh Linux or you want the source code you can go to devmask.com if you want we have Docker images that we support also I'd like to point you to the unofficial MySQLGuide.com which is our unofficial Optimizer guide. So, if you have any questions on CTEs, joins, aggregations the new JSON functions coming from MySQL 8 hence cops based optimization changes and all that. It's fully out there and documented. Now, if you need to get ahold of me Twitter handles at Stoker neva.stokes.org I have two blogs out there and I'm going to upload this weekend it's early midday Sunday the swag tends to go a lot faster by the way ping me, I have folks who ping me several months after I showed up questions. If I'm traveling I'll get back to you within a day or two but other than that I'm usually fairly quick and with that let's say your MySQL is hiring we're doing very well under Oracle we're still one of the more popular classes with all of Oracle with that I'd like to open up to questions or comments and if you go for others you can get hats or squeezable dolphins should run squeezable dolphins I'll be over in the next one well I mean I don't want any more hats the cluster folks have already laid for the track to seven zero yes sir as you do it in Arizona which is even more amazing you send them to the humanities department if you go to the MySQL manual listing of how to get around the old skip grant table stuff that used to work it's a slightly different command it's an alter user if you, well let me see if I can get to the well that's how you know the B students from the C students yeah well but we have the full manual here it has the exact thing on how to work through it's not going to fit me right now the embedded market for us is kind of horrible that is for just anyone who's running embedded database I haven't talked to Rebecca Hansen or that crew for a while so I'm not sure what they're up to there's Lady Catherine Colson who's with me this weekend and drop by the booth and she's around she's up on the embedded stuff but I'm not miss it should be a super set of what you have now yes sir will the feature set fit five many years ago and they've been backporting features as they can there's some things they haven't like the JSON data type I don't see how they're going to have the number of bodies to backport the data dictionary and that's that's kind of like going from a steam power car to a gas power car you know some things work but you still have to hit that boiler for the old version to get it to run I sincerely doubt they're going to be able to get a lot of these features in there surprising for the Moscow Enterprise customers I'm talking to folks who want to check every quarter for several million dollars to quarter for Oracle support for databases and all that they say the Moscow stuff is part of the least bothersome it's almost overlooked for them if you're an Enterprise customer your sales guys are very responsive if you ever have a complaint or gripe just call them up and tell them hey this isn't right and they'll get on it well the Biosql community servers are always going to be out under GPL2 the Enterprise edition does give you a better backup tool, better monitoring tool buffer pooling that's rather nice and for $5,000 starting for a 4 core box it's fairly cheap insurance if you need that it's a better number in the middle of the night if your paycheck does depend on your database it's a fairly nice reassuring piece of insurance but Moscow is part of the least onerous of all the Oracle products for licensing issues so we're kind of the odd man out in the corporate world yes sir I see some of the work logs for MEB4 and 401 and I know the folks working on are trying to add a lot of new features and a lot of hardening of the process but I really don't play with MEB enough to really talk about it but I can guarantee you they are working on new versions if you need more details come see me in the booth and I'll get your contact information I'll hand you off to a guy named Mike Frank who's very good at project manager he knows all the business yes sir technology I used to do a lot of stuff in COBOL with indexed sequential access method files before we should database it they were the hot thing but NRDB is just so much better than it is I'd rather try not to especially with big tables it's not your quite group for the first thing Peter yeah as you saw fabric kind of was not exactly products we thought it would be and um I think at a what is the name of that show next month Pritona Live Pritona Live there's going to be a talk that encompasses some of that but I don't really know the details so server ID per database not that I've heard that doesn't mean they're not doing it um my trouble is I only touch the replication folks twice a year to shape the secrets out of them and one of the opportunities coming up the other was last October I know they're working on a whole bunch of stuff in GTIDs I don't know if that's in there though but per database okay officially per schema and then per schema per server or just per schema period um I can see some good ideas there let me pingerley fred about that because he's more in touch with them than I am with all the proxies in the world that everyone seems to love right now that's an object logical extension hey I want to send this off to this table I don't care where it is just get it back to me okay cool this is kind of a client group for the first day at scale yes sir not that I know of which probably means during out scene on Monday um I know there's some work in that area but I'm not sure how mature it is if it's just someone trying something out like I said before at the prokona show if you attend they might actually have some announcements by the way if you don't know about it the old MySQL community show evolved into prokona live it's in Santa Clara next month and there's see me if you need a 15% discount code off the price it's a great event and if you're a real MySQL head show is out there if not the premiere show um on that if you have any other questions hit me at the booth or email me or tweet me thank you all for coming out be sure to save the volunteers again broadcast to oh it's quite loud actually oh I don't need audio probably no audio port here it's here I don't use any audio do I actually need this or use the microphone for the sake of the video recording which is here so maybe you have to do it oh yeah absolutely so there are more seats in the front and they don't have to stand at the back they must all scan jackets so you got all your stamps I much prefer I much prefer when I just give it to you yes you're all here to learn about high availability options in 2017 and MySQL being about 22 years old has lots and lots of changes over time so what may have been a good best practice may have changed or maybe going out of fashion or you may learn new best practices or what you could possibly use later this year and so forth so I'm going to take a quick show of hands how many of you here don't use replication today what ok so I I'm going to hope that you will start using replication so otherwise you can't really get high availability ok so I am Colin and I am the Chief Evangelist in the CTO office at Precona Inc and before this I used to be the founding team of the MariaDB server anybody here use Precona software ok anybody here use MariaDB server ok everybody else use MySQL ok ok just making sure ok uh yeah I used to also work at MySQL so I spent a long time working on similar database product for maybe like 15 years so um MySQL has spawned a huge ecosystem right so I'm going to presume since we're at scale a lot of you run Linux in production at least yes so Linux in production they tend to ship varying sorts of MySQL in fact there is less MySQL being shipped as default but it's well worth noting where you get your MySQL from so upstream MySQL is the Oracle MySQL 5.5 spawned the Precona server 5.5 as well as MariaDB server 5.5 but 5.6 and 5.7 only spawned the Precona server 5.6 and 5.7 because MariaDB server decided at 5.5 to branch off to create 10.0 and 10.1 and there is a 10.2 beta now actually I should change that and there's also MySQL 8 DMR and MySQL 8 will spawn off the Precona server 8.0 as well now if you are using a Linux distribution chances are you're going to be getting um very loud sounds from next door yeah if you are using Linux distribution you're going to be careful because when you ask for MySQL many times you get MariaDB server for free so you have to be very explicit that you're asking for state community MySQL and so forth or Precona server so MariaDB tends to be the default in most Linux distributions and the next version of Dabian will also have it as a default but this doesn't mean that MySQL goes away and it's getting extremely important now that you have MariaDB with a different set of features and MySQL 5.7 with different set of features that you kind of want to possibly use one or the other for its feature set but that's not the focus of this talk just a caveat that you need to actually know so what is high availability I promise these scribbles will go away after a while but this is setting you up for the next talk because Solomon is actually going to use a lot of scribbles as well or he may actually just mind his entire talk I'm not sure so um so what is HA? it is performance and scalability where you think about things from the standpoint of throughput where you look at it from a transaction per second, TPS you also think about latency where you measure in person the response time but HA is also a lot about durability which is why you obviously need to have replicas snapshots as well as backups and um it's well worth noting that MySQL is an asset-compliant database and the D in asset actually stands for durability and that's why you don't lose COVID data right HA is also where you don't have a single point of failure so you have the idea of clustering where there could be failover, maybe manual failover or automated failover and in today's world you probably can get away with doing an automated failover with the framework and there's also about replication where you have redundancies where you have multiple copies of data that you could access so you could write to a master and read from slaves and so forth so as I said the ecosystem is like 22 years old these are just the products that have logos not all of them have logos many of them are just purl scripts that have been cobbled together without logos and if you are very interested in trying out replication on your own laptop where your laptop has to either run macOS or linux this tool called Sandbox is actually quite useful for you you can actually run everything inside your home directory and Sandbox will allow you it's called MySQL Sandbox you can get it from MySQL Sandbox on that you can actually set up replication environments very quickly like 3 node, 5 node replication environments inside your home directory and it automatically sets up stuff for different parts that you can access and it does all the hard work for you so for testing purposes I highly recommend Sandbox if you happen to not run the macOS or linux and you run I don't know windows then you need to have a VM I guess okay so that's a good way to start playing with replication so up times this is a pretty standard personal target with the maximum downtime per year and this is translated to the level of availability a lot of people say they provide up to 5 nines of up times and that is very fashionable but if you go and look at website surveys from various sites not limited to sites like Bingdom you'll actually see that the average site hovers are only around 99.6% worth of up time so very few websites get past 3 nines and I guess if we're going to talk about downtime I'm sure all of you read that you could take I think S3 went down just a couple of days ago and that was like 3 hours out of downtime so somewhere between maybe there and then maybe more I'm not sure well let's smoke wood S3 being up I'm sure you want to see your Netflix and everything else we can also estimate levels of availability so regular replication without you running any framework could possibly give you up to 3 nines up time as well if you use a tool I don't recommend you to use any longer MMM master master you can get 3 nines up time the reason why I still put this here is because the websites are up and we do know people still using it which is just shocking because when it fails it fails massively Sands there's still something people tend to use and you also get typically 3 nines up time if you use DRVD or a framework like MHA or Toxin applicator you could also probably get 3 nines up time and more or less to get 3 nines up time you have the ability to use something like MySQL and DB cluster Gamera cluster should sort of also give you that more or less and I guess once group replication is fully baked it also give you that but it didn't quite fit here anyway dealing with downtime at scale you tend to measure things in thousands of hours so a simple failure can trigger your ops to come back because there is always a replicated copy of your data serving just fine so you know the larger you grow the less you'll disturb your ops because you'll have a lot more redundancy there as well HAA is one about redundancy so you think about it from planning your architecture when this crashes you always have RAID you tend to cluster you tend to have two power supplies redundant power supplies inside the hardware you tend to have more than one network interface card having geographical redundancies also quite important because sometimes data centers can go offline or can get destroyed so an anecdote a couple years ago I went to South Korea and I wanted to swipe my American Express card on a Sunday it wouldn't work wonder why turns out that they had a data center fire at Ilsan which is maybe 60 kilometers out of Korea and out of Seoul and that data center Samsung it was a Samsung data center that caught fire that data center and Samsung were the only people that processed AMX cards in all of South Korea so that meant that while the data center was burning they didn't have another data center where they could actually make use of so AMX transactions were down in South Korea for about a week so that was a good time to have a V-cell Mastercard outside of Korea if you happen to have a Samsung smart TV that Sunday or smart TV stopped being smart and if you happen to use the now defunct sChat service the sChat service went down as well so data centers going offline or getting destroyed is a real problem two years back I think downtown Manhattan got a little bit of extra water that was a bit of flooding many data centers went offline so this is something well worth thinking about you want to be geographically spread nowadays and we kind of have HA in our bodies like if you lose one kidney you could probably still function with the other one right not only the heart is not highly available so durability which I mentioned earlier is the data actually stored on a disk is it actually really written to a disk and being durable actually basically means you call S-Sync on each and every comment but S-Sync calls it fairly expensive which is why MySQL 5.6 and greater and MariaDB Server 5.3 and greater have what is known as group comment in the binary log so you don't call S-Sync each and every time but then of course you also want to make sure that your data is written in a transactional way to ensure the ACI of acid as well HA is definitely harder for databases because you're not just ensuring your hardware resources are redundant but you're constantly changing data is also having to be redundant so you want to make sure that this is uninterrupted as long as possible right there is in the Java circles the idea that you could actually have redundancy through client-side XA transactions where you write the two independent but identical databases this does not use replication and this sounds like a very simplistic idea but it can actually cause other thing databases generally not recommended for you to use in the MySQL world this is possibly useful if you use JavaDB or something which maybe lacks reasonable replication but in MySQL there has been reasonable replication for many many years so suggest you use that so people also look at recovery times trainerDB and one good public example from about 10 years ago was that Wikipedia said it took them about 40 minutes for recovery with even just 256 megs of log files at the worst case so sometimes it would take 5 to 10 minutes and that's because you have fuzzy check pointing and then you need to find the log sequence number you're going to scan the log files see if it's applied to pages and so forth so the locality size of logs the buffer pool size all of this play a role however I would say paradigm is changed today right you don't actually need to wait for recovery time you can just you probably already have it on the slave anyways recovery time thing is not so important nowadays so there is this option to have redundancy through shared storage which definitely is the idea of scale up versus scale out it's generally complex to operate your scan is now your single point of failure it it turns out it's also cold fail over right so it doesn't fail over immediately you can have wait and there's some active active solutions that could benefit from you using shared storage like scale dv even there's also redundancy through disk replication where now you don't call your scan domain this straighter but you call your licks admin over and this is synchronous replication it's referred to as you know rate over internet and why would you use drvd if you already used to a scan drvd could be possibly quite useful for you when you you know you believe that replication you know could be could actually give you transaction loss and you cannot lose a transaction but you also won't longer fail over you could be another option but it's generally not too popular because you also have the fact that you have the second set of data that's inaccessible for use so you have a passive server acting as a standby now there are workarounds this way you could have set the data writing back and forth so both are effectively half of each server is effectively a master and half is passive but it's not something you do too often and then of course performance it as well because you have high average latency and compare this to single load performance you know you sometimes get worst case up to 60% loss there's also redundancies through mysql replication which is really the focus of this talk where you have mysql replication things like tungsten galera cluster ndb cluster and there's huge huge potential here for scaling out mysql replication is generally statement based previously role-based replication was only introduced in mysql 5.1 but it has become the default in mysql 5.7 so this is actually quite important to note so the default in mysql server 5.7 and percolator 5.7 would actually be role-based replication but the default in mysql server 10.2 is mixed mode replication which is by default statement based unless it is calling so-called unsafe statements like if you end up having a server variable being used in a statement or you know two or more auto increments and so forth so why is the default different that's a good question there was lots of debate about this there's actually a georagic about the default should be different there's probably not much good reason for it to still use mixed mode most people are probably now going to already use role-based but if you're using MariaDB it's well worth noting that something you may need to change mysql replication is asynchronous by default which is why it is so fast and so common that basically means your master rights events it doesn't know whether a slave has retrieved or processed it either and with async replication if the master crashes obviously your transactions that have been committed may not be transferred to another slave and it could be lost there is however something called a semi synchronous plugin in 5.5 that has been improved tremendously in 5.7 that allows you to actually not just try to a master but also will ensure that at least one semi synchronous slave has acknowledged the transaction before the client gets the okay which is possibly quite good use for you and of course the idea is you want fully synchronous replication and that is the holy grail maybe but it obviously comes to trade-offs so it's worth noting that Galera that's prokonectdb cluster Galera cluster as well as now MariaDB server 10.1 which includes Galera cluster it's worth noting that that is also not it's virtually synchronous so to speak and we'll talk more about that later as well the binary log today is crash safe they've used a mysql a modern mysql so that's 5.6 or MariaDB 5.3 onwards it means that you're now storing it inside of tables as opposed to sitting on the file system you've got the bin log and the relay log and in 5.6 and greater you've also got these new things called the master info log and the relay log info log quick show of hands how many of you are running mysql 5.6 and greater okay how many are running 5.5 okay so it's all of you plus the MariaDB users are running a 5.5 ish good to note so if anything it truly is quite useful at scale I think one of the very first people to use semi-sync at scale were in focus at Google in fact they published their patches against 5.0 and 5.1 as well and Yoshinori Matsunobu also published how they have been using semi-sync for several years at Facebook and here you have the idea that a thread will perform on the master and it blocks after the commit is done and waits until at least one semi-synchronous slave acknowledges that it has received all the events of the transaction or until a time out occurs um definitely useful for you to take a look at requires some configuration but definitely useful to take a look at and it has improved tremendously as I said in 5.7 replication improved tremendously in 5.6 you know for one we got the ability to have global transaction IDs so each and every transaction gets a unique identifier each server also has a unique identifier so GT IDs are also present inside of MariaDB server 10.0 server UU IDs are not present inside of MariaDB 10.0 and greater you can filter across all servers the per schema multi-threaded slaves is in the MariaDB equivalent it's a per table group committed the bin log also available everywhere check sums very useful that you're going to you know run it on the slave it doesn't take too much but if you realize and you've got some garbage data it'll actually stop replication so it might be worth running of course I mentioned the bin log and the reloads amount crash save time-delayed replication another fairly useful feature that allows you to have a slave that could be set behind by say 10 minutes or 2 hours so if you do live pushes on your MySQL and you make big mistakes you can always just get your time-delayed replica which is maybe say 2 hours delayed and say point to that and things are fixed quicker time-delayed replication is not available in MariaDB yet it's part of MariaDB 10.0 so it's kind of in beta now and parallel replication as well yes why don't you use the old mkdelay slave instead of waiting for MariaDB to implement time-delayed replication if it still works for you then by all means but time-delayed is part of 5.6 and greater so I don't know if this is a huge requirement from the MariaDB users I mean it's just been it was a bug for a long time with an overlooked feature that eventually is getting implemented but maybe you can use this I don't believe it made its way to prokona toolkit did it? yeah yeah so I don't know if you want to use older tools now or not and I was going to say parallel replication as well great benchmarks and real world use cases by a guy called Jean François Garnier at booking.com so look for his blogs at jfg.blogspot.com and an answer that's made its way to MySQL 5.7 that's also been available in prokona server 5.6 and greater as well as MariaDB server 5.3 and greater is start transaction with consistent snapshots the announcement makes binary log positions consistent with unidb transaction snapshots feature is quite useful to obtain logical backups with the correct positions that you require without running a flash table to read lock the bin log position will be obtained by two newly implemented status variables log snapshot file and bin log snapshot position and once you start once you start a transaction using start transaction with consistent snapshots the variables that I just mentioned will actually provide the bin log positions corresponding to the state of the database and a consistent snapshot will be taken off them irrespectively of which transactions have been committed since the snapshot was actually taken so of course you then so my skill dump single transaction has always been around with master data and you get a full non-blocking backup it also does work consistently across storage engines this is the original implementation note and then prokona server improved it by also giving you the opportunity to have session id as well as the introduction of backup logs that work with extra backup so well worth taking a look at both resources multi-source replication was first actually created and made usable by most people inside of tungsten replicator including the open source version of tungsten replicator it then made its first appearance inside of MariaDB 10.0 and multiple people started using at least MariaDB as an aggregator node they've used a framework like tumblr jetpans it started off with they would use I believe a lot of prokona server in production but for an aggregation node they would use MariaDB to aggregate upon resharding and multi-source replication of course has multiple uses you can use it for things like real-time analytics we've seen fun plus games write stuff about how they use multi-source application for real-time analytics inside of AWS DC2 we've seen it for short provisioning which I just mentioned what tumblr does sometimes people say they also run complete backups across shards multiple ways to use multi-source replication it's of course available in MySQL 5.7 and greater as well and the syntax of course is different in between both MySQL and MariaDB so it's well worth noting the differences depending on what you end up using with MySQL you can have up to 254 masters per per save and with MariaDB if I'm not mistaken the limit is 63 multi-source application does not implement any conflict detection or resolution so it just applies transactions so these tasks have now left the application to check if required global transaction ID of course multi-source application global transaction ID in 5.6 doesn't because multi-source application is a new feature in 5.7 global transaction ID can of course be enabled or disabled independently for masters and slaves this is true in 5.7 as well as MariaDB server 10.0 in 5.6 you would actually have to shut down servers to restart to get GTID globally on masters and slaves MariaDB supports this thing called replication domain so you can actually have independent binary long streams so that you can improve the parallelism of the server and this is currently unique to MariaDB where you have this idea of a dash-dash GIT domain that you don't see in MySQL why is global transaction ID different from making replication between MySQL with GTID and MariaDB with GTID now not work for one 5.6 with GTID didn't support multi-source application so MariaDB had to define its own version of global transaction ID MariaDB also wanted to have long-slave updates equal zero for space efficiency but 5.7 also supports this MariaDB at one stage wanted to enable this by default as well and the idea was to also be able to turn it on without ever having to restart your topology so again only 5.7 implemented this as well so what has fundamentally happened is that if you use 5.6 or 5.7 with GTID and you try to attach it to MariaDB say 10.1 or 10.2 slaves this should work as in if it doesn't work you should be able to file a bug with MariaDB and say look this is a problem you got to fix it however if you have a MariaDB 10.1 master and you try to attach MySQL 5.7 slaves this will not work because there is no filter for the GTIDs on the MySQL side so effectively MariaDB server is easy to upgrade to as in if you have a topology and you are going to upgrade the slaves and then promote them to the masters it is easy to upgrade too but extremely hard to upgrade from to back to MySQL you will have to do a dump and restore which may not be one year after so well worth noting since we are in California I guess that is kind of like hotel California right so parallel replication multiple waves you have multi-cell applications from different masters that are also executed in parallel in MariaDB they run this by table in MySQL 5.6 it is run per database across schema you can also run queries in parallel on the slaves again based on group commit remember earlier I mentioned the idea of domains replication domains you can use dynamic domains inside of MariaDB and you can make slaves to be extremely fast in terms of just applying streams of replication data into it so all in replication you can sometimes get out of sync for various reasons some reasons include people changing information on the slaves directly this is more common than you would expect statement based replication causing weird results sometimes you have a trigger they don't execute it similarly on the slave I don't know how many people still bother using my ISAM does anyone here still use my ISAM really but for the three of you here I guess it's time to migrate but if you are master happens to be in my ISAM slave is in MariaDB you may get deadlocks sometimes you have corruption of the binary log itself on the master sometimes you have bugs replication obviously has bugs means software has bugs read all the bug databases and just type replication and you'll find lots and lots of bugs so in terms of monitoring application Prokona Toolkit is probably what everyone ends up having to use PT slave find quite useful to find PTable checksum which will do a replication consistency check so it will execute checksum queries on the master PTable sync will actually change the data that it reads for PTable checksum that will actually synchronize table data fairly efficiently this works across the board across the ecosystem so highly recommended you read the documentation and know exactly what you're doing especially with PTable sync you can also have replication monitoring with PMM and I'm not going to use the internet and fire this up but you can if you go to pmmdemo.prokona.com you can see prokona monitoring and management which does have replication monitoring it also does other things like query analytics that are improved things like spark lines, metrics it's fully open source software it's built on top of things like Prometheus so you basically found the shows of giants check it out you can install this yourself it's fully darkerized or you can check out the demo and I'm sure we'll show the demo at the booth that we have now MySQL binlog if you're doing anything with replications and high availability you probably end up having to use MySQL binlog in your life and you may see an error like this and this is largely because your MySQL binlog version does not agree with the MySQL binlog that is actually sitting in your data directory that's because the MySQL binlog provided by MariaDB is like compatible with the MySQL binlog provided in MySQL and vice versa it's an easy error to make especially when you're playing around with packages because MySQL binlog is not part of the MySQL server package or the MariaDB server package for that matter so this error is quite common, lots of people report bugs about it also there is the idea of the streaming binlog backup server that 5.6 has and this is useful again when you're using tools like MHA so you can use the streaming binlog backup to basically point a failover to set backup server and this does not exist in MariaDB 10 but it does it will come in 10.2 so take a look at 10.2 as well so it's a different thing there and of course the whole transaction ID varies in both so as I said MySQL binlog even though the versions may look similar they may not they're not actually compatible with each other so caveat use the right MySQL binlog to actually the streaming bar has been locked Slave prefetching was something that function actually I believe had first and then you'll also see this inside of Replication Booster and there was this idea of having unidb state changes as well which Prokona server had till 5.6 and MariaDB till 10.1 which would allow you to read rows for inserts, updates and deletes but then don't update them hence the term business usually nothing to do but slave prefetching has gone out of fashion because it's been removed from XRDB and I believe it's extremely unlikely that MariaDB will want to port fake changes as well because you can do introschema parallel replication now and this will actually solve all your problems in terms of warming up slaves tungsten replicator now I had removed this slide before and then I had to put it back again because this product is not dead it cannot die for some reason so it's been around for a while it does replace the MySQL application layer by writing, because MySQL writes the bin log and tungsten actually will read it and use its own replication protocol in what is known as a transactional history layer THL and so it obviously got more network traffic it obviously supported GTIDs long before any MySQL did it also does every genus replication fairly useful and it has enterprise version which I don't talk about here but the open source version does everything I will talk about here and why did I say it doesn't die because at one stage a person was bought by VMware VMware I think EOL the product last May but then last October a bunch of people spun the company out of VMware and decided to continue doing tungsten replicator so this is a company that's very much alive and kicking and the open source product is still around so what is it good for it's great for maybe possibly aiding in migration so MySQL to Oracle replication is something that you can still do then there's Oracle to Oracle replication right before Oracle 12c remove change data capture you could use this for Oracle to Oracle replication so that could be handy it also works for you to push data into things like Hadoop and so forth so it's still a valid product for you to consider using if need be Galera it's basically inside of MySQL it's a replication plugin WSRAP stands for right set replication it obviously the idea is for it to replace MySQL replication but it can also work alongside say asynchronous replication the idea is to make it truly multi-master and active active so there is no idea of master and slave here every node is now supposed to be equal it only works the transactional storage engines we always tend to say it's generally synchronous but the right term for it I guess is virtually synchronous because it's because of the way because it's locally synchronous and then the actual writing and then the committing will actually take place in the table space independently so it's sort of asynchronously done on each node it does make use of optimistic concurrency control where hence the transaction being done locally as all commit time being applied to all nodes of course there is a commit penalty versus a asynchronous replication or even semi synchronous replication there should be relatively no slave lag it does do automatic node provisioning after you've got the second node as well and when codership first started they aimed for about three releases a year it's been open source for possibly around seven years now but this doesn't really happen so much nowadays there are two distributions of it Percona actually B cluster where lots of engineering actually happens within Percona itself and there's MarieDB Galra cluster or being referred to as MarieDB cluster itself and of course naturally this is based on we have PXC for 5.6 and 5.7 and MarieDB Galra cluster is integrated inside 10.1 or it will be also in 10.2 and we see lots of people say they use some form of Galra cluster naturally and there's lots of use cases on the internet so how is PXC 5.7 different Percona actually B cluster 5.7 for one we do not necessarily have to rely on the engineering done at codership so it's done within Percona we bundle proxy SQL so you can do load balancing with proxy SQL it's integrated with Percona monitoring and management and you benefit obviously from the 5.7 feature set in fact the PXC 5.7 came out before Galra cluster 5.7 release as well now another virtually synchronous update everywhere is group replication based on a variant of the Paxos protocol called Manicus it supports multiple leaders it is supposed to be fully self-healing with the LASACD as well as redundancy and it also supports the idea of a single primary mode where you have kind of like what you have with master play replication but and then of course the idea is that they're going to promote a new product line called DB cluster so you have group replication MySQL router and you have this sort of magic with MySQL shell with JavaScript we have noted there are some problems even though this is GA there's sort of no automatic provisioning you get stale reads or nodes large transactions cause nodes to become unusable as well so it's something to look forward to in the future but not quite yet there's obviously NDB cluster where you have the SQL nodes as well as two SQL nodes two data nodes and one management node this is probably the first NoSQL database out there because it had to be it only came with the NDB API it's NDB itself is different to NDB it does have transparent sharding it does have automatic node provisioning it's extremely popular inside the telco space but outside the telco space I can't really say many people actually say they want to use NDB cluster it has grown loops and bounds in terms of better manageability in terms of ease of use but it's still not in a DB it still doesn't have as much wide and varied usage so I'd say this is still quite niche so to speak so quick summary of replication performance obviously SANS have latencies over head compared to local disks DRBD has a performance penalty I'd say replication when implemented correctly gives you the best bang for the buck semi sync is obviously going to be poor over a WAN so over a WAN you probably want to think about async and Galera or NDB it's a great read write scale out but we're going to obviously focus and recommend more Galera over NDB when it comes to handling failure there's multiple ways to think about it you can pull, monitor, look well at then you need to decide what you do about failure once you've detected that there was some kind of failure and your most important concern when you're running databases is how do you do the data integrity so you want to make sure that there's only one master at any given time and this is why you may think about the options of things like shooting the other node in the head the good news is that there are many many frameworks to handle failure MMM the tool that I do not recommend you to use even though it's still available on the internet that is a framework to handle failure you have to control also can handle failure but it's not open source so not so interesting to the audience orchestrator definitely useful we'll talk about it as well MHA also pretty useful I've got a replication manager where it's rolled in into Linux HA tungsten obviously has been doing that for quite some time then the MySQL failover and replication that you can use as well as other tool called replication manager and I put MariaDB in brackets because it used to be known as MariaDB replication manager but now it's just known as replication manager for all intents and purposes it handles automatic failover of your say 3 node or 4 node, 5 node MariaDB server database cluster in the sense that it will actually read the GTIDs and perform a master save failover so you can think of it like MHA but written inside of Go it's one binary and it currently is the only thing that supports the MariaDB GTIDs MHA for example only supports the MySQL GTIDs and it goes without saying that these utilities of course only support the MySQL GTIDs so in terms of the tools that you get as well it's probably worth noting that if they are involved using global transaction IDs you would have better mileage with MySQL and Prakona server than you would have with MariaDB server Orchestrator I'm extremely excited by this because it's a Pashi2 license it's extremely well documented it started life at Outbrain a lot of work was done booking and now it's actually a Github project you can visualize replication problems you can also review, kill long running queries it's written in Go you can modify your topology in the GUI you can move slaves around it's got an API, a CLI and best of all it's also if you go to pmmdemo.prakona.com slash orchestrator you can take a look at it there as well so orchestrator is definitely very useful technology and eventually also handle automatic failovers then there's it's a specialized solution for MySQL application it was developed by Yoshinori Matsunobu at DNA it does do automated as well as manual failover why would you do manual failover because maybe you want to do an online schema change and so forth it has the idea of having one master many slaves but it can also support end-tier slaves master, rows of slaves rows of slaves below that and you can of course use this as a rejection because you have commits on github fairly regularly so I'd say it's still useful, useful software then there's pacemaker where you can use the prakona PRM replication manager where the resource agents are now part of pacemaker resource agents I highly recommend that you take a look at the documentation because this is now not just involving MySQL it's evolving Linux HA solutions it's also worth noting that GTID based PRM also works with 5.6 GTID and greater and not the 10.0 GTID there's also load balances for multi master clusters so if you have these virtually synchronous multi master clusters you require to have a load balance in front of it, HA proxy used to be a good start then there's the cal load balancer, there's also max scale and proxy SQL and one thing I didn't put here but probably we'll have on another slide is called the MySQL router router allows you to route between applications as well as back end MySQL servers it handles failover load balancing it has a pluggable architecture application connection is basically a transparently routed based on load balancing policies so you don't implement custom application so you don't have to have a load load it can distribute connections in a route robin fashion so you really do have maximum throughput with router router is new, I mean this is not the first stab at this tool before this that was also a fabric but I think going forward there is future with router, with the group application and the MySQL shell none of them already be max scale which is a pluggable router that also offers connection and statement based load balancing it's a level 7 proxy router constantly monitors the state of database modes can react to information based on hints or filters and you can load balance together classes with it today the only caveat with this is that anything before version 1.4 is GPL v2 licensed which is good anything after that which is 2.0 or 2.1 is licensed under the business source license which is not GPL v2 it basically says that if you use anything greater than 3 servers you would need to buy a license to use it and that's not open source unfortunately you can see the source code but you can't use it in production so source is viewable but it's not open source licensed so not a good solution 2.0 and greater going forward and in this proxy SQL high performance max scale proxy with a GPL license the idea behind this is it's written by DBAs for DBAs performance is a priority larger deployment stands hundreds of proxies you can rewrite queries you can in a sharp bio schema it's very actively developed and it's not even developed it's got lots of contributions from people at Prokona but it's developed by Rene Canao and he does an awesome job at this there are also JDBC PHP drivers that can also handle failover so you can handle re-dry splitting round robin random host and so forth so these are connectors that are fully aware of mysql transaction states as well as errors this is not true for all languages but for some languages you can get these connectors a good example is the MariaDB Java connector only got load balancing and re-dry splitting maybe in the last year as opposed to the mysql variant having it for much longer so it's clustering part of the solution or problem if you go to Prokona website Baron Schwartz is a former prokona prokonian he wrote a white paper called causes of down time production mysql servers in his survey was human error and number two cause is running a sand so clustering framework plus sand you think more complex makes sense but it will give you more problems so you know possibly use think about using things like Galera think about using things like semi-sync with a framework and so forth I'd say InnoDB is awesome because everybody knows about InnoDB everybody knows and they use it at scale around some solutions may call for using something like NDB but it's not InnoDB so how do you choose competence definitely plays a role mysql DBAs typically have access to replication but they may not have access to DRBD or access to a sand so when there is a failure you may need to involve more people to know what solutions to use in terms of operations DRBD definitely has a longer failover application can give you a shorter failover GTID is amazing in this sense in terms of performance replication has the least overhead compared to DRBD and then sand and of course in terms of redundancy it's kind of nice to use replication because if a server goes away it probably makes not much difference to you real base application is definitely preferred because it's deterministic I'd say statement base is dangerous unless you have a reason to run it don't go with transaction ID it's definitely easy to set up and you can handle failover complex apologies now as well async can give you data loss so maybe you should run semi-sync fully sync is best but it's not free right and multi-tune plays are good so in conclusion replication is amazing if you know how to use it and monitor it well enough I would say simpler is always better the kiss principle works here large large websites run just fine with semi-sync plus tooling for automated failovers in fact you can get large websites with tooling for automated failover plus semi-sync with a 30 second SLA which is amazing GT ID of course helps that because it really does increase operational usability Gala cluster itself is great for fully secret replication and you probably don't forget the need for a load balancer Proxy SQL is a very good solution so Prokona we really do care about your high availability we make all these wonderful open source tools for you to use with MySQL so of course PXE with support for Proxy SQL and PMM PMM also with orchestrator Toolkit, I'm sure if you're a DBA you've already used it going to serve for MySQL as well as extra backup the only open source hot backup solution for MySQL so we have a conference in Santa Clara in April to use this code if you'll get a 15% discount if you feel like 15% is not good enough for you you can email me and maybe I can give you a better discount also we are at the Expo hall at booth 209 I think you should maybe pay us a visit also I think at 2 o'clock today you get t-shirts not at our booth but scale is giving away t-shirts at scale booth at 2 o'clock at the Expo hall they told me to tell you that yesterday yeah we're about 55 minutes in and I guess you have lunch but I'm open to a question or two question thank you by the way I can't hear you it does require application level to migrate from master slaves to Galera and not all applications may be a right fit for Galera so definitely it's not a drop in replacement so to speak for master slaves but if you're willing to change the application we're definitely willing to possibly even help I guess sure if you give me your email address I can send them to you too sure hang on do you want to just write it or type it or not write it good talk by the way obviously I think I'm trying to get a copy so we use RDS so how much of the replication by the RDS right so RDS definitely you use monkey as well so it probably does make you some VR so monitoring the RDS testing 1, 2, 3 you guys are so I don't waste your time I'm going to I guess this is about as close to the face as I want it I was mentioning here that this is going to be more like a birds of a feather but that's perfectly okay my old high school algebra teacher Mr. Dunbar whenever the school was under construction would simply get more animated and tell jokes during this presentation and just articulate a lot and people would still stay awake and learn stuff I should be able to do the gesticulating part because I just had 2 shots of 5 hour energy and 3 shots of espresso so anyways so I'm going to make an assumption as to who you are you are people who have plenty of my sepul experience but haven't really done a whole lot of Cassandra if that's not you I mean don't be afraid to make me think I'm wasting your time and a little bit about me I'm a MySQL DBA I have a little bit over 15 years of experience I have every MySQL cert that there is that will probably never exist again and I really mean that honestly because there are only a lot of MySQL certs kind of got discontinued after a while and there's no way you can actually take them again and I've been a senior DBA at Tivo you know the little 4 or 2 years up until 4 days ago my contract ran out as a contractor so that also means I'm free and well I don't mean I'm free as an outcome work for people for free but I'm a but I'm a free agent again well like I said I'm assuming that most people here are here to learn a little bit about Cassandra and some of the pitfalls and trying to install it and having been told that Cassandra can do everything that MySQL can do well I'm going to I'm going to kind of mention that's not really the case so what's Cassandra? Well Cassandra is generally used as a clustered database for its high availability incremental scalability and eventually consistent data you're probably wondering well that eventually consistent data is the pitfall that most people generally will try to not use Cassandra for sorry photo op and let's see originally I was going to have this little my slides had this little XKCD strip that shows that one guy had a desk saying oh no our database is slow we've got to switch everything over into a NoSQL data store so that we can actually put everything in there and the MySQL guide says wait hold on click click click wow that's fast, what's fast again what'd you do? I just added an index you can use your MySQL database again oh hi there oh ok I didn't know if you needed anything from here and I'm going to confess that that was one of the things that initially turned me off to Cassandra I was also told that Cassandra was just a was just a data key store and I thought if it's just a data key store why not use Memcache then later on I was told well NoSQL actually doesn't mean NoSQL, NoSQL means not only SQL and if you look at all these SQL statements in MySQL they actually work in Cassandra as well but so amongst the pitfalls that I found out well ok that's where I first stubbed my toe if any of you have played around with the SQL command line for Cassandra, sure you can do your select updates, inserts, deletes and so forth but one of the problems you run into is well the first time you'll stub your toe is when you actually tried to do a where statement you can't actually use a where statement in CQL which by the way is Cassandra's name for Cassandra query language without actually using without specifying fields against index keys if you actually have fields in Cassandra that aren't indexed you are not going to go anywhere when you say where that field equals or that field is greater than such and such value now one of the main differences between Cassandra and MySQL is we always think of MySQL as having a master somewhere it scales out great for reads Cassandra however is a no master is a no master cluster and I don't mean no master no master not again but I actually mean as in in most MySQL clusters you assign a master in which you do most of your rights to some of you might have failover strategies that say if this master dies then this master or this slave will take over and will do the entire thing but you are still everything is still writing to one master that master still has to replicate out to the other slaves in a Cassandra cluster you tend to work in these ring architectures where every single node of the ring is equally the master you can write to any one of them and it will just propagate to everything else if you lose any node in that ring the entire thing will stay up now well that's not true straight off the bat Cassandra has a replication factor that says how many extra copies of each node is going to be copied across to another node but in shorthand you can say that replication factor the number of nodes that you could lose before your cluster gets screwed up so I mean just a little bit here what's most people's level of experience with Cassandra okay so you're approaching from like the CQL or the line syntax side then the okay that's a good place to stub your foot and you've probably already learned that if you can't just you can't just query it for any field that you want has anyone here so we've got like one syntax person anyone on the admin side okay two three so I'll just assume that most people are kind of have heard that Cassandra is some kind of data store some kind of database that's not quite an rdbms well it isn't because in an rdbms you usually rely on having you usually rely on on having a table that has foreign key constraints to other tables and those other tables act as lookup tables to values in the original one Cassandra actually relies on actually insists that you well not insist Cassandra actually prefers that space you assume that space is cheap everything is encouraged to be as denormalized as possible I am not particularly used to this and the first time that comes up to bite you in the ass is when you try to create regular rdbms I mean relational tables and you realize there is no join statement in cql the way that you're the way that you're going to end up having to you can still stay by that structure if you want the only way that you're going to have to do joins is you're going to have to you're going to have to do it on the client side you'll pull up your first list of records then have a then make individual calls to to a lookup table if that's what you really want to do but once again Cassandra as a whole prefers that you denormalize everything as much as possible well so one of the main reasons why some people switch to Cassandra and this was my company's main one main reason is that most rdbms rdbms structures actually intend for you to scale out your reads they don't have a good way of scaling out your writes not to mention that a lot of our traditional rdbms structures make the assumption that there's only a few number of agents that are doing reads and writes from your database like for example maybe a few web maybe a few web servers or maybe a few application servers and the assumption is that sometimes even when they're trying to scale out application servers I've been at companies like price grabber that that if you're getting too many connections to the database they're going to get around this by writing a connection pooler that collects all the connections for the intended database and pulls them into a few connections Cassandra tries to get around this by simply letting you load balance your connections all around the ring whether or not it's a reader writes some of the trade-offs that you get in this is what's generally called cap is anyone familiar with cap well okay is anyone familiar with acid is anyone familiar with the type of acid that you don't look off of postage stamps okay so okay well okay so acid in database parlance stands for atomicity consistency isolation durability Cassandra throws that out the window in favor of the caps model which basically stands for consistency availability and partition durability and unfortunately you can't have all three you pretty much have to kind of pick two and let the third one go to crap now consistency is pretty much the same as in the original acid model in which the moment you make a read another application server making the same read should come up with the same result if that's the thing you sacrifice unfortunately that means that you could probably make a you could write something and the next thing that reads it might not be the same results obviously there are places you don't want to do this you don't want to do this for your shopping cart like hey I just added three items to my shopping cart and on the next page it's not there you don't want to use it for banking because it's like hey I just transferred $10,000 to someone else's account and it's not there and you don't want to do this for high frequency trading yeah oh is that what happened so that's where my inheritance went I mean if you really really need consistency you can tune consistency up to the point where you sacrifice either availability or partition durability availability is usually the big one I mean without it you're not really highly available if you sacrifice availability then each time you query the Cassandra cluster for connection you might not get that connection and if you if you sacrifice partition durability this means that every time you destroy a node if you destroy a node and I am not implying that you are intentionally going out to the data center with a shotgun and blowing away a node we all know that nodes just have a tendency to go down on their own but without the data without the partition durability this means that when you lose a node your well the less durable you are the more chances you have of data corruption now even though I say that Cassandra has a problem has a consistency problem most people prefer to sacrifice the consistency aspect that's because Cassandra guarantees that will eventually be consistent I mean I might write I might have two different reads that are returning a completely different result but in a couple I mean in a couple of days if not a couple of hours or even as short as a couple of minutes those results I'll get the same results now this problem tends to exacerbate with the size of the cluster the larger the more nodes I throw into a Cassandra ring the more inconsistent my reads are if I've made my reads if I've decided to sacrifice consistency for most applications that people want their big data this is not a problem this is like if you're recording clicks or if you're recording impressions to a website or recording or recording paths that people take through your website or recording sales or things that you do not need that will not immediately affect your bottom line I mean one good example is at my last company one of the things that we would start experimentally storing is program guide data for television shows if you if HBO gave us a program guide and said this is our lineup for the next two months that's fine somebody at home might actually say hey Westworld at 8 o'clock and then the night after HBO says oh wait no no no we're actually moving Westworld to Fridays well it's not really going to affect that Westwood has been moved to Fridays except maybe he might get a little angry but it's not going to affect his business or financially hit his bottom line unless he was an advertiser specifically depending on Westworld the point being that eventual consistency is not a big standard killer a lot of people would prefer for that eventual consistency to as a trade off for being able to be able to scale out users most databases make it very hard to scale writes so what Sandra does is you can scale continue to scale writes by simply adding more nodes these don't have to be expensive nodes either these can be totally commodity nodes that you didn't spend a whole lot of money on any any DBAs who use NODB I'm going to assume that I was hoping that would be everybody but yeah so one of the problems well okay does anybody know what a storage engine everybody knows what a storage engine is okay so one of the little one of the little caveats of NODB is that when you are you is that all tables default to using clustered indexes clustered indexes where data is actually physically ordered on the block medium in the order in which the key values are so this works best when your key values is just a single auto incrementing integer column because you'll just simply keep adding on to the end keep adding on to free space if you're if you start I mean it's I mean it's in almost every single optimization book don't use bar charts for your NODB primary keys don't use don't use compounded keys for your primary for your or don't use composite indexes for your primary keys because the moment you start adding in low value valued items there there's going to be a little bit of rewrite in order to physically order that row in the same order as what the key is um Cassandra flies in the face of that so instead of using a clustered index it uses a hat it's going to determine which note which nodes that row is going to live on by by hashing the key by hashing the primary key for this reason and since he can only do select where is based off of key values it's highly recommended that you actually do use composite keys as your primary key now I used to think well what's the whole point of having a cluster while this is being off to an aside but in case you actually do come across people who do use a clustered a composite index for their clustered primary key one of the things you save is that cluster key doesn't have to load into memory you can actually perform your searches directly on the I mean just off of a hard drive search itself this tends to save a lot of space but at the same time like I said you really should not be using anything other than auto incrementing integers as your primary keys on NODB that was oh let's see here so other places that I've stub other places I've stub my foot is how many people are used to how many people actually program connecting to my sequel like maybe in PHP or Pearl you're probably used to receiving a list of rows that you get back like six rows affected or 20 rows deleted unfortunately I hate to tell you this but Cassandra does not return meta information like that when you perform operations against it just pretty much tells you it's succeeded I can see where they're going with this because they don't really because the operation succeeded on one node and it's supposed to become eventually consistent with all the remaining nodes of Cassandra but it is not necessarily there that instant concurrency most databases like to have some kind of concurrency management Cassandra claims to be very highly concurrent but that's because it's so denormalized you should still design most of your most of your reads to try to not hit to not modify the same records as other agents at the same time so if you're used to doing bulk loads and people are used to doing bulk loads in parallel you really shouldn't you should try to avoid that Cassandra is generally Cassandra is considered schema less which basically I mean in most rdbms where you in most relational database models used to having a hard fast schema everything has to conform to that schema but Cassandra or should I say most node sql solutions will have a will will encourage either a key value model or a document model there are other models that you can work by but document model is generally what you see in xml or json so you can have you basically name your fields but these fields don't necessarily have to match up they can be I mean you can have a name an artist name favorite artist and phone number whereas somebody another field might suddenly have something arbitrary like the height of the grass on my front lawn this actually this actually makes treating it like an actual rdbms problematic still most people I know who have employed Cassandra will stick to a hard fast schema and say I don't want to add anything else to this I don't want to make the schema change unless there is a good case for it but being able to have a schema less model when you're storing things as document related stores actually lets you add extra fields on the fly that is not actually within the scope of this because I haven't actually had to deal with putting Cassandra in multiple data centers before in fact when testing across different availability sounds in AWS I've often come up with some very bad latency issues so I've been meaning to read up on that most of this is problems I've had with dealing with Cassandra and to get other people's stories as to problems that they've had with Cassandra generally anywhere where you had a problem in changing a schema in an rdbms having to wait for an entire weekend and then coming back to it even though for me most of changing a schema usually involves using an outside tool, a third party tool such as PT online schema change or TokuDB actually is a storage engine that will let you do a relatively quick schema change and when I say relatively quick, well okay not quick I mean without disrupting without disrupting walking of the database itself and usually the way this works is that they actually just create an entirely new table with the new schema change copy data over and then once it's ready to go they apply all the deltas that have occurred to the first table and then rename the tables over but most of the reasons why people change to Cassandra is the fact that you can scale out users for cheap and scale writes for cheap what does Flyweight do? you know so far I don't I don't know whenever I've had to migrate Cassandra or migrate things I kind of just dump out everything from Cassandra and use a homemade ETL tool to load it elsewhere Cassandra isn't I'm going to say Cassandra isn't for oh I'm sorry this guy in Galera every node keeps a copy every node keeps a copy of the database just like in ODBE I need to make sure that there's enough memory to hold that minimum all the indexes and optimally hold the entire database if I can pull it off if the size of those indexes start to grow I'm going to have to add more memory in order to accommodate that whereas Cassandra everything is sharded you're holding your own shard plus however many other shards have this factor so I only have to have as much index memory as the shards that I'm holding well because in a document store basically in practice I'll still stick to a strict schema but in a document store whenever you add a new field you don't need to add that new field to every single record that you have in there you can add a new field basically to a single record I have not tried that before it's just simply in the documentation I think well that sounds really dangerous do people ever start querying for anything well okay I guess you wouldn't play so let's see here that's pretty much the ways which I've got I have in my notes here joke about HA joke about keys joke about no master actually I'm not going to pepper jokes into here unlike a lot of my sequel a lot of my sequel setups Cassandra has no single point of failure well like I said that's not necessarily true because the first time I had to set up a Cassandra for evaluation for my company I ended up putting it on a single note which you totally can do with Cassandra you can just simply say put it on the single note and I guess eventually comes back to you when your boss says hey I thought you said this was no single point of failure yeah in general the entire row will live on a single will live on a single note plus whatever notes you set the replication factor too what determines which note it ends up living on is its primary key the primary key has some kind of hash function which I'm not sure what the hash function is specifically but it generally will try to guarantee that the rows are evenly spread across your clusters if for some reason you decide to add a new cluster and there are two tools like no tool for doing this then Cassandra will immediately rehash or try to restart rebalancing rebalancing your rows in order to maintain an equal distribution don't ask me how the rebalancing works so yeah to be honest if there is I have not tried because in fact actually I kind of consider the rebalancing to be a desirable trait rather than I haven't come across the concept of V-Nodes in trying to do my migrations so but if you want to like give a brief definition of V-Nodes I would love to hear it and I think I saw someone else do I didn't really think of the documents as being version I mean you have no way of saying it specifically like JSON or XML it's just document based as in I mean it's it's not strictly keeping you to a specific structure you're not necessarily you don't necessarily guarantee that unfortunately like I said Cassandra is eventually consistent but it's not totally consistent it kind of throws the acid model out out the window a lot of atomicity in Cassandra well if you want to do a single node setup of Cassandra it's actually relatively quick you just set it up, fire off Cassandra and you say this and you have a running Cassandra cluster if you actually want to add it when you're ready to add another node you install Cassandra on that but during your fire up at the time that you're starting up you say I actually want to this Cassandra node off of this off of this IP address of the original one you set up and I mean once it's actually pulled the data and communicated back and forth that it's now you now technically have a two a two a two node cluster well technically I don't do that I mean I do set it up as a seed but I use node tool to say I want to add this machine as a second one and it automatically adds in most of the seed most of the seed metadata and then it will catch up and rebalance and you can keep doing this to keep adding new nodes to it but if you're setting up one node it's fast it's easy it's just like installing my sequel you accept that there I don't see Cassandra in the yum repositories you generally have to go to data stacks or Apache site get the RPM for Cassandra and install it and once it's installed you just say I want to run cql from the command line and you get this prompt it looks just like the mysql command it looks just like the mysql prompt it's just that there are a few there are a few differences so one of the things I kind of had on my slide eventually there will be slides once I retrieve my laptop my slides kind of included a cheat sheet of things that you would normally do in my sequel you can't do in and how to do them in Cassandra like in Cassandra you don't call them databases you call them key spaces and instead of create database such as you say create key space such and such and one thing which I really got to me is in my sequel I'm so used to saying show databases in Cassandra the same thing would be select star from system schema key spaces well that's pretty much the same way Oracle works most of the things that you want to know about metadata you want to know about the database installation you're going to have to get from some kind of system schema tables but otherwise for the most part everywhere that you would normally use the word database you just use the word key space everywhere that you would normally use show you would use describe as just a general rule of thumb on my original slides I had a cheat sheet of things you would do in my sequel how to do it in Cassandra yes and unfortunately not to a whole lot of success we're kind of trying to do that with Docker these days but ultimately some of our Docker containers suddenly stop running Cassandra and we and we have to start the Docker container up again in fact actually I totally missed Thursday's Docker presentations which I really wanted to catch because I have absolutely no idea why Docker hates Cassandra and I'll just say that that statement Docker hates Cassandra and one of the things I had told my boss I mean I still speak as if I'm employed but I wasn't fired for this I mean I wasn't fired and it wasn't for this reason I just simply said we cannot virtualize Docker and we cannot virtualize Cassandra and that kind of earned a very dark look from my manager I wasn't let go because I was fired my contract just simply my recruiting company just simply said after two years if you're not converted you have to leave but anyways yeah well like I said the pretty big downfall that I see as the virtualized environment stops running Cassandra it stops talking to the other stops talking to the other to the rest of the cluster I haven't figured out why yet now so I haven't tried it on any I haven't tried it on VMware or virtual box but I have tried it on Docker there's a slight performance increase if I run on bare metal basically there is a patch he makes this nice tool called Cassandra stress for testing for hitting your Cassandra cluster as fast as you want you can specify how many threads you want to hit it with how many records you want to do it with I generally on a three-node Docker cluster of Dockerized on Apache M3 mediums I mean I can maybe about 16 threads at 10 million records of 384 bytes a piece before it start before one of the nodes decides to throw up on me that's also something I would recommend checking out if you want to compare my sequel against Cassandra Apache makes a tool called specifically Cassandra dash stress to hit your Cassandra cluster as hard and fast as you want even though I call it a cluster I would encourage most people to actually just start off with one and start scaling start scaling out from there if your replication factor equals one then yes that will be the case you can set something called replication factor that says how many other nodes a shard's data will be replicated onto and generally if one that way so if you have replication factor equals three then you kind of guarantee that at least two other nodes will have your will have that data as well I mean it might take a little bit longer because if one node goes down Cassandra is automatically going to start trying to rebalance start rebalancing everything and during these times of rebalancing the cluster starts to slow down oh yeah and likewise when I was talking about consistency earlier you can also set a quorum level on Cassandra especially if you have multiple nodes quorum level if you actually demand consistency out of your database then you can actually set it by raising the quorum level this says how many results you must get from other nodes that agree to the same results before you actually before returning that result to you but unfortunately the higher your quorum value is the slower your databases your Cassandra cluster is going to perform as it keeps going to these other nodes that have the same data and make sure that everything matches that if you're looking for speed you can just simply set it to no quorum value and you can just get those values back instantly is this related to sort of like relational relational keys or not that I've seen for now this is sort of a discussion of all the mistakes I've made in Cassandra and comparing against mistakes other people have made as well as originally there was going to be a focus on a cheat sheet of everything you would do in MySQL and how you would do it in Cassandra and since that's something I would not do in MySQL that is have since I wouldn't have child documents in MySQL there's really no having child documents it's not something I've explored in Cassandra let's see here I also happen to have these cheat sheet notes for Docker like I said Docker has blown up in my face when it comes to Cassandra so I've talked about trade-offs between consistency and latency yeah I have not actually looked at why Cassandra is written in Java personally I have not had a whole lot of problems with Java in fact actually most Java programmers I know usually say if they really need speed adjustment they can just compile Java I mean they can compile Java before instead of interpreting it not necessarily well okay I mean kind of going off topic here I started noticing that everything I wanted to do to administer AWS was written in Ruby and I had no idea why that was and I just figured oh because somebody wanted something to do in Ruby and whenever I come across any I mean I don't really pay attention to the language something's written in because I think somebody wanted to write in that language and it just grew and it just got more followers and then grew amongst the community of that language community no but like this gentleman said I can see how it starts to step on Docker's on the virtualized environment's toes and after a while they just simply have to say we'll fix it but that probably does mean they'll have to go back to the Java roots and address what's blowing up there we suspect it's specifically the way that Docker handles network connectivity Cassandra generally tries to communicate with its other nodes on port 7199 and the first thing we notice is that suddenly we can't see port 7199 on some of the other Docker nodes oh since I haven't run anything else on Docker it's just Cassandra well for most of my tests it's open but I'm sure that there are ways to do it and even if there was no way to do it you can always write your own SSH tunneling system to encrypt your communications going across to the other nodes I mean when my SQL bad practice that I guess I could say will probably be picked up by Cassandra is that most people do not have I mean in most my SQL structures I usually see that there's no root password for my SQL I mean anyone who comes in can do anything they want and they rely on the fact that the operating system has been secured in that you would probably demand that some kind of SSH key from any user that comes into the that comes into the database server itself and you would probably have your firewall would probably have a list of other nodes that only a node can connect to so that no one can just come in with a stray my SQL prompt from just anywhere and start executing system or database root user commands but like I said since most people are sure to do their security on the operating on the root operating system side I kind of predict that nobody is going to try to set up any kind of encryption between Cassandra nodes well is there oh yeah oh by the way hey George and I guess that's that's pretty much all I have there is once I get my I mean there will be slides available for this there's a little cheat sheet on those slides for everything that you would do in my SQL and how you would do it in Cassandra and generally I was hoping to answer any questions about any questions about those as well as how what's the difference between doing it in my SQL on some on the design on the design level versus how I would do it in Cassandra and how I've had to make those mistakes so you don't have to yeah so far no not yet I mean like it like I said I know that they do exist if you actually have two things trying to update update the same record at the same time you will encounter a race condition and that's actually a little bit more race conditions for updates are less performant in Cassandra than they are in my SQL so if you actually have a lot of those if you actually have a lot of those from several parallel scripts running you're probably better off with my SQL I'm sorry what so what if I need something that is absolutely absolute acid compliance then I would stick with my SQL if you can actually stick with the cap if you can actually if you need to scale up and you can live with the cap model then go with Cassandra no but we actually have someone giving a MongoDB talk three seats behind you after I'm done here so alright well thanks for showing up and next up is Peter Zaitsev hello everyone it's time and today we'll talk about my SQL and MongoDB now before we do that let's do a little experiment so which of you guys are running MySQL somewhere in your system and by MySQL I mean MySQL, MariaDB, Percona server, Amazon, RDS MySQL all the others okay now hold that hold that and then if you guys are running MongoDB as well left hand okay look at that quite a few of you actually running both of your technology at the same time you know thank you for participating now I think if you look at this presentation before we talk about MySQL and MongoDB you probably should ask a big question right what kind of open source database technology you should be using for your application or maybe even what kind of database technologies open source and not should you be using now in our world we see a lot of companies which increase adoption of the open source software and then you can speak about those as companies have open source source approach where we would use open source software first and only use proprietary software then there is no good solutions for open source solutions available for problems we are trying to solve now why would you use open source well in reality as I talked to folks the big reason is the cost open source software is typically allows people to reduce the cost as well as reduce vendor locking increase flexibility and provide all kind of other cool stuff now this is not just me imagining stuff right if you guys take a look at the DB engines which is the well known source of a kind of competitive data for different database engines they provide the graphs about the open source and closed source the database and you can see by their methodology the open source license software adoption has been growing while closed source have been going down even though last years this process kind of have been a little bit more even for many years the trend is quite clear but I think I find another piece of data is even more interesting there is if you look at the different technologies you would see what were for technologies started to be adopted long ago like even things like relational databases there is still a lot of market share especially in terms of dollars held by commercial databases now if you look at some newer technologies like for example white column stores such as Cassander or graph database the key value stores the vast majority of those is open source of database so if you look at the trend and momentum new stuff tends to be open source now we see another trend which is quite interesting is what you would use multiple database systems in one environment and then you would use the strong sides because really there is no single technology which is like a silver bullet which is absolutely magical and that everything and absolutely well because when you think about software engineering it's all about the trade off right you take one road you will have one benefit another it's another and you can't really now before we get to the next one I totally forgot to tell about the t-shirts why did I put them here oh my gosh how could I forget well these t-shirts is t-shirts you can get by asking questions and please please be active because I don't want to carry them back right I have a bad back and I don't want to carry the t-shirts on the conference so be active okay now how can we see multiple engines being deployed now one approach if you see a lot you would have some main kind of operation store if you mean data store plus some access to services right another approach which is common those days where instead of one kind of monolith application you would have microservice approach where each of those microservices may have their own data store and in many cases the development teams handling that microservice are empowered to figure out what kind of data store they would use which targets which best assist their microservice application and there are some other architectures which are focused on the kind of data flows right when you get all the data put in some sort of data bus right there is like different names of that something like pure like Kafka where multiple systems can read and write data from that data bus and process that appropriate so for example in MySQL and that's something that we see so for many years you could see something like this you would use MySQL as your main data store you would use something like memcache or edis for caching anybody does that many users of course then often you would use something like elastic search for search MySQL has full tick search but it's kind of not very good and then you can use something like Kafka if you want to elastic search for analytics right pretty common data part now when you look at the data store there are two kind of big approaches which you can be thinking about one is relational which is where you use SQL as a language and another is non-relational it's often also referred as NoSQL but NoSQL is not a single language right there is no single language or data model which is called NoSQL or non-relational right it is many different models which exist out here and they are really many of them right and some of them are pretty standard some of them are quite exotic the most common ones which you would see is something like a key value store like memcache a white column right as a Cassandra here like MongoDB or couch basis and graph as Neo4j right I would say those are probably the most common NoSQL data model at least in the operational data store ok so now why do we talk about MySQL and MongoDB specifically well actually there are a few reasons now if you look at the same engine rankings trends these are the top 10 systems here right now if you look at this case if you look at the open source relational database that's MySQL and the top non-relational database the document store would be MongoDB that is why I believe it is fair to talk about them when they compare so that is one reason I think if you think about that both MySQL and MongoDB have been really focused at their start for ease of ease of use right ease of use by developers if you would go and talk to MySQL founders they will say what their initial goal was making sure developer could get running with MySQL up and running in 15 seconds right well that is probably the same what applies to MongoDB but it only was 5 minutes instead of 15 instead of single instance you want to be able to deploy the MongoDB cluster in the cloud right but at the same time the point of being able to get stuff going very very quickly is very important for those technologies well and the last thing is these are actually the two technologies which we focus at the corner provide support and other services now what do we do for for MySQL and MongoDB well in addition to services we really provide a lot of software both for MySQL and MongoDB MongoDB ecosystem right so we really are working hard to contribute to both those ecosystems through open source software the other important thing myself I know much much more about MySQL and MongoDB so I warn you I will be probably slightly biased towards MySQL but you guys are welcome and encouraged to call my bullshit right if I say something wrong about MongoDB please correct me I will appreciate being educated and an important thing now I am going to talk here about MySQL as relational database now in reality MySQL is not only relational anymore but in MySQL 5.7 there is a which is called document plugin X or protocol X interface which allows you to usually to use MySQL as document-arranged engine as well so it works pretty much very similar to MongoDB with only kind of exception to support transactions and doesn't support service but this is not something which you see very widely adopted in MySQL so I prefer not to talk about that because I believe until something was really barrel tested in production we don't really have a very good feel is what actually works and what actually doesn't okay choosing MySQL and MongoDB I believe you need to consider a number of things and these are things I am going to go through one by one and talk about them the first one and I think the most important is team preferences and experience as the saying goes there is more than one way to skin the cat there are more than one solution to problem exist and frankly I have seen so many times when for exactly same applications for example MySQL guys would be saying oh my gosh it's impossible to use MongoDB here and then we have a very good list of reasons but the other team would actually build it very successfully using the same MongoDB and I see it over time in other circumstances MySQL and Oracle oh my gosh there is no way this toy database as Oracle could handle our workload as MySQL could handle our workload so that is very important and I think if you are building your application if you have existing development development team that is a very important strong preference if it reasonably can be built with technology of choice let them let them do that now what are the typical benefits and draw prior from that well Maya which can be based on the team experience with MySQL that is a very mature technology so people who prefer for safety something where things have been proven over long period of time they would kind of like MySQL there is an SQL standard which is again has been here for even longer time it's kind of easy to move to other relational databases right if you you know if it doesn't work chances that you can move to you know Postgres or Oracle or something else you get the transactions and really MySQL gets you a lot of choices both in terms of schema design you can be very creative there are hundreds of configuration options to choose from there are many different open source tools to play with right many many choices in MySQL MongoDB comes from different background I would say that is a very modern and dynamic many developers hate SQL have you ever guys seen developer who hates SQL yes you have right so for many folks what the fact that you don't need to read this nasty stupid SQL is a benefit in fact you don't have to be thinking about your schema and have dynamic schema is a plus and then also often here is what the fact that you can actually use SQL to run the complex queries is disaster right because complex queries means complex problems did you ever try to optimize the SQL query which takes 15 pages when you print it out and you did that well I mean I have that's not that's not a pleasure right you can do very seriously complicated stuff with SQL it's easy to scale and especially if you can frontend application developer use javascript really MongoDB plays much nicer than JSON and integrative javascript kind of mentality wise compared to my SQL another one I think is important is development pros and life cycle right what are you thinking about here is it you want the faster development pace or are you thinking about more more control right and I think you can see is what the data always have schema right in the end wherever you have that schema control in the application or in the database that is a choice and often driven by what is your part is that what the data belongs to one application and in this case schema control in the application actually can make sense or do you have many applications which are kind of maybe written by many different teams which work in the same data in this case maintaining data schema across set of many different applications becomes very hard application development time and lifetime and I will mention why those are important in a second now if you look at my SQL well relational database requires more planning and control you have to design your schema you have to do some schema change maintenance operations to other columns or change the types of other stuff but it also makes it easier to use the data from many applications because it's kind of rigid right and it's sort of documented in essentially in a database now what we see in my SQL is there are many successful applications which have been really going through very long life cycle like 15 plus years and for that time we are able to maintain all those kind of changes to the schema without causing some complete chaos where application versions evolve and you have a lot of different objects and database which are from in terms of MongoDB when you look at the teams which are having focus on a very high development base in this case MongoDB is great because it does not require you to think so much about the schema on the database level of application developers can really design it and iterate very quickly and another important thing is it has a clear path to scalability for the shorting with my SQL if you look at that typically if you want to implement shorting compared to your single boss there are a lot of changes in terms of data model I would also point out that the optimal data model depends a lot on the application and the team experience again as I mentioned there is more than one solution for the same problem which often exists but what are the benefits and drawbacks here with my SQL you get a relational data model it is easy to have a relationship between the objects in the relational schema it is often you can normalize your data so any kind of change like from normal form to others can be really done only in one in one place avoiding duplication and your result from any select statement is going to be a table with benefits and drawbacks but then again depends on your application in MongoDB it is focused on documents and the documents can have a complicated structure you can have one document to embed another document inside it or even arise of other documents with benefits here it is very easy to represent the JSON data which is very common for application and you often can do it avoiding joints in some cases you can design the data in a way many like simple joints actually can be avoided and the clear result in this case is list of objects again which can be very complicated structure which often can be better suited for some applications then then flat table let me show in this case is very simple the case for model design and how it looks in MySQL and MongoDB let's look at very simple question you have a people and they have passwords most people have just one password some people may choose or have none and there are some people who have more than one password if you look at the relational schema design you probably have two tables like this where you would have passwords which refers to the identifier of a person and it allows you maybe to have one to many relationships now it allows you through SQL to do all kind of queries relatively easy to count a number of people you can check validity of a person password you can even find who doesn't have a password here through your SQL interface now in terms of MongoDB you actually have multiple choices of course you can all go ahead and say I want to interpret my tables like MongoDB collections and you can actually flatten relational data to non-relational in the same way just convert columns to attributes but that is now how people do that because joins are complicated to non-existent in MongoDB and because of that you would do something like this you would say hey let me build the collection of people that we will embed for each person the document which corresponds to the password and for this model people and passwords that works very well why? because passwords tends to belong to only one person now this model becomes more complicated then it's not a password that something which is shared by multiple people and may have their own other attributes which will require us to have a lot of duplication or have some sort of object identifier stored and join an application or use some other things now if you look at MySQL and MongoDB and now if you're interested in what you have terms which are similar but sometimes different we can refer to the database of schema as a database in MongoDB instead of table we have collection instead of row we have document which has fields instead of columns the primary key they're called primary key now when they are speaking about doing the join or running subquery in SQL language it's about either using embedded document or storing the document identifier and using Lincoln access language for our technology for SQL we have a structured query language SQL that's what it stands for the interface for MongoDB as well as actually for other database generally called CRUD which tends to create read, update, delete more and more CRUD is a family of languages it simply states that the language or interface provides some way to create, read, update and delete document it's not as prescriptive as SQL which has a very exact standard may I make sense ok here are some of the examples how they would look let's say if I would have here some sort of table or collection of books that is how the inserting adding the new data would look like this is how you can update the data comparable that looks pretty similar this is how you would do delete by publisher ID and publisher name in this case respectfully now in terms of select actually there are a lot of different selects in SQL so I'll show a different example now in terms of select you have to that is an example compared to MySQL and MongoDB now for my preference actually where I like a lot the previous instance dealing this kind of I find this SQL syntax more more expressive than you can do a count for this function and there is also grew by ability so it's done through normally a grew by in SQL in MongoDB that is stuff done for the aggregation framework which has a very good size because it's powerful you can do a lot of stuff you can run in parallel in a short way unlike MySQL we just kind of limit it to a single node but it also can be more complicated to do some stuff let's now talk about transactions and consistency now when you think about the transactions you often use the terms ACID which is spent for atomicity, consistency, isolation, durability now when you read up of the MySQL and MongoDB you will find a lot of different attitudes towards that some people say hey you know what MySQL support ACID transactions MongoDB doesn't I would say hey MongoDB is also ACID but it doesn't do the transaction it does it on a document level so I don't want to engage in a theoretical discussion here it's supposed to be but in the end MySQL would allow the ACID of a transaction of arbitrary size you can modify 10 million documents and commit them from many tables with MongoDB is ACID on the document level so modification of a single document is ACID for consistency and so on and so forth now what does that mean in practice and that is important to understand for people moving from your kind of conventional database to a non-relational database the first one is if you are doing reads then reads are always dirty reads when you are doing a read and have a large number of documents you will not get a consistent view corresponding to some point in time as you do in relational databases you will get go to read where different documents are updated at a different point in time now what is also possible which I know have been very puzzling for folks coming from relational background there is a low probability but there is a chance that you can get the same document more than once in the result set the idea is if you are doing the read by the index and the document is updated so it is kind of jump from this position an index somewhere here you can read that document twice so in many workloads that is not a big deal in some other workloads that can be kind of dangerous when you want to update all your documents and as you update you read the same document and update the second time that can be slightly inconvenient now performance in terms of performance it is kind of hard to compare directly in that of course depends a lot on application architecture now especially a few years back you would have a lot of benchmarks where MySQL is MongoDB is much much faster than MySQL the idea though in this case is often it is run with a very different either durability settings or configuration for example if you start MySQL and MongoDB by default MySQL offered would especially a few years back would allocate just 8 megabytes for its buffer pool for its cache where MongoDB will say oh that is wonderful it will take a full advantage of it or for example where MySQL each update would be the durable case transaction which is flushed directly to the disk where MongoDB especially in the earlier versions would just say hey you know what just send it to the server and as long as it is received that is fine right so very very different durability settings and if you guys compare that for your application to to ensure you cannot compare apples to apples now it is also interesting I find interesting this right this is a Mark Colligan does anybody know who Mark Colligan is now well this is a guy from Facebook who is doing a lot of the performance engineering out there and he has been doing the test for MySQL and MongoDB using different storage engines and in this case if you look at that he measures both the throughput he is able to to get for a storage engine as well as how many CPU is being used for for operation and in his case he finds what in terms of the CPU usage for the pure transaction where MongoDB was the most efficient and where Tiger and again in his case much much more CPU for pure transaction but again take this with a grind of salt better for application Mark was working with a lot of other results you would find which will say oh my gosh MongoDB is so much faster okay the next item you should be thinking about is scalability right for scalability typically for application developers mean how do I take my application and scale it from 100 users to you know maybe I need 100 100 million users right now, oh okay they started okay now we are going to play who is speaking louder okay thank you for the laugh I think you got a message okay so when you think about the scalability what not every application really needs to scale to 100 million users right and in this case we really need to understand to what point you guys need to scale and how that scalability path is going to look for you are you going to scale if in a single system I do have to kind of shard and kind of scale across many many systems and what does scalability looks for you are you scaling reads or are you scaling writes are you scaling kind of data size or always kind of is not always the same right the proportions can be different now if you look at the MySQL I think MySQL especially more than one scales very well in the terms of a single system you can find MySQL working well with you know more than 64 cores right and you can really get MySQL server more than a million simple queries a second single system well that is kind of an extreme environment but I see a lot of systems out there which are serving many of tens of thousands of queries a second of somewhat like moderate complexity now we also know what the MySQL can be used to build what I call a Facebook scale application well I know that because Facebook uses MySQL right and we also know what adds relatively easy to scale for medium size applications right you can scale things through scale reads for replication right you often use something like memcache or ready to supplement through discussion and so on but we know also what the shorting is quite painful in MySQL right anybody here who loves shorting MySQL applications oh okay yes oh that sounds like sarcasm right yes well shorting means pain in MySQL right so that's just how it goes and that comes from the fact that MySQL was born in the time that wasn't just a big deal right and there was no a lot of applications like systems really doing shorting at the time in MySQL in Mongrelia besides really scalability was focused on some very early versions right so shorting was pretty early on and frankly if you have a relatively simple interface as MongoDB has now it's much easier to do right so in MongoDB I would say you have a very good and replication and shorting which great framework for building hybrid NHR and it really requires less experience and a lot less time to get going compared to MySQL ones okay so what about the database operation these are all the things which developers tend not to think about a lot that is something which DBA, sysadmin, sre right often have to take care of now in this case I would look at this this way in terms of MySQL I think there is a lot of focus on the flexibility and frankly this flexibility is often kind of organically evolved for many many years because there was no good solution built in well to give you an example there are many many choices for how you manage MySQL replication failure you have like Colin spoke earlier today about there is like MHA, there is PRM there is orchestrator a lot of them why well because there was no good replication failure solution built in MySQL to begin with in MongoDB in comparison there is one way to the replication which has an automated failure over which is integrative drivers built in and that is what everybody is using in the right extent so in MySQL there are many ways to solve the same problems and many open source tools available which gives you a lot of a lot of flexibility then in MongoDB it is often one way which is simple it is based about kind of virtually zero administration often we see people just set up MongoDB and kind of let it run without doing any settings that is how we know there is about 2 million of open databases right available in the Internet in MongoDB so but at the same time it is very easy to run it right another interesting thing is what have not been as large community of I think open source third party tools developed from MongoDB at least of yet there is much more reliance on the on the kind of vendor specific tools like an ops manager for example if you take a look at the MySQL community and how many folks are actually running MySQL enterprise monitor right which is kind of comparable to MongoDB ops manager it will be much much smaller fraction compared to folks in MongoDB ecosystem reliant on MongoDB ops manager another thing to consider is the license right so there is actually an important difference to the license in MySQL and MongoDB MySQL is available community edition over GPL right and there is a property license you can also buy from Oracle and then for MongoDB it is AGPL and property and that's really a actually places a lot of restrictions you'll find what a lot of companies they don't want to touch AGPL because of kind of complicated restrictions that can place on on the product which uses this technology this is AGPL I think it's for example why I believe there is no official MongoDB service on Amazon right there is RDS for MySQL, MariaDB or Postgres right there is elastic search or radius or mempage but there is no MongoDB why well AGPL really would not allow Amazon kind of to patch it and then keep the changed services in house if you're kind of distributing that distributing that now I think it's well good to know what both MySQL and MongoDB they don't have a more liberal open source license out there if you're looking for even more liberal and kind of hassle free license then Postgres SQL really is convenient in this case because they have what's called permissive license or you disclose your changes try to place any kind of restrictions for application you're using okay next thing now another thing I wanted to talk about is what kind of problems existed for MongoDB but they have been fixed one is it was discovered years back and wrote a lot of them I would be very poor write performance with storage engine right this was kind of same same sort of deja vu with MySQL like MySum there would be table logs right MongoDB original version has even worse kind of global log for writing to M-Map V1 collection but that was fixed even with FireTiger or with MongoRock storage engine which was created by Facebook. Simplized to poor disk usage that will fix it as well no way to validate document that was another issue right as I mentioned there is always schema in your application and in many cases you would like the database to do at least some validation in starting MongoDB 3.2 you can actually implement a way to do that for validate the framework and there is no join well there is still no join per se but in starting MongoDB 3.2 there is a limited support of joins in the aggregation framework by limited time in for example it doesn't work cross shards right that's like one hardened is a vice shard is so kind of painful MySQL is exactly because of joins because joins in shards are very very painful yes you have a question well I would say die hard relational guys continue using die hard relational databases but I mean I don't see that a lot because I think it's really not very serious I would say naive relational guy approach to MongoDB just conversion all your tables to to collections right and trying