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.

6 files changed, 102 insertions, 57 deletions

diff --git a/storage/xtradb/include/srv0srv.h b/storage/xtradb/include/srv0srv.h
index de994e79b30..de33f767021 100644
--- a/storage/xtradb/include/srv0srv.h
+++ b/storage/xtradb/include/srv0srv.h
@@ -708,6 +708,9 @@ extern ulong	srv_fatal_semaphore_wait_threshold;
 /** Default encryption key used for page encryption */
 extern uint srv_default_page_encryption_key;
 
+/** Enable semaphore request instrumentation */
+extern my_bool srv_instrument_semaphores;
+
 # ifdef UNIV_PFS_THREAD
 /* Keys to register InnoDB threads with performance schema */
 extern mysql_pfs_key_t	buf_page_cleaner_thread_key;
diff --git a/storage/xtradb/include/sync0arr.h b/storage/xtradb/include/sync0arr.h
index 46e192d05f1..a3a326a0d1d 100644
--- a/storage/xtradb/include/sync0arr.h
+++ b/storage/xtradb/include/sync0arr.h
@@ -31,7 +31,7 @@ Created 9/5/1995 Heikki Tuuri
 #include "ut0mem.h"
 #include "os0thread.h"
 
-/** Synchronization wait array cell */
+/** Synchonization cell */
 struct sync_cell_t;
 /** Synchronization wait array */
 struct sync_array_t;
@@ -154,6 +154,16 @@ UNIV_INTERN
 void
 sync_array_print_xtradb(void);
 
+/*****************************************************************//**
+Gets the nth cell in array.
+@return	cell */
+UNIV_INTERN
+sync_cell_t*
+sync_array_get_nth_cell(
+/*====================*/
+	sync_array_t*	arr,	/*!< in: sync array */
+	ulint		n);	/*!< in: index */
+
 #ifndef UNIV_NONINL
 #include "sync0arr.ic"
 #endif
diff --git a/storage/xtradb/include/sync0rw.h b/storage/xtradb/include/sync0rw.h
index 0ac6b0f3f69..1df6a793637 100644
--- a/storage/xtradb/include/sync0rw.h
+++ b/storage/xtradb/include/sync0rw.h
@@ -40,6 +40,9 @@ Created 9/11/1995 Heikki Tuuri
 #include "sync0sync.h"
 #include "os0sync.h"
 
+/** Enable semaphore request instrumentation */
+extern my_bool srv_instrument_semaphores;
+
 /* The following undef is to prevent a name conflict with a macro
 in MySQL: */
 #undef rw_lock_t
@@ -153,14 +156,14 @@ defined, the rwlock are instrumented with performance schema probes. */
 # ifdef UNIV_DEBUG
 #  ifdef UNIV_SYNC_DEBUG
 #   define rw_lock_create(K, L, level)				\
