[MySQL Patch]为mysql增加响应时间status值

转载请署名： 印风

----------------

实现的思路很简单，借助了percona server5.5的information_schema表：query_response_time

过程

1.增加变量，记录上次查询rt时，统计的query数，以及总时间(last_count, last_total)

2.采集sql执行时间时(collect函数)，累加当前query数，执行总时间(cur_count, cur_total)

3.计算一段时间内rt： (cur_total - last_total)/(cur_count - last_count)

4.设置last_total = cur_total， last_count =cur_count;

5.循环到2)

增加一个新status值，命名有点挫...

root@(none) 02:17:21>show status like 'rt_from_last_query%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| rt_from_last_query | 192   |
+--------------------+-------+
1 row in set (0.00 sec)

注意这个patch理论上会有额外的开销，因为多计算了两个值(cur_count，cur_total)。

以下是patch，基于percona 5.5.18

Index: sql/mysqld.cc
===================================================================
--- sql/mysqld.cc	(revision 1006)
+++ sql/mysqld.cc	(working copy)
@@ -6307,6 +6307,17 @@
   return 0;
 }
 
+#ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
+static int show_rt(THD *thd, SHOW_VAR *var, char *buff)
+{
+   var->type = SHOW_LONGLONG;
+   ulonglong rt = query_response_time_rt();
+   var->value = buff;
+   *((long long *)buff)= (long long)rt;
+   return 0;
+}
+#endif
+
 #ifdef ENABLED_PROFILING
 static int show_flushstatustime(THD *thd, SHOW_VAR *var, char *buff)
 {
@@ -6811,6 +6822,9 @@
 #ifdef ENABLED_PROFILING
   {"Uptime_since_flush_status",(char*) &show_flushstatustime,   SHOW_FUNC},
 #endif
+#ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
+  {"rt_from_last_query",       (char*) &show_rt, SHOW_FUNC},
+#endif
   {NullS, NullS, SHOW_LONG}
 };
 
Index: sql/query_response_time.h
===================================================================
--- sql/query_response_time.h	(revision 1006)
+++ sql/query_response_time.h	(working copy)
@@ -59,6 +59,7 @@
 extern void query_response_time_flush  ();
 extern void query_response_time_collect(ulonglong query_time);
 extern int  query_response_time_fill   (THD* thd, TABLE_LIST *tables, COND *cond);
+extern ulonglong query_response_time_rt();
 #endif // HAVE_RESPONSE_TIME_DISTRIBUTION
 
 #endif // QUERY_RESPONSE_TIME_H
Index: sql/query_response_time.cc
===================================================================
--- sql/query_response_time.cc	(revision 1006)
+++ sql/query_response_time.cc	(working copy)
@@ -170,16 +170,44 @@
     my_atomic_rwlock_rdunlock(&time_collector_lock);
     return result;
   }
+  uint64 rt()
+  {
+      my_atomic_rwlock_rdlock(&time_collector_lock);
+      uint32 diff_count =  my_atomic_load32((int32*)(&cur_count)) - 
+          my_atomic_load32((int32*)(&last_count));
+      uint64 diff_total =  my_atomic_load64((int64*)(&cur_total)) - 
+          my_atomic_load64((int64*)(&last_total));
+
+      my_atomic_store64((int64*)(&last_total), my_atomic_load64((int64*)(&cur_total)));
+      my_atomic_store32((int32*)(&last_count), my_atomic_load32((int32*)(&cur_count)));
+      my_atomic_rwlock_rdunlock(&time_collector_lock);
+
+      uint64 rt = 0;
+      if (diff_count != 0)
+          rt = diff_total/((uint64)diff_count);
+
+      return rt; 
+  }
+  
 public:
   void flush()
   {
     my_atomic_rwlock_wrlock(&time_collector_lock);
     memset((void*)&m_count,0,sizeof(m_count));
     memset((void*)&m_total,0,sizeof(m_total));
-    my_atomic_rwlock_wrunlock(&time_collector_lock);
+    memset((void*)&cur_count,0,sizeof(cur_count));
+    memset((void*)&cur_total,0,sizeof(cur_total));
+    memset((void*)&last_count,0,sizeof(last_count));
+    memset((void*)&last_total,0,sizeof(last_total));
+  my_atomic_rwlock_wrunlock(&time_collector_lock);
   }
   void collect(uint64 time)
   {
+    my_atomic_rwlock_wrlock(&time_collector_lock);
+    my_atomic_add32((int32*)(&cur_count), 1);
+    my_atomic_add64((int64*)(&cur_total), time);
+    my_atomic_rwlock_wrunlock(&time_collector_lock);
+  
     int i= 0;
     for(int count= m_utility->bound_count(); count > i; ++i)
     {
@@ -201,6 +229,10 @@
   my_atomic_rwlock_t time_collector_lock;
   uint32   m_count[OVERALL_POWER_COUNT + 1];
   uint64   m_total[OVERALL_POWER_COUNT + 1];
+  uint32   last_count;
+  uint64   last_total;
+  uint32   cur_count;
+  uint64   cur_total;
 };
 
 class collector
@@ -268,6 +300,10 @@
   {
     return m_time.total(index);
   }
+  ulonglong rt()
+  {
+    return m_time.rt();
+  }
 private:
   utility          m_utility;
   time_collector   m_time;
@@ -299,4 +335,10 @@
 {
   return query_response_time::g_collector.fill(thd,tables,cond);
 }
+
+ulonglong query_response_time_rt()
+{
+      return query_response_time::g_collector.rt();
+}
+
 #endif // HAVE_RESPONSE_TIME_DISTRIBUTION