Commits

tuck committed 2a81a55

add sql number of queries per view avg

Comments (0)

Files changed (3)

pyramid_perfstat/__init__.py

 
 from pyramid_perfstat.utils import STATIC_PATH, ROUTE_PREFIX
 from pyramid_perfstat.perf_serializer import PerfDbManager
-from pyramid_perfstat.perf_serializer import new_perfstat_request_subscriber
-
 
 def to_int(*segment_names):
     def predicate(info, request):
 
         perf_manager = PerfDbManager(request)
         perf_manager.insert_data(time.time()-t0)
-        
         return result
     
     return perf_tween
     such as mean time, number of request urls etc ...
     """
     config.add_static_view(ROUTE_PREFIX+'/static', STATIC_PATH)
-    config.add_subscriber(new_perfstat_request_subscriber,NewRequest)
     
     config.add_tween('pyramid_perfstat.perf_tween_factory', under=EXCVIEW)
     config.include(include_perf_routes, route_prefix=ROUTE_PREFIX)
 
     # scan views
     config.scan('pyramid_perfstat.views')
+    config.scan('pyramid_perfstat.perf_serializer')
 

pyramid_perfstat/perf_serializer.py

 
 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.events import NewRequest
 
 from pyramid.threadlocal import get_current_request
 from pyramid_perfstat.utils import ROUTE_PREFIX
+from sqlalchemy.ext.sqlsoup import SqlSoup
 
 lock = threading.Lock()
 
 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):
                                                                view_name text,
                                                                measure real,
                                                                sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                date_measure datetime )""")
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_route_perf""")
                                                                view_name text,
                                                                measure real,
                                                                sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                fetch_count integer )""")
 
         if from_scratch :
                                                                view_name text,
                                                                measure real,
                                                                sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                fetch_count integer )""")
-        
         self.conn.commit()
         self.end_connection()
 
         return resume measure liste
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pr.measure, pr.sql_avg_duration, pr.url from pyramid_perf pr
+        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()
         :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.url,
+        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries, 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
         :param id_measure: the session id
         """
         c = self.conn.cursor()
