 Okay, do you guys hear me? And I see you can see my slides. That is wonderful. The only thing I'm missing is water, but okay. I guess we'll have to do without that. So, we'll talk about MySQL sharding today. So, anybody knows what sharding is here? Okay, some of you do, right? Anybody thinks sharding is painful? Oh, okay. That's good. So, first let me mention a few words about Percona and why really we are speaking about that. So, we as a company focus on really helping our customers to succeed with MySQL and more recently in ADB. We provide a whole bunch of services running from support to managed services, and we write a lot of software for MySQL. Colin has mentioned some, right? We have Percona Server, Percona X3B cluster, as well as tools for backups, Percona X3 backup, and Percona Toolkit. There are, I think, two things which are important about us. One is all our software is open source. We are not having some enterprise version, which is closed source, so there is some other way to require you to pay if you want to use that. And we are also really vendor neutral. That is not our goal to really make you to use Percona software. That is our goal as a company to really provide you the best solution for yourself. And, frankly, that's maybe MySQL, that's maybe the Percona server, that's maybe MariaDB or Amazon RDS. If you are happy, we are happy. So, let me start with the story when I talk about Sharding. A couple of years back, a customer comes to me and says, oh my gosh, you know, I read this all wonderful stories about Facebook, their Sharding, and there is a Dropbox Sharding, and there is everybody in the world is Sharding. So, can you guys please advise me how to Shard? I say, hmm, okay, let's look at your system first. And I would ask you what exactly is your database size, right? Probably it's like, well, it's about two gigs in total. Hmm, interesting, right? Do you guys have a lot of traffic? Actually, no, it's about 100 queries a second. Well, maybe you just guys just launched and you're planning to grow approximately 1,000 times in the next three months. Well, no, actually we've been in business for about five years. We're growing about 7% a year and we're expecting that for the next year, right? Well, wonderful story, right? And, well, the case in this case is obviously with such a small system, oh, thank you, you don't really need to Shard, right? Because even if the Moore's Law is kind of, you know, kind of getting dead, right, they're still expecting performance improvements in the system much more than 7% a year, right? So the outcome for that is before you really decide how you're going to Shard, you guys really need to decide wherever you need to Shard at all, right? And I think that is a very important question because if you guys go to different conference, read a lot of famous bloggers, a lot of them work for really large and famous companies and they are often going to talk about how to operate MySQL on extreme scale, that may not be exactly your case and in your case the different principles may apply. Now, what is interesting is with modern technology you can really go quite far with our Shard, right? And I think with our semi-obsession of distributed systems in the recent years, we may forget how much you can actually get from a single simple MySQL box, right? And let me throw out some numbers. From a single MySQL server you can get more than 100k queries a second. You can get more than 100k rows updated or inserted a liter per second. If you're going to some data kind of scanning queries you can get probably more than 5 million rows in memory. MySQL can deal with 10k of concurrent connections and if you are having some good box with a good storage and networking you can actually have 10 terabytes or so on a single MySQL instance, right? And this is not the marketing data and it's not extreme, right? So if you scale the storage, that's one of the things which I hear a lot of people tell me really. Do you really have anybody on the planet having 10 terabytes on the single MySQL instance? And I'll tell you, yes, I've seen many people doing that and I have seen people up to about 50 terabytes on the single MySQL instance, right? That is kind of extreme and I wouldn't quite go that way but I think that gives you a good perspective. Now, guys, these are not some extreme marketing numbers, right? These are something that you can get from a pretty mid-range system even in the cloud, right? And for a change, I'll show you some marketing numbers, right? So I stole that from Oracle. Where is Dave here, right? Dave, he ran away anyway. So I hope nobody is watching. But anyway, this is the marketing slide which is actually also true, right? But it is on a larger box with relatively simple query but you can see we are getting well over half a million queries with MySQL 5.7 on the high end, right? So 100,000 queries is quite conservative. Now let's do some math, right? So today we have sort of a system and we are planning for maybe 3 million active users and those users have 30 interactions per day and with all our kind of fault we can kind of adjust an interactive probably for single interaction you are going to have unlikely more than 10 queries, right? So with all this math and accounting for peak for free acts between your night hours and kind of peak hours, right, we came to, oh, shit. I was talking about the wrong slide, okay. Anyway, that's the math I was talking about. Can you actually say shit on camera here? Oh, I did twice. Oh, shit. Okay, anyway. So if you do this over math, right, what do we come out to? It is about 30,000 of queries a second, right? So really it is not a lot, even with those quite large numbers of users, interactions, and so on and so forth, right? So if you look at examples, the companies we worked with, which avoided charging, that's quite range a lot, right? We had worked with pretty large enterprise household name here in US, but the more household name it is, the more they don't like you talking about them and naming them, right? I don't know why. But anyway, so those guys are using Drupal for everyday workflow for everybody for 200,000 employees, right? Can use Drupal, no shrouding require, MySQL server loaded about, I think, 3% in average. Of course, they would use some replicas, right, for high ability purposes, but from performance alone, you don't need that. We also worked with a number of e-commerce merchants, right? And some of them are selling well over the $1 million per month, so that's a very pretty serious e-commerce businesses don't need any kind of shrouding. So, as you guys already told me at the start of this talk is charging is a pain, right? Yes, well, and I know some of us like pain. Well, maybe many of us like pain. I'm not quite sure what kind of audience is, but, well, pain is not always good, right? Let's all agree on that. When it becomes too much pain, we still stop liking it, right? So, in a lot of cases, even if the sharding is pain, and in a lot of cases, you want to either delay it or avoid it, right? So, what kind of pains do sharding bring to us? Well, a lot of that has to do with complexity. Complexity for developers, right? Now I have to, instead of querying one system, I have to figure out where my data is, and again, even if it's sharding done automatically for you, if you're a good developer, you still have to think about that because your performance profile is going to really depend on the data distribution. There is more complexity from operational standpoint. There is more kind of complexity in technology because you have more bits and pieces. You have to deal with much more complicated failure scenarios compared to just single box, essentially, which works or doesn't, and much more complicated performance profile with bottleneck network involved in much more sense and so on and so forth. Now, MySQL sharding is frankly especially painful because compared to many other systems, like MongoDB in a good example, MySQL sharding is very manual, right? MySQL came from an era when this kind of many service operating together as a single entity wasn't really a thought, right? And it was after thought, it was bolted in, bolted on, right? And in many cases, it was bolted on by the users, right? Let's say Facebook would bolt on their own sharding, right? Or the tests, which you mentioned, YouTube, their own, right? The guys like Oracle or MariaDB working on MySQL Fabric, MySQL Router, MaxScale, all those things have came much, much later, right? So if you can't avoid the sharding, you can often delay it, right? And why can't you avoid the sharding? Well, frankly, even though the modern systems became much, much more powerful and can handle a lot of beating for a single node, you can't build a Facebook on a single node, right? Or even a system which is probably 1% of a Facebook size. So, but in those cases, such systems are typically not born overnight, right? And it can take quite a while to get there, right? And you may not need to shard and waste a lot of your development and operational resources on dealing with that issues until you have much larger resources and much larger development and operational teams. So what few strategies do we have for avoiding the sharding, right? Or delaying the sharding? And they go across those several dimensions, which is using architecture, functional partitioning, replication, caching, queuing, and using some what I will call supplemental technologies, right? Let's go through them one after another. So when you look at the architecture, we'll talk about a lot of architecture questions in other top areas as well. But what I think is one of the changes in architecture recently was how people start to evolve to either microservices, each of owning kind of their own data, right? And maybe actually using different data storage technologies, right? On the back. Or some people would call it not quite microservices, but service architecture. But in any cases, you essentially do have a certain number of blocks rather than one monolith system, right? Which have different data stores, right? So the load and amount of data each of them have to store is different, right? So for example, you can say, hey, this is my interface which provides, let's say, user profile services, right? And this is maybe my billing system. If they leave in a different... If they design these different services, they probably don't have to live in the same database. Now, this also can be seen through an avalanche, which I call functional partitioning, right? And what that necessary means is what we want to keep our separate data separate, right? Think about, I don't know, even such a basic side as a per corner, right? We have a lot of different bits and pieces out there, right? We have our blog. We have our Drupal-based website. We have, you know, some other components. Really, each of them are quite independent, right? And they live on the separate databases to start with. I think it's important in this case to really maintain them to be independent and not accidentally mix them when you don't do that, because then if you need to scale and separate them on different systems later on, that's maybe hard. To give you one of the simple practical advice is if you have systems which operate in the outside on a different databases, give them different MySQL users with different permissions, which only have access to the databases they are designed to have access for, right? So that means, for example, if your main website needs an access to the content in Drupal, that wouldn't happen by accident. When you say, oh, let me just move those to, let's say, blog and Drupal website on different systems, you have things broken down because there are some joins or anything else you didn't expect. Okay, replication, right? So anybody here use MySQL replication? Yes, well, I think the MySQL replication is probably by far the most popular strategy both for MySQL high-vibability as well as for MySQL scaling, right? What replication allows us is to mainly scale reads. But what is a good thing is in the web applications, reads are typically a very large portion of a workload, right? Majority of them for many applications. What you need to be aware about MySQL replication? What MySQL replication, built-in replication that is, is asynchronous, right? And don't let words trick you. Semi-synchronous replication is also asynchronous, right? So semi-synchronous is not synchronous, that is actually asynchronous with some better recovery properties. If you're looking for other MySQL replication options, I would consider Perconex Ruby Cluster or other Galera-based solution. If you are the Amazon Cloud Fun, the Amazon Aurora also does its own unique replication approach to MySQL. The next trick which is very powerful is using some sort of caching with MySQL, which also allows us to scale reads, right? So anybody used things like memcache here or radius maybe for caching, right? Or if you guys have your application reaching in Java, you may actually cache it just in the application level, right? Not to go to a database all the time. Caching is really, really powerful, right? And I've seen applications which through use of a caching alone would able to reduce the traffic to MySQL by more than 95%, right? So that can be pretty cool. So MySQL also built in query cache. But the thing what we know about the query cache in MySQL is what it talks. I mean it was designed in MySQL 4.0, right? It did not get any attention until now. It doesn't scale very well both with many CPU cores as well as the large cache sizes, right? So if you have some really, really bad application, some really, really bad queries and low concurrency, you actually may benefit from MySQL query cache. But most high-end installations have to disable that. The interesting thing about the query cache show that is one of the things where Amazon Aurora seems to have implemented a number of changes. Amazon Aurora query cache is much, much better than MySQL query cache. And frankly, I believe a lot of benchmarks published. That is really the cause, right? Just much better query cache. Maybe this kind of a competition between the giants of Amazon and Oracle will make Oracle also interested to make some better query cache-like solutions for MySQL, too. Well, let's talk about applications, query cache, memcache and radius. Two other things I think are quite important to know. One is you often can cache MySQL and MySQL itself. Building with summary tables for some very heavy queries instead of running them all the time, right? You can call it like materialized use, if you like, which are like glorified summary tables. It can be very, very powerful optimization techniques. And now I think which I know a lot of DBAs or database developers don't think about is things as HTTP cache, right? In a lot of cases, caching on HTTP level is absolutely the most powerful, right? Because frankly, the best way to give your customer the best performance ever is if he doesn't even have to go from his, you know, system, right, to request a refresh an object. If it's cached right there in the browser memory, right, over disk space. And you can often achieve that for a lot of data by implementing proper HTTP caching policies, right? So, read on that. Queue in, that is another kind of big secret. If you look at that, any large scale system out there does use some queuing. At least for some of the things, right? Because of many things, right? Queuing helps us to scale writes for a variety of reasons, right? But it also allows us often to balance the spikes, right? Think about that. If I go ahead and something fun happens and I upload the spike of, you know, maybe 10x more YouTube videos, right, which needs to be transcoded than average, well, it would be overload the system trying to transcode them all at the same time in real time, right? By just putting them in the queue and then getting to them when you can get with your resources, right? Maybe doing some dynamic scalability on the background. That is much, much better architecture. And that also applies to a lot of the database things, right? Let's say if you build in something like Twitter and you have a lot of followers to deal with, it's much better idea not to try to put all of those notifications in real time, but put that to the queue, right? And then do whatever you have to do in the background. So there are a number of systems which we use for queuing. It's Revit, RevitMQ, ActiveMQ. Radius is very popular. And what we see a lot, getting a lot of traction those days, especially for certain use cases is Kafka. Anybody use Kafka here? Okay, well, great to see fair amount of hands. Now, the other thing that I mentioned is use different technologies, right? Well, I hope that's not a secret for you, but the MySQL is not a silver bullet, right? Nothing else. And as much as it's great for certain things, it absolutely sucks for a lot of other things, right? And frankly, it's very interesting for me being involved in MySQL for so many years to look back and say, well, let's say eight years ago, we will do something like we'll try to write apparel scripts which will go through, let's say, 10 slaves or 100 slaves, right? Run some queries in parallel and then aggregate those results of the group buys and all the other crazy stuff, right? And then what happened? Well, actually, we decided what those things are much better done on Hadoop or Spark, right? Then you need to crunch through a lot of data in parallel. Just don't do MySQL for that, right? It will be just pain and probably little gain. There are also technologies for full tech search. MySQL 5.6, 5.7 have a full tech search. Thankfully, it works in a DB now, right? So we don't have to pick between transactions or full tech search abilities, but it's not really scalable solution, right? It's what you can call like a low-duty, right? If you're implementing search for home DVD collection, right? Or something like that. If it's not overloaded, you can use MySQL full tech search, right? If you are building the search for many hundreds of gigabytes or terabytes of data, you will be much happy using elastic search, Sphinx, Solar, right? Or some other custom-made full tech search technologies which do that in parallel. And you will be also able to have much more flexible search with much better search quality. There is also selection of document stores you can use. There is also Cassandra, which we saw for some very simple but very data store, but very scalable and reliable one. It has been amazing. The next thing to consider is what you actually want to consider optimizing your system before sharding, right? I don't know. I see a lot of people who are being kind of lazy or they just like to go to a boss, ask for a lot of hardware, and then kind of play with distributed systems instead of just going and freaking tuning the indexes, right? I don't know why. But that is what we see the time and again. So what you can do, and you should do in terms of optimization, hardware, think about getting fast CPUs, right? Especially because MySQL executes single query in one core, don't buy into advertisement, oh, it will give you CPU with 14 cores, but they all will be less than two gigahertz or something like that. For MySQL, that doesn't work well, right? MySQL likes faster cores, and for more than CPUs, you essentially want to look at a turbo boost frequency, right? Not a phenomenal one because that is what your CPU is going to run at when you're just running one very, very heavy query, right? You want plenty of memory because accessing the data in memory is much, much faster than going to disk. And, guys, please understand, that happens even if you're using very fast storage. The fastest fusion IO, right? Or some NVMe storage is still going to be orders of magnitude slower than just going to the memory to fetch that, you know, 10 bytes you need from a field in the row. Fast flash storage, right? So, solid state drives. I mean, for operational databases, you should not be running anything else, frankly. Anybody else is here suffering with spinning disks? Why? What? Oh, okay. Okay. Well, so, I mean, I know in some cases we have some issues, right? I don't know. It may be politics. It may be what somebody doesn't believe what SSDs can be reliable, you know, 10 years later, right? It could be what they just spent all their storage budget for the next 10 years buying EMC for $10 million five years ago, right? Or some stuff like that. Well, but anyway, if you're being practical, you know, you want flash storage. And you also want to have a good network, of course, right? A lot of the modern application that comes to MySQL can be dominated by network through latency, right? Because I recently looked at, let's say, how much... If you look at the gigabit network, right, for example, often you will have... What's going to be around you, right, in terms of in the data center? I think it's probably about 300 microseconds, right? Now, if you look at the MySQL, it can really process the simple queries typically in less than 40 microseconds, right? So if you think about that simple selects or something, latency is going to be dominated by the network latency, at least with one gigabit network, right? Another important thing about the networks is to think about the distance and number of hops. I have seen so many people making a mistake of placing MySQL server and app server very far, right, from each other, either distance-wise or just having a lot of network equipment in between which can add up a lot of latency, right? If you go through a few routers and so on and so forth, it's all about the environment. Well, I probably would be preaching to the choir in this case, but I think Linux is a pretty good operation system to run MySQL, right? Would you agree with me, guys? Yeah. Right? Yeah. And actually, that is also the more popular ones, right? I think a lot of large-scale deployments are done on Linux these days, right? I see, I think, a little bit more, actually, three BSD deployments, right, which start popping up over the last couple of years, but Linux is still absolutely dominating. New MySQL versions generally scale better, not surprised, but scale better with multiple connections, with many CPUs, with more complicated queries because of Meriboy optimizer. But for simple queries for a single thread, MySQL has actually become slower and slower over the last releases, right? Last benchmarks I've seen, MySQL 5.7 is somewhere like 15% slower than MySQL 5.0 running single thread benchmark like SQL Bench, right? You can look up Mark Colligan has wrote about that. MySQL 5.7, you guys heard about that? That's a pretty interesting GA release. I see there's a lot of still things kind of in flux, there's some changes implemented, right? So maybe I would not just ration up great to 5.7 today, but wait, the dust settled. But if you guys are starting the new development and plan to go in production in a few months, 5.7 is a great choice. And we are also working on the Percona server, 5.7, which should be out sometime in February, and we have a release candidate available already so you guys can check it out, give us some feedback. Configuration, right? Some people may disagree, but MySQL default configuration sucks. And it is by design, right? Because really MySQL was designed something like you install new server, it sits out there in the corner, not taking a lot of resources, it can run some applications, right, and so on. So it was not designed as to go in and kind of take over the source of the box and not get anybody else to run. What that means is what you want to tune MySQL server, right? And here's a link to a webinar I did on this topic. It's not rocket science. Typically you get like maybe 10 options you tune in MySQL and you get it 95% where it needs to be compared to defaults. Storage engine, right? When it comes to storage engine, INNADB is a default storage engine and it is absolutely fantastic for a lot of workloads. In some cases, you may check out where TokuDB for high inserts and if you want to get some much high level of compressions in INNADB, you can explore that. Anybody try TokuDB here? Okay, well, I see some hands. That's good to hear. I have a feeling for sharding. So when do you want to shard? Well, here's a challenge we have, right? If you shard too early, we are likely to waste our resources because development and operations are going to be more complicated after we shard it, right? But if you shard too late, you can run into a wall, right? Because your application just unable to handle the performance required on that. So in this case, what I like to be thinking about is the term called architecture runway, right? Sharding is one of the architecture considerations you guys have and think about this way and take it as a part of your architecture runway planning with all the other things you guys consider as a part of architecture thing, cleanup, technical depth, other optimizations and so on and so forth. And what you need to understand in this case is how long it's going to take you to implement the sharding. Depending on your team pace, depending on your application complexity and so on and so forth, it may vary a lot, right? Like, guys, I can honestly tell you people have come to us, asked for sharding advice and tell me, oh, that's fine, we'll go ahead and implement over weekend. I'm saying, are you crazy? You're not going to implement over weekend. What they actually did, right? They had the application simple enough, small, very agile team which will go and implement sharding over weekend in the application, right? On the other hand, I have seen some other legacy application which was written for, you know, 10 years, ton of queries, complexity. It may take a year or more really to implement the sharding, right? And I think that's important to make yourself an honest assessment and balance with your capacity how much you guys can run your current application, with how long it will take you to implement sharding, right? So that brings us to the capacity planning, right? So for us not to run into a wall, we need to know where that wall is, right? Makes sense? So we want to figure out how we can do that, to understand that we want to do some estimates, maybe do some benchmarks, be conservative in this case because it's much better to predict a wall 20% lower, right? Than 20% further down, right? And when you run into a wall and you're still not ready for implementation. And one very important thing is do not ever plan for linear scalability, right? So if you're looking, let's say, at your system and saying, oh, well, currently I see only 10% of the CPU used. So that means I can handle 10x of query on this system. That is a very, very bad math, right? That's not going to happen for many reasons. Now, I have been kind of very critical of sharding, but I have any benefits of that, right? Well, and as if almost everything in this wall there are always like positives and negatives, right? If you look hard enough. So what are the positives for sharding? Well, sharding is really your road to the ultimate scalability, right? If you guys are building something on Facebook to scale, then you will need to shard sooner or later. And sharding may allow you to reduce some other complexities, right? Or eliminate them. So for example, I have seen people saying, hey, you know what? Yes, we do some caching, but you want to avoid having that extensively complicated caching level, right? With all those kind of invalidating cache properly, right? Our developers don't like that. It's complicated. Well, we just better buy the bullet ones. We shard it. We'll have most of our data in MySQL memory. And accessing data in MySQL memory is pretty fast as well, right? You guys have seen the numbers of more than half a million in a second. Asynchronous replication. That's another one, right? I have seen a number of customers saying, oh my gosh, our developers they absolutely hate dealing with asynchronous replication. Because it has this property of it works most of the time, right? I can go in most cases on my slave and get the data, which will be reasonably accurate. But sometimes I get just some silly stale data. Developers don't like it a lot because there is no built-in guarantees in the replication about the data stainless which it will be to provide. So in many cases, we see people just sharding when they say, hey, we read from masters, we write to the masters. Our slaves are used for availability purposes and for some other applications. For example, some reporting. Because reporting, I know reading a little bit stale data not a big deal. Sharing also helps us to have very security and compliance as well as possibly having the data closer to users. So for example, a lot of software as a service applications often would shard things to really separate the data between different customers completely. Different customers live in a different MySQL instances so even if developers do some mistakes, it's very hard for this data to intermix, right? And frankly, some of the customers may even require this kind of isolation as a part of that contract so that is a benefit, right? Now you guys also may hear some shard on the global scale, right? Some of you may have heard about those recent law changes and as well as like political changes in the countries. There are some countries may require you to keep the data for the users in that country. It's much easier to move to that kind of environment if you shard already, right? Makes sense. So often that will be kind of not purely technical consideration but something beyond that. Now costs may also be an important factor here. Now, if you take a look at the majority like managed hosting providers and the cloud providers, they really kind of want to take advantage of your pain, right? So what I mean by that is you would find what the margin, right, or how the cost escalates to a very, very high powerful system is phenomenal, right? You can see high-end system being charged, you know, 10x as a low-end system even if you go to, I don't know, let's say, the store to buy one, right? The difference in price will be only like 2x or so, right? So what that means in practice is what you often find if you're cloud providers or, I mean, even if the systems you buy, the systems which have an optimal price performance ratio, right? To give an example, you can get four socket systems with Intel CPUs, right? But those will be very, very expensive those days, right? They're much more expensive than the two socket systems even though they don't even give you like typically 2x of performance, right? So if we don't have to go for that the most expensive hardware, we can have a much more optimal system and Sharding allows us to pretty much pick the systems of any size because if you have implemented Sharding well, you can Shard over 10 systems or 20 systems, right? And that really should not matter. And as I mentioned, that's especially important in the cloud because cloud may have a relatively lower instance size what you can get compared to physical hardware you can buy, right? In terms of the top performance which is accessible in the cloud. So when do we Shard, right, as a summary? Well, first is Sharding can be a good idea when it's easy in your case. Some applications are easier to Shard than others in both in development and operations. Second is when scaling up is an impossible to expensive. I mentioned cloud, but enterprise can also have a silly politics, right? I remember the guys who had to Shard because there was a MySQL, DB80 and the OPS team and the OPS team believed what 16 GB ought to be enough for everybody, right? And they would not provide the virtual instance with more than 16 GB of memory, right? So they had to Shard, well. Now, you also may want to do that then your application is growing so far so Sharding is imminent anyway, right? And in this case, you may want to invest in Sharding which will give you huge performance gains compared to some small optimizations trying to pick up 5% here and 10% there. So what are a few Sharding questions we can talk about? Well, Sharding level, key, you need how we handle have ability in Sharding technology. Let's look at those in more details and that will make sense. What is the Sharding level? Well, there is two ways to think about that. One is you Shard only on the MySQL level and other you kind of think at your Sharding by deployment unit to a full stack level, right? So you can say, hey, I take my MySQL and web servers and the cache, right? And I Shard all of that completely, right? Now, you often do the second one, for example, if you want to place those things in different regions, right? Or something like that. Or to get either better security isolation and isolation at all, right? Because if you have essentially some very thin load balancer which kind of splits your customers to different pods, right? Or wherever you want to call those nodes then they're completely independent, right? And the failure affecting one of that is probably not going to impact everybody else, right? And you may have just, you know, 5% of unhappy customers which is much easier to deal with from all kind of things, right? Including providing them a good customer service. Sharding keys. When you pick a key on the database for Sharding, we can see a couple of things. One is what most accesses, right? Simple accesses should go to a single Shard, right? If most of your accesses hit all the Shards, that is bad design, right? Now, no Shard is large in terms of data size or loader generates, right? So, for example, Sharding by country may be a good idea in the first one, right? Because most accesses for many applications will go to a single country. But then there are way too large countries, right, for most applications. And they're very disbalanced, right? I mean, you have a country like China or US with a ton of population that have somebody like, I don't know, let's say Czech Republic, right? Which is, you know, much, much smaller. Another thing to consider is when you Shard, you don't have to pick one, right? In certain cases for certain data access points, you may want to pick a couple of Sharding keys and double store the data one way or another, right? So, to give you example, systems like Flixter, for example, right? We just social network around movies. So, it will have two very different, but the most important data access pattern. Even through a movie, when you have all the comments and a vibe about a movie, or about you as a user, what you have said and so on and so forth, right? So, in such case, it would make sense to double store some of the data which is very fast to access for one dimension or another, because, well, storage is cheap. Sharding unit, what do we do here? Do we Shard by physical MySQL instance schema, or do we kind of Shard it on a very logical level where you can have multiple Shard objects placed in the same set of schemas and database? Well, it depends, right? Typically, if you are dealing with something like software service where you have, I don't know, that's a small number of customers which pay you lots of money, right? Maybe 1,000, 10,000, 100,000. You can actually even set up a small physical MySQL instances, especially those as if containers, right? You can, you know, run pretty large amount of them effectively. But if you have a ton of users, something like a Facebook, then typically you end up having multiple kind of Shard keys short in the same set of tables or schemas. And I think to consider what is hybridity, right? Because the more service you have, the more there is chance for one of them would fail, right? So, pretty much, if you have rolled out the large Shard, you most likely will need to implement some hybridity as well, right? And it even becomes more important than just single server, because if you just have one single physical server, I've seen instances when people would have no downtime in many years, right? And they just rely on backups and it works for them. I mean, if you think about somebody like Facebook, right, or Google, they have many, many failures every day, right? Every hour even probably. So, if technology doesn't compensate for that, that would be a disaster. So, pretty much two common approaches to that. You can shard even over master-slave clusters, right over the orphaned effort, or use PXE or some other Galera-related technology. Let's look at some sharding approaches which exist those days. Roll your own, which is probably the most popular one, especially on the large scale, right? I mean, all those big guys typically have something very tied in with everything that they're doing. And in my school world, it's heavily custom. VTES. VTES is actually, in my opinion, the open-source technology is to watch, because I think it has been proven in production in YouTube. But those guys are very, very serious about making that into a good open-source project, and putting a lot of very good documentation, and so on and so forth, right? So, I'm very excited to see what that would be. There are some other technologies, like JetPants and ShardQuery, which kind of exist out there in an open-source, but they don't have nearly as supported momentum out there. Other things. This is a proprietary technology which has a built-in... MySQL Comportable has built-in Sharding, right? From technological standpoint, it's actually quite interesting, but because it's kind of closed-source in a small community, I didn't see it having a lot of traction. MySQL Cluster, that is another interesting technology, which does a certain level of Sharding automatically for you. Anybody runs MySQL Cluster here? Anybody tried Ryan MySQL Cluster here? You guys know it, right? People who tried Ryan MySQL Cluster were much more of those when they were still running it, right? This is rather complicated technology with a lot of kind of tricks. It's absolutely fantastic for certain workloads, right? But it's not as great for many other things. It's relatively complicated, and what I will also mention is there's actually had been a lot of interesting work done with MySQL Cluster over the last couple of years, or last three years, so if you gave it a look like five years ago, you may want to check it out again, right? But I would say also, when you speak about MySQL Cluster, it's kind of something that gives you a medium level of Sharding, right? Maybe if you need to Shard and get, let's say, 10 nodes with all the data fits and memory, that may be the application for MySQL Cluster. If you are doing math and saying, oh, I will have 1,000 nodes in production in a year or two, that is not nearly what MySQL Cluster can support, right? And in this case, it's not going to be good enough. A few others. MySQL Fabric, that is a Sharding effort by Oracle, which I think is interesting, and I don't know what I mean by interesting in this case. I mean, I really would be interested to see it more in production. So far, there is a lot of a developer talk from Oracle about that, but I haven't seen it really deployed at the large scale. Tizor database virtualization engine, that was also pretty nice open source project, but as they kind of moved out as a company and focused on different things, I don't think it's been well maintained anymore. Scale Arc has some rule-based Sharding engine, which is also commercial, but I actually see a lot of Scale Arc deployed, right? So that is a pretty common solution, especially by enterprise customers who don't mind using this kind of proprietary software. There was alternative to Scale Arc here, Scale Base, which I keep here as a zombie on the slide, as a reminder, what a lot of proprietary technology companies may die and kind of the technology we had can just disappear and stop being available. So a few things in summary, right? You guys can see what there are multiple technologies available for Sharding. In the space of MySQL, unlike some others, there is no just standard way to Shard, right? There is no some Sharding solutions, which 99% of the people are using out there, right? And you just can't follow. So you can see it as a downside or you can see it as an opportunity to become creative. That's up to you. So if you guys are looking for Sharding, what we, as a Berconna, can do? Well, there is a lot we can do, right? We have both helped a number of companies to implement Sharding as well as supported them with Sharding environments, youthful support, or managed services. And if you guys are very interested to learn more about MySQL, is that MySQL is a kind of big part of your life. Consider coming to Berconna Life in Santa Clara in April. We'll have a huge amount of MySQL content. And this year we'll also have content about a lot of other open source operational and analytical databases, right? MongoDB, Cassandra, and others. Well, that's it for me. And I would be happy to answer your questions. Oh, okay. Well, I mentioned that I didn't clarify the abbreviation. So when I mentioned PXC, that is Berconna X3B cluster. Yes, yes, no, but that is a good catch, right? So is that actually what happened? You entered PXC and there is no PXC out there? Okay, can you send me a screenshot of our marketing, right? And tell me, you know, how come, yeah? You can't even get us to the front page? Okay, any other questions? And if you want to troll me, that's fine, right? Okay. Well, yeah, two things. First, let me put in another plug, right? Actually, what I would be, one of the talks I'm very interested in is Perconna Live. Facebook is going to talk about how they're doing backs up at scale, right? And I think it's, for me, it would be very interesting to see what exactly strategy is the very important, right? Now, backups, you need for charging, right? And what I see a lot is that people have some sort of fuzzy backups, right? So, for example, say, hey, we take the shards and backups plus the backup binary logs, right? So if you ever have to need to restore the shard to a given point in time, approximately, they can do it, right? Having something like on a live starting system doing the kind of a global checkpoint that is possible but typically is quite, is a bit painful, right? I mean, you can, I've seen people doing that by, for example, you know, like posing replication on all the slaves, right? So not to impact the master when making some sort of, well, note on the binary log position which corresponds to them and taking the consistent backup, right, for everything if consistency is needed. Now, another thing about the shards is consistency between the different shards, right? It's typically a little bit loose. It's not like you have a foreign keys, right? And it's kind of absolutely going to be out there. So, because of various failure scenarios, if there is certain data consistency which has to be maintained between the shards, then it is good to have certain scripts to check it and fix it because data drift will happen, right? And you better have some tools to fix it. And that is what people can do, right? They can, well, in the worst case scenario, if something happens, I restore backup, maybe it's not kind of exactly consistent with some other shards, right? But I can, well, I know there is my source of truth, right? For the data, let's say this user lives on this shard and then I go and check the things to ensure the relationship with other shards are consistent. Does that make sense? Okay. Well, I think you always have to be looking at what exactly problem you're solving. So, because you can really get, like, 100% consistent backups, right, across, of course, but that's going to cost you, right? And in a lot of cases, people just don't pay those costs. Because, again, I'm on other things. Well, like, here's the thing, right? I think backups in general, if you think about backups in scenario where there is redundancy and there is no redundancy, there are two different things, right? Because if I, let's say, have my laptop, right, and I just lose my hard drive or something like this, right? Then in a lot of cases, I will have a full restore, right? And that is going to my use case. Now, think about if there is a master and the slave which is constantly replicated. If I lose hard drive on this laptop, I will just fail over, right, to the slave. I don't do backup restore in this case. When does backup restore happen? Well, if you have some developer with dirty fingers, right? Anybody seen those, right? Which are kind of, like, you know, ruin some database. What happens in this case, though, is typically they trash one of the tables. Well, some developers are very gifted and will trash all your tables, right? But typically it's going to one of a few, right? So you have to restore the tables which were trashed or even the data which was trashed, right? Which is a very different backup scenario, restore scenario, right, than you may be thinking about, right? So that is why when you have a replication, often you have to optimize for how you can restore pieces of the data, right, and maybe kind of do partial binary log recovery to catch up those pieces of data you restore, right? And fun stuff like that. Make sense? Okay. Well, I think, yes. In some case we will see that as a consolidation, right? Possible. I think things have actually become a little bit better in this regard more recently with MariaDB and now in MySQL 5.7 offer multi-source replication, which allows you to find in replication if you want to get all the stuff together, you may be able to do that. We also did some work with a tungsten replicator to do that. But, well, it is possible, but that's also, I would say, not totally easy because in a lot of cases people would have created the ideas which are conflicting and so on and so forth, right? So that's going to depend a lot on application, what exactly you're going to do. Well, yes, I think that is a... And I think in a lot of cases it's also process of the during the sharding, right? Because when you speak undo the sharding, is that a consequence of a failed sharding project, what they implemented the sharding and now that application version which supports sharding just blow up, right? And we want to go back. Or it was in production for five years and then they say, hey, you know what? Application is not growing and now hardware is so powerful anymore, right? Those are two very different cases because in the first case we'll have to kind of temporary maintain some scaffolding so we can always go back to uncharted system, right? Okay, any other questions? Well, I think it depends a lot on the scale, right? Line orders I see very well used on the kind of smaller scale. If you look for large scale resource, the AWS is probably the most popular one we see. We see some more of a rack space, right? People who has been using rack space managed service started to use rack space cloud a lot, right? We like the support approach and other goodies. So it has been worrying, right? Google cloud SQL also looks interesting. But I think, you know, when it comes to us, people really choose cloud for their databases, right? They choose cloud for their applications and their databases are going to live in that cloud and that's what we have to deal with, okay? Anything else? Yes, go ahead. Talk a little bit more about what? Balance in the demand spikes. Oh, okay, okay. So when I spoke about the spikes, right, I was talking about the queuing, right? And here is an interesting thing, right? If you think about read and writes, right? Which of those are really hard to deal with? A lot of people speak about writes, right? Writes are hard. You have to actually modify the data. But actually, reads are much more hard in many cases because when you are doing reads, you have to get the user data right now, right? Until you have given that user the data, I mean, he doesn't get his value, right? When you speak about the writes, in a lot of cases, we can just put them in some sort of queue and apply later. And frankly, my SQL is already doing some effect for you, right? Think about what happens when you update the data in the debit table. It goes essentially in the queue like a log, right? And then it's going to be flushed from a buffer pool to the disk sometime later, right? If you log files at a large, it's maybe much, much later, right? And that is essentially the idea for writes. If you have some writes which are very expensive and you can afford certain delays, right? You put them in the queue and have some background working to apply that at the given pace instead of slowing all your users, right? So that, I think, is the main thing. Now, if you look at the overload management technique, which I would mention, is, well, not all the features are created equals from a user value standpoint, right? So make sure you can disable them in the load spikes, right? If you can bring up in more capacity, right? Or if you don't have any other tools in toolbox. I remember for years, for example, you guys could see Wikipedia search sometimes being down, right? They're saying, hey, guys, you know what? Wikipedia, you didn't donate enough money, so we don't have enough servers, so search is not working, right? Come back later. But if you just use Google as a search, right, it still would be working and performing very, very fast, right? And I think that is very important to prioritize what a user experience, right? And not trying to make it kind of bad performance or a bad experience for all your users for all features, but better to have, you know, certain users to have a downtime, right, and certain features not work, and others to be, like, absolutely fantastic. That makes sense in terms of load management. And what that means, however, right, is what you want to design the features so you can disable them easily, right, in production. So then the feature becomes broken, right, or it creates too much unanticipated load, you can maybe disable that. And what's that? Well, yes, I think you're speaking about another interesting thing, right, which is kind of a good development practice, right? If you're developing the large features on the scale, often gradual rollout is great, right? So your rollout feature for 5%, then you say, oh, it works, you know, gradually you rollout it for more, right? Well, yeah, absolutely. So it can be back and forth, right? It doesn't have to be black and white. Yes? Well, I think that is a very good question, because if you think about that, MySQL is not really great analytical data-raise, right, in this case, I mean, it's because those days you have a lot of data, and if you want to analyze data, if you want to have features, typically, right, being able to crunch a lot of data in a parallel, right, and use all the multi-course. In this case, it's some sort of ETL process, right? And I mean, there's a lot of integration those days, right? You can use Scoop with MySQL to get the data in Hadoop. There is multiple tools right now how you can get from binary logs to Hadoop. You can use tons and replications. There is, I know some work, how Kafka is being integrated, right, with MySQL. So you get some data out, and then put it into whatever system you would like to do, right? So lots of choices those days. Yeah, and I would also mention that some people, right, we see who really love MySQL and they like pain, they can use multi-source replication to try to get it to one Frankenstein box, right, and try to run queries out there. Yes. Well, after all, he is dead, right, if you haven't noticed it, right? Unfortunately, with CPUs, we pretty much don't have a choice those days, right? I mean, in server-grade CPUs, Intel owns the market, right? We may have something to come out of your power, or like ARM is kind of picking up a head on the server, or there is a risk five, right? But it's all probably like five years ahead. For now, you typically would look at the zeons, right? And the choices you have is pretty much going to be defined with how many sockets you have, right, and how much memory you support, because low-end zeons would only support like 32 or 64 gigs of RAM, right? Not enough, right? And then that is pretty much its main direction, what you're looking for. As I mentioned with MySQL, for most workloads, you want faster cores, not necessarily so many of them, right? But that, again, varies, right? In some cases, you may want it with more cores if your workload has enough concurrency. So that's what I would mention. Any other particles you're looking for? Well, I would also mean a lot of memory, right? I mentioned for that. If all your cloud fields in memory, that's fantastic. And for solid-state drives, SATA and SAS are bad, right? Because that kind of bandwidth that SAS and SATA interface provided is just way too narrow. So you want to get some NVME, or like other PCI Express-based storage if you're looking for best performance. Yeah, NVME, right? Or other PCI Express things, right? For example, Fusion IO is not quite NVME because NVME is kind of standard, but it's also very fast because it goes through PCI Express bus, not through some, you know, funky interface which was designed for spinning disks. What's that? Well, for Flash, if you have a chance, avoid SAS and SATA drives, right? Going to limit your bandwidth compared to PCI Express. Well, I think this is kind of a little bit Apple and Oranges, right, if you look in this case. So if you speak about Galera, that is a replication technology, right? Wherever you, so, in this case, right? And Galera doesn't necessarily help. It helps you in terms of that you have a little bit more predictable latency in replication, right? But still, you can't scale your write 10x, right, with Galera. Wherever it's you're using one or another, you need to think about that, right? Also a thing is, actually I would say because of how Galera works, right, often the limit of a data size per instance you would keep if Galera is smaller than for a conventional MySQL, right? I mean, I wouldn't go into, let's say, 10 terabytes Galera cluster because if you ever need to do SSD, that's going to be very painful. Any other questions? Okay, guys, and I will hand it out today and tomorrow at the Percona booth at Expert Hall. You know, if you want to stop by, please do. We should also have some toys out there, right? If you'd like to. Thank you.