MDEV-7109: Add support for INFORMATION_SCHEMA.INNODB_SEMAPHORE_WAITS

    MDEV-7399: Add support for INFORMATION_SCHEMA.INNODB_MUTEXES
    MDEV-7618: Improve semaphore instrumentation

    Introduced two new information schema tables to monitor mutex waits
    and semaphore waits. Added a new configuration variable
    innodb_intrument_semaphores to add thread_id, file name and
    line of current holder of mutex/rw_lock.

3 files changed, 205 insertions, 13 deletions

diff --git a/storage/innobase/sync/sync0arr.cc b/storage/innobase/sync/sync0arr.cc
index 10c201e990e..e501423f222 100644
--- a/storage/innobase/sync/sync0arr.cc
+++ b/storage/innobase/sync/sync0arr.cc
@@ -2,6 +2,7 @@
 
 Copyright (c) 1995, 2013, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, Google Inc.
+Copyright (c) 2013, 2015, MariaDB Corporation. All Rights Reserved.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -30,11 +31,26 @@ The wait array used in synchronization primitives
 Created 9/5/1995 Heikki Tuuri
 *******************************************************/
 
+#include "univ.i"
+
 #include "sync0arr.h"
 #ifdef UNIV_NONINL
 #include "sync0arr.ic"
 #endif
 
+#include <mysqld_error.h>
+#include <mysql/plugin.h>
+#include <hash.h>
+#include <myisampack.h>
+#include <sql_acl.h>
+#include <mysys_err.h>
+#include <my_sys.h>
+#include "srv0srv.h"
+#include "srv0start.h"
+#include "i_s.h"
+#include <sql_plugin.h>
+#include <innodb_priv.h>
+
 #include "sync0sync.h"
 #include "sync0rw.h"
 #include "os0sync.h"
@@ -115,7 +131,6 @@ for an event allocated for the array without owning the
 protecting mutex (depending on the case: OS or database mutex), but
 all changes (set or reset) to the state of the event must be made
 while owning the mutex. */
