Running MySQL at Hyper Scale, When You Know Weird Things Will Happen – Percona Database Podcast 66 – Oleksandr Kuzminskyi

Link to listen and subscribe: PodBean

When a bug affects 1 out of 1000 systems, but you are running 10K systems… odds are you are going to have to fix it. Long time MySQL contributor and community member Oleksandr Kuzminskyi, Staff Database Engineer at Pinterest, stops by the HOSS talks FOSS to share his insights into managing MySQL at massive scales at companies like Pinterest, Box, Drop Box, and others! He shares his insights and advice on managing those systems where you know weird problems are going to happen just because of the scale at which you are running. Devops and automation are key, but are they the only things you should consider?

YouTube

Link: https://youtu.be/0XsMIvXgF68

Oleksandr Kuzminskyi

Staff Database Engineer at Pinterest

Aleks works at Pinterest on automating its MySQL fleet operation. Before, he worked for Box, Dropbox, and Percona. He believes in Infrastructure as a Code principle and CI/CD with a goal to exterminate toil.

See all talks by Oleksandr Kuzminskyi »

Matt Yonkovit

The HOSS, Percona

Matt is currently working as the Head of Open Source Strategy (HOSS) for Percona, a leader in open source database software and services. He has over 15 years of experience in the open source industry including over 10 years of executive-level experience leading open source teams. Matt’s experience merges the technical and business aspects of the open source database experience with both a passion for hands on development and management and the leadership of building strong teams. During his time he has created or managed business units responsible for service delivery ( consulting, support, and managed services ), customer success, product management, marketing, and operations. He currently leads efforts around Percona’s OSPO, community, and developer relations efforts. He hosts the HOSS talks FOSS podcast, writes regularly, and shares his MySQL and PostgreSQL knowledge as often as possible.

See all talks by Matt Yonkovit »

Transcript

Matt Yonkovit:
Hey, everybody, welcome to another HOSS talks FOSS. I’m the HOSS, Head of Open Source Strategy here at Percona Matt Yonkovit. And today I’m joined by a longtime member of the MySQL community, someone that I used to work with years and years ago, and we’ve kept up over the years at various conferences and things when they were in person. Olex Kuzminskyi. Oleks, how are you doing today?

Oleksandr Kuzminskyi:
I’m doing well. Thank you. I’m really excited, to go to the conference, to see faces, to hang out at the bar. And look forward to all the amazing talks. I just checked out the schedule. The talks are really interesting. And I plan to attend a lot of them.

Matt Yonkovit:
Yeah, I mean, yeah, there’s so much content, it’s gonna be great. And we’re actually, we had so much content, we couldn’t get it all in. So we’re going to plan like, a couple of weeks later, a month later, some virtual days, just so we could get people who couldn’t make it. Because of everything in the world pandemic wars, and everything else going on, not everyone could attend in person. So we wanted to give them the opportunity. So we’re gonna have a little bit of follow-up as well. So there’ll be even more content when all said and done. So, that should be good as well. Now, for those of you who don’t know, Oleks, Oleks is a staff MySQL SRE at Pinterest. And maybe Oleks, maybe give a little background on yourself to our listeners, just so they can get to know you a little bit more. Tell us a little bit about who you are, and your background.

Oleksandr Kuzminskyi:
And all the background. Okay. I was one of the early days Percona employees. Well, not the very first one, but I joined in 2008. And that was a blessing. I’m thankful for such a great opportunity that I had a chance to learn from the base. And I used that opportunity. And in Percona, I did Data Recovery Services. I supported the data recovery toolkit, I was a consultant, and it was a remote DBA. So we did a lot of great things at Percona. And then, I started working for Internet companies. I was in Dropbox, managing my skills over there. Then, and then now I’m Pinterest. So after Percona, I was mostly managing large fleets of MySQL servers, Dropbox and Box, it was on bare metal machines. Pinterest is AWS-based. So it’s a kind of a different environment, but still good old MySQL. It’s been always a lot of MySQL instances, a lot of loads a lot of QPS. And our job, my and my colleagues was to keep it up and running.

