This ChangeSet must be null-merged to 5.1. Applied innodb-5.0-ss982, -ss998, -ss1003

Fixes:
- Bug #15815: Very poor performance with multiple queries running concurrently
- Bug #22868: 'Thread thrashing' with > 50 concurrent conns under an upd-intensive workloadw
- Bug #23769: Debug assertion failure with innodb_locks_unsafe_for_binlog
- Bug #24089: Race condition in fil_flush_file_spaces()


innobase/buf/buf0buf.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1000:
  branches/5.0: Merge r999 from trunk:
  
  Reduce buffer pool mutex contention under >= 4 big concurrent
  CPU-bound SELECT queries.  (Bug #22868)
  
  Fix: replace the mutex by one mutex protecting the 'flush list'
  (and the free list) and several mutexes protecting portions of the
  buffer pool, where we keep several indivudual LRU lists of pages.
  
  This patch is from Sunny Bains and Heikki Tuuri.
innobase/buf/buf0flu.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1000:
  branches/5.0: Merge r999 from trunk:
  
  Reduce buffer pool mutex contention under >= 4 big concurrent
  CPU-bound SELECT queries.  (Bug #22868)
  
  Fix: replace the mutex by one mutex protecting the 'flush list'
  (and the free list) and several mutexes protecting portions of the
  buffer pool, where we keep several indivudual LRU lists of pages.
  
  This patch is from Sunny Bains and Heikki Tuuri.
innobase/buf/buf0lru.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1000:
  branches/5.0: Merge r999 from trunk:
  
  Reduce buffer pool mutex contention under >= 4 big concurrent
  CPU-bound SELECT queries.  (Bug #22868)
  
  Fix: replace the mutex by one mutex protecting the 'flush list'
  (and the free list) and several mutexes protecting portions of the
  buffer pool, where we keep several indivudual LRU lists of pages.
  
  This patch is from Sunny Bains and Heikki Tuuri.
innobase/dict/dict0crea.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r974:
  branches/5.0: Port r973 from trunk.
  
  Do not break the latching order in TRUNCATE TABLE.
  
  dict_truncate_index_tree(): Replace parameter rec_t* rec with
  btr_pcur_t* pcur.  Reposition pcur before calling btr_create().
  
  sync_thread_add_level(): Remove the relaxation of the assertion added in r968.
innobase/fil/fil0fil.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1003:
  branches/5.0: Merge r1002 from trunk:
  
  fil_flush_file_spaces(): Copy the system->unflushed_spaces list to an
  array while holding the mutex.  This removes the crash-triggering
  race condition that was introduced when fixing Bug 15653.  (Bug #24089)
innobase/include/buf0buf.h:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1000:
  branches/5.0: Merge r999 from trunk:
  
  Reduce buffer pool mutex contention under >= 4 big concurrent
  CPU-bound SELECT queries.  (Bug #22868)
  
  Fix: replace the mutex by one mutex protecting the 'flush list'
  (and the free list) and several mutexes protecting portions of the
  buffer pool, where we keep several indivudual LRU lists of pages.
  
  This patch is from Sunny Bains and Heikki Tuuri.
innobase/include/buf0buf.ic:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1000:
  branches/5.0: Merge r999 from trunk:
  
  Reduce buffer pool mutex contention under >= 4 big concurrent
  CPU-bound SELECT queries.  (Bug #22868)
  
  Fix: replace the mutex by one mutex protecting the 'flush list'
  (and the free list) and several mutexes protecting portions of the
  buffer pool, where we keep several indivudual LRU lists of pages.
  
  This patch is from Sunny Bains and Heikki Tuuri.
innobase/include/dict0crea.h:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r974:
  branches/5.0: Port r973 from trunk.
  
  Do not break the latching order in TRUNCATE TABLE.
  
  dict_truncate_index_tree(): Replace parameter rec_t* rec with
  btr_pcur_t* pcur.  Reposition pcur before calling btr_create().
  
  sync_thread_add_level(): Remove the relaxation of the assertion added in r968.
innobase/include/sync0arr.h:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/include/sync0rw.h:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/include/sync0rw.ic:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/include/sync0sync.h:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/os/os0sync.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/row/row0mysql.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r974:
  branches/5.0: Port r973 from trunk.
  
  Do not break the latching order in TRUNCATE TABLE.
  
  dict_truncate_index_tree(): Replace parameter rec_t* rec with
  btr_pcur_t* pcur.  Reposition pcur before calling btr_create().
  
  sync_thread_add_level(): Remove the relaxation of the assertion added in r968.
innobase/row/row0sel.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r982:
  branches/5.0: row_sel(): Do not try to acquire a LOCK_REC_NOT_GAP lock
  on the supremum record.  Instead, skip to the next record.  (Bug #23769)
  This fix was backported from r623 in the 5.1 tree.
innobase/srv/srv0start.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r926:
  Refer to bug: 22268. Since no one tries to run 5.0 on Windows 95/ME it was
  decided to raise the limit of srv_max_n_threads to 10000 on Windows.
innobase/sync/sync0arr.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/sync/sync0rw.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.
innobase/sync/sync0sync.c:
  Applied innodb-5.0-ss982, -ss998, -ss1003
  
  Revision r969:
  branches/5.0: Port r968 from trunk:
  
  sync_thread_add_level(): When level == SYNC_TREE_NODE, allow the latching
  order to be violated if the thread holds dict_operation_lock, whose level is
  SYNC_DICT_OPERATION.  This removes the assertion failure of TRUNCATE TABLE
  #ifdef UNIV_SYNC_DEBUG.
  
  
  Revision r974:
  branches/5.0: Port r973 from trunk.
  
  Do not break the latching order in TRUNCATE TABLE.
  
  dict_truncate_index_tree(): Replace parameter rec_t* rec with
  btr_pcur_t* pcur.  Reposition pcur before calling btr_create().
  
  sync_thread_add_level(): Remove the relaxation of the assertion added in r968.
  
  
  Revision r1001:
  branches/5.0: Reduce locking contention:
  
  Bug #15815: 'Thread thrashing' with > 50 concurrent connections under
  an update-intensive workload.
  Fix: Introduce one event per InnoDB semaphore.
  
  This patch is from Sunny Bains and Heikki Tuuri.
  This patch will not be merged to trunk (MySQL/InnoDB 5.1) yet,
  because it tries to address the problem in a different way.

7 files changed, 54 insertions, 32 deletions

diff --git a/innobase/include/buf0buf.h b/innobase/include/buf0buf.h
index fc1d9a64c7f..11e5bb39e63 100644
--- a/innobase/include/buf0buf.h
+++ b/innobase/include/buf0buf.h
@@ -461,8 +461,8 @@ Gets the mutex number protecting the page record lock hash chain in the lock
 table. */
 UNIV_INLINE
 mutex_t*
-buf_frame_get_lock_mutex(
-/*=====================*/
+buf_frame_get_mutex(
+/*================*/
 			/* out: mutex */
 	byte*	ptr);	/* in: pointer to within a buffer frame */
 /***********************************************************************
@@ -713,7 +713,10 @@ struct buf_block_struct{
 
 	ulint		magic_n;	/* magic number to check */
 	ulint		state;		/* state of the control block:
-					BUF_BLOCK_NOT_USED, ... */
+					BUF_BLOCK_NOT_USED, ...; changing
+					this is only allowed when a thread
+					has BOTH the buffer pool mutex AND
+					block->mutex locked */
 	byte*		frame;		/* pointer to buffer frame which
 					is of size UNIV_PAGE_SIZE, and
 					aligned to an address divisible by
@@ -731,8 +734,12 @@ struct buf_block_struct{
 	ulint		offset;		/* page number within the space */
 	ulint		lock_hash_val;	/* hashed value of the page address
 					in the record lock hash table */
-	mutex_t*	lock_mutex;	/* mutex protecting the chain in the
-					record lock hash table */
+	mutex_t		mutex;		/* mutex protecting this block:
+					state (also protected by the buffer
+					pool mutex), io_fix, buf_fix_count,
+					and accessed; we introduce this new
+					mutex in InnoDB-5.1 to relieve
+					contention on the buffer pool mutex */
 	rw_lock_t	lock;		/* read-write lock of the buffer
 					frame */
 	buf_block_t*	hash;		/* node used in chaining to the page
@@ -788,20 +795,27 @@ struct buf_block_struct{
 					in heuristic algorithms, because of
 					the possibility of a wrap-around! */
 	ulint		freed_page_clock;/* the value of freed_page_clock
-					buffer pool when this block was
-					last time put to the head of the
-					LRU list */
+					of the buffer pool when this block was
+					the last time put to the head of the
+					LRU list; a thread is allowed to
+					read this for heuristic purposes
+					without holding any mutex or latch */
 	ibool		old;		/* TRUE if the block is in the old
 					blocks in the LRU list */
 	ibool		accessed;	/* TRUE if the page has been accessed
 					while in the buffer pool: read-ahead
 					may read in pages which have not been
-					accessed yet */
+					accessed yet; this is protected by
+					block->mutex; a thread is allowed to
+					read this for heuristic purposes
+					without holding any mutex or latch */
 	ulint		buf_fix_count;	/* count of how manyfold this block
-					is currently bufferfixed */
+					is currently bufferfixed; this is
+					protected by block->mutex */
 	ulint		io_fix;		/* if a read is pending to the frame,
 					io_fix is BUF_IO_READ, in the case
-					of a write BUF_IO_WRITE, otherwise 0 */
+					of a write BUF_IO_WRITE, otherwise 0;
+					this is protected by block->mutex */
 	/* 4. Optimistic search field */
 
 	dulint		modify_clock;	/* this clock is incremented every
@@ -962,7 +976,9 @@ struct buf_pool_struct{
 					number of buffer blocks removed from
 					the end of the LRU list; NOTE that
 					this counter may wrap around at 4
-					billion! */
+					billion! A thread is allowed to
+					read this for heuristic purposes
+					without holding any mutex or latch */
 	ulint		LRU_flush_ended;/* when an LRU flush ends for a page,
 					this is incremented by one; this is
 					set to zero when a buffer block is
diff --git a/innobase/include/buf0buf.ic b/innobase/include/buf0buf.ic
index af32db10b5f..ddc91b8d66c 100644
--- a/innobase/include/buf0buf.ic
+++ b/innobase/include/buf0buf.ic
@@ -330,8 +330,8 @@ Gets the mutex number protecting the page record lock hash chain in the lock
 table. */
 UNIV_INLINE
 mutex_t*
-buf_frame_get_lock_mutex(
-/*=====================*/
+buf_frame_get_mutex(
+/*================*/
 			/* out: mutex */
 	byte*	ptr)	/* in: pointer to within a buffer frame */
 {
@@ -339,7 +339,7 @@ buf_frame_get_lock_mutex(
 
 	block = buf_block_align(ptr);
 
-	return(block->lock_mutex);
+	return(&block->mutex);
 }
 
 /*************************************************************************
@@ -512,6 +512,7 @@ buf_block_buf_fix_inc_debug(
 	ret = rw_lock_s_lock_func_nowait(&(block->debug_latch), file, line);
 
 	ut_ad(ret == TRUE);
+	ut_ad(mutex_own(&block->mutex));
 #endif
 	block->buf_fix_count++;
 }
@@ -524,6 +525,9 @@ buf_block_buf_fix_inc(
 /*==================*/
 	buf_block_t*	block)	/* in: block to bufferfix */
 {
+#ifdef UNIV_SYNC_DEBUG
+	ut_ad(mutex_own(&block->mutex));
+#endif
 	block->buf_fix_count++;
 }
 #endif /* UNIV_SYNC_DEBUG */
@@ -618,23 +622,24 @@ buf_page_release(
 	
 	ut_ad(block);
 
-	mutex_enter_fast(&(buf_pool->mutex));
-
 	ut_a(block->state == BUF_BLOCK_FILE_PAGE);
 	ut_a(block->buf_fix_count > 0);
 
 	if (rw_latch == RW_X_LATCH && mtr->modifications) {
-
+		mutex_enter(&buf_pool->mutex);
 		buf_flush_note_modification(block, mtr);
+		mutex_exit(&buf_pool->mutex);
 	}
 
+	mutex_enter(&block->mutex);
+
 #ifdef UNIV_SYNC_DEBUG
 	rw_lock_s_unlock(&(block->debug_latch));
 #endif
 	buf_fix_count = block->buf_fix_count;
 	block->buf_fix_count = buf_fix_count - 1;
 
-	mutex_exit(&(buf_pool->mutex));
+	mutex_exit(&block->mutex);
 
 	if (rw_latch == RW_S_LATCH) {
 		rw_lock_s_unlock(&(block->lock));
diff --git a/innobase/include/dict0crea.h b/innobase/include/dict0crea.h
index 5dd571be59c..44acca61c5e 100644
--- a/innobase/include/dict0crea.h
+++ b/innobase/include/dict0crea.h
@@ -62,8 +62,10 @@ dict_truncate_index_tree(
 				/* out: new root page number, or
 				FIL_NULL on failure */
 	dict_table_t*	table,	/* in: the table the index belongs to */
-	rec_t*		rec,	/* in: record in the clustered index of
-				SYS_INDEXES table */
+	btr_pcur_t*	pcur,	/* in/out: persistent cursor pointing to
+				record in the clustered index of
+				SYS_INDEXES table. The cursor may be
+				repositioned in this call. */
 	mtr_t*		mtr);	/* in: mtr having the latch
 				on the record page. The mtr may be
 				committed and restarted in this call. */
diff --git a/innobase/include/sync0arr.h b/innobase/include/sync0arr.h
index fecd910683e..ba712d14aad 100644
--- a/innobase/include/sync0arr.h
+++ b/innobase/include/sync0arr.h
@@ -75,17 +75,12 @@ sync_array_free_cell(
 	sync_array_t*	arr,	/* in: wait array */
         ulint    	index);  /* in: index of the cell in array */
 /**************************************************************************
-Looks for the cells in the wait array which refer
-to the wait object specified,
-and sets their corresponding events to the signaled state. In this
-way releases the threads waiting for the object to contend for the object.
-It is possible that no such cell is found, in which case does nothing. */
+Note that one of the wait objects was signalled. */
 
 void
-sync_array_signal_object(
-/*=====================*/
-	sync_array_t*	arr,	/* in: wait array */
-	void*		object);/* in: wait object */
+sync_array_object_signalled(
+/*========================*/
+	sync_array_t*	arr);	/* in: wait array */
 /**************************************************************************
 If the wakeup algorithm does not work perfectly at semaphore relases,
 this function will do the waking (see the comment in mutex_exit). This
diff --git a/innobase/include/sync0rw.h b/innobase/include/sync0rw.h
index 911c8ac3f4a..741f9500612 100644
--- a/innobase/include/sync0rw.h
+++ b/innobase/include/sync0rw.h
@@ -411,6 +411,7 @@ blocked by readers, a writer may queue for the lock by setting the writer
 field. Then no new readers are allowed in. */
 
 struct rw_lock_struct {
+	os_event_t	event;	/* Used by sync0arr.c for thread queueing */
 	ulint	reader_count;	/* Number of readers who have locked this
 				lock in the shared mode */
 	ulint	writer; 	/* This field is set to RW_LOCK_EX if there
diff --git a/innobase/include/sync0rw.ic b/innobase/include/sync0rw.ic
index 9e15475ae53..31a1ea6562a 100644
--- a/innobase/include/sync0rw.ic
+++ b/innobase/include/sync0rw.ic
@@ -382,7 +382,8 @@ rw_lock_s_unlock_func(
 	mutex_exit(mutex);
 
 	if (UNIV_UNLIKELY(sg)) {
-		sync_array_signal_object(sync_primary_wait_array, lock);
+		os_event_set(lock->event);
+		sync_array_object_signalled(sync_primary_wait_array);
 	}
 
         ut_ad(rw_lock_validate(lock));
@@ -462,7 +463,8 @@ rw_lock_x_unlock_func(
 	mutex_exit(&(lock->mutex));
 
 	if (UNIV_UNLIKELY(sg)) {
-		sync_array_signal_object(sync_primary_wait_array, lock);
+		os_event_set(lock->event);
+		sync_array_object_signalled(sync_primary_wait_array);
 	}
 
         ut_ad(rw_lock_validate(lock));
diff --git a/innobase/include/sync0sync.h b/innobase/include/sync0sync.h
index 5955ab9a06a..9893921c5d2 100644
--- a/innobase/include/sync0sync.h
+++ b/innobase/include/sync0sync.h
@@ -453,6 +453,7 @@ Do not use its fields directly! The structure used in the spin lock
 implementation of a mutual exclusion semaphore. */
 
 struct mutex_struct {
+	os_event_t	event;	/* Used by sync0arr.c for the wait queue */
 	ulint	lock_word;	/* This ulint is the target of the atomic
 				test-and-set instruction in Win32 */
 #if !defined(_WIN32) || !defined(UNIV_CAN_USE_X86_ASSEMBLER)