CRAIG LABENZ: In the last episode,
you created a web app in Cloud Run backed by a Cloud SQL
database.
MARTIN OMANDER: Yes.
And it's an app that pulls developers preferences for tabs
or spaces in their code.
Everything works.
But I'm not sure about how scalable it is.
CRAIG LABENZ: No one is yet.
That's why we need to run some load tests.
[MUSIC PLAYING]
My favorite tool for this is a Python library
called Locust.io.
MARTIN OMANDER: Oh, I've heard of that.
Let's see.
Python3 and pip.
OK.
I'm installing it now.
CRAIG LABENZ: Great.
I also sent you another file earlier called locustfile.py.
MARTIN OMANDER: Yep.
I have it right here.
What does this file do exactly?
CRAIG LABENZ: It creates two types
of users, one that views the homepage and one that
cast votes.
And now with the weight attribute,
we've told Locust.io to send five times as many home page
views as votes cast.
Most apps are pretty read heavy.
So I thought that might mimic your users' behavior.
MARTIN OMANDER: 5 to 1, yeah.
That ratio sounds about right.
CRAIG LABENZ: OK, great.
In that case, you can run locust-f locustfile.py
to start the process.
MARTIN OMANDER: All righty.
Starting it.
CRAIG LABENZ: Cool.
And the Locust server is now running.
So if you click on the link it just printed in your terminal,
we'll be ready to start the test.
MARTIN OMANDER: All righty.
Open that one.
Ah, OK.
Number of users.
I'll start with 50.
Spawn rate, what's that?
CRAIG LABENZ: The spawn rate controls
how quickly Locust should reach full throttle.
So if you enter a value of 10, Locust
would reach 50 concurrent users in five seconds.
MARTIN OMANDER: All right.
Cool.
And then for your URL, I will grab that
from the live URL over here and paste that in,
and this is neat.
I'm going to release the swarm.
OK.
So it's sending data to the app if I click here on charts.
Ah, right.
Here I see the traffic ramping up.
Response times, OK.
Bit of a long response time right there in the beginning
but then it's plateauing out.
Pretty good response times.
The subsecond response times.
It looks like it's holding up pretty well, especially
since we deployed this app to Europe.
And we're running this test from California.
But we should take a peek at how things
are running on the server.
Let's see here.
We'll go into Cloud Run.
All right.
So I'm scrolling down here, checking out the container
memory utilization.
Looks like it's at a pretty healthy level.
But of course, Cloud Run is serverless.
So it would add more resources if they were needed.
So Cloud Run should really always be healthy.
CRAIG LABENZ: Right.
And to that point, the real question we're here to answer
is how your database is holding up.
MARTIN OMANDER: Ah, OK.
Switching to the database in the console now.
Going into the poll database.
CPU was very low.
Then we created the database.
So that took a lot of CPU.
Then it was very low here when it did nothing.
And then we started the test right around here.
It looks like CPU is still pretty low even though we're
putting a lot of load on it.
Let's have a look at memory.
Memory usage is holding steady around 600 megabytes.
And our server has 3.75 gigabytes.
So memory is also healthy.
CRAIG LABENZ: And remember, since you're not launched yet,
if it shows high CPU or memory, it
would be very easy to increase those for your database
while there are still no users to care about downtime.
MARTIN OMANDER: Oh, sounds good.
But what if I have launched my app
and the database is struggling?
What should I do then?
CRAIG LABENZ: I usually follow a four step plan to ensure
that my apps perform well.
MARTIN OMANDER: Oh, I love ordered lists.
CRAIG LABENZ: First, the obvious place to start
is optimizing your actual database queries.
Things like making sure every column involved in a where
clause has an underlying database index,
or that frequently run expensive queries like counts,
are denormalized if possible.
MARTIN OMANDER: That's great to keep in mind.
But what should I do if my app is still
slow after optimizing all of my queries?
CRAIG LABENZ: It's true that optimization can only
get you so far.
So the second step is to simply add more CPUs or memory
to your database instance.
This is often the simplest fix.
However, even that runs into limits.
For example, making your main database larger
won't necessarily help an app that
performs well in one hemisphere but poorly in the other.
And so my third step is to add read replicas,
potentially in different regions if your users are global.
MARTIN OMANDER: OK.
This makes sense so far.
And it sounds like these steps would
accommodate most applications.
What's the last step?
CRAIG LABENZ: The last step is to introduce partitioning,
or sharding, to your database.
But you should never attempt these solutions
until you've exhausted all of your simpler options.
Because these are the most complicated to do correctly.
MARTIN OMANDER: Oh, that list is really helpful.
My users incessantly refresh the page
to track which team is winning tabs or spaces.
So I'm optimistic about read replicas.
Could we set one up?
CRAIG LABENZ: For sure.
To set up a read replica, click on your Cloud SQL instance
and then click on the replicas tab on the left.
MARTIN OMANDER: All right.
Replicas.
Yep.
CRAIG LABENZ: Select the Create Read Replica
button in the middle.
MARTIN OMANDER: Very nice.
CRAIG LABENZ: And all of these default values should be good.
So you can press Create.
MARTIN OMANDER: All right.
CRAIG LABENZ: Now this will take several minutes to catch up
to your production database.
Now that your replica is ready, navigate back to your Cloud Run
service and click on the Edit and Deploy
a New Revision button.
MARTIN OMANDER: All right.
Here's my poll service.
I'm clicking it.
Edit and deploy new version.
All right.
And I'm clicking it.
CRAIG LABENZ: And on that page, select the Connections tab.
MARTIN OMANDER: Connections.
Very good.
Cloud SQL connections?
CRAIG LABENZ: Yeah.
Right there, you can press Add Connection
and select your new replica.
MARTIN OMANDER: Oh, there's my poll database replica.
CRAIG LABENZ: And once you deploy this,
you'll be ready to route your select queries
to that replica in your application code
as you see fit.
MARTIN OMANDER: Oh, that sounds excellent.
Do you have any advice on how to do that,
where to route queries?
CRAIG LABENZ: Yes.
First and foremost, read replicas
can only receive read queries, so select statements.
Insert or update queries cannot go to your read replicas.
They will fail.
And they must go to the primary database.
Now just be aware if you write to the main database
and then immediately trying to read
those same rows from your replica,
you may not get the latest data.
And in this situation, you must also send those select queries
to your main database.
MARTIN OMANDER: All right.
I'll work the read replica into my application code later.
For now, I think I'm ready to launch.
CRAIG LABENZ: Congratulations.
Your MVP is out the door and now begins
the long process of adding new bugs, I mean features.
MARTIN OMANDER: Maybe you write bugs, Craig.
But I prefer not to.
CRAIG LABENZ: Well, if you prefer not adding bugs,
then you might want to plan ahead for your eventual changes
to your database schema.
With a distributed infrastructure like Cloud Run,
it's critical to keep database schema updates out
of your application startup code.
MARTIN OMANDER: Why is that?
CRAIG LABENZ: Well, some developers
run database schema updates like creating new tables or columns
as part of their application launch.
But for example, let's say the next version
of your application needs a country table.
If this table is created by your framework when it starts,
multiple Cloud Run instances could simultaneously
detect that the table is missing and attempt
to create and populate it.
Some of those queries could fail or you
could end up with a countries table
full of duplicate records.
MARTIN OMANDER: Oh, I see.
I usually run all of my database schema
changes a separate Cloud build step
when I build my new container.
CRAIG LABENZ: Perfect.
That's definitely the right way to handle it.
MARTIN OMANDER: Cool.
I feel a lot more confident about pairing Cloud SQL
and Cloud Run now.
Let's see if I can remember all of the important tips.
First, I load test and profile my app to measure performance.
As needed, I apply the optimization steps.
Second, to avoid conflict I make sure
that my database scheme updates don't
run on application startup.
CRAIG LABENZ: That's it.
With those steps, Cloud Run and Cloud SQL
can be a great pairing like cheese and wine or peas
and carrots.
Or dare I say, like tabs and spaces.
MARTIN OMANDER: What?
No.
You don't mix tabs and spaces.
What's wrong with you, Craig?
[MUSIC PLAYING]