Cloud Native Computing Foundation Cloud Native DevX Day North America 2021, 30 Oct 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: The Future of Database Observability is Open: SQL Commenter Merges W... Jan Kleinert & Nimesh Bhagat

Description

Don’t miss out! Join us at our next event: KubeCon + CloudNativeCon Europe 2022 in Valencia, Spain from May 17-20. Learn more at https://kubecon.io The conference features presentations from developers and end users of Kubernetes, Prometheus, Envoy, and all of the other CNCF-hosted projects.

The Future of Database Observability is Open: SQL Commenter Merges With OpenTelemetry- Jan Kleinert & Nimesh Bhagat, Google

Google is merging and donating SQL commenter to OpenTelemetry to create a vendor-neutral standard for database observability. Databases are at the heart of an enterprise’s application stack and data fabric. Database observability is important to every role in the organization: developers, DBAs, security engineers, data engineers, and SREs. Each of these roles use a variety of application and infrastructure monitoring tools that need access to database activity, so it's important that database telemetry is easily accessible and integrates seamlessly with your choice of tooling. OpenTelemetry makes it easy to create and collect telemetry data from services and software, then forward them to a variety of analysis tools. But OpenTelemetry today lacks a common standard by which application tags and traces can be sent to databases and correlated with application stack. Adding SQL commenter to OpenTelemetry will enable a rich ecosystem of APM tools to integrate with databases eliminating need to build custom integration for every database vendor. In this session, Jan and Nimesh will describe the benefits of SQL commenter and demonstrate how to enable it and use it to troubleshoot slow running databases with query tagging, end to end tracing and auto-ORM instrumentation.

A

Hello all welcome to our talk. The future of database observability is open. I am nimesh bhagat. I am a product manager at google cloud. I lead database observable with me. I have jan kreinert 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'll tell you what is database observability for developers, give you an overview on sql commenter, which helps you simplify.

A

It then we'll get into a demo and close it with a q, a.

A

So, let's start why there needs to be a database observability for developers at google. We did an extensive research and we found with devops more and more developers are responsible for troubleshooting database. So when a pager goes up at 2 am it is the developer is on the line and less and less bba is in many of the organizations.

A

What our reset fund is the three key aspects where a developer might differ than a dba. The number one is: the developers are responsible for the full stack and not just the databases.

A

Number two many of these developers are increasingly using orms to write the database code in their 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 a journey inside database.

A

If you look at the picture on the right, that's how a modern application stack looks like when you have so many micro services. It becomes hard even to tell if the database is a problem or even tell what part of the application start stack is causing the slowness.

A

That is where application performance, monitoring tools come to rescue developers, love them, and there are plenty of them available which each of the teams and developers prefer.

A

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.

A

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.

A

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 manage 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.

A

Now think of a word 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 span in such a world. The developers can use their own tool. The dbs no longer have to think about installing agents, the ecosystem partners, the apm tools.

A

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 in this world. Everyone wins, you might say this is the. 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 in this year. In january, we'll launch cloud sql insights and in order to enable this application, centric monitoring, we open sourced a library called sql commander.

A

What sql commander does is it passes tags, open, telemetry context to databases via sql commands, as you see in the example on the right over here now, once we got sql commander, we found our open source community a partner 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 merge 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.

B

Thanks numesch, now we're going to 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.

B

This helps in more easily correlating slow queries with source code, giving insights into back-end 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.

B

You can find the link on this slide to the github repo, which, in turn, links to the documentation for sql commenter.

B

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 cloud sql.

B

If 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.

B

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.

B

These comments must come after the sql statement. Any existing comments must be preserved and the key value pairs have to be comma separated, url encoded with meta characters escaped and then sorted in lexicographical order.

B

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.

B

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.

B

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.

B

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.

B

Since this query has taken 400 milliseconds, we could go into the assign order method to see if we can understand why it's taking a long time.

B

Now we're going to walk through the process of setting up a postgres application, deploying an app that uses postgres database for back-end storage, then we'll enable sql commenter in the application and look at logs to view and monitor queries.

B

The application for our demo is a food delivery application. So there are drivers. There are orders.

B

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.

B

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.

B

As we troubleshoot this issue.

B

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.

B

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. We have to add this line to the middleware section to be able to use sql commenter.

B

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 open census or open telemetry 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.

B

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.

B

So not only are we able to see the queries that were issued by our application.

B

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.

B

I'm going to pull up logging right now and I'm going to filter just for database logs, so we want to see the logs for our postgres database. Let's look for a time frame that matches what we had in our application.

B

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.

B

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.

B

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 log, 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.

B

In this case we see payment charge here. This is the one that seems to have the most total time.

B

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 in 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.

B

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.

B

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, dba 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.

B

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.

B

This will enable a rich ecosystem of apm tools to integrate with databases and eliminate the need to build custom integration for every database.

B

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.

B

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.

B

All right so now we've had a demo. I want to 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 names put together on the donation of sql commenter to open telemetry, 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.

B

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.