#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 Bruehe | 10 Jan |
