mysql-innodb_show_status.patch

   1 # name       : innodb_show_status.patch
   2 # introduced : 11 or before
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- a/storage/innodb_plugin/buf/buf0buf.c
   9 +++ b/storage/innodb_plugin/buf/buf0buf.c
  10 @@ -3515,14 +3515,16 @@
  11         buf_pool_mutex_enter();
  12
  13         fprintf(file,
  14 -               "Buffer pool size   %lu\n"
  15 -               "Free buffers       %lu\n"
  16 -               "Database pages     %lu\n"
  17 -               "Old database pages %lu\n"
  18 -               "Modified db pages  %lu\n"
  19 +               "Buffer pool size        %lu\n"
  20 +               "Buffer pool size, bytes %lu\n"
  21 +               "Free buffers            %lu\n"
  22 +               "Database pages          %lu\n"
  23 +               "Old database pages      %lu\n"
  24 +               "Modified db pages       %lu\n"
  25                 "Pending reads %lu\n"
  26                 "Pending writes: LRU %lu, flush list %lu, single page %lu\n",
  27                 (ulong) buf_pool->curr_size,
  28 +               (ulong) buf_pool->curr_size * UNIV_PAGE_SIZE,
  29                 (ulong) UT_LIST_GET_LEN(buf_pool->free),
  30                 (ulong) UT_LIST_GET_LEN(buf_pool->LRU),
  31                 (ulong) buf_pool->LRU_old_len,
  32 --- a/storage/innodb_plugin/fil/fil0fil.c
  33 +++ b/storage/innodb_plugin/fil/fil0fil.c
  34 @@ -4833,3 +4833,30 @@
  35
  36         fil_system = NULL;
  37  }
  38 +
  39 +/*************************************************************************
  40 +Return local hash table informations. */
  41 +
  42 +ulint
  43 +fil_system_hash_cells(void)
  44 +/*=======================*/
  45 +{
  46 +       if (fil_system) {
  47 +               return (fil_system->spaces->n_cells
  48 +                       + fil_system->name_hash->n_cells);
  49 +       } else {
  50 +               return 0;
  51 +       }
  52 +}
  53 +
  54 +ulint
  55 +fil_system_hash_nodes(void)
  56 +/*=======================*/
  57 +{
  58 +       if (fil_system) {
  59 +               return (UT_LIST_GET_LEN(fil_system->space_list)
  60 +                       * (sizeof(fil_space_t) + MEM_BLOCK_HEADER_SIZE));
  61 +       } else {
  62 +               return 0;
  63 +       }
  64 +}
  65 --- a/storage/innodb_plugin/handler/ha_innodb.cc
  66 +++ b/storage/innodb_plugin/handler/ha_innodb.cc
  67 @@ -10807,6 +10807,16 @@
  68    "Force InnoDB to not use next-key locking, to use only row-level locking.",
  69    NULL, NULL, FALSE);
  70
  71 +static MYSQL_SYSVAR_ULONG(show_verbose_locks, srv_show_verbose_locks,
  72 +  PLUGIN_VAR_OPCMDARG,
  73 +  "Whether to show records locked in SHOW INNODB STATUS.",
  74 +  NULL, NULL, 0, 0, 1, 0);
  75 +
  76 +static MYSQL_SYSVAR_ULONG(show_locks_held, srv_show_locks_held,
  77 +  PLUGIN_VAR_RQCMDARG,
  78 +  "Number of locks held to print for each InnoDB transaction in SHOW INNODB STATUS.",
  79 +  NULL, NULL, 10, 0, 1000, 0);
  80 +
  81  #ifdef UNIV_LOG_ARCHIVE
  82  static MYSQL_SYSVAR_STR(log_arch_dir, innobase_log_arch_dir,
  83    PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
  84 @@ -10989,7 +10999,7 @@
  85
  86  static MYSQL_SYSVAR_STR(version, innodb_version_str,
  87    PLUGIN_VAR_NOCMDOPT | PLUGIN_VAR_READONLY,
  88 -  "InnoDB version", NULL, NULL, INNODB_VERSION_STR);
  89 +  "Percona-InnoDB-plugin version", NULL, NULL, INNODB_VERSION_STR);
  90
  91  static MYSQL_SYSVAR_BOOL(use_sys_malloc, srv_use_sys_malloc,
  92    PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY,
  93 @@ -11080,6 +11090,8 @@
  94    MYSQL_SYSVAR(thread_concurrency),
  95    MYSQL_SYSVAR(thread_sleep_delay),
  96    MYSQL_SYSVAR(autoinc_lock_mode),
  97 +  MYSQL_SYSVAR(show_verbose_locks),
  98 +  MYSQL_SYSVAR(show_locks_held),
  99    MYSQL_SYSVAR(version),
 100    MYSQL_SYSVAR(use_sys_malloc),
 101    MYSQL_SYSVAR(change_buffering),
 102 --- a/storage/innodb_plugin/handler/innodb_patch_info.h
 103 +++ b/storage/innodb_plugin/handler/innodb_patch_info.h
 104 @@ -24,5 +24,6 @@
 105         const char *link;
 106  }innodb_enhancements[] = {
 107  {"xtradb_show_enhancements","I_S.XTRADB_ENHANCEMENTS","","http://www.percona.com/docs/wiki/percona-xtradb"},
 108 +{"innodb_show_status","Improvements to SHOW INNODB STATUS","Memory information and lock info fixes","http://www.percona.com/docs/wiki/percona-xtradb"},
 109  {NULL, NULL, NULL, NULL}
 110  };
 111 --- a/storage/innodb_plugin/include/fil0fil.h
 112 +++ b/storage/innodb_plugin/include/fil0fil.h
 113 @@ -725,6 +725,17 @@
 114  /*============================*/
 115         ulint           id);    /*!< in: space id */
 116
 117 +/*************************************************************************
 118 +Return local hash table informations. */
 119 +
 120 +ulint
 121 +fil_system_hash_cells(void);
 122 +/*========================*/
 123 +
 124 +ulint
 125 +fil_system_hash_nodes(void);
 126 +/*========================*/
 127 +
 128  typedef        struct fil_space_struct fil_space_t;
 129
 130  #endif
 131 --- a/storage/innodb_plugin/include/srv0srv.h
 132 +++ b/storage/innodb_plugin/include/srv0srv.h
 133 @@ -128,6 +128,9 @@
 134  extern char    srv_adaptive_flushing;
 135
 136
 137 +extern ulint    srv_show_locks_held;
 138 +extern ulint    srv_show_verbose_locks;
 139 +
 140  /* The sort order table of the MySQL latin1_swedish_ci character set
 141  collation */
 142  extern const byte*     srv_latin1_ordering;
 143 --- a/storage/innodb_plugin/include/thr0loc.h
 144 +++ b/storage/innodb_plugin/include/thr0loc.h
 145 @@ -83,6 +83,17 @@
 146  thr_local_get_in_ibuf_field(void);
 147  /*=============================*/
 148
 149 +/*************************************************************************
 150 +Return local hash table informations. */
 151 +
 152 +ulint
 153 +thr_local_hash_cells(void);
 154 +/*=======================*/
 155 +
 156 +ulint
 157 +thr_local_hash_nodes(void);
 158 +/*=======================*/
 159 +
 160  #ifndef UNIV_NONINL
 161  #include "thr0loc.ic"
 162  #endif
 163 --- a/storage/innodb_plugin/lock/lock0lock.c
 164 +++ b/storage/innodb_plugin/lock/lock0lock.c
 165 @@ -4379,6 +4379,7 @@
 166
 167         putc('\n', file);
 168
 169 +       if ( srv_show_verbose_locks ) {
 170         block = buf_page_try_get(space, page_no, &mtr);
 171
 172         for (i = 0; i < lock_rec_get_n_bits(lock); ++i) {
 173 @@ -4405,6 +4406,7 @@
 174
 175                 putc('\n', file);
 176         }
 177 +       }
 178
 179         mtr_commit(&mtr);
 180         if (UNIV_LIKELY_NULL(heap)) {
 181 @@ -4590,7 +4592,7 @@
 182                 }
 183         }
 184
 185 -       if (!srv_print_innodb_lock_monitor) {
 186 +        if (!srv_print_innodb_lock_monitor && !srv_show_locks_held) {
 187                 nth_trx++;
 188                 goto loop;
 189         }
 190 @@ -4662,8 +4664,8 @@
 191
 192         nth_lock++;
 193
 194 -       if (nth_lock >= 10) {
 195 -               fputs("10 LOCKS PRINTED FOR THIS TRX:"
 196 +       if (nth_lock >= srv_show_locks_held) {
 197 +               fputs("TOO MANY LOCKS PRINTED FOR THIS TRX:"
 198                       " SUPPRESSING FURTHER PRINTS\n",
 199                       file);
 200
 201 --- a/storage/innodb_plugin/srv/srv0srv.c
 202 +++ b/storage/innodb_plugin/srv/srv0srv.c
 203 @@ -171,6 +171,9 @@
 204  the checkpoints. */
 205  UNIV_INTERN char       srv_adaptive_flushing   = TRUE;
 206
 207 +UNIV_INTERN ulint      srv_show_locks_held     = 10;
 208 +UNIV_INTERN ulint      srv_show_verbose_locks  = 0;
 209 +
 210  /** Maximum number of times allowed to conditionally acquire
 211  mutex before switching to blocking wait on the mutex */
 212  #define MAX_MUTEX_NOWAIT       20
 213 @@ -1707,6 +1710,13 @@
 214         ulint   n_reserved;
 215         ibool   ret;
 216
 217 +       ulint   btr_search_sys_subtotal;
 218 +       ulint   lock_sys_subtotal;
 219 +       ulint   recv_sys_subtotal;
 220 +
 221 +       ulint   i;
 222 +       trx_t*  trx;
 223 +
 224         mutex_enter(&srv_innodb_monitor_mutex);
 225
 226         current_time = time(NULL);
 227 @@ -1755,31 +1765,6 @@
 228
 229         mutex_exit(&dict_foreign_err_mutex);
 230
 231 -       /* Only if lock_print_info_summary proceeds correctly,
 232 -       before we call the lock_print_info_all_transactions
 233 -       to print all the lock information. */
 234 -       ret = lock_print_info_summary(file, nowait);
 235 -
 236 -       if (ret) {
 237 -               if (trx_start) {
 238 -                       long    t = ftell(file);
 239 -                       if (t < 0) {
 240 -                               *trx_start = ULINT_UNDEFINED;
 241 -                       } else {
 242 -                               *trx_start = (ulint) t;
 243 -                       }
 244 -               }
 245 -               lock_print_info_all_transactions(file);
 246 -               if (trx_end) {
 247 -                       long    t = ftell(file);
 248 -                       if (t < 0) {
 249 -                               *trx_end = ULINT_UNDEFINED;
 250 -                       } else {
 251 -                               *trx_end = (ulint) t;
 252 -                       }
 253 -               }
 254 -       }
 255 -
 256         fputs("--------\n"
 257               "FILE I/O\n"
 258               "--------\n", file);
 259 @@ -1810,10 +1795,84 @@
 260               "BUFFER POOL AND MEMORY\n"
 261               "----------------------\n", file);
 262         fprintf(file,
 263 -               "Total memory allocated " ULINTPF
 264 -               "; in additional pool allocated " ULINTPF "\n",
 265 -               ut_total_allocated_memory,
 266 -               mem_pool_get_reserved(mem_comm_pool));
 267 +                       "Total memory allocated " ULINTPF
 268 +                       "; in additional pool allocated " ULINTPF "\n",
 269 +                       ut_total_allocated_memory,
 270 +                       mem_pool_get_reserved(mem_comm_pool));
 271 +       /* Calcurate reserved memories */
 272 +       if (btr_search_sys && btr_search_sys->hash_index->heap) {
 273 +               btr_search_sys_subtotal = mem_heap_get_size(btr_search_sys->hash_index->heap);
 274 +       } else {
 275 +               btr_search_sys_subtotal = 0;
 276 +               for (i=0; i < btr_search_sys->hash_index->n_mutexes; i++) {
 277 +                       btr_search_sys_subtotal += mem_heap_get_size(btr_search_sys->hash_index->heaps[i]);
 278 +               }
 279 +       }
 280 +
 281 +       lock_sys_subtotal = 0;
 282 +       if (trx_sys) {
 283 +               mutex_enter(&kernel_mutex);
 284 +               trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
 285 +               while (trx) {
 286 +                       lock_sys_subtotal += ((trx->lock_heap) ? mem_heap_get_size(trx->lock_heap) : 0);
 287 +                       trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
 288 +               }
 289 +               mutex_exit(&kernel_mutex);
 290 +       }
 291 +
 292 +       recv_sys_subtotal = ((recv_sys && recv_sys->addr_hash)
 293 +                       ? mem_heap_get_size(recv_sys->heap) : 0);
 294 +
 295 +       fprintf(file,
 296 +                       "Internal hash tables (constant factor + variable factor)\n"
 297 +                       "    Adaptive hash index %lu \t(%lu + %lu)\n"
 298 +                       "    Page hash           %lu\n"
 299 +                       "    Dictionary cache    %lu \t(%lu + %lu)\n"
 300 +                       "    File system         %lu \t(%lu + %lu)\n"
 301 +                       "    Lock system         %lu \t(%lu + %lu)\n"
 302 +                       "    Recovery system     %lu \t(%lu + %lu)\n"
 303 +                       "    Threads             %lu \t(%lu + %lu)\n",
 304 +
 305 +                       (ulong) (btr_search_sys
 306 +                               ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0)
 307 +                       + btr_search_sys_subtotal,
 308 +                       (ulong) (btr_search_sys
 309 +                               ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0),
 310 +                       (ulong) btr_search_sys_subtotal,
 311 +
 312 +                       (ulong) (buf_pool->page_hash->n_cells * sizeof(hash_cell_t)),
 313 +
 314 +                       (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells
 315 +                                               + dict_sys->table_id_hash->n_cells
 316 +                                               ) * sizeof(hash_cell_t)
 317 +                                       + dict_sys->size) : 0),
 318 +                       (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells
 319 +                                                       + dict_sys->table_id_hash->n_cells
 320 +                                                       ) * sizeof(hash_cell_t)) : 0),
 321 +                       (ulong) (dict_sys ? (dict_sys->size) : 0),
 322 +
 323 +                       (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t)
 324 +                                       + fil_system_hash_nodes()),
 325 +                       (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t)),
 326 +                       (ulong) fil_system_hash_nodes(),
 327 +
 328 +                       (ulong) ((lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0)
 329 +                                       + lock_sys_subtotal),
 330 +                       (ulong) (lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0),
 331 +                       (ulong) lock_sys_subtotal,
 332 +
 333 +                       (ulong) (((recv_sys && recv_sys->addr_hash)
 334 +                                               ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0)
 335 +                                       + recv_sys_subtotal),
 336 +                       (ulong) ((recv_sys && recv_sys->addr_hash)
 337 +                                       ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0),
 338 +                       (ulong) recv_sys_subtotal,
 339 +
 340 +                       (ulong) (thr_local_hash_cells() * sizeof(hash_cell_t)
 341 +                                       + thr_local_hash_nodes()),
 342 +                       (ulong) (thr_local_hash_cells() * sizeof(hash_cell_t)),
 343 +                       (ulong) thr_local_hash_nodes());
 344 +
 345         fprintf(file, "Dictionary memory allocated " ULINTPF "\n",
 346                 dict_sys->size);
 347
 348 @@ -1872,6 +1931,31 @@
 349         srv_n_rows_deleted_old = srv_n_rows_deleted;
 350         srv_n_rows_read_old = srv_n_rows_read;
 351
 352 +       /* Only if lock_print_info_summary proceeds correctly,
 353 +       before we call the lock_print_info_all_transactions
 354 +       to print all the lock information. */
 355 +       ret = lock_print_info_summary(file, nowait);
 356 +
 357 +       if (ret) {
 358 +               if (trx_start) {
 359 +                       long    t = ftell(file);
 360 +                       if (t < 0) {
 361 +                               *trx_start = ULINT_UNDEFINED;
 362 +                       } else {
 363 +                               *trx_start = (ulint) t;
 364 +                       }
 365 +               }
 366 +               lock_print_info_all_transactions(file);
 367 +               if (trx_end) {
 368 +                       long    t = ftell(file);
 369 +                       if (t < 0) {
 370 +                               *trx_end = ULINT_UNDEFINED;
 371 +                       } else {
 372 +                               *trx_end = (ulint) t;
 373 +                       }
 374 +               }
 375 +       }
 376 +
 377         fputs("----------------------------\n"
 378               "END OF INNODB MONITOR OUTPUT\n"
 379               "============================\n", file);
 380 --- a/storage/innodb_plugin/sync/sync0arr.c
 381 +++ b/storage/innodb_plugin/sync/sync0arr.c
 382 @@ -471,7 +471,7 @@
 383
 384         fprintf(file,
 385                 "--Thread %lu has waited at %s line %lu"
 386 -               " for %.2f seconds the semaphore:\n",
 387 +               " for %#.5g seconds the semaphore:\n",
 388                 (ulong) os_thread_pf(cell->thread), cell->file,
 389                 (ulong) cell->line,
 390                 difftime(time(NULL), cell->reservation_time));
 391 --- a/storage/innodb_plugin/thr/thr0loc.c
 392 +++ b/storage/innodb_plugin/thr/thr0loc.c
 393 @@ -49,6 +49,7 @@
 394
 395  /** The hash table. The module is not yet initialized when it is NULL. */
 396  static hash_table_t*   thr_local_hash  = NULL;
 397 +ulint          thr_local_hash_n_nodes = 0;
 398
 399  /** Thread local data */
 400  typedef struct thr_local_struct thr_local_t;
 401 @@ -216,6 +217,7 @@
 402                     os_thread_pf(os_thread_get_curr_id()),
 403                     local);
 404
 405 +       thr_local_hash_n_nodes++;
 406         mutex_exit(&thr_local_mutex);
 407  }
 408
 409 @@ -244,6 +246,7 @@
 410
 411         HASH_DELETE(thr_local_t, hash, thr_local_hash,
 412                     os_thread_pf(id), local);
 413 +       thr_local_hash_n_nodes--;
 414
 415         mutex_exit(&thr_local_mutex);
 416
 417 @@ -299,3 +302,29 @@
 418         hash_table_free(thr_local_hash);
 419         thr_local_hash = NULL;
 420  }
 421 +
 422 +/*************************************************************************
 423 +Return local hash table informations. */
 424 +
 425 +ulint
 426 +thr_local_hash_cells(void)
 427 +/*======================*/
 428 +{
 429 +       if (thr_local_hash) {
 430 +               return (thr_local_hash->n_cells);
 431 +       } else {
 432 +               return 0;
 433 +       }
 434 +}
 435 +
 436 +ulint
 437 +thr_local_hash_nodes(void)
 438 +/*======================*/
 439 +{
 440 +       if (thr_local_hash) {
 441 +               return (thr_local_hash_n_nodes
 442 +                       * (sizeof(thr_local_t) + MEM_BLOCK_HEADER_SIZE));
 443 +       } else {
 444 +               return 0;
 445 +       }
 446 +}