mysql-innodb_show_bp.patch

   1 diff -ruN a/innobase/btr/btr0btr.c b/innobase/btr/btr0btr.c
   2 --- a/innobase/btr/btr0btr.c    Mon Nov 10 19:47:27 2008 -0800
   3 +++ b/innobase/btr/btr0btr.c    Mon Nov 10 19:48:24 2008 -0800
   4 @@ -2989,3 +2989,11 @@
   5
   6         return(TRUE);
   7  }
   8 +
   9 +dulint
  10 +btr_page_get_index_id_noninline(
  11 +/*============*/
  12 +        page_t* page)  /* in: index page */
  13 +{
  14 +  return btr_page_get_index_id(page);
  15 +}
  16 diff -ruN a/innobase/buf/buf0buf.c b/innobase/buf/buf0buf.c
  17 --- a/innobase/buf/buf0buf.c    Mon Nov 10 19:47:27 2008 -0800
  18 +++ b/innobase/buf/buf0buf.c    Mon Nov 10 19:48:24 2008 -0800
  19 @@ -2647,3 +2647,13 @@
  20         buf_block_print(block);
  21  }
  22
  23 +buf_block_t*
  24 +buf_pool_get_nth_block_no_inline(
  25 +/*===================*/
  26 +                                /* out: pointer to block */
  27 +        buf_pool_t*     buf_pool,/* in: buf_pool */
  28 +        ulint           i)      /* in: index of the block */{
  29 +
  30 +return buf_pool_get_nth_block(buf_pool, i);
  31 +
  32 +}
  33 diff -ruN a/innobase/include/btr0btr.h b/innobase/include/btr0btr.h
  34 --- a/innobase/include/btr0btr.h        Mon Nov 10 19:47:27 2008 -0800
  35 +++ b/innobase/include/btr0btr.h        Mon Nov 10 19:48:24 2008 -0800
  36 @@ -72,6 +72,12 @@
  37  /*==================*/
  38                                 /* out: index id */
  39         page_t*         page);  /* in: index page */
  40 +
  41 +dulint
  42 +btr_page_get_index_id_noninline(
  43 +/*==================*/
  44 +                               /* out: index id */
  45 +       page_t*         page);  /* in: index page */
  46  /************************************************************
  47  Gets the node level field in an index page. */
  48  UNIV_INLINE
  49 diff -ruN a/innobase/include/buf0buf.h b/innobase/include/buf0buf.h
  50 --- a/innobase/include/buf0buf.h        Mon Nov 10 19:47:27 2008 -0800
  51 +++ b/innobase/include/buf0buf.h        Mon Nov 10 19:48:24 2008 -0800
  52 @@ -706,6 +706,8 @@
  53  buf_get_free_list_len(void);
  54  /*=======================*/
  55
  56 +void buf_pool_dump(void);
  57 +buf_block_t* buf_pool_get_nth_block_no_inline(buf_pool_t* pool, ulint i);
  58
  59
  60  /* The buffer control block structure */
  61 diff -ruN a/innobase/include/page0page.h b/innobase/include/page0page.h
  62 --- a/innobase/include/page0page.h      Mon Nov 10 19:47:27 2008 -0800
  63 +++ b/innobase/include/page0page.h      Mon Nov 10 19:48:24 2008 -0800
  64 @@ -260,6 +260,12 @@
  65  /*============*/
  66                         /* out: number of user records */
  67         page_t* page);  /* in: index page */
  68 +
  69 +ulint
  70 +page_get_n_recs_noninline(
  71 +/*============*/
  72 +                       /* out: number of user records */
  73 +       page_t* page);  /* in: index page */
  74  /*******************************************************************
  75  Returns the number of records before the given record in chain.
  76  The number includes infimum and supremum records. */
  77 @@ -522,6 +528,12 @@
  78  /*===============*/
  79                         /* out: data in bytes */
  80         page_t* page);  /* in: index page */
  81 +
  82 +ulint
  83 +page_get_data_size_noninline(
  84 +/*===============*/
  85 +                       /* out: data in bytes */
  86 +       page_t* page);  /* in: index page */
  87  /****************************************************************
  88  Allocates a block of memory from an index page. */
  89
  90 diff -ruN a/innobase/page/page0page.c b/innobase/page/page0page.c
  91 --- a/innobase/page/page0page.c Mon Nov 10 19:47:27 2008 -0800
  92 +++ b/innobase/page/page0page.c Mon Nov 10 19:48:24 2008 -0800
  93 @@ -1994,3 +1994,25 @@
  94                 page_cur_move_to_next(&cur);
  95         }
  96  }
  97 +
  98 +ulint
  99 +page_get_n_recs_noninline(
 100 +/*============*/
 101 +                        /* out: number of user records */
 102 +        page_t* page)  /* in: index page */
 103 +{
 104 + return page_get_n_recs(page);
 105 +}
 106 +
 107 +
 108 +ulint
 109 +page_get_data_size_noninline(
 110 +/*============*/
 111 +                        /* out: number of user records */
 112 +        page_t* page)  /* in: index page */
 113 +{
 114 + return page_get_data_size(page);
 115 +}
 116 +
 117 +
 118 +
 119 diff -ruN a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
 120 --- a/mysql-test/r/information_schema.result    Mon Nov 10 19:47:27 2008 -0800
 121 +++ b/mysql-test/r/information_schema.result    Mon Nov 10 19:48:25 2008 -0800
 122 @@ -42,6 +42,7 @@
 123  COLLATION_CHARACTER_SET_APPLICABILITY
 124  COLUMNS
 125  COLUMN_PRIVILEGES
 126 +INNODB_BUFFER_POOL_CONTENT
 127  INDEX_STATISTICS
 128  KEY_COLUMN_USAGE
 129  PROCESSLIST
 130 @@ -741,7 +742,7 @@
 131  CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
 132  CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
 133  count(*)
 134 -107
 135 +108
 136  drop view a2, a1;
 137  drop table t_crashme;
 138  select table_schema,table_name, column_name from
 139 @@ -802,6 +803,7 @@
 140  TABLE_NAME     COLUMN_NAME     PRIVILEGES
 141  COLUMNS        TABLE_NAME      select
 142  COLUMN_PRIVILEGES      TABLE_NAME      select
 143 +INNODB_BUFFER_POOL_CONTENT     TABLE_NAME      select
 144  INDEX_STATISTICS       TABLE_NAME      select
 145  KEY_COLUMN_USAGE       TABLE_NAME      select
 146  STATISTICS     TABLE_NAME      select
 147 @@ -815,7 +817,7 @@
 148  flush privileges;
 149  SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
 150  table_schema   count(*)
 151 -information_schema     22
 152 +information_schema     23
 153  mysql  17
 154  create table t1 (i int, j int);
 155  create trigger trg1 before insert on t1 for each row
 156 @@ -1206,6 +1208,7 @@
 157  COLLATION_CHARACTER_SET_APPLICABILITY  COLLATION_NAME
 158  COLUMNS        TABLE_SCHEMA
 159  COLUMN_PRIVILEGES      TABLE_SCHEMA
 160 +INNODB_BUFFER_POOL_CONTENT     TABLE_SCHEMA
 161  INDEX_STATISTICS       TABLE_SCHEMA
 162  KEY_COLUMN_USAGE       CONSTRAINT_SCHEMA
 163  PROCESSLIST    ID
 164 @@ -1243,6 +1246,7 @@
 165  COLLATION_CHARACTER_SET_APPLICABILITY  COLLATION_NAME
 166  COLUMNS        TABLE_SCHEMA
 167  COLUMN_PRIVILEGES      TABLE_SCHEMA
 168 +INNODB_BUFFER_POOL_CONTENT     TABLE_SCHEMA
 169  INDEX_STATISTICS       TABLE_SCHEMA
 170  KEY_COLUMN_USAGE       CONSTRAINT_SCHEMA
 171  PROCESSLIST    ID
 172 @@ -1332,6 +1336,7 @@
 173  COLUMNS        information_schema.COLUMNS      1
 174  COLUMN_PRIVILEGES      information_schema.COLUMN_PRIVILEGES    1
 175  INDEX_STATISTICS       information_schema.INDEX_STATISTICS     1
 176 +INNODB_BUFFER_POOL_CONTENT     information_schema.INNODB_BUFFER_POOL_CONTENT   1
 177  KEY_COLUMN_USAGE       information_schema.KEY_COLUMN_USAGE     1
 178  PROCESSLIST    information_schema.PROCESSLIST  1
 179  PROFILING      information_schema.PROFILING    1
 180 diff -ruN a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result
 181 --- a/mysql-test/r/information_schema_db.result Mon Nov 10 19:47:27 2008 -0800
 182 +++ b/mysql-test/r/information_schema_db.result Mon Nov 10 19:48:25 2008 -0800
 183 @@ -11,6 +11,7 @@
 184  COLLATION_CHARACTER_SET_APPLICABILITY
 185  COLUMNS
 186  COLUMN_PRIVILEGES
 187 +INNODB_BUFFER_POOL_CONTENT
 188  INDEX_STATISTICS
 189  KEY_COLUMN_USAGE
 190  PROCESSLIST
 191 diff -ruN a/mysql-test/r/mysqlshow.result b/mysql-test/r/mysqlshow.result
 192 --- a/mysql-test/r/mysqlshow.result     Mon Nov 10 19:47:27 2008 -0800
 193 +++ b/mysql-test/r/mysqlshow.result     Mon Nov 10 19:48:25 2008 -0800
 194 @@ -85,6 +85,7 @@
 195  | COLLATION_CHARACTER_SET_APPLICABILITY |
 196  | COLUMNS                               |
 197  | COLUMN_PRIVILEGES                     |
 198 +| INNODB_BUFFER_POOL_CONTENT            |
 199  | INDEX_STATISTICS                      |
 200  | KEY_COLUMN_USAGE                      |
 201  | PROCESSLIST                           |
 202 @@ -112,6 +113,7 @@
 203  | COLLATION_CHARACTER_SET_APPLICABILITY |
 204  | COLUMNS                               |
 205  | COLUMN_PRIVILEGES                     |
 206 +| INNODB_BUFFER_POOL_CONTENT            |
 207  | INDEX_STATISTICS                      |
 208  | KEY_COLUMN_USAGE                      |
 209  | PROCESSLIST                           |
 210 diff -ruN /dev/null b/patch_info/innodb_show_bp.info
 211 --- /dev/null   Thu Jan 01 00:00:00 1970 +0000
 212 +++ b/patch_info/innodb_show_bp.info    Mon Nov 10 19:48:25 2008 -0800
 213 @@ -0,0 +1,6 @@
 214 +File=innodb_show_bp.patch
 215 +Name=show innodb buffer pool content
 216 +Version=1.0
 217 +Author=Percona <info@percona.com>
 218 +License=GPL
 219 +Comment=
 220 diff -ruN a/sql/ha_innodb.cc b/sql/ha_innodb.cc
 221 --- a/sql/ha_innodb.cc  Mon Nov 10 19:47:27 2008 -0800
 222 +++ b/sql/ha_innodb.cc  Mon Nov 10 19:48:25 2008 -0800
 223 @@ -128,10 +128,12 @@
 224  #include "../innobase/include/lock0lock.h"
 225  #include "../innobase/include/dict0crea.h"
 226  #include "../innobase/include/btr0cur.h"
 227 +#include "../innobase/include/buf0buf.h"
 228  #include "../innobase/include/btr0btr.h"
 229  #include "../innobase/include/fsp0fsp.h"
 230  #include "../innobase/include/sync0sync.h"
 231  #include "../innobase/include/fil0fil.h"
 232 +#include "../innobase/include/page0page.h"
 233  #include "../innobase/include/trx0xa.h"
 234  }
 235
 236 @@ -6557,6 +6559,116 @@
 237         DBUG_RETURN(FALSE);
 238  }
 239
 240 +bool
 241 +innodb_I_S_buffer_pool_content(THD* thd, TABLE_LIST *tables)
 242 +{
 243 +       ulint           size;
 244 +       ulint           i;
 245 +       dulint          id;
 246 +       ulint           n_found;
 247 +       buf_frame_t*    frame;
 248 +       dict_index_t*   index;
 249 +       buf_block_t*    block;
 250 +
 251 +       const char *p;
 252 +       char db_name_raw[NAME_LEN*5+1];
 253 +       char table_name_raw[NAME_LEN*5+1];
 254 +
 255 +        DBUG_ENTER("innodb_I_S_buffer_pool_content");
 256 +
 257 +
 258 +       size = buf_pool->curr_size;
 259 +
 260 +       n_found = 0;
 261 +
 262 +       TABLE *table= tables->table;
 263 +
 264 +
 265 +       //buf_pool_dump();
 266 +
 267 +
 268 +       for (i = 0; i < size; i++) {
 269 +               block = buf_pool_get_nth_block_no_inline(buf_pool, i);
 270 +               frame = block->frame;
 271 +               if (fil_page_get_type(frame)==0) continue;
 272 +
 273 +               char page_type[64];
 274 +
 275 +               switch(fil_page_get_type(frame))
 276 +               {
 277 +                       case FIL_PAGE_INDEX:
 278 +                               strcpy(page_type, "index");
 279 +                               break;
 280 +                       case FIL_PAGE_UNDO_LOG:
 281 +                               strcpy(page_type, "undo_log");
 282 +                               break;
 283 +                       case FIL_PAGE_INODE:
 284 +                               strcpy(page_type, "inode");
 285 +                               break;
 286 +                       case FIL_PAGE_IBUF_FREE_LIST:
 287 +                               strcpy(page_type, "ibuf_free_list");
 288 +                               break;
 289 +                       default:
 290 +                               sprintf(page_type, "unknown", fil_page_get_type(frame));
 291 +               }
 292 +
 293 +               table->field[0]->store((longlong)i, TRUE);
 294 +               table->field[1]->store((longlong)block->space, TRUE);
 295 +               table->field[2]->store((longlong)block->offset, TRUE);
 296 +               table->field[3]->store((longlong)page_get_n_recs_noninline(block->frame), TRUE);
 297 +               table->field[4]->store( ( fil_page_get_type(frame) == FIL_PAGE_INDEX ) ?  (longlong)page_get_data_size_noninline(block->frame):0, TRUE);
 298 +               table->field[5]->store((longlong)block->flush_type, TRUE);
 299 +               table->field[6]->store((longlong)block->buf_fix_count, TRUE);
 300 +               table->field[7]->store((longlong)block->LRU_position, TRUE);
 301 +               table->field[8]->store((longlong)fil_page_get_type(frame), TRUE);
 302 +
 303 +               table->field[9]->store(page_type, strlen(page_type), system_charset_info);
 304 +
 305 +               //fprintf(stderr, "block N %d, space %d, offset %d, records %d, datasize %d, page_type %s, flush_type %d, buf_fix_count %d, LRU_position %d", i, block->space, block->offset, page_get_n_recs_noninline(block->frame), page_get_data_size_noninline(block->frame), page_type,block->flush_type, block->buf_fix_count, block->LRU_position);
 306 +
 307 +               // flush_type, buf_fix_count, LRU_position
 308 +
 309 +               if (fil_page_get_type(frame) == FIL_PAGE_INDEX) {
 310 +
 311 +                       id = btr_page_get_index_id_noninline(frame);
 312 +                       index = dict_index_get_if_in_cache(id);
 313 +                       if (index) {
 314 +                               table->field[10]->store(index->name, strlen(index->name), system_charset_info);
 315 +                               // fprintf(stderr, " index %s, table %s", index->name, index->table_name);
 316 +
 317 +                               if((p = strchr((char *) index->table_name, '/')))
 318 +                               {
 319 +                                       strncpy(db_name_raw, index->table_name, p-index->table_name);
 320 +                                       db_name_raw[p-index->table_name] = 0;
 321 +                                       table->field[11]->store(db_name_raw, strlen(db_name_raw), system_charset_info);
 322 +                                       p++;
 323 +                               } else {
 324 +                                       table->field[11]->store(NULL, 0, system_charset_info);
 325 +                                       p = index->table_name;
 326 +                               }
 327 +                               strcpy(table_name_raw, p);
 328 +
 329 +                               table->field[12]->store(table_name_raw, strlen(table_name_raw), system_charset_info);
 330 +                       } else {
 331 +                               table->field[10]->store(NULL, 0, system_charset_info);
 332 +                               table->field[11]->store(NULL, 0, system_charset_info);
 333 +                               table->field[12]->store(NULL, 0, system_charset_info);
 334 +                       }
 335 +               }else{
 336 +                       table->field[10]->store(NULL, 0, system_charset_info);
 337 +                       table->field[11]->store(NULL, 0, system_charset_info);
 338 +                       table->field[12]->store(NULL, 0, system_charset_info);
 339 +               }
 340 +               //fprintf(stderr, "\n");
 341 +               if (schema_table_store_record(thd, table))
 342 +               {
 343 +                       DBUG_RETURN(1);
 344 +               }
 345 +       }
 346 +
 347 +       DBUG_RETURN(0);
 348 +}
 349 +
 350  /****************************************************************************
 351  Implements the SHOW MUTEX STATUS command. . */
 352
 353 diff -ruN a/sql/ha_innodb.h b/sql/ha_innodb.h
 354 --- a/sql/ha_innodb.h   Mon Nov 10 19:47:27 2008 -0800
 355 +++ b/sql/ha_innodb.h   Mon Nov 10 19:48:25 2008 -0800
 356 @@ -274,6 +274,7 @@
 357
 358  int innobase_drop_database(char *path);
 359  bool innodb_show_status(THD* thd);
 360 +bool innodb_I_S_buffer_pool_content(THD* thd, TABLE_LIST *tables);
 361  bool innodb_mutex_show_status(THD* thd);
 362  void innodb_export_status(void);
 363
 364 diff -ruN a/sql/sql_parse.cc b/sql/sql_parse.cc
 365 --- a/sql/sql_parse.cc  Mon Nov 10 19:47:27 2008 -0800
 366 +++ b/sql/sql_parse.cc  Mon Nov 10 19:48:25 2008 -0800
 367 @@ -3037,6 +3037,7 @@
 368    case SCH_COLUMN_PRIVILEGES:
 369    case SCH_TABLE_CONSTRAINTS:
 370    case SCH_KEY_COLUMN_USAGE:
 371 +  case SCH_INNODB_I_S_BUFFER_POOL_CONTENT:
 372    default:
 373      break;
 374    }
 375 diff -ruN a/sql/sql_show.cc b/sql/sql_show.cc
 376 --- a/sql/sql_show.cc   Mon Nov 10 19:47:27 2008 -0800
 377 +++ b/sql/sql_show.cc   Mon Nov 10 19:48:25 2008 -0800
 378 @@ -29,6 +29,10 @@
 379  #include "ha_berkeley.h"                       // For berkeley_show_logs
 380  #endif
 381
 382 +#ifdef HAVE_INNOBASE_DB
 383 +#include "ha_innodb.h"
 384 +#endif
 385 +
 386  #ifndef NO_EMBEDDED_ACCESS_CHECKS
 387  static const char *grant_names[]={
 388    "select","insert","update","delete","create","drop","reload","shutdown",
 389 @@ -4091,6 +4095,19 @@
 390    DBUG_RETURN(res);
 391  }
 392
 393 +int fill_innodb_bp_content(THD *thd, TABLE_LIST *tables, COND *cond)
 394 +{
 395 +  DBUG_ENTER("fill_innodb_bp_content");
 396 +  int res= 0;
 397 +
 398 +  /* deny access to non-superusers */
 399 +  if (check_global_access(thd, PROCESS_ACL)) {
 400 +    DBUG_RETURN(0);
 401 +  }
 402 +
 403 +  innodb_I_S_buffer_pool_content(thd, tables);
 404 +  DBUG_RETURN(res);
 405 +}
 406
 407  /*
 408    Find schema_tables elment by name
 409 @@ -4994,6 +5011,24 @@
 410  };
 411
 412
 413 +ST_FIELD_INFO innodb_bp_content_fields_info[]=
 414 +{
 415 +  {"BLOCK_NUM", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Block_num"},
 416 +  {"SPACE", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Space"},
 417 +  {"OFFSET", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Offset"},
 418 +  {"RECORDS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Records"},
 419 +  {"DATASIZE", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Datasize"},
 420 +  {"FLUSH_TYPE", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Flush_type"},
 421 +  {"FIX_COUNT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Fix_count"},
 422 +  {"LRU_POSITION", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "LRU_position"},
 423 +  {"PAGE_TYPE_ID", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Page_type_id"},
 424 +  {"PAGE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Page_type"},
 425 +  {"INDEX_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Index_name"},
 426 +  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_schem"},
 427 +  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table_name"},
 428 +  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
 429 +};
 430 +
 431  /*
 432    Description of ST_FIELD_INFO in table.h
 433  */
 434 @@ -5012,6 +5047,8 @@
 435     get_all_tables, make_columns_old_format, get_schema_column_record, 1, 2, 0},
 436    {"COLUMN_PRIVILEGES", column_privileges_fields_info, create_schema_table,
 437      fill_schema_column_privileges, 0, 0, -1, -1, 0},
 438 +  {"INNODB_BUFFER_POOL_CONTENT", innodb_bp_content_fields_info, create_schema_table,
 439 +    fill_innodb_bp_content, 0, 0, -1, -1, 0},
 440    {"INDEX_STATISTICS", index_stats_fields_info, create_schema_table,
 441     fill_schema_index_stats, make_old_format, 0, -1, -1, 0},
 442    {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
 443 diff -ruN a/sql/table.h b/sql/table.h
 444 --- a/sql/table.h       Mon Nov 10 19:47:27 2008 -0800
 445 +++ b/sql/table.h       Mon Nov 10 19:48:25 2008 -0800
 446 @@ -376,6 +376,7 @@
 447    SCH_COLLATION_CHARACTER_SET_APPLICABILITY,
 448    SCH_COLUMNS,
 449    SCH_COLUMN_PRIVILEGES,
 450 +  SCH_INNODB_I_S_BUFFER_POOL_CONTENT,
 451    SCH_INDEX_STATS,
 452    SCH_KEY_COLUMN_USAGE,
 453    SCH_OPEN_TABLES,