1 diff -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
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 */
11 /* The following can't be set with mysql_refresh() */
12 #define REFRESH_READ_LOCK 16384 /* Lock tables for read */
13 diff -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
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 */
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 */
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 */
41 dup_ref=ref+ALIGN_SIZE(ref_length);
42 cached_table_flags= table_flags();
44 + rows_read = rows_changed = 0;
45 + memset(index_rows_read, 0, sizeof(index_rows_read));
49 @@ -3487,6 +3492,97 @@
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;
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);
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,
69 + if (!(table_stats = ((TABLE_STATS*)
70 + my_malloc(sizeof(TABLE_STATS), MYF(MY_WME))))) {
72 + sql_print_error("Allocating table stats failed.");
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;
80 + if (my_hash_insert(&global_table_stats, (uchar*)table_stats)) {
82 + sql_print_error("Inserting table stats failed.");
83 + my_free((char*)table_stats, 0);
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;
94 + pthread_mutex_unlock(&LOCK_global_table_stats);
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;
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];
107 + if (!key_info->name) continue;
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);
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,
120 + if (!(index_stats = ((INDEX_STATS*)
121 + my_malloc(sizeof(INDEX_STATS), MYF(MY_WME))))) {
123 + sql_print_error("Allocating index stats failed.");
126 + strncpy(index_stats->index, key, sizeof(index_stats->index));
127 + index_stats->rows_read = 0;
129 + if (my_hash_insert(&global_index_stats, (uchar*)index_stats)) {
131 + sql_print_error("Inserting index stats failed.");
132 + my_free((char*)index_stats, 0);
136 + // Updates the global index stats.
137 + index_stats->rows_read += index_rows_read[x];
138 + index_rows_read[x] = 0;
140 + pthread_mutex_unlock(&LOCK_global_index_stats);
145 /****************************************************************************
146 ** Some general functions that isn't in the handler class
147 diff -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
153 #define USING_TRANSACTIONS
156 +#error MAX_KEY is too large. Values up to 128 are supported.
159 // the following is for checking tables
163 enum log_status (*get_log_status)(handlerton *hton, char *log);
168 Presence of the pointer should be checked before using
169 @@ -1137,6 +1142,10 @@
171 uint auto_inc_intervals_count;
173 + ulonglong rows_read;
174 + ulonglong rows_changed;
175 + ulonglong index_rows_read[MAX_KEY];
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)
186 + auto_inc_intervals_count(0), rows_read(0), rows_changed(0)
188 + memset(index_rows_read, 0, sizeof(index_rows_read));
190 virtual ~handler(void)
192 DBUG_ASSERT(locked == FALSE);
193 @@ -1267,7 +1278,13 @@
197 + rows_read = rows_changed = 0;
198 + memset(index_rows_read, 0, sizeof(index_rows_read));
201 + void update_global_table_stats();
202 + void update_global_index_stats();
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)
207 diff -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
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)},
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)},
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)},
234 diff -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;
268 extern const char *opt_date_time_formats[];
269 extern KNOWN_DATE_TIME_FORMAT known_date_time_formats[];
270 diff -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
274 LOCK_global_system_variables,
275 LOCK_user_conn, LOCK_slave_list, LOCK_active_mi,
276 LOCK_connection_count;
278 +pthread_mutex_t LOCK_global_user_stats;
279 +pthread_mutex_t LOCK_global_table_stats;
280 +pthread_mutex_t LOCK_global_index_stats;
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
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);
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 @@
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);
333 #ifdef HAVE_EVENT_SCHEDULER
334 Events::init_mutexes();
335 @@ -3872,6 +3889,9 @@
339 + init_global_table_stats();
340 + init_global_index_stats();
342 /* We have to initialize the storage engines before CSV logging */
345 @@ -4018,6 +4038,7 @@
347 init_max_user_conn();
348 init_update_queries();
349 + init_global_user_stats();
353 diff -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));
362 + table->file->update_global_table_stats();
363 + table->file->update_global_index_stats();
366 *table_ptr=table->next;
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));
374 + table->file->update_global_table_stats();
375 + table->file->update_global_index_stats();
377 free_io_cache(table);
379 diff -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
383 bzero(ha_data, sizeof(ha_data));
385 binlog_evt_union.do_union= FALSE;
387 + updated_row_count = 0;
390 dbug_sentry=THD_SENTRY_MAGIC;
392 reset_current_stmt_binlog_row_based();
393 bzero((char *) &status_var, sizeof(status_var));
394 sql_log_bin_toplevel= options & OPTION_BIN_LOG;
397 #if defined(ENABLED_DEBUG_SYNC)
398 /* Initialize the Debug Sync Facility. See debug_sync.cc. */
400 #endif /* defined(ENABLED_DEBUG_SYNC) */
403 +// Resets stats in a THD.
404 +void THD::reset_stats(void) {
405 + current_connect_time = time(NULL);
406 + last_global_update_time = current_connect_time;
407 + reset_diff_stats();
410 +// Resets the 'diff' stats, which are used to update global stats.
411 +void THD::reset_diff_stats(void) {
412 + diff_total_busy_time = 0;
413 + diff_total_sent_rows = 0;
414 + diff_total_updated_rows = 0;
415 + diff_select_commands = 0;
416 + diff_update_commands = 0;
417 + diff_other_commands = 0;
418 + diff_commit_trans = 0;
419 + diff_rollback_trans = 0;
422 +// Updates 'diff' stats of a THD.
423 +void THD::update_stats() {
424 + diff_total_busy_time += busy_time;
425 + diff_total_sent_rows += sent_row_count;
426 + diff_total_updated_rows += updated_row_count;
427 + // The replication thread has the COM_CONNECT command.
428 + if ((old_command == COM_QUERY || command == COM_CONNECT) &&
429 + (lex->sql_command >= 0 && lex->sql_command < SQLCOM_END)) {
431 + if (lex->sql_command == SQLCOM_SELECT) {
432 + if (!(sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND)) {
433 + diff_select_commands++;
435 + // 'SHOW ' commands become SQLCOM_SELECT.
436 + diff_other_commands++;
437 + // 'SHOW ' commands shouldn't inflate total sent row count.
438 + diff_total_sent_rows -= sent_row_count;
440 + } else if (is_update_query(lex->sql_command)) {
441 + diff_update_commands++;
443 + diff_other_commands++;
446 + // diff_commit_trans is updated in handler.cc.
447 + // diff_rollback_trans is updated in handler.cc.
451 Init THD for query processing.
452 diff -r 3ed7e96969f9 sql/sql_class.h
453 --- a/sql/sql_class.h Thu Dec 04 08:54:17 2008 -0800
454 +++ b/sql/sql_class.h Thu Dec 04 08:54:27 2008 -0800
455 @@ -1327,6 +1327,8 @@
456 first byte of the packet in do_command()
458 enum enum_server_command command;
459 + // Used to save the command, before it is set to COM_SLEEP.
460 + enum enum_server_command old_command;
462 uint32 file_id; // for LOAD DATA INFILE
463 /* remote (peer) port */
464 @@ -1616,6 +1618,7 @@
465 ulonglong options; /* Bitmap of states */
466 longlong row_count_func; /* For the ROW_COUNT() function */
467 ha_rows cuted_fields;
468 + ha_rows updated_row_count;
471 number of rows we actually sent to the client, including "synthetic"
472 @@ -1767,6 +1770,27 @@
474 LOG_INFO* current_linfo;
475 NET* slave_net; // network connection from slave -> m.
478 + Used to update global user stats. The global user stats are updated
479 + occasionally with the 'diff' variables. After the update, the 'diff'
480 + variables are reset to 0.
482 + // Time when the current thread connected to MySQL.
483 + time_t current_connect_time;
484 + // Last time when THD stats were updated in global_user_stats.
485 + time_t last_global_update_time;
486 + // Busy (non-idle) time for just one command.
488 + // Busy time not updated in global_user_stats yet.
489 + double diff_total_busy_time;
490 + // Number of rows not reflected in global_user_stats yet.
491 + ha_rows diff_total_sent_rows, diff_total_updated_rows;
492 + // Number of commands not reflected in global_user_stats yet.
493 + ulonglong diff_select_commands, diff_update_commands, diff_other_commands;
494 + // Number of transactions not reflected in global_user_stats yet.
495 + ulonglong diff_commit_trans, diff_rollback_trans;
497 /* Used by the sys_var class to store temporary values */
500 @@ -1827,6 +1851,9 @@
503 void init_for_queries();
504 + void reset_stats(void);
505 + void reset_diff_stats(void);
506 + void update_stats(void);
507 void change_user(void);
509 void cleanup_after_query();
510 diff -r 3ed7e96969f9 sql/sql_connect.cc
511 --- a/sql/sql_connect.cc Thu Dec 04 08:54:17 2008 -0800
512 +++ b/sql/sql_connect.cc Thu Dec 04 08:54:27 2008 -0800
515 (hash_get_key) get_key_conn, (hash_free_key) free_user,
517 + if (hash_init(&hash_user_connections,system_charset_info,max_connections,
519 + (hash_get_key) get_key_conn, (hash_free_key) free_user,
521 + sql_print_error("Initializing hash_user_connections failed.");
528 @@ -1107,6 +1115,13 @@
529 if (login_connection(thd))
532 + thd->reset_stats();
533 + // Updates global user connection stats.
534 + if (increment_connection_count(thd, true)) {
535 + net_send_error(thd, ER_OUTOFMEMORY); // Out of memory
539 prepare_new_connection_state(thd);
541 while (!net->error && net->vio != 0 &&
542 @@ -1119,6 +1134,8 @@
545 close_connection(thd, 0, 1);
546 + thd->update_stats();
547 + update_global_user_stats(thd);
548 if (thread_scheduler.end_thread(thd,1))
549 return 0; // Probably no-threads
551 diff -r 3ed7e96969f9 sql/sql_delete.cc
552 --- a/sql/sql_delete.cc Thu Dec 04 08:54:17 2008 -0800
553 +++ b/sql/sql_delete.cc Thu Dec 04 08:54:27 2008 -0800
555 my_ok(thd, (ha_rows) thd->row_count_func);
556 DBUG_PRINT("info",("%ld records deleted",(long) deleted));
558 + thd->updated_row_count += deleted;
559 DBUG_RETURN(error >= 0 || thd->is_error());
563 thd->row_count_func= deleted;
564 ::my_ok(thd, (ha_rows) thd->row_count_func);
566 + thd->updated_row_count += deleted;
570 diff -r 3ed7e96969f9 sql/sql_insert.cc
571 --- a/sql/sql_insert.cc Thu Dec 04 08:54:17 2008 -0800
572 +++ b/sql/sql_insert.cc Thu Dec 04 08:54:27 2008 -0800
574 thd->row_count_func= info.copied + info.deleted + updated;
575 ::my_ok(thd, (ulong) thd->row_count_func, id, buff);
577 + thd->updated_row_count += thd->row_count_func;
578 thd->abort_on_warning= 0;
581 diff -r 3ed7e96969f9 sql/sql_lex.h
582 --- a/sql/sql_lex.h Thu Dec 04 08:54:17 2008 -0800
583 +++ b/sql/sql_lex.h Thu Dec 04 08:54:27 2008 -0800
585 SQLCOM_SHOW_CREATE_TRIGGER,
586 SQLCOM_ALTER_DB_UPGRADE,
587 SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
589 + SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS,
591 When a command is added here, be sure it's also added in mysqld.cc
592 in "struct show_var_st status_vars[]= {" ...
593 diff -r 3ed7e96969f9 sql/sql_parse.cc
594 --- a/sql/sql_parse.cc Thu Dec 04 08:54:17 2008 -0800
595 +++ b/sql/sql_parse.cc Thu Dec 04 08:54:27 2008 -0800
598 static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables);
599 static bool check_show_create_table_access(THD *thd, TABLE_LIST *table);
601 +HASH global_user_stats;
602 +extern pthread_mutex_t LOCK_global_user_stats;
604 +HASH global_table_stats;
605 +extern pthread_mutex_t LOCK_global_table_stats;
607 +HASH global_index_stats;
608 +extern pthread_mutex_t LOCK_global_index_stats;
610 const char *any_db="*any*"; // Special symbol for check_access
613 sql_command_flags[SQLCOM_SHOW_FUNC_CODE]= CF_STATUS_COMMAND;
614 sql_command_flags[SQLCOM_SHOW_CREATE_EVENT]= CF_STATUS_COMMAND;
615 sql_command_flags[SQLCOM_SHOW_PROFILES]= CF_STATUS_COMMAND;
616 - sql_command_flags[SQLCOM_SHOW_PROFILE]= CF_STATUS_COMMAND;
617 + sql_command_flags[SQLCOM_SHOW_USER_STATS]= CF_STATUS_COMMAND;
618 + sql_command_flags[SQLCOM_SHOW_TABLE_STATS]= CF_STATUS_COMMAND;
619 + sql_command_flags[SQLCOM_SHOW_INDEX_STATS]= CF_STATUS_COMMAND;
621 sql_command_flags[SQLCOM_SHOW_TABLES]= (CF_STATUS_COMMAND |
622 CF_SHOW_TABLE_COMMAND |
627 +extern "C" uchar *get_key_user_stats(USER_STATS *user_stats, size_t *length,
628 + my_bool not_used __attribute__((unused)))
630 + *length = strlen(user_stats->user);
631 + return (uchar*)user_stats->user;
634 +extern "C" void free_user_stats(USER_STATS* user_stats)
636 + my_free((char*)user_stats, MYF(0));
639 +void init_global_user_stats(void)
641 + if (hash_init(&global_user_stats, system_charset_info, max_connections,
642 + 0, 0, (hash_get_key)get_key_user_stats,
643 + (hash_free_key)free_user_stats, 0)) {
644 + sql_print_error("Initializing global_user_stats failed.");
649 +extern "C" uchar *get_key_table_stats(TABLE_STATS *table_stats, size_t *length,
650 + my_bool not_used __attribute__((unused)))
652 + *length = strlen(table_stats->table);
653 + return (uchar*)table_stats->table;
656 +extern "C" void free_table_stats(TABLE_STATS* table_stats)
658 + my_free((char*)table_stats, MYF(0));
661 +void init_global_table_stats(void)
663 + if (hash_init(&global_table_stats, system_charset_info, max_connections,
664 + 0, 0, (hash_get_key)get_key_table_stats,
665 + (hash_free_key)free_table_stats, 0)) {
666 + sql_print_error("Initializing global_table_stats failed.");
671 +extern "C" uchar *get_key_index_stats(INDEX_STATS *index_stats, size_t *length,
672 + my_bool not_used __attribute__((unused)))
674 + *length = strlen(index_stats->index);
675 + return (uchar*)index_stats->index;
678 +extern "C" void free_index_stats(INDEX_STATS* index_stats)
680 + my_free((char*)index_stats, MYF(0));
683 +void init_global_index_stats(void)
685 + if (hash_init(&global_index_stats, system_charset_info, max_connections,
686 + 0, 0, (hash_get_key)get_key_index_stats,
687 + (hash_free_key)free_index_stats, 0)) {
688 + sql_print_error("Initializing global_index_stats failed.");
693 +void free_global_user_stats(void)
695 + hash_free(&global_user_stats);
698 +void free_global_table_stats(void)
700 + hash_free(&global_table_stats);
703 +void free_global_index_stats(void)
705 + hash_free(&global_index_stats);
709 @brief Check access privs for a MERGE table and fix children lock types.
711 DBUG_PRINT("info",("packet: '%*.s'; command: %d", packet_length, packet, command));
713 thd->command=command;
714 + // To increment the correct command counter for user stats, 'command' must
715 + // be saved because it is set to COM_SLEEP at the end of this function.
716 + thd->old_command = command;
718 Commands which always take a long time are logged into
719 the slow log only if opt_log_slow_admin_statements is set.
720 @@ -1740,6 +1834,9 @@
721 case SCH_COLUMN_PRIVILEGES:
722 case SCH_TABLE_CONSTRAINTS:
723 case SCH_KEY_COLUMN_USAGE:
724 + case SCH_USER_STATS:
725 + case SCH_TABLE_STATS:
726 + case SCH_INDEX_STATS:
730 @@ -2129,6 +2226,9 @@
731 my_error(ER_NOT_SUPPORTED_YET, MYF(0), "embedded server");
734 + case SQLCOM_SHOW_USER_STATS:
735 + case SQLCOM_SHOW_TABLE_STATS:
736 + case SQLCOM_SHOW_INDEX_STATS:
737 case SQLCOM_SHOW_STATUS_PROC:
738 case SQLCOM_SHOW_STATUS_FUNC:
739 if (!(res= check_table_access(thd, SELECT_ACL, all_tables, UINT_MAX, FALSE)))
740 @@ -2306,6 +2406,7 @@
745 case SQLCOM_BACKUP_TABLE:
747 DBUG_ASSERT(first_table == all_tables && first_table != 0);
748 @@ -5372,6 +5473,130 @@
749 #endif /*NO_EMBEDDED_ACCESS_CHECKS*/
752 +// 'mysql_system_user' is used for when the user is not defined for a THD.
753 +static char mysql_system_user[] = "#mysql_system#";
755 +// Returns 'user' if it's not NULL. Returns 'mysql_system_user' otherwise.
756 +static char* get_valid_user_string(char* user) {
757 + return user ? user : mysql_system_user;
760 +// Increments the global user stats connection count. If 'use_lock' is true,
761 +// 'LOCK_global_user_stats' will be locked/unlocked. Returns 0 on success,
763 +int increment_connection_count(THD* thd, bool use_lock) {
764 + char* user_string = get_valid_user_string(thd->main_security_ctx.user);
766 + USER_STATS* user_stats;
767 + int return_value = 0;
769 + if (use_lock) pthread_mutex_lock(&LOCK_global_user_stats);
770 + if (!(user_stats = (USER_STATS*)hash_search(&global_user_stats,
771 + (uchar*)user_string,
772 + strlen(user_string)))) {
773 + // First connection for this user.
774 + if (!(user_stats = ((USER_STATS*)
775 + my_malloc(sizeof(USER_STATS), MYF(MY_WME))))) {
780 + strncpy(user_stats->user, user_string, sizeof(user_stats->user));
781 + user_stats->total_connections = 0;
782 + user_stats->concurrent_connections = 0;
783 + user_stats->connected_time = 0;
784 + user_stats->busy_time = 0;
785 + user_stats->rows_fetched = 0;
786 + user_stats->rows_updated = 0;
787 + user_stats->select_commands = 0;
788 + user_stats->update_commands = 0;
789 + user_stats->other_commands = 0;
790 + user_stats->commit_trans = 0;
791 + user_stats->rollback_trans = 0;
793 + if (my_hash_insert(&global_user_stats, (uchar*)user_stats)) {
795 + my_free((char*)user_stats, 0);
800 + user_stats->total_connections++;
802 + if (use_lock) pthread_mutex_unlock(&LOCK_global_user_stats);
803 + return return_value;
806 +// Used to update the global user stats.
807 +static void update_global_user_stats_with_user(THD* thd,
808 + USER_STATS* user_stats) {
809 + time_t current_time = time(NULL);
810 + user_stats->connected_time += current_time - thd->last_global_update_time;
811 + thd->last_global_update_time = current_time;
812 + user_stats->busy_time += thd->diff_total_busy_time;
813 + user_stats->rows_fetched += thd->diff_total_sent_rows;
814 + user_stats->rows_updated += thd->diff_total_updated_rows;
815 + user_stats->select_commands += thd->diff_select_commands;
816 + user_stats->update_commands += thd->diff_update_commands;
817 + user_stats->other_commands += thd->diff_other_commands;
818 + user_stats->commit_trans += thd->diff_commit_trans;
819 + user_stats->rollback_trans += thd->diff_rollback_trans;
822 +// Updates the global stats of a thread/user.
823 +void update_global_user_stats(THD* thd) {
824 + char* user_string = get_valid_user_string(thd->main_security_ctx.user);
826 + USER_STATS* user_stats;
827 + pthread_mutex_lock(&LOCK_global_user_stats);
828 + if ((user_stats = (USER_STATS*)hash_search(&global_user_stats,
829 + (uchar*)user_string,
830 + strlen(user_string)))) {
832 + update_global_user_stats_with_user(thd, user_stats);
833 + thd->reset_diff_stats();
835 + // The user name should exist.
836 + increment_connection_count(thd, false);
838 + pthread_mutex_unlock(&LOCK_global_user_stats);
841 +// Determines the concurrent number of connections of current threads.
842 +void set_concurrent_connections_stats() {
843 + USER_STATS* user_stats;
845 + pthread_mutex_lock(&LOCK_global_user_stats);
846 + pthread_mutex_lock(&LOCK_thread_count);
848 + // Resets all concurrent connections to 0.
849 + for (int i = 0; i < global_user_stats.records; ++i) {
850 + user_stats = (USER_STATS*)hash_element(&global_user_stats, i);
851 + user_stats->concurrent_connections = 0;
854 + I_List_iterator<THD> it(threads);
856 + // Iterates through the current threads.
857 + while ((thd = it++)) {
858 + char* user_string = get_valid_user_string(thd->main_security_ctx.user);
859 + if ((user_stats = (USER_STATS*)hash_search(&global_user_stats,
860 + (uchar*)user_string,
861 + strlen(user_string)))) {
863 + user_stats->concurrent_connections++;
864 + update_global_user_stats_with_user(thd, user_stats);
865 + thd->reset_diff_stats();
867 + // The user name should exist.
868 + increment_connection_count(thd, false);
871 + pthread_mutex_unlock(&LOCK_thread_count);
872 + pthread_mutex_unlock(&LOCK_global_user_stats);
877 check for global access and give descriptive error message if it fails.
879 @@ -5539,6 +5764,10 @@
880 reset_dynamic(&thd->user_var_events);
881 thd->user_var_events_alloc= thd->mem_root;
884 + thd->updated_row_count=0;
888 thd->main_da.reset_diagnostics_area();
889 thd->total_warn_count=0; // Warnings for this query
890 @@ -5722,6 +5951,16 @@
891 DBUG_ENTER("mysql_parse");
893 DBUG_EXECUTE_IF("parser_debug", turn_parser_debug_on(););
895 + int start_time_error = 0;
896 + int end_time_error = 0;
897 + struct timeval start_time, end_time;
898 + double start_usecs = 0;
899 + double end_usecs = 0;
900 + // Gets the start time, in order to measure how long this command takes.
901 + if (!(start_time_error = gettimeofday(&start_time, NULL))) {
902 + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec;
907 @@ -5816,6 +6055,27 @@
908 *found_semicolon= NULL;
911 + // Gets the end time.
912 + if (!(end_time_error = gettimeofday(&end_time, NULL))) {
913 + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec;
916 + // Calculates the difference between the end and start times.
917 + if (end_usecs >= start_usecs && !start_time_error && !end_time_error) {
918 + thd->busy_time = (end_usecs - start_usecs) / 1000000;
919 + // In case there are bad values, 2629743 is the #seconds in a month.
920 + if (thd->busy_time > 2629743) {
921 + thd->busy_time = 0;
924 + // end time went back in time, or gettimeofday() failed.
925 + thd->busy_time = 0;
928 + // Updates THD stats and the global user stats.
929 + thd->update_stats();
930 + update_global_user_stats(thd);
935 @@ -6398,6 +6658,7 @@
936 tables->lock_type= lock_type;
937 tables->updating= for_update;
943 @@ -6779,6 +7040,21 @@
945 if (options & REFRESH_USER_RESOURCES)
946 reset_mqh((LEX_USER *) NULL, 0); /* purecov: inspected */
947 + if (options & REFRESH_TABLE_STATS)
949 + pthread_mutex_lock(&LOCK_global_table_stats);
950 + free_global_table_stats();
951 + init_global_table_stats();
952 + pthread_mutex_unlock(&LOCK_global_table_stats);
954 + if (options & REFRESH_INDEX_STATS)
956 + pthread_mutex_lock(&LOCK_global_index_stats);
957 + free_global_index_stats();
958 + init_global_index_stats();
959 + pthread_mutex_unlock(&LOCK_global_index_stats);
962 *write_to_binlog= tmp_write_to_binlog;
965 diff -r 3ed7e96969f9 sql/sql_show.cc
966 --- a/sql/sql_show.cc Thu Dec 04 08:54:17 2008 -0800
967 +++ b/sql/sql_show.cc Thu Dec 04 08:54:27 2008 -0800
968 @@ -2260,6 +2260,90 @@
973 +int fill_schema_user_stats(THD* thd, TABLE_LIST* tables, COND* cond)
975 + TABLE *table= tables->table;
976 + DBUG_ENTER("fill_schema_user_stats");
978 + set_concurrent_connections_stats();
980 + pthread_mutex_lock(&LOCK_global_user_stats);
981 + for (int i = 0; i < global_user_stats.records; ++i) {
982 + restore_record(table, s->default_values);
983 + USER_STATS *user_stats = (USER_STATS*)hash_element(&global_user_stats, i);
984 + table->field[0]->store(user_stats->user, strlen(user_stats->user), system_charset_info);
985 + table->field[1]->store((longlong)user_stats->total_connections, TRUE);
986 + table->field[2]->store((longlong)user_stats->concurrent_connections, TRUE);
987 + table->field[3]->store((longlong)user_stats->connected_time, TRUE);
988 + table->field[4]->store((longlong)user_stats->busy_time, TRUE);
989 + table->field[5]->store((longlong)user_stats->rows_fetched, TRUE);
990 + table->field[6]->store((longlong)user_stats->rows_updated, TRUE);
991 + table->field[7]->store((longlong)user_stats->select_commands, TRUE);
992 + table->field[8]->store((longlong)user_stats->update_commands, TRUE);
993 + table->field[9]->store((longlong)user_stats->other_commands, TRUE);
994 + table->field[10]->store((longlong)user_stats->commit_trans, TRUE);
995 + table->field[11]->store((longlong)user_stats->rollback_trans, TRUE);
997 + if (schema_table_store_record(thd, table))
999 + VOID(pthread_mutex_unlock(&LOCK_global_user_stats));
1003 + pthread_mutex_unlock(&LOCK_global_user_stats);
1008 +int fill_schema_table_stats(THD* thd, TABLE_LIST* tables, COND* cond)
1010 + TABLE *table= tables->table;
1011 + DBUG_ENTER("fill_schema_table_stats");
1013 + pthread_mutex_lock(&LOCK_global_table_stats);
1014 + for (int i = 0; i < global_table_stats.records; ++i) {
1015 + restore_record(table, s->default_values);
1016 + TABLE_STATS *table_stats =
1017 + (TABLE_STATS*)hash_element(&global_table_stats, i);
1018 + table->field[0]->store(table_stats->table, strlen(table_stats->table), system_charset_info);
1019 + table->field[1]->store((longlong)table_stats->rows_read, TRUE);
1020 + table->field[2]->store((longlong)table_stats->rows_changed, TRUE);
1021 + table->field[3]->store((longlong)table_stats->rows_changed_x_indexes, TRUE);
1023 + if (schema_table_store_record(thd, table))
1025 + VOID(pthread_mutex_unlock(&LOCK_global_table_stats));
1029 + pthread_mutex_unlock(&LOCK_global_table_stats);
1034 +int fill_schema_index_stats(THD* thd, TABLE_LIST* tables, COND* cond)
1036 + TABLE *table= tables->table;
1037 + DBUG_ENTER("fill_schema_index_stats");
1039 + pthread_mutex_lock(&LOCK_global_index_stats);
1040 + for (int i = 0; i < global_index_stats.records; ++i) {
1041 + restore_record(table, s->default_values);
1042 + INDEX_STATS *index_stats =
1043 + (INDEX_STATS*)hash_element(&global_index_stats, i);
1044 + table->field[0]->store(index_stats->index, strlen(index_stats->index), system_charset_info);
1045 + table->field[1]->store((longlong)index_stats->rows_read, TRUE);
1047 + if (schema_table_store_record(thd, table))
1049 + VOID(pthread_mutex_unlock(&LOCK_global_index_stats));
1053 + pthread_mutex_unlock(&LOCK_global_index_stats);
1057 /* collect status for all running threads */
1059 @@ -6606,6 +6690,38 @@
1060 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1063 +ST_FIELD_INFO user_stats_fields_info[]=
1065 + {"USER", 16, MYSQL_TYPE_STRING, 0, 0, "User", SKIP_OPEN_TABLE},
1066 + {"TOTAL_CONNECTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Total_connections", SKIP_OPEN_TABLE},
1067 + {"CONCURRENT_CONNECTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Concurrent_connections", SKIP_OPEN_TABLE},
1068 + {"CONNECTED_TIME", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Connected_time", SKIP_OPEN_TABLE},
1069 + {"BUSY_TIME", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Busy_time", SKIP_OPEN_TABLE},
1070 + {"ROWS_FETCHED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_fetched", SKIP_OPEN_TABLE},
1071 + {"ROWS_UPDATED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_updated", SKIP_OPEN_TABLE},
1072 + {"SELECT_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Select_commands", SKIP_OPEN_TABLE},
1073 + {"UPDATE_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Update_commands", SKIP_OPEN_TABLE},
1074 + {"OTHER_COMMANDS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Other_commands", SKIP_OPEN_TABLE},
1075 + {"COMMIT_TRANSACTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Commit_transactions", SKIP_OPEN_TABLE},
1076 + {"ROLLBACK_TRANSACTIONS", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rollback_transactions", SKIP_OPEN_TABLE},
1077 + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1080 +ST_FIELD_INFO table_stats_fields_info[]=
1082 + {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_name", SKIP_OPEN_TABLE},
1083 + {"ROWS_READ", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE},
1084 + {"ROWS_CHANGED", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_changed", SKIP_OPEN_TABLE},
1085 + {"ROWS_CHANGED_INDEXES", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_changed_x_#indexes", SKIP_OPEN_TABLE},
1086 + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1089 +ST_FIELD_INFO index_stats_fields_info[]=
1091 + {"INDEX_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Index_name", SKIP_OPEN_TABLE},
1092 + {"ROWS_READ", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE},
1093 + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
1097 Description of ST_FIELD_INFO in table.h
1098 @@ -6824,6 +6824,8 @@
1099 fill_status, make_old_format, 0, 0, -1, 0, 0},
1100 {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,
1101 fill_variables, make_old_format, 0, 0, -1, 0, 0},
1102 + {"INDEX_STATISTICS", index_stats_fields_info, create_schema_table,
1103 + fill_schema_index_stats, make_old_format, 0, -1, -1, 0, 0},
1104 {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
1105 get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0,
1107 @@ -6683,11 +6801,15 @@
1108 get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
1109 {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
1110 fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
1111 + {"TABLE_STATISTICS", table_stats_fields_info, create_schema_table,
1112 + fill_schema_table_stats, make_old_format, 0, -1, -1, 0, 0},
1113 {"TRIGGERS", triggers_fields_info, create_schema_table,
1114 get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,
1116 {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table,
1117 fill_schema_user_privileges, 0, 0, -1, -1, 0, 0},
1118 + {"USER_STATISTICS", user_stats_fields_info, create_schema_table,
1119 + fill_schema_user_stats, make_old_format, 0, -1, -1, 0, 0},
1120 {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
1121 make_old_format, 0, -1, -1, 1, 0},
1122 {"VIEWS", view_fields_info, create_schema_table,
1123 diff -r 3ed7e96969f9 sql/sql_update.cc
1124 --- a/sql/sql_update.cc Thu Dec 04 08:54:17 2008 -0800
1125 +++ b/sql/sql_update.cc Thu Dec 04 08:54:27 2008 -0800
1127 thd->row_count_func=
1128 (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
1129 my_ok(thd, (ulong) thd->row_count_func, id, buff);
1130 + thd->updated_row_count += thd->row_count_func;
1131 DBUG_PRINT("info",("%ld records updated", (long) updated));
1133 thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
1134 @@ -2038,5 +2039,6 @@
1135 thd->row_count_func=
1136 (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
1137 ::my_ok(thd, (ulong) thd->row_count_func, id, buff);
1138 + thd->updated_row_count += thd->row_count_func;
1141 diff -r 3ed7e96969f9 sql/sql_yacc.yy
1142 --- a/sql/sql_yacc.yy Thu Dec 04 08:54:17 2008 -0800
1143 +++ b/sql/sql_yacc.yy Thu Dec 04 08:54:27 2008 -0800
1148 +%token INDEX_STATS_SYM
1150 %token INITIAL_SIZE_SYM
1151 %token INNER_SYM /* SQL-2003-R */
1152 @@ -1026,6 +1027,7 @@
1153 %token TABLE_REF_PRIORITY
1154 %token TABLE_SYM /* SQL-2003-R */
1155 %token TABLE_CHECKSUM_SYM
1156 +%token TABLE_STATS_SYM
1157 %token TEMPORARY /* SQL-2003-N */
1158 %token TEMPTABLE_SYM
1160 @@ -1071,6 +1073,7 @@
1162 %token USAGE /* SQL-2003-N */
1163 %token USER /* SQL-2003-R */
1164 +%token USER_STATS_SYM
1167 %token USING /* SQL-2003-R */
1168 @@ -10090,6 +10093,27 @@
1170 Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
1172 + | USER_STATS_SYM wild_and_where
1175 + lex->sql_command= SQLCOM_SHOW_USER_STATS;
1176 + if (prepare_schema_table(YYTHD, lex, 0, SCH_USER_STATS))
1179 + | TABLE_STATS_SYM wild_and_where
1182 + lex->sql_command= SQLCOM_SHOW_TABLE_STATS;
1183 + if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_STATS))
1186 + | INDEX_STATS_SYM wild_and_where
1189 + lex->sql_command= SQLCOM_SHOW_INDEX_STATS;
1190 + if (prepare_schema_table(YYTHD, lex, 0, SCH_INDEX_STATS))
1193 | CREATE PROCEDURE sp_name
1196 @@ -10304,6 +10328,10 @@
1197 { Lex->type|= REFRESH_DES_KEY_FILE; }
1199 { Lex->type|= REFRESH_USER_RESOURCES; }
1201 + { Lex->type|= REFRESH_TABLE_STATS; }
1203 + { Lex->type|= REFRESH_INDEX_STATS; }
1207 diff -r 3ed7e96969f9 sql/structs.h
1208 --- a/sql/structs.h Thu Dec 04 08:54:17 2008 -0800
1209 +++ b/sql/structs.h Thu Dec 04 08:54:27 2008 -0800
1210 @@ -226,6 +226,28 @@
1211 /* Maximum amount of resources which account is allowed to consume. */
1212 USER_RESOURCES user_resources;
1215 +typedef struct st_user_stats {
1216 + char user[USERNAME_LENGTH + 1];
1217 + uint total_connections;
1218 + uint concurrent_connections;
1219 + time_t connected_time; // in seconds
1220 + double busy_time; // in seconds
1221 + ha_rows rows_fetched, rows_updated;
1222 + ulonglong select_commands, update_commands, other_commands;
1223 + ulonglong commit_trans, rollback_trans;
1226 +typedef struct st_table_stats {
1227 + char table[NAME_LEN * 2 + 2]; // [db] + '.' + [table] + '\0'
1228 + ulonglong rows_read, rows_changed;
1229 + ulonglong rows_changed_x_indexes;
1232 +typedef struct st_index_stats {
1233 + char index[NAME_LEN * 3 + 3]; // [db] + '.' + [table] + '.' + [index] + '\0'
1234 + ulonglong rows_read;
1237 /* Bits in form->update */
1238 #define REG_MAKE_DUPP 1 /* Make a copy of record when read */
1239 diff -r 3ed7e96969f9 sql/table.h
1240 --- a/sql/table.h Thu Dec 04 08:54:17 2008 -0800
1241 +++ b/sql/table.h Thu Dec 04 08:54:27 2008 -0800
1245 SCH_GLOBAL_VARIABLES,
1247 SCH_KEY_COLUMN_USAGE,
1250 @@ -897,8 +898,10 @@
1251 SCH_TABLE_CONSTRAINTS,
1253 SCH_TABLE_PRIVILEGES,
1256 SCH_USER_PRIVILEGES,
1261 diff -r 3ed7e96969f9 storage/innobase/handler/ha_innodb.cc
1262 --- a/storage/innobase/handler/ha_innodb.cc Thu Dec 04 08:54:17 2008 -0800
1263 +++ b/storage/innobase/handler/ha_innodb.cc Thu Dec 04 08:54:27 2008 -0800
1264 @@ -3494,6 +3494,8 @@
1266 error = row_insert_for_mysql((byte*) record, prebuilt);
1268 + if (error == DB_SUCCESS) rows_changed++;
1270 /* Handle duplicate key errors */
1271 if (auto_inc_used) {
1273 @@ -3571,6 +3573,8 @@
1278 + if (error == DB_SUCCESS) rows_changed++;
1280 innodb_srv_conc_exit_innodb(prebuilt->trx);
1282 @@ -4178,6 +4182,8 @@
1283 ret = row_search_for_mysql((byte*) buf, mode, prebuilt,
1286 + if (error == DB_SUCCESS) rows_changed++;
1288 innodb_srv_conc_exit_innodb(prebuilt->trx);
1291 @@ -4187,6 +4193,9 @@
1292 if (ret == DB_SUCCESS) {
1296 + if (active_index >= 0 && active_index < MAX_KEY)
1297 + index_rows_read[active_index]++;
1299 } else if (ret == DB_RECORD_NOT_FOUND) {
1300 error = HA_ERR_KEY_NOT_FOUND;
1301 @@ -4360,6 +4369,9 @@
1302 if (ret == DB_SUCCESS) {
1306 + if (active_index >= 0 && active_index < MAX_KEY)
1307 + index_rows_read[active_index]++;
1309 } else if (ret == DB_RECORD_NOT_FOUND) {
1310 error = HA_ERR_END_OF_FILE;
1311 diff -r 3ed7e96969f9 storage/myisam/ha_myisam.cc
1312 --- a/storage/myisam/ha_myisam.cc Thu Dec 04 08:54:17 2008 -0800
1313 +++ b/storage/myisam/ha_myisam.cc Thu Dec 04 08:54:27 2008 -0800
1315 if ((error= update_auto_increment()))
1318 - return mi_write(file,buf);
1319 + int error=mi_write(file,buf);
1320 + if (!error) rows_changed++;
1324 int ha_myisam::check(THD* thd, HA_CHECK_OPT* check_opt)
1325 @@ -1589,13 +1591,17 @@
1326 ha_statistic_increment(&SSV::ha_update_count);
1327 if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
1328 table->timestamp_field->set_time();
1329 - return mi_update(file,old_data,new_data);
1330 + int error=mi_update(file,old_data,new_data);
1331 + if (!error) rows_changed++;
1335 int ha_myisam::delete_row(const uchar *buf)
1337 ha_statistic_increment(&SSV::ha_delete_count);
1338 - return mi_delete(file,buf);
1339 + int error=mi_delete(file,buf);
1340 + if (!error) rows_changed++;
1344 int ha_myisam::index_read_map(uchar *buf, const uchar *key,
1345 @@ -1606,6 +1612,13 @@
1346 ha_statistic_increment(&SSV::ha_read_key_count);
1347 int error=mi_rkey(file, buf, active_index, key, keypart_map, find_flag);
1348 table->status=error ? STATUS_NOT_FOUND: 0;
1352 + int inx = (active_index == -1) ? file->lastinx : active_index;
1353 + if (inx >= 0 && inx < MAX_KEY)
1354 + index_rows_read[inx]++;
1359 @@ -1616,6 +1629,14 @@
1360 ha_statistic_increment(&SSV::ha_read_key_count);
1361 int error=mi_rkey(file, buf, index, key, keypart_map, find_flag);
1362 table->status=error ? STATUS_NOT_FOUND: 0;
1366 +// int inx = (active_index == -1) ? file->lastinx : active_index;
1368 + if (inx >= 0 && inx < MAX_KEY)
1369 + index_rows_read[inx]++;
1374 @@ -1637,6 +1658,13 @@
1375 ha_statistic_increment(&SSV::ha_read_next_count);
1376 int error=mi_rnext(file,buf,active_index);
1377 table->status=error ? STATUS_NOT_FOUND: 0;
1381 + int inx = (active_index == -1) ? file->lastinx : active_index;
1382 + if (inx >= 0 && inx < MAX_KEY)
1383 + index_rows_read[inx]++;
1388 @@ -1646,6 +1674,13 @@
1389 ha_statistic_increment(&SSV::ha_read_prev_count);
1390 int error=mi_rprev(file,buf, active_index);
1391 table->status=error ? STATUS_NOT_FOUND: 0;
1395 + int inx = (active_index == -1) ? file->lastinx : active_index;
1396 + if (inx >= 0 && inx < MAX_KEY)
1397 + index_rows_read[inx]++;
1402 @@ -1655,6 +1690,13 @@
1403 ha_statistic_increment(&SSV::ha_read_first_count);
1404 int error=mi_rfirst(file, buf, active_index);
1405 table->status=error ? STATUS_NOT_FOUND: 0;
1409 + int inx = (active_index == -1) ? file->lastinx : active_index;
1410 + if (inx >= 0 && inx < MAX_KEY)
1411 + index_rows_read[inx]++;
1416 @@ -1664,6 +1706,13 @@
1417 ha_statistic_increment(&SSV::ha_read_last_count);
1418 int error=mi_rlast(file, buf, active_index);
1419 table->status=error ? STATUS_NOT_FOUND: 0;
1423 + int inx = (active_index == -1) ? file->lastinx : active_index;
1424 + if (inx >= 0 && inx < MAX_KEY)
1425 + index_rows_read[inx]++;
1430 @@ -1679,6 +1728,20 @@
1431 error= mi_rnext_same(file,buf);
1432 } while (error == HA_ERR_RECORD_DELETED);
1433 table->status=error ? STATUS_NOT_FOUND: 0;
1437 + int inx = (active_index == -1) ? file->lastinx : active_index;
1438 + if (inx >= 0 && inx < MAX_KEY)
1439 + index_rows_read[inx]++;
1444 + int inx = (active_index == -1) ? file->lastinx : active_index;
1445 + if (inx >= 0 && inx < MAX_KEY)
1446 + index_rows_read[inx]++;
1451 @@ -1695,6 +1758,7 @@
1452 ha_statistic_increment(&SSV::ha_read_rnd_next_count);
1453 int error=mi_scan(file, buf);
1454 table->status=error ? STATUS_NOT_FOUND: 0;
1455 + if (!error) rows_read++;
1459 @@ -1708,6 +1772,7 @@
1460 ha_statistic_increment(&SSV::ha_read_rnd_count);
1461 int error=mi_rrnd(file, buf, my_get_ptr(pos,ref_length));
1462 table->status=error ? STATUS_NOT_FOUND: 0;
1463 + if (!error) rows_read++;