 Hi, thank you for joining us for this session on demystifying database performance issues with SQL Commenter. I'm Jan Kleiner and I lead an advocacy team within Google Cloud that focuses on databases and infrastructure. And Bala Chandrasekharan is a software engineer in Google Cloud focusing on databases and also working as a software engineer on SQL Commenter. So many developers use ORMs to simplify development of applications that communicate with databases. But since ORMs are generating the SQL statements, it becomes difficult to determine which application code is resulting in slow queries. So in this session, we'll show you how you can use SQL Commenter and open source library to help solve this problem. We'll demonstrate how to set up a new SQL Commenter with a Node.js application that uses Kinex so that we can more easily diagnose query performance. We'll also look at the other frameworks and ORMs that SQL Commenter supports and how you can view this information in database logs and observability tools, including Cloud SQL Insights. So let's get started. SQL Commenter is an open source library that enables ORMs to augment SQL statements before execution with comments about the code that caused its 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 without having to be a database performance expert or use a multitude of tools. Application developers don't need to do very much application code change at all to enable SQL Commenter for applications that use ORMs. You can find a link on the 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 is supported by several ORMs and frameworks that you can see here. It's also been tested against a number of databases including Postgres, MySQL, MariaDB, SQLite and Cloud SQL. Now let's look at some of the key parts of the specification for the SQL Commenter algorithm to understand how it works. SQL Commenter augments a SQL statement with a comment containing serialized key value pairs or application tags as we call them that are retrieved from the supported ORM or framework that you're using. So this can be information like the controller, the route, the action, the database driver, the framework, those types of things. The comments must come after the SQL statement and any existing comments must be preserved and the key value pairs have to be comma separated, URL encoded and any meta characters escaped and then they get sorted in lexicographical order. You can read even more detail on the spec and the algorithm at this link but what we just described should give you the general idea for how it works. These augmented SQL statements will then show up in your database logs. By having these application tags from the ORM in your logs, SQL Commenter will help you determine what application code is associated with any slow queries. Here's an example of a query log from a Postgres database that's 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, the SQL style comment has been added in the form of key value pairs. This 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 a Django application. You can also see information about the route, database driver and framework. Now, since this query has taken 400 milliseconds, we can go into the assign order method to see if we can understand why it's taking a relatively long time. Now we're going to walk through the process of deploying a Node.js application that uses a Postgres database for backend storage. Then we'll enable SQL Commenter in that application and look at logs and also Cloud SQL insights to view and monitor queries taking advantage of that SQL Commenter information. We'll switch over to the demo now. So first we'll use a GCloud command line tool to create a new Cloud SQL instance named MyInstance. And we'll create that with Cloud SQL insights enabled. So here you can see we're going to create a Cloud SQL instance named MyInstance. Here we're specifying the version of Postgres, the region where we want this to be created. You need to enter our password. And then this flag here enables query insights, allows it to record application tags and record client IP addresses. We'll learn more about this in a moment. And now our Cloud SQL instance is being created. This may take a few minutes, so we're going to speed this up for the sake of the demo. Okay, so now our Cloud SQL instance has been created. Next, we will create a database that we'll use for the sample application. The database is going to be named VotesDB. We'll create that now. Okay, so now we have our VotesDB database. I'm going to get the instance connection name. You can get that from here in the console. I'm going to copy it now. Alternatively, you can get it from the command line by running the following. GCloud SQL instances described. My instance pipe that to grep connection name. And you can see there, we have the same instance connection name information that I grabbed from the console. We'll need that later when we are configuring the Node.js application. Okay, so now we need to create a service account that we can use with the application. Service accounts are used to grant permissions to use different services within your Google Cloud project. So in this instance, we will need one to give the Cloud SQL proxy that we'll be using with this application permission to connect to our Cloud SQL instance. So let's do that now. Here, the service accounts. We'll create a new service account. I will call it node demo. Click create for roles. I'm going to filter for a Cloud SQL and we will find the Cloud SQL client role. That's what we need in this case. Click continue and then done. Once that service account is created, I'll click here to manage keys and we want to add or create a new key. We want the JSON format. So we'll click that. And now we have this key created. So I can click these three dots here and choose upload file. And I will upload that JSON file that was just downloaded to my local machine. So that's uploading. And then when I type LS, it's actually in our home directory. So I'm going to move, I'm going to move that JSON file here so we know where it is. So as I mentioned before, we're going to use the Cloud SQL proxy for communication between our application and the database instance. So we will download that now with this command. You can see that we have it here now. And then to run the proxy, we're going to use that instance connection name that we copied before as well as the path to our credential file. Now I don't believe that I have that in my clipboard anymore. So let's go ahead and get that again. And copy it. All right, so Cloud SQL proxy, credential file equals no demo. No demo. One, two, three, four, five, six, seven, eight. JSON into instances. Pass that into our connection name. Close this out so we can see. Okay, so Cloud SQL proxy will pass in the path to our credential file. For instances, we'll set that to our instance connection name followed by equals TCP colon five, four, three, two, which is the port. And ampersand, so that runs in the background. All right, we know this is successful since it says ready for new connections here. I'll close this so we have more space. All right, now we need to clone and test our application. So we're going to be using the Node.js documentation samples. So I'll clone that now and we'll go into that directory, put SQL and Postgres connects. All right, here's where our application is. I'm going to go ahead and run npm install. She'll take a few seconds here. Now we're going to set up some environment variables. There's other ways of doing this, but for the sake of this demo, we're just going to use these environment variables here. So Cloud SQL connection name. I have forgotten our connection name, so let's go back over and grab that again. As a reminder, you can get it from here. I'm gonna paste that. All right, next we will export DB host, which we're going to set to 127.0.0.1 colon five, four, three, two, since we are running through the Cloud SQL proxy, which is running locally. Okay, next export DB user. Our user is the Postgres user, export DB pass, which is the password we set up for it, and then export DB name. And our DB name is votes DB. All right, so now in our sample application, we have this create table.js file. We're going to run that, and what that's going to do is create a database table that the app needs in order to run, and it'll ensure that the database is properly configured. Then we'll start our sample app. So to do that, we are going to run the following. So we'll run node create table.js, then we'll pass in that database user, password name, our Cloud SQL connection name, votes, and then our DB host. So these are just parameters expected by this create table.js. All right, it looks good. So it says it successfully created that votes table and then closed the connection. So we know we've got good connectivity here, and we're ready to go. So let's make sure this application works. We're going to go to web preview, preview on port 8080. It's going to open in this new tab here, and hopefully we forgot to actually start the application. That's important. So let's run npm start. All right, and now if we go here and refresh, we should, yes, now we have access to our application. So this demo application is a voting app where you can vote for tabs versus spaces. So if I click here for spaces, I can vote. Lots of people could vote if we had multiple people accessing this app. For now I'm just going to put in a few votes, mostly for spaces, but we'll put one in for tabs just for good measure. All right, so now we've saved some data in the database by making those votes. Now let's create a page to be able to view all votes. Instead of just recent votes here, what you can see on the recent votes page, it just shows the five most recent votes. But let's say we had a lot of votes and we want to be able to view all of them. I'll go ahead and do that now. So control C to stop the application. I'm going to open up the editor in Cloud Shell here. Make it a little taller, so you can see what we're doing. And we're gonna find server.js in here. Doc samples, Cloud SQL, Postgres connects. And then here we go, server.js. We'll add a new function. Let's find where we have get votes in here. Insert, vote, get votes. And below that we're gonna add a new function for get all votes. Below where all the other routes are defined here, we'll add a new one for get all votes, as you can see. Okay, and now we need to create a new file in our views directory, new file. I'm gonna call this one allvotes.pug and I'll just paste in the code for this new page. It's just gonna be a page to display all the votes. Okay, now we can switch back over to the terminal and run npm start again. Let's see how that goes. The reason that we're doing this is to just make things a little bit more interesting once we use Cloud SQL Insights to look at the database activity. So now if I add get all votes to our URL here, there you can see we've got a list of all the votes, all eight of them that are present in the database. All right, so that's working. Now we wanna enable SQL Commenter in our application. Now for every framework or ORM that you're using, this process will be a little bit different. This is how we're going to do it for this particular app which is using connects. So the first thing that we'll do is install the packages that SQL Commenter needs. Now I'm gonna do this all in one step with an npm install. So we have Google Cloud, SQL Commenter Connect. So on, you can find the instructions for each ORM or framework in the SQL Commenter documentation. We'll run this now, great. Okay, we'll switch back to the editor. So now that we've got those dependencies set up, we need to go back to our server.js file and go up to the top here of this file here. After this, we're going to add the following code. So add this require and then set it up to actually use it, that will go here. And again, you can find these code snippets and the documentation in the SQL Commenter docs. All right, so that's all we need to do to enable SQL Commenter in this application. If we've done that correctly now when we run npm start, we should be good to go. Let's go back to our application and add some more data to the database now that we have SQL Commenter enabled. So now that SQL Commenter is enabled, there should be comments added into the logs that include some of the information about our application. Those application tags we talked about before, those should be present now that we've enabled SQL Commenter in the application. I'm going to go to that Get All Votes page again, Get All Votes, and for fun, we'll just refresh this a few times. Okay, now let's take a look at Cloud SQL Insights so that we can view some of the query performance and look it into ntracing. So if we go into our Cloud SQL instance, this query insights link here or this link here below the main graph will take us to the query insights dashboard. So here we go. This top level query insights dashboard shows this database load all queries graph which contains things like CPU capacity, CPU and CPU weight, IO weight and lock weight. In this case, the database query load is low so there aren't really any large spikes on the graph. Below this graph, you can see a table of queries that contains normalize queries for whatever time range is selected. Here we have one hour. So you can click into an individual query if you'd like to view detailed information about the query like database load for the specific query, query latency, query plan samples and top users. But if an application is built using an ORM like this one, you may not know which part of the application is responsible for which query and that's what the top tags section here can help you understand. So we'll toggle there to the tags table and now you can see database load broken out by which route generated the load. So what you can see here is get all votes has more rows returned on average, which makes sense. The execution time isn't problematic in any of these cases, but we're going to go ahead and click on get all votes anyway to look at it in more detail. One other thing to notice, there is this row here where there is no route. This is because before we enabled SQL Commenter in our application, we didn't have any application tag information being passed through to Cloud SQL Insights. So you can still use it even without SQL Commenter enabled and see this query information, but to give that added benefit of having application tags, SQL Commenter must be enabled for your application. So let's go ahead and click on get all votes and take a look at what data we can see in more detail. You can click on one of the dots in the query plan samples graph to see a sample query plan. Now I'm going to switch to showing you query plans and end-to-end tracing for a more complex application so that you can get a better sense for what you can see in the tool. The query plans show how Postgres executes a query under the covers, making it easier to determine if there's operations that result in slowness. Cloud SQL Insights also has in-context visualization of end-to-end tracing, which can be helpful for doing further investigation into what parts of an application are generating slow queries. To see it, you can click the end-to-end tab to view an in-context trace. Note that you can learn even more about how to use and interpret the query plans and end-to-end tracing in the Cloud SQL Insights documentation which we'll reference at the end of the presentation. All right, so now you've learned how to enable SQL Commenter in a Node.js app that connects to a Cloud SQL Postgres database and you've learned a little bit about how you can use Cloud SQL Insights to monitor and investigate query performance in that application. On this slide, we have a list of resources if you want to learn more about either SQL Commenter or Cloud SQL Insights. There's a link to the SQL Commenter documentation and the GitHub repo. And for Cloud SQL Insights, there's a link to the overview docs as well as a code lab that's very similar to the demo we showed you today. Thanks so much for joining us today to learn a bit more about SQL Commenter. If you'd like to follow up with us, you can reach us on Twitter or email as shown on this slide. We would love to hear your feedback, so get in touch with any questions, comments. We'd be more than happy to hear from you. Thanks so much.