Cloud Native Computing Foundation KubeCon + CloudNativeCon North America 2022, 11 Nov 2022

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Vitess: Introduction And New Features - Deepthi Sigireddi, Matt Lord & Rohit Nayak, PlanetScale

Description

Don’t miss out! Join us at our upcoming event: KubeCon + CloudNativeCon Europe in Amsterdam, The Netherlands from April 17-21, 2023. Learn more at https://kubecon.io The conference features presentations from developers and end users of Kubernetes, Prometheus, Envoy, and all of the other CNCF-hosted projects.

Vitess: Introduction And New Features - Deepthi Sigireddi, Matt Lord & Rohit Nayak, PlanetScale

Speakers: Deepthi Sigireddi, Rohit Nayak, Matt Lord
Vitess is a cloud-native database solution that enables virtually unlimited scaling of MySQL. In this session we’ll first cover a high level overview of Vitess features, the architecture, and what database workloads are a good fit. We’ll then dive deeper into VReplication — the Vitess subsystem that enables seamless migrations, resharding, materialized views, CDC, job queues, and other data workflows. This is a big part of the value Vitess offers in empowering infrastructure teams to manage a fleet of MySQL servers as a single logical database. We’ll conclude with demos of key VReplication workflows to illustrate how they make it easy to perform common data management tasks.

A

Welcome everyone to this breakout session. We are live in Detroit at kubecon n a and welcome also to our virtual audience. Today we will be talking about with us.

A

My name is deepti sigredi I am the tech lead and a maintainer of with us.

B

I'm Rohit Naik I'm, also a middle of fitness.

A

Matt was supposed to join us, but he wasn't able to travel at the last minute. We'll start with an overview of witness is a cncf graduated project. It became a cncf project in January of 2018 at the incubating level and in November of 2019 britus became the eighth project to graduate from cncf.

A

Wittus is a cloud native database. What does it mean to be Cloud native? It means that, for a very long time now, bitus has been able to run in kubernetes uh Witnesses massively scalable. It was originally created at YouTube to create to solve YouTube's scaling problems with mySQL and uh witness is also highly available and the way witters achieves scalability partly is through sharing, and that will be a focus of our talk today.

A

I already mentioned with mySQL, but I just want to reiterate that with us is built around MySQL and it's compatible with both 5.7 and 8.0.

A

Witness works with database Frameworks orms any MySQL.

C

A

That you already have Legacy code, that's using MySQL, pretty much anything that works with mySQL will work with with us.

A

What we test does is that it gives a logical database view to clients behind the scenes. There may be many many physical databases, there are people who are running thousands of MySQL instances behind wittus and wittest can provide this logical view across all of those. It takes care of doing the query routing to the appropriate backend databases.

A

Wittest supports both grpc and MySQL clients, and it gives a single connection view to clients.

A

This is the witness architecture. uh Queries come in to with us and first hit the vtgate component, which is a proxy layer and what vtgate will do is based on the query. It will figure out where to Route the queries and behind the scenes, especially if uh you're running with us. In a sharded environment, there are multiple shards and each Shard will consist of a primary and one or more replicas people run anywhere from two to ten.

A

In the most extreme case, 85 replicas, the primary and replicas the primary and replica instances of MySQL form MySQL replication group and each of those MySQL instances is managed by a witness component called BT tablet.

A

So a BT tablet is the one which receives queries from BT gate and then sends them down to mySQL, and by doing this, one of the things we are able to do is that connections to bitters are much lighter weight than MySQL connections.

A

So you can have hundreds of thousands of connections to VT Gates which, in the backend, translate to a much smaller number of MySQL connections through connection pooling. So we do connection pooling at the VT tablet level. So that the same MySQL connections can be shared across clients, so this is the query serving path. What happens when a query comes to witness?

A

