# name : innodb_show_status.patch # introduced : 11 or before # maintainer : Yasufumi # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! diff -ruN a/storage/innobase/buf/buf0buf.c b/storage/innobase/buf/buf0buf.c --- a/storage/innobase/buf/buf0buf.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/buf/buf0buf.c 2010-12-03 15:07:31.786968193 +0900 @@ -4814,6 +4814,7 @@ } total_info->pool_size += pool_info->pool_size; + total_info->pool_size_bytes += pool_info->pool_size_bytes; total_info->lru_len += pool_info->lru_len; total_info->old_lru_len += pool_info->old_lru_len; total_info->free_list_len += pool_info->free_list_len; @@ -4877,6 +4878,8 @@ pool_info->pool_size = buf_pool->curr_size; + pool_info->pool_size_bytes = buf_pool->curr_pool_size; + pool_info->lru_len = UT_LIST_GET_LEN(buf_pool->LRU); pool_info->old_lru_len = buf_pool->LRU_old_len; @@ -4993,14 +4996,16 @@ ut_ad(pool_info); fprintf(file, - "Buffer pool size %lu\n" - "Free buffers %lu\n" - "Database pages %lu\n" - "Old database pages %lu\n" - "Modified db pages %lu\n" + "Buffer pool size %lu\n" + "Buffer pool size, bytes %lu\n" + "Free buffers %lu\n" + "Database pages %lu\n" + "Old database pages %lu\n" + "Modified db pages %lu\n" "Pending reads %lu\n" "Pending writes: LRU %lu, flush list %lu, single page %lu\n", pool_info->pool_size, + pool_info->pool_size_bytes, pool_info->free_list_len, pool_info->lru_len, pool_info->old_lru_len, diff -ruN a/storage/innobase/buf/buf0flu.c b/storage/innobase/buf/buf0flu.c --- a/storage/innobase/buf/buf0flu.c 2010-12-03 20:58:26.000000000 +0300 +++ b/storage/innobase/buf/buf0flu.c 2011-01-07 03:37:41.000000000 +0300 @@ -75,7 +75,7 @@ static buf_flush_stat_t buf_flush_stat_sum; /** Number of pages flushed through non flush_list flushes. */ -static ulint buf_lru_flush_page_count = 0; +// static ulint buf_lru_flush_page_count = 0; /* @} */ diff -ruN a/storage/innobase/fil/fil0fil.c b/storage/innobase/fil/fil0fil.c --- a/storage/innobase/fil/fil0fil.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/fil/fil0fil.c 2010-12-03 15:07:31.790357112 +0900 @@ -4886,3 +4886,30 @@ fil_system = NULL; } + +/************************************************************************* +Return local hash table informations. */ + +ulint +fil_system_hash_cells(void) +/*=======================*/ +{ + if (fil_system) { + return (fil_system->spaces->n_cells + + fil_system->name_hash->n_cells); + } else { + return 0; + } +} + +ulint +fil_system_hash_nodes(void) +/*=======================*/ +{ + if (fil_system) { + return (UT_LIST_GET_LEN(fil_system->space_list) + * (sizeof(fil_space_t) + MEM_BLOCK_HEADER_SIZE)); + } else { + return 0; + } +} diff -ruN a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc --- a/storage/innobase/handler/ha_innodb.cc 2010-12-03 15:06:58.727955654 +0900 +++ b/storage/innobase/handler/ha_innodb.cc 2010-12-03 15:07:31.799376984 +0900 @@ -604,6 +604,8 @@ (char*) &export_vars.innodb_buffer_pool_pages_dirty, SHOW_LONG}, {"buffer_pool_pages_flushed", (char*) &export_vars.innodb_buffer_pool_pages_flushed, SHOW_LONG}, + {"buffer_pool_pages_LRU_flushed", + (char*) &export_vars.innodb_buffer_pool_pages_LRU_flushed, SHOW_LONG}, {"buffer_pool_pages_free", (char*) &export_vars.innodb_buffer_pool_pages_free, SHOW_LONG}, #ifdef UNIV_DEBUG @@ -11057,6 +11059,16 @@ "Force InnoDB to not use next-key locking, to use only row-level locking.", NULL, NULL, FALSE); +static MYSQL_SYSVAR_ULONG(show_verbose_locks, srv_show_verbose_locks, + PLUGIN_VAR_OPCMDARG, + "Whether to show records locked in SHOW INNODB STATUS.", + NULL, NULL, 0, 0, 1, 0); + +static MYSQL_SYSVAR_ULONG(show_locks_held, srv_show_locks_held, + PLUGIN_VAR_RQCMDARG, + "Number of locks held to print for each InnoDB transaction in SHOW INNODB STATUS.", + NULL, NULL, 10, 0, 1000, 0); + #ifdef UNIV_LOG_ARCHIVE static MYSQL_SYSVAR_STR(log_arch_dir, innobase_log_arch_dir, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY, @@ -11244,7 +11256,7 @@ static MYSQL_SYSVAR_STR(version, innodb_version_str, PLUGIN_VAR_NOCMDOPT | PLUGIN_VAR_READONLY, - "InnoDB version", NULL, NULL, INNODB_VERSION_STR); + "Percona-InnoDB-plugin version", NULL, NULL, INNODB_VERSION_STR); static MYSQL_SYSVAR_BOOL(use_sys_malloc, srv_use_sys_malloc, PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY, @@ -11337,6 +11349,8 @@ MYSQL_SYSVAR(thread_concurrency), MYSQL_SYSVAR(thread_sleep_delay), MYSQL_SYSVAR(autoinc_lock_mode), + MYSQL_SYSVAR(show_verbose_locks), + MYSQL_SYSVAR(show_locks_held), MYSQL_SYSVAR(version), MYSQL_SYSVAR(use_sys_malloc), MYSQL_SYSVAR(use_native_aio), diff -ruN a/storage/innobase/include/buf0buf.h b/storage/innobase/include/buf0buf.h --- a/storage/innobase/include/buf0buf.h 2011-01-20 07:37:08.000000000 +0900 +++ b/storage/innobase/include/buf0buf.h 2011-02-14 15:25:20.859126532 +0900 @@ -123,6 +123,7 @@ /* General buffer pool info */ ulint pool_unique_id; /*!< Buffer Pool ID */ ulint pool_size; /*!< Buffer Pool size in pages */ + ulint pool_size_bytes; ulint lru_len; /*!< Length of buf_pool->LRU */ ulint old_lru_len; /*!< buf_pool->LRU_old_len */ ulint free_list_len; /*!< Length of buf_pool->free list */ diff -ruN a/storage/innobase/include/fil0fil.h b/storage/innobase/include/fil0fil.h --- a/storage/innobase/include/fil0fil.h 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/include/fil0fil.h 2010-12-03 15:07:31.812028575 +0900 @@ -726,6 +726,17 @@ /*============================*/ ulint id); /*!< in: space id */ +/************************************************************************* +Return local hash table informations. */ + +ulint +fil_system_hash_cells(void); +/*========================*/ + +ulint +fil_system_hash_nodes(void); +/*========================*/ + typedef struct fil_space_struct fil_space_t; #endif diff -ruN a/storage/innobase/include/read0read.h b/storage/innobase/include/read0read.h --- a/storage/innobase/include/read0read.h 2010-12-04 02:58:26.000000000 +0900 +++ b/storage/innobase/include/read0read.h 2011-01-21 19:35:44.127631727 +0900 @@ -88,6 +88,7 @@ void read_view_print( /*============*/ + FILE* file, const read_view_t* view); /*!< in: read view */ /*********************************************************************//** Create a consistent cursor view for mysql to be used in cursors. In this diff -ruN a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h --- a/storage/innobase/include/srv0srv.h 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/include/srv0srv.h 2010-12-03 15:07:31.813958103 +0900 @@ -145,6 +145,9 @@ extern char srv_adaptive_flushing; +extern ulint srv_show_locks_held; +extern ulint srv_show_verbose_locks; + /* The sort order table of the MySQL latin1_swedish_ci character set collation */ extern const byte* srv_latin1_ordering; @@ -326,6 +329,8 @@ buffer pool to disk */ extern ulint srv_buf_pool_flushed; +extern ulint buf_lru_flush_page_count; + /** Number of buffer pool reads that led to the reading of a disk page */ extern ulint srv_buf_pool_reads; @@ -720,6 +725,7 @@ ulint innodb_buffer_pool_reads; /*!< srv_buf_pool_reads */ ulint innodb_buffer_pool_wait_free; /*!< srv_buf_pool_wait_free */ ulint innodb_buffer_pool_pages_flushed; /*!< srv_buf_pool_flushed */ + ulint innodb_buffer_pool_pages_LRU_flushed; /*!< buf_lru_flush_page_count */ ulint innodb_buffer_pool_write_requests;/*!< srv_buf_pool_write_requests */ ulint innodb_buffer_pool_read_ahead; /*!< srv_read_ahead */ ulint innodb_buffer_pool_read_ahead_evicted;/*!< srv_read_ahead evicted*/ diff -ruN a/storage/innobase/include/thr0loc.h b/storage/innobase/include/thr0loc.h --- a/storage/innobase/include/thr0loc.h 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/include/thr0loc.h 2010-12-03 15:07:31.815081509 +0900 @@ -83,6 +83,17 @@ thr_local_get_in_ibuf_field(void); /*=============================*/ +/************************************************************************* +Return local hash table informations. */ + +ulint +thr_local_hash_cells(void); +/*=======================*/ + +ulint +thr_local_hash_nodes(void); +/*=======================*/ + #ifndef UNIV_NONINL #include "thr0loc.ic" #endif diff -ruN a/storage/innobase/lock/lock0lock.c b/storage/innobase/lock/lock0lock.c --- a/storage/innobase/lock/lock0lock.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/lock/lock0lock.c 2010-12-03 15:07:31.819023998 +0900 @@ -4374,6 +4374,7 @@ putc('\n', file); + if ( srv_show_verbose_locks ) { block = buf_page_try_get(space, page_no, &mtr); for (i = 0; i < lock_rec_get_n_bits(lock); ++i) { @@ -4400,6 +4401,7 @@ putc('\n', file); } + } mtr_commit(&mtr); if (UNIV_LIKELY_NULL(heap)) { @@ -4583,7 +4585,7 @@ } } - if (!srv_print_innodb_lock_monitor) { + if (!srv_print_innodb_lock_monitor && !srv_show_locks_held) { nth_trx++; goto loop; } @@ -4655,8 +4657,8 @@ nth_lock++; - if (nth_lock >= 10) { - fputs("10 LOCKS PRINTED FOR THIS TRX:" + if (nth_lock >= srv_show_locks_held) { + fputs("TOO MANY LOCKS PRINTED FOR THIS TRX:" " SUPPRESSING FURTHER PRINTS\n", file); diff -ruN a/storage/innobase/read/read0read.c b/storage/innobase/read/read0read.c --- a/storage/innobase/read/read0read.c 2010-12-04 02:58:26.000000000 +0900 +++ b/storage/innobase/read/read0read.c 2011-01-21 19:37:08.292650181 +0900 @@ -357,34 +357,35 @@ void read_view_print( /*============*/ + FILE* file, const read_view_t* view) /*!< in: read view */ { ulint n_ids; ulint i; if (view->type == VIEW_HIGH_GRANULARITY) { - fprintf(stderr, + fprintf(file, "High-granularity read view undo_n:o %llu\n", (ullint) view->undo_no); } else { - fprintf(stderr, "Normal read view\n"); + fprintf(file, "Normal read view\n"); } - fprintf(stderr, "Read view low limit trx n:o " TRX_ID_FMT "\n", + fprintf(file, "Read view low limit trx n:o " TRX_ID_FMT "\n", (ullint) view->low_limit_no); - fprintf(stderr, "Read view up limit trx id " TRX_ID_FMT "\n", + fprintf(file, "Read view up limit trx id " TRX_ID_FMT "\n", (ullint) view->up_limit_id); - fprintf(stderr, "Read view low limit trx id " TRX_ID_FMT "\n", + fprintf(file, "Read view low limit trx id " TRX_ID_FMT "\n", (ullint) view->low_limit_id); - fprintf(stderr, "Read view individually stored trx ids:\n"); + fprintf(file, "Read view individually stored trx ids:\n"); n_ids = view->n_trx_ids; for (i = 0; i < n_ids; i++) { - fprintf(stderr, "Read view trx id " TRX_ID_FMT "\n", + fprintf(file, "Read view trx id " TRX_ID_FMT "\n", (ullint) read_view_get_nth_trx_id(view, i)); } } diff -ruN a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c --- a/storage/innobase/srv/srv0srv.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/srv/srv0srv.c 2010-12-03 15:07:31.824022673 +0900 @@ -84,6 +84,7 @@ #include "ha_prototypes.h" #include "trx0i_s.h" #include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */ +#include "read0read.h" #include "mysql/plugin.h" #include "mysql/service_thd_wait.h" @@ -195,6 +196,9 @@ the checkpoints. */ UNIV_INTERN char srv_adaptive_flushing = TRUE; +UNIV_INTERN ulint srv_show_locks_held = 10; +UNIV_INTERN ulint srv_show_verbose_locks = 0; + /** Maximum number of times allowed to conditionally acquire mutex before switching to blocking wait on the mutex */ #define MAX_MUTEX_NOWAIT 20 @@ -321,6 +325,7 @@ /* variable to count the number of pages that were written from buffer pool to the disk */ UNIV_INTERN ulint srv_buf_pool_flushed = 0; +UNIV_INTERN ulint buf_lru_flush_page_count = 0; /** Number of buffer pool reads that led to the reading of a disk page */ @@ -1829,6 +1834,13 @@ ulint n_reserved; ibool ret; + ulint btr_search_sys_subtotal; + ulint lock_sys_subtotal; + ulint recv_sys_subtotal; + + ulint i; + trx_t* trx; + mutex_enter(&srv_innodb_monitor_mutex); current_time = time(NULL); @@ -1877,31 +1889,6 @@ mutex_exit(&dict_foreign_err_mutex); - /* Only if lock_print_info_summary proceeds correctly, - before we call the lock_print_info_all_transactions - to print all the lock information. */ - ret = lock_print_info_summary(file, nowait); - - if (ret) { - if (trx_start) { - long t = ftell(file); - if (t < 0) { - *trx_start = ULINT_UNDEFINED; - } else { - *trx_start = (ulint) t; - } - } - lock_print_info_all_transactions(file); - if (trx_end) { - long t = ftell(file); - if (t < 0) { - *trx_end = ULINT_UNDEFINED; - } else { - *trx_end = (ulint) t; - } - } - } - fputs("--------\n" "FILE I/O\n" "--------\n", file); @@ -1932,10 +1919,84 @@ "BUFFER POOL AND MEMORY\n" "----------------------\n", file); fprintf(file, - "Total memory allocated " ULINTPF - "; in additional pool allocated " ULINTPF "\n", - ut_total_allocated_memory, - mem_pool_get_reserved(mem_comm_pool)); + "Total memory allocated " ULINTPF + "; in additional pool allocated " ULINTPF "\n", + ut_total_allocated_memory, + mem_pool_get_reserved(mem_comm_pool)); + /* Calcurate reserved memories */ + if (btr_search_sys && btr_search_sys->hash_index->heap) { + btr_search_sys_subtotal = mem_heap_get_size(btr_search_sys->hash_index->heap); + } else { + btr_search_sys_subtotal = 0; + for (i=0; i < btr_search_sys->hash_index->n_mutexes; i++) { + btr_search_sys_subtotal += mem_heap_get_size(btr_search_sys->hash_index->heaps[i]); + } + } + + lock_sys_subtotal = 0; + if (trx_sys) { + mutex_enter(&kernel_mutex); + trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); + while (trx) { + lock_sys_subtotal += ((trx->lock_heap) ? mem_heap_get_size(trx->lock_heap) : 0); + trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); + } + mutex_exit(&kernel_mutex); + } + + recv_sys_subtotal = ((recv_sys && recv_sys->addr_hash) + ? mem_heap_get_size(recv_sys->heap) : 0); + + fprintf(file, + "Internal hash tables (constant factor + variable factor)\n" + " Adaptive hash index %lu \t(%lu + %lu)\n" + " Page hash %lu (buffer pool 0 only)\n" + " Dictionary cache %lu \t(%lu + %lu)\n" + " File system %lu \t(%lu + %lu)\n" + " Lock system %lu \t(%lu + %lu)\n" + " Recovery system %lu \t(%lu + %lu)\n" + " Threads %lu \t(%lu + %lu)\n", + + (ulong) (btr_search_sys + ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0) + + btr_search_sys_subtotal, + (ulong) (btr_search_sys + ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0), + (ulong) btr_search_sys_subtotal, + + (ulong) (buf_pool_from_array(0)->page_hash->n_cells * sizeof(hash_cell_t)), + + (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells + + dict_sys->table_id_hash->n_cells + ) * sizeof(hash_cell_t) + + dict_sys->size) : 0), + (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells + + dict_sys->table_id_hash->n_cells + ) * sizeof(hash_cell_t)) : 0), + (ulong) (dict_sys ? (dict_sys->size) : 0), + + (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t) + + fil_system_hash_nodes()), + (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t)), + (ulong) fil_system_hash_nodes(), + + (ulong) ((lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0) + + lock_sys_subtotal), + (ulong) (lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0), + (ulong) lock_sys_subtotal, + + (ulong) (((recv_sys && recv_sys->addr_hash) + ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0) + + recv_sys_subtotal), + (ulong) ((recv_sys && recv_sys->addr_hash) + ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0), + (ulong) recv_sys_subtotal, + + (ulong) (thr_local_hash_cells() * sizeof(hash_cell_t) + + thr_local_hash_nodes()), + (ulong) (thr_local_hash_cells() * sizeof(hash_cell_t)), + (ulong) thr_local_hash_nodes()); + fprintf(file, "Dictionary memory allocated " ULINTPF "\n", dict_sys->size); @@ -1951,6 +2012,16 @@ fprintf(file, "%lu read views open inside InnoDB\n", UT_LIST_GET_LEN(trx_sys->view_list)); + if (UT_LIST_GET_LEN(trx_sys->view_list)) { + read_view_t* view = UT_LIST_GET_LAST(trx_sys->view_list); + + if (view) { + fprintf(file, "---OLDEST VIEW---\n"); + read_view_print(file, view); + fprintf(file, "-----------------\n"); + } + } + n_reserved = fil_space_get_n_reserved_extents(0); if (n_reserved > 0) { fprintf(file, @@ -1994,6 +2065,31 @@ srv_n_rows_deleted_old = srv_n_rows_deleted; srv_n_rows_read_old = srv_n_rows_read; + /* Only if lock_print_info_summary proceeds correctly, + before we call the lock_print_info_all_transactions + to print all the lock information. */ + ret = lock_print_info_summary(file, nowait); + + if (ret) { + if (trx_start) { + long t = ftell(file); + if (t < 0) { + *trx_start = ULINT_UNDEFINED; + } else { + *trx_start = (ulint) t; + } + } + lock_print_info_all_transactions(file); + if (trx_end) { + long t = ftell(file); + if (t < 0) { + *trx_end = ULINT_UNDEFINED; + } else { + *trx_end = (ulint) t; + } + } + } + fputs("----------------------------\n" "END OF INNODB MONITOR OUTPUT\n" "============================\n", file); @@ -2037,6 +2133,7 @@ = srv_buf_pool_write_requests; export_vars.innodb_buffer_pool_wait_free = srv_buf_pool_wait_free; export_vars.innodb_buffer_pool_pages_flushed = srv_buf_pool_flushed; + export_vars.innodb_buffer_pool_pages_LRU_flushed = buf_lru_flush_page_count; export_vars.innodb_buffer_pool_reads = srv_buf_pool_reads; export_vars.innodb_buffer_pool_read_ahead = stat.n_ra_pages_read; diff -ruN a/storage/innobase/sync/sync0arr.c b/storage/innobase/sync/sync0arr.c --- a/storage/innobase/sync/sync0arr.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/sync/sync0arr.c 2010-12-03 15:07:31.826041368 +0900 @@ -477,7 +477,7 @@ fprintf(file, "--Thread %lu has waited at %s line %lu" - " for %.2f seconds the semaphore:\n", + " for %#.5g seconds the semaphore:\n", (ulong) os_thread_pf(cell->thread), cell->file, (ulong) cell->line, difftime(time(NULL), cell->reservation_time)); diff -ruN a/storage/innobase/thr/thr0loc.c b/storage/innobase/thr/thr0loc.c --- a/storage/innobase/thr/thr0loc.c 2010-11-03 07:01:13.000000000 +0900 +++ b/storage/innobase/thr/thr0loc.c 2010-12-03 15:07:31.828023915 +0900 @@ -49,6 +49,7 @@ /** The hash table. The module is not yet initialized when it is NULL. */ static hash_table_t* thr_local_hash = NULL; +ulint thr_local_hash_n_nodes = 0; /** Thread local data */ typedef struct thr_local_struct thr_local_t; @@ -221,6 +222,7 @@ os_thread_pf(os_thread_get_curr_id()), local); + thr_local_hash_n_nodes++; mutex_exit(&thr_local_mutex); } @@ -249,6 +251,7 @@ HASH_DELETE(thr_local_t, hash, thr_local_hash, os_thread_pf(id), local); + thr_local_hash_n_nodes--; mutex_exit(&thr_local_mutex); @@ -305,3 +308,29 @@ hash_table_free(thr_local_hash); thr_local_hash = NULL; } + +/************************************************************************* +Return local hash table informations. */ + +ulint +thr_local_hash_cells(void) +/*======================*/ +{ + if (thr_local_hash) { + return (thr_local_hash->n_cells); + } else { + return 0; + } +} + +ulint +thr_local_hash_nodes(void) +/*======================*/ +{ + if (thr_local_hash) { + return (thr_local_hash_n_nodes + * (sizeof(thr_local_t) + MEM_BLOCK_HEADER_SIZE)); + } else { + return 0; + } +} diff -ruN a/storage/innobase/trx/trx0purge.c b/storage/innobase/trx/trx0purge.c --- a/storage/innobase/trx/trx0purge.c 2010-12-04 02:58:26.000000000 +0900 +++ b/storage/innobase/trx/trx0purge.c 2011-01-21 19:40:42.086683671 +0900 @@ -1212,7 +1212,7 @@ /*=====================*/ { fprintf(stderr, "InnoDB: Purge system view:\n"); - read_view_print(purge_sys->view); + read_view_print(stderr, purge_sys->view); fprintf(stderr, "InnoDB: Purge trx n:o " TRX_ID_FMT ", undo n:o " TRX_ID_FMT "\n",