Login

Color SQL logging middleware

Author:
moe
Posted:
January 30, 2008
Language:
Python
Version:
.96
Score:
4 (after 4 ratings)

Yet another SQL logger, this time with color and tresholds. By default it will only print a summary line (total time, query count) unless one of the tresholds (total time, query count, individual query time) was exceeded.

You may use LOG_COLORSQL_VERBOSE = True to get the raw SQL for all requests regardless of any configured tresholds.

In either case the middleware will highlight problematic areas in yellow or red.

If you don't like the colors you can either set your tresholds to really high values, edit the code, or use a different middleware. :-)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
import sys
from django.db import connection
from time import *

class ColorSQLMiddleware:
    """
    Color SQL logging middleware.
    Prints a short summary (number of database queries and
    total time) for each request. Additionally it will print
    the raw SQL if any of the configurable tresholds are
    exceeded, in order to draw your attention to pages
    that trigger slow or too many queries.

    The available tresholds are:
    * Query count
    * Total Time (for all SQL queries)
    * Query Time (for individual SQL queries)

    We're trying to keep the output readable by modest use of ANSI color.  

    Example output
    ==============

    This is what you get for normal page views where
    no treshold was exceeded:
    ---------------------------------------------------------------------- 
     0.006s | 3 queries
    ---------------------------------------------------------------------- 

    This is what you get for page views where one or more 
    tresholds have been exceeded. Please note that in reality the
    SQL is *not* truncated. I did that by hand to keep this comment
    readable:
    ---------------------------------------------------------------------- 
    SELECT "django_session"."session_key","django_session"."session_data",
    FROM "django_session"
    WHERE ("django_session"."session_key" = '6495c82cf81b34dc0c4dbb91fc641
    
    SELECT "core_userprofile"."id","core_userprofile"."user_id","core_user
    FROM "core_userprofile" INNER JOIN "auth_user" AS "core_userprofile__u
    WHERE ("core_userprofile__user"."username" = 'sook') 
    
    SELECT "auth_user"."id","auth_user"."username","auth_user"."first_name
    f","auth_user"."is_active","auth_user"."is_superuser","auth_user"."las
    FROM "auth_user"
    WHERE ("auth_user"."id" = 55) 
    
    SELECT "auth_user"."id","auth_user"."username","auth_user"."first_name
    f","auth_user"."is_active","auth_user"."is_superuser","auth_user"."las
    FROM "auth_user"
    WHERE ("auth_user"."id" = 55) 
    
    SELECT "auth_message"."id","auth_message"."user_id","auth_message"."me
    FROM "auth_message"
    WHERE ("auth_message"."user_id" = 55) 
    ---------------------------------------------------------------------- 
     0.176s | 5 queries
    ---------------------------------------------------------------------- 


    Installation
    ============

    Simply put this into your middleware/ directory,
    into a file called "colorsql.py".

    Then edit your settings.py to enable the middleware:

    MIDDLEWARE_CLASSES = (
        ...
        'YOURPATH.colorsql.ColorSQLMiddleware',
        ...
    )

    Finally you may tweak the various options in
    settings.py, see below.


    Options in settings.py (with defaults)
    ======================================

    LOG_COLORSQL_ENABLE = True (boolean)
    -------------------
    True  == Enable the middleware (default)
    False == Disable the middleware, nothing will be printed

    LOG_COLORSQL_VERBOSE = False (boolean)
    --------------------
    True  == The SQL will always be printed.
    False == The SQL will only be printed if one of the tresholds is
             exceeded

    LOG_COLORSQL_WARN_TOTALTIME = 0.5 (float)
    ---------------------------
    If the total time for all db queries exceeds
    this value (in seconds) then the SQL will
    be printed and the measured time in the summary-
    line will be printed in YELLOW.

    LOG_COLORSQL_ALERT_TOTALTIME = 1.0 (float)
    ----------------------------
    If the total time for all db queries exceeds
    this value (in seconds) then the SQL will
    be printed and the measured time in the summary-
    line will be printed in RED.

    LOG_COLORSQL_WARN_TOTALCOUNT = 6 (integer)
    ----------------------------
    If the number of db queries exceeds this value
    then the SQL will be printed and the query-count
    in the summary line will be printed in YELLOW.

    LOG_COLORSQL_ALERT_TOTALCOUNT = 10 (integer)
    -----------------------------
    If the number of db queries exceeds this value
    then the SQL will be printed and the query-count
    in the summary line will be printed in RED.

    LOG_COLORSQL_WARN_TIME = 0.05 (float)
    ----------------------
    If the time for any individual db query exceeds
    this value (in seconds) then the SQL will be
    printed and the offending query be highlighted
    in YELLOW.

    LOG_COLORSQL_ALERT_TIME = 0.20 (float)
    -----------------------
    If the time for any individual db query exceeds
    this value (in seconds) then the SQL will be
    printed and the offending query be highlighted
    in RED.

    """
    def process_response ( self, request, response ):
        from django.conf import settings
        enable  = getattr( settings, 'LOG_COLORSQL_ENABLE', True )

        if False == enable:
            return response

        verbose = getattr( settings, 'LOG_COLORSQL_VERBOSE', False )

        timewarn  = getattr( settings, 'LOG_COLORSQL_WARN_TOTALTIME', 0.5 )
        timealert = getattr( settings, 'LOG_COLORSQL_ALERT_TOTALTIME', 1.0 )

        countwarn  = getattr( settings, 'LOG_COLORSQL_WARN_TOTALCOUNT', 6 )
        countalert = getattr( settings, 'LOG_COLORSQL_ALERT_TOTALCOUNT', 10 )

        qtimewarn = getattr( settings, 'LOG_COLORSQL_WARN_TIME', 0.05 )
        qtimealert = getattr( settings, 'LOG_COLORSQL_ALERT_TIME', 0.20 )

        # sanity checks...
        if qtimealert < qtimewarn:
            qtimewarn = qtimealert

        if countalert < countwarn:
            countwarn = countalert

        if timealert < timewarn:
            timewarn = timealert

        ttime = 0.0
        for q in connection.queries:
            time = float(q['time'])
            ttime = ttime + time
            if qtimewarn < time:
                verbose = True

        count = len(connection.queries)
        if timewarn <= ttime or countwarn <= count:
            verbose = True

        if verbose:
            print "\033[0;30;1m"
            print "-" * 70,
            print "\033[0m"

        i = 0
        for q in connection.queries:
            time = float(q['time'])

            if verbose or timewarn <= ttime or countwarn <= count:
                sql = q['sql']
                sql = sql.replace( ' FROM ', '\nFROM ' ).replace( ' WHERE ', '\nWHERE ' )

                tcolor = "\033[31m"
                ptime = "\033[7m %ss \033[0m" % ( time )
                if qtimealert > time:
                    tcolor = "\033[33m"
                if qtimewarn > time:
                    tcolor = "\033[30;1m"
                    ptime = ""

                print "%s%s" % ( tcolor, sql ),
                print "%s\033[1m%s\033[0m" % ( tcolor, ptime )
                i = i + 1
                if i < len(connection.queries):
                    print

        sys.stdout.write( "\033[0;30;1m" )
        print "-" * 70,
        print "\033[35;1m"

        time = ttime

        tcolor = "\033[31;1m"
        if timealert > time:
            tcolor = "\033[33;1m"
        if timewarn > time:
            tcolor = "\033[30;1m"

        ccolor = "\033[31;1m"
        if countalert > count:
            ccolor = "\033[33;1m"
        if countwarn > count:
            ccolor = "\033[30;1m"

        print "%s %.3fs \033[30;1m| %s%d queries\033[0m" % ( tcolor, time, ccolor, count )
        sys.stdout.write( "\033[0;30;1m" )
        print "-" * 70,
        print "\033[0m"

        return response

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 2 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 2 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 9 months, 2 weeks ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 10 months, 1 week ago
  5. Help text hyperlinks by sa2812 11 months ago

Comments

Please login first before commenting.