2

In my Django application, I want to count the number of queries made by each endpoint. I want to run this in production, so I cannot rely on any of the techniques I found with regular Django SQL logging that is only enabled when DEBUG=True, like adding a SQL logger or relying on django.db.connection.queries. Is there a context manager that works similar to self.assertNumQueries() in test cases that can be run in production like this?

def my_view(request):
    with count_sql_queries() as nr_queries:
        response = business_logic()
    logger.debug(f'There were {nr_queries} queries made')
    return response
physicalattraction
  • 6,485
  • 10
  • 63
  • 122
  • Have you looked at doing this using the database logs? – Mo. Atairu Jul 19 '21 at 15:16
  • What do you mean with "the database logs"? – physicalattraction Jul 19 '21 at 15:49
  • 1
    You can probably do this in a middleware, and then write a [`db connection execute wrapper`](https://docs.djangoproject.com/en/3.2/topics/db/instrumentation/#connection-execute-wrapper) to increment some counter everytime a database query is done – Brian Destura Jul 20 '21 at 00:31
  • I hadn't heard of execute_wrapper before, thanks! Unfortunately, this also only executes when DEBUG=True. – physicalattraction Jul 20 '21 at 14:36
  • *transaction logs. - mysql ; https://github.com/Yelp/ybinlogp OR site:github.com mysql+binlog+reader - postgres ; site:github.com postgres+log+reader – Mo. Atairu Jul 20 '21 at 16:16
  • @EM28: As far as I understand it, those are tools to work with the existing database logs, right? That is not exactly what I was looking for, since it has no direct connection with the code base itself, so you cannot easily build decorators like I intend to. – physicalattraction Jul 22 '21 at 07:15
  • related: https://stackoverflow.com/q/58396629 – djvg Aug 25 '23 at 07:02

1 Answers1

1

This implementation makes use of Django Middleware and docs on Database Instrumentation, which was released with Django 2.0. It looks something like the following:

import time

from datadog import statsd
from django.db import connection
from django.urls import resolve


class SQLPerformanceMetrics:
    def __init__(self):
        self.count = 0
        self.elapsed_query_time = 0.0

    def __call__(self, execute, sql, params, many, context):
        start_time = time.time()
        try:
            return execute(sql, params, many, context)
        finally:
            self.count += 1
            self.elapsed_query_time += time.time() - start_time


class SQLPerformanceMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response
        # One-time configuration and initialization.

    def __call__(self, request):
        metrics = SQLPerformanceMetrics()
        with connection.execute_wrapper(metrics):
            response = self.get_response(request)

        tags = [f"endpoint:{resolve(request.path).route}"]
        statsd.distribution("api.total_sql_queries", metrics.count, tags=tags)
        statsd.timing("api.time_elapsed_sql_queries", metrics.elapsed_query_time, tags=tags)

        return response

Note that this uses similar mechanisms to popular libraries like django-zen-queries.

Ben H
  • 11
  • 1