-        c.execute("""select id, measure, sql_avg_duration, view_name, fetch_count from pyramid_agg_view_perf 
+        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 last values inserted in pyramid_agg_route_perf
         """
         c = self.conn.cursor()
-        c.execute("""select measure, sql_avg_duration, fetch_count from pyramid_agg_route_perf 
+        c.execute("""select measure, sql_avg_duration, sql_nb_queries, 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, sql_avg_duration, fetch_count = None, None, None
+        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
         if rows :
-           measure, sql_avg_duration, fetch_count = rows[0]
+           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
         c.close()
-        return measure, sql_avg_duration, fetch_count
+        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, fetch_count from pyramid_agg_view_perf 
+        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, fetch_count = None, None, None
+        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
         if rows :
-           measure, sql_avg_duration, fetch_count = rows[0]
+           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
         c.close()
-        return measure, sql_avg_duration, fetch_count
+        return measure, sql_avg_duration, sql_nb_queries, fetch_count
 
-    def update_mean_route_perf_value(self, resume_perf, sql_resume_perf, insert=False):
+    def update_mean_route(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
         """
-            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
+        """
+        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(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,
+                     (null, ?, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                                   self.matched_url,
                                                   self.matched_route_name,
                                                   self.view_name,
                                                   resume_perf,
-                                                  sql_resume_perf))
+                                                  sql_resume_perf,
+                                                  sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_route_perf
-             set measure=%f, sql_avg_duration=%f, fetch_count=fetch_count+1 
+             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
                      where id_measure=%d and url='%s'"""%(resume_perf,
                                                    sql_resume_perf,
+                                                   sql_nb_queries,
                                                    self.session_perf_id,
                                                    self.matched_url))
         c.close()
 
-    def update_mean_view_perf_value(self, resume_perf, sql_resume_perf, insert=False):
+    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_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(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_record_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
+        else :
+            self.update_mean_view_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries)
+        
+
+    def update_mean_view_perf_value(self, resume_perf, sql_resume_perf, sql_nb_queries, insert=False):
         """
         """
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_view_perf values 
-                     (null, ?, ?, ?, ?, 1)""", (self.session_perf_id,
-                                            self.view_name,
-                                            resume_perf,
-                                            sql_resume_perf))
+                     (null, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
+                                                   self.view_name,
+                                                   resume_perf,
+                                                   sql_resume_perf,
+                                                   sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_view_perf
-             set measure=%f, sql_avg_duration=%f, fetch_count=fetch_count+1 
+             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
                      where id_measure=%d and view_name='%s'"""%(resume_perf,
                                                    sql_resume_perf,
+                                                   sql_nb_queries,
                                                    self.session_perf_id,
                                                    self.view_name))         
         c.close()
            result = (last_resume+(perf/float(last_cpt)))*(float(last_cpt)/float(last_cpt+1))
         return result
     
-    def insert_data(self, perf):
+    def get_queries_information(self):
         """
-        insert the perf data in db
-        :param matched_url: the application pyramid url
-        :param matched_route_name: the application pyramid matched route name
-               if url dispatched si used and request.matched_route is fill
-        :param pef: the time measure (should be ms)
+        if sql alchemy is know. we can return sql query sigma time execution
+        return sql_sigma_duration, sql_nb_queries
         """
-
+        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
               sql_sigma_duration = sum(duration_queries_liste)
-        else :
-              sql_sigma_duration = None
+              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,
+                          (null, ?, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
                                                         self.matched_url,
                                                         self.matched_route_name,
                                                         self.view_name,
-                                                        perf,
+                                                        request_duration_time,
                                                         sql_sigma_duration,
+                                                        sql_nb_queries,
                                                         datetime.datetime.now(),))
      
-             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)
+             self.update_mean_view(request_duration_time, sql_sigma_duration, sql_nb_queries)
+     
 
-             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, new_sql_sigma_duration, insert=True)
-             else :
-                self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration)
-     
-             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, new_sql_sigma_duration, insert=True)
-             else :
-                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, sql_avg_time, url)">
+<%def name="publish_url_row(id, request_avg_time, sql_avg_time, sql_nb_queries, url)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_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>${sql_nb_queries}</td>
 		<td>${url}</td>
 	</tr>
 </%def>
 
-<%def name="publish_agg_routes_row(id, id_view, avg_time, sql_avg_time, url, route_name, view_name, cpt)">
+<%def name="publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, url, route_name, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_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>${sql_nb_queries}</td>
 		<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, sql_avg_time, view_name, cpt)">
+<%def name="publish_agg_view_row(id, request_avg_time, sql_avg_time, sql_nb_queries, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_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>${sql_nb_queries}</td>
 		<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>
 #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) {
+	text-align: right;
 	width: 5%;
-	min-width: 50px;
+	min-width: 30px;
 }
 
-#perf_views_reporting_table  tbody td:nth-child(6),
-#perf_routes_reporting_table tbody td:nth-child(8) {
+#perf_views_reporting_table  tbody td:nth-child(5),
+#perf_routes_reporting_table tbody td:nth-child(5),
+#perf_urls_reporting_table   tbody td:nth-child(5) {
+	text-align: right;
+	width: 5%;
+	min-width: 30px;
+}
+
+#perf_views_reporting_table  tbody td:nth-child(7),
+#perf_routes_reporting_table tbody td:nth-child(9) {
 	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><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>matched view name</th>
 					<th>view count</th>
 				</tr>
 			</thead>
 			<tbody>
-				% 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)}
+				% for (id, request_avg_time, sql_avg_time, sql_nb_queries, view_name, cpt) in lst_agg_views_measures :
+					${publish_agg_view_row(id, request_avg_time, sql_avg_time, sql_nb_queries, 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><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>requested url</th>
 					<th>matched route name</th>
 					<th>matched view name</th>
 				</tr>
 			</thead>
 			<tbody>
-				% 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)}
+				% for (id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, url, route_name, view_name, cpt) in lst_agg_routes_measures :
+					${publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, 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><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>requested url</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, avg_time, sql_avg_time, url) in lst_urls_measures :
-					${publish_url_row(id, avg_time, sql_avg_time, url)}
+				% for (id, request_avg_time, sql_avg_time, sql_nb_queries, url) in lst_urls_measures :
+					${publish_url_row(id, request_avg_time, sql_avg_time, sql_nb_queries, url)}
 				% endfor
 			</tbody>
 		</table>