-	rw_lock_create_func((L), (level), __FILE__, __LINE__, #L)
+	rw_lock_create_func((L), (level), #L, __FILE__, __LINE__)
 #  else	/* UNIV_SYNC_DEBUG */
 #   define rw_lock_create(K, L, level)				\
-	rw_lock_create_func((L), __FILE__, __LINE__, #L)
+	rw_lock_create_func((L), #L, __FILE__, __LINE__)
 #  endif/* UNIV_SYNC_DEBUG */
 # else /* UNIV_DEBUG */
 #  define rw_lock_create(K, L, level)				\
-	rw_lock_create_func((L), #L)
+	rw_lock_create_func((L), __FILE__, __LINE__)
 # endif	/* UNIV_DEBUG */
 
 /**************************************************************//**
@@ -218,14 +221,14 @@ unlocking, not the corresponding function. */
 # ifdef UNIV_DEBUG
 #  ifdef UNIV_SYNC_DEBUG
 #   define rw_lock_create(K, L, level)				\
-	pfs_rw_lock_create_func((K), (L), (level), __FILE__, __LINE__, #L)
+	pfs_rw_lock_create_func((K), (L), (level), #L, __FILE__, __LINE__)
 #  else	/* UNIV_SYNC_DEBUG */
 #   define rw_lock_create(K, L, level)				\
-	pfs_rw_lock_create_func((K), (L), __FILE__, __LINE__, #L)
+	pfs_rw_lock_create_func((K), (L), #L, __FILE__, __LINE__)
 #  endif/* UNIV_SYNC_DEBUG */
 # else	/* UNIV_DEBUG */
 #  define rw_lock_create(K, L, level)				\
-	pfs_rw_lock_create_func((K), (L), #L)
+	pfs_rw_lock_create_func((K), (L), #L, __FILE__, __LINE__)
 # endif	/* UNIV_DEBUG */
 
 /******************************************************************
@@ -295,10 +298,10 @@ rw_lock_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 #endif /* UNIV_DEBUG */
-	const char*	cmutex_name);	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline);		/*!< in: file line where created */
 /******************************************************************//**
 Creates, or rather, initializes a priority rw-lock object in a specified memory
 location (which must be appropriately aligned). The rw-lock is initialized
@@ -313,10 +316,10 @@ rw_lock_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 #endif /* UNIV_DEBUG */
-	const char*	cmutex_name);	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline);		/*!< in: file line where created */
 /******************************************************************//**
 Calling this function is obligatory only if the memory buffer containing
 the rw-lock is freed. Removes an rw-lock object from the global list. The
@@ -752,8 +755,11 @@ struct rw_lock_t {
 	struct PSI_rwlock *pfs_psi;/*!< The instrumentation hook */
 #endif
 	ulint count_os_wait;	/*!< Count of os_waits. May not be accurate */
-	//const char*	cfile_name;/*!< File name where lock created */
+	const char*	cfile_name;/*!< File name where lock created */
 	const char*	lock_name;/*!< lock name */
+	os_thread_id_t	thread_id;/*!< thread id */
+	const char*	file_name;/*!< File name where the lock was obtained */
+	ulint		line;	  /*!< Line where the rw-lock was locked */
         /* last s-lock file/line is not guaranteed to be correct */
 	const char*	last_s_file_name;/*!< File name where last s-locked */
 	const char*	last_x_file_name;/*!< File name where last x-locked */
@@ -764,7 +770,7 @@ struct rw_lock_t {
 				are at the start of this struct, thus we can
 				peek this field without causing much memory
 				bus traffic */
-	//unsigned	cline:14;	/*!< Line where created */
+	unsigned	cline:14;	/*!< Line where created */
 	unsigned	last_s_line:14;	/*!< Line number where last time s-locked */
 	unsigned	last_x_line:14;	/*!< Line number where last time x-locked */
 #ifdef UNIV_DEBUG
@@ -853,10 +859,10 @@ pfs_rw_lock_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 #endif /* UNIV_DEBUG */
-	const char*	cmutex_name);	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline);		/*!< in: file line where created */
 
 /******************************************************************//**
 Performance schema instrumented wrap function for rw_lock_create_func()
@@ -873,10 +879,10 @@ pfs_rw_lock_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 #endif /* UNIV_DEBUG */
-	const char*	cmutex_name);	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline);		/*!< in: file line where created */
 
 /******************************************************************//**
 Performance schema instrumented wrap function for rw_lock_x_lock_func()
diff --git a/storage/xtradb/include/sync0rw.ic b/storage/xtradb/include/sync0rw.ic
index 8aadc406132..72bce228170 100644
--- a/storage/xtradb/include/sync0rw.ic
+++ b/storage/xtradb/include/sync0rw.ic
@@ -382,6 +382,12 @@ rw_lock_s_lock_low(
 	lock->last_s_file_name = file_name;
 	lock->last_s_line = line;
 
+	if (srv_instrument_semaphores) {
+		lock->thread_id = os_thread_get_curr_id();
+		lock->file_name = file_name;
+		lock->line = line;
+	}
+
 	return(TRUE);	/* locking succeeded */
 }
 
@@ -551,6 +557,12 @@ rw_lock_x_lock_func_nowait(
 	rw_lock_add_debug_info(lock, 0, 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 = line;
 
@@ -799,10 +811,10 @@ pfs_rw_lock_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 # endif /* UNIV_DEBUG */
-	const char*	cmutex_name)	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline)		/*!< in: file line where created */
 {
 	/* Initialize the rwlock for performance schema */
 	lock->pfs_psi = PSI_RWLOCK_CALL(init_rwlock)(key, lock);
@@ -813,10 +825,10 @@ pfs_rw_lock_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 			    level,
 #  endif /* UNIV_SYNC_DEBUG */
-			    cfile_name,
-			    cline,
 # endif /* UNIV_DEBUG */
-			    cmutex_name);
+			    cmutex_name,
+			    cfile_name,
+			    cline);
 }
 
 /******************************************************************//**
@@ -834,10 +846,10 @@ pfs_rw_lock_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
-	const char*	cfile_name,	/*!< in: file name where created */
-	ulint		cline,		/*!< in: file line where created */
 # endif /* UNIV_DEBUG */
-	const char*	cmutex_name)	/*!< in: mutex name */
+	const char*	cmutex_name,	/*!< in: mutex name */
+	const char*	cfile_name,	/*!< in: file name where created */
+	ulint		cline)		/*!< in: file line where created */
 {
 	/* Initialize the rwlock for performance schema */
 	lock->base_lock.pfs_psi = PSI_RWLOCK_CALL(init_rwlock)(key, lock);
@@ -848,10 +860,10 @@ pfs_rw_lock_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 			    level,
 #  endif /* UNIV_SYNC_DEBUG */
-			    cfile_name,
-			    cline,
 # endif /* UNIV_DEBUG */
-			    cmutex_name);
+			    cmutex_name,
+			    cfile_name,
+			    cline);
 }
 
 /******************************************************************//**
diff --git a/storage/xtradb/include/sync0sync.h b/storage/xtradb/include/sync0sync.h
index 72cfbf61dd8..8112fff8a30 100644
--- a/storage/xtradb/include/sync0sync.h
+++ b/storage/xtradb/include/sync0sync.h
@@ -43,6 +43,9 @@ Created 9/5/1995 Heikki Tuuri
 #include "sync0arr.h"
 #include "ut0counter.h"
 
+/** Enable semaphore request instrumentation */
+extern my_bool srv_instrument_semaphores;
+
 #if  defined(UNIV_DEBUG) && !defined(UNIV_HOTBACKUP)
 extern "C" my_bool	timed_mutexes;
 #endif /* UNIV_DEBUG && !UNIV_HOTBACKUP */
