 Good morning I'm very happy to be here and thank you very much to Zynav for Reaching out I'm gonna talk to you about lessons from database failures I'm also fairly severely jet lagged and the heat hasn't helped I Work on MariaDB server and have been since the start When we did it at Monty program and I've been in the my SQL world Basically hacking on my SQL for more than 10 years and using it for probably more than 15 years Also worked on Fedora and open office and couple years ago. I even won an award So these slides were prepared a Little earlier, but I couldn't help but resist putting this slide here This this actually was probably quite famous post from yesterday Where this guy said he basically RM dash RF'd his entire Root directory including his backups Null mounted storage So it turns out I've never seen this in production, but this guy's little hosting company is officially hosed due to a scripting error my rough agenda today is to talk to you a little bit about backups and Verification of those backups So basically having off-site backups makes sense. So you don't RM dash RF slash everything, right? Also talked to you about replication and failover a Lot of people tend to believe my SQL Maybe it's a toy database or doesn't do many things as one would expect But replication has been one of the key reasons why my SQL has actually become fairly popular and used at fairly large sites I mean if you look at the top 20 websites today 18 of the 20 according to the Alexa ranking are powered by MySQL or MariaDB or some variant of my SQL and The other two actually owned by Microsoft. So presumably they run SQL Server and The other thing that's fairly new I'd say maybe only in production use for about six to eight months Would be security and encryption Securities been around obviously for quite some time, but the ability to encrypt stuff is fairly new in the my SQL ecosystem So I'll start off with a site called Magnolia. Does anybody remember this or has anybody used this before? Can I get a show of hands maybe? Not many. What about delicious? Okay, so a lot more people use delicious than Magnolia, but Magnolia probably started a little earlier You know, it conformed to open standards. It had micro formats. It started around 10 years ago They also had a paid component to Magnolia. Whereas delicious generally was free for all and According to the postmortem anyway, they never made any profits and here's the funny thing about postmortems something as New new as Magnolia. I mean it only you know probably shut down in 2009 You can barely find information on the internet anymore about Magnolia and why it it shut it shut down In fact, if you google carefully you I found an old blog post of myself Which is you know shocking so they had a complete outage at the end of January 2009 and Of a couple of weeks later, they realized they had data corruption to the entire udb the user database and they basically said they were essentially dead They tried very hard to come back And that didn't happen. So by September 2010. I believe they said, you know, that we're completely out of business Your bookmarks are mostly hosed if they weren't saved in the archives and delicious. Basically one I mean yahoo then acquired them I well, I don't know if you can say they won by acquiring them But then, you know pin board came along as well and so forth What happened? turns out they had a couple of x-serves that they were running the service on And they used some intel self-hosted mac minis as front-end servers. They only had a half terabyte mysql database It was mysql 5 back in the day And it turns out they had file system corruption So running your database on a hfs plus file system in production is probably not the wisest choice I mean, if you see lots of database benchmarks that are run on max They also tend to maybe even turn off fsync. And yeah, of course database is extremely fast when you turn off fsync Or you run on a file system that is, you know, not battle tested like xfs or ext4 So they already had file system corruption And that also then led to a corrupted database backup. So they weren't obviously testing this Turns out that the other thing they did was they made use of rsync to back up the database over a firewire network And rsync is great for static data, but the db is constantly changing. So why would use rsync to do that? We never did find out if they used mysm or inner db though speculation from Most of us would say that they did likely use my ism So they should have probably used a transactional store And you know, how would you do it today if you were going to start a pinboard equivalent or a magnolia equivalent What would you do today? well for one I'd probably just launch it in something like ec2 And then I'd use ebs snapshots to make sure that that's being backed up And then I'd start the database in rds and also use snapshotting there And I probably also have multi az deployments And I might be able to actually start a service like magnolia Even on amazon's free tier if I write code. Well At least for the first year but We're at rootcon. So let's presume you're going to want to self host all of this for one Backups I would definitely use something like extra backup. How many of you use mysql and don't use extra backup? Okay, that's quite a number of you So extra backup is really good and if you happen to use extra db Which is the modified version of inner db That ships in maria db and bacona server You'll realize that there is this additional feature known as bitmap change page tracking This allows you to actually just look for change pages In the backups and make backups of change pages. So you're kind of getting More efficient backups than just entire page backups Extra backup is definitely proven. It's battle tested People use it in production every day Another thing I would highly recommend is if you were going to maybe provision a new slave and you probably do want to provision slaves for for replicas Is to use something like mysql dump with single transaction and master data With the option to actually execute the transaction completely as start transaction with consistent snapshot This does not work with stock mysql even up to five seven But it does work in bacona server and maria db Because this is a byproduct of the way we implemented group comet in the binary lock If you happen to be still using just regular mysql You will still have to run flush tables with read lock and then only do a mysql dump with a single transaction Now naturally you don't want to do that too often. So upgrade your servers Another thing that I would say is a good thing to do is probably is to back up your replica You don't want to you know add more load to your masters. You definitely want to do that off a slave And replication event checksums have been in the mysql world since mysql five six and maria db Five three both of which came out around the 2011 2012 mark. It's 2016 now. If you're not running event checksums This is obviously not a very good thing for you and this would have prevented magnolia some problems overall because Basically checksums are written to binary logs as well as relay logs and they're checked at various points And when they see an error either caused by memory disk or network failures They will actually stop replication immediately Based on checksums and you can choose where to run the checksums. You can either verify them on the master You can verify them on the slave choices up to you but running this has Fairly little overhead, but it's extremely good to make sure you're not actually replicating crap And you can easily replicate crap if you don't know what you're replicating. I mean if you're running statement based replication today and they are you know changes to UU IDs and so forth you may actually realize you are Replicating crap after well, which is why the default for a fairly long time has been mixed mode replication Where things that were likely to change would switch from statement to um row based replication and today More and more I reckon I would probably recommend you to just run row based replication Unless you know why you want to run statement based replication row basis By enlarge the default today and there is really no reason not to run it Now now if walter was in the audience he might actually laugh a little bit at this because um couch surfing Basically almost said they were going to close in 2006 tech crunch wrote an article saying that they were now sent to the Deadpool and It was out that it was because the database administrator made some critical mistakes And uh avoidable hard drive crash, but they also did some things that you know equated to dropping stuff that they shouldn't have dropped and Couch surfing is still around today. I guess does anybody use couch surfing here? Okay, not many how many use air bnb? A lot more. Okay, so couch surfing probably survived because air bnb wasn't around in 2006 But had air bnb been around I guess it would probably have died And one of those things that were made you avoid this would be to actually run time delayed replication You can basically implement this on it's on a per slave level So it holds the sequel thread So the replication delay on one slave user could uh configure multiple slaves to apply replication And you basically have this since mysql 5 6 so since 2012 you have been able to actually state that one slave is now Maybe 10 minutes behind Two hours behind and so forth and this is intentional so that if a dba does make mistakes You can actually get around from those mistakes Do you like to draw your attention to how long it does take to make these features happen? So time delayed replication wasn't an option for couch surfing, but it is an option for you today I mean this feature was suggested in 2001 long before mysql had you know sensible replication There was a bug opened in 2006 It was pushed to a tree in 2010 so a four-year delay And it was only available in a GA release three years later Which is Pretty long if you think about it. So yes, there are features that people will find useful That may not may not have existed back then But if you're starting out today or you're you know rolling out a database today You should be using the features of today's database and not be biased by yesterday's problems So Why replicate? The easy reason is to scale out a lot of people Think about the whole master slave methodology as a way to just have more read capacity basically And today with global transaction IDs, you could have multi master setups to even have you know better write scale out Then another one is Failover failover is a fairly common Topic when your master goes down you'd like to fail over to the most current slave and that slave should become the new master now You may want this to be automatic or you may want this to be manual and we'll talk about that going forward as well Another common problem is geographical redundancy across multiple data centers now replication and Having multiple data centers is important because you know your data centers can get flooded They can actually go up in smoke due to fire And I wanted to take a clipping about why this makes sense But most of those articles were in the korean language. So how many here use samsung devices? Not so many. Okay. So if you happen to have had a samsung smart tv Or you know, you have a samsung device which has all these s apps that sync with samsung about April two years ago You'd have realized that on a sunday You could not actually make use of your smart tv or all these s apps would die And it so happened I was in Seoul south korea then as well And my a max wouldn't work either because samsung has the rights to a max in korea It turns out that their ilsan data center caught fire And because it caught fire and they weren't replicating to multiple data centers You couldn't effectively use your smart tv your s services were down And for nearly a week, I couldn't charge my a max in korea So I had to go back to using like a visa. Which is kind of awful So Geographical redundancies across multiple data centers make a lot of sense And today with you know cloud and the idea that you may want to do it in a hybrid fashion Amazon rack space. They all allow you to just open up Accounts and have multiple data centers across you know continents And then the other one As to why you'd like to replicate would be online schema change You know my skill 5 6 supports online ddl and you know large ddl operations may cause replication lag But there is a tool you know pt online schema change does non blocking altar tables and people do want to change their schema as in production today I'm happy to say that we don't really find many Failures being reported up with pt online schema change. We've seen you know positive stories from the uk government digital service task rabbit Wikimedia pdl uses this with you know, the a maria db servers Basecamp from 37 signals Open stack deployments talk about pt online schema change and this was actually you know, it became very famous Largely because facebook took something called oak online altar and released facebook ocs Facebook were probably The first largest deployment of my sql to do online schema changes And then came pt online schema change which became a lot more mainstream. So there's they're fairly good blog posts Um from facebook's mark Callahan about how they did online schema change Of course, if you're looking for a facebook blog post, this must mean you also have a facebook account Because you have to read it on facebook so types of replication We've had asynchronous replication for the longest time right This is the default And it's fast mean you can commit In 20 milliseconds 30 milliseconds and you know, you're you're good to go Your master will get it. You have no idea if your slave received it But you're pretty much good to go then we have semisynchronous since five five as a plugin and semisynchronous is generally I would say how very large sites tend to run My sql the very first people to make a semisync plugin available was from the google tree of my sql They had a patch available in the since the mysql five zero days And it was probably running semisync since 2007 or eight facebook also runs with semisync and We've improved semisync in the sense that it is now enhanced and lossless because it turns out That the way we would do semisync before You would actually commit to inodb And a master may crash and now the slaves may have received the data because based on the way it was being written Um, so your but your applications may see them. So previous versions of semisync actually Occasionally suffered from this this idea of phantom reads But the idea of lossless semisync is that we would only commit Inuit to inodb after getting an act from one of the semisync slaves And actually this surprisingly does not degrade performance either So now you're committed So only data that is committed on at least one slave is now visible through applications And I think this is a huge huge Again in getting semisync out there. So this is available in mysql five seven Percona server five seven and maria db ten one So this is what we call enhanced lossless semisync And semisync by and large is how most large deployments today tend to run Then there's also the idea of fully synchronous replication And this is you know, not completely new I mean we fed this idea of from ndb cluster or commonly known as mysql cluster For a fairly long time. It's just that ndb cluster is extremely complex to set up Requires a minimum of five nodes which has one management node And it's typically only used for in the telco world You know upgrading ndb cluster in the old days used to basically mean you Type 54 commands or something to that effect just to upgrade your cluster But it was very very highly available and felt tolerant. In fact, most of your cell phones And your cell phone providers they have these home location recorders at cell towers Most of them tend to be powered by ndb cluster because they need to know where you are and you and when you're changing cells They need to also inform the database that you've you've changed cells So ndb cluster hard to set up doesn't use no db So what were the other alternatives? Galera has been around for about six years. Does anybody run galera cluster or extra db cluster? All right, a bunch of you excellent Now galera is obviously fully synchronous replication. So it basically means that all nodes are the same or the transaction gets rolled back Not to be left out Mysql based on the same exact paper decided well galera looks like it's getting some steam I mean lots of people from pager duty avg etc make use of it Including they recommend this for open stack deployment to some extent They said let's try this thing called group replication group replication today is still very much I would say beta quality and it's not released against any mysql release, but It's well worth watching because it makes use of fully synchronous replication as well Early versions made use of things like chorosync, but they've gotten rid of it And one of the cool things about group replication that galera doesn't do is that if you happen to run Windows you could run group replication there Now does anybody run windows in production? I thought so and then there's drbd the idea of a raid over ethernet or distributed replication block device drbd was probably great tech for mysql back in you know 2007 When we didn't have as robust replication and required you to actually replicate blocks across the network This is likely still how amazon's multi az is deployed for for one We we obviously don't know but we can guess based on performance numbers, but today I really don't think you need to you know use drbd and have you know a passive master just waiting to take over Having a passive master just waiting to take over is you know burning up your data center resources Because you can't actually do anything with it and many people then say oh, well, why don't we do cross drbd, right? So the passive master is now also a master for a certain set of data Well, then you don't really have that much high availability so There are a bunch of frameworks to make your life better There is this thing called mysql mmm that was fairly popular also probably about you know eight years ago If you go to the website, it tells you do not use this you should probably listen to them It is fairly easy to set up, but it's also fairly easy for something to go wrong fairly quickly So don't use it Then there are several lines cluster control This one's a great piece of you know gooey software You know you can set up a four node cluster in less than you know five minutes It does galerite support semi-sync. You can do an automated failover manual failover You can set up ha proxy. You can you know do You know on-prem off-prem hybrid do simulations of workloads hot backups But these these these folk Also, I'd say probably don't use them largely because you know, they charge you a license fee They give you a two week trial version, which is crazy in the open source world Though they do obviously have customers from what I gather My personal favorite is orchestrator And this one's written in go was written at outbrained by shlomi noa Who now actually works at github and he was in the middle of it He was working on it at booking.com and many people actually end up using this I think etsy uses this now as well actually So this one's got Much use in multiple places. It does read, you know, it'll read the topologies It'll monitor state it'll poll you can modify your topologies. That's really important, right? Moving replication around it has a gooey. It has a json api You can you know visualize issues you can kill long running queries This is open source. This is easy to install This is easy to use. This is what I would recommend you to actually make use of There's also my sql mh a and you can tie mh a in to orchestrator mh a is known as my sql high availability. It's a set of pearl scripts I know maybe not many people still write pearl but I do And I didn't write this but I do contribute to it. It was created at dna And it's actually today used in multiple places like, you know, the sk telecom group jet air in belgium And um, oh, yeah, the big one facebook facebook uses this as well So mh a is a tool to monitor your master and slave topologies You can have n-tier slaves so slaves off slaves And it'll allow you to do an automatic failover or a manual failover if you want to do something like an online schema change And all of this is fairly easily configurable So I'd say definitely use mh a you can get started with mh a in production with testing in probably less than 20 hours with scripting Then there's tungsten replicator Most of this is open source except for the our geographical redundancy tools where they then want to sell you something It was actually made by continuant Vm went up picking it up a couple years ago and you can still get it today um Not quite my choice of tool, but I should mention it for, you know Posterity sake and also completeness because some people do use it We've heard, you know, large customers for them include people like zappos and box.net I don't personally like this because it makes use of Java api and it instead of using just regular binary logs It pauses the binary logs and converts them into what is known as a transactional history log with thl And if you look at the network traffic of a thl It's almost double the size of you know your transaction in a bin log So you're actually extremely chatty And then if you use 5.6 and greater You realize it actually comes with tools like mysql failover and mysql rpm rpl admin This of course requires you to enable to have to have a gtid enabled topology And um It turns out there are many people that end up using this and lately one of the people that did case studies So this was a was a open source mail app called nylas And they make use of mysql failover One of the caveats of using this is that you need to turn on log slave updates Which also means that you end up using a lot more space one of the cool things it does is that it does Fail back Which means that if your node rejoins the cluster when it's come back up again It'll just rejoins as a slave and it has automatic topology discovery So it rejoins as a slave and then you can you know promote it to a master later as well There's pakona replication manager and this one makes use of things From the linux HA stack, you know the heartbeat pacemaker or chorosync And we will talk a little bit more about this later because we have a little bit of a case study around it Now this is great if you also happen to be a linux admin But if you were just like a dba This one's much harder to use in my opinion There's also now maria db replication manager. It's Fairly new. I don't believe it has production users It's aimed to be like mha, but with maria db's gtid So case study github anybody here from github Anybody here use github Okay So this is all public okay It turns out github a couple years ago went down And maybe your productivity increased tremendously when it was down because you weren't starring projects filing issues, etc So they were replacing drbd With prm and the pacemaker resource agent. This meant that it would get more efficient failovers as they say in that abstract For one drbd wasn't really working for them because You know once you did have a failover you'd have an extremely cold buffer pool And you'd have to actually warm that buffer pool up This is a painful process when your buffer pool is you know 64 gigs in size 128 gigs in size people are getting larger servers Now there's there's a little solution in both pacona server and maria db known as inno db fake changes And there's the idea of replication prefetching as well And it basically replays statements from the relay log and then it'll manually convert them So basically converts commits to rollbacks But nobody really likes to actually make use of these fake changes So often in production because you know we consider this feature beta quality So for them moving to something like prm made sense however When there's poor performance One of the quantum's of prm would then lead to failing tests And because you're having load and you're failing tests and health checks this would actually trigger a failover What actually happens is failovers then start triggering more failovers Long story short They realize that once they had fail failovers being you know triggering more failovers. They had to stop Running pacemaker. They had to stop running prm But the one thing that I'd love to Bring to you here is that they also decided that automated failover was probably not for them In many situations, they would not have actually done this had an admin been involved So they've made changes to the pacemaker configuration to ensure failover of the active database roll only occur when initiated by a member of our ops team It turns out This is a fairly sensible way of doing failovers People love the idea of automatic failovers, but you may occasionally have multiple automatic failovers And you can read about it and it is very nice tear down here so Fully automated failovers, can it be a good idea though? Sometimes you get false False alarms. This will actually get basically give you a short downtime But also restart all your right connections. It's kind of annoying This whole idea of repeated failover, you know failovers triggering failovers This is this awful Which is why mha from the very get go had this thing where it says you wouldn't allow a failover if a failover had occurred in the last eight hours This would prevent A failover triggering a failover. So overloaded servers not a problem Unless you set the last failover mean and that end can be set to something lower if you like Sometimes you'll also notice that you may lose data, right? So what happens if you you know triggered a failover automatically and you weren't using semi synchronous application This could be a problem But today we have you know semi sync and we have group commit in the binary log Now you can turn on sync bin log equals one and in to be flush log a transaction commit equals one and find no performance penalty So, you know inner db with those options Satisfies the d in acid. It is fully durable The other problem is split brain Sometimes power off takes a long time Sometimes the network basically says oh, you know, it's flaky from the monitoring node to the master But maybe the slave still sees it and you didn't you know execute power off Which is why a tool like mha actually has what is known as a secondary check script Which allows you to actually use another network interface use a slave to see if it sees the master And then it would not trigger a failover but a warning for you So, um, you definitely want to avoid things like split brain so At many many large places, you know, and there have been presentations about this You know even my favorite example going back to facebook is they used global transaction ids But they don't do automated failovers. They actually ask an op. It is all not automated All right, so let's talk a little bit about proxies How many of you have heard of max scale before No, well very few Max scale is basically what i'd call a pluggable router You can do you know connection and statement based load balancing you can use it for logging You can write to other databases back ends besides my sql if you write your own filters So pakona wrote a kafka plugin which can allow you to actually push data into kafka You can also prevent sql injections You can route queries via hints. You can rewrite queries You can have a binary log relay You can even do schema based sharding and Large use cases for max scale would be a binary log server Now booking.com anybody use booking.com to book hotel rooms Okay So they run a huge mysql and meredb installation and they also obviously have max scale And they also still use pearl for what it's worth And they replace the intermediate masters by actually basically having max scale ship bin logs to slaves And this is actually a way to get rid of intermediate masters And you can also then ship archives of bin logs to other Places like you know hdfs so you can keep data there and you want to get long-term backups You can get it from there as well And a very popular use case for max scale besides having ha proxy do load balancing of galera clusters is to actually use max scale And why why max scale? I mean it's gpl and all that but the most important thing is that it understands The mysql protocol so it h a proxy doesn't understand it And you actually need to have a proxy user inside your database to actually pass those commands So it's kind definitely kind of useful to to use that Then there is the option of mysql router and fabric Now this is this one's actually interesting because this is also fairly new We don't see as many people up taking it though. We've heard people like dropbox say they're trying it out It definitely requires you to turn on global transaction id and you need what is known as a fabric aware connector A fabric aware connector is basically a connector that understands the fabric Fabric is there. So it's all the modern mysql connectors generally supported And it's also worth noting that some modern mysql connectors Say the connector j the mysql nd for php at the maria db java connector They all support load balancing within the connector as well So you can actually state a bunch of ip's that you're planning to use or or a pool And actually use that from even within the connector without using frameworks like this And then there's also proxy sql This is written by one chap who works at dropbox And another good use user and use case. So this would be nylas the one I just recommended And the thing that this one does over say something like max scale or even router and fabric is that Not only does it handle persistent connections fairly well. It does query caching So if you're actually doing caching inside the proxy layer You actually realize that you're probably getting a lot better performance, especially in benchmarks It may not be true in real world, but in benchmarks Your benchmark queries are most likely cached anyway so I thought I'd throw in a little bit of malaysia And offer you a piece of a durian It's one of my it's actually one of my favorite fruits. Do you eat durians? I don't know You don't get them here. Okay Well, you should all now go to philander malaysia and try some durian But why why do I have a picture of durian up here? It's because it looks like it's sharded And you know sharding is a database concept that basically it's like you partition data and you put it on different machines Uh have the idea that not all data lives in one place You basically hash records to partitions. You can choose to eat the partition alphabetically You can put a whole bunch of users per shard You can you know, even Partition your schema in a way that says, you know, I want to organize say by something crazy like postal code And so forth and sharding is also important And and why you need frameworks for sharding is because you also want to move content between shards You also want to reshard because when a shard goes down you don't want the data on that shard to be, you know gone And you also may may need to regularly split shards because a shard gets too big Let's say, you know, they're 400 users storing photos and I suddenly decide to upload my tenure archive Maybe I need to be rebalanced off the shard or you know push other users off said shard So there are multiple ways to do sharding one of them is the spider storage engine You can either compile this from my sql or gather this stock inside of maria db server and this Handles shards by using inodb as a back end So your application just connects to one maria db server and it and spider automatically divides it to have multiple back ends based on what you've configured it So you can maybe have 32 back end servers all power binary db But looking like one very large database to your app tungsten Also does sharding out of the box makes use of a jmx bean Um, obviously it makes use of its transactional history log as well And it can do sharding for you in the open source version as well And then one of the most famous ones that's written in ruby would be jetpans from tumblr This one allows you to clone replicas It allows you to also do online shard rebalancing you can promote masters You can do range based sharding and it makes use of when it's creating a new node It makes use of multi-sauce replication and for the longest time Multi-sauce replication was only available in maria db 10 and greater But my sql 5 7 now has the ability for you to do multi-sauce as well So make use of new technology again And one of the most interesting battle tested versions of sharding today is vitess Vitess is what make ensures that when you go to youtube you actually watch your videos You don't get angry that you can't see videos you want to take videos offline. It's vitess. It's also vitess telling you that Every my sql connection opens up One thread one thread per connection unless you use a thread pool and that Can easily you eat up anywhere between 256 kilobytes of ram to maybe even three megs of ram Vitess on the other hand makes use of Bison connections They limited to 32 kilobytes of ram And the connection pooling is all handled via go I say it has maria db support too And if it wasn't already obvious youtube is powered by maria db As is most of google i guess So so realistically i should say it has recently added my sql 5 6 support It comes with a python client interface It also allows you to rewrite sql queries It can do range based sharding either horizontally or vertically And you back it basically either via something like etcd or zookeeper And the cluster view is generally always up to date and then you use the proxy for query routing It is also now extremely easy to use it has extremely good documentation And if you go to vitess.io you can actually just roll it out in kubernetes relatively quickly You just need to enter your credit card details We've all heard the term fail well And i guess twitter was the one that actually had this image of a fail well And you realize that twitter it started on my sql. It's still on my sql You just need to evolve your my sql usage over time They then they built things like gizzard for sharding Which they have now obviously deprecated Then they went on to to build their data center operating system called apache mesos And then they said look we need to manage my sql in Mesos as a resource then they went on to build apache cotton And the good thing about twitter building all this and donating it to the apache project is Even if the ceo decides that open source is not so important for them anymore and they don't want to dedicate engineers I think last year they had a bit of a layoff Being part of an apache project means other people are also contributing to it. So cotton and mesos are extremely alive and kicking Then who remembers dig dig.com It's like reddit before reddit, right? Maybe i don't know i don't use reddit Anyway dig started on my sql They migrated to kassandra And there were all these great blog posts by the dba as to why they were migrating And it turns out that they just didn't understand how to index very well So they migrated to kassandra They pissed off their users then they migrated back to my sql with indexes But at this point in time, I guess they lost a whole bunch of users So I guess you know that one of the key takeaways of database failures and not understanding a database well And you know not maximizing its strengths and minimizing its weaknesses is that this can actually cause Lost to business lost to your income lost to people's happiness and so forth security Security is becoming kind of a big deal I think if you read the newspapers you always think hey, there's you know, someone got broken into again Just this past week the philippines voter data Leaves 55 million philippines at risk You can download a 338 gigabyte my sql dump get lots of email addresses And passport numbers of overseas philippines who are allowed to vote This cannot be a good thing for most people, right? So this is voter data. It's a good thing. They at least don't you know Store as to who you voted for right because then they can probably do political persecution Actually medicine was just last year Names street addresses email addresses phone numbers credit card transactions of over what 36 million accounts There were 9.6 million credit card transactions Including credit card numbers that are still valid 6.9 gigabyte compressed dump lord knows how many marriages broke up because of actually medicine And you know the worst part is they're probably just chatting up bots, right? Not that i'm advocating for actually medicine just just to be clear It just feel bad for them patreon If you would if you were kind enough to donate to either open source projects and so forth They got hacked not long ago as well Nearly 14 gig dump 99 tables lots of lots and lots of information Actually, you know put out as well Happy to say Most act from seca's panama papers. This must be pretty famous in all newspapers now, right? They took out 2.6 terabytes worth of data Due to wordpress and drupal bugs. So i'm happy to say they were running a fork of Oracle's htp server, which is fork of a patchy and they decided that my skill wasn't good enough for them So they ran the oracle database so This is not a database part, but you know 2.6 terabytes of data traveling out of your network and you don't notice it Seriously So while i can't tell you to update all your applications I can at least try to help you maybe prevent sequel injections The free version is the version on the top and then the the bottom version is the non-free version the mysql enterprise firewall Which has intrusion detection now already it blocks sequel injections You know it blocks traffic builds whitelist and so forth But you aren't likely to use it because you know i think you have to it starts at five thousand dollars I mean i'm unlikely to use it as well. I mean you go get a 30 day trial version But the good news is that max scale keeps on improving its database firewall filter So you can now even configure things like rule matching You can you know log matching queries You know it's really hard for you to Pause sequel with regular expressions So we obviously have to write a database firewall filter that is not that But if you know regular expressions, it helps you then write front ends to it Which will allow you to then extend the max scale database firewall filter now another thing So philippine voted data And you know ashley madison imagine, you know, how many marriages having encryption at rest would have you know Saved ashley madison users Maria db server 10 one has had gives you the option of either having Encryption on a per table basis or we want you to encrypt the entire table space Now when we thought about this we thought there were many ways to do this, you know Oh, actually to be extremely fair. We didn't think so hard about it google thought about this Google runs fully encrypted maria db They not only give you hdps all your user data is also encrypted And the keys are stored on a key server So this is actually really good for you and google contributed the patch and fixed back to us and which is why we integrated it So to be fair they thought a lot about this as well Column encryption within an app You you know and encrypt encrypt individual columns for from the app or the rm Then you lose direct access to sql You can also encrypt it via middleware mydiamo Actually wraps engines, you know db and my isam, but if you use any of the other engines, it doesn't work Crypt db uses clients server proxy as well Then there's the other option of using dm crypt, you know, why don't you just encrypt the entire Files file system you get a huge chunk of cpu overhead Especially when you're retrieving pages and putting into a buffer pool it needs decryption same for logs So the idea here was to make It extremely foolproof and to make sure that the attacker not only has to steal your data But it also has to access your internal key management systems And naturally the design also focused on building Um Key rotations so you can rotate the keys and then scrubbing data to get rid of the data that was already encrypted So We encrypt everything we can encrypt everything that touches the disk including bin logs temporary tables temporary files So maria db makes use of the aria storage engine for temporary tables. This allows you to encrypt that as well Now, of course, we do provide a key management plugin on the file system Now if you're storing your key on the file system, do you get broken into they take the key with it? So it's not very useful, right? So we highly recommend you using some kind of key management system that you have So we also provide a fairly useful key management solution called the amazon kms plugin You can get this in stock maria db today And you have to pay amazon obviously a certain sum of money But you'll actually ensure that key management happens on amazon servers And if someone does break into your database and if they can't quite get access to the keys They'll just get encrypted data. So no more credit card numbers being shown and so forth So don't do key management on the file system Definitely use a key management system and the cheapest option is amazon kms There are many other options probably available to you as well Of course, there are some caveats So one of the famous things is, you know, if you lose your encryption key, we can't recover the data This also means that we need to make mysql bin log Work with encrypted bin logs so that it can pass bin logs So now we have to add server hooks to that And if you happen to use galera cluster, which is now integrated into maria db server 10 1 We don't encrypt the gcash. So there's still some data leak there But if you're using, you know, semi synchronous replication Or asynchronous replication, this is not a huge deal for you And mysql 57 g8 two days after we g8 last year But only this year did they include Encryption for inner db table spaces So they're adding new features into ga releases kind of shocking Now, um, they are recommended Key management solution is the oracle key management solution oracle kv They ship that plug-in, but you'll have to pay for that as well So all things considered, I think we have pretty good encryption And we've probably got a more well thought out version of encryption because it encrypts everything including the logs So in conclusion, I think it's really worth to, you know, learn from others Learn from others failures so that you don't have to repeat them yourselves So one key takeaway is definitely use semi sync replication, you know with a failover solution That ensures you don't fail over too often. This is probably really important You want to always make good backups Our sync is not a good way to make good backups. Then you want to test your backups. You want to test a restore Um, you know, sometimes you want to do logical backups. Mysql dump is still great for logical backups Also, you want to save your backups somewhere else so you don't do like a rm-rf and go Uh You most definitely need to shard your data at scale. You're not going to be running on one master and one slave So use proven frame frameworks Maybe get even a proxy involved. Most large scenarios do get proxies involved You want to complete backups? You can make you some multi-sauce application when needed If you really felt that that was something you needed get all your shards your masters writing to a single slave And then doing backups or you know real-time analytics or whatever you want to do I would definitely use mysql dump and extra backup together because sometimes not snapshot backups are great But you also want to get logical backups and they've been extreme speed-up improvements From the web skill sql tree to make mysql dump Much faster as well. And then if you want to do parallel backup and restore There's a tool called mydumper. Mydumper has been around for many years You can totally use that And lately mysql in 5.7 has released in its utilities pack something known as mysql pump That's aimed to compete with mydumper. It's a lot newer your mileage will vary Also, I think security is extremely key. So you want to you know prevent sql injections You want to keep your apps up to date? And you know today there is next to no reason not to encrypt data at rest. I mean in all our tests We see no more than a five to seven percent decline in performance When you're running encryption and you're not running a database service at 90 percent the capacity most of the time. I'm sure now The very large user running it in production Basically says that they don't even see a one percent hit on the out workload. So, you know, your mileage will completely vary Also, it's 2016 You don't want something like this to happen to you. So this is Malaysia Kini It's a website that's been going on for 16 years It gives you alternative independent news that may be opposition centric in Malaysia and Just a couple of weeks ago. They said that, you know, they could not update their website Because they had a hardware failure on their database server And they also Couldn't access the database and couldn't recover the database and the backups were not usable So they were not testing their backups They obviously Had some fault and they decided this is subscription service by the way And they decided that the best way to move publishing would be to publish on facebook For subscribers So you don't want this happening to you in 2016 So with that, I'd like to say thank you for listening to me and I'm open for some q and a Hi, um, I got a question here Any opinions on aws aurora? Right opinions on aws aurora So for one It doesn't make use of many of these logs And they actually have changed the way my my sql indb works They've actually forked my sql and they have a fairly old fork at the moment Think it's a 5 6 10 fork and Um They've they've actually basically got you writing to the disk layer much sooner So you remove all the optimizations that my sql 5 6 and mary adb 5 3 provides So there's no more disk base joint base optimizations They disable all that to then stripe the three servers At the very least if i'm not mistaken Now it's it obviously has a lot of memory pressure So it only works on very large instances if you try it out on a small 15 gig instance You'll actually see heavy memory pressure when you're doing benchmarking We have heard people using it in production But I guess the one thing that i'm not so happy with is that you can't take it off amazon when you want to down the line And we find that more often than not people start with rds And then they move to their own self-hosted my sql variant on ec2 for example And we we don't know how long or you know how long one aurora is going to be around and how interested This will be going forward as well, and it's still a 5 6 not moved to 5 7 yet So my opinion on aurora is mixed But since we're recording a video, I know I can't say Also too much because we work with amazon So um They pay us for the you know Java connector and load balancing and stuff and a whole bunch other things. So I don't want to offend them in any way, but you know, you all right, you know It's your choice to use if you want, but I would go with you know rds or you know regular My sql variant that you host yourself personally Hello Great talk and it was really informative. So I wanted to get your thoughts on The factors to be considered while moving from a managed my sql service like we use google my sql service to our own My sql cluster built on top of Infrastructure services or VMs So what are the factors to be considered because currently we use that and we feel it's very costly, but There is some doubt whether like it's to whether the management complexity will be too high once we have it like To be frank like before your talk. I was more confident towards doing it ourselves but now we see like there are a lot of options and Lot of choices to be made like for us doing this for mongo db sharding plus replication was easy It may not be that good reliable replication, but choices were less There was only a default scheme to do it and we did it but here there is lot So I wanted to get your more detailed thoughts on that sure So yeah, my intention was not to scare you but to realize that my sql is now you know more than 21 years old this year It'll be 22 in may It it has a lot of solutions built around it. So there's lots of history Moving off something like cloud sql or rds or some hosted instance is something you do Generally once you've grown past your limits I mean if you start rds for example by default you can only start 40 instances, right? So by the time you're like at 20 instances, you need to actually now tell them look. I want you to upgrade my account So you have to take these things into consideration as well. So moving off say cloud sql It's not actually complex. I mean they allow you to do dumps and then you can restore it later as well But you do definitely want to think about having some kind of person with some kind of dba knowledge And you know, it really depends on what other features you end up using from the from google's Set of features. So, you know, we go back to the amazon option Let's say you use rds But you may also use some kind of caching service that amazon offers This now means you are going to manage your own caching service, right? So this means either you install memcash d you install redis by yourself and now you're managing that as well So I I do get that it's costly as you scale, but you realize that you're probably using a lot of these services that You know, you don't have to think about which is why You pay that money so Moving just say my sql off cloud sql should be relatively easy But you also want to make sure you build the caching layer You also want to make sure you obviously configure your you know my sql config. Well That's one thing that the cloud kind of abstracts, you know to configure it so much You are allowed to configure bits and pieces, but most of it is kind of configured for you You know running with a default config is like the greatest way to see lots of pain in the my sql world So, yeah, I think some level of dba experience is important And it shouldn't be a problem for you to migrate we see lots of people migrating off Hosted services some due to cost some due to just overgrowing the service And I'm happy to talk to you about you know this offline as well I'm sorry. We have time only for one more question. All right one more question Excuse me Yeah Yeah, uh, I suppose so So, uh, my question is on who's I don't know who's speaking which one of you I'm I'm here to begin. Okay. So What would be the best way to compress data in my sql? Do you have any Compressed recommendations? Yeah, so a good way to compress data So if you're using you know db, naturally, you know, it's a balance b plus three and b B trees are not known for their you know great compression You can obviously compress with you know db, but it's not going to be great But there are other engines that actually do better compression. There is tokyu db tokyu db You know can do 90% compression. It's it claims to do, you know, maybe even 12x sometimes better than uh, you know db And tokyu db is obviously an engine that we ship in maria db and pagona server as well So you could try that for compression and that one makes use of fractal tree indexes But then you give away some things like tokyu db doesn't allow you to use foreign keys So you can't just alter your table over you gain compression, but you lose foreign key support And then the new upcoming engine that I would say is kind of hard is known as my rocks Which is based on rocks db and that one also has pretty amazing compression as well So that may be something that you use in the future But at the moment if you use five six to five seven or a modern maria db You realize that there is you know db compression now, but it's just not great Do I get that other question or are we done? We're done. Okay. Um for the other chap you can see me here. Thank you very much