SPEAKER: Cloud SQL Insights is a simple open tool
that helps developers quickly understand and resolve
database performance issues on Cloud SQL.
It provides developers with a single interface
for self-service application-centric monitoring
and diagnosis of database performance issues.
To access Insights, I can click Query Insights
in the left navigation.
I'm going to walk you through how
to use Insights to identify and diagnose a database performance
problem in an application.
In this scenario, I'm an application developer
on a team that developed a ride-sharing application.
We've gotten a report from some of our users
that the application is running slowly.
Let's look at how I can use Insights
to investigate and find the cause of the problem.
Looking at the top-level query insights dashboard,
I'll start with the database load
all queries graph, which can help me visually
identify performance problems or spikes in database load.
The database load graph confirms that there's
a problem that started happening around 12 o'clock.
The colors in the graph help me see
that there's an increase in CPU and an even larger increase
in lock weight.
Now I need to find out which query
is causing the problem, what the root cause of the problem is,
and where in my application code the problem lies.
To try to identify which query is causing the problem,
I'll start with the queries table.
The queries table contains normalized queries
for the time frame selected.
The queries in the table are sorted by total execution time
by default, but I can also filter it by other criteria,
like CPU time, I/O weight, or lock weight.
The first query in the table certainly
stands out, as its total execution time is much larger
than the rest of the entries.
Like many modern applications, this application
consists of several different services
and I want to know which one is causing the performance
problem.
Traditional monitoring tools only
provide a query-centric view of performance.
With Insights, I can toggle from the queries table
to the tags table to see a list of queries
tagged by business logic, giving me an application-centric view.
The tags table is especially helpful to me
as a developer, since this application was built
using Django's ORM rather than by writing
the SQL queries myself.
Insights uses SQL commenter, an open source library
which provides instrumentation and wrappers to augment SQL
from frameworks in ORMs.
If your application is using one of the supported ORMs,
the queries are automatically tagged with little to no change
to your application code.
So you're more easily able to identify query load
and pinpoint where the problem may lie in your application,
since you can see information like actions, controllers,
and routes.
The first tag query for the payment charge controller
looks like it's the problem.
Now that I've identified that this is likely the slow query,
I want to find out why it's slow.
I'm going to click through to see more details for this tag
query.
Insights include sample executed query plans, which
I can use to understand why the query is slow.
Query plans can be complex and difficult to understand,
but Insights provides visual query plans
that can help me easily detect which part of the query
is slow.
Each part of the query shows the latency, rows returned,
and the costs for that operation.
In this query, an update is happening.
The modified table looks to be contributing the most
to the latency because of locking contention.
Now that we've identified the slow query and the cause,
let's confirm the source of the problem.
Insights provides in context visualization
of end-to-end tracing to help me find which application code is
causing the problem.
In addition to being able to see the controller and route,
it can also help me understand if a particular host or user is
the source of a problem.
While I'm viewing the trace visualization
from within Insights in this example,
Insights complements your existing APM and observability
tools.
Insights provides database metrics and traces
through the Open Standard, OpenTelemetry,
and Cloud monitoring and Cloud trace APIs.
This makes it easy to do end-to-end tracing
in your existing tools.
In this video, we've looked at ways
you can use Cloud SQL insights to help with diagnosing
and troubleshooting application performance issues
all from a single intuitive UI.
In the description below the video,
you'll find links to a blog post introducing insights
and the documentation for Cloud SQL insights and SQL commenter.
Or you can click on the Codelab to try it out for yourself.