Hitchhiker's Guide to MySQL
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Dijk, Liz van
Did you wake up one day to find a baby database left in a box by your front door? While it was cute and fairly self-sufficient at first, has it now hit database puberty and is it making you wish there were such a thing as DBA school? Did you feed your database after midnight or let it get in contact with water and is it now making your life a living hell? Don't panic, because here's a 20 minute cram session of the most basic database parenting skills as well as general things you should be aware of when starting out with MySQL.
 Hi everybody, thanks for joining me. It's such a pleasure to see that Faustem is alive and well during these strange times and that we're able to keep bringing our little community together. My last round as a speaker in this dev room might have been 10 years ago, but Faustem has always felt a little bit like coming home and not just because of the lingering smell of French fries in the hallways, which are doing this dearly. My talk is scheduled to be the last of the day and has a long time attending myself. I know that our dev room is visited by the sharpest talents in the MySQL world and I'm really proud to get to work with some of these people regularly. After so many years, I'm happy to be continuously impressed by all the new developments, but being at the cutting edge of this space and listening to talks that focus on solving problems at the highest levels all day, that can also create an impression that using MySQL and becoming a part of this community is quite intimidating. So here I am to lower the standards. Just kidding. Here I am to extend a hand to all of those who are here to learn more about MySQL by necessity, because it's hard not to nowadays, but also because thanks to its long life and history can be really hard to know where it starts when all you really want is to make sure that things aren't actively on fire. So my name is Liz Mendeich and despite having spoken in this dev room before and having considered myself a part of this community for the past 10 or so years, I don't actually use or think about MySQL that often. In my role as a solution architect currently, I'm more often called to sound knowledgeable about various parts of the stack rather than actually having an environment where I can build real world experience. So I find myself constantly needing to review even the most basic things and making sure that what I'm putting together or demoing hasn't diverged too far from the current reality of things. I'm what you could call a perpetual beginner at MySQL and I believe given how widely MySQL is implemented, that probably aligns pretty closely with a lot of the engineers to whom it's just one more part of a very diverse and a rapidly evolving stack, but still one they want to learn about. So I work for PlanetScale. I fulfill a technical presales role there and I love it as a company, PlanetScale has got a very strong vision of what the database of the future looks like and it's very deeply invested into the MySQL ecosystem as well as Fites, which Lomi also covered during his talk. I wish I'd accumulated anywhere near the chopsy and the others in this dev room have in these past 12 years of being involved with MySQL, but the reality is my knowledge of various parts of the stack is kind of like a sandcastle and in my role I've had to learn how to be effective at starting from scratch multiple times and finding the right tool belt to get me 80% of the way there. So that's what I'm hoping to share with you here today. So you're a developer, of course you know of MySQL. You can write queries sort of or at least you know your ORM does and for the most part it just feels like a bit of a dinosaur that's more or less a given wherever you encounter it. No wonder. The software is so old. Clearly there ought to be better solutions for things, but we're all just kind of used to it, right? So no matter the actual situation, most people don't grow up dreaming of becoming a DBA specifically and more and more the responsibility over this very critical part of the stack has become shared with that of many other components and so we have just as many stack overflow DBAs as we do developers. First of all I am one too. So hello friends. As you can imagine floating up and down the stack every day and only having a couple of moments to really sit down and learn things. I'm a very good Googler. In fact to write this talk I looked up Top MySQL DBA tools in 2021 just to make sure I didn't have any glaringly obvious emissions. Of course everyone's kind of got their own favorites but my main goal for this talk was to collect some of the tools and the areas of knowledge that any five minute DBA should be thinking of first when stepping into care for what's often their company's most critical set of data. There's so much movement and history to be found on the internet that it can be hard to identify what is proven and what is stood the test of time versus what is a brand new bleeding edge development that you probably shouldn't be unleashing right away. So without further ado here's what I'll be running down. Keep in mind any of the areas have multiple books worth of material to dive into. Some of them are written by the people in this very dev room as well as having seen 25 years of ongoing development. So I'll mainly be pointing you to the first baby steps on most of this but I hope it can give you a bit of a guide as to the main areas to have a plan around when dealing with MySQL and your development stack. Although we like getting cozy and from a community standpoint we certainly maintain very strong ties. It's very important to be aware of the heavily diversified landscape around MySQL nowadays. It's especially pronounced for us at FOSTEM this year as MariaDB is graduated into having their own dev room track. The list on this slide is far from comprehensive as cloud specific implementations of MySQL are kind of popping up left and right, not unlike PlanetScaleDB. While all of them still speak MySQL when finding solutions to specific problems that you're experiencing or understanding which tools may or may not work it's a good sense to know it's good to get a sense of what flavor exactly you're dealing with as well as its version. So a quick way to spot your service version is by logging in using the CLI client and scanning the first couple of lines. You can see that on the slide. So looking at my local example you can see I'm running version 8.0.22 which more or less already rules out MariaDB since they opted to break away from Oracle's version in past 5.5. Taking a closer look at homebrews packaging reveals that they're pulling them in from MySQL.com which means this is a normal community addition and that's good information to have. Regarding versioning in general for MySQL although I'm sure there's a reason that MySQL was stuck in a different dimension for about 13 years in 5.somethingland but coming into it as a new user it may not be all that obvious that 5.7 and 8 are actually only one major upgrade apart. So MySQL 8 was officially released two years ago but it's pretty common and not overly worrisome to still be using 5.7 at this point. If you do happen to be on 5.6 still I believe you may, I believe you have exactly until the end of the month to make your way up as official support for that is going to be ending very soon. Now cloud versions generally relate back to Oracle's, Vercona Server tracks those as well. Most of what I talk about here applies to all flavors but knowing which one you're running specifically is going to be quite helpful if you're looking to get help addressing more complicated challenges. One thing that's good to bear in mind is that the jump from 5.7 to 8 introduced quite a few changes. In some cases different ways to handle certain basic operations. So oftentimes you'll see that reflected compatibility of tools with either version tends to explicitly be called out in the documentation. So here that might be the case. Now in all my years of dipping in and out of MySQL and often needing to come across as knowing exactly what I'm doing, which are the tools that I've always come back to. So first off I don't really bother with GUI tools unless I'm planning on moving in long term so I can't really help you there. There's a few nice administrative ones available. There's SQL Pro or Adminer but I generally don't even really bother because the default MySQL CLI has evolved so much over the years that I hardly even need to look beyond that. So I love it because it runs basically everywhere. It comes with a boatload of documentation built in and honestly it's all I need 99% of the time. So having spent a bit of time over the years getting comfortable with it has really paid off in efficiency. So if you haven't heard of MySQL Shell, which is the CLI tools up and coming replacement, you should check out the linked presentation. It's a presentation by Fred and that'll give you a sense of what's coming there. I think it's quite exciting. So second point here is the Prokona toolkit, which I'm assuming will have been referenced quite a few times in this step room already today. This is probably still Prokona's most widely used and loved set of software and it's the end result of many years of consulting in MySQL implementations, all possible sizes and shapes and they built some tools that really help create visibility and understanding at all of those levels. The list of what's available can be quite overwhelming. I'm just going to call out a couple of gems in particular in the next slide. But for now, the last piece of software I would recommend is someone who's often confronted with a list of potentially questionable queries to pour over and only having topical theoretical SQL understanding myself. It's called SQL check. This is a tool that has attempted to take the work of our community's very own Bill Carwin and his brilliant book called SQL Anti-Patterns and it applies that analysis and those recommendations against a query or a list of queries that you provide. For me, this has been a great help in identifying problematic structures and sometimes I suppose I really like that it is a more hands-on way of learning about how that theory applies in realistic environments. So to come back to the Prokona toolkit, in the wild you'll be able to identify these tools by the PT prefix that they all use and their applicability is very diverse. I wanted to call out a few of the most commonly used ones as they're ideal for diving into your environment and solving problems very quickly. Archiver allows you to safely and gradually move data around between tables into an external file or simply purging those rows altogether. The reason you might want to use this is that on very large tables, running delete statements could lock up rights to those tables and so those end up being a blocking operation. So what archiver does is it takes your instructions and it diligently works its way down the entire table. I think in the documentation it's described as nibbling down the table by breaking that archiving job into smaller non-blocking steps. Next tool here is called duplicate key checker. It's one that helps you understand which keys in your schema may be duplicates or redundants and those generally end up taking up unnecessary disk space. So this is a great little spring cleaner. If you're dealing with a schema that's seen many years of gradual growth and lots of haphazard attempts at optimizing performance by adding various indexes. Now query digest might be the single most helpful tool in addressing bad performance in MySQL. It's used most efficiently by allowing it to analyze a chunk of representative traffic which you can capture by turning on the slow query log or you can query the built-in performance schema in MySQL to get a list. But depending on the amount of data available, query digest can spit out a normalized list of those queries that your server spends most of its time on. Those could mean being, you know, those could either be objectively slow or badly written queries but they could also mean very fast ones that are just executed many, many times. This overview that query digest gives you shows you exactly which queries to target for optimization. After all, even if a query takes just five milliseconds to complete, if that particular pattern takes up 80% of your workload and you can bring it down to three milliseconds somehow that will allow you to stretch your resources much further. So next in this list is stock which does more or less exactly what you would expect it to. It's built for those situations where there's sudden unexpected shifts or spikes that are disrupting your environment but you can't quite figure out where to look first. And once you've logged in, the problem's gone away and nothing seems immediately wrong. So stock is a tool that can be set up to wait for the problem to start occurring and then it proceeds to collect an absolutely ridiculous amount of information from every bit of tooling imaginable. So that spike is captured and monitored to its fullest extent and you can calmly sift through all of the data afterwards. You don't have to worry about it finishing before you're able to identify what's going on. Now variable advisor is a tool that I think we all wish existed a long time before it actually did and at this stage it might not be as useful anymore but it's still a nice little tool to run against your environment especially if it's a little bit older. Because initially my SQL and you know DB's various startup variables used to be the first stock for really glaring performance issues and quite often making tweaks in that conflict could squeeze out an incredible amount of extra performance even in a single instance. So nowadays those base variables tend to be a lot more intelligently set so there's not really that much magic that we can do here anymore but still a useful review that's always very helpful in avoiding the most obvious performance issues. So I'm going to skip over the others in this list right now as they're going to come up again later but whether you're a five minute DBA or a 15 year one if you're not using at least one of these tools at various points in your career you're definitely missing out. So switching over to privileges. Considering that relational databases tend to store a company's most business critical data there security in the way that access is provided is often overlooked and for my SQL specifically it can also be misunderstood so because my SQL displays an internal table called my SQL user it's important to understand that permissions or privileges are granted to both a user name and their location meaning the host that they're signing in from. So a user might be allowed to read from a remote location and only be able to write when signing in from local host. If you use a client from a dynamic IP address you might not necessarily be allowed to log in again when that IP address changes etc etc so at times this can be both frustrating and confusing and as a result grants are created haphazardly over the years sometimes and rarely if ever reviewed. So similarly by default when my SQL is installed it's not overly concerned with locking down root access assuming that you will be setting that up yourself but even the most seasoned DBAs are known to forget that step from time to time. So when going over the environment and trying to make sure that it's in good shape always make sure that you review which grants have been allotted and also consider running through this executable called my SQL secure installation which is included by default and that is designed to help you close the most glaring security holes on a fresh installation. Now we covered this a little bit when going over the tool belt earlier but here's another look at your basic monitoring and troubleshooting tool set. So my SQL can be very greedy when it comes to resources so when performance slows down it's always important to look at your operating system level metrics just to get an understanding of the nature of those interactions. Are you maxing out CPU? Are you IO bound on your disks? Or have we outgrown the available working memory? We might just be doing all of those but it's still good to take a look at those symptoms before trying to make a diagnosis of what's actually happening. So Prokona Toolkit again provides a couple more tools to help you relate your operating system level metrics to database performance mostly by offering them in more readable formats. So just be sure to have those ready and yeah when something happens you can take a closer look there. Another piece that might be interesting to cover here so when you know already that you're being plagued by certain slow queries the CLI Tools Explain function allows you to gather information about how the optimizer interprets it and what it expects to do to fulfill that query. That could uncover a need for additional indexes or different ways that you can write your queries to be more selective so very helpful to help you deal with specific troublesome queries and then lastly if you're not already monitoring most of this permanently, tracking it over time seems unlikely but you should. So there's another tool by Prokona that's called PMM and that's probably your quickest fix as it's absolutely packed with features to help you get a deeper understanding of your database environment and how well it's doing so definitely check that out. Now I would like to believe that anyone building a serious database environment today has given at least some thought to addressing backups but that doesn't necessarily mean that you know what that looks like and I wanted to cover a few of the basic approaches that we have available and those tend to fall into two main categories. A logical backup should be considered essentially a text-based representation of your database at a specific point in time. It's usually structured in such a way that when it's fully executed the end result is that it rebuilds your schema and its data in full wherever you might need that to happen so this is a method that's often used to allow developers to rebuild the database structure in their own environment or it allows teams to extract and transform the actual data to some extent and then they can store that in a different space so what tools like MySQL Dump and MyDumper do is they effectively log into your databases though they're a normal user. They scan all of your tables to translate that data into something executable in this case, executable queries that allow you to restore that particular state. Alternatively when we're talking about physical backups we take a more system level approach to that so we target the actual data files that MySQL stores its tables in as well as grabbing a copy of its working memory at that particular point in time so I might be generalizing quite heavily here but on the whole taking a logical backup tends to be a bit of a slow and steady process especially when it comes to recovering because all of that data needs to be read and written into a new system again whereas taking physical backups and using that strategy is generally the path to bringing your environment up and running again a lot more quickly and they also tend to be executed with less impact on the running system. Whichever method is being used both of those approaches generally make it difficult to restore the system to a very specific point in time you're kind of bound to the moment at which the backup was triggered so in situations where user action is caused irreparable damage and you'd like to restore to a state just before that action happened well if that's a situation you find yourself in I hope it wasn't you causing it but even if it was what you need to be taking a look at is MySQL's binary log tooling and pray that your backup strategy includes backups for the binary logs as well because tools there will allow you to replay those logs against a set of data and allow you to recover to a very very specific point in time if you're able to pinpoint when the mistake was made. So everything I've talked about here so far has focused on squeezing as much performance and reliability out of a single instance as possible and honestly as long as you have room to grow within those confines make it count. Before choosing to take on more administrative responsibilities try to ensure that you're not needlessly abusing or wasting resources by ensuring that that single instance is as healthy as possible and only then can you consider throwing more hardware at those problems. Evidently lots of the talks you may have seen here today have reference companies that are faced with scaling issues or challenges that need to be addressed and so over the years MySQL has had to learn how to play well with others and there's a couple of ways that you can start to consider that. So MySQL replication has been around for some 12 or 13 years now. It provides a very straightforward not entirely synchronous way of replicating data across multiple instances but on the whole it allows application architects to direct reads across multiple targets and the ideas of course that we can share the load amongst those. In recent years I'd say MySQL's built-in replication features have really hardened to a point where they can cover most scenarios where read scalability is required but of course for people wanting to introduce an additional layer of high availability for the right targets as well as well as guaranteeing synchronicity between cluster members there are now solutions like Galera and NODB cluster that have moved in to cover those bases so those are some of the options for you to consider. Now the challenge with offering synchronous writes across a cluster of multiple machines is that that means that those writes can only be accepted as quickly as the slowest link in that cluster so it means that scaling write workloads beyond the confines of an individual machine is still more or less out of reach with these systems so that's when something like sharding comes into play which is a way of stretching what is intended to be a single database schema across multiple machines. This allows for an intelligent distribution of writes as well as reads and it is of course where if a test in my employer planet scale come into play. Sharding can be considered for a variety of other purposes as well but using it to escape a limitation of your write performance is something that happens, it's used for quite often so the last links that I have on this slide are included particularly because it pertains to solving certain problems at scale especially when it comes to dealing with large volumes of traffic or when you're trying to apply changes to fleets of my SQL servers intelligently. So altering table structures is an action that's not quite as trivial when the act of doing so will lock the table up for others to write to and so that change when that change also needs to be propagated across any number of replicas that becomes a very complex situation so at this stage if this is the kind of operation you're considering you're likely not considered a five minute DBA anymore but definitely make sure you familiarize yourself with tools like PT online schema change as well as GitHub's Ghost as those are going to help you address those operations with the least amount of impact possible. Lastly we've heard about it today already in Shlomi's talk but I absolutely have to include it here as well. When you're managing my SQL topologies whether it's you know two or twenty or two hundred servers whether you're using Vitesse or any other combination of technologies orchestrator is really critical at helping you manage and understand your fleet so definitely give that a look when you're considering any configuration of my SQL servers and coordinating across those. So there we go I think that's more enough information to get you started on your way maybe you only ever need to reference the first slides in this deck maybe you've grown into needing to consider ways of scaling out already as well but wherever you are on your journey don't forget to bring your towel remember that there's a whole community here to support you as well don't hesitate to reach out directly if there's anything you'd like to discuss in more detail and again consider joining our Slack community we'd be really really happy to have you on board so thank you very much.
 Hi everybody, thanks for joining me.  It's such a pleasure to see that Faustem is alive and well during these strange times  and that we're able to keep bringing our little community together.  My last round as a speaker in this dev room might have been 10 years ago, but Faustem  has always felt a little bit like coming home and not just because of the lingering smell  of French fries in the hallways, which are doing this dearly.  My talk is scheduled to be the last of the day and has a long time attending myself.  I know that our dev room is visited by the sharpest talents in the MySQL world and I'm  really proud to get to work with some of these people regularly.  After so many years, I'm happy to be continuously impressed by all the new developments, but  being at the cutting edge of this space and listening to talks that focus on solving  problems at the highest levels all day, that can also create an impression that using MySQL  and becoming a part of this community is quite intimidating.  So here I am to lower the standards.  Just kidding.  Here I am to extend a hand to all of those who are here to learn more about MySQL by  necessity, because it's hard not to nowadays, but also because thanks to its long life and  history can be really hard to know where it starts when all you really want is to make  sure that things aren't actively on fire.  So my name is Liz Mendeich and despite having spoken in this dev room before and having  considered myself a part of this community for the past 10 or so years, I don't actually  use or think about MySQL that often.  In my role as a solution architect currently, I'm more often called to sound knowledgeable  about various parts of the stack rather than actually having an environment where I can  build real world experience.  So I find myself constantly needing to review even the most basic things and making sure  that what I'm putting together or demoing hasn't diverged too far from the current reality  of things.  I'm what you could call a perpetual beginner at MySQL and I believe given how widely MySQL  is implemented, that probably aligns pretty closely with a lot of the engineers to whom  it's just one more part of a very diverse and a rapidly evolving stack, but still one  they want to learn about.  So I work for PlanetScale.  I fulfill a technical presales role there and I love it as a company, PlanetScale has  got a very strong vision of what the database of the future looks like and it's very deeply  invested into the MySQL ecosystem as well as Fites, which Lomi also covered during his  talk.  I wish I'd accumulated anywhere near the chopsy and the others in this dev room have in these  past 12 years of being involved with MySQL, but the reality is my knowledge of various  parts of the stack is kind of like a sandcastle and in my role I've had to learn how to be  effective at starting from scratch multiple times and finding the right tool belt to get  me 80% of the way there.  So that's what I'm hoping to share with you here today.  So you're a developer, of course you know of MySQL.  You can write queries sort of or at least you know your ORM does and for the most part  it just feels like a bit of a dinosaur that's more or less a given wherever you encounter  it.  No wonder.  The software is so old.  Clearly there ought to be better solutions for things, but we're all just kind of used  to it, right?  So no matter the actual situation, most people don't grow up dreaming of becoming a DBA specifically  and more and more the responsibility over this very critical part of the stack has become  shared with that of many other components and so we have just as many stack overflow  DBAs as we do developers.  First of all I am one too.  So hello friends.  As you can imagine floating up and down the stack every day and only having a couple of  moments to really sit down and learn things.  I'm a very good Googler.  In fact to write this talk I looked up Top MySQL DBA tools in 2021 just to make sure  I didn't have any glaringly obvious emissions.  Of course everyone's kind of got their own favorites but my main goal for this talk was  to collect some of the tools and the areas of knowledge that any five minute DBA should  be thinking of first when stepping into care for what's often their company's most critical  set of data.  There's so much movement and history to be found on the internet that it can be hard  to identify what is proven and what is stood the test of time versus what is a brand new  bleeding edge development that you probably shouldn't be unleashing right away.  So without further ado here's what I'll be running down.  Keep in mind any of the areas have multiple books worth of material to dive into.  Some of them are written by the people in this very dev room as well as having seen  25 years of ongoing development.  So I'll mainly be pointing you to the first baby steps on most of this but I hope it can  give you a bit of a guide as to the main areas to have a plan around when dealing with MySQL  and your development stack.  Although we like getting cozy and from a community standpoint we certainly maintain very strong  ties.  It's very important to be aware of the heavily diversified landscape around MySQL nowadays.  It's especially pronounced for us at FOSTEM this year as MariaDB is graduated into having  their own dev room track.  The list on this slide is far from comprehensive as cloud specific implementations of MySQL  are kind of popping up left and right, not unlike PlanetScaleDB.  While all of them still speak MySQL when finding solutions to specific problems that you're  experiencing or understanding which tools may or may not work it's a good sense to know  it's good to get a sense of what flavor exactly you're dealing with as well as its version.  So a quick way to spot your service version is by logging in using the CLI client and  scanning the first couple of lines.  You can see that on the slide.  So looking at my local example you can see I'm running version 8.0.22 which more or less  already rules out MariaDB since they opted to break away from Oracle's version in past  5.5.  Taking a closer look at homebrews packaging reveals that they're pulling them in from  MySQL.com which means this is a normal community addition and that's good information to have.  Regarding versioning in general for MySQL although I'm sure there's a reason that MySQL  was stuck in a different dimension for about 13 years in 5.somethingland but coming into  it as a new user it may not be all that obvious that 5.7 and 8 are actually only one major  upgrade apart.  So MySQL 8 was officially released two years ago but it's pretty common and not overly  worrisome to still be using 5.7 at this point.  If you do happen to be on 5.6 still I believe you may, I believe you have exactly until the  end of the month to make your way up as official support for that is going to be ending very  soon.  Now cloud versions generally relate back to Oracle's, Vercona Server tracks those as  well.  Most of what I talk about here applies to all flavors but knowing which one you're running  specifically is going to be quite helpful if you're looking to get help addressing more  complicated challenges.  One thing that's good to bear in mind is that the jump from 5.7 to 8 introduced quite a  few changes.  In some cases different ways to handle certain basic operations.  So oftentimes you'll see that reflected compatibility of tools with either version tends to explicitly  be called out in the documentation.  So here that might be the case.  Now in all my years of dipping in and out of MySQL and often needing to come across as  knowing exactly what I'm doing, which are the tools that I've always come back to.  So first off I don't really bother with GUI tools unless I'm planning on moving in long  term so I can't really help you there.  There's a few nice administrative ones available.  There's SQL Pro or Adminer but I generally don't even really bother because the default  MySQL CLI has evolved so much over the years that I hardly even need to look beyond that.  So I love it because it runs basically everywhere.  It comes with a boatload of documentation built in and honestly it's all I need 99%  of the time.  So having spent a bit of time over the years getting comfortable with it has really paid  off in efficiency.  So if you haven't heard of MySQL Shell, which is the CLI tools up and coming replacement,  you should check out the linked presentation.  It's a presentation by Fred and that'll give you a sense of what's coming there.  I think it's quite exciting.  So second point here is the Prokona toolkit, which I'm assuming will have been referenced  quite a few times in this step room already today.  This is probably still Prokona's most widely used and loved set of software and it's the  end result of many years of consulting in MySQL implementations, all possible sizes and  shapes and they built some tools that really help create visibility and understanding at  all of those levels.  The list of what's available can be quite overwhelming.  I'm just going to call out a couple of gems in particular in the next slide.  But for now, the last piece of software I would recommend is someone who's often confronted  with a list of potentially questionable queries to pour over and only having topical theoretical  SQL understanding myself.  It's called SQL check.  This is a tool that has attempted to take the work of our community's very own Bill  Carwin and his brilliant book called SQL Anti-Patterns and it applies that analysis and those recommendations  against a query or a list of queries that you provide.  For me, this has been a great help in identifying problematic structures and sometimes I suppose  I really like that it is a more hands-on way of learning about how that theory applies in  realistic environments.  So to come back to the Prokona toolkit, in the wild you'll be able to identify these  tools by the PT prefix that they all use and their applicability is very diverse.  I wanted to call out a few of the most commonly used ones as they're ideal for diving into  your environment and solving problems very quickly.  Archiver allows you to safely and gradually move data around between tables into an external  file or simply purging those rows altogether.  The reason you might want to use this is that on very large tables, running delete statements  could lock up rights to those tables and so those end up being a blocking operation.  So what archiver does is it takes your instructions and it diligently works its way down the entire  table.  I think in the documentation it's described as nibbling down the table by breaking that  archiving job into smaller non-blocking steps.  Next tool here is called duplicate key checker.  It's one that helps you understand which keys in your schema may be duplicates or redundants  and those generally end up taking up unnecessary disk space.  So this is a great little spring cleaner.  If you're dealing with a schema that's seen many years of gradual growth and lots of haphazard  attempts at optimizing performance by adding various indexes.  Now query digest might be the single most helpful tool in addressing bad performance  in MySQL.  It's used most efficiently by allowing it to analyze a chunk of representative traffic  which you can capture by turning on the slow query log or you can query the built-in performance  schema in MySQL to get a list.  But depending on the amount of data available, query digest can spit out a normalized list  of those queries that your server spends most of its time on.  Those could mean being, you know, those could either be objectively slow or badly written  queries but they could also mean very fast ones that are just executed many, many times.  This overview that query digest gives you shows you exactly which queries to target  for optimization.  After all, even if a query takes just five milliseconds to complete, if that particular  pattern takes up 80% of your workload and you can bring it down to three milliseconds  somehow that will allow you to stretch your resources much further.  So next in this list is stock which does more or less exactly what you would expect it to.  It's built for those situations where there's sudden unexpected shifts or spikes that are  disrupting your environment but you can't quite figure out where to look first.  And once you've logged in, the problem's gone away and nothing seems immediately wrong.  So stock is a tool that can be set up to wait for the problem to start occurring and then  it proceeds to collect an absolutely ridiculous amount of information from every bit of tooling  imaginable.  So that spike is captured and monitored to its fullest extent and you can calmly sift  through all of the data afterwards.  You don't have to worry about it finishing before you're able to identify what's going  on.  Now variable advisor is a tool that I think we all wish existed a long time before it  actually did and at this stage it might not be as useful anymore but it's still a nice  little tool to run against your environment especially if it's a little bit older.  Because initially my SQL and you know DB's various startup variables used to be the first  stock for really glaring performance issues and quite often making tweaks in that conflict  could squeeze out an incredible amount of extra performance even in a single instance.  So nowadays those base variables tend to be a lot more intelligently set so there's not  really that much magic that we can do here anymore but still a useful review that's always  very helpful in avoiding the most obvious performance issues.  So I'm going to skip over the others in this list right now as they're going to come up  again later but whether you're a five minute DBA or a 15 year one if you're not using  at least one of these tools at various points in your career you're definitely missing out.  So switching over to privileges.  Considering that relational databases tend to store a company's most business critical  data there security in the way that access is provided is often overlooked and for my  SQL specifically it can also be misunderstood so because my SQL displays an internal table  called my SQL user it's important to understand that permissions or privileges are granted  to both a user name and their location meaning the host that they're signing in from.  So a user might be allowed to read from a remote location and only be able to write  when signing in from local host.  If you use a client from a dynamic IP address you might not necessarily be allowed to log  in again when that IP address changes etc etc so at times this can be both frustrating  and confusing and as a result grants are created haphazardly over the years sometimes and rarely  if ever reviewed.  So similarly by default when my SQL is installed it's not overly concerned with locking down  root access assuming that you will be setting that up yourself but even the most seasoned  DBAs are known to forget that step from time to time.  So when going over the environment and trying to make sure that it's in good shape always  make sure that you review which grants have been allotted and also consider running through  this executable called my SQL secure installation which is included by default and that is designed  to help you close the most glaring security holes on a fresh installation.  Now we covered this a little bit when going over the tool belt earlier but here's another  look at your basic monitoring and troubleshooting tool set.  So my SQL can be very greedy when it comes to resources so when performance slows down  it's always important to look at your operating system level metrics just to get an understanding  of the nature of those interactions.  Are you maxing out CPU?  Are you IO bound on your disks?  Or have we outgrown the available working memory?  We might just be doing all of those but it's still good to take a look at those symptoms  before trying to make a diagnosis of what's actually happening.  So Prokona Toolkit again provides a couple more tools to help you relate your operating  system level metrics to database performance mostly by offering them in more readable formats.  So just be sure to have those ready and yeah when something happens you can take a closer  look there.  Another piece that might be interesting to cover here so when you know already that you're  being plagued by certain slow queries the CLI Tools Explain function allows you to  gather information about how the optimizer interprets it and what it expects to do to  fulfill that query.  That could uncover a need for additional indexes or different ways that you can write your  queries to be more selective so very helpful to help you deal with specific troublesome  queries and then lastly if you're not already monitoring most of this permanently, tracking  it over time seems unlikely but you should.  So there's another tool by Prokona that's called PMM and that's probably your quickest  fix as it's absolutely packed with features to help you get a deeper understanding of  your database environment and how well it's doing so definitely check that out.  Now I would like to believe that anyone building a serious database environment today has given  at least some thought to addressing backups but that doesn't necessarily mean that you  know what that looks like and I wanted to cover a few of the basic approaches that we  have available and those tend to fall into two main categories.  A logical backup should be considered essentially a text-based representation of your database  at a specific point in time.  It's usually structured in such a way that when it's fully executed the end result is  that it rebuilds your schema and its data in full wherever you might need that to happen  so this is a method that's often used to allow developers to rebuild the database structure  in their own environment or it allows teams to extract and transform the actual data to  some extent and then they can store that in a different space so what tools like MySQL  Dump and MyDumper do is they effectively log into your databases though they're a normal  user.  They scan all of your tables to translate that data into something executable in this case,  executable queries that allow you to restore that particular state.  Alternatively when we're talking about physical backups we take a more system level approach  to that so we target the actual data files that MySQL stores its tables in as well as  grabbing a copy of its working memory at that particular point in time so I might be generalizing  quite heavily here but on the whole taking a logical backup tends to be a bit of a slow  and steady process especially when it comes to recovering because all of that data needs  to be read and written into a new system again whereas taking physical backups and using  that strategy is generally the path to bringing your environment up and running again a lot  more quickly and they also tend to be executed with less impact on the running system.  Whichever method is being used both of those approaches generally make it difficult to  restore the system to a very specific point in time you're kind of bound to the moment  at which the backup was triggered so in situations where user action is caused irreparable damage  and you'd like to restore to a state just before that action happened well if that's  a situation you find yourself in I hope it wasn't you causing it but even if it was  what you need to be taking a look at is MySQL's binary log tooling and pray that your backup  strategy includes backups for the binary logs as well because tools there will allow you  to replay those logs against a set of data and allow you to recover to a very very specific  point in time if you're able to pinpoint when the mistake was made.  So everything I've talked about here so far has focused on squeezing as much performance  and reliability out of a single instance as possible and honestly as long as you have  room to grow within those confines make it count.  Before choosing to take on more administrative responsibilities try to ensure that you're  not needlessly abusing or wasting resources by ensuring that that single instance is as  healthy as possible and only then can you consider throwing more hardware at those  problems.  Evidently lots of the talks you may have seen here today have reference companies that  are faced with scaling issues or challenges that need to be addressed and so over the  years MySQL has had to learn how to play well with others and there's a couple of ways that  you can start to consider that.  So MySQL replication has been around for some 12 or 13 years now.  It provides a very straightforward not entirely synchronous way of replicating data across  multiple instances but on the whole it allows application architects to direct reads across  multiple targets and the ideas of course that we can share the load amongst those.  In recent years I'd say MySQL's built-in replication features have really hardened to a point where  they can cover most scenarios where read scalability is required but of course for people wanting  to introduce an additional layer of high availability for the right targets as well as well as  guaranteeing synchronicity between cluster members there are now solutions like Galera  and NODB cluster that have moved in to cover those bases so those are some of the options  for you to consider.  Now the challenge with offering synchronous writes across a cluster of multiple machines  is that that means that those writes can only be accepted as quickly as the slowest link  in that cluster so it means that scaling write workloads beyond the confines of an individual  machine is still more or less out of reach with these systems so that's when something  like sharding comes into play which is a way of stretching what is intended to be a single  database schema across multiple machines.  This allows for an intelligent distribution of writes as well as reads and it is of course  where if a test in my employer planet scale come into play.  Sharding can be considered for a variety of other purposes as well but using it to escape  a limitation of your write performance is something that happens, it's used for quite  often so the last links that I have on this slide are included particularly because it  pertains to solving certain problems at scale especially when it comes to dealing with large  volumes of traffic or when you're trying to apply changes to fleets of my SQL servers  intelligently.  So altering table structures is an action that's not quite as trivial when the act of  doing so will lock the table up for others to write to and so that change when that change  also needs to be propagated across any number of replicas that becomes a very complex situation  so at this stage if this is the kind of operation you're considering you're likely not considered  a five minute DBA anymore but definitely make sure you familiarize yourself with tools like  PT online schema change as well as GitHub's Ghost as those are going to help you address  those operations with the least amount of impact possible.  Lastly we've heard about it today already in Shlomi's talk but I absolutely have to include  it here as well.  When you're managing my SQL topologies whether it's you know two or twenty or two hundred  servers whether you're using Vitesse or any other combination of technologies orchestrator  is really critical at helping you manage and understand your fleet so definitely give that  a look when you're considering any configuration of my SQL servers and coordinating across  those.  So there we go I think that's more enough information to get you started on your way  maybe you only ever need to reference the first slides in this deck maybe you've grown  into needing to consider ways of scaling out already as well but wherever you are on your  journey don't forget to bring your towel remember that there's a whole community here to support  you as well don't hesitate to reach out directly if there's anything you'd like to discuss  in more detail and again consider joining our Slack community we'd be really really  happy to have you on board so thank you very much.
