List:Commits« Previous MessageNext Message »
From:Joerg Bruehe Date:January 10 2011 3:48pm
Subject:bzr commit into mysql-trunk branch (joerg:3447)
View as plain text  
#At file:///MySQL/REPO/Vtu/clone-trunk/ based on revid:andrei.elkin@stripped

 3447 Joerg Bruehe	2011-01-10
      Transfer a bug fix into the 5.6.1-m5 release build:
      
      > revno: 3229.3.130
      > committer: Sunny Bains <Sunny.Bains@stripped>
      > branch nick: trunk
      > timestamp: Thu 2011-01-06 09:49:20 +1100
      > message:
      >   Fix Bug #59032- InnoDB data files for the same data are much bigger than in previous versions
      > 
      >   There are two problems that need to be fixed.
      > 
      >    1. The purge batch size was in records, should have been in UNDO log pages.
      >       This caused trx_purge() to return 0 UNDO log pages processed for the case
      >       where multiple (20) small UNDO records fit into a single UNDO log page.
      >       This confused the caller into thinking that there are no more UNDO log
      >       to purge. This caused the caller to back off for sometime and in the
      >       meantime the UNDO records generated by user activity filled up the system
      >       tablespace.
      > 
      >    2. The black magic constants in srv_purge_coordinator_thread() are too
      >       conservative. I've made them a little aggressive.
      > 
      >   Removed trx_purge_t::n_pages_handled, it is now a local variable.
      > 
      >   Made the code more obvious in trx_purge_read_undo_rec(). This change doesn't
      >   affect correctness. It was already doing the right thing.
      > 
      >   Approved by Jimmy, rb://558.

    modified:
      storage/innobase/include/trx0purge.h
      storage/innobase/srv/srv0srv.c
      storage/innobase/trx/trx0purge.c
