- rel. 2

[packages/mysql.git] / innodb_show_status.patch

# 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!
--- a/storage/innobase/buf/buf0buf.c
+++ b/storage/innobase/buf/buf0buf.c
@@ -4372,6 +4372,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;
@@ -4437,6 +4438,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;
@@ -4558,14 +4561,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,
--- a/storage/innobase/buf/buf0flu.c
+++ b/storage/innobase/buf/buf0flu.c
@@ -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;
 
 /* @} */
 
--- a/storage/innobase/fil/fil0fil.c
+++ b/storage/innobase/fil/fil0fil.c
@@ -4897,3 +4897,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;
+       }
+}
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -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
@@ -11207,6 +11209,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,
@@ -11394,7 +11406,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,
@@ -11494,6 +11506,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),
--- a/storage/innobase/include/buf0buf.h
+++ b/storage/innobase/include/buf0buf.h
@@ -128,6 +128,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 */
--- a/storage/innobase/include/fil0fil.h
+++ b/storage/innobase/include/fil0fil.h
@@ -731,6 +731,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
--- a/storage/innobase/include/read0read.h
+++ b/storage/innobase/include/read0read.h
@@ -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
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -146,6 +146,9 @@
 corrupted index and table */
 extern my_bool	srv_load_corrupted;
 
+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;
@@ -328,6 +331,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;
@@ -707,6 +712,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_rnd;/*!< srv_read_ahead_rnd */
 	ulint innodb_buffer_pool_read_ahead;	/*!< srv_read_ahead */
--- a/storage/innobase/lock/lock0lock.c
+++ b/storage/innobase/lock/lock0lock.c
@@ -4377,6 +4377,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) {
@@ -4403,6 +4404,7 @@
 
 		putc('\n', file);
 	}
+	}
 
 	mtr_commit(&mtr);
 	if (UNIV_LIKELY_NULL(heap)) {
@@ -4586,7 +4588,7 @@
 		}
 	}
 
-	if (!srv_print_innodb_lock_monitor) {
+        if (!srv_print_innodb_lock_monitor && !srv_show_locks_held) {
 		nth_trx++;
 		goto loop;
 	}
@@ -4658,8 +4660,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);
 
--- a/storage/innobase/read/read0read.c
+++ b/storage/innobase/read/read0read.c
@@ -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));
 	}
 }
--- a/storage/innobase/srv/srv0srv.c
+++ b/storage/innobase/srv/srv0srv.c
@@ -83,6 +83,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"
 
@@ -188,6 +189,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
@@ -316,6 +320,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 */
@@ -1825,6 +1830,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);
@@ -1873,31 +1885,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);
@@ -1928,10 +1915,78 @@
 	      "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",
+
+			(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);
+
 	fprintf(file, "Dictionary memory allocated " ULINTPF "\n",
 		dict_sys->size);
 
@@ -1947,6 +2002,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,
@@ -1990,6 +2055,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);
@@ -2033,6 +2123,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_rnd
 		= stat.n_ra_pages_read_rnd;
--- a/storage/innobase/sync/sync0arr.c
+++ b/storage/innobase/sync/sync0arr.c
@@ -478,7 +478,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),
 		innobase_basename(cell->file), (ulong) cell->line,
 		difftime(time(NULL), cell->reservation_time));
--- a/storage/innobase/trx/trx0purge.c
+++ b/storage/innobase/trx/trx0purge.c
@@ -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",