-
 /** Synchronization array */
 struct sync_array_t {
 	ulint		n_reserved;	/*!< number of currently reserved
@@ -168,7 +183,6 @@ sync_array_detect_deadlock(
 /*****************************************************************//**
 Gets the nth cell in array.
 @return	cell */
-static
 sync_cell_t*
 sync_array_get_nth_cell(
 /*====================*/
@@ -507,7 +521,7 @@ sync_array_cell_print(
 		      : type == RW_LOCK_WAIT_EX ? "X-lock (wait_ex) on"
 		      : "S-lock on", file);
 
-		rwlock = cell->old_wait_rw_lock;
+		rwlock = (rw_lock_t*)cell->old_wait_rw_lock;
 
 		if (rwlock) {
 			fprintf(file,
@@ -1282,3 +1296,153 @@ sync_array_print_innodb(void)
 	fputs("InnoDB: Semaphore wait debug output ended:\n", stderr);
 
 }
+
+/**********************************************************************//**
+Get number of items on sync array. */
+UNIV_INTERN
+ulint
+sync_arr_get_n_items(void)
+/*======================*/
+{
+	sync_array_t*	sync_arr = sync_array_get();
+	return (ulint) sync_arr->n_cells;
+}
+
+/******************************************************************//**
+Get specified item from sync array if it is reserved. Set given
+pointer to array item if it is reserved.
+@return true if item is reserved, false othervise */
+UNIV_INTERN
+ibool
+sync_arr_get_item(
+/*==============*/
+	ulint		i,		/*!< in: requested item */
+	sync_cell_t	**cell)		/*!< out: cell contents if item
+					reserved */
+{
+	sync_array_t*	sync_arr;
+	sync_cell_t*	wait_cell;
+	void*		wait_object;
+	ibool		found = FALSE;
+
+	sync_arr = sync_array_get();
+	wait_cell = sync_array_get_nth_cell(sync_arr, i);
+
+	if (wait_cell) {
+		wait_object = wait_cell->wait_object;
+
+		if(wait_object != NULL && wait_cell->waiting) {
+			found = TRUE;
+			*cell = wait_cell;
+		}
+	}
+
+	return found;
+}
+
+/*******************************************************************//**
+Function to populate INFORMATION_SCHEMA.INNODB_SYS_SEMAPHORE_WAITS table.
+Loop through each item on sync array, and extract the column
+information and fill the INFORMATION_SCHEMA.INNODB_SYS_SEMAPHORE_WAITS table.
+@return 0 on success */
+UNIV_INTERN
+int
+sync_arr_fill_sys_semphore_waits_table(
+/*===================================*/
+	THD*		thd,	/*!< in: thread */
+	TABLE_LIST*	tables,	/*!< in/out: tables to fill */
+	Item*		)	/*!< in: condition (not used) */
+{
+	Field**		fields;
+	ulint		n_items;
+
+	DBUG_ENTER("i_s_sys_semaphore_waits_fill_table");
+	RETURN_IF_INNODB_NOT_STARTED(tables->schema_table_name);
+
+	/* deny access to user without PROCESS_ACL privilege */
+	if (check_global_access(thd, PROCESS_ACL)) {
+		DBUG_RETURN(0);
+	}
+
+	fields = tables->table->field;
+	n_items = sync_arr_get_n_items();
+	ulint type;
+
+	for(ulint i=0; i < n_items;i++) {
+		sync_cell_t *cell=NULL;
+		if (sync_arr_get_item(i, &cell)) {
+			ib_mutex_t* mutex;
+			type = cell->request_type;
+			OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_THREAD_ID], (longlong)os_thread_pf(cell->thread)));
+			OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_FILE], innobase_basename(cell->file)));
+			OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LINE], cell->line));
+			OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WAIT_TIME], (longlong)difftime(time(NULL), cell->reservation_time)));
+
+			if (type == SYNC_MUTEX) {
+				mutex = static_cast<ib_mutex_t*>(cell->old_wait_mutex);
+
+				if (mutex) {
+					OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_OBJECT_NAME], mutex->cmutex_name));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WAIT_OBJECT], (longlong)mutex));
+					OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_WAIT_TYPE], "MUTEX"));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_HOLDER_THREAD_ID], (longlong)mutex->thread_id));
+					OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_HOLDER_FILE], innobase_basename(mutex->file_name)));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_HOLDER_LINE], mutex->line));
+					OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_CREATED_FILE], innobase_basename(mutex->cfile_name)));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_CREATED_LINE], mutex->cline));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WAITERS_FLAG], (longlong)mutex->waiters));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LOCK_WORD], (longlong)mutex->lock_word));
+					OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_LAST_WRITER_FILE], innobase_basename(mutex->file_name)));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LAST_WRITER_LINE], mutex->line));
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_OS_WAIT_COUNT], mutex->count_os_wait));
+				}
+			} else if (type == RW_LOCK_EX
+				|| type == RW_LOCK_WAIT_EX
+				|| type == RW_LOCK_SHARED) {
+				rw_lock_t* rwlock=NULL;
+
+				rwlock = static_cast<rw_lock_t *> (cell->old_wait_rw_lock);
+
+				if (rwlock) {
+					ulint writer = rw_lock_get_writer(rwlock);
+
+					OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WAIT_OBJECT], (longlong)rwlock));
+					if (type == RW_LOCK_EX) {
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_WAIT_TYPE], "RW_LOCK_EX"));
+					} else if (type == RW_LOCK_WAIT_EX) {
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_WAIT_TYPE], "RW_LOCK_WAIT_EX"));
+					} else if (type == RW_LOCK_SHARED) {
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_WAIT_TYPE], "RW_LOCK_SHARED"));
+					}
+
+					if (writer != RW_LOCK_NOT_LOCKED) {
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_OBJECT_NAME], rwlock->lock_name));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WRITER_THREAD], (longlong)os_thread_pf(rwlock->writer_thread)));
+
+						if (writer == RW_LOCK_EX) {
+							OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_RESERVATION_MODE], "RW_LOCK_EX"));
+						} else if (writer == RW_LOCK_WAIT_EX) {
+							OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_RESERVATION_MODE], "RW_LOCK_WAIT_EX"));
+						}
+
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_HOLDER_THREAD_ID], (longlong)rwlock->thread_id));
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_HOLDER_FILE], innobase_basename(rwlock->file_name)));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_HOLDER_LINE], rwlock->line));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_READERS], rw_lock_get_reader_count(rwlock)));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_WAITERS_FLAG], (longlong)rwlock->waiters));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LOCK_WORD], (longlong)rwlock->lock_word));
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_LAST_READER_FILE], innobase_basename(rwlock->last_s_file_name)));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LAST_READER_LINE], rwlock->last_s_line));
+						OK(field_store_string(fields[SYS_SEMAPHORE_WAITS_LAST_WRITER_FILE], innobase_basename(rwlock->last_x_file_name)));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_LAST_WRITER_LINE], rwlock->last_x_line));
+						OK(field_store_ulint(fields[SYS_SEMAPHORE_WAITS_OS_WAIT_COUNT], rwlock->count_os_wait));
+					}
+				}
+			}
+
+			OK(schema_table_store_record(thd, tables->table));
+		}
+	}
+
+	DBUG_RETURN(0);
+}
diff --git a/storage/innobase/sync/sync0rw.cc b/storage/innobase/sync/sync0rw.cc
index 4ff330791a0..8e5faed08dd 100644
--- a/storage/innobase/sync/sync0rw.cc
+++ b/storage/innobase/sync/sync0rw.cc
@@ -209,8 +209,8 @@ rw_lock_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
-	const char*	cmutex_name,	/*!< in: mutex name */
 #endif /* UNIV_DEBUG */
