mysql-userstats.patch

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