實現的思路很簡單,借助了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
[cpp] view plaincopyprint?
- 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