pyramid_perfstat / pyramid_perfstat / perf_serializer.py

  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
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
#!/usr/bin/python
# -*- coding: utf-8 -*- 
#-----------------------------------------------------------------------------
# Name:        pyramid_perfstat/perf_serializer.py
# Purpose:     serialize recorded data inside a db
#              for the moment only sqlite db is supported ...
#
#              this module don't use sqlalchemy or ORM to keep 
#              performance as best as possible. SqlAlchemy would
#              bring confort and will decrease performance as well...
#              pyramid_perfstat try to be as light as possible
#              
#              (even if sql is boring ...  :'( )
#
#              to reduce overhead :
#                    - no object
#                    - procedural code
#                    - simple sql query
#
#              TODO : add some configuration key to change default db
#              TODO : add interface for serialization
#              TODO : write other db target
#
# Author:      sbard
#
# Created:      01/02/2012
# Copyright:   --
# Licence:     BSD License
# New field:   ----------
#-----------------------------------------------------------------------------

import logging
import datetime
import sqlite3
import time
import threading

from zope.interface import Interface

from pyramid.events import subscriber
from pyramid.events import NewRequest

from pyramid.interfaces import IRouteRequest
from pyramid.interfaces import IRoutesMapper
from pyramid.interfaces import IViewClassifier
from pyramid.interfaces import IMultiView
from pyramid.interfaces import IView

from pyramid.threadlocal import get_current_request
from pyramid_perfstat.utils import ROUTE_PREFIX
from sqlalchemy.ext.sqlsoup import SqlSoup

lock = threading.Lock()

try:
    from sqlalchemy import event
    from sqlalchemy.engine.base import Engine

    @event.listens_for(Engine, "before_cursor_execute")
    def _before_cursor_execute(conn, cursor, stmt, params, context, execmany):
        setattr(conn, 'pfstb_start_timer', time.time())

    @event.listens_for(Engine, "after_cursor_execute")
    def _after_cursor_execute(conn, cursor, stmt, params, context, execmany):
        stop_timer = time.time()
        request = get_current_request()
        duration = stop_timer - conn.pfstb_start_timer
        if request is not None:
            with lock:
                duration_queries = getattr(request, 'pfstb_duration_queries', [])
                duration_queries.append(duration)
                setattr(request, 'pfstb_duration_queries', duration_queries)
        delattr(conn, 'pfstb_start_timer')
    has_sqla = True
except ImportError:
    has_sqla = False

@subscriber(NewRequest)
def new_perfstat_request_subscriber(event):
    """
    open the connection to sqlite db
    subscribe to event NewRequest
    """
    request = event.request
    request._PerfDbManager__perfstat_db = sqlite3.connect('perf_app.db')
    request.add_finished_callback(close_db_connection)

def close_db_connection(request):
    """
    close the connection to sqlite db
    """
    request._PerfDbManager__perfstat_db.close()

