Cloud SQL Insights — Kicking the Tyres

Yesterday, via a blog post Google announced Cloud SQL Insights for PostgreSQL. After a read through the post I was immediately impressed by the detail offered in this functionality and for free!

I used to be a hardcore Oracle Database developer and spent a lot of my time down in the weeds analysing and optimising SQL, firing up the awful SQL Developer and digging though Oracles v$ views for insights. I don’t miss those days and thankfully most of the SQL I deal with now is left to basic CRUD operations.

The databases I work with don’t tend to be the bottleneck and we’re a small team, as such there isn’t a great deal of effort invested into tooling and monitoring beyond the basics. Most of the time everything “just works”… until it doesn’t, at which point there’s a mad scramble to figure out what the issue is and lots of “what’s the name of that Postgres debug view?” questions being asked. This is part of the reason we use fully managed Cloud SQL — so we don’t have to spend the resources on managing the database itself and can easily upgrade in a pinch.

Cloud SQL Insights looked like a perfect solution — low effort for our small team, it’s there when we need it and surfaces almost everything you could want to debug as well as highlight queries that are in need of tuning. I was keen to kick the tyres immediately.

Quick caveat — Cloud SQL Insights is only supported on Postgres at the time of writing.

First step is to turn Cloud SQL Insights on, navigate to the console (or use gcloud) and hit enable, there are a couple of options which can be changed at a later. I turned everything on — store client IP, enable application tags — and went with the default for the query lengths.

Options to pick when enabling Cloud SQL Insights

Once you’ve selected the options, click “enable” and it immediately begins doing an update — there was no confirmation and initially appeared the same as doing an update to CPU or RAM which requires downtime via a restart. Luckily though, there was no downtime and it took about 30 seconds.

You now get access to the Cloud SQL Insights UI and data is immediately available. The home page gives you a graph of database load and beneath that a ranked list of queries in this period based on their total execution time.

Screenshot of the main Cloud SQL Insights after data had been collected for a few hours.

The graph is interactive so I can easily select a region where there are spikes in load and see whats going on. The “Top queries” beneath the graph filters to the selected period to pin-point the culprit(s). In this case it’s a Zabbix database and some time series monitoring data is being queried.

Interacting with the UI to identify queries during a load spike.

There are a few key things you can tell straight off the bat by looking at the table. You might have a fast query thats called thousands of times so it adds up pretty quickly, or you could have a slow query thats called occasionally. Both could be worth investigation and Cloud SQL Insights ranks them by total overall time — this is great!

Example of the ranked queries by total time.

From here you can click on a query and learn more, in particular you can see a full query plan — this shows you how the database engine is optimising the query and what steps are being taken to get the results. This is not a post about SQL optimisation but by using query plan you can identify how best to improve the query itself.

The way Cloud SQL Insights present the plan is breath of fresh air — it’s highly visual and easy to read. The thickness of the lines joining the steps indicates how many rows were returned and blue labels are placed on steps indicating which step has the highest latency, row count or cost. In the case of the image below they are all attributed to the “Seq scan” step.

sqlcommenter

Knowing which query is the culprit of high load and why is one thing, but now you’ve got to fix it. In order to do that you’ve got to track down where that SQL lives in your applications code base. Sometimes it’s easy — maybe its a unique query, but other times there could be a plethora of calls to very similar or even the same query with different arguments.

sqlcommenter lets you add plain SQL comments to a query that contain key value pairs, these pairs will then be read by Cloud SQL Insights and stored alongside your queries as tags. For example:

SELECT * from SALES /*Controller='customer_dashboard',Route='demo/customer_dashboard'*/

You can then see this information in the UI right beside your queries.

Image taken from Cloud SQL Insights documentation showing tags picked up from sqlcommenter

Whilst this is great for raw SQL queries, a lot of people (including myself) use an ORM and never get access to the actual SQL in order to add these comments. Luckily Google have though of this and created a set of sqlcommenter client libraries that support most of the common ORMs including Django which I use.

I took the Django one for a spin and it couldn’t have been easier. You simply install the library:

pip install google-cloud-sqlcommenter

And add it as a middleware in settings.py

MIDDLEWARE = [
    "google.cloud.sqlcommenter.django.middleware.SqlCommenter",
    ...
]

I added it, built and deployed in about 10 minutes and could immediately start seeing tags appearing with the Controller and Framework populated.

List of queries showing their tags added by the Django sqlcommenter middleware

Settings are provided to determine which tags are populated. The documentation on GitHub claims that Route should be populated by default. I’ll have to investigate why this isn’t the case.

The sqlcommenter library didn’t seem to mention python and/or Django version compatibility so I gave it a quick spin on a legacy codebase that’s still python2.7. It looks like it makes use of DatabaseWrapper.execute_wrapper which wasn’t introduced until Django 2, so you’ll need at least Django 2 which means python3.4 or greater — not surprises there! I’ve submitted a PR to help clarify this.

Final Remarks

Cloud SQL Insights is a great addition and I’m excited to see it added for Postgres first which usually tends to fall behind mysql functionality on Cloud SQL.

This is a low-effort tool that can really help in a crisis or help you get your database running smoother — I often find that if you provide an engineering team more visibility they make small tweaks an improvements along the way.

I think additional functionality provided by sqlcommenter is great it’s a very elegant and simple solution to this problem and the fact Google have launched the client libraries alongside Cloud SQL Insights on day one is fantastic.

I’m pretty excited to start using this in anger.

Now, if you’ll excuse me I’m off to enable this is production and add sqlcommenter to all of the things…