Matt Yonkovit:
Yeah, well, it’s interesting because you have a variety of experiences at Percona, we tend to touch a lot of different environments. But post-Percona, you have been in three very large environments that run MySQL at a significant scale. Something that is quite impressive in terms of size-wise now, obviously, size is relative to where you are. But for most people, when we’re talking about 1000s of machines or even hundreds, that’s not something that in the past has been standard. Now we’re seeing more and more people have more machines. But I think that’s a trend that is starting. And so learning and being able to pick up the tips and tricks from someone who’s been there and done that as often. Good. So I want to ask you, what are some of the common problems you see at scale? Now, as you’re supporting multiple databases, you’re supporting multiple environments. What are some of those common things that may be things that developers do that drive you nuts or other things like that?

Oleksandr Kuzminskyi:
Yes, it is different. First of all, there are a lot of instances and physically it’s impossible to do the manual work that you usually do, if you manage a couple, let’s say 10 MySQL databases, when there are 1000s of them, you have to script your work, you have to automate a lot of things. This becomes vitally important because teams are usually small and the number of MySQL databases per head is very high. So that’s one thing. You have to automate a lot of things. The second thing is because there are so many databases, unlikely events, happen more often than usual, right. If there is a bug that manifests itself in very rare circumstances, managing that large fleet, you can be sure that you will hate it.

Matt Yonkovit:
That’s not good. But

Oleksandr Kuzminskyi:
Yeah, that’s, that’s why what can we do? I remember, there was a, there was a bug in InnoDB, checksum when. So the checksum value is four bytes. Okay. And if, if the format was compressed, InnoDB considered the zero value of the checksum as the wrong value, and it crashed then. But we had so much data that zero checksums! Well, first of all, it’s a valid value. And when this value had a valid value happened, instances crashed. And we had a lot of crashes because of that. So this is the moment when I realized how massive our fleet is and how low probability bugs can be certain if you work in such a big environment.

Matt Yonkovit:
Yeah, that’s a really, I guess, I haven’t thought about that. If you have a one in 1000 chance to hit a bug, and you’re running 1000 instances, you will hit you have the 1000 chance. So that’s, that is an interesting and a little bit scary possibility if you will. I’m guessing, from a Pinterest perspective, or even some of your Box, and Dropbox, the workloads are so diverse as well, not all of the same clusters, or the same machines are running the same workload. So that’s going to increase the probability, if you will, of those bugs happening as well because if you have one application that’s always running the same workload, if you don’t hit the bugs, then that’s great. But if you have analytics workload and OLTP workload and auditing workload and this workload, that’s going to increase the probability as well.

Oleksandr Kuzminskyi:
Yep. And that’s why, if you manage, again, a big, big fleet of MySQL databases, you have to be able to quickly identify problems. And that’s, that’s why you have to have some tools to help you. So visibility is really important. And if somebody says, if let’s say developers complain about some problems with the database, you have to be able to quickly identify that. And we, we know, we use some tools, I use PMM a lot to use it to identify problems with queries. And what I like about it is that it provides a historical perspective, this is something that is not available in like a normal slow load. That helps a lot. And again, if you, if you manage a large fleet, you have to be well-equipped to do so.

Matt Yonkovit:
Indeed. And I think that finding that one problem in a large fleet is a challenge that everyone running at scale has. I think it also changes the types of things you think about from a monitoring or alerting perspective, things that you key in on it. You’re going to be looking for certain patterns or changes in patterns that you might not look at if you’re running a single or a few things at once. And I’m curious, are your developers kind of like what you’re supporting, enabling developers to add their own databases add their own systems? So it’s almost like you’re running a database as a service infrastructure for them? Is that how your internals work? Or does each group kind of get like servers, you set up for them directly?

