]> git.pld-linux.org Git - packages/mysql.git/blame - mysql-userstats.patch
projects / packages / mysql.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
- update mysqld.conf path in init fail message, don't suggest to use mysqlbug script...
[packages/mysql.git] / mysql-userstats.patch
CommitLineData
f11bce9d
ER		 1diff -r 3ed7e96969f9 include/mysql_com.h
2--- a/include/mysql_com.h Thu Dec 04 08:54:17 2008 -0800
3+++ b/include/mysql_com.h Thu Dec 04 08:54:27 2008 -0800
4@@ -115,6 +115,8 @@
5 thread */
6 #define REFRESH_MASTER 128 /* Remove all bin logs in the index
7 and truncate the index */
8+#define REFRESH_TABLE_STATS 256 /* Refresh table stats hash table */
9+#define REFRESH_INDEX_STATS 512 /* Refresh index stats hash table */
10
11 /* The following can't be set with mysql_refresh() */
12 #define REFRESH_READ_LOCK 16384 /* Lock tables for read */
13diff -r 3ed7e96969f9 sql/handler.cc
14--- a/sql/handler.cc Thu Dec 04 08:54:17 2008 -0800
15+++ b/sql/handler.cc Thu Dec 04 08:54:27 2008 -0800
16@@ -1205,6 +1205,7 @@
17 error=1;
18 }
19 status_var_increment(thd->status_var.ha_commit_count);
20+ thd->diff_commit_trans++;
21 ha_info_next= ha_info->next();
22 ha_info->reset(); /* keep it conveniently zero-filled */
23 }
24@@ -1272,6 +1273,7 @@
25 error=1;
26 }
27 status_var_increment(thd->status_var.ha_rollback_count);
28+ thd->diff_rollback_trans++;
29 ha_info_next= ha_info->next();
30 ha_info->reset(); /* keep it conveniently zero-filled */
31 }
32@@ -1724,6 +1726,7 @@
33 error=1;
34 }
35 status_var_increment(thd->status_var.ha_rollback_count);
36+ thd->diff_rollback_trans++;
37 ha_info_next= ha_info->next();
38 ha_info->reset(); /* keep it conveniently zero-filled */
39 }
40@@ -2058,6 +2061,8 @@
41 dup_ref=ref+ALIGN_SIZE(ref_length);
42 cached_table_flags= table_flags();
43 }
44+ rows_read = rows_changed = 0;
45+ memset(index_rows_read, 0, sizeof(index_rows_read));
46 DBUG_RETURN(error);
47 }
48
49@@ -3487,6 +3492,97 @@
50 return;
51 }
52
53+// Updates the global table stats with the TABLE this handler represents.
54+void handler::update_global_table_stats() {
55+ if (!rows_read && !rows_changed) return; // Nothing to update.
56+ // table_cache_key is db_name + '\0' + table_name + '\0'.
57+ if (!table->s || !table->s->table_cache_key.str || !table->s->table_name.str) return;
58+
59+ TABLE_STATS* table_stats;
60+ char key[NAME_LEN * 2 + 2];
61+ // [db] + '.' + [table]
62+ sprintf(key, "%s.%s", table->s->table_cache_key.str, table->s->table_name.str);
63+
64+ pthread_mutex_lock(&LOCK_global_table_stats);
65+ // Gets the global table stats, creating one if necessary.
66+ if (!(table_stats = (TABLE_STATS*)hash_search(&global_table_stats,
67+ (uchar*)key,
68+ strlen(key)))) {
69+ if (!(table_stats = ((TABLE_STATS*)
70+ my_malloc(sizeof(TABLE_STATS), MYF(MY_WME))))) {
71+ // Out of memory.
72+ sql_print_error("Allocating table stats failed.");
73+ goto end;
74+ }
75+ strncpy(table_stats->table, key, sizeof(table_stats->table));
76+ table_stats->rows_read = 0;
77+ table_stats->rows_changed = 0;
78+ table_stats->rows_changed_x_indexes = 0;
79+
80+ if (my_hash_insert(&global_table_stats, (uchar*)table_stats)) {
81+ // Out of memory.
82+ sql_print_error("Inserting table stats failed.");
83+ my_free((char*)table_stats, 0);
84+ goto end;
85+ }
86+ }
87+ // Updates the global table stats.
88+ table_stats->rows_read += rows_read;
89+ table_stats->rows_changed += rows_changed;
90+ table_stats->rows_changed_x_indexes +=
91+ rows_changed * (table->s->keys ? table->s->keys : 1);
92+ rows_read = rows_changed = 0;
93+end:
94+ pthread_mutex_unlock(&LOCK_global_table_stats);
95+}
96+
97+// Updates the global index stats with this handler's accumulated index reads.
98+void handler::update_global_index_stats() {
99+ // table_cache_key is db_name + '\0' + table_name + '\0'.
100+ if (!table->s || !table->s->table_cache_key.str || !table->s->table_name.str) return;
101+
102+ for (int x = 0; x < table->s->keys; x++) {
103+ if (index_rows_read[x]) {
104+ // Rows were read using this index.
105+ KEY* key_info = &table->key_info[x];
106+
107+ if (!key_info->name) continue;
108+
109+ INDEX_STATS* index_stats;
110+ char key[NAME_LEN * 3 + 3];
111+ // [db] + '.' + [table] + '.' + [index]
112+ sprintf(key, "%s.%s.%s", table->s->table_cache_key.str,
113+ table->s->table_name.str, key_info->name);
114+
115+ pthread_mutex_lock(&LOCK_global_index_stats);
116+ // Gets the global index stats, creating one if necessary.
117+ if (!(index_stats = (INDEX_STATS*)hash_search(&global_index_stats,
118+ (uchar*)key,
119+ strlen(key)))) {
120+ if (!(index_stats = ((INDEX_STATS*)
121+ my_malloc(sizeof(INDEX_STATS), MYF(MY_WME))))) {
122+ // Out of memory.
123+ sql_print_error("Allocating index stats failed.");
124+ goto end;
125+ }
126+ strncpy(index_stats->index, key, sizeof(index_stats->index));
127+ index_stats->rows_read = 0;
128+
129+ if (my_hash_insert(&global_index_stats, (uchar*)index_stats)) {
130+ // Out of memory.
131+ sql_print_error("Inserting index stats failed.");
132+ my_free((char*)index_stats, 0);
133+ goto end;
134+ }
135+ }
136+ // Updates the global index stats.
137+ index_stats->rows_read += index_rows_read[x];
138+ index_rows_read[x] = 0;
139+end:
140+ pthread_mutex_unlock(&LOCK_global_index_stats);
141+ }
142+ }
143+}
144
145 /****************************************************************************
146 ** Some general functions that isn't in the handler class
147diff -r 3ed7e96969f9 sql/handler.h
148--- a/sql/handler.h Thu Dec 04 08:54:17 2008 -0800
149+++ b/sql/handler.h Thu Dec 04 08:54:27 2008 -0800
150@@ -29,6 +29,10 @@
151 #endif
152
153 #define USING_TRANSACTIONS
154+
155+#if MAX_KEY > 128
156+#error MAX_KEY is too large. Values up to 128 are supported.
157+#endif
158
159 // the following is for checking tables
160
161@@ -690,6 +694,7 @@
162 */
163 enum log_status (*get_log_status)(handlerton *hton, char *log);
164
165+
166 /*
167 Iterators creator.
168 Presence of the pointer should be checked before using
169@@ -1137,6 +1142,10 @@
170 */
171 uint auto_inc_intervals_count;
172
173+ ulonglong rows_read;
174+ ulonglong rows_changed;
175+ ulonglong index_rows_read[MAX_KEY];
176+
177 handler(handlerton *ht_arg, TABLE_SHARE *share_arg)
178 :table_share(share_arg), table(0),
179 estimation_rows_to_insert(0), ht(ht_arg),
180@@ -1145,8 +1154,10 @@
181 ft_handler(0), inited(NONE),
182 locked(FALSE), implicit_emptied(0),
183 pushed_cond(0), next_insert_id(0), insert_id_for_cur_row(0),
184- auto_inc_intervals_count(0)
185- {}
186+ auto_inc_intervals_count(0), rows_read(0), rows_changed(0)
187+ {
188+ memset(index_rows_read, 0, sizeof(index_rows_read));
189+ }
190 virtual ~handler(void)
191 {
192 DBUG_ASSERT(locked == FALSE);
193@@ -1267,7 +1278,13 @@
194 {
195 table= table_arg;
196 table_share= share;
197+ rows_read = rows_changed = 0;
198+ memset(index_rows_read, 0, sizeof(index_rows_read));
199 }
200+
201+ void update_global_table_stats();
202+ void update_global_index_stats();
203+
204 virtual double scan_time()
205 { return ulonglong2double(stats.data_file_length) / IO_SIZE + 2; }
206 virtual double read_time(uint index, uint ranges, ha_rows rows)
207diff -r 3ed7e96969f9 sql/lex.h
208--- a/sql/lex.h Thu Dec 04 08:54:17 2008 -0800
209+++ b/sql/lex.h Thu Dec 04 08:54:27 2008 -0800
210@@ -245,6 +245,7 @@
211 { "IN", SYM(IN_SYM)},
212 { "INDEX", SYM(INDEX_SYM)},
213 { "INDEXES", SYM(INDEXES)},
214+ { "INDEX_STATISTICS", SYM(INDEX_STATS_SYM)},
215 { "INFILE", SYM(INFILE)},
216 { "INITIAL_SIZE", SYM(INITIAL_SIZE_SYM)},
217 { "INNER", SYM(INNER_SYM)},
218@@ -528,6 +529,7 @@
219 { "TABLES", SYM(TABLES)},
220 { "TABLESPACE", SYM(TABLESPACE)},
221 { "TABLE_CHECKSUM", SYM(TABLE_CHECKSUM_SYM)},
222+ { "TABLE_STATISTICS", SYM(TABLE_STATS_SYM)},
223 { "TEMPORARY", SYM(TEMPORARY)},
224 { "TEMPTABLE", SYM(TEMPTABLE_SYM)},
225 { "TERMINATED", SYM(TERMINATED)},
226@@ -570,6 +572,7 @@
227 { "USE", SYM(USE_SYM)},
228 { "USER", SYM(USER)},
229 { "USER_RESOURCES", SYM(RESOURCES)},
230+ { "USER_STATISTICS", SYM(USER_STATS_SYM)},
231 { "USE_FRM", SYM(USE_FRM)},
232 { "USING", SYM(USING)},
233 { "UTC_DATE", SYM(UTC_DATE_SYM)},
234diff -r 3ed7e96969f9 sql/mysql_priv.h
235--- a/sql/mysql_priv.h Thu Dec 04 08:54:17 2008 -0800
236+++ b/sql/mysql_priv.h Thu Dec 04 08:54:27 2008 -0800
237@@ -1060,7 +1060,19 @@
238 bool multi_delete_set_locks_and_link_aux_tables(LEX *lex);
239 void init_max_user_conn(void);
240 void init_update_queries(void);
241+void init_global_user_stats(void);
242+void init_global_table_stats(void);
243+void init_global_index_stats(void);
244 void free_max_user_conn(void);
245+void free_global_user_stats(void);
246+void free_global_table_stats(void);
247+void free_global_index_stats(void);
248+// Uses the THD to update the global stats.
249+void update_global_user_stats(THD* thd);
250+// Set stats for concurrent connections displayed by mysqld_show().
251+void set_concurrent_connections_stats();
252+// Increments connection count for user.
253+int increment_connection_count(THD* thd, bool use_lock);
254 pthread_handler_t handle_bootstrap(void *arg);
255 int mysql_execute_command(THD *thd);
256 bool do_command(THD *thd);
257@@ -2015,6 +2027,12 @@
258 extern struct system_variables max_system_variables;
259 extern struct system_status_var global_status_var;
260 extern struct rand_struct sql_rand;
261+extern HASH global_user_stats;
262+extern pthread_mutex_t LOCK_global_user_stats;
263+extern HASH global_table_stats;
264+extern pthread_mutex_t LOCK_global_table_stats;
265+extern HASH global_index_stats;
266+extern pthread_mutex_t LOCK_global_index_stats;
267
268 extern const char *opt_date_time_formats[];
269 extern KNOWN_DATE_TIME_FORMAT known_date_time_formats[];
270diff -r 3ed7e96969f9 sql/mysqld.cc
271--- a/sql/mysqld.cc Thu Dec 04 08:54:17 2008 -0800
272+++ b/sql/mysqld.cc Thu Dec 04 08:54:27 2008 -0800
273@@ -589,6 +589,11 @@
274 LOCK_global_system_variables,
275 LOCK_user_conn, LOCK_slave_list, LOCK_active_mi,
276 LOCK_connection_count;
277+
278+pthread_mutex_t LOCK_global_user_stats;
279+pthread_mutex_t LOCK_global_table_stats;
280+pthread_mutex_t LOCK_global_index_stats;
281+
282 /**
283 The below lock protects access to two global server variables:
284 max_prepared_stmt_count and prepared_stmt_count. These variables
285@@ -1266,6 +1271,9 @@
286 x_free(opt_secure_file_priv);
287 bitmap_free(&temp_pool);
288 free_max_user_conn();
289+ free_global_user_stats();
290+ free_global_table_stats();
291+ free_global_index_stats();
292 #ifdef HAVE_REPLICATION
293 end_slave_list();
294 #endif
295@@ -1378,6 +1386,9 @@
296 (void) pthread_cond_destroy(&COND_thread_cache);
297 (void) pthread_cond_destroy(&COND_flush_thread_cache);
298 (void) pthread_cond_destroy(&COND_manager);
299+ (void) pthread_mutex_destroy(&LOCK_global_user_stats);
300+ (void) pthread_mutex_destroy(&LOCK_global_table_stats);
301+ (void) pthread_mutex_destroy(&LOCK_global_index_stats);
302 }
303
304 #endif /*EMBEDDED_LIBRARY*/
305@@ -3072,6 +3083,7 @@
306 {"show_function_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS_FUNC]), SHOW_LONG_STATUS},
307 {"show_grants", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_GRANTS]), SHOW_LONG_STATUS},
308 {"show_keys", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_KEYS]), SHOW_LONG_STATUS},
309+ {"show_index_stats", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_INDEX_STATS]), SHOW_LONG_STATUS},
310 {"show_master_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_MASTER_STAT]), SHOW_LONG_STATUS},
311 {"show_new_master", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_NEW_MASTER]), SHOW_LONG_STATUS},
312 {"show_open_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_OPEN_TABLES]), SHOW_LONG_STATUS},
313@@ -3089,9 +3101,11 @@
314 {"show_slave_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_STAT]), SHOW_LONG_STATUS},
315 {"show_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS},
316 {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
317+ {"show_table_stats", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATS]), SHOW_LONG_STATUS},
318 {"show_table_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
319 {"show_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS},
320 {"show_triggers", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},
321+ {"show_user_stats", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_USER_STATS]), SHOW_LONG_STATUS},
322 {"show_variables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},
323 {"show_warnings", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS},
324 {"slave_start", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_START]), SHOW_LONG_STATUS},
325@@ -3507,6 +3521,9 @@
326 #endif
327 (void) pthread_mutex_init(&LOCK_server_started, MY_MUTEX_INIT_FAST);
328 (void) pthread_cond_init(&COND_server_started,NULL);
329+ (void) pthread_mutex_init(&LOCK_global_user_stats, MY_MUTEX_INIT_FAST);
330+ (void) pthread_mutex_init(&LOCK_global_table_stats, MY_MUTEX_INIT_FAST);
331+ (void) pthread_mutex_init(&LOCK_global_index_stats, MY_MUTEX_INIT_FAST);
332 sp_cache_init();
333 #ifdef HAVE_EVENT_SCHEDULER
334 Events::init_mutexes();
335@@ -3872,6 +3889,9 @@
336 if (!errmesg[0][0])
337 unireg_abort(1);
338
339+ init_global_table_stats();
340+ init_global_index_stats();
341+
342 /* We have to initialize the storage engines before CSV logging */
343 if (ha_init())
344 {
345@@ -4018,6 +4038,7 @@
346
347 init_max_user_conn();
348 init_update_queries();
349+ init_global_user_stats();
350 DBUG_RETURN(0);
351 }
352
353diff -r 3ed7e96969f9 sql/sql_base.cc
354--- a/sql/sql_base.cc Thu Dec 04 08:54:17 2008 -0800
355+++ b/sql/sql_base.cc Thu Dec 04 08:54:27 2008 -0800
356@@ -1343,6 +1343,12 @@
357 DBUG_PRINT("tcache", ("table: '%s'.'%s' 0x%lx", table->s->db.str,
358 table->s->table_name.str, (long) table));
359
360+ if(table->file)
361+ {
362+ table->file->update_global_table_stats();
363+ table->file->update_global_index_stats();
364+ }
365+
366 *table_ptr=table->next;
367 /*
368 When closing a MERGE parent or child table, detach the children first.
369@@ -1882,6 +1888,9 @@
370 DBUG_ENTER("close_temporary");
371 DBUG_PRINT("tmptable", ("closing table: '%s'.'%s'",
372 table->s->db.str, table->s->table_name.str));
373+
374+ table->file->update_global_table_stats();
375+ table->file->update_global_index_stats();
376
377 free_io_cache(table);
378 closefrm(table, 0);
379diff -r 3ed7e96969f9 sql/sql_class.cc
380--- a/sql/sql_class.cc Thu Dec 04 08:54:17 2008 -0800
381+++ b/sql/sql_class.cc Thu Dec 04 08:54:27 2008 -0800
382@@ -578,6 +578,8 @@
383 bzero(ha_data, sizeof(ha_data));
384 mysys_var=0;
385 binlog_evt_union.do_union= FALSE;
386+ busy_time = 0;
387+ updated_row_count = 0;
388 enable_slow_log= 0;
389 #ifndef DBUG_OFF
390 dbug_sentry=THD_SENTRY_MAGIC;
391@@ -762,8 +764,55 @@
392 reset_current_stmt_binlog_row_based();
393 bzero((char *) &status_var, sizeof(status_var));
394 sql_log_bin_toplevel= options & OPTION_BIN_LOG;
395+ reset_stats();
396 }
397
398+// Resets stats in a THD.
399+void THD::reset_stats(void) {
400+ current_connect_time = time(NULL);
401+ last_global_update_time = current_connect_time;
402+ reset_diff_stats();
403+}
404+
405+// Resets the 'diff' stats, which are used to update global stats.
406+void THD::reset_diff_stats(void) {
407+ diff_total_busy_time = 0;
408+ diff_total_sent_rows = 0;
409+ diff_total_updated_rows = 0;
410+ diff_select_commands = 0;
411+ diff_update_commands = 0;
412+ diff_other_commands = 0;
413+ diff_commit_trans = 0;
414+ diff_rollback_trans = 0;
415+}
416+
417+// Updates 'diff' stats of a THD.
418+void THD::update_stats() {
419+ diff_total_busy_time += busy_time;
420+ diff_total_sent_rows += sent_row_count;
421+ diff_total_updated_rows += updated_row_count;
422+ // The replication thread has the COM_CONNECT command.
423+ if ((old_command == COM_QUERY || command == COM_CONNECT) &&
424+ (lex->sql_command >= 0 && lex->sql_command < SQLCOM_END)) {
425+ // A SQL query.
426+ if (lex->sql_command == SQLCOM_SELECT) {
427+ if (!(sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND)) {
428+ diff_select_commands++;
429+ } else {
430+ // 'SHOW ' commands become SQLCOM_SELECT.
431+ diff_other_commands++;
432+ // 'SHOW ' commands shouldn't inflate total sent row count.
433+ diff_total_sent_rows -= sent_row_count;
434+ }
435+ } else if (is_update_query(lex->sql_command)) {
436+ diff_update_commands++;
437+ } else {
438+ diff_other_commands++;
439+ }
440+ }
441+ // diff_commit_trans is updated in handler.cc.
442+ // diff_rollback_trans is updated in handler.cc.
443+}
444
445 /*
446 Init THD for query processing.
447diff -r 3ed7e96969f9 sql/sql_class.h
448--- a/sql/sql_class.h Thu Dec 04 08:54:17 2008 -0800
449+++ b/sql/sql_class.h Thu Dec 04 08:54:27 2008 -0800
450@@ -1327,6 +1327,8 @@
451 first byte of the packet in do_command()
452 */
453 enum enum_server_command command;
454+ // Used to save the command, before it is set to COM_SLEEP.
455+ enum enum_server_command old_command;
456 uint32 server_id;
457 uint32 file_id; // for LOAD DATA INFILE
458 /* remote (peer) port */
459@@ -1616,6 +1618,7 @@
460 ulonglong options; /* Bitmap of states */
461 longlong row_count_func; /* For the ROW_COUNT() function */
462 ha_rows cuted_fields;
463+ ha_rows updated_row_count;
464
465 /*
466 number of rows we actually sent to the client, including "synthetic"
467@@ -1767,6 +1770,27 @@
468 */
469 LOG_INFO* current_linfo;
470 NET* slave_net; // network connection from slave -> m.
471+
472+ /*
473+ Used to update global user stats. The global user stats are updated
474+ occasionally with the 'diff' variables. After the update, the 'diff'
475+ variables are reset to 0.
476+ */
477+ // Time when the current thread connected to MySQL.
478+ time_t current_connect_time;
479+ // Last time when THD stats were updated in global_user_stats.
480+ time_t last_global_update_time;
481+ // Busy (non-idle) time for just one command.
482+ double busy_time;
483+ // Busy time not updated in global_user_stats yet.
484+ double diff_total_busy_time;
485+ // Number of rows not reflected in global_user_stats yet.
486+ ha_rows diff_total_sent_rows, diff_total_updated_rows;
487+ // Number of commands not reflected in global_user_stats yet.
488+ ulonglong diff_select_commands, diff_update_commands, diff_other_commands;
489+ // Number of transactions not reflected in global_user_stats yet.
490+ ulonglong diff_commit_trans, diff_rollback_trans;
491+
492 /* Used by the sys_var class to store temporary values */
493 union
494 {
495@@ -1827,6 +1851,9 @@
496 alloc_root.
497 */
498 void init_for_queries();
499+ void reset_stats(void);
500+ void reset_diff_stats(void);
501+ void update_stats(void);
502 void change_user(void);
503 void cleanup(void);
504 void cleanup_after_query();
505diff -r 3ed7e96969f9 sql/sql_connect.cc
506--- a/sql/sql_connect.cc Thu Dec 04 08:54:17 2008 -0800
507+++ b/sql/sql_connect.cc Thu Dec 04 08:54:27 2008 -0800
508@@ -520,6 +520,14 @@
509 0,0,
510 (hash_get_key) get_key_conn, (hash_free_key) free_user,
511 0);
512+ if (hash_init(&hash_user_connections,system_charset_info,max_connections,
513+ 0,0,
514+ (hash_get_key) get_key_conn, (hash_free_key) free_user,
515+ 0)) {
516+ sql_print_error("Initializing hash_user_connections failed.");
517+ exit(1);
518+ }
519+
520 #endif
521 }
522
523@@ -1107,6 +1115,13 @@
524 if (login_connection(thd))
525 goto end_thread;
526
527+ thd->reset_stats();
528+ // Updates global user connection stats.
529+ if (increment_connection_count(thd, true)) {
530+ net_send_error(thd, ER_OUTOFMEMORY); // Out of memory
531+ goto end_thread;
532+ }
533+
534 prepare_new_connection_state(thd);
535
536 while (!net->error && net->vio != 0 &&
537@@ -1119,6 +1134,8 @@
538
539 end_thread:
540 close_connection(thd, 0, 1);
541+ thd->update_stats();
542+ update_global_user_stats(thd);
543 if (thread_scheduler.end_thread(thd,1))
544 return 0; // Probably no-threads
545
546diff -r 3ed7e96969f9 sql/sql_delete.cc
547--- a/sql/sql_delete.cc Thu Dec 04 08:54:17 2008 -0800
548+++ b/sql/sql_delete.cc Thu Dec 04 08:54:27 2008 -0800
549@@ -402,6 +402,7 @@
550 my_ok(thd, (ha_rows) thd->row_count_func);
551 DBUG_PRINT("info",("%ld records deleted",(long) deleted));
552 }
553+ thd->updated_row_count += deleted;
554 DBUG_RETURN(error >= 0 || thd->is_error());
555 }
556
557@@ -938,6 +939,7 @@
558 thd->row_count_func= deleted;
559 ::my_ok(thd, (ha_rows) thd->row_count_func);
560 }
561+ thd->updated_row_count += deleted;
562 return 0;
563 }
564
565diff -r 3ed7e96969f9 sql/sql_insert.cc
566--- a/sql/sql_insert.cc Thu Dec 04 08:54:17 2008 -0800
567+++ b/sql/sql_insert.cc Thu Dec 04 08:54:27 2008 -0800
568@@ -969,6 +969,7 @@
569 thd->row_count_func= info.copied + info.deleted + updated;
570 ::my_ok(thd, (ulong) thd->row_count_func, id, buff);
571 }
572+ thd->updated_row_count += thd->row_count_func;
573 thd->abort_on_warning= 0;
574 DBUG_RETURN(FALSE);
575
576diff -r 3ed7e96969f9 sql/sql_lex.h
577--- a/sql/sql_lex.h Thu Dec 04 08:54:17 2008 -0800
578+++ b/sql/sql_lex.h Thu Dec 04 08:54:27 2008 -0800
579@@ -118,7 +118,7 @@
580 SQLCOM_SHOW_CREATE_TRIGGER,
581 SQLCOM_ALTER_DB_UPGRADE,
582 SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
583-
584+ SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS,
585 /*
586 When a command is added here, be sure it's also added in mysqld.cc
587 in "struct show_var_st status_vars[]= {" ...
588diff -r 3ed7e96969f9 sql/sql_parse.cc
589--- a/sql/sql_parse.cc Thu Dec 04 08:54:17 2008 -0800
590+++ b/sql/sql_parse.cc Thu Dec 04 08:54:27 2008 -0800
591@@ -45,6 +45,15 @@
592
593 static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables);
594 static bool check_show_create_table_access(THD *thd, TABLE_LIST *table);
595+
596+HASH global_user_stats;
597+extern pthread_mutex_t LOCK_global_user_stats;
598+
599+HASH global_table_stats;
600+extern pthread_mutex_t LOCK_global_table_stats;
601+
602+HASH global_index_stats;
603+extern pthread_mutex_t LOCK_global_index_stats;
604
605 const char *any_db="*any*"; // Special symbol for check_access
606
607@@ -326,7 +335,9 @@
608 sql_command_flags[SQLCOM_SHOW_FUNC_CODE]= CF_STATUS_COMMAND;
609 sql_command_flags[SQLCOM_SHOW_CREATE_EVENT]= CF_STATUS_COMMAND;
610 sql_command_flags[SQLCOM_SHOW_PROFILES]= CF_STATUS_COMMAND;
611- sql_command_flags[SQLCOM_SHOW_PROFILE]= CF_STATUS_COMMAND;
612+ sql_command_flags[SQLCOM_SHOW_USER_STATS]= CF_STATUS_COMMAND;
613+ sql_command_flags[SQLCOM_SHOW_TABLE_STATS]= CF_STATUS_COMMAND;
614+ sql_command_flags[SQLCOM_SHOW_INDEX_STATS]= CF_STATUS_COMMAND;
615
616 sql_command_flags[SQLCOM_SHOW_TABLES]= (CF_STATUS_COMMAND |
617 CF_SHOW_TABLE_COMMAND |
618@@ -544,6 +555,86 @@
619 DBUG_RETURN(0);
620 }
621
622+extern "C" uchar *get_key_user_stats(USER_STATS *user_stats, size_t *length,
623+ my_bool not_used __attribute__((unused)))
624+{
625+ *length = strlen(user_stats->user);
626+ return (uchar*)user_stats->user;
627+}
628+
629+extern "C" void free_user_stats(USER_STATS* user_stats)
630+{
631+ my_free((char*)user_stats, MYF(0));
632+}
633+
634+void init_global_user_stats(void)
635+{
636+ if (hash_init(&global_user_stats, system_charset_info, max_connections,
637+ 0, 0, (hash_get_key)get_key_user_stats,
638+ (hash_free_key)free_user_stats, 0)) {
639+ sql_print_error("Initializing global_user_stats failed.");
640+ exit(1);
641+ }
642+}
643+
644+extern "C" uchar *get_key_table_stats(TABLE_STATS *table_stats, size_t *length,
645+ my_bool not_used __attribute__((unused)))
646+{
647+ *length = strlen(table_stats->table);
648+ return (uchar*)table_stats->table;
649+}
650+
651+extern "C" void free_table_stats(TABLE_STATS* table_stats)
652+{
653+ my_free((char*)table_stats, MYF(0));
654+}
655+
656+void init_global_table_stats(void)
657+{
658+ if (hash_init(&global_table_stats, system_charset_info, max_connections,
659+ 0, 0, (hash_get_key)get_key_table_stats,
660+ (hash_free_key)free_table_stats, 0)) {
661+ sql_print_error("Initializing global_table_stats failed.");
662+ exit(1);
663+ }
664+}
665+
666+extern "C" uchar *get_key_index_stats(INDEX_STATS *index_stats, size_t *length,
667+ my_bool not_used __attribute__((unused)))
668+{
669+ *length = strlen(index_stats->index);
670+ return (uchar*)index_stats->index;
671+}
672+
673+extern "C" void free_index_stats(INDEX_STATS* index_stats)
674+{
675+ my_free((char*)index_stats, MYF(0));
676+}
677+
678+void init_global_index_stats(void)
679+{
680+ if (hash_init(&global_index_stats, system_charset_info, max_connections,
681+ 0, 0, (hash_get_key)get_key_index_stats,
682+ (hash_free_key)free_index_stats, 0)) {
683+ sql_print_error("Initializing global_index_stats failed.");
684+ exit(1);
685+ }
686+}
687+
688+void free_global_user_stats(void)
689+{
690+ hash_free(&global_user_stats);
691+}
692+
693+void free_global_table_stats(void)
694+{
695+ hash_free(&global_table_stats);
696+}
697+
698+void free_global_index_stats(void)
699+{
700+ hash_free(&global_index_stats);
701+}
702
703 /**
704 @brief Check access privs for a MERGE table and fix children lock types.
705@@ -962,6 +1053,9 @@
706 DBUG_PRINT("info",("packet: '%*.s'; command: %d", packet_length, packet, command));
707
708 thd->command=command;
709+ // To increment the correct command counter for user stats, 'command' must
710+ // be saved because it is set to COM_SLEEP at the end of this function.
711+ thd->old_command = command;
712 /*
713 Commands which always take a long time are logged into
714 the slow log only if opt_log_slow_admin_statements is set.
715@@ -1740,6 +1834,9 @@
716 case SCH_COLUMN_PRIVILEGES:
717 case SCH_TABLE_CONSTRAINTS:
718 case SCH_KEY_COLUMN_USAGE:
719+ case SCH_USER_STATS:
720+ case SCH_TABLE_STATS:
721+ case SCH_INDEX_STATS:
722 default:
723 break;
724 }
725@@ -2129,6 +2226,9 @@
726 my_error(ER_NOT_SUPPORTED_YET, MYF(0), "embedded server");
727 break;
728 #endif
729+ case SQLCOM_SHOW_USER_STATS:
730+ case SQLCOM_SHOW_TABLE_STATS:
731+ case SQLCOM_SHOW_INDEX_STATS:
732 case SQLCOM_SHOW_STATUS_PROC:
733 case SQLCOM_SHOW_STATUS_FUNC:
734 if (!(res= check_table_access(thd, SELECT_ACL, all_tables, UINT_MAX, FALSE)))
735@@ -2306,6 +2406,7 @@
736 }
737 #endif
738
739+
740 case SQLCOM_BACKUP_TABLE:
741 {
742 DBUG_ASSERT(first_table == all_tables && first_table != 0);
743@@ -5372,6 +5473,130 @@
744 #endif /*NO_EMBEDDED_ACCESS_CHECKS*/
745
746
747+// 'mysql_system_user' is used for when the user is not defined for a THD.
748+static char mysql_system_user[] = "#mysql_system#";
749+
750+// Returns 'user' if it's not NULL. Returns 'mysql_system_user' otherwise.
751+static char* get_valid_user_string(char* user) {
752+ return user ? user : mysql_system_user;
753+}
754+
755+// Increments the global user stats connection count. If 'use_lock' is true,
756+// 'LOCK_global_user_stats' will be locked/unlocked. Returns 0 on success,
757+// 1 on error.
758+int increment_connection_count(THD* thd, bool use_lock) {
759+ char* user_string = get_valid_user_string(thd->main_security_ctx.user);
760+
761+ USER_STATS* user_stats;
762+ int return_value = 0;
763+
764+ if (use_lock) pthread_mutex_lock(&LOCK_global_user_stats);
765+ if (!(user_stats = (USER_STATS*)hash_search(&global_user_stats,
766+ (uchar*)user_string,
767+ strlen(user_string)))) {
768+ // First connection for this user.
769+ if (!(user_stats = ((USER_STATS*)
770+ my_malloc(sizeof(USER_STATS), MYF(MY_WME))))) {
771+ // Out of memory.
772+ return_value = 1;
773+ goto end;
774+ }
775+ strncpy(user_stats->user, user_string, sizeof(user_stats->user));
776+ user_stats->total_connections = 0;
777+ user_stats->concurrent_connections = 0;
778+ user_stats->connected_time = 0;
779+ user_stats->busy_time = 0;
780+ user_stats->rows_fetched = 0;
781+ user_stats->rows_updated = 0;
782+ user_stats->select_commands = 0;
783+ user_stats->update_commands = 0;
784+ user_stats->other_commands = 0;
785+ user_stats->commit_trans = 0;
786+ user_stats->rollback_trans = 0;
787+
788+ if (my_hash_insert(&global_user_stats, (uchar*)user_stats)) {
789+ // Out of memory.
790+ my_free((char*)user_stats, 0);
791+ return_value = 1;
792+ goto end;
793+ }
794+ }
795+ user_stats->total_connections++;
796+end:
797+ if (use_lock) pthread_mutex_unlock(&LOCK_global_user_stats);
798+ return return_value;
799+}
800+
801+// Used to update the global user stats.
802+static void update_global_user_stats_with_user(THD* thd,
803+ USER_STATS* user_stats) {
804+ time_t current_time = time(NULL);
805+ user_stats->connected_time += current_time - thd->last_global_update_time;
806+ thd->last_global_update_time = current_time;
807+ user_stats->busy_time += thd->diff_total_busy_time;
808+ user_stats->rows_fetched += thd->diff_total_sent_rows;
809+ user_stats->rows_updated += thd->diff_total_updated_rows;
810+ user_stats->select_commands += thd->diff_select_commands;
811+ user_stats->update_commands += thd->diff_update_commands;
812+ user_stats->other_commands += thd->diff_other_commands;
813+ user_stats->commit_trans += thd->diff_commit_trans;
814+ user_stats->rollback_trans += thd->diff_rollback_trans;
815+}
816+
817+// Updates the global stats of a thread/user.
818+void update_global_user_stats(THD* thd) {
819+ char* user_string = get_valid_user_string(thd->main_security_ctx.user);
820+
821+ USER_STATS* user_stats;
822+ pthread_mutex_lock(&LOCK_global_user_stats);
823+ if ((user_stats = (USER_STATS*)hash_search(&global_user_stats,
824+ (uchar*)user_string,
825+ strlen(user_string)))) {
826+ // Found user.
827+ update_global_user_stats_with_user(thd, user_stats);
828+ thd->reset_diff_stats();
829+ } else {
830+ // The user name should exist.
831+ increment_connection_count(thd, false);
832+ }
833+ pthread_mutex_unlock(&LOCK_global_user_stats);
834+}
835+
836+// Determines the concurrent number of connections of current threads.
837+void set_concurrent_connections_stats() {
838+ USER_STATS* user_stats;
839+
840+ pthread_mutex_lock(&LOCK_global_user_stats);
841+ pthread_mutex_lock(&LOCK_thread_count);
842+
843+ // Resets all concurrent connections to 0.
844+ for (int i = 0; i < global_user_stats.records; ++i) {
845+ user_stats = (USER_STATS*)hash_element(&global_user_stats, i);
846+ user_stats->concurrent_connections = 0;
847+ }
848+
849+ I_List_iterator<THD> it(threads);
850+ THD* thd;
851+ // Iterates through the current threads.
852+ while ((thd = it++)) {
853+ char* user_string = get_valid_user_string(thd->main_security_ctx.user);
854+ if ((user_stats = (USER_STATS*)hash_search(&global_user_stats,
855+ (uchar*)user_string,
856+ strlen(user_string)))) {
857+ // Found user.
858+ user_stats->concurrent_connections++;
859+ update_global_user_stats_with_user(thd, user_stats);
860+ thd->reset_diff_stats();
861+ } else {
862+ // The user name should exist.
863+ increment_connection_count(thd, false);
864+ }
865+ }
866+ pthread_mutex_unlock(&LOCK_thread_count);
867+ pthread_mutex_unlock(&LOCK_global_user_stats);
868+}
869+
870+
871 /**
872 check for global access and give descriptive error message if it fails.
873
874@@ -5539,6 +5764,10 @@
875 reset_dynamic(&thd->user_var_events);
876 thd->user_var_events_alloc= thd->mem_root;
877 }
878+
879+ thd->updated_row_count=0;
880+ thd->busy_time=0;
881+
882 thd->clear_error();
883 thd->main_da.reset_diagnostics_area();
884 thd->total_warn_count=0; // Warnings for this query
885@@ -5722,6 +5951,16 @@
886 DBUG_ENTER("mysql_parse");
887
888 DBUG_EXECUTE_IF("parser_debug", turn_parser_debug_on(););
889+
890+ int start_time_error = 0;
891+ int end_time_error = 0;
892+ struct timeval start_time, end_time;
893+ double start_usecs = 0;
894+ double end_usecs = 0;
895+ // Gets the start time, in order to measure how long this command takes.
896+ if (!(start_time_error = gettimeofday(&start_time, NULL))) {
897+ start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec;
898+ }
899
900 /*
901 Warning.
902@@ -5816,6 +6055,27 @@
903 *found_semicolon= NULL;
904 }
905
906+ // Gets the end time.
907+ if (!(end_time_error = gettimeofday(&end_time, NULL))) {
908+ end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec;
909+ }
910+
911+ // Calculates the difference between the end and start times.
912+ if (end_usecs >= start_usecs && !start_time_error && !end_time_error) {
913+ thd->busy_time = (end_usecs - start_usecs) / 1000000;
914+ // In case there are bad values, 2629743 is the #seconds in a month.
915+ if (thd->busy_time > 2629743) {
916+ thd->busy_time = 0;
917+ }
918+ } else {
919+ // end time went back in time, or gettimeofday() failed.
920+ thd->busy_time = 0;
921+ }
922+
923+ // Updates THD stats and the global user stats.
924+ thd->update_stats();
925+ update_global_user_stats(thd);
926+
927 DBUG_VOID_RETURN;
928 }
929
930@@ -6398,6 +6658,7 @@
931 tables->lock_type= lock_type;
932 tables->updating= for_update;
933 }
934+
935 DBUG_VOID_RETURN;
936 }
937
938@@ -6779,6 +7040,21 @@
939 #endif
940 if (options & REFRESH_USER_RESOURCES)
941 reset_mqh((LEX_USER *) NULL, 0); /* purecov: inspected */
942+ if (options & REFRESH_TABLE_STATS)
943+ {
944+ pthread_mutex_lock(&LOCK_global_table_stats);
945+ free_global_table_stats();
946+ init_global_table_stats();
947+ pthread_mutex_unlock(&LOCK_global_table_stats);
948+ }
949+ if (options & REFRESH_INDEX_STATS)
950+ {
951+ pthread_mutex_lock(&LOCK_global_index_stats);
952+ free_global_index_stats();
953+ init_global_index_stats();
954+ pthread_mutex_unlock(&LOCK_global_index_stats);
955+ }
956+
957 *write_to_binlog= tmp_write_to_binlog;
958 return result;
959 }
960diff -r 3ed7e96969f9 sql/sql_show.cc
961--- a/sql/sql_show.cc Thu Dec 04 08:54:17 2008 -0800
962+++ b/sql/sql_show.cc Thu Dec 04 08:54:27 2008 -0800
963@@ -2260,6 +2260,90 @@
964 DBUG_RETURN(FALSE);
965 }
966
967+
968+int fill_schema_user_stats(THD* thd, TABLE_LIST* tables, COND* cond)
969+{
970+ TABLE *table= tables->table;
971+ DBUG_ENTER("fill_schema_user_stats");
972+
973+ set_concurrent_connections_stats();
974+
975+ pthread_mutex_lock(&LOCK_global_user_stats);
976+ for (int i = 0; i < global_user_stats.records; ++i) {
977+ restore_record(table, s->default_values);
978+ USER_STATS *user_stats = (USER_STATS*)hash_element(&global_user_stats, i);
979+ table->field[0]->store(user_stats->user, strlen(user_stats->user), system_charset_info);
980+ table->field[1]->store((longlong)user_stats->total_connections, TRUE);
981+ table->field[2]->store((longlong)user_stats->concurrent_connections, TRUE);
982+ table->field[3]->store((longlong)user_stats->connected_time, TRUE);
983+ table->field[4]->store((longlong)user_stats->busy_time, TRUE);
984+ table->field[5]->store((longlong)user_stats->rows_fetched, TRUE);
985+ table->field[6]->store((longlong)user_stats->rows_updated, TRUE);
986+ table->field[7]->store((longlong)user_stats->select_commands, TRUE);
987+ table->field[8]->store((longlong)user_stats->update_commands, TRUE);
988+ table->field[9]->store((longlong)user_stats->other_commands, TRUE);
989+ table->field[10]->store((longlong)user_stats->commit_trans, TRUE);
990+ table->field[11]->store((longlong)user_stats->rollback_trans, TRUE);
991+
992+ if (schema_table_store_record(thd, table))
993+ {
994+ VOID(pthread_mutex_unlock(&LOCK_global_user_stats));
995+ DBUG_RETURN(1);
996+ }
997+ }
998+ pthread_mutex_unlock(&LOCK_global_user_stats);
999+ DBUG_RETURN(0);
1000+}
1001+
1002+
1003+int fill_schema_table_stats(THD* thd, TABLE_LIST* tables, COND* cond)
1004+{
1005+ TABLE *table= tables->table;
1006+ DBUG_ENTER("fill_schema_table_stats");
1007+
1008+ pthread_mutex_lock(&LOCK_global_table_stats);
1009+ for (int i = 0; i < global_table_stats.records; ++i) {
1010+ restore_record(table, s->default_values);
1011+ TABLE_STATS *table_stats =
1012+ (TABLE_STATS*)hash_element(&global_table_stats, i);
1013+ table->field[0]->store(table_stats->table, strlen(table_stats->table), system_charset_info);
1014+ table->field[1]->store((longlong)table_stats->rows_read, TRUE);
1015+ table->field[2]->store((longlong)table_stats->rows_changed, TRUE);
1016+ table->field[3]->store((longlong)table_stats->rows_changed_x_indexes, TRUE);
1017+
1018+ if (schema_table_store_record(thd, table))
1019+ {
1020+ VOID(pthread_mutex_unlock(&LOCK_global_table_stats));
1021+ DBUG_RETURN(1);
1022+ }
1023+ }
1024+ pthread_mutex_unlock(&LOCK_global_table_stats);
1025+ DBUG_RETURN(0);
1026+}
1027+
1028+
1029+int fill_schema_index_stats(THD* thd, TABLE_LIST* tables, COND* cond)
1030+{
1031+ TABLE *table= tables->table;
1032+ DBUG_ENTER("fill_schema_index_stats");
1033+
1034+ pthread_mutex_lock(&LOCK_global_index_stats);
1035+ for (int i = 0; i < global_index_stats.records; ++i) {
1036+ restore_record(table, s->default_values);
1037+ INDEX_STATS *index_stats =
1038+ (INDEX_STATS*)hash_element(&global_index_stats, i);
1039+ table->field[0]->store(index_stats->index, strlen(index_stats->index), system_charset_info);
1040+ table->field[1]->store((longlong)index_stats->rows_read, TRUE);
1041+
1042+ if (schema_table_store_record(thd, table))
1043+ {
1044+ VOID(pthread_mutex_unlock(&LOCK_global_index_stats));
1045+ DBUG_RETURN(1);
1046+ }
1047+ }
1048+ pthread_mutex_unlock(&LOCK_global_index_stats);
1049+ DBUG_RETURN(0);
1050+}
1051
1052 /* collect status for all running threads */
1053
1054@@ -6606,6 +6690,38 @@
1055 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1056 };
1057
1058+ST_FIELD_INFO user_stats_fields_info[]=
1059+{
1060+ {"USER", 16, MYSQL_TYPE_STRING, 0, 0, "User", SKIP_OPEN_TABLE},
1061+ {"TOTAL_CONNECTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Total_connections", SKIP_OPEN_TABLE},
1062+ {"CONCURRENT_CONNECTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Concurrent_connections", SKIP_OPEN_TABLE},
1063+ {"CONNECTED_TIME", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Connected_time", SKIP_OPEN_TABLE},
1064+ {"BUSY_TIME", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Busy_time", SKIP_OPEN_TABLE},
1065+ {"ROWS_FETCHED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_fetched", SKIP_OPEN_TABLE},
1066+ {"ROWS_UPDATED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_updated", SKIP_OPEN_TABLE},
1067+ {"SELECT_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Select_commands", SKIP_OPEN_TABLE},
1068+ {"UPDATE_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Update_commands", SKIP_OPEN_TABLE},
1069+ {"OTHER_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Other_commands", SKIP_OPEN_TABLE},
1070+ {"COMMIT_TRANSACTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Commit_transactions", SKIP_OPEN_TABLE},
1071+ {"ROLLBACK_TRANSACTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rollback_transactions", SKIP_OPEN_TABLE},
1072+ {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1073+};
1074+
1075+ST_FIELD_INFO table_stats_fields_info[]=
1076+{
1077+ {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_name", SKIP_OPEN_TABLE},
1078+ {"ROWS_READ", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE},
1079+ {"ROWS_CHANGED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_changed", SKIP_OPEN_TABLE},
1080+ {"ROWS_CHANGED_INDEXES", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_changed_x_#indexes", SKIP_OPEN_TABLE},
1081+ {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1082+};
1083+
1084+ST_FIELD_INFO index_stats_fields_info[]=
1085+{
1086+ {"INDEX_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Index_name", SKIP_OPEN_TABLE},
1087+ {"ROWS_READ", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE},
1088+ {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1089+};
1090
1091 /*
1092 Description of ST_FIELD_INFO in table.h
1093@@ -6824,6 +6824,8 @@
1094 fill_status, make_old_format, 0, 0, -1, 0, 0},
1095 {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,
1096 fill_variables, make_old_format, 0, 0, -1, 0, 0},
1097+ {"INDEX_STATISTICS", index_stats_fields_info, create_schema_table,
1098+ fill_schema_index_stats, make_old_format, 0, -1, -1, 0, 0},
1099 {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
1100 get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0,
1101 OPEN_TABLE_ONLY},
1102@@ -6683,11 +6801,15 @@
1103 get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
1104 {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
1105 fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
1106+ {"TABLE_STATISTICS", table_stats_fields_info, create_schema_table,
1107+ fill_schema_table_stats, make_old_format, 0, -1, -1, 0, 0},
1108 {"TRIGGERS", triggers_fields_info, create_schema_table,
1109 get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,
1110 OPEN_TABLE_ONLY},
1111 {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table,
1112 fill_schema_user_privileges, 0, 0, -1, -1, 0, 0},
1113+ {"USER_STATISTICS", user_stats_fields_info, create_schema_table,
1114+ fill_schema_user_stats, make_old_format, 0, -1, -1, 0, 0},
1115 {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
1116 make_old_format, 0, -1, -1, 1, 0},
1117 {"VIEWS", view_fields_info, create_schema_table,
1118diff -r 3ed7e96969f9 sql/sql_update.cc
1119--- a/sql/sql_update.cc Thu Dec 04 08:54:17 2008 -0800
1120+++ b/sql/sql_update.cc Thu Dec 04 08:54:27 2008 -0800
1121@@ -816,6 +816,7 @@
1122 thd->row_count_func=
1123 (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
1124 my_ok(thd, (ulong) thd->row_count_func, id, buff);
1125+ thd->updated_row_count += thd->row_count_func;
1126 DBUG_PRINT("info",("%ld records updated", (long) updated));
1127 }
1128 thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
1129@@ -2038,5 +2039,6 @@
1130 thd->row_count_func=
1131 (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
1132 ::my_ok(thd, (ulong) thd->row_count_func, id, buff);
1133+ thd->updated_row_count += thd->row_count_func;
1134 DBUG_RETURN(FALSE);
1135 }
1136diff -r 3ed7e96969f9 sql/sql_yacc.yy
1137--- a/sql/sql_yacc.yy Thu Dec 04 08:54:17 2008 -0800
1138+++ b/sql/sql_yacc.yy Thu Dec 04 08:54:27 2008 -0800
1139@@ -738,6 +738,7 @@
1140 %token IMPORT
1141 %token INDEXES
1142 %token INDEX_SYM
1143+%token INDEX_STATS_SYM
1144 %token INFILE
1145 %token INITIAL_SIZE_SYM
1146 %token INNER_SYM /* SQL-2003-R */
1147@@ -1026,6 +1027,7 @@
1148 %token TABLE_REF_PRIORITY
1149 %token TABLE_SYM /* SQL-2003-R */
1150 %token TABLE_CHECKSUM_SYM
1151+%token TABLE_STATS_SYM
1152 %token TEMPORARY /* SQL-2003-N */
1153 %token TEMPTABLE_SYM
1154 %token TERMINATED
1155@@ -1071,6 +1073,7 @@
1156 %token UPGRADE_SYM
1157 %token USAGE /* SQL-2003-N */
1158 %token USER /* SQL-2003-R */
1159+%token USER_STATS_SYM
1160 %token USE_FRM
1161 %token USE_SYM
1162 %token USING /* SQL-2003-R */
1163@@ -10090,6 +10093,27 @@
1164 {
1165 Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
1166 }
1167+ | USER_STATS_SYM wild_and_where
1168+ {
1169+ LEX *lex= Lex;
1170+ lex->sql_command= SQLCOM_SHOW_USER_STATS;
1171+ if (prepare_schema_table(YYTHD, lex, 0, SCH_USER_STATS))
1172+ MYSQL_YYABORT;
1173+ }
1174+ | TABLE_STATS_SYM wild_and_where
1175+ {
1176+ LEX *lex= Lex;
1177+ lex->sql_command= SQLCOM_SHOW_TABLE_STATS;
1178+ if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_STATS))
1179+ MYSQL_YYABORT;
1180+ }
1181+ | INDEX_STATS_SYM wild_and_where
1182+ {
1183+ LEX *lex= Lex;
1184+ lex->sql_command= SQLCOM_SHOW_INDEX_STATS;
1185+ if (prepare_schema_table(YYTHD, lex, 0, SCH_INDEX_STATS))
1186+ MYSQL_YYABORT;
1187+ }
1188 | CREATE PROCEDURE sp_name
1189 {
1190 LEX *lex= Lex;
1191@@ -10304,6 +10328,10 @@
1192 { Lex->type|= REFRESH_DES_KEY_FILE; }
1193 | RESOURCES
1194 { Lex->type|= REFRESH_USER_RESOURCES; }
1195+ | TABLE_STATS_SYM
1196+ { Lex->type|= REFRESH_TABLE_STATS; }
1197+ | INDEX_STATS_SYM
1198+ { Lex->type|= REFRESH_INDEX_STATS; }
1199 ;
1200
1201 opt_table_list:
1202diff -r 3ed7e96969f9 sql/structs.h
1203--- a/sql/structs.h Thu Dec 04 08:54:17 2008 -0800
1204+++ b/sql/structs.h Thu Dec 04 08:54:27 2008 -0800
1205@@ -226,6 +226,28 @@
1206 /* Maximum amount of resources which account is allowed to consume. */
1207 USER_RESOURCES user_resources;
1208 } USER_CONN;
1209+
1210+typedef struct st_user_stats {
1211+ char user[USERNAME_LENGTH + 1];
1212+ uint total_connections;
1213+ uint concurrent_connections;
1214+ time_t connected_time; // in seconds
1215+ double busy_time; // in seconds
1216+ ha_rows rows_fetched, rows_updated;
1217+ ulonglong select_commands, update_commands, other_commands;
1218+ ulonglong commit_trans, rollback_trans;
1219+} USER_STATS;
1220+
1221+typedef struct st_table_stats {
1222+ char table[NAME_LEN * 2 + 2]; // [db] + '.' + [table] + '\0'
1223+ ulonglong rows_read, rows_changed;
1224+ ulonglong rows_changed_x_indexes;
1225+} TABLE_STATS;
1226+
1227+typedef struct st_index_stats {
1228+ char index[NAME_LEN * 3 + 3]; // [db] + '.' + [table] + '.' + [index] + '\0'
1229+ ulonglong rows_read;
1230+} INDEX_STATS;
1231
1232 /* Bits in form->update */
1233 #define REG_MAKE_DUPP 1 /* Make a copy of record when read */
1234diff -r 3ed7e96969f9 sql/table.h
1235--- a/sql/table.h Thu Dec 04 08:54:17 2008 -0800
1236+++ b/sql/table.h Thu Dec 04 08:54:27 2008 -0800
1237@@ -879,6 +879,7 @@
1238 SCH_FILES,
1239 SCH_GLOBAL_STATUS,
1240 SCH_GLOBAL_VARIABLES,
1241+ SCH_INDEX_STATS,
1242 SCH_KEY_COLUMN_USAGE,
1243 SCH_OPEN_TABLES,
1244 SCH_PARTITIONS,
1245@@ -897,8 +898,10 @@
1246 SCH_TABLE_CONSTRAINTS,
1247 SCH_TABLE_NAMES,
1248 SCH_TABLE_PRIVILEGES,
1249+ SCH_TABLE_STATS,
1250 SCH_TRIGGERS,
1251 SCH_USER_PRIVILEGES,
1252+ SCH_USER_STATS,
1253 SCH_VARIABLES,
1254 SCH_VIEWS
1255 };
1256diff -r 3ed7e96969f9 storage/innobase/handler/ha_innodb.cc
1257--- a/storage/innobase/handler/ha_innodb.cc Thu Dec 04 08:54:17 2008 -0800
1258+++ b/storage/innobase/handler/ha_innodb.cc Thu Dec 04 08:54:27 2008 -0800
1259@@ -3494,6 +3494,8 @@
1260
1261 error = row_insert_for_mysql((byte*) record, prebuilt);
1262
1263+ if (error == DB_SUCCESS) rows_changed++;
1264+
1265 /* Handle duplicate key errors */
1266 if (auto_inc_used) {
1267 ulint err;
1268@@ -3571,6 +3573,8 @@
1269 break;
1270 }
1271 }
1272+
1273+ if (error == DB_SUCCESS) rows_changed++;
1274
1275 innodb_srv_conc_exit_innodb(prebuilt->trx);
1276
1277@@ -4178,6 +4182,8 @@
1278 ret = row_search_for_mysql((byte*) buf, mode, prebuilt,
1279 match_mode, 0);
1280
1281+ if (error == DB_SUCCESS) rows_changed++;
1282+
1283 innodb_srv_conc_exit_innodb(prebuilt->trx);
1284 } else {
1285
1286@@ -4187,6 +4193,9 @@
1287 if (ret == DB_SUCCESS) {
1288 error = 0;
1289 table->status = 0;
1290+ rows_read++;
1291+ if (active_index >= 0 && active_index < MAX_KEY)
1292+ index_rows_read[active_index]++;
1293
1294 } else if (ret == DB_RECORD_NOT_FOUND) {
1295 error = HA_ERR_KEY_NOT_FOUND;
1296@@ -4360,6 +4369,9 @@
1297 if (ret == DB_SUCCESS) {
1298 error = 0;
1299 table->status = 0;
1300+ rows_read++;
1301+ if (active_index >= 0 && active_index < MAX_KEY)
1302+ index_rows_read[active_index]++;
1303
1304 } else if (ret == DB_RECORD_NOT_FOUND) {
1305 error = HA_ERR_END_OF_FILE;
1306diff -r 3ed7e96969f9 storage/myisam/ha_myisam.cc
1307--- a/storage/myisam/ha_myisam.cc Thu Dec 04 08:54:17 2008 -0800
1308+++ b/storage/myisam/ha_myisam.cc Thu Dec 04 08:54:27 2008 -0800
1309@@ -738,7 +738,9 @@
1310 if ((error= update_auto_increment()))
1311 return error;
1312 }
1313- return mi_write(file,buf);
1314+ int error=mi_write(file,buf);
1315+ if (!error) rows_changed++;
1316+ return error;
1317 }
1318
1319 int ha_myisam::check(THD* thd, HA_CHECK_OPT* check_opt)
1320@@ -1589,13 +1591,17 @@
1321 ha_statistic_increment(&SSV::ha_update_count);
1322 if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
1323 table->timestamp_field->set_time();
1324- return mi_update(file,old_data,new_data);
1325+ int error=mi_update(file,old_data,new_data);
1326+ if (!error) rows_changed++;
1327+ return error;
1328 }
1329
1330 int ha_myisam::delete_row(const uchar *buf)
1331 {
1332 ha_statistic_increment(&SSV::ha_delete_count);
1333- return mi_delete(file,buf);
1334+ int error=mi_delete(file,buf);
1335+ if (!error) rows_changed++;
1336+ return error;
1337 }
1338
1339 int ha_myisam::index_read_map(uchar *buf, const uchar *key,
1340@@ -1606,6 +1612,13 @@
1341 ha_statistic_increment(&SSV::ha_read_key_count);
1342 int error=mi_rkey(file, buf, active_index, key, keypart_map, find_flag);
1343 table->status=error ? STATUS_NOT_FOUND: 0;
1344+ if (!error) {
1345+ rows_read++;
1346+
1347+ int inx = (active_index == -1) ? file->lastinx : active_index;
1348+ if (inx >= 0 && inx < MAX_KEY)
1349+ index_rows_read[inx]++;
1350+ }
1351 return error;
1352 }
1353
1354@@ -1616,6 +1629,14 @@
1355 ha_statistic_increment(&SSV::ha_read_key_count);
1356 int error=mi_rkey(file, buf, index, key, keypart_map, find_flag);
1357 table->status=error ? STATUS_NOT_FOUND: 0;
1358+ if (!error) {
1359+ rows_read++;
1360+
1361+// int inx = (active_index == -1) ? file->lastinx : active_index;
1362+ int inx = index;
1363+ if (inx >= 0 && inx < MAX_KEY)
1364+ index_rows_read[inx]++;
1365+ }
1366 return error;
1367 }
1368
1369@@ -1637,6 +1658,13 @@
1370 ha_statistic_increment(&SSV::ha_read_next_count);
1371 int error=mi_rnext(file,buf,active_index);
1372 table->status=error ? STATUS_NOT_FOUND: 0;
1373+ if (!error) {
1374+ rows_read++;
1375+
1376+ int inx = (active_index == -1) ? file->lastinx : active_index;
1377+ if (inx >= 0 && inx < MAX_KEY)
1378+ index_rows_read[inx]++;
1379+ }
1380 return error;
1381 }
1382
1383@@ -1646,6 +1674,13 @@
1384 ha_statistic_increment(&SSV::ha_read_prev_count);
1385 int error=mi_rprev(file,buf, active_index);
1386 table->status=error ? STATUS_NOT_FOUND: 0;
1387+ if (!error) {
1388+ rows_read++;
1389+
1390+ int inx = (active_index == -1) ? file->lastinx : active_index;
1391+ if (inx >= 0 && inx < MAX_KEY)
1392+ index_rows_read[inx]++;
1393+ }
1394 return error;
1395 }
1396
1397@@ -1655,6 +1690,13 @@
1398 ha_statistic_increment(&SSV::ha_read_first_count);
1399 int error=mi_rfirst(file, buf, active_index);
1400 table->status=error ? STATUS_NOT_FOUND: 0;
1401+ if (!error) {
1402+ rows_read++;
1403+
1404+ int inx = (active_index == -1) ? file->lastinx : active_index;
1405+ if (inx >= 0 && inx < MAX_KEY)
1406+ index_rows_read[inx]++;
1407+ }
1408 return error;
1409 }
1410
1411@@ -1664,6 +1706,13 @@
1412 ha_statistic_increment(&SSV::ha_read_last_count);
1413 int error=mi_rlast(file, buf, active_index);
1414 table->status=error ? STATUS_NOT_FOUND: 0;
1415+ if (!error) {
1416+ rows_read++;
1417+
1418+ int inx = (active_index == -1) ? file->lastinx : active_index;
1419+ if (inx >= 0 && inx < MAX_KEY)
1420+ index_rows_read[inx]++;
1421+ }
1422 return error;
1423 }
1424
1425@@ -1679,6 +1728,20 @@
1426 error= mi_rnext_same(file,buf);
1427 } while (error == HA_ERR_RECORD_DELETED);
1428 table->status=error ? STATUS_NOT_FOUND: 0;
1429+ if (!error) {
1430+ rows_read++;
1431+
1432+ int inx = (active_index == -1) ? file->lastinx : active_index;
1433+ if (inx >= 0 && inx < MAX_KEY)
1434+ index_rows_read[inx]++;
1435+ }
1436+ if (!error) {
1437+ rows_read++;
1438+
1439+ int inx = (active_index == -1) ? file->lastinx : active_index;
1440+ if (inx >= 0 && inx < MAX_KEY)
1441+ index_rows_read[inx]++;
1442+ }
1443 return error;
1444 }
1445
1446@@ -1695,6 +1758,7 @@
1447 ha_statistic_increment(&SSV::ha_read_rnd_next_count);
1448 int error=mi_scan(file, buf);
1449 table->status=error ? STATUS_NOT_FOUND: 0;
1450+ if (!error) rows_read++;
1451 return error;
1452 }
1453
1454@@ -1708,6 +1772,7 @@
1455 ha_statistic_increment(&SSV::ha_read_rnd_count);
1456 int error=mi_rrnd(file, buf, my_get_ptr(pos,ref_length));
1457 table->status=error ? STATUS_NOT_FOUND: 0;
1458+ if (!error) rows_read++;
1459 return error;
1460 }
1461
MySQL: a very fast and reliable SQL database engine
This page took 0.17081 seconds and 4 git commands to generate.