From b3c9cc6f212c650a18ca191a8889e2af68ffc9d6 Mon Sep 17 00:00:00 2001 From: Sunny Bains Date: Tue, 22 Feb 2011 16:04:08 +1100 Subject: Bug #11766227: InnoDB purge lag much worse for 5.5.8 versus 5.1 Bug #11766501: Multiple RBS break the get rseg with mininum trx_t::no code during purge Bug# 59291 changes: Main problem is that truncating the UNDO log at the completion of every trx_purge() call is expensive as the number of rollback segments is increased. We truncate after a configurable amount of pages. The innodb_purge_batch_size parameter is used to control when InnoDB does the actual truncate. The truncate is done once after 128 (or TRX_SYS_N_RSEGS iterations). In other words we truncate after purge 128 * innodb_purge_batch_size. The smaller the batch size the quicker we truncate. Introduce a new parameter that allows how many rollback segments to use for storing REDO information. This is really step 1 in allowing complete control to the user over rollback space management. New parameters: i) innodb_rollback_segments = number of rollback_segments to use (default is now 128) dynamic parameter, can be changed anytime. Currently there is little benefit in changing it from the default. Optimisations in the patch. i. Change the O(n) behaviour of trx_rseg_get_on_id() to O(log n) Backported from 5.6. Refactor some of the binary heap code. Create a new include/ut0bh.ic file. ii. Avoid truncating the rollback segments after every purge. Related changes that were moved to a separate patch: i. Purge should not do any flushing, only wait for space to be free so that it only does purging of records unless it is held up by a long running transaction that is preventing it from progressing. ii. Give the purge thread preference over transactions when acquiring the rseg->mutex during commit. This to avoid purge blocking unnecessarily when getting the next rollback segment to purge. Bug #11766501 changes: Add the rseg to the min binary heap under the cover of the kernel mutex and the binary heap mutex. This ensures the ordering of the min binary heap. The two changes have to be committed together because they share the same that fixes both issues. rb://567 Approved by: Inaam Rana. --- storage/innobase/srv/srv0srv.c | 22 +++++++++++++++++----- 1 file changed, 17 insertions(+), 5 deletions(-) (limited to 'storage/innobase/srv/srv0srv.c') diff --git a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c index 826145005a4..3c5eed448bd 100644 --- a/storage/innobase/srv/srv0srv.c +++ b/storage/innobase/srv/srv0srv.c @@ -1,6 +1,6 @@ /***************************************************************************** -Copyright (c) 1995, 2010, Innobase Oy. All Rights Reserved. +Copyright (c) 1995, 2011, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, 2009 Google Inc. Copyright (c) 2009, Percona Inc. @@ -269,9 +269,12 @@ UNIV_INTERN ulong srv_max_buf_pool_modified_pct = 75; /* 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 pages to purge in one batch */ UNIV_INTERN ulong srv_purge_batch_size = 20; +/* the number of rollback segments to use */ +UNIV_INTERN ulong srv_rollback_segments = TRX_SYS_N_RSEGS; + /* variable counts amount of data read in total (in bytes) */ UNIV_INTERN ulint srv_data_read = 0; @@ -3096,6 +3099,7 @@ srv_purge_thread( required by os_thread_create */ { srv_slot_t* slot; + ulint retries = 0; ulint slot_no = ULINT_UNDEFINED; ulint n_total_purged = ULINT_UNDEFINED; @@ -3122,7 +3126,7 @@ srv_purge_thread( while (srv_shutdown_state != SRV_SHUTDOWN_EXIT_THREADS) { - ulint n_pages_purged; + ulint n_pages_purged = 0; /* If there are very few records to purge or the last purge didn't purge any records then wait for activity. @@ -3130,7 +3134,8 @@ srv_purge_thread( because in the worst case we will end up waiting for the next purge event. */ if (trx_sys->rseg_history_len < srv_purge_batch_size - || n_total_purged == 0) { + || (n_total_purged == 0 + && retries >= TRX_SYS_N_RSEGS)) { os_event_t event; @@ -3141,6 +3146,8 @@ srv_purge_thread( mutex_exit(&kernel_mutex); os_event_wait(event); + + retries = 0; } /* Check for shutdown and whether we should do purge at all. */ @@ -3151,7 +3158,12 @@ srv_purge_thread( break; } - n_total_purged = 0; + if (n_total_purged == 0 && retries <= TRX_SYS_N_RSEGS) { + ++retries; + } else if (n_total_purged > 0) { + retries = 0; + n_total_purged = 0; + } /* Purge until there are no more records to purge and there is no change in configuration or server state. */ -- cgit v1.2.1