Oleksandr Kuzminskyi:
We, strive to work towards that direction, we do want a self-provisioned database service. It’s not, we’re not there yet. There are things to improve, to introduce. But we are moving, in that direction. And we kind of have to because again, we cannot do all the work and typical requests from developers are, well, first of all, they start service, right, they need the database. So they need the database, and they ask us to provision it, the very first step that we do is try to automate or tool the process. So we can do that quickly. The next stage is to provide an interface for developers. So they can do it themselves. So when the database is up and running, what usually happens is schema changes, right? Because the data size grows, and the simple altar table is not acceptable, because it’s blocking, even if it’s online DDL, there are problems with replication lag. So we have to do it. And again, if we do it manually, we have as much capacity. So we kind of have to introduce services that would allow our clients to do some of the work. And we are, that’s essentially our work to, to enable our customers to, to let them do what they want to achieve. And by the way, this is how DBA role transforms, right? So there are talks about DBA being dead, right? Like, it’s not that job anymore. And that’s true, because in our daily work, most of our time we spend programming, we write code, and this is, this is probably 80% of our time, we only act as DBAs when you need to troubleshoot a query, or you need to understand the bug, that kind of stuff. Otherwise, you write code. And we write code to automate things to provide interfaces for clients and so on.

Matt Yonkovit:
Yeah, I’ve seen that as well. I think I just saw a survey that we were doing. And I think the results were 40% of people’s time, on average, across everybody we surveyed is spent doing development type work which is it’s not as much as you but that’s across every company whether it’s a web company with massive scale or small company. So there’s just more development work happening. And I think automation is required, because users’ demands are changing so much, and they want it fast, they want it immediately. They don’t want to have to wait for you or me to look into what’s causing an issue or to set things up. So everything’s just automated now. And I think that’s going to be critical and important. And I know that’s your talk at Percona Live as well as going to be talking about how you’re deploying proxy SQL at scale, to help with some of that automation, and to help with some of the infrastructures across all of these fleets and servers. And I know Pinterest has 1000s of servers. So that should be a really interesting talk for those who are interested in how to deploy MySQL at scale and some of the tools that are out there that you can make use of.

Oleksandr Kuzminskyi:
Yep, yep. We employ infrastructures, and code principles, we do that to basically make it possible to make provisions. First of all, easy to maintain. We do it to ensure uniform provisioning when, let’s say you need to provision 10, identical instances, and infrastructures code enables you to create identical, all of them. And, again, the law of big numbers kicks in, he kicks in here as well, because let’s say you have a provisioning script or tool. And, of course, like any other piece of software, it has some bugs, and in some unique edge cases, it will, it will work the wrong way. And when it happens, you need to, you need to make sure that those edge cases are caught. And if let’s say one out of 10 instances didn’t come up, come up healthy. There are health checks that disable it. And it’s reprovisioned again. So eventually you have what you requested. And applying this principle, like infrastructures, code on multiple layers of provisioning, starting from provisioning AWS resources like EC2 instances, load balancers, etc. towards operating system services, proxy scale operating system settings, and up to the fine-tuning of proxy SQL instance itself. So all of that is present as code in multiple repos. And we will maintain the code and we maintain automation that takes those pieces of code and makes things happen as we request that

Matt Yonkovit:
Excellent. So I’m curious as you’re looking at the automation as code side, as you’re looking at deploying these, are you deploying things via containers and Kubernetes? Or are you using other infrastructure software, or orchestration software, whether it’s Ansible, Puppet, or Chef?

Oleksandr Kuzminskyi:
On different layers of provisioning, we use different tools. For AWS, we use TerraForm. Okay, and so, a short, short summary of my dog, three layers of provisioning. We start with AWS which is TerraForm. Then when the EC2 instance starts, Puppet provisions OS level, things like configuration, some repos config, some common services, specific services, then proxy SQL itself, and provisioning software for proxy SQL services. So that’s the second provisional layer, I would say it’s driven by Puppet. And the third layer of provisioning is more dynamic. It’s a custom our Python Software that we wrote, It finalizes proxy scale provisioning. And it also updates configuration as user requests, I mean, DBA is requested. So these tools, work together, each of them have have its own benefits and its own scope of competence. So we reorganized our provisioning process to take benefit of each of the tools. We do not use containers. Frankly, working in AWS environment, I don’t see much need for that. Because AWS itself is, is a giant, I mean, EC2 instances, if you think about it, they are not very much different from containers, you can start it, you can dispose of it. So it works well for us.