hitchhikers_guide_mysql_0_179.png hitchhikers_guide_mysql_180_239.png hitchhikers_guide_mysql_240_287.png hitchhikers_guide_mysql_288_563.png hitchhikers_guide_mysql_564_611.png hitchhikers_guide_mysql_612_1043.png hitchhikers_guide_mysql_1044_1991.png hitchhikers_guide_mysql_1992_2039.png hitchhikers_guide_mysql_2040_2111.png hitchhikers_guide_mysql_2112_2471.png hitchhikers_guide_mysql_2472_4511.png hitchhikers_guide_mysql_4512_6779.png hitchhikers_guide_mysql_6780_7499.png hitchhikers_guide_mysql_7500_11399.png hitchhikers_guide_mysql_11400_14867.png hitchhikers_guide_mysql_14868_21071.png hitchhikers_guide_mysql_21072_23447.png hitchhikers_guide_mysql_23448_26159.png hitchhikers_guide_mysql_26160_26243.png hitchhikers_guide_mysql_26244_30119.png hitchhikers_guide_mysql_30120_30515.png hitchhikers_guide_mysql_30516_36779.png hitchhikers_guide_mysql_36780_36875.png hitchhikers_guide_mysql_36876_36923.png hitchhikers_guide_mysql_36924_37019.png hitchhikers_guide_mysql_37020_37211.png hitchhikers_guide_mysql_37212_37271.png hitchhikers_guide_mysql_37272_37319.png hitchhikers_guide_mysql_37320_37403.png hitchhikers_guide_mysql_37404_37463.png hitchhikers_guide_mysql_37464_37511.png hitchhikers_guide_mysql_37512_37571.png hitchhikers_guide_mysql_37572_37799.png hitchhikers_guide_mysql_37800_38003.png hitchhikers_guide_mysql_38004_38129.png