class PerfDbManager(object):
    """
    sqlite hand serializer
    """
    # unique id identifier each time pyramid web app
    # reload
    
    id_session = None

    def __init__(self, request):
        """
        connect to db ...
        """
        # init behavior ?
        if request is None :
            self.conn = sqlite3.connect('perf_app.db')
        else :
            self.conn = request.__perfstat_db
        
        self.request = request

        if request is not None :
            self.matched_url = request.path_url
            self.route = request.matched_route
            self.matched_route = self.route.name
            
            self.mapper = request.registry.queryUtility(IRoutesMapper)
    
            registry = request.registry
    
            request_iface = registry.queryUtility(IRouteRequest,
                                                  name=self.route.name)
            self.matched_route_name = self.route.name

            view_callable = '<unknown>'
            if (request_iface is None) or (self.route.factory is not
                                           None):
                view_callable = '<unknown>'
            else:
                view_callable = registry.adapters.lookup(
                    (IViewClassifier, request_iface, Interface),
                    IView, name='', default=None)
                if IMultiView.providedBy(view_callable):
                    view_callable = view_callable.match(None, request) # TODO MultiView.match() expects a context as a first arg. I guess it would be preferrable to provide this information instead of None.

            self.view_name = ".".join((view_callable.__module__,view_callable.__name__))

    def init_db(self, from_scratch=False):
        """
        create table and usefull stuff ...
        in the db ...
        """
        
        c = self.conn.cursor()
        
        if from_scratch :
            c.execute("""drop table if exists pyramid_measure""")

        c.execute("""create table if not exists pyramid_measure (id integer PRIMARY KEY,
                                                                 date_measure datetime)""")

        PerfDbManager.session_perf_id = c.execute("""insert into pyramid_measure values (null, ?)""",
                                                  (datetime.datetime.now(),))
        PerfDbManager.session_perf_id = c.lastrowid

        if from_scratch :
            c.execute("""drop table if exists pyramid_perf""")
            
        c.execute("""create table if not exists pyramid_perf ( id integer PRIMARY KEY,
                                                               id_measure integer,
                                                               url text,
                                                               route_name text,
                                                               view_name text,
                                                               measure real,
                                                               sql_avg_duration real,
                                                               sql_nb_queries integer,
                                                               date_measure datetime )""")

        c.execute("""create index if not exists idx_url on pyramid_perf (url)""")
        c.execute("""create index if not exists idx_route on pyramid_perf (route_name)""")
        c.execute("""create index if not exists idx_view on pyramid_perf (view_name)""")

        if from_scratch :
            c.execute("""drop table if exists pyramid_agg_route_perf""")
            
        c.execute("""create table if not exists pyramid_agg_route_perf ( id integer PRIMARY KEY,
                                                               id_measure integer,
                                                               route_name text,
                                                               view_name text,
                                                               measure real,
                                                               sql_avg_duration real,
                                                               sql_nb_queries integer,
                                                               fetch_count integer )""")

        c.execute("""create index if not exists idx_url on pyramid_agg_route_perf (url)""")
        c.execute("""create index if not exists idx_route on pyramid_agg_route_perf (route_name)""")
        c.execute("""create index if not exists idx_view on pyramid_agg_route_perf (view_name)""")

        if from_scratch :
            c.execute("""drop table if exists pyramid_agg_view_perf""")
            
        c.execute("""create table if not exists pyramid_agg_view_perf ( id integer PRIMARY KEY,
                                                               id_measure integer,
                                                               view_name text,
                                                               measure real,
                                                               sql_avg_duration real,
                                                               sql_nb_queries integer,
                                                               fetch_count integer )""")
        c.execute("""create index if not exists idx_view on pyramid_agg_view_perf (view_name)""")

        self.conn.commit()
        self.end_connection()

    def get_session_liste(self):
        """
        return sessions liste
        """
        c = self.conn.cursor()
        c.execute("""select m.id, m.date_measure, count(p.id) 
                    from pyramid_measure m
                        left join pyramid_agg_view_perf p on p.id_measure=m.id
                group BY m.id order by m.id asc""")
        rows = c.fetchall()
        c.close()
        return rows

    def get_urls_measure_liste(self, id_measure, id_route):
        """
        return resume measure liste
        """
        c = self.conn.cursor()
        c.execute("""select pr.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries, pr.url from pyramid_perf pr
                     join pyramid_agg_route_perf rp on rp.id=%d and pr.route_name=rp.route_name
                     where rp.id_measure=%d and pr.id_measure=%d"""%(id_route, id_measure, id_measure))
        rows = c.fetchall()
        c.close()
        return rows 

    def get_route_measure_summary_liste(self, id_measure, id_view):
        """
        return measure liste
        :param id_measure: the session id
        :param id_view: an view recorded id
        """
        c = self.conn.cursor()
        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries,
                            pr.route_name, pr.view_name, pr.fetch_count
                     from pyramid_agg_route_perf pr
                     join pyramid_agg_view_perf pv on pv.id=%d and pr.view_name=pv.view_name
                     where pr.id_measure=%d order by pr.measure desc, pr.fetch_count desc"""%(id_view,id_measure))
        rows = c.fetchall()
        c.close()
        return rows 

    def get_view_measure_summary_liste(self, id_measure):
        """
        return measure liste
        :param id_measure: the session id
        """
        c = self.conn.cursor()
        c.execute("""select id, measure, sql_avg_duration, sql_nb_queries, view_name, fetch_count 
                     from pyramid_agg_view_perf 
                     where id_measure=%d
                     order by measure desc, fetch_count desc, view_name desc"""%id_measure)
        rows = c.fetchall()
        c.close()
        return rows 

    def end_connection(self):
        """
        close the connection
        """
        self.conn.close()

    def get_last_route_mean_perf(self):
        """
          return last values inserted in pyramid_agg_route_perf
        """
        c = self.conn.cursor()
        c.execute("""select measure, sql_avg_duration, sql_nb_queries, fetch_count from pyramid_agg_route_perf 
                    where id_measure=%d and route_name='%s'"""%(self.session_perf_id, self.matched_route_name))
        rows = c.fetchall()
        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
        if rows :
           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
        c.close()
        return measure, sql_avg_duration, sql_nb_queries, fetch_count

    def get_last_view_mean_perf(self):
        """
          return last value inserted in pyramid_agg_route_perf
        """
        c = self.conn.cursor()
        c.execute("""select measure, sql_avg_duration, sql_nb_queries, fetch_count from pyramid_agg_view_perf 
                    where id_measure=%d and view_name='%s'"""%(self.session_perf_id, self.view_name))
        rows = c.fetchall()
        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
        if rows :
           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
        c.close()
        return measure, sql_avg_duration, sql_nb_queries, fetch_count

    def update_mean_route(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
        """
        """
        last_record_resume, last_sql_avg_duration, last_sql_nb_queries, last_cpt = self.get_last_route_mean_perf()
        new_record_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
        
        new_sql_sigma_duration = 0.0
        new_sql_nb_queries = 0
        if sql_sigma_duration is not None :
            new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
            new_sql_nb_queries = int(round(self.cumulative_upkeep(last_sql_nb_queries, last_cpt, sql_nb_queries)))
        if last_record_resume is None :
            self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
        else :
            self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries)

    def update_mean_route_perf_value(self, resume_perf, sql_resume_perf, sql_nb_queries, insert=False):
        """
        :resume_perf: the average to request time duration
        :sql_resume_perf: the average to sql time duration
        :sql_nb_queries: the number of queries occured during view call
        :key-param insert: this key-word indicate we should insert or update
        """
        c = self.conn.cursor()
        if insert :
            c.execute("""insert into pyramid_agg_route_perf values 
                     (null, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                                      self.matched_route_name,
                                                      self.view_name,
                                                      resume_perf,
                                                      sql_resume_perf,
                                                      sql_nb_queries))
        else :
            c.execute("""update pyramid_agg_route_perf
             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
                     where id_measure=%d and route_name='%s'"""%(resume_perf,
                                                   sql_resume_perf,
                                                   sql_nb_queries,
                                                   self.session_perf_id,
                                                   self.matched_route_name))
        c.close()

    def update_mean_view(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
        """
        update the average time table for view resume
        
        :param request_duration_time: 
        :param sql_sigma_duration:
        :param sql_nb_queries:
        """
        last_record_resume, last_sql_avg_duration, last_sql_nb_queries, last_cpt = self.get_last_view_mean_perf()
        new_avg_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
        new_sql_sigma_duration = 0.0
        new_sql_nb_queries = 0
        
        if sql_sigma_duration is not None :
            new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
            new_sql_nb_queries = int(round(self.cumulative_upkeep(last_sql_nb_queries, last_cpt, sql_nb_queries)))
        
        if last_record_resume is None :
            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
        else :
            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries)
        

    def update_mean_view_perf_value(self, request_time_avg, sql_resume_perf, sql_nb_queries, insert=False):
        """
           update every recorded params linked to the current view
           :request_time_avg:
           :sql_resume_perf:
           :sql_nb_queries:
           :insert: boolean
        """
        c = self.conn.cursor()
        if insert :
            c.execute("""insert into pyramid_agg_view_perf values 
                     (null, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                                   self.view_name,
                                                   request_time_avg,
                                                   sql_resume_perf,
                                                   sql_nb_queries))
        else :
            c.execute("""update pyramid_agg_view_perf
             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
                     where id_measure=%d and view_name='%s'"""%(request_time_avg,
                                                                sql_resume_perf,
                                                                sql_nb_queries,
                                                                self.session_perf_id,
                                                                self.view_name))
        c.close()
    
    def cumulative_upkeep(self, last_resume, last_cpt, perf):
        """
        :param last_resume: the last recorded average
        :param last_cpt: last average number of elements having served for the calculation
        :param perf: new record
        """
        result = None
        if last_resume is None :
           result = perf
        else :
           result = (last_resume+(perf/float(last_cpt)))*(float(last_cpt)/float(last_cpt+1))
        return result
    
    def get_queries_information(self):
        """
        if sql alchemy is know. we can return sql query sigma time execution
        :param return :
              sql_sigma_duration : sum duration of every sql request executed during the request
              sql_nb_queries : the number of queries executed in the request
        """
        sql_sigma_duration, sql_nb_queries = None, None
        
        if self.request is not None and has_sqla and hasattr(self.request,'pfstb_duration_queries') :
              duration_queries_liste = self.request.pfstb_duration_queries
              # sum duration of every sql request executed during the request
              sql_sigma_duration = sum(duration_queries_liste)
              # the number of queries executed in the request
              sql_nb_queries = len(duration_queries_liste)

        return sql_sigma_duration, sql_nb_queries
    
    def insert_data(self, request_duration_time):
        """
        insert all recorded data in db
        :param request_duration_time: the request time duration
        """
        
        sql_sigma_duration, sql_nb_queries = self.get_queries_information()

        # we forget pyramid_perfstat self routes
        if self.matched_route_name is not None and self.matched_url.count(ROUTE_PREFIX) == 0 :
             c = self.conn.cursor()
             c.execute("""insert into pyramid_perf values 
                          (null, ?, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
                                                             self.matched_url,
                                                             self.matched_route_name,
                                                             self.view_name,
                                                             request_duration_time,
                                                             sql_sigma_duration,
                                                             sql_nb_queries,
                                                             datetime.datetime.now(),))
     
             # update specific view table
             self.update_mean_view(request_duration_time, sql_sigma_duration, sql_nb_queries)
             
             # update specific route table 
             self.update_mean_route(request_duration_time, sql_sigma_duration, sql_nb_queries)
             
             self.conn.commit()
             c.close()

        self.end_connection()
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.