Commits

tuck  committed b058c58

some flush point

  • Participants
  • Parent commits 4c676a4
  • Branches 0.3

Comments (0)

Files changed (4)

 it adds a very unique exposure of youre code. Pyramid perfstat try to record each request and its execution time and some other stuff
 (average queries count per request etc ...). Thus it can display averages by view or route. This gives you an overview of your
 application in order to quickly determine its points of improvement.
-
+PRAGMA synchronous=OFF
 For the moment it's only tested with pyramid route dispatch scaffold.
 
 Please feel free to 
 
 http://localhost:6543/__perfstat/stat
 
+
+Performance :
+=============
+
+sqlite and bottleneck
+
+some few advices :
+    - use memory storage if hard disk storage is not you're goal
+
+further more 
+    - use PRAGMA synchronous=OFF

File pyramid_perfstat/__init__.py

 # New field:   ----------
 #-----------------------------------------------------------------------------
 
-
 import logging
 import sys
 from pprint import pformat
 import time
 import datetime
 
-from pyramid_perfstat.utils import STATIC_PATH, ROUTE_PREFIX
-from pyramid_perfstat.perf_serializer import PerfDbManager
+from .utils import STATIC_PATH, ROUTE_PREFIX
+from .perf_serializer import PerfDbManager
+
 
 def to_int(*segment_names):
     def predicate(info, request):
         return True
     return predicate
 
-
 def perf_tween_factory(handler, registry):
     """
     """
-    PerfDbManager(None).init_db()
     
     def perf_tween(request, getLogger=logging.getLogger):
         """
     # scan views
     config.scan('pyramid_perfstat.views')
     config.scan('pyramid_perfstat.perf_serializer')
-

File pyramid_perfstat/perf_serializer.py

 # 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 datetime
-import sqlite3
+import threading
 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 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, "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):
+@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):
     """
-    open the connection to sqlite db
-    subscribe to event NewRequest
+    fonction exécutée lors de la création de l'application
     """
-    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()
-
+    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
     
-    id_session = None
+    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
+#        # init behavior ?
+#        if request is None :
+#            self.conn = sqlite3.connect('perf_app.db')
+#        else :
+#            self.conn = request.__perfstat_db
         
         self.request = request
 
                                                                url text,
                                                                route_name text,
                                                                view_name text,
-                                                               measure real,
-                                                               sql_avg_duration real,
-                                                               sql_nb_queries integer,
+                                                               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)""")
                                                                id_measure integer,
                                                                route_name text,
                                                                view_name text,
-                                                               measure real,
-                                                               sql_avg_duration real,
-                                                               sql_nb_queries integer,
-                                                               fetch_count integer )""")
+                                                               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 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 )""")
+                                                               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_liste(self):
+    def get_session_list(self):
         """
-        return sessions liste
+        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""")
+                     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):
+    def get_urls_measure_list(self, id_measure, id_route):
         """
-        return resume measure liste
+        return resume measure list
         """
         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
+        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 rows 
+        return ((row[0], SqlRecordAvg(row[1:11]), row[11]) for row in rows)
 
-    def get_route_measure_summary_liste(self, id_measure, id_view):
+    def get_route_measure_summary_list(self, id_measure, id_view):
         """
-        return measure liste
+        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.measure, pr.sql_avg_duration, pr.sql_nb_queries,
-                            pr.route_name, pr.view_name, pr.fetch_count
+        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 rows 
+        return ((row[0], row[1], SqlRecordAvg(row[2:12]), row[12], row[13], row[14]) for row in rows)
 
-    def get_view_measure_summary_liste(self, id_measure):
+    def get_view_measure_summary_list(self, id_measure):
         """
-        return measure liste
+        return measure list
         :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 
+        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 rows 
+        return ((row[0], SqlRecordAvg(row[1:11]), row[11], row[12]) for row in rows)
 
     def end_connection(self):
         """
         """
         self.conn.close()
 
-    def get_last_route_mean_perf(self):
+    def get_last_route_avg_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 
+        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()
-        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]
+        if rows : 
+           rows = ((SqlRecordAvg(row[0:10]), row[10]) for row in c.fetchall())
+        else :
+           rows = ((SqlRecordAvg((None,)*10), None),)
         c.close()
-        return measure, sql_avg_duration, sql_nb_queries, fetch_count
+        return rows[0]
 
-    def get_last_view_mean_perf(self):
+    def get_last_view_avg_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 
+        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()
-        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]
+        if rows : 
+           rows = ((SqlRecordAvg(row[0:10]), row[10]) for row in c.fetchall())
+        else :
+           rows = ((SqlRecordAvg((None,)*10), None),)
         c.close()
-        return measure, sql_avg_duration, sql_nb_queries, fetch_count
+        return rows[0]
 
     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)
+        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_mean_route_perf_value(self, resume_perf, sql_resume_perf, sql_nb_queries, insert=False):
+    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
         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))
+                     (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'"""%(resume_perf,
-                                                   sql_resume_perf,
-                                                   sql_nb_queries,
+             where id_measure=%d and route_name='%s'"""%(
+
                                                    self.session_perf_id,
-                                                   self.matched_route_name))
+
+                                                   self.matched_route_name
+            ))
         c.close()
 
-    def update_mean_view(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
+    def update_avg_view(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
         """
         update the average time table for view resume
         
         :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)
-        
+        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_mean_view_perf_value(self, request_time_avg, sql_resume_perf, sql_nb_queries, insert=False):
+    def update_avg_view_perf_value(self, updated_record, insert=False):
         """
            update every recorded params linked to the current view
            :request_time_avg:
         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))
+                     (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 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
         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
+              duration_queries_list = self.request.pfstb_duration_queries
               # sum duration of every sql request executed during the request
-              sql_sigma_duration = sum(duration_queries_liste)
+              sql_sigma_duration = sum(duration_queries_list)
               # the number of queries executed in the request
-              sql_nb_queries = len(duration_queries_liste)
+              sql_nb_queries = len(duration_queries_list)
 
         return 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)
+             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)
              c.close()
 
         self.end_connection()
+
+
+def main():
+    """
+    """
+    print SqlRecordAvg((1,2,3,4,5,6,7,8,9,2,2))
+    
+
+if __name__=="__main__":
+   main()
+Road map ?
+
+- pyramid_perfstat refactoring
+    - using sqlalchemy ?
+    - storm ?
+    - actually pyramid_perfstat is just as a project idea
+      so data structure is not fixed and for the moment
+      it's really a mess
+
+- pyramid_perfstat and performance
+    - allow end user to choose an sqlite memory backend
+    - allow end user to specify PRAGMA synchronous=OFF
+    - add other backend (memcache)
+    - 
+