There is also the control plane, which includes a topology server which can be at CD or console or zookeeper, and there is a control, Daemon bitter control, Daemon, which accepts administrative commands through a command line interface and sends them to the appropriate components. There's VT Arc, which we spoke about yesterday. We did a talk on video arc yesterday. Vt Arc is the automatic failure, detection and Recovery mechanism in Vitus.

A

So if the primary MySQL for any given Shard, a particular Shard or key space is down, VTR is the one that detects and fails over to a replica with minimal downtime uh VT admin is our new web UI API component and there is a web UI as well.

A

So that's a an overview of the architecture of with us.

A

Witness is in production at many places. This is just a sample subset of all the companies that are running and with running with us in production. Many of them run on kubernetes. Some of them still run on VMS and basically non-kubernetes environments.

A

uh Some of the key adopters of witness are slack, which is 100 on with us GitHub, which uh has started their migration to with us, but are still in the process of completing that migration. So right now they have a mix of witness, managed and non-bitis managed uh databases jd.com a Chinese retailer.

A

They are running, 10, 000, plus databases behind Bitters and a planet scale has a database service that runs with us behind the scenes, and now has tens of thousands of witness clusters running.

A

Here are some uh testimonials from current witness users, uh Etsy and nozzle I'll. Give you a minute to read those.

A

We have a Vibrant Community uh for the calendar years, 2021 2020 to 2021 I gathered some statistics from Dev stats, which is hosted by cncf. We had 21 maintainers over 400 contributors of whom 214 were PR authors, so we are counting non-code contributions as well website. uh Docs issues, comments on issues, reviews all of those things and our contributors came from 63, distinct companies and PR. Authors came from 30 distinct companies now I'll hand off to Rohit to talk about weed application.

B

Hi wittest has always been Shard aware, so all the components are inbuilt understanding of sharding architecture of witness, for example, query serving cluster management, backups Etc.

B

We have had tools to support Shadi right from the beginning, but around four years ago our witness co-creator and co-founder of Planet skills. Suga Suga Martin had a sort of aha moment where he came up with this weird application: algorithm, which works not just for sharding but for several other day, Zero and day, two tasks that are required in at scale systems.

B

So weird application is essentially a framework for creating and managing data pipelines. These are Shard aware pipelines to perform particular actions at an abstract level. What it does is if it matches a criteria that you define and then executes a defined workflow. When we look at some of these workflows, you'll get a better understanding of this diff definition.

B

um There are short-lived workflows, which perform a particular task, and then they are done, such as importing data into vitess sharding, doing online schema changes for when you're running ddls. On large databases, you don't need to bring your database down and there are long-lived workflows which are used for continuous materialization and for a change that capture.

B

We take a slightly deeper look into some of these when you migrate into a test. Let's say you want to just try it out, or you decided that it's you want to move it into move your MySQL cluster into production.

B

We would first, of course, create the witness cluster. In this case, we have created a sharded cluster, but you could start with Uncharted as well. That's the target: the source is the existing database, which is in your data center. It could be RDS, Aurora, Maria, DB or vanilla MySQL.

B

uh You would start an unmanaged tablet, so in bites typical witness clusters, which has also the VT tablet, also manages the database in this case. Of course, the database is external, so it's called an unmanaged tablet and we start the move tables workflow. In this case, all tables are moved from source to the Target.

B

The way the algorithm works is initially, we do a bulk copy, that's bulk, inserts into the target from the source, and once we are close, we start streaming the bin logs, it's a little more complicated than that, but at high level this is the algorithm.

B

This results in a very highly rapid, eventually consistent system and we're able to get uh like Shard hundreds of terabytes uh in days or up to a week now, once the workflow is running, realize that you're still online in all these workflows, you're still online, of course, sourcing uh serving your data from the source. So the target is not yet serving so when your application lag between the source and the target is low, let us say about under a second or so you can start switching traffic over into a test.

B

You have the choice of switching all traffic in the beginning or just reads into uh with tests and still serving the rights from your source and then switching rights later on now, when the rights are switched, we also by default start a reverse replication into the source from the target, so that at any time you can roll back. If you like, for whatever reason.

