1 # name : processlist_row_stats.patch
2 # introduced : 11 or before
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
12 thd->sent_row_count++;
13 thd->sent_row_count_2++;
14 + DEBUG_SYNC(thd, "sent_row");
17 DBUG_RETURN(protocol->write());
22 /****************************************************************************
23 Return info about all processes
24 - returns for each thread: thread id, user, host, db, command, info
25 + returns for each thread: thread id, user, host, db, command, info,
26 + rows_sent, rows_examined, rows_read
27 ****************************************************************************/
29 class thread_info :public ilink {
32 const char *user,*host,*db,*proc_info,*state_info;
33 CSET_STRING query_string;
34 + ulonglong rows_sent, rows_examined, rows_read;
37 #ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
38 @@ -1836,6 +1838,15 @@
40 field_list.push_back(field=new Item_empty_string("Info",max_query_length));
42 + field_list.push_back(field= new Item_return_int("Rows_sent",
43 + MY_INT64_NUM_DECIMAL_DIGITS,
44 + MYSQL_TYPE_LONGLONG));
45 + field_list.push_back(field= new Item_return_int("Rows_examined",
46 + MY_INT64_NUM_DECIMAL_DIGITS,
47 + MYSQL_TYPE_LONGLONG));
48 + field_list.push_back(field= new Item_return_int("Rows_read",
49 + MY_INT64_NUM_DECIMAL_DIGITS,
50 + MYSQL_TYPE_LONGLONG));
51 if (protocol->send_result_set_metadata(&field_list,
52 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
55 thd_info->query_string=
56 CSET_STRING(q, q ? length : 0, tmp->query_charset());
58 + thd_info->rows_sent= tmp->sent_row_count;
59 + thd_info->rows_examined= tmp->examined_row_count;
60 + thd_info->rows_read= tmp->warning_info->current_row_for_warning();
61 mysql_mutex_unlock(&tmp->LOCK_thd_data);
62 thd_info->start_time= tmp->start_time;
63 thread_infos.append(thd_info);
65 protocol->store(thd_info->state_info, system_charset_info);
66 protocol->store(thd_info->query_string.str(),
67 thd_info->query_string.charset());
68 + protocol->store(thd_info->rows_sent);
69 + protocol->store(thd_info->rows_examined);
70 + protocol->store(thd_info->rows_read);
71 if (protocol->write())
72 break; /* purecov: inspected */
74 @@ -2027,6 +2044,15 @@
75 table->field[8]->store(((tmp->start_utime ?
76 now_utime - tmp->start_utime : 0)/ 1000));
78 + mysql_mutex_lock(&tmp->LOCK_thd_data);
80 + table->field[9]->store((ulonglong) tmp->sent_row_count);
82 + table->field[10]->store((ulonglong) tmp->examined_row_count);
84 + table->field[11]->store((ulonglong) tmp->warning_info->current_row_for_warning());
85 + mysql_mutex_unlock(&tmp->LOCK_thd_data);
87 if (schema_table_store_record(thd, table))
89 mysql_mutex_unlock(&LOCK_thread_count);
90 @@ -8140,6 +8166,12 @@
92 {"TIME_MS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG,
93 0, 0, "Time_ms", SKIP_OPEN_TABLE},
94 + {"ROWS_SENT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
95 + MY_I_S_UNSIGNED, "Rows_sent", SKIP_OPEN_TABLE},
96 + {"ROWS_EXAMINED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
97 + MY_I_S_UNSIGNED, "Rows_examined", SKIP_OPEN_TABLE},
98 + {"ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
99 + MY_I_S_UNSIGNED, "Rows_read", SKIP_OPEN_TABLE},
100 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
104 +++ b/mysql-test/include/percona_processlist_row_stats_show.inc
106 +--replace_column 1 ### 3 ### 6 ### 7 ###
109 +--replace_column 1 ###
110 +SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
112 +SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
114 +++ b/mysql-test/r/percona_processlist_row_stats.result
116 +DROP TABLE IF EXISTS t1;
117 +DROP TABLE IF EXISTS t2;
118 +CREATE TABLE t2 (a INT);
119 +INSERT INTO t2 VALUES(10);
120 +INSERT INTO t2 VALUES(10);
121 +INSERT INTO t2 VALUES(20);
122 +INSERT INTO t2 VALUES(10);
123 +INSERT INTO t2 VALUES(20);
124 +SET DEBUG_SYNC= 'locked_table_name SIGNAL thread1_ready WAIT_FOR threads_dumped';
125 +CREATE TABLE t1 (a INT);
126 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
127 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
128 +SELECT a FROM t2 WHERE a > 15;
129 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
131 +Id User Host db Command Time State Info Rows_sent Rows_examined Rows_read
132 +### root ### test Query ### ### SHOW PROCESSLIST 0 0 2
133 +### root ### test Query ### ### CREATE TABLE t1 (a INT) 0 0 1
134 +### root ### test Query ### ### SELECT a FROM t2 WHERE a > 15 2 5 6
135 +SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
136 +id info rows_sent rows_examined rows_read
137 +### SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id 0 0 1
138 +### CREATE TABLE t1 (a INT) 0 0 1
139 +### SELECT a FROM t2 WHERE a > 15 2 5 6
140 +SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
144 +SET DEBUG_SYNC= 'sent_row SIGNAL thread1_ready WAIT_FOR threads_dumped';
145 +SELECT a FROM t2 WHERE a < 15;
146 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
147 +SET DEBUG_SYNC= 'sent_row SIGNAL thread2_ready WAIT_FOR threads_dumped';
148 +SELECT a FROM t2 WHERE a > 15;
149 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
151 +Id User Host db Command Time State Info Rows_sent Rows_examined Rows_read
152 +### root ### test Query ### ### SHOW PROCESSLIST 0 0 4
153 +### root ### test Query ### ### SELECT a FROM t2 WHERE a < 15 1 0 1
154 +### root ### test Query ### ### SELECT a FROM t2 WHERE a > 15 1 0 3
155 +SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
156 +id info rows_sent rows_examined rows_read
157 +### SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id 0 0 1
158 +### SELECT a FROM t2 WHERE a < 15 1 0 1
159 +### SELECT a FROM t2 WHERE a > 15 1 0 3
160 +SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
168 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread1_ready WAIT_FOR threads_dumped';
169 +UPDATE t2 SET a = 15 WHERE a = 20;
170 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
171 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
172 +UPDATE t2 SET a = 15 WHERE a = 10;
173 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
175 +Id User Host db Command Time State Info Rows_sent Rows_examined Rows_read
176 +### root ### test Query ### ### SHOW PROCESSLIST 0 0 4
177 +### root ### test Query ### ### UPDATE t2 SET a = 15 WHERE a = 20 0 5 6
178 +### root ### test Query ### ### UPDATE t2 SET a = 15 WHERE a = 10 0 5 6
179 +SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id;
180 +id info rows_sent rows_examined rows_read
181 +### SELECT id, info, rows_sent, rows_examined, rows_read FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id 0 0 1
182 +### UPDATE t2 SET a = 15 WHERE a = 20 0 5 6
183 +### UPDATE t2 SET a = 15 WHERE a = 10 0 5 6
184 +SET DEBUG_SYNC= 'now SIGNAL threads_dumped';
187 +++ b/mysql-test/t/percona_processlist_row_stats.test
189 +# Testing of INFORMATION_SCHEMA.PROCESSLIST fields ROWS_SENT, ROWS_EXAMINED, ROWS_READ
190 +--source include/have_debug_sync.inc
193 +DROP TABLE IF EXISTS t1;
194 +DROP TABLE IF EXISTS t2;
197 +CREATE TABLE t2 (a INT);
198 +INSERT INTO t2 VALUES(10);
199 +INSERT INTO t2 VALUES(10);
200 +INSERT INTO t2 VALUES(20);
201 +INSERT INTO t2 VALUES(10);
202 +INSERT INTO t2 VALUES(20);
204 +--connect (conn1, localhost, root, ,)
205 +--connect (conn2, localhost, root, ,)
208 +SET DEBUG_SYNC= 'locked_table_name SIGNAL thread1_ready WAIT_FOR threads_dumped';
209 +send CREATE TABLE t1 (a INT);
210 +--connection default
211 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
214 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
215 +send SELECT a FROM t2 WHERE a > 15;
216 +--connection default
217 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
219 +--source include/percona_processlist_row_stats_show.inc
227 +SET DEBUG_SYNC= 'sent_row SIGNAL thread1_ready WAIT_FOR threads_dumped';
228 +send SELECT a FROM t2 WHERE a < 15;
229 +--connection default
230 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
233 +SET DEBUG_SYNC= 'sent_row SIGNAL thread2_ready WAIT_FOR threads_dumped';
234 +send SELECT a FROM t2 WHERE a > 15;
235 +--connection default
236 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
238 +--source include/percona_processlist_row_stats_show.inc
246 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread1_ready WAIT_FOR threads_dumped';
247 +send UPDATE t2 SET a = 15 WHERE a = 20;
248 +--connection default
249 +SET DEBUG_SYNC= 'now WAIT_FOR thread1_ready';
252 +SET DEBUG_SYNC= 'execute_command_after_close_tables SIGNAL thread2_ready WAIT_FOR threads_dumped';
253 +send UPDATE t2 SET a = 15 WHERE a = 10;
254 +--connection default
255 +SET DEBUG_SYNC= 'now WAIT_FOR thread2_ready';
257 +--source include/percona_processlist_row_stats_show.inc
264 +--connection default