(upbeat music)
Hello and welcome.
Please enjoy this short readiness video,
on Azure database for MySQL.
Good morning Jan, how are you doing?
I'm doing very well Sanjay,
how are you doing?
Great, great to have you here with us.
My pleasure.
So what do you do at Microsoft?
I'm a product manager,
in the open source database's team
and I run the PM team for the MariaDB and MySQL services.
I see, so let's get started.
What are you going to talk about?
Yeah,
so I was thinking I would share some thoughts
on how customers can approach how they size
their database service.
For their workloads appropriately,
to essentially as you already introduced,
get the most performance out of the database service
and then I had a second piece
I wanted to talk a little bit on.
Like when you have the database server ready to go,
right Yeah.
Then it's still important how do you connect to it,
right because there's also a bunch of stuff
that you need to look at.
And I wanna share some thoughts on both of those
and hopefully get everybody set up for success.
Let's dive in.
Of course the first question always are like,
how do customers best figure out,
you know what's the right size?
Yeah so it really comes down to,
you have to know your workloads right.
And there's no way around it,
because every workload is different.
And there are a couple of questions
you really need to ask yourself,
about your workloads.
So one is,
"hey is it I/O intensive?"
and the second one,
"is it compute intensive?"
The I/O intensive piece we need typically is,
you need to make sure your storage solution is beefy enough.
Right you have enough IOPS.
Size is typically somewhat secondary,
right but the performance that you get out of the size
is the important piece.
Right so how many IOPS do you have permission to?
And then,
depending on the mix of your workload,
is it very write heavy versus very read heavy.
You may make different choices for the compute actually.
So if you have a very write intensive workload,
then having beefy storage is most important.
If you have a very read heavy workload,
you can also consider,
getting for example, a memory optimized offering.
So you have more data in memory
and actually need less I/O.
So there's some trade offs in this one.
I see.
It really depends again on what your workload is.
The second piece on the compute side of things,
one is you do need compute to drive I/O for example.
Right so it's not only do you do a lot of calculation
in memory or do you have a lot of math running,
aggregation stuff like this.
But it's also about,
hey how many resources do you need to drive the network,
to drive the I/O.
Like so all of these come together.
I see.
And then once you have a starting point
ultimately you need to figure out for your workload,
like what do you really want to achieve.
All right and this is where,
you know, know your workload,
know what you want to get out of it is super important.
Because, hey you know, you may run a query,
it takes ten minutes and that's okay for you, right.
Where someone else runs a query
and it should come back in a few milliseconds.
CO queries for example.
Exactly yeah.
So it's very different right,
but you need to know what your workload is, right
and then you make the trade offs for your workload.
And then,
the last piece,
per Richard I believe earlier in one of the earlier sessions
touched on it a little bit.
Given our quick scaling capabilities,
you can write size your workload
even when you're already in production.
So a couple good examples of these ones are,
like if you have a,
you know your workload is typically busy during work hours.
So let's say a retail example.
Yeah, well oh yeah.
Like retail obviously in the holiday season
is a good example right.
So if you know you have a big event coming up.
Like we just had Thanksgiving here,
Black Friday,
Cyber Monday obviously super popular in the United States
and I hear it's making its splash around the world also.
Yeah if you know you have this event coming up,
you scale up for this event.
You make sure you have the resources available,
to handle this peak workload.
Right and then later on you can go back down,
when you know hey you go back to,
you know business as usual right.
But then keep in mind Christmas is coming so.
Yeah. Yes exactly.
Okay that's exciting of course.
So you have a live demo for us.
Yeah let's have a look at,
a little bit of this one.
So what you can see here on the screen Sanjay is,
on the left hand side there's a workload running
and so all these green little,
Zeros. Fields
and the zeros are black.
But the green pieces around it,
those are queries running against a MySQL server.
That I've provisioned
and all these queries do very very big updates,
so think of multi megabyte updates against a single table.
And you can see,
the average milliseconds that this takes,
is around 30,000 at the moment
and we're getting about,
you know 0.5 transactions per second
on this workload against our server.
So now if we switch here to the browser.
You will see that this is a metric that I'm showing for I/O%
and you're seeing that we're running at like about 80%
and let's hit refresh also to get the latest.
You see it's very stable.
But essentially we're maxing out on I/O here.
So what I'm gonna do now,
is I'm gonna go to the,
pricing tier plate,
where we can scale these things dynamically
and what I wanna do is I'm just gonna
move the storage slide up
and give it more power.
And click okay and what you will observe.
So you get more storage, not recalls.
Exactly.
Yeah I just do storage
because we give IOPS per gigabyte provisions.
So we give you three IOPS per gigabyte right
and if you move up the storage you can see,
this particular region where I'm at right now,
we don't get the 16 terabyte support,
but it goes up to,
four terabyte and about 6,000 IOPS,
so I just took a number above that.
And what you will see,
is more or less instantly without any disruptions
the workload will get faster.
So let's have a look.
I'll click okay up here,
down here. Yes.
And go back to the workload
and you will see very quickly
that the transactions per second number will go up
and then the average latency of the workload will go down.
So you can already see the increase,
right zero disruption to the workload.
And this will go up to about,
six or so transactions per second
and again I mean there's no magic,
it's a huge update,
right so it will take some time
but the latency will go down to,
something in the order of four seconds.
So you can see it's a gigantic difference
by just making sure you have the right amount of storage.
Well that's quite a difference.
So how do customers do this?
Yeah Sanjay, I mean as I already said,
it's all about knowing your workload
and you have to test it.
And it's super important that you test it with the workload
that you,
basically a workload that represents
your actual production workload.
Ideally you have a test environment
where you simulate users against your environment,
that then generates the workload against the database.
Because then one you get a
very true representation of your workload,
against the database itself right.
But you also get actually an end to end testing
of your entire application.
And in the second set,
when we're gonna talk about latency
this will become important.
And then yeah what you do once you get this,
like you do the initial sizing,
you get the workload running
and then you can look at some of the metrics that we expose
and maybe let me just switch back.
Yes.
Real quick to the,
to the demo here.
And look at some of those metrics.
So for example here,
a girl who showed you this,
the I/O percentage right,
so this is just the different view on the same server.
For CPU and you can see actually example CPU
was also fairly heavily used.
Even though it's just updates.
The reason is because we generate all this I/O.
And so probably it would be a good idea for me
to scale the computer up for this workload as well
to get even more power out of the,
out of the database server.
The second cool thing,
is we have,
we brought the intelligent performance feature set,
that we have for SQL server,
we brought this to MySQL as well.
So what we can do,
is you can for your server,
go in and turn on what is called the query store.
And the query store lets you capture,
telemetry of your queries over time.
And it stores it over time so that it can look
over time what was happening to your server.
So you just switch on a couple of parameters here.
That are highlighted
and then you can go to the Query Performance Insights tab
in the portal
and have a look at it.
And so what you see here,
is essentially the queries that have been running,
I ran it since last night,
against this server
and you can see,
you know the 40 seconds that we saw on the test as well
and you can see it over time.
And you can see you know the button here,
which queries were running
and the cool thing is you can also look at the,
Wait Statistics for this one to figure out,
you know what I'm actually bottle necked on,
on those queries.
These do look like perfect counting queries.
Oh yeah!
It's the exact same thing,
all database engines have this
and yeah we don't do anything on on emgaic here right,
so those are the built in MySQL ones.
We just expose them here.
You can look at them
and you can see,
yeah we're waiting for I/O right,
for fetching the I/O there's no surprise,
in this.
Obviously this also helps you if you want to troubleshoot
individual queries,
stuff like this right.
So it's not only for the initial sizing
but also tuning a workload.
I see,
so of course these are very cool insights.
Now what else can customers do to get best performance.
Yeah so the second piece is really,
how do you connect to your database right?
Because essentially,
like any network adds latency right
and the second piece that
a lot of people don't actually think about is,
when you open a new connection.
That's a very expensive operation
because there's authentication involved,
there's you know a session needs to be spun up
on the database server right so.
Those two together,
can make a big difference in your end to end performance.
Right and this is what I was saying earlier,
ideally you have a test set out way ahead.
A client that generates workload against
your middle tier or your front end depending
and then look at the end to end experience.
So now,
the most obvious thing to do is,
you know pack them into the same region right.
This is a little bit like,
"oh yeah of course."
But you know,
like really do it.
Like make sure they're in the same region
because that obviously makes a gigantic difference.
The second piece is,
for Azure VMs,
we have what is called accelerated networking
on those Azure VMs.
And so that's more like a client side optimization.
But ultimately what it does,
it bypasses,
some of the VM overhead
that you have for the network in the network stack
and makes a big difference in the end to end,
latency when you connect to a database server.
Then you should definitely try to avoid many network hops.
Right so like you may put a cache in the middle
but don't do you know multiple levels of indirection
because every single one adds latency.
And then lastly,
use connection pooling right.
Because like again I said,
like establishing your connection,
is very expensive right so.
Ideally you don't want to do this,
just establish the connection
and let the system reuse it right?
So that's really,
that's a very important one
and on the MySQL side,
we know that not every
language environment has connection pooling,
there's some tools for this one.
One of them is ProxySQL.
That you can use to do this for you,
can co-locate it with the application just connect to this,
ProxySQL and it does the connection pooling for you.
It's very important.
So I have a frequently asked question for you,
like you know you see customers,
should avoid hops
and of course we always think that way too.
But isn't the gateway mentioned earlier
essentially an additional hop?
It is.
You're very right there.
So the gateway that we use for high availability
is a hop.
The good news is,
we're working on bypassing this one.
And so basically what we're doing is we're having,
we're working on client drivers that allow you to
bypass the gateway with every subsequent connection
after you establish the first connection.
So basically what it does is,
the client connects to the gateway,
gets the information from the gateway
where your database server is
currently hosted in the back end.
And then from there on,
every new connection goes directly to the database engine
and you cut this hop out.
And so we're gonna have this first for PHP
and then for Java.
So for PHP it's an add-on to the driver
that you can install with PECL
and for Java,
we have this in the MariaDB,
connector for Java.
So that obviously works with MySQL as well
because it's the same protocol.
Just get that one once we have an upstream to the community
and then you're good to go.
And you don't need to do anything
it's completely transparent for the application.
Maybe let me hop in
and show you the difference.
I have it set up here
and what you can see,
it's similar to the workload before.
Right now I'm just running one connection,
because ultimately you know I wanna do a network test
and the connection just does a select one.
So it really only tests the network
and you can see on the top one,
this is the one that goes through our gateway
and it takes about 1.8 milliseconds.
So which is overall actually what we expect to see
and it's not bad right?
But you can see at the bottom one,
if you bypass the gateway,
you can cut this time in,
well actually more than half.
So it's 0.6 milliseconds
and the consequence out of this one is,
you get three x the transactions per second.
Now obviously it's a select one,
it doesn't do anything right?
So if you have a query running that takes ten minutes to,
complete.
Well maybe that's not that important for you
but especially chatty applications that do like,
you know point, look, ops neat stuff, very fast.
This is where this will hopefully
give a big benefit to customers.
All right thank you so much Jan.
Any other closing thoughts?
No I got a couple of resources,
on the last slide here.
You know our service homepage.
Absolutely customers can reach out to us
and ask questions.
These emails actually come directly to the product team
and we answer them,
typically within a day or so minus weekends.
But yeah like reach out,
we have a great tech community,
a block,
a page on Azure where you can get
the latest updates, latest tips and tricks from us.
Thank you so much for all this great information.
Yeah thanks for having me.
All that must work for you as well.
Oh yeah.
Thanks for watching this short Azure Readiness video.