B

The same move tables workflow can also be used to move some tables into a different database. Here, for example, we are moving the product, related tables into products and Order related tables into orders. This works well. If you have services that have subsets of tables that are fairly independent, you of course have common tables. There's another flow to support that ah note. That wittest will treat all of this as a logical database.

B

So, even though internally there is a separate mySQL database that is hosting some of the tables using vtgates MySQL adapter, you will still address the tables in the same way, so the application doesn't need to change.

B

Now say you just have a very large table right, a transaction table or a message table so and that your current Hardware cannot manage that either due to storage restrictions or read or write, QPS cannot be increased, even though you have thrown Hardware with the problem.

B

So in that case you can do horizontal charting where the table gets distributed across the shards. In our example, we already had a sharded cluster, but here we are showing that we can increase the number of shards here, we're just showing uh two to four, but it could go like 2 to 50, something. If you want right, the Richard workflow will support this.

B

In all cases of sharding, you can go up or go down, I mean so the same workflow works for doing both, for example, in the holiday season. You might just want to Shard higher so that you can serve the traffic better at the end of it. Just go back for cost reasons.

B

Now these, the previous workflows, we discussed, were short-lived workflows once the particular action is done, the sharding action or the import action. The workflow is complete. These uh the next two we are talking about are.

C

B

B

Now you probably have heard of materialize views. We test supports materialized views at scale which sharded uh databases being backed by sharded databases, sharded tables you can create a materialized workflow with your query, to, for example, denormalize tables or to create aggregations for your analytics data. Warehouse reporting, Etc anonymization of data is a very important application that several users are using. When you want to move your data into your Warehouse or into staging or Dev, you might want to redact certain information.

B

Charted systems there are always common tables that are required across shards, like lookup tables country codes, product catalog, language tables, for example. So it's possible to have a single table which has the master information and all that can be uh materialized into each Shard, so that all the queries are local and not cross shot.

B

Queries, that's not a problem, but it's just a matter of performance. So vtk knows where the data is located and it will make the appropriate cross shot. Queries are required, but using something like local copies reduces that requirement. For scattering the queries.

B

The last uh application of weed application that I'll discuss is not a workflow per se, but it's a API that is available at vtgate. It's a grpc API, which will give you a event stream of changes. It can also give you snapshots or changes or both right. You can start saying that you want to stream the entire database and then keep streaming the changes.

B

This is extremely useful and many users are using this for moving the data into a data warehouse, for example, or for real-time notifications, updating your elasticsearch stuff, like that you can stream either the complete database or a subset of tables, or you can filter certain tables for any special use.

B

Now there is a division adapter, which is open, source and available. There are also users who have built a they're, not yet open sourced, but adapters for Stitch and air byte just want to give an example of what's possible. With this.

B

I'll now move to a demo of a few of these workflows.

B

First I'm going to show how vertical sharding is done here. We are starting with a single key space which has these three tables so we're going to move the order, related tables customer and order into a different key space.

B

C

We're going to use.

B

B

Now I created the cluster earlier because it takes a few seconds. So at this point, I'll show you using so this MySQL CLI client is connected to vtgate. As you can see, it is currently running on 5.7 and.

B

Yeah, so essentially it will act as a MySQL CLI.

B

This is the initial uh key space.

B

A

B

Empty right so now.

A

I'm going to.

B

Run this command, so it's a fairly simple command, um which essentially just say which key space you want and which tables you want to transfer a copy over to another case space you give the workflow a name so that you can refer to it later on.

B

In this case, uh we have not only started the workflow, but we have checked that it's complete that the lag is low and we also switched both reads and writes at the same time now, if I go back to the client, I see that the order tables no longer exist here and then move to customer. So.

B

So what I did here is essentially, those tables have moved now to a different database. So if you had a service that was serving the customers uh service separately suddenly, now you have uh your data spread across these different databases.

B

