 Hello, all. Welcome to our talk. The future of database observability is open. I am Nimesh Bhargat. I am a product manager at Google Cloud. I lead database observability. With me, I have Jan Kainat. Jan is our lead on developer advocacy team. Me and Jan have a common passion. We want to simplify database for developers. Today, we're going to talk to you about how we can improve database observability for developers. On the agenda, we have one. We're going to tell you what is database observability for developers, give you an overview on SQL commenter, which helps you simplify it. Then we'll get into a demo and close it with an Q&A. So let's start. Why there needs to be a database observability for developers? At Google, we did extensive research. And we found that DevOps, more and more developers, are responsible for troubleshooting database. So when a page goes up at 2 AM, it is the developers on the line and less and less BDAs in many of the organizations. What I recently found is the three key aspects where a developer might differ than a BDA. The number one is the developers are responsible for the full stack and not just the databases. Number two, many of these developers are increasingly using ORMs to write the database code in the favorite programming language and not SQL. And three, these developers, more and more, use application performance monitoring tools to troubleshoot issues. And they don't start the journey inside database. If you look at the picture on the right, that's how a modern application stack looks like. When you have so many microservices, it becomes hard even to tell if the database is a problem or even tell what part of the application stack is causing the slowness. That is where application performance monitoring tools come to rescue. Developers love them and have plenty of them available, which each of the teams and developers prefer. But what we saw is admins typically limit the number of APM tools they permit in the ecosystem. Let's take a look at the picture on the right. The boxes show the APM tools. In order for those APM tools to talk to the database, they have to pass on the application context associated with database telemetry. And for that, each one of them creates a format and a plugin which needs to be installed on the database. Now, most DBAs will limit the number of plugins which are permitted on the database, which limits the number of tools they permit. And to make things even more challenging, most managed database services on cloud do not permit installation of plugins on the databases, which means many of these APM tools cannot get a good visibility into the database. Now, think of a world wherein every APM tool talks to any database engine type, wherever it's hosted in a common format. Imagine your tags and open telemetry context is pushed to database, the database creates its own telemetry and associates with this space and adds its own spam. In such a world, the developers can use their own tool, the DBAs no longer have to think about installing agents, the ecosystem partners, the APM tools, they can focus their energies in building really good tools, the ones which focus on analysis and stop focusing their energies on building custom formats and plugins. And this world, everyone wins. You might say, this is what open telemetry is supposed to do. And that was a missing gap in open telemetry is what we found for databases. Earlier in this year in January, we launched Cloud SQL Insights and in order to enable this application-centric monitoring, we open sourced a library called sql-commenter. What sql-commenter does is it passes tags and open telemetry context to databases via sql-comments, as you see in the example on the right over here. Now, once we launch sql-commenter, we found our open source community, our partners starting to think about sql-commenter as a solution to close the gap which open telemetry has for databases. And the good news is in September, we merged sql-commenter to open telemetry to extend the vision of databases to open telemetry. I'll hand it over to Jan to tell you how. Thanks, Namesh. Now we're gonna look at how sql-commenter works and then how to enable it and use it in an application. As Namesh explains, sql-commenter is an open source library that enables ORMs to augment sql statements before execution with comments about the code that caused that execution. This helps in more easily correlating slow queries with source code, giving insights into backend database performance. In short, it provides observability into the state of client-side applications and their impact on database performance. Application developers don't need to do very much code change at all to enable sql-commenter for applications using supported ORMs. You can find the link on this slide to the GitHub repo which in turn links to the documentation for sql-commenter. sql-commenter is available for Python, Java, Node.js and Ruby, and it's supported already by several frameworks that you can see here. It's also been tested against a number of databases including Postgres, MySQL, MariaDB, SQLite, and CloudSQL. You're not using one of the supported ORMs, you can still use sql-commenter's algorithm to generate comments yourself and tag sql statements. Let's look at some of the key parts of the specification for sql-commenter's algorithm to understand how it works. sql-commenter augments sql statements with a comment containing serialized key value pairs that are retrieved from the supported ORM or framework that you're using. This can pass information along such as action, controller, route, framework, as well as the open telemetry trace context. These comments must come after the sql statement. Any existing comments must be preserved and the key value pairs have to become a separated URL encoded with metacharactors escaped and then sorted in lexical graphical order. You can read even more detail on the spec and algorithm at the link on the slide. So these augmented sql statements will then show up in your database logs. By having these application tags from the ORM in your log, sql-commenter helps developers determine what application code is associated with the slow or problematic queries. Here's an example of a query log from a Postgres database that's being used by a Django application with sql-commenter for Django enabled. In the log, you can see an update statement being executed. And at the end of the sql statement, you can see the sql-style comment has been added in the form of key value pairs. The comment was added by sql-commenter to the query that was generated by the Django ORM. As you can see from the comments, it provides information about the controller, which in this case is a sign order. This is the controller method that sent the query. In the case of Django, the controller in an MVC pattern maps to the view in the Django app. You can also see information about the route, database driver, framework, and trace context. Since this query's taken 400 milliseconds, we could go into the assign order method to see if we can understand why it's taking a long time. Now we're gonna walk through the process of setting up a Postgres application, deploying an app that uses Postgres database for backend storage, then we'll enable sql-commenter in the application and look at logs to view and monitor queries. The application for our demo is a food delivery application. So there are drivers, there are orders. The drivers need to be able to charge and refund orders as well as see a listing of orders. And you need to be able to register both drivers and customers. Our application is backed by a Postgres database. We happen to be using Cloud SQL here, but you could be using any Postgres database. Now we got a report within the last hour that some of our drivers are experiencing slowness with the application. When we deployed this application, we enabled sql-commenter. So we should be able to take advantage of some of that information as we troubleshoot this issue. But let me show you first how we enabled sql-commenter for the application. If you're using one of the supported ORMs for sql-commenter, there's really not much application code changes that you have to do to be able to get sql-commenter up and running. Since we're working on a Python app that uses Django, I've pulled up the documentation in the GitHub repo that tells us what we need to do to install sql-commenter and enable it in our application. So you can see the commands you need to use to install the proper Python modules here. And then there's information on usage. Again, we're using Django, so this is what we need to look at. You have to add this line to the middleware section to be able to use sql-commenter. There are also some attributes that you can enable for things like if you want the application name to flow through in the comments, or if you want to use openCensus or openTelemetry. We're not using sql-alchemy in our application, but you can see that there's also instructions for that and other frameworks as well. In the case of this Django app, we installed the necessary requirements, and then in the middleware section here, we added this line here. We wanted to set some particular options for our sql-commenter integration, so we've set app name is true, DB driver is true. Now with all of that done, that means that as our application is running, sql-commenter is augmenting any of these queries that are issued by our application with the comments. For my database, I have Cloud SQL Insights enabled, and that's using that sql-commenter data as well. So not only are we able to see the queries that were issued by our application, we can also look at the application-centric data through the application tags that we're providing through the sql-commenter integration. But before we look at that, let's look straight at the logs, because we don't need Cloud SQL Insights in order to be able to take advantage of this information. I'm gonna pull up logging right now, and I'm gonna filter just for database logs, so we wanna see the logs for our Postgres database. Let's look for a timeframe that matches what we had in our application, something like this. All right, let's look in our logs to see if we can find any of these sql-comments in our statements. They show up here in this text payload, and we'll look for things like, we'll look for things like controller, and see what we can find. Okay, so you can see, similar to what we saw in some of our examples, we have a select statement here, followed by a comment that was generated by sql-commenter. So you can see we have controller is payment charge, DB driver, framework, the route of demo charge, and then trace parent. So this information is successfully flowing in through to our logs, so we can use that now to understand what's going on. Now let's look at query insights to see how that sql-commenter information sent through to the logs can be integrated into your tools. In this case, we see payment charge here. This is the one that seems to have the most total time. Let's click in and see what we've got. So you can see here we've got some query plan samples. I'll choose one of these and see what we can see. So you can see we have the query plan here, but what I'm interested in is the end-to-end tracing. And here in the end-to-end tracing, we can see this information about the queries. And we have that normalized query data coming through here, as well as the route demo charge and controller that we saw on the dashboard here. So this is one example. You can see the total duration of the query here. And if we look at another one of these with a longer duration, we can see what is different between the two, if anything. We could go back to the logs from here if we choose, but we've already looked at that. Now, Cloud SQL Insights is just one tool. Many of your favorite APMs could also pull in this type of information so that you could do a lot of your monitoring all in one place. All right, let's have a quick recap of what we've covered today. Google is merging and donating SQL Commenter to open telemetry in order to create a vendor-neutral standard for database observability. Database observability is important to pretty much every role in an organization. Developers, DBAs, security engineers, data engineers, SREs, list goes on. Each one of these roles uses a variety of application and monitoring tools that need access to database activity. So it's important that database telemetry is easily accessible and can integrate seamlessly into your choice of tools. Open telemetry makes it easy to create and collect telemetry data from services and software and then forward them to a variety of analysis tools. But currently, open telemetry lacks a common standard by which application tags and traces can be sent to databases and correlated with the application stack. By adding SQL Commenter to open telemetry, this will enable a rich ecosystem of APM tools to integrate with databases and eliminate the need to build custom integration for every database vendor. We've also covered the steps it takes to enable SQL Commenter in a Django application and talked a little bit about the other frameworks, programming languages and ORMs that are currently supported. In addition, we looked at the SQL Commenter algorithm and the specification which you can use if you want to manually comment your SQL statements yourself. Finally, we looked at how that application tagging information and trace context can propagate through to your logs so that you can use it for application-centric monitoring and troubleshooting around database performance with your application. All right, so now we've had a demo. I wanna leave you with some resources where you can learn more. For resources on SQL Commenter, there are links here to the documentation as well as the GitHub repo. There's a recent blog post that Namesh put together on the donation of SQL Commenter to OpenTelemetry. There's a lot of great information there, including if you want to get involved. And then finally, we touched a little bit on Cloud SQL Insights. So if you'd like to learn more about that and how it integrates some of that SQL Commenter information, there's a link to the documentation here as well. Thank you so much for joining us for this talk. Namesh and I will be sticking around for Q&A and we'll be happy to answer any questions that you have. Thanks so much.