=== modified file 'storage/innobase/include/trx0purge.h'
--- a/storage/innobase/include/trx0purge.h	2010-11-11 02:05:15 +0000
+++ b/storage/innobase/include/trx0purge.h	2011-01-10 15:48:38 +0000
@@ -142,8 +142,6 @@ struct trx_purge_struct{
 
 	mutex_t		mutex;		/*!< Mutex protecting the fields
 					below */
-	ulint		n_pages_handled;/*!< Approximate number of undo log
-					pages processed in purge */
 	/*-----------------------------*/
 	purge_iter_t	iter;		/* Limit up to which we have read and
 					parsed the UNDO log records.  Not

=== modified file 'storage/innobase/srv/srv0srv.c'
--- a/storage/innobase/srv/srv0srv.c	2010-12-13 12:08:11 +0000
+++ b/storage/innobase/srv/srv0srv.c	2011-01-10 15:48:38 +0000
@@ -248,7 +248,7 @@ UNIV_INTERN ulong	srv_max_buf_pool_modif
 /* the number of purge threads to use from the worker pool (currently 0 or 1).*/
 UNIV_INTERN ulong srv_n_purge_threads = 0;
 
-/* the number of records to purge in one batch */
+/* the number of UNDO log pages to purge in one batch */
 UNIV_INTERN ulong srv_purge_batch_size = 20;
 
 /* variable counts amount of data read in total (in bytes) */
@@ -2737,14 +2737,14 @@ srv_purge_coordinator_thread(
 				is purely guess work and needs to be tuned
 				properly after some benchmarking. */
 				if (srv_check_activity(count)) {
-					sleep_ms = 1000000;
+					sleep_ms = 60000;
 					batch_size = srv_purge_batch_size;
 				} else if (n_pages_purged == 0) {
-					sleep_ms = 5000000;
+					sleep_ms = 120000;
 					batch_size = srv_purge_batch_size;
 				} else {
 					sleep_ms = 0;
-					batch_size = 5000;
+					batch_size = 500;
 				}
 
 				/* No point in sleeping during shutdown. */
@@ -2755,8 +2755,8 @@ srv_purge_coordinator_thread(
 				}
 
 				/* Take snapshot to check for user
-				activity later every 3 seconds. */
-				if (ut_time() - last_time > 1) {
+				activity at every second. */
+				if (ut_time() - last_time >= 1) {
 					count = srv_sys->activity_count;
 					last_time = ut_time();
 				}
@@ -2794,22 +2794,22 @@ srv_purge_coordinator_thread(
 				is purely guess work and needs to be tuned
 				properly after some benchmarking. */
 				if (!srv_check_activity(count)
-				    && trx_sys->rseg_history_len > 5000) {
+				    && trx_sys->rseg_history_len > 500) {
 					sleep_ms = 0;
-					batch_size = 5000;
+					batch_size = 500;
 				} else {
-					sleep_ms = 1000000;
+					sleep_ms = 60000;
 
 					if (n_pages_purged > 0) {
-						sleep_ms = 100000;
+						sleep_ms = 150000;
 					}
 
 					batch_size = srv_purge_batch_size;
 				}
 
 				/* Take snapshot to check for user
-				activity later every second. */
-				if (ut_time() - last_time > 1) {
+				activity at every second. */
+				if (ut_time() - last_time >= 1) {
 					count = srv_sys->activity_count;
 					last_time = ut_time();
 				}

=== modified file 'storage/innobase/trx/trx0purge.c'
--- a/storage/innobase/trx/trx0purge.c	2010-11-24 20:55:57 +0000
+++ b/storage/innobase/trx/trx0purge.c	2011-01-10 15:48:38 +0000
@@ -76,6 +76,8 @@ trx_undo_rec_t*
 trx_purge_fetch_next_rec(
 /*=====================*/
 	roll_ptr_t*	roll_ptr,	/*!< out: roll pointer to undo record */
+	ulint*		n_pages_handled,/*!< in/out: number of UNDO log pages
+					handled */
 	mem_heap_t*	heap);		/*!< in: memory heap where copied */
 
 /*****************************************************************//**
@@ -575,7 +577,9 @@ static
 void
 trx_purge_rseg_get_next_history_log(
 /*================================*/
-	trx_rseg_t*	rseg)	/*!< in: rollback segment */
+	trx_rseg_t*	rseg,		/*!< in: rollback segment */
+	ulint*		n_pages_handled)/*!< in/out: number of UNDO pages
+					handled */
 {
 	void*		ptr;
 	page_t*		undo_page;
@@ -606,10 +610,11 @@ trx_purge_rseg_get_next_history_log(
 
 	/* Increase the purge page count by one for every handled log */
 
-	purge_sys->n_pages_handled++;
+	(*n_pages_handled)++;
 
 	prev_log_addr = trx_purge_get_log_from_hist(
 		flst_get_prev_addr(log_hdr + TRX_UNDO_HISTORY_NODE, &mtr));
+
 	if (prev_log_addr.page == FIL_NULL) {
 		/* No logs left in the history list */
 
@@ -721,7 +726,8 @@ trx_purge_get_rseg_with_min_trx_id(
 
 		ut_a(rseg->last_page_no != FIL_NULL);
 
-		/* We assume in purge of externally stored fields that space id == 0 */
+		/* We assume in purge of externally stored fields that
+		space id == 0 */
 		ut_a(rseg->space == 0);
 
 		zip_size = rseg->zip_size;
@@ -746,12 +752,15 @@ static
 void
 trx_purge_read_undo_rec(
 /*====================*/
-	trx_purge_t*	purge_sys,		/*!< in, out: purge instance */
+	trx_purge_t*	purge_sys,		/*!< in/out: purge instance */
 	ulint		zip_size)		/*!< in: block size or 0 */
 {
+	ulint		offset;
 	ulint		page_no;
-	ulint		offset = 0;
-	ib_uint64_t	undo_no = 0;
+	ib_uint64_t	undo_no;
+
+	purge_sys->hdr_offset = purge_sys->rseg->last_offset;
+	page_no = purge_sys->hdr_page_no = purge_sys->rseg->last_page_no;
 
 	if (purge_sys->rseg->last_del_marks) {
 		mtr_t		mtr;
@@ -760,22 +769,23 @@ trx_purge_read_undo_rec(
 		mtr_start(&mtr);
 
 		undo_rec = trx_undo_get_first_rec(
-			0 /* System space id */,
-			zip_size, purge_sys->hdr_page_no,
-			purge_sys->hdr_offset, RW_S_LATCH,
-			&mtr);
+			0 /* System space id */, zip_size,
+			purge_sys->hdr_page_no,
+			purge_sys->hdr_offset, RW_S_LATCH, &mtr);
 
 		if (undo_rec != NULL) {
 			offset = page_offset(undo_rec);
 			undo_no = trx_undo_rec_get_undo_no(undo_rec);
-		       page_no = page_get_page_no(page_align(undo_rec));
+			page_no = page_get_page_no(page_align(undo_rec));
 		} else {
-			page_no = purge_sys->hdr_page_no;
+			offset = 0;
+			undo_no = 0;
 		}
 
 		mtr_commit(&mtr);
 	} else {
-		page_no = purge_sys->hdr_page_no;
+		offset = 0;
+		undo_no = 0;
 	}
 
 	purge_sys->offset = offset;
@@ -814,7 +824,9 @@ static
 trx_undo_rec_t*
 trx_purge_get_next_rec(
 /*===================*/
-	mem_heap_t*	heap)	/*!< in: memory heap where copied */
+	ulint*		n_pages_handled,/*!< in/out: number of UNDO pages
+					handled */
+	mem_heap_t*	heap)		/*!< in: memory heap where copied */
 {
 	trx_undo_rec_t*	rec;
 	trx_undo_rec_t*	rec_copy;
@@ -839,7 +851,8 @@ trx_purge_get_next_rec(
 		/* It is the dummy undo log record, which means that there is
 		no need to purge this undo log */
 
-		trx_purge_rseg_get_next_history_log(purge_sys->rseg);
+		trx_purge_rseg_get_next_history_log(
+			purge_sys->rseg, n_pages_handled);
 
 		/* Look for the next undo log and record to purge */
 
@@ -850,8 +863,8 @@ trx_purge_get_next_rec(
 
 	mtr_start(&mtr);
 
-	undo_page = trx_undo_page_get_s_latched(space, zip_size,
-						page_no, &mtr);
+	undo_page = trx_undo_page_get_s_latched(space, zip_size, page_no, &mtr);
+
 	rec = undo_page + offset;
 
 	rec2 = rec;
@@ -898,7 +911,8 @@ trx_purge_get_next_rec(
 	if (rec2 == NULL) {
 		mtr_commit(&mtr);
 
-		trx_purge_rseg_get_next_history_log(purge_sys->rseg);
+		trx_purge_rseg_get_next_history_log(
+			purge_sys->rseg, n_pages_handled);
 
 		/* Look for the next undo log and record to purge */
 
@@ -919,7 +933,7 @@ trx_purge_get_next_rec(
 
 		if (undo_page != page) {
 			/* We advance to a new page of the undo log: */
-			purge_sys->n_pages_handled++;
+			(*n_pages_handled)++;
 		}
 	}
 
@@ -940,6 +954,8 @@ trx_undo_rec_t*
 trx_purge_fetch_next_rec(
 /*=====================*/
 	roll_ptr_t*	roll_ptr,	/*!< out: roll pointer to undo record */
+	ulint*		n_pages_handled,/*!< in/out: number of UNDO log pages
+					handled */
 	mem_heap_t*	heap)		/*!< in: memory heap where copied */
 {
 	const read_view_t*	view = purge_sys->view;
@@ -955,8 +971,7 @@ trx_purge_fetch_next_rec(
 			if (srv_print_thread_releases) {
 				fprintf(stderr,
 					"Purge: No logs left in the"
-					" history list; pages handled %lu\n",
-					(ulong) purge_sys->n_pages_handled);
+					" history list\n");
 			}
 
 			return(NULL);
@@ -980,29 +995,31 @@ trx_purge_fetch_next_rec(
 	/* The following call will advance the stored values of the
 	purge iterator. */
 
-	return(trx_purge_get_next_rec(heap));
+	return(trx_purge_get_next_rec(n_pages_handled, heap));
 }
 
 /***********************************************************//**
-Fetches an undo log record into the purge nodes in the query graph. */
+Fetches an undo log record into the purge nodes in the query graph.
+@return the number of pages processed. */
 static
-void
+ulint
 trx_purge_attach_undo_recs(
 /*=======================*/
-	ulint		n_purge_threads,/*!< number of purge threads */
-	trx_purge_t*	purge_sys,	/*!< purge instance */
-	purge_iter_t*	limit,		/*!< records read up to */
-	ulint		batch_size)	/*!< no. of records to purge */
+	ulint		n_purge_threads,/*!< in: number of purge threads */
+	trx_purge_t*	purge_sys,	/*!< in/out: purge instance */
+	purge_iter_t*	limit,		/*!< out: records read up to */
+	ulint		batch_size)	/*!< in: no. of pages to purge */
 {
 	que_thr_t*	thr;
 	ulint		i = 0;
+	ulint		n_pages_handled = 0;
 	ulint		n_thrs = UT_LIST_GET_LEN(purge_sys->query->thrs);
 
 	ut_ad(mutex_own(&purge_sys->mutex));
 
 	*limit = purge_sys->iter;
 
-	/* Debug code to validate some pre-requisites. */
+	/* Debug code to validate some pre-requisites and reset done flag. */
 	for (thr = UT_LIST_GET_FIRST(purge_sys->query->thrs);
 	     thr != NULL;
 	     thr = UT_LIST_GET_NEXT(thrs, thr), ++i) {
@@ -1031,7 +1048,6 @@ trx_purge_attach_undo_recs(
 	do {
 		purge_node_t*		node;
 		trx_purge_rec_t*	purge_rec;
-		const purge_iter_t	iter = purge_sys->iter;
 
 		ut_a(!thr->is_active);
 
@@ -1044,16 +1060,16 @@ trx_purge_attach_undo_recs(
 		/* Track the max {trx_id, undo_no} for truncating the
 		UNDO logs once we have purged the records. */
 
-		if (iter.trx_no > limit->trx_no
-		    || (iter.trx_no == limit->trx_no
-			&& iter.undo_no >= limit->undo_no)) {
+		if (purge_sys->iter.trx_no > limit->trx_no
+		    || (purge_sys->iter.trx_no == limit->trx_no
+			&& purge_sys->iter.undo_no >= limit->undo_no)) {
 
-			*limit = iter;
+			*limit = purge_sys->iter;
 		}
 
 		/* Fetch the next record, and advance the purge_sys->iter. */
 		purge_rec->undo_rec = trx_purge_fetch_next_rec(
-			&purge_rec->roll_ptr, node->heap);
+			&purge_rec->roll_ptr, &n_pages_handled, node->heap);
 
 		if (purge_rec->undo_rec != NULL) {
 
@@ -1066,7 +1082,8 @@ trx_purge_attach_undo_recs(
 
 			ib_vector_push(node->undo_recs, purge_rec);
 
-			if (ib_vector_size(node->undo_recs) >= batch_size) {
+			if (n_pages_handled >= batch_size) {
+
 				break;
 			}
 		} else {
@@ -1081,6 +1098,8 @@ trx_purge_attach_undo_recs(
 	} while (thr);
 
 	ut_ad(trx_purge_check_limit());
+
+	return(n_pages_handled);
 }
 
 /*******************************************************************//**
@@ -1209,7 +1228,7 @@ trx_purge(
 					to purge in one batch */
 {
 	que_thr_t*	thr = NULL;
-	ulint		n_pages_handled_start;
+	ulint		n_pages_handled;
 
 	srv_dml_needed_delay = trx_purge_dml_delay();
 
@@ -1230,10 +1249,8 @@ trx_purge(
 
 	rw_lock_x_unlock(&purge_sys->latch);
 
-	n_pages_handled_start = purge_sys->n_pages_handled;
-
 	/* Fetch the UNDO recs that need to be purged. */
-	trx_purge_attach_undo_recs(
+	n_pages_handled = trx_purge_attach_undo_recs(
 		n_purge_threads, purge_sys, &purge_sys->limit, batch_size);
 
 	mutex_exit(&purge_sys->mutex);
@@ -1276,13 +1293,6 @@ run_synchronously:
 		os_atomic_inc_ulint(
 			&purge_sys->mutex, &purge_sys->n_completed, 1);
 
-		if (srv_print_thread_releases) {
-
-			fprintf(stderr,
-				"Purge ends; pages handled %lu\n",
-				(ulong) purge_sys->n_pages_handled);
-		}
-
 		if (n_purge_threads > 1) {
 			trx_purge_wait_for_workers_to_complete(purge_sys);
 		}
@@ -1290,7 +1300,7 @@ run_synchronously:
 
 	/* When shutdown is triggered it is possible for the worker threads
         to have already exited via os_event_wait(). We only truncate the UNDO
-	log when we are 100% sure that all submitted tasks have been completed. */
+	log when we are 100% sure that all submitted tasks have completed. */
 
 	mutex_enter(&purge_sys->mutex);
 
@@ -1300,7 +1310,12 @@ run_synchronously:
 
 	mutex_exit(&purge_sys->mutex);
 
-	return(purge_sys->n_pages_handled - n_pages_handled_start);
+	if (srv_print_thread_releases) {
+		fprintf(stderr, "Purge ends; pages handled %lu\n",
+			n_pages_handled);
+	}
+
+	return(n_pages_handled);
 }
 
 /******************************************************************//**


Attachment: [text/bzr-bundle] bzr/joerg@mysql.com-20110110154838-t6ws6uczxf4ysa55.bundle
Thread
bzr commit into mysql-trunk branch (joerg:3447) Joerg Bruehe10 Jan