tuck avatar tuck committed 9ba573c

add sql average

Comments (0)

Files changed (4)

pyramid_perfstat/perf_serializer.py

 
 import logging
 import datetime
+import sqlite3
+import time
+import threading
 
 from zope.interface import Interface
 
 from pyramid.interfaces import IRoutesMapper
 from pyramid.interfaces import IViewClassifier
 from pyramid.interfaces import IView
+from pyramid.events import NewRequest
 
-import sqlite3
+from pyramid.threadlocal import get_current_request
+from pyramid_perfstat.utils import ROUTE_PREFIX
 
-from pyramid.events import NewRequest
-from pyramid_perfstat.utils import ROUTE_PREFIX
+lock = threading.Lock()
 
 try:
     from sqlalchemy import event
     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,
-
+        duration = stop_timer - conn.pfstb_start_timer
         if request is not None:
             with lock:
                 duration_queries = getattr(request, 'pfstb_duration_queries', [])
         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
                                                                route_name text,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
                                                                date_measure datetime )""")
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_route_perf""")
                                                                route_name text,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
                                                                fetch_count integer )""")
 
         if from_scratch :
                                                                id_measure integer,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
                                                                fetch_count integer )""")
         
         self.conn.commit()
         return resume measure liste
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pr.measure, pr.url from pyramid_perf pr
+        c.execute("""select pr.id, pr.measure, pr.sql_avg_duration, 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()
         :param id_view: an view recorded id
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pv.id, pr.measure, pr.url, pr.route_name, pr.view_name, pr.fetch_count
+        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.url,
+                            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, pr.url desc"""%(id_view,id_measure))
         :param id_measure: the session id
         """
         c = self.conn.cursor()
-        c.execute("""select id, measure, view_name, fetch_count from pyramid_agg_view_perf 
+        c.execute("""select id, measure, sql_avg_duration, 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()
 
     def get_last_route_mean_perf(self):
         """
-          return last value inserted in pyramid_agg_route_perf
+          return last values inserted in pyramid_agg_route_perf
         """
         c = self.conn.cursor()
-        c.execute("""select measure, fetch_count from pyramid_agg_route_perf 
+        c.execute("""select measure, sql_avg_duration, fetch_count from pyramid_agg_route_perf 
                     where id_measure=%d and url='%s'"""%(self.session_perf_id, self.matched_url))
         rows = c.fetchall()
-        measure, fetch_count = None, None
+        measure, sql_avg_duration, fetch_count = None, None, None
         if rows :
-           measure, fetch_count = rows[0]
+           measure, sql_avg_duration, fetch_count = rows[0]
         c.close()
-        return measure, fetch_count
+        return measure, sql_avg_duration, 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, fetch_count from pyramid_agg_view_perf 
+        c.execute("""select measure, sql_avg_duration, 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, fetch_count = None, None
+        measure, sql_avg_duration, fetch_count = None, None, None
         if rows :
-           measure, fetch_count = rows[0]
+           measure, sql_avg_duration, fetch_count = rows[0]
         c.close()
-        return measure, fetch_count
+        return measure, sql_avg_duration, fetch_count
 
-    def update_mean_route_perf_value(self, resume_perf, insert=False):
+    def update_mean_route_perf_value(self, resume_perf, sql_resume_perf, insert=False):
         """
             return last value inserted in pyramid_agg_route_perf
+            :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,
+                     (null, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                                   self.matched_url,
                                                   self.matched_route_name,
                                                   self.view_name,
-                                                  resume_perf,))
+                                                  resume_perf,
+                                                  sql_resume_perf))
         else :
             c.execute("""update pyramid_agg_route_perf
-             set measure=%f, fetch_count=fetch_count+1 
+             set measure=%f, sql_avg_duration=%f, fetch_count=fetch_count+1 
                      where id_measure=%d and url='%s'"""%(resume_perf,
+                                                   sql_resume_perf,
                                                    self.session_perf_id,
                                                    self.matched_url))
         c.close()
 
-    def update_mean_view_perf_value(self, resume_perf, insert=False):
+    def update_mean_view_perf_value(self, resume_perf, sql_resume_perf, insert=False):
         """
         """
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_view_perf values 
-                     (null, ?, ?, ?, 1)""", (self.session_perf_id,
+                     (null, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                             self.view_name,
-                                            resume_perf,))
+                                            resume_perf,
+                                            sql_resume_perf))
         else :
             c.execute("""update pyramid_agg_view_perf
-             set measure=%f, fetch_count=fetch_count+1 
+             set measure=%f, sql_avg_duration=%f, fetch_count=fetch_count+1 
                      where id_measure=%d and view_name='%s'"""%(resume_perf,
+                                                   sql_resume_perf,
                                                    self.session_perf_id,
                                                    self.view_name))         
         c.close()
         :param pef: the time measure (should be ms)
         """
 
-        if has_sqla and hasattr(self.request,'pfstb_duration_queries') :
+        if self.request is not None and has_sqla and hasattr(self.request,'pfstb_duration_queries') :
               duration_queries_liste = self.request.pfstb_duration_queries
-              sigma_duration = sum(duration_queries_liste)
+              sql_sigma_duration = sum(duration_queries_liste)
         else :
-              sigma_duration = None
+              sql_sigma_duration = None
 
         # 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,
+                          (null, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
                                                         self.matched_url,
                                                         self.matched_route_name,
                                                         self.view_name,
                                                         perf,
+                                                        sql_sigma_duration,
                                                         datetime.datetime.now(),))
      
-             last_record_resume, last_cpt = self.get_last_route_mean_perf()
+             last_record_resume, last_sql_avg_duration, last_cpt = self.get_last_route_mean_perf()
              new_record_resume = self.cumulative_upkeep(last_record_resume, last_cpt, perf)
