 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. Use this index, organize things this way so that if you do know a way to optimize a query and the optimizer is picking up, you can just embed it in a comment in your SQL statement and away you go. 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. Oracle databases, yeah, you go in there and you change the value from 17 to 34 and something's back to 34 and you haven't touched anything and you realize there's stored procedures, triggers, and a whole bunch of other stuff. 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. I can see having a boss who's going to run his fantasy football league and you let him upgrade the buffer pool so he'll run a bigger sort on Friday to figure out who he's got that on. 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 to do joins in application that is a rescue for disaster because you get you get all the downsides and essentially no upsides ok the one t-shirts no you you are fine you are way too much ok now let's look at couple of examples I think we see a common seat of MySQL MongoDB online e-commerce many of those are built using MySQL right some of the popular frameworks like magenta open source e-commerce application uses MySQL and why it's important because well e-commerce often use a full feature transaction right that is very updated multiple data pointers in the same transaction is kind of very important typically good feed for those data sizes unless you are Amazon or something right typically is not so large for e-commerce right even 10 million of items you are selling is not a big deal for modern systems often you would have a long application developer is kind of iterated over long period of time right and you often would have many applications are used by many kind of different models different things talking to relational database directly right and I think that is a good fit now another example of a good fit now for MongoDB would be something like an online game back end for online gaming MongoDB is is pretty big why well because one thing with games is if they really become popular they can grow very very fast right and in fact you can scale easily through the shard and without significant upfront investment as you would have with MySQL is great now in many cases the data in this case is used by one application oops what was that your pretty much game is kind of even embedded in that application right so in this case using MongoDB works very well and frankly the data in that case is very complicated right like your character handle sword and that has a whole bunch of different properties it fits in a document model often much better than MySQL and also especially because that is one application you will think for the document level consistency often enough and also in terms of space of development you find many of those games they are very active development development kind of is very small right or on ill okay well I would say in this case right because it becomes a complex application logic that's often I see happen in the application that transaction right and that's typically the database is just held as a persistence as a persistence layer right so it's it's not relying on the transaction in this case right but but you are right right I mean in this case with a document level kind of transactions you have to be thinking carefully how exactly you manage those but I think in this case I would mention something on this which is interesting I think in many case you have to also understand the cost of this transaction remember I was working with years ago with one online processing right those guys are using my some tables I was thinking how guys you are processing credit card transactions and using my some tables that makes sense for me and say hey you know what it's not a big deal right our average transaction side is 10 bucks so if once in a blue moon my skill crashes and we lose couple of transactions people call support and we lose kind of 20 bucks and that's it problem solved so well again that was the choice and it worked for them but I think it's good to also to understand right if some events are very rare you maybe just be able to deal with that in a my skill world still many of us rely on my skill as in cross replication they choose not to enable in a semi-synth replication because semi-synth replication is expensive right but they say well if it happens to crash and I lose couple of transactions failover to slave so be it hopefully nobody even my boss doesn't he's not going to notice that okay so now a few more things I wanted to to share which corresponds to per corner contribution to the ecosystem one thing we just released is our new version of per corner serif among good e.b and some other products which come with it so what is per corner serif among good e.b well our proposition here is very simple to similar to what they have in my skill we provide the 100% free and open source version which is 100% comfortable compatible with one good e.b but offers you something more what is that something more well we provide you additional storage engines such as memory engine write for end memory workloads and Mongo rocks for write optimized workloads we provide whole bunch of features for which only exist in one good e.b enterprise such as pluggable authentication or auditing and we do some other stuff like query performance monitoring tools and for backups another thing what we add is a tool called per corner monitoring management it was now released for about 6 months but in our new version we really get significantly significantly better for support for MongoDB now what is that tool about well a lot of that is really trying to analyze my skill in the future MongoDB workloads for example we can take a look at the queries and kind of see what queries are causing the load on your my skill server which you find is very very important both for DBAs as well as for engineers you can take a look in the query and see why those queries are causing the load for example in this case you can say hey this query spends about 10% of its time waiting on the door and 15% of the time waiting on logs right which allows me to understand how to make it run faster and then actually I can also do from the same page and understand the create table, explain and other stuff which allows me to optimize that query all kind of from a single page we also integrate with per corner tool kit anybody heard about per corner tool kit some of you do so for example you can take a look at what information about the system and my skill which is often very helpful if you are looking at many systems right they all kind of maybe configure differently they also provide a lot of graphs do anybody use Grafana here I think Grafana is pretty cool and what you do here is you provide the integration with Grafana with kind of custom dashboards for my skill and MongoDB as well as the creating system so you can have a whole bunch of nice dashboards for Linux and hardware information on the database level for MongoDB a lot of insights what is going on on the MongoDB level actually I can tell you what in terms of the depth it goes into we have much more details than MongoDB Ops Manager in terms of so if you guys are doing some advanced tuning you should like that and you welcome you to check out the demo actually there is a demo probably available online which runs for maybe 20 different hosts so you can see how it works with my skill MariaDB MongoDB per corner ok now I am going to show you guys a few slides which are entirely created by marketing so don't help me against me but I need somebody to let me come here one I really don't but anyway I tell that just to anyway we have to corner live conference so if you guys are interested in my skill MongoDB Postgres so actually open source database in general we have whole bunch of them being covered and discussed on at per corner live and we offer you discount codes for that now, surprise we are also hiring so if you know someone who is kind of smart and really have a patient for open source databases encourage them to contact us and apply also please if you want to talk more to us stop by at our booth number 219 at the at the expo hall here ok and now I guys I really need to give out those t-shirts ok yes let's start from first row yes that's right so we do support MongoDB and we do have our own fork of MongoDB called per corner 0 for MongoDB yes the question was about per corner 0 for MongoDB well yes our promise in this case is what we maintain the version which is compatible with appropriate version of MongoDB community edition but we also add some additional features in this case kind of to provide more features in the open source ok support for 3BSD you can just pick one ok support for 3BSD well I would say we don't officially do build for 3BSD while I know per corner server is included in ports the reason for us is not enough commercial demand if you look for us to maintain that because I don't want just to set up and do a build I want to make sure if you're doing support for a certain platform it really invests a lot about performance engineering quality assurance for that and we just can't do that yet for MongoDB for 3BSD hopefully we'll be in position to do that ok what happens and what so what that means right that means you should remember kind of in my scoping similar to my stuff but actually it's a bit better so what happens in this case is if I'm doing the update which modifies for example million of documents and let's say MongoDB crashed in the middle what will happen is some of them will get updated some of them won't what MongoDB grantees you is that they're not going to be document which is kind of half update where part of that was update part not and then it was kind of not corrupted right but there is no guarantee what it will be or not that would be the proper full transaction support which is not there at least so MongoDB has a replication right so what we are thinking about mainly in this case is a single node when it speaks about the multi node stuff there is a concept called write concern in MongoDB when you write the document you can specify how it should be propagated through a replica set in order to be considered done typically it would say majority concern so that means the transaction only completes when data was propagated to the majority of the nodes and in this case if that node dies it's complete iteration it's still guaranteed that it will have this data does that make sense? but again that applies on a per document level it's not the whole set which is acknowledged okay yes do you want a t-shirt? why don't you oh okay how is the user management in MySQL compared to MongoDB? well you got me I haven't had a t-shirt to tell you anything meaningful I just don't know well I would say I think that's possible in this case I wouldn't say how fine-grained MongoDB permissions are compared to MySQL I just can't answer that node's MongoDB enough to answer for gentlemen no? well so when you speak like you're going to serve for MongoDB it is binary compatible right so you don't need to start need to transfer anything you can shut down your MongoDB server install to your corner binary start it up and it will work so it's it's binary compatible if you only use MySQL community edition so for example you can say hey I want to use MongoRock storage engine right and in this case you can just build a MongoRock node you can add it to the replica set it will synchronize the data and then you can promote it as a master right if you want so MongoDB makes it relatively easy to do this kind of integration well for MongoDB yes so I was thinking about something like if you want to deal with synonyms right and some other kind of yeah well I would say it's it's my understanding while MongoDB has support of full tech search right it's it's really not that advanced as elastic search is because elastic search really started as full tech search first right and then started then MongoDB took another path okay any other questions come on guys I need some more t-shirts no more questions yeah come on gentlemen back well I think in this case that's going to depend a lot about how exactly what exactly you're doing right different kind of benefits back and forth let me give you one example right for example very known bottleneck in MongoDB right now if you think about documents which can embed another documents the problem for them can be pretty large right now if you are doing heavy updates when that becomes expensive you are updating large documents all the time it's kind of similar to trying in my school to update one block right now in this case you are faced with this kind of two interesting choices there is mf1 storage engine which actually has special features where it can go in the document directly and update the field like for example if you're doing an increment but that has a pretty course lock in and a lot of other problems or you can go to wire tie which has a pretty good good concurrency control right with multiple documents but it has it has to kind of write the whole documents when you update that right and a lot of people have been kind of really stuck between those two options and none of them is really good choice right now in a relational data we often normalize the data so you have let's say some very thin table right which maintains your counter right and you just fetch them to join right so the answer is it depends so you know I can't throw that far so you'll have to come closer right I mean I kind of have a dirty throw the last one oh my gosh and I think that's actually the female t-shirt for a change is there anybody left oh we can left that for oh forget oh shit we are in California right okay anyway anyone wants the next t-shirt okay okay well out of time but on a break so if you guys have any more questions I would be happy to answer them mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three can we control how is the mic check in one oh three mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three mic check one two three four five six seven eight nine ten mic check one two 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. 1, 2, 3, 4. They should be able to hear me. Bill is a community college instructor in Arizona. One of the class of class. He has a couple of professors in Boston, and I'm going to teach him about them, especially about education. So he's also a black student. Hi, my name is Phil Beslowski, and I'm sorry for the delay. There was just a few hiccups, and I can't actually see on the screen here what I'm doing, so I'm going to have a little bit of dyslexia going back and forth. One of the things that my students have asked over time is how do you handle hierarchical data in my SQL? And I know a lot of people are going to go, well, there are other graph-based databases that can do that better and the rest. But the question was, can you do it? And there's several different models, and I realized in the process of going through two of them, that's going to be an hour talk, even if I hadn't already wasted about 15 minutes. But these slides are done in Reveal.js, and you can just simply go to this address if you want to a little bit later, and I will post it up on scale as well. You can just download it, unzip it, open up the index file, and you'll have pretty well all the code and everything else. I am a bit of a character at work. It's kind of odd being in the business department and being a little different here. I've been a faculty at Mason Community College since roughly 1994. I've only been full-time since about 2001. I've been using Linux since about 1994. I remember downloading 32 floppies in order to use Slackware at one point. Peach Blanks OS, PHP, Python, JavaScript, HTML, MySQL and others. One of my other hobbies is I'm a scuba diver, and I do also occasionally impersonate a long-dead cardinal as part of a tutor group and try to be a mad scientist in my spare time. So hierarchical data. Almost everything that we deal with is hierarchical. Some of it's just one or two levels, but genealogical trees, family trees, post-threads where you want to be able to keep track of who's responding to who. Okay, a little bit of an echo. And company organizational charts. Parts lists. If you've ever seen some of the books that they sell for repairing cars back when you could repair a car, you just have this huge exploded diagram of parts, and those are all usually subsets of parts that go into another part and so on. And of course, category and sub-category relationships. The classic adjacency list, which is probably the easiest to set up. It's the easiest to insert. It's the most difficult to get the tree back out if you go more than one or two levels. Nested sets, which I also have an example of here that we'll work through, is a whole different way of organizing it. It's very easy to recreate the tree, but you'll see that when you go to do some of the updates. In a really large method set table, depending on where you do an insert, you could end up having to update every single row, except for one, in the table. So you can see how that can be kind of expensive. There's a couple of other methods, which I won't get to today, though I have a couple of links to them at the end of the slides. Path enumeration, which is where they basically do like a comma-separated list or some value-separated list of the path for a particular one. Again, definitely not even first normal and has some issues that you still have to iterate to the list. Closure tables, I've got a couple of links on. I haven't really played with those too much yet. So first off, again, adjacency list. It's easy to insert a new item and just can be very difficult to retrieve to many levels. So I've got a simple tree here that I threw together really quickly. You've got computer, and underneath it, you've got programming, graphics, and, of course, databases. And then some subcategories underneath them, and in some cases some sub-subcategories. And in the case of routines, there's even an additional subcategory. And, you know, you could argue why is it important to keep track of this. If you're familiar with things like breadcrumbs on some websites where you're able to file your way back, that's one of the reasons why you might do something like this, where you're worried about the relationships between the subcategories rather than just which category applies. It's an amazingly simple schema. You create a table, you have your primary key, you have whatever content you've got. In this case, we're just having the category name. And then you have a parent category ID that just points to the primary key within the same table. Fairly straightforward, fairly easy to set up. And, you know, you can see it's just category ID, category name, primary category ID, and a unique key just so I don't duplicate any of the category names. Now, the fun part, let's see how the dyslexia goes here. I need to use the force. Yeah, I did that on purpose. My students grow when I do that too. Okay, so this earlier, three warning. So I just wanted to, actually, let's make sure it'll be actually created here, if anything. Okay, so I've got that, and it's got a unique key and it's got the foreign key, so we could ignore the error. It's just an extra bit of code that got copied in. First level inserts, you can kind of take advantage of the fact that you know that the very first thing you insert is going to be number one. So you could just cheat if you want to insert into the table and give it the name. The very first top level is usually null or some case is zero because you want to know what your top node is going to be. And so I've got computer and then programming which refers to one, graphics which refers to one, databases which refers to one, web design which refers to one. And of course, if something goes wrong like my SQL now tends to have a habit if an insert fails, it still increments the auto increment, in which case this will blow up because that one is a foreign key and it's got a point to the right one. So again, this is kind of lazy. As soon as you're going to do any more inserts, it's better to either create some functions and some procedures to do this for you. So I just created a couple of really helpful little functions here. One basically if you give the category name, it gives you the ID, and the other one if you give it the name, the category ID, it gives you the name. Two that I'm going to be using a couple of my others. They're fairly easy. Nothing too exciting on them. They just basically take one variable and then do a quick lookup and return that value. And as their functions, they have to be used within a query. Again, this is the fun part of not being able to actually see what I'm doing here. Okay. And then this is my procedure. Yep, see, I'm accidentally copying stuff below the list here. So I have to be a little bit more careful. I'll copy from the bottom up. So this is my first procedure. It just simply takes in the category name you want to add and then the parent category name that you want to look up, rather than having to try and do this by yourself. So all you have to do is give it the name you want, the name you want as the parent. It then basically looks up that information, makes sure the parent category ID is not null, and then does the insert. This is one of those handy little procedures. You could also do this. Let's make sure I only go as far as I'm supposed to here. See, I still copied below, as it was getting some of this already. So here's where I was just adding in all those different little pieces. And out of curiosity, let's see, do I get all? Yep. So I'll still have to be a little bit more careful on this. If we don't take a look at the table, you can see the setup. Programming has a parent of one, which is computer graphics has a parent of one, databases is a parent one. Vector has a parent of three, which is under graphics. Inkscape has a parent of six, which is under vector and so on. So we've got the relationships there. That's pretty well the basic setup. And like I said, the creation and insertion is really easy on the adjacency list. And you're actually having foreign key to primary key relationships as well. I don't know how basic folks are expecting things, or how advanced, if anybody has any questions, feel free to interrupt me. But we have effectively data in there. Now, here's where you run into the problem. I would like to be able to search for particular parts of the tree. Like say, if I pick programming, I would like to get every single piece that's underneath programming. So languages, and of course under languages, the languages that are there. So you could try and do, if you do programming, so I'm just selecting the category ID, category name from the table, where the parent category ID is the category ID for programming. That's what that little function of mine did. So it's looking up the parent category ID for programming, and that's going to show me every child that has that as its parent. And again, I have selected too much, so I'm going to do it from the bottom. So programming, oh, didn't I not get languages in there? Programming has no children. Oh, we used a different one. Let's do databases. And you can see that there's two children under databases. So some of my copy and pasting is messing up here because I can't see what's going on with the screen. But that one works. So we've got databases that has SQL and no SQL, but I know, because I set this up, that there's additional children underneath there. So I could of course then go, okay, well fine. Let's then find all the pieces that are under SQL. And hey, I see that there's MySQL, MSSQL, PostgreSQL, Routines, Triggers, and so on. But I also know that underneath Routines that there's procedures and functions. So this is one of the difficulties. You have to make some fairly complex queries to try and get the full tree. Or you can try and have your web interface go through and just query at the top levels and then rotate through and query all the sub-levels. And recursion is its own reward, right? So this is one of the complaints that people have about this. You can of course try to find the parents of all the children. And so again, I'm just using my little functions here. I'm grabbing the category ID, but I'm telling it to get the category name. So it's displaying it that way as child. And then I'm doing the parent category ID and again getting the name. But the weird part of course is that this is where you have to do a self-join. You have to basically join categories, adjunct categories T1, to adjunct categories T2 and alias it. And then a little bit here, we're going to see sub-query heck. So this is going to be interesting. Let's see if I can do this without messing things up. I can always put the S in the beginning here. And still doesn't like that. I will try one more. You can see that programming, graphics, databases, and web design have the parent computer. Vector has the parent for graphics. 3D has for graphics. Bitmap has for graphics. Sequel, parent has databases. So it's going through and you can find the parents of everything fairly quickly. And by looking at this, you can kind of figure out, okay, well it looks like computers at the top and then those four underneath that and then those are underneath that, but it's not incredibly obvious from this. So again, it's easy to do inserts. I can add stuff fairly quickly. The database is small. It does the, you know, foreign key, primary key relationships. But retrieving the entire tree is a couple of variations here. If we use group concatenate, I can have the parent and then a list of the children. So again, you can kind of see some of the queries you can do to try and help you iterate through these. And again, you get to see how good I am at selecting stuff here. Whoops, and I goofed again. Those are the numbers. It shows us that two, three, four, and five are children of one. For the child three, it has offspring six, eight, and ten. Four has, and you get the idea. And with just a few changes, we can actually make that more human readable. So we just use that little function I created to tell it to look up the name when you give it the ID. And that's the only thing I added was that little function call to adjacent underscore get cap name. And you can see that, yeah, it is giving the information. So programming, graphics, database, and web design is under computer. Vector 3D and bitmap is under graphics. Database is SQL, no SQL. SQL has MySQL, MS SQL, PostgreSQL routines and triggers. So you can retrieve the information. It just simply can take a little bit more complex queries and again, some rotating through. I hope this is somewhat interesting. I've been watching a lot of folks where they talk about stuff, but I figure we'd try to actually do some things. So, you want to go through all the levels. And this is where you run to the problem. I happen to know that I've got up to, I think, five levels. So I have to do five self left joins to try and get the whole information here. So this is somewhat insane. You see, table one dot cat name, table two dot cat name, table three dot cat name, all the way out to table five dot cat name, doing left joins between all those aliases for the exact same table. And the result isn't bad, but there's folks who are probably already cringing. How big are these table? How big is this table? And you're doing a self join five or six times. It could definitely add up to a little bit of time on the computer here. Obviously, level one computer, because that's top. There is nothing other than just a computer that's set and all. And you see, it has programming underneath it. And there's nothing under programming because those inserts failed because I didn't copy it right. But graphics has vector escape, computer graphics, 3D blender. And you can see how you're basically creating the breadcrumb trail here. So again, another query that you can use. But you're having to do these insane join to the table, to the table, to the table, to the table. Leaf nodes, those are the ones at the end of the branch. There's nothing beyond them. They don't have any kids. They're fairly easy to find. You still have to do a join between the table where you're basically matching where the category D equals the primary category D, and where the category ID is null. So this will show us where they don't have anything beyond the end. And there's a few. Looking through that, yeah, I believe those are indeed all the ones that are terminal. You could double check from the list that we've got there, but that is showing all the ones that are at the end of the branches. They have no children themselves. So you can easily find the ones towards the end. Also, as quiet as my students. And usually it's just, I don't know if it's shocked. They don't know what to ask. They don't have enough coffee. But now comes the more interesting part. I want to basically delete a node in the middle of that tree branch, right? Which has a bunch of children. And normally if I were to delete a parent, what happens to the branch that it's attached to? It's orphan. It's basically its own little tree. You could graft it on someplace else, potentially. Now, of course, if you're just needing to update it, you may just simply want to change the name of the category rather than wiping out the node. But say that you do want to. One of the things that you can do is basically go through and have it update the parent category ID to the new, to the parent, basically to the grandparent. So I'm creating a procedure here called Adjust Adjacency List Delete Node. It just takes the name of the node you want to delete. And the first thing I do is I want to get the new parent ID. And that new parent ID will basically be the grandparent, if you will. The parent of the one that I'm about to delete. The old parent, of course, is the one that I'm about to delete, getting their IDs. And then I just simply do an update where I set the parent category ID equal to the new parent, where the parent category ID equals the old parent. So basically any row that has the old parent as a parent ID just gets the grandparent. And that's actually fairly quick. I'm going to pick on MySQL. So we're going to pick on the NodeSQL folks. And we're going to delete them. So that means that all of those relational databases will just be underneath databases. They won't be under databases, NodeSQL, blah, blah, blah. Now let's see. MongoDB, Cassandra and Spanx have a parent of four, and four is databases. So SQL's gone. They've all been basically moved up the chain. Again, that wasn't too bad. The real fun part, of course, is if you're trying to rearrange and prune the trees, but you can still do it. It's when you're trying to go through multiple levels. I don't have to worry about the sub-levels of if they had any children, because all I'm worried about is their parent ID. The other ones are already pointing to them, so that's no problem. Really easy to update. Retrieving the tree can be very difficult, and depending on how many levels, you could have so many self-joins that your eyes are going to start. And, of course, that takes up more memory. All those have to be stored in memory, and it slows things down. Not so much anymore. I mean, thankfully, when Oracle did take it over, the one thing they did do is sub-queries work much better now. Prior to five, I think it was five, five, it was, yeah, they were pretty bad. They would say, you know, do it with a join instead. But, again, you're doing queries within queries, and how many levels do you need to go? Some people will put in things like they'll put a level, a depth-level field, so you calculate how deep you are, and that helps you iterate through it. Some will also try to do a path enumeration where you'll see a comma-separated or slash-separated set of values of the primary key chain for that particular field. Again, for updating it, inserting it, looking up little bits of information really quick and easy, the whole tree can be a little inefficient. Again, it depends on what you're using it for. My students hate it. They always go, well, what's the best situation here? My answer is always, it depends on what you're doing. But that is some of the big complaints about the adjacency list. Now, I tried to put this together. I realized I need to work on Inkscape a little bit, but this is a concept for how nested sets work, so I wish I had a whiteboard to draw on, but I think if I draw on this wall, they're going to get very upset. So the idea here is that everything is a nested set, so you have an overall container for a computer, and I only picked two because I was running out of room, then you had programming and databases, then you had languages, and within languages you have PHP and Python, within databases you have SQL and NoSQL, within SQL you've got MySQL, I didn't have room for the other two or three, and in NoSQL you have MongoDB. And the way that this works is you just start counting from the left. So the left value for a computer is one, the left value for programming is two, the left value for languages is three, the left value for PHP is four, the right value is five, and basically it's bounding values. You know that something is at least a child or grandchild or whatever because it's within the range. I know in this particular case that any value that's between one and 20, so a left value of one, if they're greater than one and less than 20, they're at least somewhere inside of the computer level. If it's the value of somewhere between two and nine, you know that they're underneath programming. If it's between 12 and 13 in this case, I know it's MySQL. So this is nested sets. Really cool for pulling up the tree. However, you can already see a potential problem. Anybody guess where the big, big complaint about this one is? Inserts. Say that for some reason I've got a fairly big set here. Maybe, well, we'll say a medium size. A couple hundred thousand records, right? And I have to insert something basically inside here. So this doesn't have to change. This doesn't have to change. This doesn't have to change. This doesn't have to change. But then all of these, oh, wait a minute, these are all attached to, oh my goodness, you have to do an update on every single row. And there are ways to do that. I'll show you. But you can see how that could become kind of inefficient. So nested sets, I know I'll reiterate this a little bit later, they're great for things that don't have a lot of regular inserts and updates. A parts diagram for an engine or something would be great for this because you don't usually change that. It'll take a little while to get it set up. It'll be really easy to get the parts you want out of it. But thankfully you're not going to have to make changes to it too often. But if you're trying to keep track of a really active discussion board, oh, probably not the best way to go. Again, pretty simple for the table itself. You've got the category ID, category name, and L value and right value. Oh, yes, and I want to make sure I point this out here. It's a little bit red. For this one, as I haven't played with the nested sets quite as much, I heavily used a lot of stuff from this particular person's presentation. I turned several of them into, again, it's kind of been red. It's matching hierarchical data in my sequel by Mike Hillier. And the links are at the end of the page. This is also a link. Some good stuff. I changed a few things, and I turned a lot of his queries into procedures because to me that just seems to be more useful. When he wrote this, apparently there were still quite a few people using MySQL 4, and he wanted to make sure it would work for them too. My general feeling at this point in time is to stop using 4. I mean, really. You're right up there with the XP stuff. So, the other big difference is the values that set up the relationships are not foreign keys. There is no internal foreign key pointer. These are just numbers. So you do have potential issues with, you know, referential integrity again, but they're just numbers. So there's nothing too exciting for those. Lost my mouse again. Ah, there it is. You get to tell your friends, yeah, I had a talk today where I couldn't... I was about to watch a man fail to top a highlight stuff problem. You think at my age I would know how to copy and paste. Hard way. He actually set up several and then went to do an insert. I am going to do it the single hardest way. I'm going to put in my top value, which goes from one to two. So that means for the other 29 things, I'm going to have to do an insert that increments those values every single time. Apparently I am a sucker for this. So this is just inserting computer and a left value of one and a right value of two. Again, kind of boring. And computer, one, two. I don't want to have to try to recalculate this. So this is where we're going to do it in a procedure. And other than a few name changes, this is other than the fact that I turned it into a procedure, this is almost directly lifted from his example. Why reinvent the wheel when it works. So I'm creating a procedure called nest add category. It takes one variable, two variables, the category name and the parent category name. And I have to figure out what the right value is going to be. So I need to get the value, the left value of the parent category. So I need to know where I'm going to start on the left-hand side. So if I'm going to insert after four, because that's the edge of the parent category, I need to get that number. Because I know I'm going to be changing everything from out there. So that's all that's doing. And then I have to do a whole bunch of updating. I need to set the right value equal to right value plus two, where the right value is greater than the new right value. And then I have to do the same thing for the left value, left value plus two, where L value is again greater than the new right value. And then, and only then can I insert in here the values that I've gotten. You can see it calculates out the new right plus one and for the left part the new right plus two. It's a beautiful little set of queries that he created. And they make a wonderful procedure. The one thing that he did that you can't do really with procedures in MySQL is that he would lock the table before he did his stuff and then unlock it at the end. You can't actually do that. Now to a certain extent the procedures themselves partially lock the rows involved, but there is a, I'm not quite clear whether or not there's a potential race condition here that you can run into. So I'm just going to call that and one at a time add this. So this first one of course is going to move that, that right one over two and then put in two and three and it's just gonna keep getting worse. So each one of these is having to move more and more and more values by updating more and more rows. Well the problem is that this is all one web page. I'm using, I decided to be cute and I decided to use a reveal and this is all one web page. So I just have to be careful here. Okay. Now remember right now the values are left, one, right, two and these do take a little while. And now notice computer goes from one to 60 when it was one to two and okay obviously everything else is in between it. You'll see that programming goes from 46 to 59, graphics goes from 28 to 45, databases goes from two to 27 and you get the basic idea here. So you can do inserts. It's not that hard, it's just a little bit of math and the wonderful thing is people have done it for you so you can just lift it, change it to fit your table values and when your boss says great job you just keep your mouth shut. But it's done for you. The drawback of course is that this does take a while. This is a small dataset. Could you picture on something that is 100,000 rows or longer? It could take a while. Yeah and that actually if you look at John Selkow's book one of his ideas is that you can either use a decimal setup or you can just simply start out with your basic tree. So you've got a couple hundred ones and just pat them by a thousand between each one. And then just do the math. The insert query would be a little bit more complex because we'd have to look at what range it needs to go into. But the insert would be much quicker because it would only actually it wouldn't have to update anything. It would just do the insert. Eventually you're probably going to learn to the problem or you're still going to have to do it. But yes that is one method for this. I am deliberately showing you the worst case scenario. In fact in a few cases I probably made it harder for myself than I needed to. But yeah that's actually a very good point. Those index values if you pat them, you know, spread them out by a thousand and then you just simply have your query look at where the range edges are and insert some parts in between there. You do get a diminishing returns over time but if you have enough space you may never run into a problem until you switch jobs. So you know, next person has to deal with it. So there's some really neat queries that they use for these types of things between multiple tables. However, 5.7 turned on several strict modes and one of them, only full group by, breaks a lot of the fun things that my SQL folks do which is probably not standard and probably has the other database folks cringing. But I'm going to temporarily disable it by just setting SQL mode to everything but that because that next query just doesn't work and I did not have the time to try and rewrite it so it did. Basically in 5.7 they turned out a lot of things one of which is the if a insert starts but fails still increment the auto increment value. As a teacher, that one kind of drove me nuts. I understand why they might do it but so I turned that one off. This one I'm just going to temporarily disable. Now if you're not using 5.7 you may not have this issue but on this little machine here that's what I'm using so it causes problems. So I'm just going to temporarily turn that off and only last for this session so as soon as this window closes it's gone. It does give me a warning that how dare you turn off some of the strict mode that we turned on. You can turn me in later. Now, you can't get the entire query relatively easily by doing a couple of things that I modified slightly to make it look pretty. So we're concatenating and repeating through basically two spaces and an account minus one basically just looping through from nested cats as node nested cats as parent. And we're just looking where the node value for the left side is between the parent value L value and the parent R value. And group by node.cat underscore name and order by node.L value and the reason why it's upset is that we're grouping by cat underscore name which is not what we're doing the aggregate query on. So this is normally considered a no-no in the only full group by. But it works pretty well even if we do have to disable the strict mode stuff to do it. And notice databases and graphics and programming are the first in-depth level under computer. Under databases, you've got NoSQL, SQL. Under NoSQL, you've got Sphinx, Cassandra, MongoDB. Under SQL, you've got triggers, routines. Under routines, you've got functions, procedures. Then you back to PostgreSQL. It retrieved the tree perfectly. And there wasn't all that join, join, join, join, join, join for every level type thing. So again, this is the considered to be the big plus of nested sets is that it's much easier to extract the tree or parts of the tree from it. But as you can see, the inserts are a bit of a problem. One of the other fun things though is how do you prune it? So I basically, again, modified one of theirs into a procedure where it basically finds the left value, the right value, and the width, the range, which is the r value minus left value. And then it deletes from that where the left value is between my left and my right. And then you update it and basically for all the sub ones go through and put the numbers back. So I'm going to pick on NoSQL again. Why not? I'm going to recreate this taking longer. I think my delete one is down here. So I'm going to delete NoSQL and it should hopefully re-nest them. Oh, nest delcat. Okay, I've got a typo. I've changed some names and I forgot to fix this. I'll have to re-upload those. I'll print it out for you. It does say that there's 24 rows affected because it had to re-number everything. Notice the value now goes from 1 to 52 and I don't see NoSQL. I also don't see Sphinx. I don't see Mongo. I don't see Cassandra. And it re-numbered everything. So now instead of going from 1 to 60 it goes from 1 to 52. And it deleted four rows which makes sense for NoSQL and the other three. Let's put that stuff back and thankfully that's really easy with nesting. This can reuse that and it will just put them in the appropriate spots. It may not be in the exact same order but it will be the same level. So I'm going to undo that. You see it goes from 1 to 60 again and you do see NoSQL. It's between... it goes from 3 to 10 which means it's definitely under databases which goes from 2 to 27. And MongoDB goes from 8 to 9 which is a NoSQL, 6 to 7 and then 4 to 5. So 4, 5, 6, 7, 8, 9. That's basically how it's supposed to work. This one is where you have to do a couple of extra updates. This is where you're going to remove the parent and just move all of them up one level. And then I guess I'm out of time at that point. We're going to delete the node, NoSQL but we are going to keep Mongo, Sphinx and Cassandra. And you'll see that NoSQL is missing but MongoDB, Cassandra, Sphinx are still there. The numbers have changed slightly. They now go 3, 4, 5, 6, 7, 8 instead of the 4, 5, 6, 7, 8, 9. And that means that they're now under databases directly. They're not under NoSQL. So, again, you can prune these and move it around. If you've got a situation where you have a lot of related data but you don't need to update it, again, easy to retrieve the tree removing the node or branch can make a lot of changes. Insert in the node can make huge changes. Ridiculous numbers of changes. So, some basic thoughts. Again, nested sets, full tree inserts are painful. Parts list for machine assembly would be great. Anything that doesn't change rapidly. If you're doing updates on a huge nested set, well, if it's a really huge nested set, more than once or twice a week is probably too much. If you're trying to, you know, get new tags or stuff that you want to keep the relationships between, I'm not sure why you do that. It would be a nightmare. Adjacents list. Inserts are very easy to do and very fast. Retrieving the full tree can take iteration. There are tricks to get around this. One of them is a variation on this called closure tables, which is kind of funky. And again, usually folks who actually do try to retrieve the tree do a series of little queries and then have some web interface or something else do the iteration to rebuild it for them. And that depends on how often you really need to keep the full tree. If you just need one particular path, that's not too bad. Recreating the whole tree, that is kind of a bit of a pain. And again, there's path enumeration. I don't really like that one. I don't like having multiple pieces of data in one cell. It just bugs me because of normalization. Closure tables, which there's a couple of good links on at the end here that I didn't have an example time to do. And modified adjacency lists where you can do some funky things. One example is what happens if you have two parents? I don't know about you, but quite a few folks at least had two biological parents at one point in time. You can actually create a join table that does two links to the same primary key and set up that way with it. And then you could have potentially multiple parents and so on. I've been playing around with that one, but I didn't quite get it done. Oh, that is a horrible color for the links. Again, I will re-upload this a little bit later today. And I will also put the link into the question. Anybody have any questions? Anybody learn anything useful today, maybe? I mean, I figured I could just sit and talk about it, but I wanted to try and show you how it can work. I'll be at it in a couple of cases, a really difficult example. Well, which one? In nested set, it's pretty easy. Nested set, it's very easy. Just simply go, my left value is 12, my right value is 20. Any left value that's greater than 12 and any right value that's less than 20, there are my descendants, whether they're my direct children or so on. Right. Now, depending on how you're doing it, so the method where you pad it won't work, but you can also go wherever the difference between the left value and the right value is greater than one, you know that they have grandkids and so on, and you can work your way through it. I've actually got one that does that, so it's slight variation on it. Let's see if I can go back. Now, I'm doing it for the whole table, but where is the query? Again, it's basically looking where the particular node we're looking at is between the parent value and the left value and right value, and basically just repeating through it. Again, we're doing a little bit of that. Again, we're doing a little bit of iteration, but it's pretty easy in a nested set to do this particular part, and I didn't copy properly. I'm thinking about the first level, right? So I'm at 12. The one that's right at 13 has to be one of my children. Figure out the range of that from 13 to, say, 18. Well, then you know 19 is one of my children, and then that one goes from a range of 19 to 25. Well, if there's a 26 that's within my range, then I know that's one of my children, and you can just work your way through it that way. It can be, but you can effectively fake it out with this little repeat set up here. But notice, sorry, you can email me later if you want to right now. I haven't slept yet, so I... I thought I did fairly well, but I think I'm losing steam here at the end. But it is definitely doable, and it's not quite so bad because it's kind of built into the table, yes. Oh, yeah, for the indenting, yeah. Which effectively does the results you want, because if you're indented, and actually, to be fair, I didn't do that. That was code I modified from that other site. And I don't work with the message sets as much. I don't usually have that many levels to deal with, and I was always paranoid about the inserts. Depending on the size, they're not too bad, but as soon as you get to be big, this looks nightmarish, potentially. Again, I hate to use the... It depends, but for a parts assembly list, and that's the set would go, there's a lot of folks that hate the adjacency set list. And I'm not entirely sure why. I think mainly because they have to retrieve the tree a lot, so they are using modified versions of nested sets. The closure tables, which are kind of funky, if you check out the link, everything links to everything in its path, including there'll be... You're always going to be your own parent, as well as parents to all your kids down below. It's a funky little set that supposedly is a little bit more efficient than the best of both worlds. What I quite often have done when I was using the adjacency list method is I would periodically create a table that would update periodically that kept track of the levels and everything else. The problem is that as soon as you start doing inserts to it, that falls out of date, you need to update it. So again, some drawbacks. There's some real arguments depending on what you're doing that mySQL and a relational database may not be what you should be using. For some of this, there are reasons why you might throw it into a NoSQL database. You can do it. And there are methods, and they keep coming up with doing better ones. But there are times where it's like, not sure why you're using mySQL other than your boss won't let you use anything else. Family Tree. Let's see if I can find the image that I did here. I was going to try and do that, and I just did not get to it. But I do believe I created a image that I can show you of kind of the basic idea here. Here we go. And this is included even though it's not part of the slide show. So you've got your people table. You have a separate table that acts as a join table with itself, effectively. So you've got your relationship ID, you've got the parent ID, the child ID. You can potentially have a child with multiple parents here, beyond just the normal two. You could also put in the type of relationship, whether it's biological mother, dates and other fun little things like this. But this does work. It does have some of the same potential issues that the adjacency list normally has in that retrieving the full tree can be kind of tricky. Retrieving one branch might not be quite so bad. But you can have multiple, I mean, again, potentially one person could have, assuming you're talking about people, thousands of parents. My guess is somewhere one of your scripts went bad, if that's the case, but it's possible. Does that make sense? Well, this is a variation of an adjacency list and it's really about the only way to do multiple parents. And I'm not even sure how you would do multiple parents with the nested set. It doesn't really... I've never even thought about that. Maybe that's why I was avoiding them. But yeah. So this is one method of doing it. I've had a couple of students. Apparently there's a Arizona Akita Breeders Association. And he put together a database of all the different dogs and who bred with who, pups and so on. And obviously there you do have multiple parents, half siblings and so on. He used a variation on this and it worked pretty well. He had to do some kind of funky procedures to pull out some of the data. But they worked pretty well. Well, I made up for the 14 minutes and wasted it at the beginning. So you got your full hour. I hope there's some useful stuff there and I hope I was relatively coherent. I arrived this morning and was working on this towards the last bit. Because a couple things broke and I couldn't figure out why that one query wouldn't work. It's like, oh, you're using 5.7. I will do that real quick. Let's go back one more. Maybe highlighting it will make it easier to read. I'm really impressed. A million spam a day. I didn't think my bandwidth could handle that. They used the same username and password as they used for our district office and a couple years ago our district office got hacked. So a little Chinese botnet. Well, the IP addresses were coming from China who actually owned it. Got it and it was for three days one million spam. I'm really impressed. One million spam a day. One million spam a day. For three days one million spam a day. I was blacklisted for most of the internet. Cleaned it up. Everybody dropped me off their list except for Barracuda. You have to go to them and pretty well beg and plead. Here's what I did. Here's how I fixed it. Please put me on probation for 30 days and I finally got off probation. And I tell my students, please don't use the same password. Well, not really. I'd have to change the domain name if I'm using the same domain name. The IP address doesn't really matter. Some of those attacks will go by IP address but some will go by domain name too. I hope that was useful. I'll be happy to talk afterwards. That must be somewhat coherent.