mysql-userstats.patch

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