+
+             new_sql_sigma_duration = 0.0
+             if sql_sigma_duration is not None :
+                new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
+
              if last_record_resume is None :
-                self.update_mean_route_perf_value(new_record_resume, insert=True)
+                self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration, insert=True)
              else :
-                self.update_mean_route_perf_value(new_record_resume)
+                self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration)
      
-             last_record_resume, last_cpt = self.get_last_view_mean_perf()
+             last_record_resume, last_sql_avg_duration, last_cpt = self.get_last_view_mean_perf()
              new_record_resume = self.cumulative_upkeep(last_record_resume, last_cpt, perf)
+
+             new_sql_sigma_duration = 0.0
+             if sql_sigma_duration is not None :
+                new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
+
              if last_record_resume is None :
-                self.update_mean_view_perf_value(new_record_resume, insert=True)
+                self.update_mean_view_perf_value(new_record_resume, new_sql_sigma_duration, insert=True)
              else :
-                self.update_mean_view_perf_value(new_record_resume)
+                self.update_mean_view_perf_value(new_record_resume, new_sql_sigma_duration)
      
              self.conn.commit()
              c.close()

pyramid_perfstat/templates/reporting.mako

 	% endif
 </%def>
 
-<%def name="publish_url_row(id, avg_time, url)">
+<%def name="publish_url_row(id, avg_time, sql_avg_time, url)">
 	<tr>
 		<td>${id}</td>
 		<td>${get_color_perf(avg_time)}</td>
 		<td>${get_label_perf(avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time > 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
 		<td>${url}</td>
 	</tr>
 </%def>
 
-<%def name="publish_agg_routes_row(id, id_view, avg_time, url, route_name, view_name, cpt)">
+<%def name="publish_agg_routes_row(id, id_view, avg_time, sql_avg_time, url, route_name, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
 		<td>${get_color_perf(avg_time)}</td>
 		<td>${get_label_perf(avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time > 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
 		<td>${url}</td>
 		<td>
 				  <a href="${request.route_url("pyramid_perfstat.reporting.url_detail", id_session=id_session, id_view=id_view, id_route=id)}">${route_name}</a>
 	</tr>
 </%def>
 
-<%def name="publish_agg_view_row(id, avg_time, view_name, cpt)">
+<%def name="publish_agg_view_row(id, avg_time, sql_avg_time, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
 		<td>${get_color_perf(avg_time)}</td>
 		<td>${get_label_perf(avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time > 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
 		<td><a href="${request.route_url("pyramid_perfstat.reporting.view_detail",id_session=id_session, id_view=id)}">${view_name}</a></td>
 		<td>${cpt}</td>
 	</tr>
 	min-width: 50px;
 }
 
-#perf_views_reporting_table  tbody td:nth-child(5),
-#perf_routes_reporting_table tbody td:nth-child(7) {
+#perf_views_reporting_table  tbody td:nth-child(4),
+#perf_routes_reporting_table tbody td:nth-child(4),
+#perf_urls_reporting_table   tbody td:nth-child(4) {
+	width: 5%;
+	min-width: 50px;
+}
+
+#perf_views_reporting_table  tbody td:nth-child(6),
+#perf_routes_reporting_table tbody td:nth-child(8) {
 	width: 5%;
 	min-width: 50px;
 }
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
+					<th><b>sql</b> avg&#160;time&#160;(ms)</th>
 					<th>matched view name</th>
 					<th>view count</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, avg_time, view_name, cpt) in lst_agg_views_measures :
-					${publish_agg_view_row(id, avg_time, view_name, cpt)}
+				% for (id, avg_time, sql_avg_time, view_name, cpt) in lst_agg_views_measures :
+					${publish_agg_view_row(id, avg_time, sql_avg_time, view_name, cpt)}
 				% endfor
 			</tbody>
 		</table>
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
+					<th><b>sql</b> avg&#160;time&#160;(ms)</th>
 					<th>requested url</th>
 					<th>matched route name</th>
 					<th>matched view name</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, id_view, avg_time, url, route_name, view_name, cpt) in lst_agg_routes_measures :
-					${publish_agg_routes_row(id, id_view, avg_time, url, route_name, view_name, cpt)}
+				% for (id, id_view, avg_time, sql_avg_time, url, route_name, view_name, cpt) in lst_agg_routes_measures :
+					${publish_agg_routes_row(id, id_view, avg_time, sql_avg_time, url, route_name, view_name, cpt)}
 				% endfor
 			</tbody>
 		</table>
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
+					<th><b>sql</b> avg&#160;time&#160;(ms)</th>
 					<th>requested url</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, avg_time, url) in lst_urls_measures :
-					${publish_url_row(id, avg_time, url)}
+				% for (id, avg_time, sql_avg_time, url) in lst_urls_measures :
+					${publish_url_row(id, avg_time, sql_avg_time, url)}
 				% endfor
 			</tbody>
 		</table>

pyramid_perfstat/views.py

 from pyramid_perfstat import utils
 from pyramid.security import NO_PERMISSION_REQUIRED
 
-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()
-        if request is not None:
-            with lock:
-                duration = stop_timer - conn.pfstb_start_timer,
-                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
-
 @view_config(route_name='pyramid_perfstat.reporting',
              renderer='pyramid_perfstat:templates/reporting.mako',
              permission=NO_PERMISSION_REQUIRED)
 
 setup(name='pyramid_perfstat',
       license='BSD License',
-      version='0.1.1',
+      version='0.2.0',
       description='Pyramid PerfStat logs and reports statistics about time usage of a pyramid webapp.',
       long_description=long_description,
       author='Bard Stéphane',
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.