Matt Yonkovit:
Okay, well, good, good. And I would encourage folks who want to get a bit deeper to attend your talk at Percona live, and get a bit more details. Now, one of the things you might not know about Oleks, and he mentioned it earlier, is Oleks used to spend a lot of time doing data recoveries. Now, if you’re not familiar with what data recovery is, or forensic data recovery, for those who are out there, and you’ve ever dropped a table or maybe deleted some data or done something that you really didn’t want to do with the database and couldn’t recover from it. We used to spend quite a bit of time going back and figuring out how to recover it. And Oleks was the master of data recovery. And I can tell you, there were a lot of stories way back in the day. And I think things have gotten a little better over time, where we had to spend days, weeks, weekends trying to recover people’s data from nothing, basically, or are the bits that were kind of like in the background there. And so that was kind of one of the areas that Oleks really specialized in, he spent a lot of time doing that. It’s a very unique skill set. I don’t see it as often anymore. I mean, do you still hear people having a need for that? Or have we reached a point where things are a bit more resilient and able to overcome those types of issues?

Oleksandr Kuzminskyi:
Yeah, it was interesting to work, I really enjoyed it. First of all, is because you, you learn so much about the database, you’ll learn so much about InnoDB internals, you understand how server is designed, how data is organized, how data is manipulated, what happens when you drop table, what happens when you update table, and that was a great opportunity to learn those insights that you typically do not have a chance to deal with. Okay. And that is great because understanding the basics, helped me, to do consulting work and helped to optimize the queries. I remember, based on this knowledge, I created a talk about indexes, and how they work. And I suggested listeners think about it as you would write MySQL servers, your MySQL server yourself, how would you extract data if you had a secondary index? How would you design that secondary index to be able to do the search quickly? So that was a great experience. Another aspect of it was customer relationship. Because when people came to us, at Percona, they were desperate, there is no backup, business at risk database was down. For a lot of people, that was a real tragedy, that was real drama. And I had to witness it. And when, when we started to examine what they had, like broken disks or empty, empty database server, it was great to see how people had hope and how they were excited when we recovered the data. So that’s also part of the work and that is to some degree, it’s a pleasure to see that excitement in customers when they get the data back when their business is saved. I enjoyed that. I enjoyed that part of the work.

Matt Yonkovit:
Well, and it’s, it’s a good experience because you mentioned the internals, I find that many developers SREs, DBAs, DBRs. I mean, in the current generation, doesn’t quite understand the internals, they might understand how to potentially do some light configuration, or maybe do some Korean tonight and how to add an index. But they don’t know how databases themselves work, and how the internals is processing things. And I think that potentially limits the scalability and the performance and the security of the systems.

Oleksandr Kuzminskyi:
Yeah, it does, it does. What I see is there is, there are advices on the internet, like, if you, if you want your query to be fast, create the index. And, unfortunately, this advice does not explain why. And if you don’t truly understand what happens when you create an index, and how it happens, you are indeed limited, in your ability to unlock the best from the database. And as the opposite, if you understand what you can do, that creates a lot of space for imagination. And that helps with service development as well, because you understand what the database is capable of. Right? And it’s not the black box anymore for you. So you can, and that, of course, impacts design decisions on the service itself. So it is important to know about internals to some degree, even for non DBAs even for database users, or in general for software developers.

Matt Yonkovit:
Yeah, totally true. And so I like to do this at the end of my talks. And so I want to transition just a little bit. And I like to throw some random rapid-fire questions at you. There, I have a list of questions, I randomly pick a few off the list, just to get a little bit more interaction and a little bit more detail about you. And we’ll see how it goes. I’ve been doing this for the last few weeks, and I like it. So I think I’m going to keep doing it. I think you’re like the fifth or sixth person. So here we go. Are you ready? All right, here we go. So what is the most in your opinion, the most underappreciated configuration variable in MySQL? Like what do people forget about what should they know about? What do you think?