uh The restart demo so from the current key space, which we already moved the previous tables into these are the number of rows that are present.

B

So this will start setting up the shards right. What we are going to do is we're going to set up two shards slash. Zero is basically unshared, so the single database we're going to create these two shards, essentially we're splitting the key space, the space of all IDs into two, the lower and the top I'm, showing it in Gray because they are not yet serving traffic. The traffic is only being sold by slash zero.

B

And then we're going to run the restart command.

B

Setting up um this is a quick name of the VT admin API that this is a ga in the latest version, and you can see that only one Shard, the zero Shard is serving right now the others are coming up. As you can see, only one replica has come up which it will take a little bit of time.

B

So by default we recommend one primary and two replicas and the VTR component, which we had a talk yesterday about, will automatically select one of them as a primary.

B

Yeah this is why I started the previous clusters a bit earlier, so because this VG tablet starts both itself and the MySQL instance as well, which is connected to it. Okay. So now, let's look at VT admin and check yeah, so we have both the other shots created, a primaries have been elected and uh we are going to now run the restart Command right.

B

A

B

Simple command, um of course, we just have very few rows here. This restart can run for hours and days. ah It.

A

B

In case there are network errors, etcetera, it will automatically resume and even if there's, a different primary gets selected because of failovers, all that is handled by the workflows.

B

Yeah, if you look at VT admin now we see that the Uncharted shot, zero is no longer serving and the other two are now serving so BT gate will be connecting to these shards. It knows um where each row is located by the sharding key. So when you define sharding a sharded table, you define a sharding key which could be just the identity of your auto increment or a hash of it.

B

If you want better distribution across the space or a tenant ID or a Geo ID for if you want to localize your data for legal reasons,.

B

So whenever queries are made this, we call it a Windex within bites, so the Windex is looked up by vtgate and it figures out whether it can go to A Single Shard or it has to do a crossword query.

B

So what I've shown you just to recap was that.

B

We did the restart, but we had not yet done the cut over once the cutover is done. That's you do switch traffic, the switch traffic command.

B

The shards on the right start, serving the last thing I'll show, is uh the materialization I'm, showing two things here. One is reduction of data for pii in case this, the credit card, and we want to do normalization I'm, going to move this into a facts table for your data, warehouse and I want, let's say the month and the year right, just examples.

B

B

So what is important here is the query that is being run, so you can look at the query. You can use MySQL functions, um foreign and do computations Etc. You can have a where clause. In this case, we have removed all rows for a particular skew and for a particular price. So just to give a idea of what is possible.

B

We have, for example, One customer doing pii who's running about 600 workflows on different tables when they are moving it into the data, warehouse and they're all running on a single video tablet just to give the idea of scale and it's a fairly commodity Hardware.

B

Here you see that the credit card has been masked uh and we have like the total price computation month and the year.

B

The last thing here, I will show is the aggregation, which is very useful for reporting purposes. You could so the thing is this real, uh it's happening in real time right as changes are happening, the views are updated in real time. The usual lags are extremely small, so you can use it for reporting purposes or for read purposes without having to have a separate uh like a data warehouse system or so so.

C

This is a simple aggregate query.

B

That is going to run here again. What's the use here? Is the SQL query? It's a very simple Group by count star some Etc. So this is what I wanted to demo a quick demo to give you a flavor of how it works, how easy it is to run.

B

um We are now going to look at the new features in vitess, 15 and deeply is going to talk about that.

B

A

A

We did the we did. The 15 Auto uh GA release this week on Wednesday, and uh there are a couple of things which are GA in this release. The first is BTR, which gives us the automatic failure, detection and handling, and it does it in such a way that the desired durability policies are respected.

A

The other thing that went GA in 15 is VT admin. This is the web UI that rohith included in his demo, and this uses a structured, grpc API to the witness control Daemon, and this is a more intuitive easier to use web UI than what we used to have in addition to BTR and VT admin.

A