+	const char*	cmutex_name,	/*!< in: mutex name */
 	const char*	cfile_name,	/*!< in: file name where created */
 	ulint		cline)		/*!< in: file line where created */
 {
@@ -223,8 +223,7 @@ rw_lock_create_func(
 
 	lock->mutex.cfile_name = cfile_name;
 	lock->mutex.cline = cline;
-
-	ut_d(lock->mutex.cmutex_name = cmutex_name);
+	lock->mutex.lock_name = cmutex_name;
 	ut_d(lock->mutex.ib_mutex_type = 1);
 #else /* INNODB_RW_LOCKS_USE_ATOMICS */
 # ifdef UNIV_DEBUG
@@ -253,8 +252,10 @@ rw_lock_create_func(
 
 	lock->cfile_name = cfile_name;
 	lock->cline = (unsigned int) cline;
-
+	lock->lock_name = cmutex_name;
 	lock->count_os_wait = 0;
+	lock->file_name = "not yet reserved";
+	lock->line = 0;
 	lock->last_s_file_name = "not yet reserved";
 	lock->last_x_file_name = "not yet reserved";
 	lock->last_s_line = 0;
@@ -516,6 +517,12 @@ rw_lock_x_lock_wait(
 					       file_name, line);
 #endif
 
+			if (srv_instrument_semaphores) {
+				lock->thread_id = os_thread_get_curr_id();
+				lock->file_name = file_name;
+				lock->line = line;
+			}
+
 			sync_array_wait_event(sync_arr, index);
 #ifdef UNIV_SYNC_DEBUG
 			rw_lock_remove_debug_info(
@@ -588,6 +595,13 @@ rw_lock_x_lock_low(
 #ifdef UNIV_SYNC_DEBUG
 	rw_lock_add_debug_info(lock, pass, RW_LOCK_EX, file_name, line);
 #endif
+
+	if (srv_instrument_semaphores) {
+		lock->thread_id = os_thread_get_curr_id();
+		lock->file_name = file_name;
+		lock->line = line;
+	}
+
 	lock->last_x_file_name = file_name;
 	lock->last_x_line = (unsigned int) line;
 
diff --git a/storage/innobase/sync/sync0sync.cc b/storage/innobase/sync/sync0sync.cc
index aa2b5fa29db..5f5c6d2a5f2 100644
--- a/storage/innobase/sync/sync0sync.cc
+++ b/storage/innobase/sync/sync0sync.cc
@@ -265,8 +265,8 @@ void
 mutex_create_func(
 /*==============*/
 	ib_mutex_t*	mutex,		/*!< in: pointer to memory */
-#ifdef UNIV_DEBUG
 	const char*	cmutex_name,	/*!< in: mutex name */
+#ifdef UNIV_DEBUG
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
@@ -285,9 +285,10 @@ mutex_create_func(
 #ifdef UNIV_DEBUG
 	mutex->magic_n = MUTEX_MAGIC_N;
 #endif /* UNIV_DEBUG */
-#ifdef UNIV_SYNC_DEBUG
+
 	mutex->line = 0;
 	mutex->file_name = "not yet reserved";
+#ifdef UNIV_SYNC_DEBUG
 	mutex->level = level;
 #endif /* UNIV_SYNC_DEBUG */
 	mutex->cfile_name = cfile_name;
@@ -398,11 +399,15 @@ mutex_enter_nowait_func(
 
 	if (!ib_mutex_test_and_set(mutex)) {
 
-		ut_d(mutex->thread_id = os_thread_get_curr_id());
+		mutex->thread_id = os_thread_get_curr_id();
 #ifdef UNIV_SYNC_DEBUG
 		mutex_set_debug_info(mutex, file_name, line);
+#else
+		if (srv_instrument_semaphores) {
+			mutex->file_name = file_name;
+			mutex->line = line;
+		}
 #endif
-
 		return(0);	/* Succeeded! */
 	}
 
@@ -520,10 +525,15 @@ spin_loop:
 	if (ib_mutex_test_and_set(mutex) == 0) {
 		/* Succeeded! */
 
-		ut_d(mutex->thread_id = os_thread_get_curr_id());
+		mutex->thread_id = os_thread_get_curr_id();
 #ifdef UNIV_SYNC_DEBUG
 		mutex_set_debug_info(mutex, file_name, line);
 #endif
+		if (srv_instrument_semaphores) {
+			mutex->file_name = file_name;
+			mutex->line = line;
+		}
+
 		return;
 	}
 
@@ -563,10 +573,14 @@ spin_loop:
 
 			sync_array_free_cell(sync_arr, index);
 
-			ut_d(mutex->thread_id = os_thread_get_curr_id());
+			mutex->thread_id = os_thread_get_curr_id();
 #ifdef UNIV_SYNC_DEBUG
 			mutex_set_debug_info(mutex, file_name, line);
 #endif
+			if (srv_instrument_semaphores) {
+				mutex->file_name = file_name;
+				mutex->line = line;
+			}
 
 			return;