Oleksandr Kuzminskyi:
buffer pool size

Matt Yonkovit:
Okay. There is no right or wrong answer. Right? This is just your opinion. Right? So. So, people don’t set the InnoDB buffer pool. Of course, that’s going to be a significant problem. So, there is that now, moving on, what is your favorite tool that you use on a day-to-day basis?

Oleksandr Kuzminskyi:
A favorite tool that they use on a day-to-day basis? Iis popular programming language a tool?

Matt Yonkovit Sure, there is no right or wrong answer.

Oleksandr Kuzminskyi:
Okay, then I will go with Python.

Matt Yonkovit:
Although that takes the next question off my list, which was what is your favorite programming language? But there is that. So you write a lot of Python code. Okay. Yeah, that that’s, yeah. Okay. And so, as you are thinking about high availability, okay. Do you prefer using clusters that are already kind of pre-built or do you prefer using standard replication with some sort of failover mechanism? Like a PXC or an InnoDB cluster versus a primary replica with an orchestrator or proxy SQL or something else doing the failover.

Oleksandr Kuzminskyi:
I prefer normal replication, Legacy replication, asynchronous replication, and replication There are many names for it. And the reason for that is because of its simplicity. It’s really simple. It’s rock-solid and reliable. Yeah, it has its own problems and ironically, in reliability itself, but over many years, we gain a lot of experience with it. And as a result, we have a lot of confidence in it. And over the years, those shortcomings of the standards replication I would say there are workarounds there are remediations, like semi sync replication, and aggression resiliency, that, by the way, Percona designed the Percona design it or was it with Facebook? I don’t remember. But I remember this feature in, in Percona server, and I remember testing it with Vadim. So, long story short, replication is simple, quite reliable, and familiar for everybody. And I find it more convenient to work with,

Matt Yonkovit:
The weirdest problem you’ve ever encountered, like the weirdest MySQL issue that just sticks out? Like, is there one that jumps to the top of your head?

Oleksandr Kuzminskyi:
Yeah, I remember, we had a problem. We had a problem when primary key lookups suddenly became too slow. I mean, from normal expected to milliseconds, one, two milliseconds response time, up to 30 milliseconds

Matt Yonkovit:
Oh, wow. That’s a pretty significant increase.

Oleksandr Kuzminskyi:
Yeah. And it was hard to catch bugs. And we couldn’t understand what’s, what was going on. And eventually, we have to run. So we got a moment when two identical servers were executed. One was a good one was bad. And we ran. We ran gdb. To catch stack trace and hunt down the bug that we found deep and in InnoDB internals. Oh, wow. Okay. That was, that was interesting. That was an interesting experience. And what I liked about it the most is after a week of struggle, we finally found the problem and fixed it. So that’s good.

Matt Yonkovit:
Well, in so that’s one of those things that it’s always there are two things, right. It’s good to find the problem. But it’s also good to know that you’re not going crazy. And that like it’s not like it’ll because you’re like, oh my god, am I at fault after a week? You’re kind of like, Did I do something wrong? But yeah, no, I get that. But, Oleks, I want to thank you for hanging out with me today. Thank you for sharing a little bit about your background, some of the things that you’re doing at Pinterest, and some of the history that we’ve we’ve shared together. I think this has been a great chat. And hope to have you on again sometime.

Oleksandr Kuzminskyi:
Thank you, man. It was a pleasure to see you again. And I hope to see you soon

Matt Yonkovit:
Definitely. And for those who are watching this video, please feel free to subscribe to the channel like it. Give us a thumbs up. Let us know if you want to hear from more people and give us suggestions we love. We’d love to hear from you. And we all hope to see you at Percona Live. Bye, everybody. Bye Bye. ∎