MySQL源代碼:為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

[cpp] view plaincopyprint?

1. Index: sql/mysqld.cc
2. ===================================================================
3. --- sql/mysqld.cc (revision 1006)
4. +++ sql/mysqld.cc (working copy)
5. @@ -6307,6 +6307,17 @@
6. return 0;
7. }
9. +#ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
10. +static int show_rt(THD *thd, SHOW_VAR *var, char *buff)
11. +{
12. + var->type = SHOW_LONGLONG;
13. + ulonglong rt = query_response_time_rt();
14. + var->value = buff;
15. + *((long long *)buff)= (long long)rt;
16. + return 0;
17. +}
18. +#endif
19. +
20. #ifdef ENABLED_PROFILING
21. static int show_flushstatustime(THD *thd, SHOW_VAR *var, char *buff)
22. {
23. @@ -6811,6 +6822,9 @@
24. #ifdef ENABLED_PROFILING
25. {"Uptime_since_flush_status",(char*) &show_flushstatustime, SHOW_FUNC},
26. #endif
27. +#ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
28. + {"rt_from_last_query", (char*) &show_rt, SHOW_FUNC},
29. +#endif
30. {NullS, NullS, SHOW_LONG}
31. };
33. Index: sql/query_response_time.h
34. ===================================================================
35. --- sql/query_response_time.h (revision 1006)
36. +++ sql/query_response_time.h (working copy)
37. @@ -59,6 +59,7 @@
38. extern void query_response_time_flush ();
39. extern void query_response_time_collect(ulonglong query_time);
40. extern int query_response_time_fill (THD* thd, TABLE_LIST *tables, COND *cond);
41. +extern ulonglong query_response_time_rt();
42. #endif // HAVE_RESPONSE_TIME_DISTRIBUTION
44. #endif // QUERY_RESPONSE_TIME_H
45. Index: sql/query_response_time.cc
46. ===================================================================
47. --- sql/query_response_time.cc (revision 1006)
48. +++ sql/query_response_time.cc (working copy)
49. @@ -170,16 +170,44 @@
50. my_atomic_rwlock_rdunlock(&time_collector_lock);
51. return result;
52. }
53. + uint64 rt()
54. + {
55. + my_atomic_rwlock_rdlock(&time_collector_lock);
56. + uint32 diff_count = my_atomic_load32((int32*)(&cur_count)) -
57. + my_atomic_load32((int32*)(&last_count));
58. + uint64 diff_total = my_atomic_load64((int64*)(&cur_total)) -
59. + my_atomic_load64((int64*)(&last_total));
60. +
61. + my_atomic_store64((int64*)(&last_total), my_atomic_load64((int64*)(&cur_total)));
62. + my_atomic_store32((int32*)(&last_count), my_atomic_load32((int32*)(&cur_count)));
63. + my_atomic_rwlock_rdunlock(&time_collector_lock);
64. +
65. + uint64 rt = 0;
66. + if (diff_count != 0)
67. + rt = diff_total/((uint64)diff_count);
68. +
69. + return rt;
70. + }
71. +
72. public:
73. void flush()
74. {
75. my_atomic_rwlock_wrlock(&time_collector_lock);
76. memset((void*)&m_count,0,sizeof(m_count));
77. memset((void*)&m_total,0,sizeof(m_total));
78. - my_atomic_rwlock_wrunlock(&time_collector_lock);
79. + memset((void*)&cur_count,0,sizeof(cur_count));
80. + memset((void*)&cur_total,0,sizeof(cur_total));
81. + memset((void*)&last_count,0,sizeof(last_count));
82. + memset((void*)&last_total,0,sizeof(last_total));
83. + my_atomic_rwlock_wrunlock(&time_collector_lock);
84. }
85. void collect(uint64 time)
86. {
87. + my_atomic_rwlock_wrlock(&time_collector_lock);
88. + my_atomic_add32((int32*)(&cur_count), 1);
89. + my_atomic_add64((int64*)(&cur_total), time);
90. + my_atomic_rwlock_wrunlock(&time_collector_lock);
91. +
92. int i= 0;
93. for(int count= m_utility->bound_count(); count > i; ++i)
94. {
95. @@ -201,6 +229,10 @@
96. my_atomic_rwlock_t time_collector_lock;
97. uint32 m_count[OVERALL_POWER_COUNT + 1];
98. uint64 m_total[OVERALL_POWER_COUNT + 1];
99. + uint32 last_count;
100. + uint64 last_total;
101. + uint32 cur_count;
102. + uint64 cur_total;
103. };
105. class collector
106. @@ -268,6 +300,10 @@
107. {
108. return m_time.total(index);
109. }
110. + ulonglong rt()
111. + {
112. + return m_time.rt();
113. + }
114. private:
115. utility m_utility;
116. time_collector m_time;
117. @@ -299,4 +335,10 @@
118. {
119. return query_response_time::g_collector.fill(thd,tables,cond);
120. }
121. +
122. +ulonglong query_response_time_rt()
123. +{
124. + return query_response_time::g_collector.rt();
125. +}
126. +
127. #endif // HAVE_RESPONSE_TIME_DISTRIBUTION