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
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
#!/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:   ----------
#-----------------------------------------------------------------------------

from .schemas.models import SqlRecordAvg, initialize_sql
from .utils import ROUTE_PREFIX
from pyramid.events import NewRequest, subscriber, ApplicationCreated
from pyramid.interfaces import IRouteRequest, IRoutesMapper, IView, \
    IViewClassifier
from pyramid.threadlocal import get_current_request
from sqlalchemy import event
from sqlalchemy.engine import engine_from_config
from sqlalchemy.engine.base import Engine
from zope.interface import Interface
import datetime
import logging
import threading
import time

lock = threading.Lock()

@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')
 
@subscriber(ApplicationCreated)
def init_perfstat(event):
    """
    fonction exécutée lors de la création de l'application
    """
    settings = event.app.registry.settings
    engine = engine_from_config(settings, 'sqlalchemy_perfstat.')
    initialize_sql(engine)
 
class PerfDbManager(object):
    """
    sqlite hand serializer
    """
    # unique id identifier each time pyramid web app
    # reload
    
    db_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)
                view_callable = ".".join((view_callable.__module__,view_callable.__name__))

            self.view_name = view_callable

    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,
                                                               duration_url_avg real,
                                                               duration_url_min real,
                                                               duration_url_max real,
                                                               url_fetch_count integer,
                                                               sql_duration_avg real,
                                                               sql_duration_min real,
                                                               sql_duration_max real,
                                                               sql_nb_queries_avg integer,
                                                               sql_nb_queries_min integer,
                                                               sql_nb_queries_max 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,
                                                               duration_route_avg real,
                                                               duration_route_min real,
                                                               duration_route_max real,
                                                               route_fetch_count integer,
                                                               sql_duration_avg real,
                                                               sql_duration_min real,
                                                               sql_duration_max real,
                                                               sql_nb_queries_avg integer,
                                                               sql_nb_queries_min integer,
                                                               sql_nb_queries_max 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,
                                                               duration_view_avg real,
                                                               duration_view_min real,
                                                               duration_view_max real,
                                                               view_fetch_count integer,
                                                               sql_duration_avg real,
                                                               sql_duration_min real,
                                                               sql_duration_max real,
                                                               sql_nb_queries_avg integer,
                                                               sql_nb_queries_min integer,
                                                               sql_nb_queries_max 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_list(self):
        """
        return sessions list
        """
        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_list(self, id_measure, id_route):
        """
        return resume measure list
        """
        c = self.conn.cursor()
        c.execute("""select pr.id,
                            pr.duration_url_avg,
                            pr.duration_url_min,
                            pr.duration_url_max,
                            pr.url_fetch_count,
                            pr.sql_duration_avg,
                            pr.sql_duration_min,
                            pr.sql_duration_max,
                            pr.sql_nb_queries_avg,
                            pr.sql_nb_queries_min,
                            pr.sql_nb_queries_max,
                            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 ((row[0], SqlRecordAvg(row[1:11]), row[11]) for row in rows)

    def get_route_measure_summary_list(self, id_measure, id_view):
        """
        return measure list
        :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.duration_route_avg,
                            pr.duration_route_min,
                            pr.duration_route_max,
                            pr.route_fetch_count,
                            pr.sql_duration_avg,
                            pr.sql_duration_min,
                            pr.sql_duration_max,
                            pr.sql_nb_queries_avg,
                            pr.sql_nb_queries_min,
                            pr.sql_nb_queries_max,
                            pr.route_name,
                            pr.view_name
                     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 ((row[0], row[1], SqlRecordAvg(row[2:12]), row[12], row[13], row[14]) for row in rows)

    def get_view_measure_summary_list(self, id_measure):
        """
        return measure list
        :param id_measure: the session id
        """
        c = self.conn.cursor()
        c.execute("""select id,
                            duration_view_avg,
                            duration_view_min,
                            duration_view_max,
                            view_fetch_count,
                            sql_duration_avg,
                            sql_duration_min,
                            sql_duration_max,
                            sql_nb_queries_avg,
                            sql_nb_queries_min,
                            sql_nb_queries_max,
                            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 ((row[0], SqlRecordAvg(row[1:11]), row[11], row[12]) for row in rows)

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

    def get_last_route_avg_perf(self):
        """
          return last values inserted in pyramid_agg_route_perf
        """
        c = self.conn.cursor()
        c.execute("""select duration_route_avg,
                            duration_route_min,
                            duration_route_max,
                            route_fetch_count,
                            sql_duration_avg,
                            sql_duration_min,
                            sql_duration_max,
                            sql_nb_queries_avg,
                            sql_nb_queries_min,
                            sql_nb_queries_max
                    from pyramid_agg_route_perf 
                    where id_measure=%d and route_name='%s'"""%(self.session_perf_id, self.matched_route_name))
        rows = c.fetchall()
        if rows : 
           rows = ((SqlRecordAvg(row[0:10]), row[10]) for row in c.fetchall())
        else :
           rows = ((SqlRecordAvg((None,)*10), None),)
        c.close()
        return rows[0]

    def get_last_view_avg_perf(self):
        """
          return last value inserted in pyramid_agg_route_perf
        """
        c = self.conn.cursor()
        c.execute("""select duration_view_avg,
                            duration_view_min,
                            duration_view_max,
                            sql_nb_queries_count,
                            sql_duration_avg,
                            sql_duration_min,
                            sql_duration_max,
                            sql_nb_queries_avg,
                            sql_nb_queries_min,
                            sql_nb_queries_max,
                            sql_nb_queries_count
                    from pyramid_agg_view_perf 
                    where id_measure=%d and view_name='%s'"""%(self.session_perf_id, self.view_name))
        rows = c.fetchall()
        if rows : 
           rows = ((SqlRecordAvg(row[0:10]), row[10]) for row in c.fetchall())
        else :
           rows = ((SqlRecordAvg((None,)*10), None),)
        c.close()
        return rows[0]

    def update_mean_route(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
        """
        """
        last_record, last_cpt = self.get_last_route_avg_perf()
        last_record.cumulative_upkeep(request_duration_time, sql_sigma_duration, sql_nb_queries)
        self.update_avg_route_perf_value(last_record, insert=last_record.init)

    def update_avg_route_perf_value(self, record, 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 ] +
               record.get_data() )
        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'"""%(

                                                   self.session_perf_id,

                                                   self.matched_route_name
            ))
        c.close()

    def update_avg_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, last_cpt = self.get_last_view_avg_perf
        last_record.cumulative_upkeep(request_duration_time, sql_sigma_duration, sql_nb_queries)
        self.update_avg_view_perf_value(last_record, insert=last_record.init)

    def update_avg_view_perf_value(self, updated_record, 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, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
                                         self.view_name,
                                         updated_record.duration_avg,
                                         updated_record.duration_min,
                                         updated_record.duration_max,
                                         updated_record.duration_cpt,
                                         updated_record.sql_duration_avg,
                                         updated_record.sql_duration_min,
                                         updated_record.sql_duration_max,
                                         updated_record.sql_nb_queries_avg,
                                         updated_record.sql_nb_queries_min,
                                         updated_record.sql_nb_queries_max))
##        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 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_list = self.request.pfstb_duration_queries
              # sum duration of every sql request executed during the request
              sql_sigma_duration = sum(duration_queries_list)
              # the number of queries executed in the request
              sql_nb_queries = len(duration_queries_list)

        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_avg_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()


def main():
    """
    """
    print SqlRecordAvg((1,2,3,4,5,6,7,8,9,2,2))
    

if __name__=="__main__":
   main()
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.