@@ -189,7 +192,7 @@ necessary only if the memory block containing it is freed. */
 #  endif/* UNIV_SYNC_DEBUG */
 # else
 #  define mutex_create(K, M, level)				\
-	pfs_mutex_create_func((K), (M), #M)
+	pfs_mutex_create_func((K), (M), __FILE__, __LINE__, #M)
 # endif	/* UNIV_DEBUG */
 
 # define mutex_enter(M)						\
@@ -256,9 +259,9 @@ mutex_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
+#endif /* UNIV_DEBUG */
 	const char*	cfile_name,	/*!< in: file name where created */
 	ulint		cline,		/*!< in: file line where created */
-#endif /* UNIV_DEBUG */
 	const char*	cmutex_name);	/*!< in: mutex name */
 
 /******************************************************************//**
@@ -275,11 +278,11 @@ mutex_create_func(
 # ifdef UNIV_SYNC_DEBUG
 	ulint			level,		/*!< in: level */
 # endif /* UNIV_SYNC_DEBUG */
+#endif /* UNIV_DEBUG */
 	const char*		cfile_name,	/*!< in: file name where
 						created */
 	ulint			cline,		/*!< in: file line where
 						created */
-#endif /* UNIV_DEBUG */
 	const char*		cmutex_name);	/*!< in: mutex name */
 /******************************************************************//**
 NOTE! Use the corresponding macro mutex_free(), not directly this function!
@@ -402,9 +405,9 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 	const char*	cfile_name,	/*!< in: file name where created */
 	ulint		cline,		/*!< in: file line where created */
-# endif /* UNIV_DEBUG */
 	const char*	cmutex_name);
 /******************************************************************//**
 NOTE! Please use the corresponding macro mutex_create(), not directly
@@ -423,11 +426,11 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint			level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 	const char*		cfile_name,	/*!< in: file name where
 						created */
 	ulint			cline,		/*!< in: file line where
 						created */
-# endif /* UNIV_DEBUG */
 	const char*		cmutex_name);
 /******************************************************************//**
 NOTE! Please use the corresponding macro mutex_enter(), not directly
@@ -947,27 +950,28 @@ struct ib_mutex_t {
 				Otherwise, this is 0. */
 	UT_LIST_NODE_T(ib_mutex_t)	list; /*!< All allocated mutexes are put into
 				a list.	Pointers to the next and prev. */
+
 #ifdef UNIV_SYNC_DEBUG
-	const char*	file_name;	/*!< File where the mutex was locked */
-	ulint	line;		/*!< Line where the mutex was locked */
-	ulint	level;		/*!< Level in the global latching order */
+	ulint	level;			/*!< Level in the global latching order */
 #endif /* UNIV_SYNC_DEBUG */
-#ifdef UNIV_DEBUG
-	const char*	cfile_name;/*!< File name where mutex created */
-	ulint		cline;	/*!< Line where created */
-#endif
+
+	const char*	file_name;	/*!< File where the mutex was locked */
+	ulint		line;		/*!< Line where the mutex was locked */
+	const char*	cfile_name;	/*!< File name where mutex created */
+	ulint		cline;		/*!< Line where created */
 	ulong		count_os_wait;	/*!< count of os_wait */