We also added support for a whole bunch of compression and decompression algorithms during backup and restore previously, there was only gzip that was supported, and anyone who wanted to use something different like zstd or lz4, had to write some custom hooks, that's no longer required.

A

uh There are a couple of features that went GA in 14, which happened in June, but since that is f that happened after our previous update uh I just wanted to include that. So there is a new query, planner and Optimizer, which we are calling Gen 4. The previous one was called V3. uh This query planner is able to support more my SQL features and is able to plan more complex queries.

A

Schema tracking is also GA. What schema tracking does is that, when new columns are added to existing sharded tables previously that metadata had to be provided separately to vtk, it's no longer necessary. Vtk can discover those new columns by talking to VT tablets which are connected to the MySQL.

A

We have a native View support in progress, so it will be available in a future release.

A

Next up, we replication uh one thing that is new in 15 is Shard level migrations it's possible to migrate, a key space from one witness cluster to another one Shard. At a time previously, the migration had to be done for all of the shards. At the same time, we also have a better throttling for weed application workflows in order that the databases that are serving production traffic are not negatively impacted by some of these uh sharding and materialization type of workflows.

A

Still on V replication, there is a tool called rediff which can be used to verify that we replication actually did its job correctly. So, for example, if you're doing a resharding, you want to make sure that the that the sum of the targets is equal to the source or the sum of the sources is equal to the Target. Depending on how the sharding is structured, so V diff V2 is resumable. It will start restart itself if the error is recoverable and it provides progress and ETA on the process.

A

Native online ddl was GA in 14, so wittus has built-in support for zero. Downtime schema changes, and that is also built on the replication.

A

The other thing that is nice about the native online ddl support or online schema change. Support in bites is that these schema changes are revertible.

A

We are also working on incremental backups and point-in-time Recovery in a future release. We already have a point in time. Recovery workflow, but what is coming up will support more use cases.

A

Next up is Q and A, but before we do that here are some resources for anyone who wants to learn more. We have our website, which includes docs and tutorials, there's also a link to the source code, which is just with sio Slash with us on GitHub. We have a slack workspace. There is a link to it from our website as well and we'd love to get feedback on what you thought of the stock, so that QR code will let you leave feedback for us. So now we are ready to take questions.

D

So it's on top of my sequel, but you mentioned you have your own query: planner and Optimizer correct.

A

D

How does that work.

A

We try to push down as much of the SQL computation to uh MySQL as possible, but for complex queries. For example, you may have a complex join for which you have to fetch data from one Shard and then combine it with data from another Shard or you fetch one row from one chart and then you filter across all of the shards, based on the value that you get back from that. So those types of complex queries require in-memory computation and those are the things that vtgate is doing.

A

So when a query comes in vitigate will figure out do I need to just send this to one Shard or do I need to scatter it or do I need to send it to a subset of the shards. Do I need to break it up into multiple round trips, depending on the complexity of the query? So that's the sort of planning and optimization that's happening at the VT gate level.

A

Yes, so I would say that that test Suite may not be comprehensive, but we do have a test Suite, where we compare the results between sending it directly to my SQL versus going through vtgate yeah.

A

So we in fact added support for a lot of functions in I, think 13, but there are still a few MySQL functions that we have not completed support for. uh We have a relfx intern who's working on that right now,.

C

A

C

Carry on on that uh on that query, processing on the on the v test layer, um does it give you also some insight of the quality of your sharding decisions? Well, depending on some history of queries,.

A

A

Tells you what plan was produced by vtgate, whether it was a select, unique meaning it went to one shot, was a scatter, so we do produce that query log. So it's possible to process that outside of it a sweaters doesn't do that as part of The Suite of functionality we produce, but it's possible to do that.

A

A

Have any questions about the different types of sharding keys? We support.

D

Sorry, just expanding on the optimization and uh so I guess it has its own explain. Plan would show the vitessa's query plan. Yes,.

A

D

A

