# name : userstat.patch # introduced : 11 or before # maintainer : Yasufumi # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! diff -ruN a/configure b/configure diff -ruN a/configure.in b/configure.in --- a/configure.in 2010-10-12 00:34:15.000000000 +0400 +++ b/configure.in 2010-11-24 18:00:58.000000000 +0300 @@ -2095,13 +2095,16 @@ realpath rename rint rwlock_init setupterm \ shmget shmat shmdt shmctl sigaction sigemptyset sigaddset \ sighold sigset sigthreadmask port_create sleep \ - snprintf socket stpcpy strcasecmp strerror strsignal strnlen strpbrk strstr \ + snprintf socket strsep stpcpy strcasecmp strerror strsignal strnlen strpbrk strstr \ strtol strtoll strtoul strtoull tell tempnam thr_setconcurrency vidattr \ posix_fallocate backtrace backtrace_symbols backtrace_symbols_fd printstack) # # # +# The following change can be safely null-merged to 5.5 +# since configure.cmake in 5.5 does the same check +AC_CHECK_LIB(rt, clock_gettime) case "$target" in *-*-aix4* | *-*-sco*) # (grr) aix 4.3 has a stub for clock_gettime, (returning ENOSYS) diff -ruN a/include/config.h.in b/include/config.h.in --- a/include/config.h.in 2010-10-12 00:39:59.000000000 +0400 +++ b/include/config.h.in 2010-11-24 17:53:34.000000000 +0300 @@ -498,6 +498,9 @@ /* Define to 1 if you have the `pthread' library (-lpthread). */ #undef HAVE_LIBPTHREAD +/* Define to 1 if you have the `rt' library (-lrt). */ +#undef HAVE_LIBRT + /* Define if have -lwrap */ #undef HAVE_LIBWRAP @@ -842,6 +845,9 @@ /* Define to 1 if you have the `strpbrk' function. */ #undef HAVE_STRPBRK +/* Define to 1 if you have the `strsep' function. */ +#undef HAVE_STRSEP + /* Define to 1 if you have the `strsignal' function. */ #undef HAVE_STRSIGNAL @@ -863,7 +869,7 @@ /* Define to 1 if you have the `strtoull' function. */ #undef HAVE_STRTOULL -/* Define to 1 if `st_rdev' is member of `struct stat'. */ +/* Define to 1 if `st_rdev' is a member of `struct stat'. */ #undef HAVE_STRUCT_STAT_ST_RDEV /* Define to 1 if your `struct stat' has `st_rdev'. Deprecated, use @@ -1158,6 +1164,9 @@ /* Define to the one symbol short name of this package. */ #undef PACKAGE_TARNAME +/* Define to the home page for this package. */ +#undef PACKAGE_URL + /* Define to the version of this package. */ #undef PACKAGE_VERSION diff -ruN a/include/mysql/plugin.h b/include/mysql/plugin.h --- a/include/mysql/plugin.h 2010-11-24 17:24:51.000000000 +0300 +++ b/include/mysql/plugin.h 2010-11-24 17:24:52.000000000 +0300 @@ -705,6 +705,9 @@ unsigned long thd_log_slow_verbosity(const MYSQL_THD thd); int thd_opt_slow_log(); #define EXTENDED_SLOWLOG + +#define EXTENDED_FOR_USERSTAT + /** Create a temporary file. diff -ruN a/include/mysql_com.h b/include/mysql_com.h --- a/include/mysql_com.h 2010-10-12 00:34:28.000000000 +0400 +++ b/include/mysql_com.h 2010-11-24 17:28:26.000000000 +0300 @@ -29,6 +29,7 @@ #define SERVER_VERSION_LENGTH 60 #define SQLSTATE_LENGTH 5 +#define LIST_PROCESS_HOST_LEN 64 /* USER_HOST_BUFF_SIZE -- length of string buffer, that is enough to contain @@ -115,6 +116,12 @@ thread */ #define REFRESH_MASTER 128 /* Remove all bin logs in the index and truncate the index */ +#define REFRESH_TABLE_STATS 256 /* Refresh table stats hash table */ +#define REFRESH_INDEX_STATS 512 /* Refresh index stats hash table */ +#define REFRESH_USER_STATS 1024 /* Refresh user stats hash table */ +#define REFRESH_SLOW_QUERY_LOG 2048 /* Flush slow query log and rotate*/ +#define REFRESH_CLIENT_STATS 4096 /* Refresh client stats hash table */ +#define REFRESH_THREAD_STATS 8192 /* Refresh thread stats hash table */ /* The following can't be set with mysql_refresh() */ #define REFRESH_READ_LOCK 16384 /* Lock tables for read */ diff -ruN /dev/null b/patch_info/userstats.info --- /dev/null 1970-01-01 00:00:00.000000000 +0000 +++ b/patch_info/userstats.info 2010-11-24 17:24:52.000000000 +0300 @@ -0,0 +1,11 @@ +File=userstats.patch +Name=SHOW USER/TABLE/INDEX statistics +Version=V2 +Author=Google +License=GPL +Comment=Added INFORMATION_SCHEMA.*_STATISTICS +2008-12-01 +YK: fix behavior for prepared statements + +2008-11-26 +YK: add switch variable "userstat_running" to control INFORMATION_SCHEMA.*_STATISTICS (default:OFF) diff -ruN a/sql/handler.cc b/sql/handler.cc --- a/sql/handler.cc 2010-10-12 00:34:25.000000000 +0400 +++ b/sql/handler.cc 2010-11-24 17:24:52.000000000 +0300 @@ -1194,6 +1194,8 @@ if (cookie) tc_log->unlog(cookie, xid); DBUG_EXECUTE_IF("crash_commit_after", DBUG_SUICIDE();); + if (is_real_trans) + thd->diff_commit_trans++; end: if (rw_trans) start_waiting_global_read_lock(thd); @@ -1324,6 +1326,8 @@ /* Always cleanup. Even if there nht==0. There may be savepoints. */ if (is_real_trans) thd->transaction.cleanup(); + + thd->diff_rollback_trans++; #endif /* USING_TRANSACTIONS */ if (all) thd->transaction_rollback_request= FALSE; @@ -1762,6 +1766,7 @@ ha_info->reset(); /* keep it conveniently zero-filled */ } trans->ha_list= sv->ha_list; + thd->diff_rollback_trans++; DBUG_RETURN(error); } @@ -2122,6 +2127,8 @@ dup_ref=ref+ALIGN_SIZE(ref_length); cached_table_flags= table_flags(); } + rows_read = rows_changed = 0; + memset(index_rows_read, 0, sizeof(index_rows_read)); DBUG_RETURN(error); } @@ -3571,6 +3578,111 @@ return; } +// Updates the global table stats with the TABLE this handler represents. +void handler::update_global_table_stats() { + if (!opt_userstat_running) { + rows_read = rows_changed = 0; + return; + } + + if (!rows_read && !rows_changed) return; // Nothing to update. + // table_cache_key is db_name + '\0' + table_name + '\0'. + if (!table->s || !table->s->table_cache_key.str || !table->s->table_name.str) return; + + TABLE_STATS* table_stats; + char key[NAME_LEN * 2 + 2]; + // [db] + '.' + [table] + sprintf(key, "%s.%s", table->s->table_cache_key.str, table->s->table_name.str); + + pthread_mutex_lock(&LOCK_global_table_stats); + // Gets the global table stats, creating one if necessary. + if (!(table_stats = (TABLE_STATS*)hash_search(&global_table_stats, + (uchar*)key, + strlen(key)))) { + if (!(table_stats = ((TABLE_STATS*) + my_malloc(sizeof(TABLE_STATS), MYF(MY_WME | MY_ZEROFILL))))) { + // Out of memory. + sql_print_error("Allocating table stats failed."); + goto end; + } + strncpy(table_stats->table, key, sizeof(table_stats->table)); + table_stats->rows_read = 0; + table_stats->rows_changed = 0; + table_stats->rows_changed_x_indexes = 0; + table_stats->engine_type = (int) ht->db_type; + + if (my_hash_insert(&global_table_stats, (uchar*)table_stats)) { + // Out of memory. + sql_print_error("Inserting table stats failed."); + my_free((char*)table_stats, 0); + goto end; + } + } + // Updates the global table stats. + table_stats->rows_read += rows_read; + table_stats->rows_changed += rows_changed; + table_stats->rows_changed_x_indexes += + rows_changed * (table->s->keys ? table->s->keys : 1); + current_thd->diff_total_read_rows += rows_read; + rows_read = rows_changed = 0; +end: + pthread_mutex_unlock(&LOCK_global_table_stats); +} + +// Updates the global index stats with this handler's accumulated index reads. +void handler::update_global_index_stats() { + // table_cache_key is db_name + '\0' + table_name + '\0'. + if (!table->s || !table->s->table_cache_key.str || !table->s->table_name.str) return; + + if (!opt_userstat_running) { + for (uint x = 0; x < table->s->keys; x++) { + index_rows_read[x] = 0; + } + return; + } + + for (uint x = 0; x < table->s->keys; x++) { + if (index_rows_read[x]) { + // Rows were read using this index. + KEY* key_info = &table->key_info[x]; + + if (!key_info->name) continue; + + INDEX_STATS* index_stats; + char key[NAME_LEN * 3 + 3]; + // [db] + '.' + [table] + '.' + [index] + sprintf(key, "%s.%s.%s", table->s->table_cache_key.str, + table->s->table_name.str, key_info->name); + + pthread_mutex_lock(&LOCK_global_index_stats); + // Gets the global index stats, creating one if necessary. + if (!(index_stats = (INDEX_STATS*)hash_search(&global_index_stats, + (uchar*)key, + strlen(key)))) { + if (!(index_stats = ((INDEX_STATS*) + my_malloc(sizeof(INDEX_STATS), MYF(MY_WME | MY_ZEROFILL))))) { + // Out of memory. + sql_print_error("Allocating index stats failed."); + goto end; + } + strncpy(index_stats->index, key, sizeof(index_stats->index)); + index_stats->rows_read = 0; + + if (my_hash_insert(&global_index_stats, (uchar*)index_stats)) { + // Out of memory. + sql_print_error("Inserting index stats failed."); + my_free((char*)index_stats, 0); + goto end; + } + } + // Updates the global index stats. + index_stats->rows_read += index_rows_read[x]; + index_rows_read[x] = 0; +end: + pthread_mutex_unlock(&LOCK_global_index_stats); + } + } +} /**************************************************************************** ** Some general functions that isn't in the handler class diff -ruN a/sql/handler.h b/sql/handler.h --- a/sql/handler.h 2010-10-12 00:34:25.000000000 +0400 +++ b/sql/handler.h 2010-11-24 17:28:49.000000000 +0300 @@ -30,6 +30,10 @@ #define USING_TRANSACTIONS +#if MAX_KEY > 128 +#error MAX_KEY is too large. Values up to 128 are supported. +#endif + // the following is for checking tables #define HA_ADMIN_ALREADY_DONE 1 @@ -1121,6 +1125,9 @@ bool locked; bool implicit_emptied; /* Can be !=0 only if HEAP */ const COND *pushed_cond; + ulonglong rows_read; + ulonglong rows_changed; + ulonglong index_rows_read[MAX_KEY]; /** next_insert_id is the next value which should be inserted into the auto_increment column: in a inserting-multi-row statement (like INSERT @@ -1158,9 +1165,11 @@ ref_length(sizeof(my_off_t)), ft_handler(0), inited(NONE), locked(FALSE), implicit_emptied(0), - pushed_cond(0), next_insert_id(0), insert_id_for_cur_row(0), + pushed_cond(0), rows_read(0), rows_changed(0), next_insert_id(0), insert_id_for_cur_row(0), auto_inc_intervals_count(0) - {} + { + memset(index_rows_read, 0, sizeof(index_rows_read)); + } virtual ~handler(void) { DBUG_ASSERT(locked == FALSE); @@ -1284,6 +1293,8 @@ { table= table_arg; table_share= share; + rows_read = rows_changed = 0; + memset(index_rows_read, 0, sizeof(index_rows_read)); } virtual double scan_time() { return ulonglong2double(stats.data_file_length) / IO_SIZE + 2; } @@ -1628,6 +1639,8 @@ virtual bool is_crashed() const { return 0; } virtual bool auto_repair() const { return 0; } + void update_global_table_stats(); + void update_global_index_stats(); #define CHF_CREATE_FLAG 0 #define CHF_DELETE_FLAG 1 diff -ruN a/sql/lex.h b/sql/lex.h --- a/sql/lex.h 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/lex.h 2010-11-24 17:28:26.000000000 +0300 @@ -106,6 +106,7 @@ { "CHECKSUM", SYM(CHECKSUM_SYM)}, { "CIPHER", SYM(CIPHER_SYM)}, { "CLIENT", SYM(CLIENT_SYM)}, + { "CLIENT_STATISTICS", SYM(CLIENT_STATS_SYM)}, { "CLOSE", SYM(CLOSE_SYM)}, { "COALESCE", SYM(COALESCE)}, { "CODE", SYM(CODE_SYM)}, @@ -245,6 +246,7 @@ { "IN", SYM(IN_SYM)}, { "INDEX", SYM(INDEX_SYM)}, { "INDEXES", SYM(INDEXES)}, + { "INDEX_STATISTICS", SYM(INDEX_STATS_SYM)}, { "INFILE", SYM(INFILE)}, { "INITIAL_SIZE", SYM(INITIAL_SIZE_SYM)}, { "INNER", SYM(INNER_SYM)}, @@ -478,6 +480,7 @@ { "SIGNED", SYM(SIGNED_SYM)}, { "SIMPLE", SYM(SIMPLE_SYM)}, { "SLAVE", SYM(SLAVE)}, + { "SLOW", SYM(SLOW_SYM)}, { "SNAPSHOT", SYM(SNAPSHOT_SYM)}, { "SMALLINT", SYM(SMALLINT)}, { "SOCKET", SYM(SOCKET_SYM)}, @@ -527,12 +530,14 @@ { "TABLES", SYM(TABLES)}, { "TABLESPACE", SYM(TABLESPACE)}, { "TABLE_CHECKSUM", SYM(TABLE_CHECKSUM_SYM)}, + { "TABLE_STATISTICS", SYM(TABLE_STATS_SYM)}, { "TEMPORARY", SYM(TEMPORARY)}, { "TEMPTABLE", SYM(TEMPTABLE_SYM)}, { "TERMINATED", SYM(TERMINATED)}, { "TEXT", SYM(TEXT_SYM)}, { "THAN", SYM(THAN_SYM)}, { "THEN", SYM(THEN_SYM)}, + { "THREAD_STATISTICS", SYM(THREAD_STATS_SYM)}, { "TIME", SYM(TIME_SYM)}, { "TIMESTAMP", SYM(TIMESTAMP)}, { "TIMESTAMPADD", SYM(TIMESTAMP_ADD)}, @@ -568,6 +573,7 @@ { "USE", SYM(USE_SYM)}, { "USER", SYM(USER)}, { "USER_RESOURCES", SYM(RESOURCES)}, + { "USER_STATISTICS", SYM(USER_STATS_SYM)}, { "USE_FRM", SYM(USE_FRM)}, { "USING", SYM(USING)}, { "UTC_DATE", SYM(UTC_DATE_SYM)}, diff -ruN a/sql/log.cc b/sql/log.cc --- a/sql/log.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/log.cc 2010-11-24 17:24:52.000000000 +0300 @@ -826,6 +826,13 @@ mysql_slow_log.reopen_file(); } +void Log_to_file_event_handler::flush_slow_log() +{ + /* reopen slow log file */ + if (opt_slow_log) + mysql_slow_log.reopen_file(); +} + /* Log error with all enabled log event handlers @@ -937,6 +944,21 @@ return rc; } +bool LOGGER::flush_slow_log(THD *thd) +{ + /* + Now we lock logger, as nobody should be able to use logging routines while + log tables are closed + */ + logger.lock_exclusive(); + + /* reopen log files */ + file_log_handler->flush_slow_log(); + + /* end of log flush */ + logger.unlock(); + return 0; +} /* Log slow query with all enabled log event handlers @@ -4495,6 +4517,8 @@ thd->first_successful_insert_id_in_prev_stmt_for_binlog); if (e.write(file)) goto err; + if (file == &log_file) + thd->binlog_bytes_written += e.data_written; } if (thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements() > 0) { @@ -4506,12 +4530,16 @@ minimum()); if (e.write(file)) goto err; + if (file == &log_file) + thd->binlog_bytes_written += e.data_written; } if (thd->rand_used) { Rand_log_event e(thd,thd->rand_saved_seed1,thd->rand_saved_seed2); if (e.write(file)) goto err; + if (file == &log_file) + thd->binlog_bytes_written += e.data_written; } if (thd->user_var_events.elements) { @@ -4527,6 +4555,8 @@ user_var_event->charset_number); if (e.write(file)) goto err; + if (file == &log_file) + thd->binlog_bytes_written += e.data_written; } } } @@ -4539,6 +4569,8 @@ if (event_info->write(file) || DBUG_EVALUATE_IF("injecting_fault_writing", 1, 0)) goto err; + if (file == &log_file) + thd->binlog_bytes_written += event_info->data_written; if (file == &log_file) // we are writing to the real log (disk) { @@ -4684,7 +4716,7 @@ be reset as a READ_CACHE to be able to read the contents from it. */ -int MYSQL_BIN_LOG::write_cache(IO_CACHE *cache, bool lock_log, bool sync_log) +int MYSQL_BIN_LOG::write_cache(THD *thd, IO_CACHE *cache, bool lock_log, bool sync_log) { Mutex_sentry sentry(lock_log ? &LOCK_log : NULL); @@ -4732,6 +4764,7 @@ /* write the first half of the split header */ if (my_b_write(&log_file, header, carry)) return ER_ERROR_ON_WRITE; + thd->binlog_bytes_written += carry; /* copy fixed second half of header to cache so the correct @@ -4800,6 +4833,7 @@ /* Write data to the binary log file */ if (my_b_write(&log_file, cache->read_pos, length)) return ER_ERROR_ON_WRITE; + thd->binlog_bytes_written += length; cache->read_pos=cache->read_end; // Mark buffer used up } while ((length= my_b_fill(cache))); @@ -4922,21 +4956,24 @@ */ if (qinfo.write(&log_file)) goto err; + thd->binlog_bytes_written += qinfo.data_written; DBUG_EXECUTE_IF("crash_before_writing_xid", { - if ((write_error= write_cache(cache, false, true))) + if ((write_error= write_cache(thd, cache, false, true))) DBUG_PRINT("info", ("error writing binlog cache: %d", write_error)); DBUG_PRINT("info", ("crashing before writing xid")); DBUG_SUICIDE(); }); - if ((write_error= write_cache(cache, false, false))) + if ((write_error= write_cache(thd, cache, false, false))) goto err; if (commit_event && commit_event->write(&log_file)) goto err; + if (commit_event) + thd->binlog_bytes_written += commit_event->data_written; if (incident && write_incident(thd, FALSE)) goto err; diff -ruN a/sql/log.h b/sql/log.h --- a/sql/log.h 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/log.h 2010-11-24 17:24:52.000000000 +0300 @@ -361,7 +361,7 @@ bool write(THD *thd, IO_CACHE *cache, Log_event *commit_event, bool incident); bool write_incident(THD *thd, bool lock); - int write_cache(IO_CACHE *cache, bool lock_log, bool flush_and_sync); + int write_cache(THD *thd, IO_CACHE *cache, bool lock_log, bool flush_and_sync); void set_write_error(THD *thd); bool check_write_error(THD *thd); @@ -499,6 +499,7 @@ const char *sql_text, uint sql_text_len, CHARSET_INFO *client_cs); void flush(); + void flush_slow_log(); void init_pthread_objects(); MYSQL_QUERY_LOG *get_mysql_slow_log() { return &mysql_slow_log; } MYSQL_QUERY_LOG *get_mysql_log() { return &mysql_log; } @@ -543,6 +544,7 @@ void init_base(); void init_log_tables(); bool flush_logs(THD *thd); + bool flush_slow_log(THD *thd); /* Perform basic logger cleanup. this will leave e.g. error log open. */ void cleanup_base(); /* Free memory. Nothing could be logged after this function is called */ diff -ruN a/sql/mysql_priv.h b/sql/mysql_priv.h --- a/sql/mysql_priv.h 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/mysql_priv.h 2010-11-24 17:31:34.000000000 +0300 @@ -1139,7 +1139,17 @@ bool multi_delete_set_locks_and_link_aux_tables(LEX *lex); void init_max_user_conn(void); void init_update_queries(void); +void init_global_user_stats(void); +void init_global_table_stats(void); +void init_global_index_stats(void); +void init_global_client_stats(void); +void init_global_thread_stats(void); void free_max_user_conn(void); +void free_global_user_stats(void); +void free_global_table_stats(void); +void free_global_index_stats(void); +void free_global_client_stats(void); +void free_global_thread_stats(void); pthread_handler_t handle_bootstrap(void *arg); int mysql_execute_command(THD *thd); bool do_command(THD *thd); @@ -2015,6 +2025,7 @@ extern ulong max_connect_errors, connect_timeout; extern ulong slave_net_timeout, slave_trans_retries; extern uint max_user_connections; +extern ulonglong denied_connections; extern ulong what_to_log,flush_time; extern ulong query_buff_size; extern ulong max_prepared_stmt_count, prepared_stmt_count; @@ -2068,6 +2079,7 @@ extern my_bool opt_slave_compressed_protocol, use_temp_pool; extern ulong slave_exec_mode_options; extern my_bool opt_readonly, lower_case_file_system; +extern my_bool opt_userstat_running, opt_thread_statistics; extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs; extern my_bool opt_secure_auth; extern char* opt_secure_file_priv; @@ -2132,6 +2144,15 @@ extern struct system_variables max_system_variables; extern struct system_status_var global_status_var; extern struct rand_struct sql_rand; +extern HASH global_user_stats; +extern HASH global_client_stats; +extern HASH global_thread_stats; +extern pthread_mutex_t LOCK_global_user_client_stats; +extern HASH global_table_stats; +extern pthread_mutex_t LOCK_global_table_stats; +extern HASH global_index_stats; +extern pthread_mutex_t LOCK_global_index_stats; +extern pthread_mutex_t LOCK_stats; extern const char *opt_date_time_formats[]; extern KNOWN_DATE_TIME_FORMAT known_date_time_formats[]; diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc --- a/sql/mysqld.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/mysqld.cc 2010-11-24 17:31:34.000000000 +0300 @@ -533,6 +533,7 @@ uint opt_debug_sync_timeout= 0; #endif /* defined(ENABLED_DEBUG_SYNC) */ my_bool opt_old_style_user_limits= 0, trust_function_creators= 0; +my_bool opt_userstat_running= 0, opt_thread_statistics= 0; /* True if there is at least one per-hour limit for some user, so we should check them before each query (and possibly reset counters when hour is @@ -581,6 +582,7 @@ ulong binlog_cache_use= 0, binlog_cache_disk_use= 0; ulong max_connections, max_connect_errors; uint max_user_connections= 0; +ulonglong denied_connections = 0; /** Limit of the total number of prepared statements in the server. Is necessary to protect the server against out-of-memory attacks. @@ -682,6 +684,10 @@ LOCK_global_system_variables, LOCK_user_conn, LOCK_slave_list, LOCK_active_mi, LOCK_connection_count; +pthread_mutex_t LOCK_stats; +pthread_mutex_t LOCK_global_user_client_stats; +pthread_mutex_t LOCK_global_table_stats; +pthread_mutex_t LOCK_global_index_stats; /** The below lock protects access to two global server variables: max_prepared_stmt_count and prepared_stmt_count. These variables @@ -1367,6 +1373,11 @@ x_free(opt_secure_file_priv); bitmap_free(&temp_pool); free_max_user_conn(); + free_global_user_stats(); + free_global_client_stats(); + free_global_thread_stats(); + free_global_table_stats(); + free_global_index_stats(); #ifdef HAVE_REPLICATION end_slave_list(); #endif @@ -1483,6 +1494,10 @@ (void) pthread_cond_destroy(&COND_thread_cache); (void) pthread_cond_destroy(&COND_flush_thread_cache); (void) pthread_cond_destroy(&COND_manager); + (void) pthread_mutex_destroy(&LOCK_stats); + (void) pthread_mutex_destroy(&LOCK_global_user_client_stats); + (void) pthread_mutex_destroy(&LOCK_global_table_stats); + (void) pthread_mutex_destroy(&LOCK_global_index_stats); } #endif /*EMBEDDED_LIBRARY*/ @@ -3172,6 +3187,7 @@ {"show_binlog_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_BINLOG_EVENTS]), SHOW_LONG_STATUS}, {"show_binlogs", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_BINLOGS]), SHOW_LONG_STATUS}, {"show_charsets", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CHARSETS]), SHOW_LONG_STATUS}, + {"show_client_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CLIENT_STATS]), SHOW_LONG_STATUS}, {"show_collations", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_COLLATIONS]), SHOW_LONG_STATUS}, {"show_column_types", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_COLUMN_TYPES]), SHOW_LONG_STATUS}, {"show_contributors", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CONTRIBUTORS]), SHOW_LONG_STATUS}, @@ -3193,6 +3209,7 @@ #endif {"show_function_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS_FUNC]), SHOW_LONG_STATUS}, {"show_grants", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_GRANTS]), SHOW_LONG_STATUS}, + {"show_index_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_INDEX_STATS]), SHOW_LONG_STATUS}, {"show_keys", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_KEYS]), SHOW_LONG_STATUS}, {"show_master_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_MASTER_STAT]), SHOW_LONG_STATUS}, {"show_new_master", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_NEW_MASTER]), SHOW_LONG_STATUS}, @@ -3211,9 +3228,12 @@ {"show_slave_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_STAT]), SHOW_LONG_STATUS}, {"show_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS}, {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS}, + {"show_table_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATS]), SHOW_LONG_STATUS}, {"show_table_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS}, {"show_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS}, + {"show_thread_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_THREAD_STATS]), SHOW_LONG_STATUS}, {"show_triggers", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS}, + {"show_user_statistics", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_USER_STATS]), SHOW_LONG_STATUS}, {"show_variables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS}, {"show_warnings", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS}, {"slave_start", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_START]), SHOW_LONG_STATUS}, @@ -3652,6 +3672,10 @@ #endif (void) pthread_mutex_init(&LOCK_server_started, MY_MUTEX_INIT_FAST); (void) pthread_cond_init(&COND_server_started,NULL); + (void) pthread_mutex_init(&LOCK_stats, MY_MUTEX_INIT_FAST); + (void) pthread_mutex_init(&LOCK_global_user_client_stats, MY_MUTEX_INIT_FAST); + (void) pthread_mutex_init(&LOCK_global_table_stats, MY_MUTEX_INIT_FAST); + (void) pthread_mutex_init(&LOCK_global_index_stats, MY_MUTEX_INIT_FAST); sp_cache_init(); #ifdef HAVE_EVENT_SCHEDULER Events::init_mutexes(); @@ -4053,6 +4077,9 @@ if (!errmesg[0][0]) unireg_abort(1); + init_global_table_stats(); + init_global_index_stats(); + /* We have to initialize the storage engines before CSV logging */ if (ha_init()) { @@ -4199,6 +4226,9 @@ init_max_user_conn(); init_update_queries(); + init_global_user_stats(); + init_global_client_stats(); + init_global_thread_stats(); DBUG_RETURN(0); } @@ -5016,6 +5046,7 @@ DBUG_PRINT("error",("Too many connections")); close_connection(thd, ER_CON_COUNT_ERROR, 1); + statistic_increment(denied_connections, &LOCK_status); delete thd; DBUG_VOID_RETURN; } @@ -5800,6 +5831,8 @@ OPT_SLAVE_EXEC_MODE, OPT_GENERAL_LOG_FILE, OPT_SLOW_QUERY_LOG_FILE, + OPT_USERSTAT_RUNNING, + OPT_THREAD_STATISTICS, OPT_USE_GLOBAL_LONG_QUERY_TIME, OPT_USE_GLOBAL_LOG_SLOW_CONTROL, OPT_SLOW_QUERY_LOG_MICROSECONDS_TIMESTAMP, @@ -7292,6 +7325,14 @@ &max_system_variables.net_wait_timeout, 0, GET_ULONG, REQUIRED_ARG, NET_WAIT_TIMEOUT, 1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), 0, 1, 0}, + {"userstat_running", OPT_USERSTAT_RUNNING, + "Control USER_STATISTICS, CLIENT_STATISTICS, THREAD_STATISTICS, INDEX_STATISTICS and TABLE_STATISTICS running", + (uchar**) &opt_userstat_running, (uchar**) &opt_userstat_running, + 0, GET_BOOL, NO_ARG, 0, 0, 1, 0, 1, 0}, + {"thread_statistics", OPT_THREAD_STATISTICS, + "Control TABLE_STATISTICS running, when userstat_running is enabled", + (uchar**) &opt_thread_statistics, (uchar**) &opt_thread_statistics, + 0, GET_BOOL, NO_ARG, 0, 0, 1, 0, 1, 0}, {"binlog-direct-non-transactional-updates", OPT_BINLOG_DIRECT_NON_TRANS_UPDATE, "Causes updates to non-transactional engines using statement format to be " "written directly to binary log. Before using this option, make sure that " diff -ruN a/sql/set_var.cc b/sql/set_var.cc --- a/sql/set_var.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/set_var.cc 2010-11-24 17:31:34.000000000 +0300 @@ -556,6 +556,10 @@ static sys_var_thd_ulong sys_read_buff_size(&vars, "read_buffer_size", &SV::read_buff_size); static sys_var_opt_readonly sys_readonly(&vars, "read_only", &opt_readonly); +static sys_var_bool_ptr sys_userstat_running(&vars, "userstat_running", + &opt_userstat_running); +static sys_var_bool_ptr sys_thread_statistics(&vars, "thread_statistics", + &opt_thread_statistics); static sys_var_thd_ulong sys_read_rnd_buff_size(&vars, "read_rnd_buffer_size", &SV::read_rnd_buff_size); static sys_var_thd_ulong sys_div_precincrement(&vars, "div_precision_increment", diff -ruN a/sql/sql_base.cc b/sql/sql_base.cc --- a/sql/sql_base.cc 2010-10-12 00:34:33.000000000 +0400 +++ b/sql/sql_base.cc 2010-11-24 17:29:05.000000000 +0300 @@ -1382,6 +1382,12 @@ DBUG_PRINT("tcache", ("table: '%s'.'%s' 0x%lx", table->s->db.str, table->s->table_name.str, (long) table)); + if(table->file) + { + table->file->update_global_table_stats(); + table->file->update_global_index_stats(); + } + *table_ptr=table->next; /* When closing a MERGE parent or child table, detach the children first. @@ -1922,6 +1928,8 @@ DBUG_PRINT("tmptable", ("closing table: '%s'.'%s'", table->s->db.str, table->s->table_name.str)); + table->file->update_global_table_stats(); + table->file->update_global_index_stats(); free_io_cache(table); closefrm(table, 0); if (delete_table) diff -ruN a/sql/sql_class.cc b/sql/sql_class.cc --- a/sql/sql_class.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_class.cc 2010-11-24 17:31:33.000000000 +0300 @@ -706,6 +706,13 @@ mysys_var=0; binlog_evt_union.do_union= FALSE; enable_slow_log= 0; + busy_time = 0; + cpu_time = 0; + bytes_received = 0; + bytes_sent = 0; + binlog_bytes_written = 0; + updated_row_count = 0; + sent_row_count_2 = 0; #ifndef DBUG_OFF dbug_sentry=THD_SENTRY_MAGIC; #endif @@ -909,6 +916,7 @@ reset_current_stmt_binlog_row_based(); bzero((char *) &status_var, sizeof(status_var)); sql_log_bin_toplevel= options & OPTION_BIN_LOG; + reset_stats(); #if defined(ENABLED_DEBUG_SYNC) /* Initialize the Debug Sync Facility. See debug_sync.cc. */ @@ -916,6 +924,84 @@ #endif /* defined(ENABLED_DEBUG_SYNC) */ } +// Resets stats in a THD. +void THD::reset_stats(void) { + current_connect_time = time(NULL); + last_global_update_time = current_connect_time; + reset_diff_stats(); +} + +// Resets the 'diff' stats, which are used to update global stats. +void THD::reset_diff_stats(void) { + diff_total_busy_time = 0; + diff_total_cpu_time = 0; + diff_total_bytes_received = 0; + diff_total_bytes_sent = 0; + diff_total_binlog_bytes_written = 0; + diff_total_sent_rows = 0; + diff_total_updated_rows = 0; + diff_total_read_rows = 0; + diff_select_commands = 0; + diff_update_commands = 0; + diff_other_commands = 0; + diff_commit_trans = 0; + diff_rollback_trans = 0; + diff_denied_connections = 0; + diff_lost_connections = 0; + diff_access_denied_errors = 0; + diff_empty_queries = 0; +} + +// Updates 'diff' stats of a THD. +void THD::update_stats(bool ran_command) { + if (opt_userstat_running) { + diff_total_busy_time += busy_time; + diff_total_cpu_time += cpu_time; + diff_total_bytes_received += bytes_received; + diff_total_bytes_sent += bytes_sent; + diff_total_binlog_bytes_written += binlog_bytes_written; + diff_total_sent_rows += sent_row_count_2; + diff_total_updated_rows += updated_row_count; + // diff_total_read_rows is updated in handler.cc. + + if (ran_command) { + // The replication thread has the COM_CONNECT command. + if ((old_command == COM_QUERY || command == COM_CONNECT) && + (lex->sql_command >= 0 && lex->sql_command < SQLCOM_END)) { + // A SQL query. + if (lex->sql_command == SQLCOM_SELECT) { + diff_select_commands++; + if (!sent_row_count_2) + diff_empty_queries++; + } else if (! sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) { + // 'SHOW ' commands become SQLCOM_SELECT. + diff_other_commands++; + // 'SHOW ' commands shouldn't inflate total sent row count. + diff_total_sent_rows -= sent_row_count_2; + } else if (is_update_query(lex->sql_command)) { + diff_update_commands++; + } else { + diff_other_commands++; + } + } + } + // diff_commit_trans is updated in handler.cc. + // diff_rollback_trans is updated in handler.cc. + // diff_denied_connections is updated in sql_parse.cc. + // diff_lost_connections is updated in sql_parse.cc. + // diff_access_denied_errors is updated in sql_parse.cc. + + /* reset counters to zero to avoid double-counting since values + are already store in diff_total_*. */ + } + busy_time = 0; + cpu_time = 0; + bytes_received = 0; + bytes_sent = 0; + binlog_bytes_written = 0; + updated_row_count = 0; + sent_row_count_2 = 0; +} /* Init THD for query processing. @@ -1547,6 +1633,32 @@ } #endif +char *THD::get_client_host_port(THD *client) +{ + Security_context *client_sctx= client->security_ctx; + char *client_host= NULL; + + if (client->peer_port && (client_sctx->host || client_sctx->ip) && + security_ctx->host_or_ip[0]) + { + if ((client_host= (char *) this->alloc(LIST_PROCESS_HOST_LEN+1))) + my_snprintf((char *) client_host, LIST_PROCESS_HOST_LEN, + "%s:%u", client_sctx->host_or_ip, client->peer_port); + } + else + client_host= this->strdup(client_sctx->host_or_ip[0] ? + client_sctx->host_or_ip : + client_sctx->host ? client_sctx->host : ""); + + return client_host; +} + +const char *get_client_host(THD *client) +{ + return client->security_ctx->host_or_ip[0] ? + client->security_ctx->host_or_ip : + client->security_ctx->host ? client->security_ctx->host : ""; +} struct Item_change_record: public ilink { @@ -1734,6 +1846,7 @@ buffer.set(buff, sizeof(buff), &my_charset_bin); } thd->sent_row_count++; + thd->sent_row_count_2++; if (thd->is_error()) { protocol->remove_last_row(); @@ -1838,6 +1951,7 @@ select_export::~select_export() { thd->sent_row_count=row_count; + thd->sent_row_count_2=row_count; } @@ -2870,6 +2984,7 @@ if (likely(thd != 0)) { /* current_thd==0 when close_connection() calls net_send_error() */ thd->status_var.bytes_sent+= length; + thd->bytes_sent+= length; } } @@ -2877,6 +2992,7 @@ void thd_increment_bytes_received(ulong length) { current_thd->status_var.bytes_received+= length; + current_thd->bytes_received+= length; } diff -ruN a/sql/sql_class.h b/sql/sql_class.h --- a/sql/sql_class.h 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_class.h 2010-11-24 17:28:57.000000000 +0300 @@ -1435,6 +1435,8 @@ first byte of the packet in do_command() */ enum enum_server_command command; + // Used to save the command, before it is set to COM_SLEEP. + enum enum_server_command old_command; uint32 server_id; uint32 file_id; // for LOAD DATA INFILE /* remote (peer) port */ @@ -1828,6 +1830,8 @@ /* variables.transaction_isolation is reset to this after each commit */ enum_tx_isolation session_tx_isolation; enum_check_fields count_cuted_fields; + ha_rows updated_row_count; + ha_rows sent_row_count_2; /* for userstat */ DYNAMIC_ARRAY user_var_events; /* For user variables replication */ MEM_ROOT *user_var_events_alloc; /* Allocate above array elements here */ @@ -1916,6 +1920,49 @@ */ LOG_INFO* current_linfo; NET* slave_net; // network connection from slave -> m. + + /* + Used to update global user stats. The global user stats are updated + occasionally with the 'diff' variables. After the update, the 'diff' + variables are reset to 0. + */ + // Time when the current thread connected to MySQL. + time_t current_connect_time; + // Last time when THD stats were updated in global_user_stats. + time_t last_global_update_time; + // Busy (non-idle) time for just one command. + double busy_time; + // Busy time not updated in global_user_stats yet. + double diff_total_busy_time; + // Cpu (non-idle) time for just one thread. + double cpu_time; + // Cpu time not updated in global_user_stats yet. + double diff_total_cpu_time; + /* bytes counting */ + ulonglong bytes_received; + ulonglong diff_total_bytes_received; + ulonglong bytes_sent; + ulonglong diff_total_bytes_sent; + ulonglong binlog_bytes_written; + ulonglong diff_total_binlog_bytes_written; + + // Number of rows not reflected in global_user_stats yet. + ha_rows diff_total_sent_rows, diff_total_updated_rows, diff_total_read_rows; + // Number of commands not reflected in global_user_stats yet. + ulonglong diff_select_commands, diff_update_commands, diff_other_commands; + // Number of transactions not reflected in global_user_stats yet. + ulonglong diff_commit_trans, diff_rollback_trans; + // Number of connection errors not reflected in global_user_stats yet. + ulonglong diff_denied_connections, diff_lost_connections; + // Number of db access denied, not reflected in global_user_stats yet. + ulonglong diff_access_denied_errors; + // Number of queries that return 0 rows + ulonglong diff_empty_queries; + + // Per account query delay in miliseconds. When not 0, sleep this number of + // milliseconds before every SQL command. + ulonglong query_delay_millis; + /* Used by the sys_var class to store temporary values */ union { @@ -1981,6 +2028,11 @@ alloc_root. */ void init_for_queries(); + void reset_stats(void); + void reset_diff_stats(void); + // ran_command is true when this is called immediately after a + // command has been run. + void update_stats(bool ran_command); void change_user(void); void cleanup(void); void cleanup_after_query(); @@ -2351,9 +2403,15 @@ *p_db= strmake(db, db_length); *p_db_length= db_length; return FALSE; + + // Returns string as 'IP:port' for the client-side of the connnection represented + // by 'client' as displayed by SHOW PROCESSLIST. Allocates memory from the heap of + // this THD and that is not reclaimed immediately, so use sparingly. May return NULL. } thd_scheduler scheduler; + char *get_client_host_port(THD *client); + public: inline Internal_error_handler *get_internal_handler() { return m_internal_handler; } @@ -2437,6 +2495,9 @@ LEX_STRING invoker_host; }; +// Returns string as 'IP' for the client-side of the connection represented by +// 'client'. Does not allocate memory. May return "". +const char *get_client_host(THD *client); /** A short cut for thd->main_da.set_ok_status(). */ diff -ruN a/sql/sql_connect.cc b/sql/sql_connect.cc --- a/sql/sql_connect.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_connect.cc 2010-11-24 17:24:52.000000000 +0300 @@ -42,6 +42,24 @@ extern void win_install_sigabrt_handler(); #endif +// Increments connection count for user. +static int increment_connection_count(THD* thd, bool use_lock); + +// Uses the THD to update the global stats by user name and client IP +void update_global_user_stats(THD* thd, bool create_user, time_t now); + +HASH global_user_stats; +HASH global_client_stats; +HASH global_thread_stats; +// Protects global_user_stats and global_client_stats +extern pthread_mutex_t LOCK_global_user_client_stats; + +HASH global_table_stats; +extern pthread_mutex_t LOCK_global_table_stats; + +HASH global_index_stats; +extern pthread_mutex_t LOCK_global_index_stats; + /* Get structure for logging connection data for the current user */ @@ -99,6 +117,563 @@ } +extern "C" uchar *get_key_user_stats(USER_STATS *user_stats, size_t *length, + my_bool not_used __attribute__((unused))) +{ + *length = strlen(user_stats->user); + return (uchar*)user_stats->user; +} + +extern "C" uchar *get_key_thread_stats(THREAD_STATS *thread_stats, size_t *length, + my_bool not_used __attribute__((unused))) +{ + *length = sizeof(my_thread_id); + return (uchar*)&(thread_stats->id); +} + +void free_user_stats(USER_STATS* user_stats) +{ + my_free((char*)user_stats, MYF(0)); +} + +void free_thread_stats(THREAD_STATS* thread_stats) +{ + my_free((char*)thread_stats, MYF(0)); +} + +void init_user_stats(USER_STATS *user_stats, + const char *user, + const char *priv_user, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries) +{ + DBUG_ENTER("init_user_stats"); + DBUG_PRINT("info", + ("Add user_stats entry for user %s - priv_user %s", + user, priv_user)); + strncpy(user_stats->user, user, sizeof(user_stats->user)); + strncpy(user_stats->priv_user, priv_user, sizeof(user_stats->priv_user)); + + user_stats->total_connections = total_connections; + user_stats->concurrent_connections = concurrent_connections; + user_stats->connected_time = connected_time; + user_stats->busy_time = busy_time; + user_stats->cpu_time = cpu_time; + user_stats->bytes_received = bytes_received; + user_stats->bytes_sent = bytes_sent; + user_stats->binlog_bytes_written = binlog_bytes_written; + user_stats->rows_fetched = rows_fetched; + user_stats->rows_updated = rows_updated; + user_stats->rows_read = rows_read; + user_stats->select_commands = select_commands; + user_stats->update_commands = update_commands; + user_stats->other_commands = other_commands; + user_stats->commit_trans = commit_trans; + user_stats->rollback_trans = rollback_trans; + user_stats->denied_connections = denied_connections; + user_stats->lost_connections = lost_connections; + user_stats->access_denied_errors = access_denied_errors; + user_stats->empty_queries = empty_queries; + DBUG_VOID_RETURN; +} + +void init_thread_stats(THREAD_STATS *thread_stats, + my_thread_id id, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries) +{ + DBUG_ENTER("init_thread_stats"); + DBUG_PRINT("info", + ("Add thread_stats entry for thread %lu", + id)); + thread_stats->id = id; + + thread_stats->total_connections = total_connections; + thread_stats->concurrent_connections = concurrent_connections; + thread_stats->connected_time = connected_time; + thread_stats->busy_time = busy_time; + thread_stats->cpu_time = cpu_time; + thread_stats->bytes_received = bytes_received; + thread_stats->bytes_sent = bytes_sent; + thread_stats->binlog_bytes_written = binlog_bytes_written; + thread_stats->rows_fetched = rows_fetched; + thread_stats->rows_updated = rows_updated; + thread_stats->rows_read = rows_read; + thread_stats->select_commands = select_commands; + thread_stats->update_commands = update_commands; + thread_stats->other_commands = other_commands; + thread_stats->commit_trans = commit_trans; + thread_stats->rollback_trans = rollback_trans; + thread_stats->denied_connections = denied_connections; + thread_stats->lost_connections = lost_connections; + thread_stats->access_denied_errors = access_denied_errors; + thread_stats->empty_queries = empty_queries; + DBUG_VOID_RETURN; +} + +void add_user_stats(USER_STATS *user_stats, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries) +{ + user_stats->total_connections += total_connections; + user_stats->concurrent_connections += concurrent_connections; + user_stats->connected_time += connected_time; + user_stats->busy_time += busy_time; + user_stats->cpu_time += cpu_time; + user_stats->bytes_received += bytes_received; + user_stats->bytes_sent += bytes_sent; + user_stats->binlog_bytes_written += binlog_bytes_written; + user_stats->rows_fetched += rows_fetched; + user_stats->rows_updated += rows_updated; + user_stats->rows_read += rows_read; + user_stats->select_commands += select_commands; + user_stats->update_commands += update_commands; + user_stats->other_commands += other_commands; + user_stats->commit_trans += commit_trans; + user_stats->rollback_trans += rollback_trans; + user_stats->denied_connections += denied_connections; + user_stats->lost_connections += lost_connections; + user_stats->access_denied_errors += access_denied_errors; + user_stats->empty_queries += empty_queries; +} + +void add_thread_stats(THREAD_STATS *thread_stats, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries) +{ + thread_stats->total_connections += total_connections; + thread_stats->concurrent_connections += concurrent_connections; + thread_stats->connected_time += connected_time; + thread_stats->busy_time += busy_time; + thread_stats->cpu_time += cpu_time; + thread_stats->bytes_received += bytes_received; + thread_stats->bytes_sent += bytes_sent; + thread_stats->binlog_bytes_written += binlog_bytes_written; + thread_stats->rows_fetched += rows_fetched; + thread_stats->rows_updated += rows_updated; + thread_stats->rows_read += rows_read; + thread_stats->select_commands += select_commands; + thread_stats->update_commands += update_commands; + thread_stats->other_commands += other_commands; + thread_stats->commit_trans += commit_trans; + thread_stats->rollback_trans += rollback_trans; + thread_stats->denied_connections += denied_connections; + thread_stats->lost_connections += lost_connections; + thread_stats->access_denied_errors += access_denied_errors; + thread_stats->empty_queries += empty_queries; +} + +void init_global_user_stats(void) +{ + if (hash_init(&global_user_stats, system_charset_info, max_connections, + 0, 0, (hash_get_key)get_key_user_stats, + (hash_free_key)free_user_stats, 0)) { + sql_print_error("Initializing global_user_stats failed."); + exit(1); + } +} + +void init_global_client_stats(void) +{ + if (hash_init(&global_client_stats, system_charset_info, max_connections, + 0, 0, (hash_get_key)get_key_user_stats, + (hash_free_key)free_user_stats, 0)) { + sql_print_error("Initializing global_client_stats failed."); + exit(1); + } +} + +void init_global_thread_stats(void) +{ + if (hash_init(&global_thread_stats, &my_charset_bin, max_connections, + 0, 0, (hash_get_key)get_key_thread_stats, + (hash_free_key)free_thread_stats, 0)) { + sql_print_error("Initializing global_client_stats failed."); + exit(1); + } +} + +extern "C" uchar *get_key_table_stats(TABLE_STATS *table_stats, size_t *length, + my_bool not_used __attribute__((unused))) +{ + *length = strlen(table_stats->table); + return (uchar*)table_stats->table; +} + +extern "C" void free_table_stats(TABLE_STATS* table_stats) +{ + my_free((char*)table_stats, MYF(0)); +} + +void init_global_table_stats(void) +{ + if (hash_init(&global_table_stats, system_charset_info, max_connections, + 0, 0, (hash_get_key)get_key_table_stats, + (hash_free_key)free_table_stats, 0)) { + sql_print_error("Initializing global_table_stats failed."); + exit(1); + } +} + +extern "C" uchar *get_key_index_stats(INDEX_STATS *index_stats, size_t *length, + my_bool not_used __attribute__((unused))) +{ + *length = strlen(index_stats->index); + return (uchar*)index_stats->index; +} + +extern "C" void free_index_stats(INDEX_STATS* index_stats) +{ + my_free((char*)index_stats, MYF(0)); +} + +void init_global_index_stats(void) +{ + if (hash_init(&global_index_stats, system_charset_info, max_connections, + 0, 0, (hash_get_key)get_key_index_stats, + (hash_free_key)free_index_stats, 0)) { + sql_print_error("Initializing global_index_stats failed."); + exit(1); + } +} + +void free_global_user_stats(void) +{ + hash_free(&global_user_stats); +} + +void free_global_thread_stats(void) +{ + hash_free(&global_thread_stats); +} + +void free_global_table_stats(void) +{ + hash_free(&global_table_stats); +} + +void free_global_index_stats(void) +{ + hash_free(&global_index_stats); +} + +void free_global_client_stats(void) +{ + hash_free(&global_client_stats); +} + +// 'mysql_system_user' is used for when the user is not defined for a THD. +static char mysql_system_user[] = "#mysql_system#"; + +// Returns 'user' if it's not NULL. Returns 'mysql_system_user' otherwise. +static char* get_valid_user_string(char* user) { + return user ? user : mysql_system_user; +} + +// Increments the global stats connection count for an entry from +// global_client_stats or global_user_stats. Returns 0 on success +// and 1 on error. +static int increment_count_by_name(const char *name, const char *role_name, + HASH *users_or_clients, THD *thd) +{ + USER_STATS* user_stats; + + if (!(user_stats = (USER_STATS*)hash_search(users_or_clients, (uchar*) name, + strlen(name)))) + { + // First connection for this user or client + if (!(user_stats = ((USER_STATS*) + my_malloc(sizeof(USER_STATS), MYF(MY_WME | MY_ZEROFILL))))) + { + return 1; // Out of memory + } + + init_user_stats(user_stats, name, role_name, + 0, 0, // connections + 0, 0, 0, // time + 0, 0, 0, // bytes sent, received and written + 0, 0, 0, // rows fetched, updated and read + 0, 0, 0, // select, update and other commands + 0, 0, // commit and rollback trans + thd->diff_denied_connections, + 0, // lost connections + 0, // access denied errors + 0); // empty queries + + if (my_hash_insert(users_or_clients, (uchar*)user_stats)) + { + my_free((char*)user_stats, 0); + return 1; // Out of memory + } + } + user_stats->total_connections++; + return 0; +} + +static int increment_count_by_id(my_thread_id id, + HASH *users_or_clients, THD *thd) +{ + THREAD_STATS* thread_stats; + + if (!(thread_stats = (THREAD_STATS*)hash_search(users_or_clients, (uchar*) &id, + sizeof(my_thread_id)))) + { + // First connection for this user or client + if (!(thread_stats = ((THREAD_STATS*) + my_malloc(sizeof(THREAD_STATS), MYF(MY_WME | MY_ZEROFILL))))) + { + return 1; // Out of memory + } + + init_thread_stats(thread_stats, id, + 0, 0, // connections + 0, 0, 0, // time + 0, 0, 0, // bytes sent, received and written + 0, 0, 0, // rows fetched, updated and read + 0, 0, 0, // select, update and other commands + 0, 0, // commit and rollback trans + thd->diff_denied_connections, + 0, // lost connections + 0, // access denied errors + 0); // empty queries + + if (my_hash_insert(users_or_clients, (uchar*)thread_stats)) + { + my_free((char*)thread_stats, 0); + return 1; // Out of memory + } + } + thread_stats->total_connections++; + return 0; +} + +// Increments the global user and client stats connection count. If 'use_lock' +// is true, LOCK_global_user_client_stats will be locked/unlocked. Returns +// 0 on success, 1 on error. +static int increment_connection_count(THD* thd, bool use_lock) +{ + char* user_string = get_valid_user_string(thd->main_security_ctx.user); + const char* client_string = get_client_host(thd); + int return_value = 0; + + if (!opt_userstat_running) + return return_value; + + if (use_lock) pthread_mutex_lock(&LOCK_global_user_client_stats); + + if (increment_count_by_name(user_string, user_string, + &global_user_stats, thd)) + { + return_value = 1; + goto end; + } + if (increment_count_by_name(client_string, + user_string, + &global_client_stats, thd)) + { + return_value = 1; + goto end; + } + if (opt_thread_statistics) { + if (increment_count_by_id(thd->thread_id, &global_thread_stats, thd)) + { + return_value = 1; + goto end; + } + } + +end: + if (use_lock) pthread_mutex_unlock(&LOCK_global_user_client_stats); + return return_value; +} + +// Used to update the global user and client stats. +static void update_global_user_stats_with_user(THD* thd, + USER_STATS* user_stats, + time_t now) +{ + user_stats->connected_time += now - thd->last_global_update_time; +// thd->last_global_update_time = now; + user_stats->busy_time += thd->diff_total_busy_time; + user_stats->cpu_time += thd->diff_total_cpu_time; + user_stats->bytes_received += thd->diff_total_bytes_received; + user_stats->bytes_sent += thd->diff_total_bytes_sent; + user_stats->binlog_bytes_written += thd->diff_total_binlog_bytes_written; + user_stats->rows_fetched += thd->diff_total_sent_rows; + user_stats->rows_updated += thd->diff_total_updated_rows; + user_stats->rows_read += thd->diff_total_read_rows; + user_stats->select_commands += thd->diff_select_commands; + user_stats->update_commands += thd->diff_update_commands; + user_stats->other_commands += thd->diff_other_commands; + user_stats->commit_trans += thd->diff_commit_trans; + user_stats->rollback_trans += thd->diff_rollback_trans; + user_stats->denied_connections += thd->diff_denied_connections; + user_stats->lost_connections += thd->diff_lost_connections; + user_stats->access_denied_errors += thd->diff_access_denied_errors; + user_stats->empty_queries += thd->diff_empty_queries; +} + +static void update_global_thread_stats_with_thread(THD* thd, + THREAD_STATS* thread_stats, + time_t now) +{ + thread_stats->connected_time += now - thd->last_global_update_time; +// thd->last_global_update_time = now; + thread_stats->busy_time += thd->diff_total_busy_time; + thread_stats->cpu_time += thd->diff_total_cpu_time; + thread_stats->bytes_received += thd->diff_total_bytes_received; + thread_stats->bytes_sent += thd->diff_total_bytes_sent; + thread_stats->binlog_bytes_written += thd->diff_total_binlog_bytes_written; + thread_stats->rows_fetched += thd->diff_total_sent_rows; + thread_stats->rows_updated += thd->diff_total_updated_rows; + thread_stats->rows_read += thd->diff_total_read_rows; + thread_stats->select_commands += thd->diff_select_commands; + thread_stats->update_commands += thd->diff_update_commands; + thread_stats->other_commands += thd->diff_other_commands; + thread_stats->commit_trans += thd->diff_commit_trans; + thread_stats->rollback_trans += thd->diff_rollback_trans; + thread_stats->denied_connections += thd->diff_denied_connections; + thread_stats->lost_connections += thd->diff_lost_connections; + thread_stats->access_denied_errors += thd->diff_access_denied_errors; + thread_stats->empty_queries += thd->diff_empty_queries; +} + +// Updates the global stats of a user or client +void update_global_user_stats(THD* thd, bool create_user, time_t now) +{ + if (opt_userstat_running) { + char* user_string = get_valid_user_string(thd->main_security_ctx.user); + const char* client_string = get_client_host(thd); + + USER_STATS* user_stats; + THREAD_STATS* thread_stats; + pthread_mutex_lock(&LOCK_global_user_client_stats); + + // Update by user name + if ((user_stats = (USER_STATS*)hash_search(&global_user_stats, + (uchar*)user_string, + strlen(user_string)))) { + // Found user. + update_global_user_stats_with_user(thd, user_stats, now); + } else { + // Create the entry + if (create_user) { + increment_count_by_name(user_string, user_string, + &global_user_stats, thd); + } + } + + // Update by client IP + if ((user_stats = (USER_STATS*)hash_search(&global_client_stats, + (uchar*)client_string, + strlen(client_string)))) { + // Found by client IP + update_global_user_stats_with_user(thd, user_stats, now); + } else { + // Create the entry + if (create_user) { + increment_count_by_name(client_string, + user_string, + &global_client_stats, thd); + } + } + + if (opt_thread_statistics) { + // Update by thread ID + if ((thread_stats = (THREAD_STATS*)hash_search(&global_thread_stats, + (uchar*) &(thd->thread_id), + sizeof(my_thread_id)))) { + // Found by thread ID + update_global_thread_stats_with_thread(thd, thread_stats, now); + } else { + // Create the entry + if (create_user) { + increment_count_by_id(thd->thread_id, + &global_thread_stats, thd); + } + } + } + + thd->last_global_update_time = now; + thd->reset_diff_stats(); + + pthread_mutex_unlock(&LOCK_global_user_client_stats); + } else { + thd->reset_diff_stats(); + } +} /* check if user has already too many connections @@ -154,7 +729,10 @@ end: if (error) + { uc->connections--; // no need for decrease_user_connections() here + statistic_increment(denied_connections, &LOCK_status); + } (void) pthread_mutex_unlock(&LOCK_user_conn); DBUG_RETURN(error); } @@ -490,6 +1068,7 @@ general_log_print(thd, COM_CONNECT, ER(ER_NOT_SUPPORTED_AUTH_MODE)); DBUG_RETURN(1); } + thd->diff_access_denied_errors++; my_error(ER_ACCESS_DENIED_ERROR, MYF(0), thd->main_security_ctx.user, thd->main_security_ctx.host_or_ip, @@ -971,11 +1550,20 @@ my_sleep(1000); /* must wait after eof() */ #endif statistic_increment(aborted_connects,&LOCK_status); + thd->diff_denied_connections++; DBUG_RETURN(1); } /* Connect completed, set read/write timeouts back to default */ my_net_set_read_timeout(net, thd->variables.net_read_timeout); my_net_set_write_timeout(net, thd->variables.net_write_timeout); + + thd->reset_stats(); + // Updates global user connection stats. + if (increment_connection_count(thd, true)) { + net_send_error(thd, ER_OUTOFMEMORY); // Out of memory + DBUG_RETURN(1); + } + DBUG_RETURN(0); } @@ -997,6 +1585,7 @@ if (thd->killed || (net->error && net->vio != 0)) { statistic_increment(aborted_threads,&LOCK_status); + thd->diff_lost_connections++; } if (net->error && net->vio != 0) @@ -1123,10 +1712,14 @@ for (;;) { NET *net= &thd->net; + bool create_user= TRUE; lex_start(thd); if (login_connection(thd)) + { + create_user= FALSE; goto end_thread; + } prepare_new_connection_state(thd); @@ -1149,6 +1742,8 @@ end_thread: close_connection(thd, 0, 1); + thd->update_stats(false); + update_global_user_stats(thd, create_user, time(NULL)); if (thread_scheduler.end_thread(thd,1)) return 0; // Probably no-threads diff -ruN a/sql/sql_delete.cc b/sql/sql_delete.cc --- a/sql/sql_delete.cc 2010-10-12 00:34:33.000000000 +0400 +++ b/sql/sql_delete.cc 2010-11-24 17:24:52.000000000 +0300 @@ -452,6 +452,7 @@ my_ok(thd, (ha_rows) thd->row_count_func); DBUG_PRINT("info",("%ld records deleted",(long) deleted)); } + thd->updated_row_count += deleted; DBUG_RETURN(error >= 0 || thd->is_error()); } @@ -1059,6 +1060,7 @@ thd->row_count_func= deleted; ::my_ok(thd, (ha_rows) thd->row_count_func); } + thd->updated_row_count += deleted; return 0; } diff -ruN a/sql/sql_insert.cc b/sql/sql_insert.cc --- a/sql/sql_insert.cc 2010-10-12 00:34:16.000000000 +0400 +++ b/sql/sql_insert.cc 2010-11-24 17:24:52.000000000 +0300 @@ -981,6 +981,7 @@ thd->row_count_func= info.copied + info.deleted + updated; ::my_ok(thd, (ulong) thd->row_count_func, id, buff); } + thd->updated_row_count += thd->row_count_func; thd->abort_on_warning= 0; DBUG_RETURN(FALSE); @@ -3309,6 +3310,7 @@ thd->first_successful_insert_id_in_prev_stmt : (info.copied ? autoinc_value_of_last_inserted_row : 0)); ::my_ok(thd, (ulong) thd->row_count_func, id, buff); + thd->updated_row_count += thd->row_count_func; DBUG_RETURN(0); } diff -ruN a/sql/sql_lex.h b/sql/sql_lex.h --- a/sql/sql_lex.h 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_lex.h 2010-11-24 17:31:33.000000000 +0300 @@ -124,6 +124,9 @@ When a command is added here, be sure it's also added in mysqld.cc in "struct show_var_st status_vars[]= {" ... */ + // TODO(mcallaghan): update status_vars in mysqld to export these + SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS, + SQLCOM_SHOW_CLIENT_STATS, SQLCOM_SHOW_THREAD_STATS, /* This should be the last !!! */ SQLCOM_END }; diff -ruN a/sql/sql_parse.cc b/sql/sql_parse.cc --- a/sql/sql_parse.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_parse.cc 2010-11-24 17:45:19.000000000 +0300 @@ -47,6 +47,9 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables); static bool check_show_create_table_access(THD *thd, TABLE_LIST *table); +// Uses the THD to update the global stats by user name and client IP +void update_global_user_stats(THD* thd, bool create_user, time_t now); + const char *any_db="*any*"; // Special symbol for check_access const LEX_STRING command_name[]={ @@ -825,6 +828,12 @@ */ thd->clear_error(); // Clear error message thd->main_da.reset_diagnostics_area(); + thd->updated_row_count=0; + thd->busy_time=0; + thd->cpu_time=0; + thd->bytes_received=0; + thd->bytes_sent=0; + thd->binlog_bytes_written=0; net_new_transaction(net); @@ -994,6 +1003,9 @@ DBUG_PRINT("info",("packet: '%*.s'; command: %d", packet_length, packet, command)); thd->command=command; + // To increment the corrent command counter for user stats, 'command' must + // be saved because it is set to COM_SLEEP at the end of this function. + thd->old_command = command; /* Commands which always take a long time are logged into the slow log only if opt_log_slow_admin_statements is set. @@ -1865,6 +1877,13 @@ thd->profiling.discard_current_query(); #endif break; + case SCH_USER_STATS: + case SCH_CLIENT_STATS: + case SCH_THREAD_STATS: + if (check_global_access(thd, SUPER_ACL | PROCESS_ACL)) + DBUG_RETURN(1); + case SCH_TABLE_STATS: + case SCH_INDEX_STATS: case SCH_OPEN_TABLES: case SCH_VARIABLES: case SCH_STATUS: @@ -2021,6 +2040,7 @@ thd->security_ctx->priv_host)) && check_global_access(thd, SUPER_ACL)) { + thd->diff_access_denied_errors++; my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER"); DBUG_RETURN(TRUE); } @@ -5348,6 +5368,7 @@ if (!no_errors) { const char *db_name= db ? db : thd->db; + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->priv_host, db_name); } @@ -5380,12 +5401,15 @@ { // We can never grant this DBUG_PRINT("error",("No possible access")); if (!no_errors) + { + thd->diff_access_denied_errors++; my_error(ER_ACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->priv_host, (thd->password ? ER(ER_YES) : ER(ER_NO))); /* purecov: tested */ + } DBUG_RETURN(TRUE); /* purecov: tested */ } @@ -5411,11 +5435,15 @@ DBUG_PRINT("error",("Access denied")); if (!no_errors) + { + // increment needs !no_errors condition, otherwise double counting. + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->priv_host, (db ? db : (thd->db ? thd->db : "unknown"))); /* purecov: tested */ + } DBUG_RETURN(TRUE); /* purecov: tested */ } @@ -5444,6 +5472,7 @@ if (!thd->col_access && check_grant_db(thd, dst_db_name)) { + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), thd->security_ctx->priv_user, thd->security_ctx->priv_host, @@ -5525,9 +5554,12 @@ (want_access & ~(SELECT_ACL | EXTRA_ACL | FILE_ACL))) { if (!no_errors) + { + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->priv_host, INFORMATION_SCHEMA_NAME.str); + } return TRUE; } /* @@ -5690,6 +5722,7 @@ if ((thd->security_ctx->master_access & want_access)) return 0; get_privilege_desc(command, sizeof(command), want_access); + thd->diff_access_denied_errors++; my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), command); return 1; #else @@ -6071,6 +6104,30 @@ lex_start(thd); mysql_reset_thd_for_next_command(thd); + int start_time_error = 0; + int end_time_error = 0; + struct timeval start_time, end_time; + double start_usecs = 0; + double end_usecs = 0; + /* cpu time */ + int cputime_error = 0; + struct timespec tp; + double start_cpu_nsecs = 0; + double end_cpu_nsecs = 0; + + if (opt_userstat_running) { +#ifdef HAVE_CLOCK_GETTIME + /* get start cputime */ + if (!(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + start_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + + // Gets the start time, in order to measure how long this command takes. + if (!(start_time_error = gettimeofday(&start_time, NULL))) { + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec; + } + } + if (query_cache_send_result_to_client(thd, rawbuf, length) <= 0) { LEX *lex= thd->lex; @@ -6151,6 +6208,43 @@ *found_semicolon= NULL; } + if (opt_userstat_running) { + // Gets the end time. + if (!(end_time_error = gettimeofday(&end_time, NULL))) { + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec; + } + + // Calculates the difference between the end and start times. + if (start_usecs && end_usecs >= start_usecs && !start_time_error && !end_time_error) { + thd->busy_time = (end_usecs - start_usecs) / 1000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->busy_time > 2629743) { + thd->busy_time = 0; + } + } else { + // end time went back in time, or gettimeofday() failed. + thd->busy_time = 0; + } + +#ifdef HAVE_CLOCK_GETTIME + /* get end cputime */ + if (!cputime_error && + !(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + end_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + if (start_cpu_nsecs && !cputime_error) { + thd->cpu_time = (end_cpu_nsecs - start_cpu_nsecs) / 1000000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->cpu_time > 2629743) { + thd->cpu_time = 0; + } + } else + thd->cpu_time = 0; + } + // Updates THD stats and the global user stats. + thd->update_stats(true); + update_global_user_stats(thd, true, time(NULL)); + DBUG_VOID_RETURN; } @@ -7016,6 +7110,13 @@ if (flush_error_log()) result=1; } + if (((options & (REFRESH_SLOW_QUERY_LOG | REFRESH_LOG)) == + REFRESH_SLOW_QUERY_LOG)) + { + /* We are only flushing slow query log */ + logger.flush_slow_log(thd); + } + #ifdef HAVE_QUERY_CACHE if (options & REFRESH_QUERY_CACHE_FREE) { @@ -7116,6 +7217,40 @@ #endif if (options & REFRESH_USER_RESOURCES) reset_mqh((LEX_USER *) NULL, 0); /* purecov: inspected */ + if (options & REFRESH_TABLE_STATS) + { + pthread_mutex_lock(&LOCK_global_table_stats); + free_global_table_stats(); + init_global_table_stats(); + pthread_mutex_unlock(&LOCK_global_table_stats); + } + if (options & REFRESH_INDEX_STATS) + { + pthread_mutex_lock(&LOCK_global_index_stats); + free_global_index_stats(); + init_global_index_stats(); + pthread_mutex_unlock(&LOCK_global_index_stats); + } + if (options & (REFRESH_USER_STATS | REFRESH_CLIENT_STATS | REFRESH_THREAD_STATS)) + { + pthread_mutex_lock(&LOCK_global_user_client_stats); + if (options & REFRESH_USER_STATS) + { + free_global_user_stats(); + init_global_user_stats(); + } + if (options & REFRESH_CLIENT_STATS) + { + free_global_client_stats(); + init_global_client_stats(); + } + if (options & REFRESH_THREAD_STATS) + { + free_global_thread_stats(); + init_global_thread_stats(); + } + pthread_mutex_unlock(&LOCK_global_user_client_stats); + } *write_to_binlog= tmp_write_to_binlog; /* If the query was killed then this function must fail. diff -ruN a/sql/sql_prepare.cc b/sql/sql_prepare.cc --- a/sql/sql_prepare.cc 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_prepare.cc 2010-11-24 17:45:09.000000000 +0300 @@ -96,6 +96,9 @@ #include #endif +// Uses the THD to update the global stats by user name and client IP +void update_global_user_stats(THD* thd, bool create_user, time_t now); + /** A result class used to send cursor rows using the binary protocol. */ @@ -2103,8 +2106,32 @@ /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); + int start_time_error = 0; + int end_time_error = 0; + struct timeval start_time, end_time; + double start_usecs = 0; + double end_usecs = 0; + /* cpu time */ + int cputime_error = 0; + struct timespec tp; + double start_cpu_nsecs = 0; + double end_cpu_nsecs = 0; + + if (opt_userstat_running) { +#ifdef HAVE_CLOCK_GETTIME + /* get start cputime */ + if (!(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + start_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + + // Gets the start time, in order to measure how long this command takes. + if (!(start_time_error = gettimeofday(&start_time, NULL))) { + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec; + } + } + if (! (stmt= new Prepared_statement(thd))) - DBUG_VOID_RETURN; /* out of memory: error is set in Sql_alloc */ + goto end; /* out of memory: error is set in Sql_alloc */ if (thd->stmt_map.insert(thd, stmt)) { @@ -2112,7 +2139,7 @@ The error is set in the insert. The statement itself will be also deleted there (this is how the hash works). */ - DBUG_VOID_RETURN; + goto end; } /* Reset warnings from previous command */ @@ -2139,6 +2166,44 @@ thd->protocol= save_protocol; /* check_prepared_statemnt sends the metadata packet in case of success */ +end: + if (opt_userstat_running) { + // Gets the end time. + if (!(end_time_error = gettimeofday(&end_time, NULL))) { + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec; + } + + // Calculates the difference between the end and start times. + if (start_usecs && end_usecs >= start_usecs && !start_time_error && !end_time_error) { + thd->busy_time = (end_usecs - start_usecs) / 1000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->busy_time > 2629743) { + thd->busy_time = 0; + } + } else { + // end time went back in time, or gettimeofday() failed. + thd->busy_time = 0; + } + +#ifdef HAVE_CLOCK_GETTIME + /* get end cputime */ + if (!cputime_error && + !(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + end_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + if (start_cpu_nsecs && !cputime_error) { + thd->cpu_time = (end_cpu_nsecs - start_cpu_nsecs) / 1000000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->cpu_time > 2629743) { + thd->cpu_time = 0; + } + } else + thd->cpu_time = 0; + } + // Updates THD stats and the global user stats. + thd->update_stats(true); + update_global_user_stats(thd, true, time(NULL)); + DBUG_VOID_RETURN; } @@ -2489,12 +2554,36 @@ /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); + int start_time_error = 0; + int end_time_error = 0; + struct timeval start_time, end_time; + double start_usecs = 0; + double end_usecs = 0; + /* cpu time */ + int cputime_error = 0; + struct timespec tp; + double start_cpu_nsecs = 0; + double end_cpu_nsecs = 0; + + if (opt_userstat_running) { +#ifdef HAVE_CLOCK_GETTIME + /* get start cputime */ + if (!(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + start_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + + // Gets the start time, in order to measure how long this command takes. + if (!(start_time_error = gettimeofday(&start_time, NULL))) { + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec; + } + } + if (!(stmt= find_prepared_statement(thd, stmt_id))) { char llbuf[22]; my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), sizeof(llbuf), llstr(stmt_id, llbuf), "mysqld_stmt_execute"); - DBUG_VOID_RETURN; + goto end; } #if defined(ENABLED_PROFILING) && defined(COMMUNITY_SERVER) @@ -2515,6 +2604,44 @@ /* Close connection socket; for use with client testing (Bug#43560). */ DBUG_EXECUTE_IF("close_conn_after_stmt_execute", vio_close(thd->net.vio);); +end: + if (opt_userstat_running) { + // Gets the end time. + if (!(end_time_error = gettimeofday(&end_time, NULL))) { + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec; + } + + // Calculates the difference between the end and start times. + if (start_usecs && end_usecs >= start_usecs && !start_time_error && !end_time_error) { + thd->busy_time = (end_usecs - start_usecs) / 1000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->busy_time > 2629743) { + thd->busy_time = 0; + } + } else { + // end time went back in time, or gettimeofday() failed. + thd->busy_time = 0; + } + +#ifdef HAVE_CLOCK_GETTIME + /* get end cputime */ + if (!cputime_error && + !(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + end_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + if (start_cpu_nsecs && !cputime_error) { + thd->cpu_time = (end_cpu_nsecs - start_cpu_nsecs) / 1000000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->cpu_time > 2629743) { + thd->cpu_time = 0; + } + } else + thd->cpu_time = 0; + } + // Updates THD stats and the global user stats. + thd->update_stats(true); + update_global_user_stats(thd, true, time(NULL)); + DBUG_VOID_RETURN; } @@ -2588,20 +2715,45 @@ /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); + + int start_time_error = 0; + int end_time_error = 0; + struct timeval start_time, end_time; + double start_usecs = 0; + double end_usecs = 0; + /* cpu time */ + int cputime_error = 0; + struct timespec tp; + double start_cpu_nsecs = 0; + double end_cpu_nsecs = 0; + + if (opt_userstat_running) { +#ifdef HAVE_CLOCK_GETTIME + /* get start cputime */ + if (!(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + start_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + + // Gets the start time, in order to measure how long this command takes. + if (!(start_time_error = gettimeofday(&start_time, NULL))) { + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec; + } + } + status_var_increment(thd->status_var.com_stmt_fetch); if (!(stmt= find_prepared_statement(thd, stmt_id))) { char llbuf[22]; my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), sizeof(llbuf), llstr(stmt_id, llbuf), "mysqld_stmt_fetch"); - DBUG_VOID_RETURN; + goto end; } cursor= stmt->cursor; if (!cursor) { my_error(ER_STMT_HAS_NO_OPEN_CURSOR, MYF(0), stmt_id); - DBUG_VOID_RETURN; + goto end; } thd->stmt_arena= stmt; @@ -2625,6 +2777,44 @@ thd->restore_backup_statement(stmt, &stmt_backup); thd->stmt_arena= thd; +end: + if (opt_userstat_running) { + // Gets the end time. + if (!(end_time_error = gettimeofday(&end_time, NULL))) { + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec; + } + + // Calculates the difference between the end and start times. + if (start_usecs && end_usecs >= start_usecs && !start_time_error && !end_time_error) { + thd->busy_time = (end_usecs - start_usecs) / 1000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->busy_time > 2629743) { + thd->busy_time = 0; + } + } else { + // end time went back in time, or gettimeofday() failed. + thd->busy_time = 0; + } + +#ifdef HAVE_CLOCK_GETTIME + /* get end cputime */ + if (!cputime_error && + !(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + end_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + if (start_cpu_nsecs && !cputime_error) { + thd->cpu_time = (end_cpu_nsecs - start_cpu_nsecs) / 1000000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->cpu_time > 2629743) { + thd->cpu_time = 0; + } + } else + thd->cpu_time = 0; + } + // Updates THD stats and the global user stats. + thd->update_stats(true); + update_global_user_stats(thd, true, time(NULL)); + DBUG_VOID_RETURN; } @@ -2655,13 +2845,37 @@ /* First of all clear possible warnings from the previous command */ mysql_reset_thd_for_next_command(thd); + int start_time_error = 0; + int end_time_error = 0; + struct timeval start_time, end_time; + double start_usecs = 0; + double end_usecs = 0; + /* cpu time */ + int cputime_error = 0; + struct timespec tp; + double start_cpu_nsecs = 0; + double end_cpu_nsecs = 0; + + if (opt_userstat_running) { +#ifdef HAVE_CLOCK_GETTIME + /* get start cputime */ + if (!(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + start_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + + // Gets the start time, in order to measure how long this command takes. + if (!(start_time_error = gettimeofday(&start_time, NULL))) { + start_usecs = start_time.tv_sec * 1000000.0 + start_time.tv_usec; + } + } + status_var_increment(thd->status_var.com_stmt_reset); if (!(stmt= find_prepared_statement(thd, stmt_id))) { char llbuf[22]; my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), sizeof(llbuf), llstr(stmt_id, llbuf), "mysqld_stmt_reset"); - DBUG_VOID_RETURN; + goto end; } stmt->close_cursor(); @@ -2678,6 +2892,44 @@ my_ok(thd); +end: + if (opt_userstat_running) { + // Gets the end time. + if (!(end_time_error = gettimeofday(&end_time, NULL))) { + end_usecs = end_time.tv_sec * 1000000.0 + end_time.tv_usec; + } + + // Calculates the difference between the end and start times. + if (start_usecs && end_usecs >= start_usecs && !start_time_error && !end_time_error) { + thd->busy_time = (end_usecs - start_usecs) / 1000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->busy_time > 2629743) { + thd->busy_time = 0; + } + } else { + // end time went back in time, or gettimeofday() failed. + thd->busy_time = 0; + } + +#ifdef HAVE_CLOCK_GETTIME + /* get end cputime */ + if (!cputime_error && + !(cputime_error = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp))) + end_cpu_nsecs = tp.tv_sec*1000000000.0+tp.tv_nsec; +#endif + if (start_cpu_nsecs && !cputime_error) { + thd->cpu_time = (end_cpu_nsecs - start_cpu_nsecs) / 1000000000; + // In case there are bad values, 2629743 is the #seconds in a month. + if (thd->cpu_time > 2629743) { + thd->cpu_time = 0; + } + } else + thd->cpu_time = 0; + } + // Updates THD stats and the global user stats. + thd->update_stats(true); + update_global_user_stats(thd, true, time(NULL)); + DBUG_VOID_RETURN; } diff -ruN a/sql/sql_show.cc b/sql/sql_show.cc --- a/sql/sql_show.cc 2010-11-24 17:24:52.000000000 +0300 +++ b/sql/sql_show.cc 2010-11-24 17:31:33.000000000 +0300 @@ -84,6 +84,40 @@ static COND * make_cond_for_info_schema(COND *cond, TABLE_LIST *table); +/* + * Solaris 10 does not have strsep(). + * + * based on getToken from http://www.winehq.org/pipermail/wine-patches/2001-November/001322.html + * + */ + +#ifndef HAVE_STRSEP +static char* strsep(char** str, const char* delims) +{ + char *token; + + if (*str == NULL) { + /* No more tokens */ + return NULL; + } + + token = *str; + while (**str != '\0') { + if (strchr(delims, **str) != NULL) { + **str = '\0'; + (*str)++; + return token; + } + (*str)++; + } + + /* There is not another token */ + *str = NULL; + + return token; +} +#endif + /*************************************************************************** ** List all table types supported ***************************************************************************/ @@ -832,6 +866,7 @@ sctx->master_access); if (!(db_access & DB_ACLS) && check_grant_db(thd,dbname)) { + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->host_or_ip, dbname); general_log_print(thd,COM_INIT_DB,ER(ER_DBACCESS_DENIED_ERROR), @@ -2386,6 +2421,279 @@ DBUG_RETURN(res); } +/* + Write result to network for SHOW USER_STATISTICS + + SYNOPSIS + send_user_stats + all_user_stats - values to return + table - I_S table + + RETURN + 0 - OK + 1 - error + */ +int send_user_stats(THD* thd, HASH *all_user_stats, TABLE *table) +{ + DBUG_ENTER("send_user_stats"); + for (uint i = 0; i < all_user_stats->records; ++i) { + restore_record(table, s->default_values); + USER_STATS *user_stats = (USER_STATS*)hash_element(all_user_stats, i); + table->field[0]->store(user_stats->user, strlen(user_stats->user), system_charset_info); + table->field[1]->store((longlong)user_stats->total_connections); + table->field[2]->store((longlong)user_stats->concurrent_connections); + table->field[3]->store((longlong)user_stats->connected_time); + table->field[4]->store((longlong)user_stats->busy_time); + table->field[5]->store((longlong)user_stats->cpu_time); + table->field[6]->store((longlong)user_stats->bytes_received); + table->field[7]->store((longlong)user_stats->bytes_sent); + table->field[8]->store((longlong)user_stats->binlog_bytes_written); + table->field[9]->store((longlong)user_stats->rows_fetched); + table->field[10]->store((longlong)user_stats->rows_updated); + table->field[11]->store((longlong)user_stats->rows_read); + table->field[12]->store((longlong)user_stats->select_commands); + table->field[13]->store((longlong)user_stats->update_commands); + table->field[14]->store((longlong)user_stats->other_commands); + table->field[15]->store((longlong)user_stats->commit_trans); + table->field[16]->store((longlong)user_stats->rollback_trans); + table->field[17]->store((longlong)user_stats->denied_connections); + table->field[18]->store((longlong)user_stats->lost_connections); + table->field[19]->store((longlong)user_stats->access_denied_errors); + table->field[20]->store((longlong)user_stats->empty_queries); + if (schema_table_store_record(thd, table)) + { + DBUG_PRINT("error", ("store record error")); + DBUG_RETURN(1); + } + } + DBUG_RETURN(0); +} + +int send_thread_stats(THD* thd, HASH *all_thread_stats, TABLE *table) +{ + DBUG_ENTER("send_thread_stats"); + for (uint i = 0; i < all_thread_stats->records; ++i) { + restore_record(table, s->default_values); + THREAD_STATS *user_stats = (THREAD_STATS*)hash_element(all_thread_stats, i); + table->field[0]->store((longlong)user_stats->id); + table->field[1]->store((longlong)user_stats->total_connections); + table->field[2]->store((longlong)user_stats->concurrent_connections); + table->field[3]->store((longlong)user_stats->connected_time); + table->field[4]->store((longlong)user_stats->busy_time); + table->field[5]->store((longlong)user_stats->cpu_time); + table->field[6]->store((longlong)user_stats->bytes_received); + table->field[7]->store((longlong)user_stats->bytes_sent); + table->field[8]->store((longlong)user_stats->binlog_bytes_written); + table->field[9]->store((longlong)user_stats->rows_fetched); + table->field[10]->store((longlong)user_stats->rows_updated); + table->field[11]->store((longlong)user_stats->rows_read); + table->field[12]->store((longlong)user_stats->select_commands); + table->field[13]->store((longlong)user_stats->update_commands); + table->field[14]->store((longlong)user_stats->other_commands); + table->field[15]->store((longlong)user_stats->commit_trans); + table->field[16]->store((longlong)user_stats->rollback_trans); + table->field[17]->store((longlong)user_stats->denied_connections); + table->field[18]->store((longlong)user_stats->lost_connections); + table->field[19]->store((longlong)user_stats->access_denied_errors); + table->field[20]->store((longlong)user_stats->empty_queries); + if (schema_table_store_record(thd, table)) + { + DBUG_PRINT("error", ("store record error")); + DBUG_RETURN(1); + } + } + DBUG_RETURN(0); +} + +/* + Process SHOW USER_STATISTICS + + SYNOPSIS + mysqld_show_user_stats + thd - current thread + wild - limit results to the entry for this user + with_roles - when true, display role for mapped users + + RETURN + 0 - OK + 1 - error + */ + + +int fill_schema_user_stats(THD* thd, TABLE_LIST* tables, COND* cond) +{ + TABLE *table= tables->table; + DBUG_ENTER("fill_schema_user_stats"); + + if (check_global_access(thd, SUPER_ACL | PROCESS_ACL)) + DBUG_RETURN(1); + + // Iterates through all the global stats and sends them to the client. + // Pattern matching on the client IP is supported. + + pthread_mutex_lock(&LOCK_global_user_client_stats); + int result= send_user_stats(thd, &global_user_stats, table); + pthread_mutex_unlock(&LOCK_global_user_client_stats); + if (result) + goto err; + + DBUG_PRINT("exit", ("fill_schema_user_stats result is 0")); + DBUG_RETURN(0); + + err: + DBUG_PRINT("exit", ("fill_schema_user_stats result is 1")); + DBUG_RETURN(1); +} + +/* + Process SHOW CLIENT_STATISTICS + + SYNOPSIS + mysqld_show_client_stats + thd - current thread + wild - limit results to the entry for this client + + RETURN + 0 - OK + 1 - error + */ + + +int fill_schema_client_stats(THD* thd, TABLE_LIST* tables, COND* cond) +{ + TABLE *table= tables->table; + DBUG_ENTER("fill_schema_client_stats"); + + if (check_global_access(thd, SUPER_ACL | PROCESS_ACL)) + DBUG_RETURN(1); + + // Iterates through all the global stats and sends them to the client. + // Pattern matching on the client IP is supported. + + pthread_mutex_lock(&LOCK_global_user_client_stats); + int result= send_user_stats(thd, &global_client_stats, table); + pthread_mutex_unlock(&LOCK_global_user_client_stats); + if (result) + goto err; + + DBUG_PRINT("exit", ("mysqld_show_client_stats result is 0")); + DBUG_RETURN(0); + + err: + DBUG_PRINT("exit", ("mysqld_show_client_stats result is 1")); + DBUG_RETURN(1); +} + +int fill_schema_thread_stats(THD* thd, TABLE_LIST* tables, COND* cond) +{ + TABLE *table= tables->table; + DBUG_ENTER("fill_schema_thread_stats"); + + if (check_global_access(thd, SUPER_ACL | PROCESS_ACL)) + DBUG_RETURN(1); + + // Iterates through all the global stats and sends them to the client. + // Pattern matching on the client IP is supported. + + pthread_mutex_lock(&LOCK_global_user_client_stats); + int result= send_thread_stats(thd, &global_thread_stats, table); + pthread_mutex_unlock(&LOCK_global_user_client_stats); + if (result) + goto err; + + DBUG_PRINT("exit", ("mysqld_show_thread_stats result is 0")); + DBUG_RETURN(0); + + err: + DBUG_PRINT("exit", ("mysqld_show_thread_stats result is 1")); + DBUG_RETURN(1); +} + +// Sends the global table stats back to the client. +int fill_schema_table_stats(THD* thd, TABLE_LIST* tables, COND* cond) +{ + TABLE *table= tables->table; + DBUG_ENTER("fill_schema_table_stats"); + char *table_full_name, *table_schema; + + pthread_mutex_lock(&LOCK_global_table_stats); + for (uint i = 0; i < global_table_stats.records; ++i) { + restore_record(table, s->default_values); + TABLE_STATS *table_stats = + (TABLE_STATS*)hash_element(&global_table_stats, i); + + table_full_name= thd->strdup(table_stats->table); + table_schema= strsep(&table_full_name, "."); + + TABLE_LIST tmp_table; + bzero((char*) &tmp_table,sizeof(tmp_table)); + tmp_table.table_name= table_full_name; + tmp_table.db= table_schema; + tmp_table.grant.privilege= 0; + if (check_access(thd, SELECT_ACL | EXTRA_ACL, tmp_table.db, + &tmp_table.grant.privilege, 0, 0, + is_schema_db(table_schema)) || + check_grant(thd, SELECT_ACL, &tmp_table, 1, UINT_MAX, 1)) + continue; + + table->field[0]->store(table_schema, strlen(table_schema), system_charset_info); + table->field[1]->store(table_full_name, strlen(table_full_name), system_charset_info); + table->field[2]->store((longlong)table_stats->rows_read, TRUE); + table->field[3]->store((longlong)table_stats->rows_changed, TRUE); + table->field[4]->store((longlong)table_stats->rows_changed_x_indexes, TRUE); + + if (schema_table_store_record(thd, table)) + { + VOID(pthread_mutex_unlock(&LOCK_global_table_stats)); + DBUG_RETURN(1); + } + } + pthread_mutex_unlock(&LOCK_global_table_stats); + DBUG_RETURN(0); +} + +// Sends the global index stats back to the client. +int fill_schema_index_stats(THD* thd, TABLE_LIST* tables, COND* cond) +{ + TABLE *table= tables->table; + DBUG_ENTER("fill_schema_index_stats"); + char *index_full_name, *table_schema, *table_name; + + pthread_mutex_lock(&LOCK_global_index_stats); + for (uint i = 0; i < global_index_stats.records; ++i) { + restore_record(table, s->default_values); + INDEX_STATS *index_stats = + (INDEX_STATS*)hash_element(&global_index_stats, i); + + index_full_name= thd->strdup(index_stats->index); + table_schema= strsep(&index_full_name, "."); + table_name= strsep(&index_full_name, "."); + + TABLE_LIST tmp_table; + bzero((char*) &tmp_table,sizeof(tmp_table)); + tmp_table.table_name= table_name; + tmp_table.db= table_schema; + tmp_table.grant.privilege= 0; + if (check_access(thd, SELECT_ACL | EXTRA_ACL, tmp_table.db, + &tmp_table.grant.privilege, 0, 0, + is_schema_db(table_schema)) || + check_grant(thd, SELECT_ACL, &tmp_table, 1, UINT_MAX, 1)) + continue; + + table->field[0]->store(table_schema, strlen(table_schema), system_charset_info); + table->field[1]->store(table_name, strlen(table_name), system_charset_info); + table->field[2]->store(index_full_name, strlen(index_full_name), system_charset_info); + table->field[3]->store((longlong)index_stats->rows_read, TRUE); + + if (schema_table_store_record(thd, table)) + { + VOID(pthread_mutex_unlock(&LOCK_global_index_stats)); + DBUG_RETURN(1); + } + } + pthread_mutex_unlock(&LOCK_global_index_stats); + DBUG_RETURN(0); +} /* collect status for all running threads */ @@ -6688,6 +6996,104 @@ }; +ST_FIELD_INFO user_stats_fields_info[]= +{ + {"USER", USERNAME_LENGTH, MYSQL_TYPE_STRING, 0, 0, "User", SKIP_OPEN_TABLE}, + {"TOTAL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_connections", SKIP_OPEN_TABLE}, + {"CONCURRENT_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Concurrent_connections", SKIP_OPEN_TABLE}, + {"CONNECTED_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Connected_time", SKIP_OPEN_TABLE}, + {"BUSY_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Busy_time", SKIP_OPEN_TABLE}, + {"CPU_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Cpu_time", SKIP_OPEN_TABLE}, + {"BYTES_RECEIVED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_received", SKIP_OPEN_TABLE}, + {"BYTES_SENT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_sent", SKIP_OPEN_TABLE}, + {"BINLOG_BYTES_WRITTEN", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Binlog_bytes_written", SKIP_OPEN_TABLE}, + {"ROWS_FETCHED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_fetched", SKIP_OPEN_TABLE}, + {"ROWS_UPDATED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_updated", SKIP_OPEN_TABLE}, + {"TABLE_ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Table_rows_read", SKIP_OPEN_TABLE}, + {"SELECT_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Select_commands", SKIP_OPEN_TABLE}, + {"UPDATE_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Update_commands", SKIP_OPEN_TABLE}, + {"OTHER_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Other_commands", SKIP_OPEN_TABLE}, + {"COMMIT_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Commit_transactions", SKIP_OPEN_TABLE}, + {"ROLLBACK_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rollback_transactions", SKIP_OPEN_TABLE}, + {"DENIED_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Denied_connections", SKIP_OPEN_TABLE}, + {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE}, + {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE}, + {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + +ST_FIELD_INFO client_stats_fields_info[]= +{ + {"CLIENT", LIST_PROCESS_HOST_LEN, MYSQL_TYPE_STRING, 0, 0, "Client", SKIP_OPEN_TABLE}, + {"TOTAL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_connections", SKIP_OPEN_TABLE}, + {"CONCURRENT_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Concurrent_connections", SKIP_OPEN_TABLE}, + {"CONNECTED_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Connected_time", SKIP_OPEN_TABLE}, + {"BUSY_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Busy_time", SKIP_OPEN_TABLE}, + {"CPU_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Cpu_time", SKIP_OPEN_TABLE}, + {"BYTES_RECEIVED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_received", SKIP_OPEN_TABLE}, + {"BYTES_SENT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_sent", SKIP_OPEN_TABLE}, + {"BINLOG_BYTES_WRITTEN", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Binlog_bytes_written", SKIP_OPEN_TABLE}, + {"ROWS_FETCHED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_fetched", SKIP_OPEN_TABLE}, + {"ROWS_UPDATED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_updated", SKIP_OPEN_TABLE}, + {"TABLE_ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Table_rows_read", SKIP_OPEN_TABLE}, + {"SELECT_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Select_commands", SKIP_OPEN_TABLE}, + {"UPDATE_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Update_commands", SKIP_OPEN_TABLE}, + {"OTHER_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Other_commands", SKIP_OPEN_TABLE}, + {"COMMIT_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Commit_transactions", SKIP_OPEN_TABLE}, + {"ROLLBACK_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rollback_transactions", SKIP_OPEN_TABLE}, + {"DENIED_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Denied_connections", SKIP_OPEN_TABLE}, + {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE}, + {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE}, + {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + +ST_FIELD_INFO thread_stats_fields_info[]= +{ + {"THREAD_ID", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Thread_id", SKIP_OPEN_TABLE}, + {"TOTAL_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Total_connections", SKIP_OPEN_TABLE}, + {"CONCURRENT_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Concurrent_connections", SKIP_OPEN_TABLE}, + {"CONNECTED_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Connected_time", SKIP_OPEN_TABLE}, + {"BUSY_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Busy_time", SKIP_OPEN_TABLE}, + {"CPU_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Cpu_time", SKIP_OPEN_TABLE}, + {"BYTES_RECEIVED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_received", SKIP_OPEN_TABLE}, + {"BYTES_SENT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_sent", SKIP_OPEN_TABLE}, + {"BINLOG_BYTES_WRITTEN", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Binlog_bytes_written", SKIP_OPEN_TABLE}, + {"ROWS_FETCHED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_fetched", SKIP_OPEN_TABLE}, + {"ROWS_UPDATED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_updated", SKIP_OPEN_TABLE}, + {"TABLE_ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Table_rows_read", SKIP_OPEN_TABLE}, + {"SELECT_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Select_commands", SKIP_OPEN_TABLE}, + {"UPDATE_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Update_commands", SKIP_OPEN_TABLE}, + {"OTHER_COMMANDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Other_commands", SKIP_OPEN_TABLE}, + {"COMMIT_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Commit_transactions", SKIP_OPEN_TABLE}, + {"ROLLBACK_TRANSACTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rollback_transactions", SKIP_OPEN_TABLE}, + {"DENIED_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Denied_connections", SKIP_OPEN_TABLE}, + {"LOST_CONNECTIONS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Lost_connections", SKIP_OPEN_TABLE}, + {"ACCESS_DENIED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Access_denied", SKIP_OPEN_TABLE}, + {"EMPTY_QUERIES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Empty_queries", SKIP_OPEN_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + +ST_FIELD_INFO table_stats_fields_info[]= +{ + {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_schema", SKIP_OPEN_TABLE}, + {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_name", SKIP_OPEN_TABLE}, + {"ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE}, + {"ROWS_CHANGED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_changed", SKIP_OPEN_TABLE}, + {"ROWS_CHANGED_X_INDEXES", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_changed_x_#indexes", SKIP_OPEN_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + +ST_FIELD_INFO index_stats_fields_info[]= +{ + {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_schema", SKIP_OPEN_TABLE}, + {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_name", SKIP_OPEN_TABLE}, + {"INDEX_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Index_name", SKIP_OPEN_TABLE}, + {"ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Rows_read", SKIP_OPEN_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + + ST_FIELD_INFO processlist_fields_info[]= { {"ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Id", SKIP_OPEN_TABLE}, @@ -6823,6 +7229,8 @@ { {"CHARACTER_SETS", charsets_fields_info, create_schema_table, fill_schema_charsets, make_character_sets_old_format, 0, -1, -1, 0, 0}, + {"CLIENT_STATISTICS", client_stats_fields_info, create_schema_table, + fill_schema_client_stats, make_old_format, 0, -1, -1, 0, 0}, {"COLLATIONS", collation_fields_info, create_schema_table, fill_schema_collation, make_old_format, 0, -1, -1, 0, 0}, {"COLLATION_CHARACTER_SET_APPLICABILITY", coll_charset_app_fields_info, @@ -6832,6 +7240,8 @@ OPTIMIZE_I_S_TABLE|OPEN_VIEW_FULL}, {"COLUMN_PRIVILEGES", column_privileges_fields_info, create_schema_table, fill_schema_column_privileges, 0, 0, -1, -1, 0, 0}, + {"INDEX_STATISTICS", index_stats_fields_info, create_schema_table, + fill_schema_index_stats, make_old_format, 0, -1, -1, 0, 0}, {"ENGINES", engines_fields_info, create_schema_table, fill_schema_engines, make_old_format, 0, -1, -1, 0, 0}, #ifdef HAVE_EVENT_SCHEDULER @@ -6888,11 +7298,17 @@ get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0}, {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table, fill_schema_table_privileges, 0, 0, -1, -1, 0, 0}, + {"TABLE_STATISTICS", table_stats_fields_info, create_schema_table, + fill_schema_table_stats, make_old_format, 0, -1, -1, 0, 0}, + {"THREAD_STATISTICS", thread_stats_fields_info, create_schema_table, + fill_schema_thread_stats, make_old_format, 0, -1, -1, 0, 0}, {"TRIGGERS", triggers_fields_info, create_schema_table, get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0, OPEN_TABLE_ONLY}, {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table, fill_schema_user_privileges, 0, 0, -1, -1, 0, 0}, + {"USER_STATISTICS", user_stats_fields_info, create_schema_table, + fill_schema_user_stats, make_old_format, 0, -1, -1, 0, 0}, {"VARIABLES", variables_fields_info, create_schema_table, fill_variables, make_old_format, 0, 0, -1, 1, 0}, {"VIEWS", view_fields_info, create_schema_table, diff -ruN a/sql/sql_update.cc b/sql/sql_update.cc --- a/sql/sql_update.cc 2010-10-12 00:34:16.000000000 +0400 +++ b/sql/sql_update.cc 2010-11-24 17:24:52.000000000 +0300 @@ -890,6 +890,7 @@ thd->row_count_func= (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; my_ok(thd, (ulong) thd->row_count_func, id, buff); + thd->updated_row_count += thd->row_count_func; DBUG_PRINT("info",("%ld records updated", (long) updated)); } thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */ @@ -2176,5 +2177,6 @@ thd->row_count_func= (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; ::my_ok(thd, (ulong) thd->row_count_func, id, buff); + thd->updated_row_count += thd->row_count_func; DBUG_RETURN(FALSE); } diff -ruN a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy 2010-11-24 17:24:51.000000000 +0300 +++ b/sql/sql_yacc.yy 2010-11-24 17:31:33.000000000 +0300 @@ -757,6 +757,7 @@ %token CHECK_SYM /* SQL-2003-R */ %token CIPHER_SYM %token CLIENT_SYM +%token CLIENT_STATS_SYM %token CLOSE_SYM /* SQL-2003-R */ %token COALESCE /* SQL-2003-N */ %token CODE_SYM @@ -903,6 +904,7 @@ %token IMPORT %token INDEXES %token INDEX_SYM +%token INDEX_STATS_SYM %token INFILE %token INITIAL_SIZE_SYM %token INNER_SYM /* SQL-2003-R */ @@ -1144,6 +1146,7 @@ %token SIGNED_SYM %token SIMPLE_SYM /* SQL-2003-N */ %token SLAVE +%token SLOW_SYM %token SMALLINT /* SQL-2003-R */ %token SNAPSHOT_SYM %token SOCKET_SYM @@ -1189,6 +1192,7 @@ %token TABLESPACE %token TABLE_REF_PRIORITY %token TABLE_SYM /* SQL-2003-R */ +%token TABLE_STATS_SYM %token TABLE_CHECKSUM_SYM %token TEMPORARY /* SQL-2003-N */ %token TEMPTABLE_SYM @@ -1197,6 +1201,7 @@ %token TEXT_SYM %token THAN_SYM %token THEN_SYM /* SQL-2003-R */ +%token THREAD_STATS_SYM %token TIMESTAMP /* SQL-2003-R */ %token TIMESTAMP_ADD %token TIMESTAMP_DIFF @@ -1234,6 +1239,7 @@ %token UPGRADE_SYM %token USAGE /* SQL-2003-N */ %token USER /* SQL-2003-R */ +%token USER_STATS_SYM %token USE_FRM %token USE_SYM %token USING /* SQL-2003-R */ @@ -10346,6 +10352,41 @@ { Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT; } + | CLIENT_STATS_SYM wild_and_where + { + LEX *lex= Lex; + Lex->sql_command = SQLCOM_SELECT; + if (prepare_schema_table(YYTHD, lex, 0, SCH_CLIENT_STATS)) + MYSQL_YYABORT; + } + | USER_STATS_SYM wild_and_where + { + LEX *lex= Lex; + lex->sql_command = SQLCOM_SELECT; + if (prepare_schema_table(YYTHD, lex, 0, SCH_USER_STATS)) + MYSQL_YYABORT; + } + | THREAD_STATS_SYM wild_and_where + { + LEX *lex= Lex; + Lex->sql_command = SQLCOM_SELECT; + if (prepare_schema_table(YYTHD, lex, 0, SCH_THREAD_STATS)) + MYSQL_YYABORT; + } + | TABLE_STATS_SYM wild_and_where + { + LEX *lex= Lex; + lex->sql_command= SQLCOM_SELECT; + if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_STATS)) + MYSQL_YYABORT; + } + | INDEX_STATS_SYM wild_and_where + { + LEX *lex= Lex; + lex->sql_command= SQLCOM_SELECT; + if (prepare_schema_table(YYTHD, lex, 0, SCH_INDEX_STATS)) + MYSQL_YYABORT; + } | CREATE PROCEDURE sp_name { LEX *lex= Lex; @@ -10554,6 +10595,18 @@ { Lex->type|= REFRESH_STATUS; } | SLAVE { Lex->type|= REFRESH_SLAVE; } + | SLOW_SYM QUERY_SYM LOGS_SYM + { Lex->type |= REFRESH_SLOW_QUERY_LOG; } + | CLIENT_STATS_SYM + { Lex->type|= REFRESH_CLIENT_STATS; } + | USER_STATS_SYM + { Lex->type|= REFRESH_USER_STATS; } + | THREAD_STATS_SYM + { Lex->type|= REFRESH_THREAD_STATS; } + | TABLE_STATS_SYM + { Lex->type|= REFRESH_TABLE_STATS; } + | INDEX_STATS_SYM + { Lex->type|= REFRESH_INDEX_STATS; } | MASTER_SYM { Lex->type|= REFRESH_MASTER; } | DES_KEY_FILE @@ -11677,6 +11730,7 @@ | CHAIN_SYM {} | CHANGED {} | CIPHER_SYM {} + | CLIENT_STATS_SYM {} | CLIENT_SYM {} | COALESCE {} | CODE_SYM {} @@ -11738,6 +11792,7 @@ | HOSTS_SYM {} | HOUR_SYM {} | IDENTIFIED_SYM {} + | INDEX_STATS_SYM {} | INVOKER_SYM {} | IMPORT {} | INDEXES {} @@ -11862,6 +11917,7 @@ | SIMPLE_SYM {} | SHARE_SYM {} | SHUTDOWN {} + | SLOW_SYM {} | SNAPSHOT_SYM {} | SOUNDS_SYM {} | SOURCE_SYM {} @@ -11881,6 +11937,7 @@ | SUSPEND_SYM {} | SWAPS_SYM {} | SWITCHES_SYM {} + | TABLE_STATS_SYM {} | TABLES {} | TABLE_CHECKSUM_SYM {} | TABLESPACE {} @@ -11888,6 +11945,7 @@ | TEMPTABLE_SYM {} | TEXT_SYM {} | THAN_SYM {} + | THREAD_STATS_SYM {} | TRANSACTION_SYM {} | TRIGGERS_SYM {} | TIMESTAMP {} @@ -11905,6 +11963,7 @@ | UNKNOWN_SYM {} | UNTIL_SYM {} | USER {} + | USER_STATS_SYM {} | USE_FRM {} | VARIABLES {} | VIEW_SYM {} diff -ruN a/sql/structs.h b/sql/structs.h --- a/sql/structs.h 2010-10-12 00:34:34.000000000 +0400 +++ b/sql/structs.h 2010-11-24 17:24:52.000000000 +0300 @@ -237,6 +237,171 @@ USER_RESOURCES user_resources; } USER_CONN; +typedef struct st_user_stats { + char user[max(USERNAME_LENGTH, LIST_PROCESS_HOST_LEN) + 1]; + // Account name the user is mapped to when this is a user from mapped_user. + // Otherwise, the same value as user. + char priv_user[max(USERNAME_LENGTH, LIST_PROCESS_HOST_LEN) + 1]; + uint total_connections; + uint concurrent_connections; + time_t connected_time; // in seconds + double busy_time; // in seconds + double cpu_time; // in seconds + ulonglong bytes_received; + ulonglong bytes_sent; + ulonglong binlog_bytes_written; + ha_rows rows_fetched, rows_updated, rows_read; + ulonglong select_commands, update_commands, other_commands; + ulonglong commit_trans, rollback_trans; + ulonglong denied_connections, lost_connections; + ulonglong access_denied_errors; + ulonglong empty_queries; +} USER_STATS; + +/* Lookup function for hash tables with USER_STATS entries */ +extern "C" uchar *get_key_user_stats(USER_STATS *user_stats, size_t *length, + my_bool not_used __attribute__((unused))); + +/* Free all memory for a hash table with USER_STATS entries */ +extern void free_user_stats(USER_STATS* user_stats); + +/* Intialize an instance of USER_STATS */ +extern void +init_user_stats(USER_STATS *user_stats, + const char *user, + const char *priv_user, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries); + +/* Increment values of an instance of USER_STATS */ +extern void +add_user_stats(USER_STATS *user_stats, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries); + +typedef struct st_thread_stats { + my_thread_id id; + uint total_connections; + uint concurrent_connections; + time_t connected_time; // in seconds + double busy_time; // in seconds + double cpu_time; // in seconds + ulonglong bytes_received; + ulonglong bytes_sent; + ulonglong binlog_bytes_written; + ha_rows rows_fetched, rows_updated, rows_read; + ulonglong select_commands, update_commands, other_commands; + ulonglong commit_trans, rollback_trans; + ulonglong denied_connections, lost_connections; + ulonglong access_denied_errors; + ulonglong empty_queries; +} THREAD_STATS; + +/* Lookup function for hash tables with THREAD_STATS entries */ +extern "C" uchar *get_key_thread_stats(THREAD_STATS *thread_stats, size_t *length, + my_bool not_used __attribute__((unused))); + +/* Free all memory for a hash table with THREAD_STATS entries */ +extern void free_thread_stats(THREAD_STATS* thread_stats); + +/* Intialize an instance of THREAD_STATS */ +extern void +init_thread_stats(THREAD_STATS *thread_stats, + my_thread_id id, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries); + +/* Increment values of an instance of THREAD_STATS */ +extern void +add_thread_stats(THREAD_STATS *thread_stats, + uint total_connections, + uint concurrent_connections, + time_t connected_time, + double busy_time, + double cpu_time, + ulonglong bytes_received, + ulonglong bytes_sent, + ulonglong binlog_bytes_written, + ha_rows rows_fetched, + ha_rows rows_updated, + ha_rows rows_read, + ulonglong select_commands, + ulonglong update_commands, + ulonglong other_commands, + ulonglong commit_trans, + ulonglong rollback_trans, + ulonglong denied_connections, + ulonglong lost_connections, + ulonglong access_denied_errors, + ulonglong empty_queries); + +typedef struct st_table_stats { + char table[NAME_LEN * 2 + 2]; // [db] + '.' + [table] + '\0' + ulonglong rows_read, rows_changed; + ulonglong rows_changed_x_indexes; + /* Stores enum db_type, but forward declarations cannot be done */ + int engine_type; +} TABLE_STATS; + +typedef struct st_index_stats { + char index[NAME_LEN * 3 + 3]; // [db] + '.' + [table] + '.' + [index] + '\0' + ulonglong rows_read; +} INDEX_STATS; + /* Bits in form->update */ #define REG_MAKE_DUPP 1 /* Make a copy of record when read */ #define REG_NEW_RECORD 2 /* Write a new record if not found */ diff -ruN a/sql/table.h b/sql/table.h --- a/sql/table.h 2010-10-12 00:34:16.000000000 +0400 +++ b/sql/table.h 2010-11-24 17:31:33.000000000 +0300 @@ -944,10 +944,12 @@ enum enum_schema_tables { SCH_CHARSETS= 0, + SCH_CLIENT_STATS, SCH_COLLATIONS, SCH_COLLATION_CHARACTER_SET_APPLICABILITY, SCH_COLUMNS, SCH_COLUMN_PRIVILEGES, + SCH_INDEX_STATS, SCH_ENGINES, SCH_EVENTS, SCH_FILES, @@ -971,8 +973,11 @@ SCH_TABLE_CONSTRAINTS, SCH_TABLE_NAMES, SCH_TABLE_PRIVILEGES, + SCH_TABLE_STATS, + SCH_THREAD_STATS, SCH_TRIGGERS, SCH_USER_PRIVILEGES, + SCH_USER_STATS, SCH_VARIABLES, SCH_VIEWS }; diff -ruN a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc --- a/storage/innobase/handler/ha_innodb.cc 2010-10-12 00:34:15.000000000 +0400 +++ b/storage/innobase/handler/ha_innodb.cc 2010-11-24 17:24:52.000000000 +0300 @@ -4055,6 +4055,8 @@ error = row_insert_for_mysql((byte*) record, prebuilt); + if (error == DB_SUCCESS) rows_changed++; + /* Handle duplicate key errors */ if (auto_inc_used) { ulint err; @@ -4392,6 +4394,8 @@ } } + if (error == DB_SUCCESS) rows_changed++; + innodb_srv_conc_exit_innodb(trx); error = convert_error_code_to_mysql(error, user_thd); @@ -4444,6 +4448,8 @@ error = row_update_for_mysql((byte*) record, prebuilt); + if (error == DB_SUCCESS) rows_changed++; + innodb_srv_conc_exit_innodb(trx); error = convert_error_code_to_mysql(error, user_thd); @@ -4923,6 +4929,9 @@ if (ret == DB_SUCCESS) { error = 0; table->status = 0; + rows_read++; + if (active_index >= 0 && active_index < MAX_KEY) + index_rows_read[active_index]++; } else if (ret == DB_RECORD_NOT_FOUND) { error = HA_ERR_END_OF_FILE; diff -ruN a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc --- a/storage/myisam/ha_myisam.cc 2010-10-12 00:34:25.000000000 +0400 +++ b/storage/myisam/ha_myisam.cc 2010-11-24 17:24:52.000000000 +0300 @@ -761,6 +761,7 @@ int ha_myisam::write_row(uchar *buf) { + int error; ha_statistic_increment(&SSV::ha_write_count); /* If we have a timestamp column, update it to the current time */ @@ -773,11 +774,12 @@ */ if (table->next_number_field && buf == table->record[0]) { - int error; if ((error= update_auto_increment())) return error; } - return mi_write(file,buf); + error=mi_write(file,buf); + if (!error) rows_changed++; + return error; } int ha_myisam::check(THD* thd, HA_CHECK_OPT* check_opt) @@ -1638,16 +1640,22 @@ int ha_myisam::update_row(const uchar *old_data, uchar *new_data) { + int error; ha_statistic_increment(&SSV::ha_update_count); if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE) table->timestamp_field->set_time(); - return mi_update(file,old_data,new_data); + error=mi_update(file,old_data,new_data); + if (!error) rows_changed++; + return error; } int ha_myisam::delete_row(const uchar *buf) { + int error; ha_statistic_increment(&SSV::ha_delete_count); - return mi_delete(file,buf); + error=mi_delete(file,buf); + if (!error) rows_changed++; + return error; } int ha_myisam::index_read_map(uchar *buf, const uchar *key, @@ -1658,6 +1666,13 @@ ha_statistic_increment(&SSV::ha_read_key_count); int error=mi_rkey(file, buf, active_index, key, keypart_map, find_flag); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1668,6 +1683,13 @@ ha_statistic_increment(&SSV::ha_read_key_count); int error=mi_rkey(file, buf, index, key, keypart_map, find_flag); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1680,6 +1702,13 @@ int error=mi_rkey(file, buf, active_index, key, keypart_map, HA_READ_PREFIX_LAST); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } DBUG_RETURN(error); } @@ -1689,6 +1718,13 @@ ha_statistic_increment(&SSV::ha_read_next_count); int error=mi_rnext(file,buf,active_index); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1698,6 +1734,13 @@ ha_statistic_increment(&SSV::ha_read_prev_count); int error=mi_rprev(file,buf, active_index); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1707,6 +1750,13 @@ ha_statistic_increment(&SSV::ha_read_first_count); int error=mi_rfirst(file, buf, active_index); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1716,6 +1766,13 @@ ha_statistic_increment(&SSV::ha_read_last_count); int error=mi_rlast(file, buf, active_index); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1731,6 +1788,13 @@ error= mi_rnext_same(file,buf); } while (error == HA_ERR_RECORD_DELETED); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) { + rows_read++; + + int inx = (active_index == MAX_KEY) ? file->lastinx : active_index; + if (inx >= 0 && inx < MAX_KEY) + index_rows_read[inx]++; + } return error; } @@ -1747,6 +1811,7 @@ ha_statistic_increment(&SSV::ha_read_rnd_next_count); int error=mi_scan(file, buf); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) rows_read++; return error; } @@ -1760,6 +1825,7 @@ ha_statistic_increment(&SSV::ha_read_rnd_count); int error=mi_rrnd(file, buf, my_get_ptr(pos,ref_length)); table->status=error ? STATUS_NOT_FOUND: 0; + if (!error) rows_read++; return error; }