+	const char*	cmutex_name;	/*!< mutex name */
+	os_thread_id_t thread_id;	/*!< The thread id of the thread
+					which locked the mutex. */
+
 #ifdef UNIV_DEBUG
 
 /** Value of mutex_t::magic_n */
 # define MUTEX_MAGIC_N	979585UL
-
-	os_thread_id_t thread_id; /*!< The thread id of the thread
-				which locked the mutex. */
 	ulint		magic_n;	/*!< MUTEX_MAGIC_N */
 	ulint		ib_mutex_type;	/*!< 0=usual mutex, 1=rw_lock mutex */
 #endif /* UNIV_DEBUG */
-	const char*	cmutex_name;	/*!< mutex name */
+
 #ifdef UNIV_PFS_MUTEX
 	struct PSI_mutex* pfs_psi;	/*!< The performance schema
 					instrumentation hook */
diff --git a/storage/xtradb/include/sync0sync.ic b/storage/xtradb/include/sync0sync.ic
index 8176ae55fd8..90789339249 100644
--- a/storage/xtradb/include/sync0sync.ic
+++ b/storage/xtradb/include/sync0sync.ic
@@ -165,7 +165,7 @@ mutex_exit_func(
 {
 	ut_ad(mutex_own(mutex));
 
-	ut_d(mutex->thread_id = (os_thread_id_t) ULINT_UNDEFINED);
+	mutex->thread_id = (os_thread_id_t) ULINT_UNDEFINED;
 
 #ifdef UNIV_SYNC_DEBUG
 	sync_thread_reset_level(mutex);
@@ -205,7 +205,7 @@ mutex_exit_func(
 {
 	ut_ad(mutex_own(mutex));
 
-	ut_d(mutex->base_mutex.thread_id = (os_thread_id_t) ULINT_UNDEFINED);
+	mutex->base_mutex.thread_id = (os_thread_id_t) ULINT_UNDEFINED;
 
 #ifdef UNIV_SYNC_DEBUG
 	sync_thread_reset_level(&mutex->base_mutex);
@@ -264,10 +264,15 @@ mutex_enter_func(
 	the atomic test_and_set; we could peek, and possibly save time. */
 
 	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);
 #endif
+		if (srv_instrument_semaphores) {
+			mutex->file_name = file_name;
+			mutex->line = line;
+		}
+
 		return;	/* Succeeded! */
 	}
 
@@ -304,10 +309,15 @@ mutex_enter_func(
 	the atomic test_and_set; we could peek, and possibly save time. */
 
 	if (!ib_mutex_test_and_set(&mutex->base_mutex)) {
-		ut_d(mutex->base_mutex.thread_id = os_thread_get_curr_id());
+		mutex->base_mutex.thread_id = os_thread_get_curr_id();
 #ifdef UNIV_SYNC_DEBUG
 		mutex_set_debug_info(&mutex->base_mutex, file_name, line);
 #endif
+		if(srv_instrument_semaphores) {
+			mutex->base_mutex.file_name = file_name;
+			mutex->base_mutex.line = line;
+		}
+
 		return;	/* Succeeded! */
 	}
 
@@ -515,9 +525,9 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint		level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 	const char*	cfile_name,	/*!< in: file name where created */
 	ulint		cline,		/*!< in: file line where created */
-# endif /* UNIV_DEBUG */
 	const char*	cmutex_name)	/*!< in: mutex name */
 {
 	mutex->pfs_psi = PSI_MUTEX_CALL(init_mutex)(key, mutex);
@@ -527,9 +537,9 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 			  level,
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 			  cfile_name,
 			  cline,
-# endif /* UNIV_DEBUG */
 			  cmutex_name);
 }
 
@@ -550,11 +560,11 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 	ulint			level,		/*!< in: level */
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 	const char*		cfile_name,	/*!< in: file name where
 						created */
 	ulint			cline,		/*!< in: file line where
 						  created */
-# endif /* UNIV_DEBUG */
 	const char*		cmutex_name)
 {
 	mutex->base_mutex.pfs_psi = PSI_MUTEX_CALL(init_mutex)(key, mutex);
@@ -564,9 +574,9 @@ pfs_mutex_create_func(
 #  ifdef UNIV_SYNC_DEBUG
 			  level,
 #  endif /* UNIV_SYNC_DEBUG */
+# endif /* UNIV_DEBUG */
 			  cfile_name,
 			  cline,
-# endif /* UNIV_DEBUG */
 			  cmutex_name);
 }