uh We extend the explain, syntax and we have an explain, format equals Json and format equals BT explain. So there are a couple of them. Harshit can answer that yeah.

E

So I'm from the query serving team from Witness, so yes, we do have. Basically we have a explain plan for witness where you can understand that if you use this, if you basically what we do is like you have to give uh like for this table, what is your sharding key and stuff? So you give that initially now you're running your queries, so you want to know what kind of plan is going to get executed by the vtkit. So you can do explain format equal to a test and your query.

E

So it will tell you what is the plan that is going to be generated by VT git, and it will tell you whether it's going to be a select like if you're given us, suppose a select query. So it will tell you whether it will go to a single chart we'll go to multi-sharp, whether it's a select any kind of query. So you'll you'll understand whether it's using uh the right sharding key or not. If it's not, then you can add uh more shading.

E

So basically there's one thing called sharding key and then you can have secondary when indexing on it. So we call Windex, and so you can add more and more Windex according to your query. So suppose you have given your sharding key as suppose as ID, but in your where Clause you are not using ID, you have using some other column, so you can create secondary index on that column, so that then it doesn't go to all the shards, because your data is distributed across multiple charts.

E

So you don't want a query to become expensive, so you'll create a secondary Windex on it, so that now the btkit can plan your query better, and so it goes only to the subset of the charts where your data is actually residing to.

E

uh So yes, so lower level performance is lfnoids only at the MySQL level. So you can only what queries goes down. You can evaluate it, but at the VT gate level, what we give you is actually a streamable API which will tell you that for this query, how much time it took like and uh where, where it has gone to which shot this has gone to and what tables is just and how much time it takes at the MySQL level at the VT gate level.

E

So those informations are available at the VT gate level, not a full performance. Mysql that you like what you see at the MySQL level, but you can query the MySQL levels performance. How.

A

Should let me add to that so uh with witness? You will use both sources to tune the performance of your system, so you have the metrics and the statistics coming out of vtk, but you will also then see how each of those individual queries is performing at the bicycle level.

A

So you will need to do that, so it's possible that the query sweety gate is coming up with are inefficient, in which case the VT gate planning has to be improved and it's possible that btgate is coming up with the right queries, but you don't have the right indexes on the underlying tables, which is also fixable. So it's a com, it becomes a combination.

D

A

uh No not really, but.

E

You can override means, like I told you right like. If you have missing uh indexes at the BT gate level, you can add it and then you will start having those. If, if your, if the planner itself is producing wrong bad plans, then you you have to just create an issue and then someone from the query serving will get a better plan for that query.

A

Okay, so while you can't overwrite the query plan directly, like harshit said, you can add secondary indexes which can uh produce a more efficient query plan.

E

The other thing that we support is, if you know that this query has to go only in this particular shot. You can do chart targeting and stuff like that. So then it doesn't go through the planar phase of vtk. It just directly pushes to my SQL. So what we do with we ticket level is we try to the plans that we generate at vtk level?

E

We try to push as much as to the MySQL so that we have to do less things on the in-memory stuff and let the MySQL handles more and more so like, uh even if, like we have to do aggregation side, we try to so we try to push those like. If you have to do just count stuff, you try to push all the concepts till the MySQL and we then we just combine the counselor again at the VT gate level, so we don't have to get full data.

E

We just need counselor from all the shards and then do it for you. So those kind of things we try to push as much as push down approach on the MySQL.

D

To do the MySQL monitoring tools work on top of the test, also.

A

Yes, yes, they do so uh witness also comes with a sample grafana dashboard that was contributed by one of our contributors that you can use to monitor the cluster.

A

Well, you you will okay, I'll repeat that question the question was: uh does the sample grafana dashboard include metrics at the query level? Yes, it does because in BT gate we do track the amount of time taken in VT gate, but also the amount of time taken at the MySQL level.

A

But we do not export all of the performance schema metrics, so those are probably not available in the sample. Grafana dashboard.

A

Any other questions.

A

All right thank.

D

You everybody for.

A