# name : userstat.patch # introduced : 11 or before # maintainer : Oleg # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! --- a/include/mysql/plugin.h +++ b/include/mysql/plugin.h @@ -556,6 +556,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. --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -31,6 +31,7 @@ #define SERVER_VERSION_LENGTH 60 #define SQLSTATE_LENGTH 5 +#define LIST_PROCESS_HOST_LEN 64 /* Maximum length of comments @@ -146,6 +147,11 @@ #define REFRESH_DES_KEY_FILE 0x40000L #define REFRESH_USER_RESOURCES 0x80000L #define REFRESH_QUERY_RESPONSE_TIME 0x100000L /* response time distibution */ +#define REFRESH_TABLE_STATS 0x200000L /* Refresh table stats my_hash table */ +#define REFRESH_INDEX_STATS 0x400000L /* Refresh index stats my_hash table */ +#define REFRESH_USER_STATS 0x800000L /* Refresh user stats my_hash table */ +#define REFRESH_CLIENT_STATS 0x1000000L /* Refresh client stats my_hash table */ +#define REFRESH_THREAD_STATS 0x2000000L /* Refresh thread stats my_hash table */ #define CLIENT_LONG_PASSWORD 1 /* new more secure passwords */ #define CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */ --- /dev/null +++ b/patch_info/userstats.patch @@ -0,0 +1,17 @@ +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" to control INFORMATION_SCHEMA.*_STATISTICS (default:OFF) +2010-12-31 +Ported to 5.5.8 +2011-1-5 +Fix porting +2011-02 +Rename variable USERSTAT_RUNNING => USERSTAT --- a/sql/handler.cc +++ b/sql/handler.cc @@ -1245,6 +1245,8 @@ goto end; } DBUG_EXECUTE_IF("crash_commit_after", DBUG_SUICIDE();); + if (is_real_trans) + thd->diff_commit_trans++; RUN_HOOK(transaction, after_commit, (thd, FALSE)); end: if (rw_trans && mdl_request.ticket) @@ -1399,6 +1401,8 @@ /* Always cleanup. Even if nht==0. There may be savepoints. */ if (is_real_trans) thd->transaction.cleanup(); + + thd->diff_rollback_trans++; if (all) thd->transaction_rollback_request= FALSE; @@ -1803,6 +1807,7 @@ ha_info->reset(); /* keep it conveniently zero-filled */ } trans->ha_list= sv->ha_list; + thd->diff_rollback_trans++; DBUG_RETURN(error); } @@ -2179,6 +2184,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); } @@ -3644,6 +3651,127 @@ return; } +// Updates the global table stats with the TABLE this handler represents. +void handler::update_global_table_stats() +{ + if (!opt_userstat) + { + 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); + + mysql_mutex_lock(&LOCK_global_table_stats); + // Gets the global table stats, creating one if necessary. + if (!(table_stats = (TABLE_STATS *) my_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); + 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: + mysql_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) + { + 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); + + mysql_mutex_lock(&LOCK_global_index_stats); + // Gets the global index stats, creating one if necessary. + if (!(index_stats = (INDEX_STATS *) my_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); + goto end; + } + } + // Updates the global index stats. + index_stats->rows_read+= index_rows_read[x]; + index_rows_read[x]= 0; + end: + mysql_mutex_unlock(&LOCK_global_index_stats); + } + } +} /**************************************************************************** ** Some general functions that isn't in the handler class --- a/sql/handler.h +++ b/sql/handler.h @@ -36,6 +36,10 @@ #include #include +#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 @@ -562,10 +566,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, @@ -593,9 +599,12 @@ SCH_TABLE_CONSTRAINTS, SCH_TABLE_NAMES, SCH_TABLE_PRIVILEGES, + SCH_TABLE_STATS, SCH_TEMPORARY_TABLES, + SCH_THREAD_STATS, SCH_TRIGGERS, SCH_USER_PRIVILEGES, + SCH_USER_STATS, SCH_VARIABLES, SCH_VIEWS }; @@ -1233,6 +1242,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 @@ -1284,10 +1296,12 @@ 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), m_psi(NULL) - {} + { + memset(index_rows_read, 0, sizeof(index_rows_read)); + } virtual ~handler(void) { DBUG_ASSERT(locked == FALSE); @@ -1410,6 +1424,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; } @@ -1805,6 +1821,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 --- a/sql/lex.h +++ b/sql/lex.h @@ -111,6 +111,7 @@ { "CIPHER", SYM(CIPHER_SYM)}, { "CLASS_ORIGIN", SYM(CLASS_ORIGIN_SYM)}, { "CLIENT", SYM(CLIENT_SYM)}, + { "CLIENT_STATISTICS", SYM(CLIENT_STATS_SYM)}, { "CLOSE", SYM(CLOSE_SYM)}, { "COALESCE", SYM(COALESCE)}, { "CODE", SYM(CODE_SYM)}, @@ -257,6 +258,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)}, @@ -550,12 +552,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)}, @@ -591,6 +595,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)}, --- a/sql/log.cc +++ b/sql/log.cc @@ -1007,6 +1007,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 @@ -5062,6 +5069,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) { @@ -5073,12 +5082,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) { @@ -5101,6 +5114,8 @@ flags); if (e.write(file)) goto err; + if (file == &log_file) + thd->binlog_bytes_written+= e.data_written; } } } @@ -5112,6 +5127,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; error= 0; err: @@ -5346,7 +5363,8 @@ 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); @@ -5393,6 +5411,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 @@ -5461,6 +5480,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))); @@ -5584,20 +5604,23 @@ Query_log_event qinfo(thd, STRING_WITH_LEN("BEGIN"), TRUE, FALSE, TRUE, 0); 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; --- a/sql/log.h +++ b/sql/log.h @@ -437,7 +437,8 @@ 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 is_transactional); bool check_write_error(THD *thd); @@ -591,6 +592,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; } --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -445,6 +445,7 @@ MYSQL_PLUGIN_IMPORT 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= 0, opt_thread_statistics= 0; my_bool opt_optimizer_fix= 0; /* True if there is at least one per-hour limit for some user, so we should @@ -496,6 +497,7 @@ ulong binlog_cache_use= 0, binlog_cache_disk_use= 0; ulong binlog_stmt_cache_use= 0, binlog_stmt_cache_disk_use= 0; ulong max_connections, max_connect_errors; +ulonglong denied_connections= 0; /* flashcache */ int cachedev_fd; @@ -645,7 +647,9 @@ LOCK_crypt, LOCK_global_system_variables, LOCK_user_conn, LOCK_slave_list, LOCK_active_mi, - LOCK_connection_count, LOCK_error_messages; + LOCK_connection_count, LOCK_error_messages, + LOCK_stats, LOCK_global_user_client_stats, + LOCK_global_table_stats, LOCK_global_index_stats; /** The below lock protects access to two global server variables: max_prepared_stmt_count and prepared_stmt_count. These variables @@ -1509,6 +1513,11 @@ #ifdef HAVE_RESPONSE_TIME_DISTRIBUTION query_response_time_free(); #endif // HAVE_RESPONSE_TIME_DISTRIBUTION + 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 @@ -1612,6 +1621,10 @@ mysql_cond_destroy(&COND_thread_cache); mysql_cond_destroy(&COND_flush_thread_cache); mysql_cond_destroy(&COND_manager); + mysql_mutex_destroy(&LOCK_stats); + mysql_mutex_destroy(&LOCK_global_user_client_stats); + mysql_mutex_destroy(&LOCK_global_table_stats); + mysql_mutex_destroy(&LOCK_global_index_stats); } #endif /*EMBEDDED_LIBRARY*/ @@ -2938,6 +2951,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_contributors", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CONTRIBUTORS]), SHOW_LONG_STATUS}, {"show_create_db", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_DB]), SHOW_LONG_STATUS}, @@ -2958,6 +2972,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_open_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_OPEN_TABLES]), SHOW_LONG_STATUS}, @@ -2976,10 +2991,13 @@ {"show_slave_status_nolock", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_NOLOCK_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_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_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}, @@ -3517,6 +3535,13 @@ mysql_mutex_init(key_LOCK_server_started, &LOCK_server_started, MY_MUTEX_INIT_FAST); mysql_cond_init(key_COND_server_started, &COND_server_started, NULL); + mysql_mutex_init(key_LOCK_stats, &LOCK_stats, MY_MUTEX_INIT_FAST); + mysql_mutex_init(key_LOCK_global_user_client_stats, + &LOCK_global_user_client_stats, MY_MUTEX_INIT_FAST); + mysql_mutex_init(key_LOCK_global_table_stats, + &LOCK_global_table_stats, MY_MUTEX_INIT_FAST); + mysql_mutex_init(key_LOCK_global_index_stats, + &LOCK_global_index_stats, MY_MUTEX_INIT_FAST); sp_cache_init(); #ifdef HAVE_EVENT_SCHEDULER Events::init_mutexes(); @@ -3886,6 +3911,9 @@ query_response_time_init(); #endif // HAVE_RESPONSE_TIME_DISTRIBUTION /* We have to initialize the storage engines before CSV logging */ + init_global_table_stats(); + init_global_index_stats(); + if (ha_init()) { sql_print_error("Can't init databases"); @@ -4022,6 +4050,9 @@ init_max_user_conn(); init_update_queries(); + init_global_user_stats(); + init_global_client_stats(); + init_global_thread_stats(); DBUG_RETURN(0); } @@ -5087,6 +5118,7 @@ { sql_print_warning("%s", ER_DEFAULT(ER_CON_COUNT_ERROR)); } + statistic_increment(denied_connections, &LOCK_status); delete thd; DBUG_VOID_RETURN; } @@ -7825,6 +7857,8 @@ key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi, key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create, key_LOCK_delayed_insert, key_LOCK_delayed_status, key_LOCK_error_log, + key_LOCK_stats, key_LOCK_global_user_client_stats, + key_LOCK_global_table_stats, key_LOCK_global_index_stats, key_LOCK_gdl, key_LOCK_global_system_variables, key_LOCK_manager, key_LOCK_prepared_stmt_count, @@ -7864,6 +7898,13 @@ { &key_LOCK_delayed_insert, "LOCK_delayed_insert", PSI_FLAG_GLOBAL}, { &key_LOCK_delayed_status, "LOCK_delayed_status", PSI_FLAG_GLOBAL}, { &key_LOCK_error_log, "LOCK_error_log", PSI_FLAG_GLOBAL}, + { &key_LOCK_stats, "LOCK_stats", PSI_FLAG_GLOBAL}, + { &key_LOCK_global_user_client_stats, + "LOCK_global_user_client_stats", PSI_FLAG_GLOBAL}, + { &key_LOCK_global_table_stats, + "LOCK_global_table_stats", PSI_FLAG_GLOBAL}, + { &key_LOCK_global_index_stats, + "LOCK_global_index_stats", PSI_FLAG_GLOBAL}, { &key_LOCK_gdl, "LOCK_gdl", PSI_FLAG_GLOBAL}, { &key_LOCK_global_system_variables, "LOCK_global_system_variables", PSI_FLAG_GLOBAL}, { &key_LOCK_manager, "LOCK_manager", PSI_FLAG_GLOBAL}, --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -23,6 +23,7 @@ #include "my_atomic.h" /* my_atomic_rwlock_t */ #include "mysql/psi/mysql_file.h" /* MYSQL_FILE */ #include "sql_list.h" /* I_List */ +#include "hash.h" class THD; struct handlerton; @@ -114,6 +115,7 @@ extern ulonglong slave_type_conversions_options; extern my_bool read_only, opt_readonly; extern my_bool lower_case_file_system; +extern my_bool opt_userstat, opt_thread_statistics; extern my_bool opt_optimizer_fix; extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs; extern my_bool opt_secure_auth; @@ -183,6 +185,7 @@ extern ulong slave_trans_retries; extern uint slave_net_timeout; extern uint max_user_connections; +extern ulonglong denied_connections; extern ulong what_to_log,flush_time; extern ulong max_prepared_stmt_count, prepared_stmt_count; extern ulong open_files_limit; @@ -210,6 +213,11 @@ 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 HASH global_table_stats; +extern HASH global_index_stats; extern const char *opt_date_time_formats[]; extern handlerton *partition_hton; extern handlerton *myisam_hton; @@ -252,6 +260,8 @@ key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi, key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create, key_LOCK_delayed_insert, key_LOCK_delayed_status, key_LOCK_error_log, + key_LOCK_stats, key_LOCK_global_user_client_stats, + key_LOCK_global_table_stats, key_LOCK_global_index_stats, key_LOCK_gdl, key_LOCK_global_system_variables, key_LOCK_logger, key_LOCK_manager, key_LOCK_prepared_stmt_count, @@ -351,7 +361,9 @@ LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone, LOCK_slave_list, LOCK_active_mi, LOCK_manager, LOCK_global_system_variables, LOCK_user_conn, - LOCK_prepared_stmt_count, LOCK_error_messages, LOCK_connection_count; + LOCK_prepared_stmt_count, LOCK_error_messages, LOCK_connection_count, + LOCK_stats, LOCK_global_user_client_stats, + LOCK_global_table_stats, LOCK_global_index_stats; extern MYSQL_PLUGIN_IMPORT mysql_mutex_t LOCK_thread_count; #ifdef HAVE_OPENSSL extern mysql_mutex_t LOCK_des_key_file; @@ -463,6 +475,16 @@ return id; } +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_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); /* TODO: Replace this with an inline function. --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1587,6 +1587,11 @@ table->mdl_ticket= NULL; mysql_mutex_lock(&thd->LOCK_thd_data); + if(table->file) + { + table->file->update_global_table_stats(); + table->file->update_global_index_stats(); + } *table_ptr=table->next; mysql_mutex_unlock(&thd->LOCK_thd_data); @@ -2225,6 +2230,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) --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -978,6 +978,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 @@ -1357,6 +1364,7 @@ variables.option_bits|= OPTION_BIN_LOG; else variables.option_bits&= ~OPTION_BIN_LOG; + reset_stats(); #if defined(ENABLED_DEBUG_SYNC) /* Initialize the Debug Sync Facility. See debug_sync.cc. */ @@ -1366,6 +1374,94 @@ clear_slow_extended(); } +// 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) + { + 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. @@ -2125,6 +2221,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 { @@ -2301,6 +2423,7 @@ } thd->sent_row_count++; + thd->sent_row_count_2++; if (thd->vio_ok()) DBUG_RETURN(protocol->write()); @@ -2393,6 +2516,7 @@ select_export::~select_export() { thd->sent_row_count=row_count; + thd->sent_row_count_2= row_count; } @@ -3416,6 +3540,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; } } @@ -3423,6 +3548,7 @@ void thd_increment_bytes_received(ulong length) { current_thd->status_var.bytes_received+= length; + current_thd->bytes_received+= length; } --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1705,6 +1705,8 @@ */ enum enum_server_command command; uint32 server_id; + // Used to save the command, before it is set to COM_SLEEP. + enum enum_server_command old_command; uint32 file_id; // for LOAD DATA INFILE /* remote (peer) port */ uint16 peer_port; @@ -2214,6 +2216,8 @@ */ enum_tx_isolation 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 */ @@ -2308,6 +2312,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 { @@ -2388,6 +2435,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(); @@ -2860,6 +2912,15 @@ } thd_scheduler scheduler; + /* 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. + */ + char *get_client_host_port(THD *client); + public: inline Internal_error_handler *get_internal_handler() { return m_internal_handler; } @@ -3060,6 +3121,10 @@ 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->stmt_da->set_ok_status(). */ --- a/sql/sql_connect.cc +++ b/sql/sql_connect.cc @@ -56,6 +56,24 @@ #define MIN_HANDSHAKE_SIZE 6 #endif /* HAVE_OPENSSL && !EMBEDDED_LIBRARY */ +// 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 mysql_mutex_t LOCK_global_user_client_stats; + +HASH global_table_stats; +extern mysql_mutex_t LOCK_global_table_stats; + +HASH global_index_stats; +extern mysql_mutex_t LOCK_global_index_stats; + /* Get structure for logging connection data for the current user */ @@ -113,6 +131,586 @@ } +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); +} + +void free_thread_stats(THREAD_STATS* thread_stats) +{ + my_free((char *) thread_stats); +} + +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 (my_hash_init(&global_user_stats, system_charset_info, max_connections, + 0, 0, (my_hash_get_key)get_key_user_stats, + (my_hash_free_key)free_user_stats, 0)) { + sql_print_error("Initializing global_user_stats failed."); + exit(1); + } +} + +void init_global_client_stats(void) +{ + if (my_hash_init(&global_client_stats, system_charset_info, max_connections, + 0, 0, (my_hash_get_key)get_key_user_stats, + (my_hash_free_key)free_user_stats, 0)) { + sql_print_error("Initializing global_client_stats failed."); + exit(1); + } +} + +void init_global_thread_stats(void) +{ + if (my_hash_init(&global_thread_stats, &my_charset_bin, max_connections, + 0, 0, (my_hash_get_key) get_key_thread_stats, + (my_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); +} + +void init_global_table_stats(void) +{ + if (my_hash_init(&global_table_stats, system_charset_info, max_connections, + 0, 0, (my_hash_get_key)get_key_table_stats, + (my_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); +} + +void init_global_index_stats(void) +{ + if (my_hash_init(&global_index_stats, system_charset_info, max_connections, + 0, 0, (my_hash_get_key)get_key_index_stats, + (my_hash_free_key)free_index_stats, 0)) { + sql_print_error("Initializing global_index_stats failed."); + exit(1); + } +} + +void free_global_user_stats(void) +{ + my_hash_free(&global_user_stats); +} + +void free_global_thread_stats(void) +{ + my_hash_free(&global_thread_stats); +} + +void free_global_table_stats(void) +{ + my_hash_free(&global_table_stats); +} + +void free_global_index_stats(void) +{ + my_hash_free(&global_index_stats); +} + +void free_global_client_stats(void) +{ + my_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 *) my_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); + 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 *) my_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); + 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) + return return_value; + + if (use_lock) + mysql_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) + mysql_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) + { + 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; + mysql_mutex_lock(&LOCK_global_user_client_stats); + + // Update by user name + if ((user_stats = (USER_STATS *) my_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 *) my_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 *) my_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(); + + mysql_mutex_unlock(&LOCK_global_user_client_stats); + } + else + { + thd->reset_diff_stats(); + } +} /* check if user has already too many connections @@ -170,6 +768,7 @@ if (error) { uc->connections--; // no need for decrease_user_connections() here + statistic_increment(denied_connections, &LOCK_status); /* The thread may returned back to the pool and assigned to a user that doesn't have a limit. Ensure the user is not using resources @@ -589,11 +1188,18 @@ 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)) + DBUG_RETURN(1); + DBUG_RETURN(0); } @@ -623,6 +1229,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) @@ -787,10 +1394,14 @@ for (;;) { bool rc; + bool create_user= TRUE; rc= thd_prepare_connection(thd); if (rc) + { + create_user= FALSE; goto end_thread; + } while (thd_is_connection_alive(thd)) { @@ -802,6 +1413,8 @@ end_thread: close_connection(thd); + thd->update_stats(false); + update_global_user_stats(thd, create_user, time(NULL)); if (MYSQL_CALLBACK_ELSE(thread_scheduler, end_thread, (thd, 1), 0)) return; // Probably no-threads --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -411,6 +411,7 @@ my_ok(thd, deleted); DBUG_PRINT("info",("%ld records deleted",(long) deleted)); } + thd->updated_row_count+= deleted; DBUG_RETURN(error >= 0 || thd->is_error()); } @@ -1005,6 +1006,7 @@ { ::my_ok(thd, deleted); } + thd->updated_row_count+= deleted; return 0; } --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1072,13 +1072,14 @@ if (error) goto abort; + ha_rows row_count; if (values_list.elements == 1 && (!(thd->variables.option_bits & OPTION_WARNINGS) || !thd->cuted_fields)) { - my_ok(thd, info.copied + info.deleted + + row_count= info.copied + info.deleted + ((thd->client_capabilities & CLIENT_FOUND_ROWS) ? - info.touched : info.updated), - id); + info.touched : info.updated); + my_ok(thd, row_count, id); } else { @@ -1094,8 +1095,10 @@ sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records, (ulong) (info.deleted + updated), (ulong) thd->warning_info->statement_warn_count()); - ::my_ok(thd, info.copied + info.deleted + updated, id, buff); + row_count= info.copied + info.deleted + updated; + ::my_ok(thd, row_count, id, buff); } + thd->updated_row_count+= row_count; thd->abort_on_warning= 0; DBUG_RETURN(FALSE); @@ -3540,6 +3543,7 @@ thd->first_successful_insert_id_in_prev_stmt : (info.copied ? autoinc_value_of_last_inserted_row : 0)); ::my_ok(thd, row_count, id, buff); + thd->updated_row_count+= row_count; DBUG_RETURN(0); } --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -197,6 +197,9 @@ in "struct show_var_st status_vars[]= {" ... */ SQLCOM_SHOW_TEMPORARY_TABLES, + // 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 }; --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -117,6 +117,9 @@ static void sql_kill(THD *thd, ulong id, bool only_kill_query); static inline ulonglong get_query_exec_time(THD *thd, ulonglong cur_utime); +// 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[]={ @@ -703,6 +706,12 @@ */ thd->clear_error(); // Clear error message thd->stmt_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); @@ -888,6 +897,10 @@ (char *) thd->security_ctx->host_or_ip); 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. @@ -1683,6 +1696,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: @@ -1857,6 +1877,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); } @@ -4892,6 +4913,7 @@ case ACL_INTERNAL_ACCESS_DENIED: if (! no_errors) { + thd->diff_access_denied_errors++; my_error(ER_DBACCESS_DENIED_ERROR, MYF(0), sctx->priv_user, sctx->priv_host, db); } @@ -4942,6 +4964,7 @@ DBUG_PRINT("error",("No possible access")); if (!no_errors) { + thd->diff_access_denied_errors++; if (thd->password == 2) my_error(ER_ACCESS_DENIED_NO_PASSWORD_ERROR, MYF(0), sctx->priv_user, @@ -5058,6 +5081,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, @@ -5328,6 +5352,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 @@ -5695,6 +5720,32 @@ 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) + { +#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; @@ -5763,6 +5814,52 @@ DBUG_ASSERT(thd->change_list.is_empty()); } + if (opt_userstat) + { + // 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; } --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -114,6 +114,9 @@ #endif #include "lock.h" // MYSQL_OPEN_FORCE_SHARED_MDL +// 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. */ @@ -2173,8 +2176,34 @@ /* 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) + { +#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)) { @@ -2182,7 +2211,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; } thd->protocol= &thd->protocol_binary; @@ -2196,6 +2225,53 @@ thd->protocol= save_protocol; /* check_prepared_statemnt sends the metadata packet in case of success */ +end: + if (opt_userstat) + { + // 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; } @@ -2540,12 +2616,38 @@ /* 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) + { +#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), static_cast(sizeof(llbuf)), llstr(stmt_id, llbuf), "mysqld_stmt_execute"); - DBUG_VOID_RETURN; + goto end; } #if defined(ENABLED_PROFILING) @@ -2563,6 +2665,53 @@ /* 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) + { + // 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; } @@ -2635,20 +2784,47 @@ /* 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) + { +#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), static_cast(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; @@ -2665,6 +2841,52 @@ thd->restore_backup_statement(stmt, &stmt_backup); thd->stmt_arena= thd; +end: + if (opt_userstat) + { + // 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; } @@ -2695,13 +2917,39 @@ /* 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) + { +#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), static_cast(sizeof(llbuf)), llstr(stmt_id, llbuf), "mysqld_stmt_reset"); - DBUG_VOID_RETURN; + goto end; } stmt->close_cursor(); @@ -2718,6 +2966,53 @@ my_ok(thd); +end: + if (opt_userstat) + { + // 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; } --- a/sql/sql_reload.cc +++ b/sql/sql_reload.cc @@ -320,14 +320,48 @@ mysql_mutex_unlock(&LOCK_active_mi); } #endif - if (options & REFRESH_USER_RESOURCES) - reset_mqh((LEX_USER *) NULL, 0); /* purecov: inspected */ #ifdef HAVE_RESPONSE_TIME_DISTRIBUTION if (options & REFRESH_QUERY_RESPONSE_TIME) { query_response_time_flush(); } #endif // HAVE_RESPONSE_TIME_DISTRIBUTION + if (options & REFRESH_USER_RESOURCES) + reset_mqh((LEX_USER *) NULL, 0); /* purecov: inspected */ + if (options & REFRESH_TABLE_STATS) + { + mysql_mutex_lock(&LOCK_global_table_stats); + free_global_table_stats(); + init_global_table_stats(); + mysql_mutex_unlock(&LOCK_global_table_stats); + } + if (options & REFRESH_INDEX_STATS) + { + mysql_mutex_lock(&LOCK_global_index_stats); + free_global_index_stats(); + init_global_index_stats(); + mysql_mutex_unlock(&LOCK_global_index_stats); + } + if (options & (REFRESH_USER_STATS | REFRESH_CLIENT_STATS | REFRESH_THREAD_STATS)) + { + mysql_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(); + } + mysql_mutex_unlock(&LOCK_global_user_client_stats); + } if (*write_to_binlog != -1) *write_to_binlog= tmp_write_to_binlog; /* --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -114,6 +114,43 @@ 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 ***************************************************************************/ @@ -800,6 +837,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), @@ -2359,6 +2397,284 @@ 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 *) my_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 *) my_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. + + mysql_mutex_lock(&LOCK_global_user_client_stats); + int result= send_user_stats(thd, &global_user_stats, table); + mysql_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. + + mysql_mutex_lock(&LOCK_global_user_client_stats); + int result= send_user_stats(thd, &global_client_stats, table); + mysql_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. + + mysql_mutex_lock(&LOCK_global_user_client_stats); + int result= send_thread_stats(thd, &global_thread_stats, table); + mysql_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; + + mysql_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 *) my_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, tmp_table.db, + &tmp_table.grant.privilege, 0, 0, + is_infoschema_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)) + { + mysql_mutex_unlock(&LOCK_global_table_stats); + DBUG_RETURN(1); + } + } + mysql_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; + + mysql_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 *) my_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, tmp_table.db, + &tmp_table.grant.privilege, 0, 0, + is_infoschema_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)) + { + mysql_mutex_unlock(&LOCK_global_index_stats); + DBUG_RETURN(1); + } + } + mysql_mutex_unlock(&LOCK_global_index_stats); + DBUG_RETURN(0); +} + /* collect status for all running threads */ @@ -7712,6 +8028,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}, @@ -7901,6 +8315,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, @@ -7910,6 +8326,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 @@ -7982,14 +8400,20 @@ 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}, {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0, OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE}, + {"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_TRIGGER_ONLY|OPTIMIZE_I_S_TABLE}, {"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, --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -900,8 +900,10 @@ my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, (ulong) thd->warning_info->statement_warn_count()); - my_ok(thd, (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated, - id, buff); + ha_rows row_count= + (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; + my_ok(thd, row_count, id, buff); + thd->updated_row_count += row_count; DBUG_PRINT("info",("%ld records updated", (long) updated)); } thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */ @@ -2252,7 +2254,9 @@ thd->first_successful_insert_id_in_prev_stmt : 0; my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, (ulong) thd->cuted_fields); - ::my_ok(thd, (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated, - id, buff); + ha_rows row_count= + (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated; + ::my_ok(thd, row_count, id, buff); + thd->updated_row_count+= row_count; DBUG_RETURN(FALSE); } --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -865,6 +865,7 @@ %token CIPHER_SYM %token CLASS_ORIGIN_SYM /* SQL-2003-N */ %token CLIENT_SYM +%token CLIENT_STATS_SYM %token CLOSE_SYM /* SQL-2003-R */ %token COALESCE /* SQL-2003-N */ %token CODE_SYM @@ -1018,6 +1019,7 @@ %token IMPORT %token INDEXES %token INDEX_SYM +%token INDEX_STATS_SYM %token INFILE %token INITIAL_SIZE_SYM %token INNER_SYM /* SQL-2003-R */ @@ -1316,6 +1318,7 @@ %token TABLESPACE %token TABLE_REF_PRIORITY %token TABLE_SYM /* SQL-2003-R */ +%token TABLE_STATS_SYM %token TABLE_CHECKSUM_SYM %token TABLE_NAME_SYM /* SQL-2003-N */ %token TEMPORARY /* SQL-2003-N */ @@ -1325,6 +1328,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 @@ -1362,6 +1366,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 */ @@ -11126,6 +11131,41 @@ MYSQL_YYABORT; #endif // HAVE_RESPONSE_TIME_DISTRIBUTION } + | 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_SYM sp_name { LEX *lex= Lex; @@ -11371,6 +11411,16 @@ Lex->type|= REFRESH_QUERY_RESPONSE_TIME; #endif // HAVE_RESPONSE_TIME_DISTRIBUTION } + | 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 @@ -12515,6 +12565,7 @@ | CHAIN_SYM {} | CHANGED {} | CIPHER_SYM {} + | CLIENT_STATS_SYM {} | CLIENT_SYM {} | CLASS_ORIGIN_SYM {} | COALESCE {} @@ -12583,6 +12634,7 @@ | HOSTS_SYM {} | HOUR_SYM {} | IDENTIFIED_SYM {} + | INDEX_STATS_SYM {} | IGNORE_SERVER_IDS_SYM {} | INVOKER_SYM {} | IMPORT {} @@ -12734,6 +12786,7 @@ | SUSPEND_SYM {} | SWAPS_SYM {} | SWITCHES_SYM {} + | TABLE_STATS_SYM {} | TABLE_NAME_SYM {} | TABLES {} | TABLE_CHECKSUM_SYM {} @@ -12759,6 +12812,7 @@ | UNKNOWN_SYM {} | UNTIL_SYM {} | USER {} + | USER_STATS_SYM {} | USE_FRM {} | VARIABLES {} | VIEW_SYM {} --- a/sql/structs.h +++ b/sql/structs.h @@ -25,6 +25,7 @@ #include "my_time.h" /* enum_mysql_timestamp_type */ #include "thr_lock.h" /* thr_lock_type */ #include "my_base.h" /* ha_rows, ha_key_alg */ +#include "mysql_com.h" struct TABLE; class Field; @@ -218,6 +219,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 my_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 my_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 my_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 my_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 */ --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -1711,6 +1711,17 @@ NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_read_only), ON_UPDATE(fix_read_only)); +static Sys_var_mybool Sys_userstat( + "userstat", + "Control USER_STATISTICS, CLIENT_STATISTICS, THREAD_STATISTICS, " + "INDEX_STATISTICS and TABLE_STATISTICS running", + GLOBAL_VAR(opt_userstat), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); + +static Sys_var_mybool Sys_thread_statistics( + "thread_statistics", + "Control TABLE_STATISTICS running, when userstat is enabled", + GLOBAL_VAR(opt_thread_statistics), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); + // Small lower limit to be able to test MRR static Sys_var_ulong Sys_read_rnd_buff_size( "read_rnd_buffer_size", --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -770,6 +770,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 */ @@ -782,11 +783,13 @@ */ 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) @@ -1553,16 +1556,24 @@ 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, @@ -1574,6 +1585,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1586,6 +1605,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1600,6 +1627,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); DBUG_RETURN(error); } @@ -1611,6 +1646,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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1622,6 +1664,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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1633,6 +1682,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1644,6 +1701,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1661,6 +1726,14 @@ 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]++; + } MYSQL_INDEX_READ_ROW_DONE(error); return error; } @@ -1680,6 +1753,8 @@ 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++; MYSQL_READ_ROW_DONE(error); return error; } @@ -1696,6 +1771,8 @@ 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++; MYSQL_READ_ROW_DONE(error); return error; } --- /dev/null +++ b/mysql-test/r/userstat_bug602047.result @@ -0,0 +1,15 @@ +DROP TABLE IF EXISTS t1; +SET GLOBAL userstat=ON; +CREATE TABLE t1 ( id int(10), PRIMARY KEY (id)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT ROWS_READ FROM information_schema.table_statistics WHERE TABLE_NAME='t1'; +ROWS_READ +10 +SELECT ROWS_READ FROM information_schema.index_statistics WHERE TABLE_NAME='t1'; +ROWS_READ +10 +SET GLOBAL userstat=OFF; +DROP TABLE t1; --- /dev/null +++ b/mysql-test/t/userstat_bug602047.test @@ -0,0 +1,11 @@ +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +SET GLOBAL userstat=ON; +CREATE TABLE t1 ( id int(10), PRIMARY KEY (id)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SELECT COUNT(*) FROM t1; +SELECT ROWS_READ FROM information_schema.table_statistics WHERE TABLE_NAME='t1'; +SELECT ROWS_READ FROM information_schema.index_statistics WHERE TABLE_NAME='t1'; +SET GLOBAL userstat=OFF; +DROP TABLE t1; \ No newline at end of file --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -745,6 +745,8 @@ Define threads usage for handling queries, one of one-thread-per-connection, no-threads, loaded-dynamically --thread-stack=# The stack size for each thread + --thread-statistics Control TABLE_STATISTICS running, when userstat is + enabled --time-format=name The TIME format (ignored) --timed-mutexes Specify whether to time mutexes (only InnoDB mutexes are currently supported) @@ -770,6 +772,9 @@ of the underlying table and the query uses a LIMIT clause (usually get from GUI tools) -u, --user=name Run mysqld daemon as user. + --userstat Control USER_STATISTICS, CLIENT_STATISTICS, + THREAD_STATISTICS, INDEX_STATISTICS and TABLE_STATISTICS + running -v, --verbose Used with --help option for detailed help. -V, --version Output version information and exit. --wait-timeout=# The number of seconds the server waits for activity on a @@ -1005,6 +1010,7 @@ thread-cache-size 0 thread-handling one-thread-per-connection thread-stack 262144 +thread-statistics FALSE time-format %H:%i:%s timed-mutexes FALSE tmp-table-size 16777216 @@ -1012,6 +1018,7 @@ transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES +userstat FALSE verbose TRUE wait-timeout 28800 xtradb-admin-command ON