MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Inaam Rana Date:February 10 2011 3:03pm
Subject:bzr commit into mysql-trunk-innodb branch (inaam.rana:3495) Bug#59214
View as plain text  
#At file:///home/inaam/w/ibuf/ based on revid:marko.makela@stripped

 3495 Inaam Rana	2011-02-10
      Bug #59214 :increase rate limit for insert buffer prefetch reads
      
      rb://593
      approved by: Marko
      
      1) Make ibuf size configurable. Current behavior is to have a maximum
      on-disk size equal to half the buffer pool size. This patch introduces
      a new parameter:
      
      innodb_change_buffer_max_size = default 25, min 0, max 50. Value means
      %age of buffer pool size. Dynamic.
      
      2) Make ibuf merge more aggressive:
      * upto ibuf::size < ibuf::max_size / 2, merge at PCT_IO(5) same as
        current. And should be good for most of the workloads.
      * when ibuf::size > ibuf::max_size / 2, merge at PCT_IO(5) +
        PCT_IO(excess difference in % of max_size)

    added:
      mysql-test/suite/sys_vars/r/innodb_change_buffer_max_size_basic.result
      mysql-test/suite/sys_vars/t/innodb_change_buffer_max_size_basic.test
    modified:
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/ibuf/ibuf0ibuf.c
      storage/innobase/include/ibuf0ibuf.h
      storage/innobase/include/ibuf0ibuf.ic
      storage/innobase/include/srv0srv.h
      storage/innobase/srv/srv0srv.c
=== added file 'mysql-test/suite/sys_vars/r/innodb_change_buffer_max_size_basic.result'
--- a/mysql-test/suite/sys_vars/r/innodb_change_buffer_max_size_basic.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/sys_vars/r/innodb_change_buffer_max_size_basic.result	revid:inaam.rana@stripped
@@ -0,0 +1,77 @@
+SET @start_global_value = @@global.innodb_change_buffer_max_size;
+SELECT @start_global_value;
+@start_global_value
+25
+Valid values are between 0 and 50
+select @@global.innodb_change_buffer_max_size between 0 and 50;
+@@global.innodb_change_buffer_max_size between 0 and 50
+1
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+25
+select @@session.innodb_change_buffer_max_size;
+ERROR HY000: Variable 'innodb_change_buffer_max_size' is a GLOBAL variable
+show global variables like 'innodb_change_buffer_max_size';
+Variable_name	Value
+innodb_change_buffer_max_size	25
+show session variables like 'innodb_change_buffer_max_size';
+Variable_name	Value
+innodb_change_buffer_max_size	25
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	25
+select * from information_schema.session_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	25
+set global innodb_change_buffer_max_size=10;
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+10
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	10
+select * from information_schema.session_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	10
+set session innodb_change_buffer_max_size=1;
+ERROR HY000: Variable 'innodb_change_buffer_max_size' is a GLOBAL variable and should be set with SET GLOBAL
+set global innodb_change_buffer_max_size=1.1;
+ERROR 42000: Incorrect argument type to variable 'innodb_change_buffer_max_size'
+set global innodb_change_buffer_max_size=1e1;
+ERROR 42000: Incorrect argument type to variable 'innodb_change_buffer_max_size'
+set global innodb_change_buffer_max_size="foo";
+ERROR 42000: Incorrect argument type to variable 'innodb_change_buffer_max_size'
+set global innodb_change_buffer_max_size=-7;
+Warnings:
+Warning	1292	Truncated incorrect innodb_change_buffer_max_size value: '-7'
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+0
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	0
+set global innodb_change_buffer_max_size=56;
+Warnings:
+Warning	1292	Truncated incorrect innodb_change_buffer_max_size value: '56'
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+50
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_CHANGE_BUFFER_MAX_SIZE	50
+set global innodb_change_buffer_max_size=0;
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+0
+set global innodb_change_buffer_max_size=50;
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+50
+set global innodb_change_buffer_max_size=DEFAULT;
+select @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+25
+SET @@global.innodb_change_buffer_max_size = @start_global_value;
+SELECT @@global.innodb_change_buffer_max_size;
+@@global.innodb_change_buffer_max_size
+25

=== added file 'mysql-test/suite/sys_vars/t/innodb_change_buffer_max_size_basic.test'
--- a/mysql-test/suite/sys_vars/t/innodb_change_buffer_max_size_basic.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/sys_vars/t/innodb_change_buffer_max_size_basic.test	revid:inaam.rana@stripped
@@ -0,0 +1,63 @@
+
+
+# 2011-02-09 - Added
+#
+
+--source include/have_innodb.inc
+
+SET @start_global_value = @@global.innodb_change_buffer_max_size;
+SELECT @start_global_value;
+
+#
+# exists as global only
+#
+--echo Valid values are between 0 and 50
+select @@global.innodb_change_buffer_max_size between 0 and 50;
+select @@global.innodb_change_buffer_max_size;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.innodb_change_buffer_max_size;
+show global variables like 'innodb_change_buffer_max_size';
+show session variables like 'innodb_change_buffer_max_size';
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+select * from information_schema.session_variables where variable_name='innodb_change_buffer_max_size';
+
+#
+# show that it's writable
+#
+set global innodb_change_buffer_max_size=10;
+select @@global.innodb_change_buffer_max_size;
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+select * from information_schema.session_variables where variable_name='innodb_change_buffer_max_size';
+--error ER_GLOBAL_VARIABLE
+set session innodb_change_buffer_max_size=1;
+
+#
+# incorrect types
+#
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_change_buffer_max_size=1.1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_change_buffer_max_size=1e1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_change_buffer_max_size="foo";
+
+set global innodb_change_buffer_max_size=-7;
+select @@global.innodb_change_buffer_max_size;
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+set global innodb_change_buffer_max_size=56;
+select @@global.innodb_change_buffer_max_size;
+select * from information_schema.global_variables where variable_name='innodb_change_buffer_max_size';
+
+#
+# min/max/DEFAULT values
+#
+set global innodb_change_buffer_max_size=0;
+select @@global.innodb_change_buffer_max_size;
+set global innodb_change_buffer_max_size=50;
+select @@global.innodb_change_buffer_max_size;
+set global innodb_change_buffer_max_size=DEFAULT;
+select @@global.innodb_change_buffer_max_size;
+
+
+SET @@global.innodb_change_buffer_max_size = @start_global_value;
+SELECT @@global.innodb_change_buffer_max_size;

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	revid:marko.makela@stripped
+++ b/storage/innobase/handler/ha_innodb.cc	revid:inaam.rana@stripped
@@ -135,6 +135,10 @@ static long long innobase_buffer_pool_si
 Connected to buf_LRU_old_ratio. */
 static uint innobase_old_blocks_pct;
 
+/** Maximum on-disk size of change buffer in terms of percentage
+of the buffer pool. */
+static uint innobase_change_buffer_max_size = CHANGE_BUFFER_DEFAULT_SIZE;
+
 /* The default values for the following char* start-up parameters
 are determined in innobase_init below: */
 
@@ -2702,6 +2706,8 @@ innobase_change_buffering_inited_ok:
 	innobase_old_blocks_pct = buf_LRU_old_ratio_update(
 		innobase_old_blocks_pct, TRUE);
 
+	ibuf_max_size_update(innobase_change_buffer_max_size);
+
 	innobase_open_tables = hash_create(200);
 	mysql_mutex_init(innobase_share_mutex_key,
 			 &innobase_share_mutex,
@@ -11305,6 +11311,26 @@ innodb_old_blocks_pct_update(
 		*static_cast<const uint*>(save), TRUE);
 }
 
+/****************************************************************//**
+Update the system variable innodb_old_blocks_pct using the "saved"
+value. This function is registered as a callback with MySQL. */
+static
+void
+innodb_change_buffer_max_size_update(
+/*=================================*/
+	THD*				thd,	/*!< in: thread handle */
+	struct st_mysql_sys_var*	var,	/*!< in: pointer to
+						system variable */
+	void*				var_ptr,/*!< out: where the
+						formal string goes */
+	const void*			save)	/*!< in: immediate result
+						from check function */
+{
+	innobase_change_buffer_max_size =
+			(*static_cast<const uint*>(save));
+	ibuf_max_size_update(innobase_change_buffer_max_size);
+}
+
 /*************************************************************//**
 Find the corresponding ibuf_use_t value that indexes into
 innobase_change_buffering_values[] array for the input
@@ -12275,6 +12301,14 @@ static MYSQL_SYSVAR_STR(change_buffering
   innodb_change_buffering_validate,
   innodb_change_buffering_update, "all");
 
+static MYSQL_SYSVAR_UINT(change_buffer_max_size,
+  innobase_change_buffer_max_size,
+  PLUGIN_VAR_RQCMDARG,
+  "Maximum on-disk size of change buffer in terms of percentage"
+  " of the buffer pool.",
+  NULL, innodb_change_buffer_max_size_update,
+  CHANGE_BUFFER_DEFAULT_SIZE, 0, 50, 0);
+
 static MYSQL_SYSVAR_ENUM(stats_method, srv_innodb_stats_method,
    PLUGIN_VAR_RQCMDARG,
   "Specifies how InnoDB index statistics collection code should "
@@ -12385,6 +12419,7 @@ static struct st_mysql_sys_var* innobase
   MYSQL_SYSVAR(use_sys_malloc),
   MYSQL_SYSVAR(use_native_aio),
   MYSQL_SYSVAR(change_buffering),
+  MYSQL_SYSVAR(change_buffer_max_size),
 #if defined UNIV_DEBUG || defined UNIV_IBUF_DEBUG
   MYSQL_SYSVAR(change_buffering_debug),
 #endif /* UNIV_DEBUG || UNIV_IBUF_DEBUG */

=== modified file 'storage/innobase/ibuf/ibuf0ibuf.c'
--- a/storage/innobase/ibuf/ibuf0ibuf.c	revid:marko.makela@stripped
+++ b/storage/innobase/ibuf/ibuf0ibuf.c	revid:inaam.rana@stripped
@@ -184,9 +184,6 @@ level 2 i/o. However, if an OS thread do
 it uses synchronous aio, it can access any pages, as long as it obeys the
 access order rules. */
 
-/** Buffer pool size per the maximum insert buffer size */
-#define IBUF_POOL_SIZE_PER_MAX_SIZE	2
-
 /** Table name for the insert buffer. */
 #define IBUF_TABLE_NAME		"SYS_IBUF_TABLE"
 
@@ -520,12 +517,13 @@ ibuf_init_at_db_start(void)
 
 	memset(ibuf, 0, sizeof(*ibuf));
 
-	/* Note that also a pessimistic delete can sometimes make a B-tree
-	grow in size, as the references on the upper levels of the tree can
-	change */
-
-	ibuf->max_size = buf_pool_get_curr_size() / UNIV_PAGE_SIZE
-		/ IBUF_POOL_SIZE_PER_MAX_SIZE;
+	/* At startup we intialize ibuf to have a maximum of
+	CHANGE_BUFFER_DEFAULT_SIZE in terms of percentage of the
+	buffer pool size. Once ibuf struct is initialized this
+	value is updated with the user supplied size by calling
+	ibuf_max_size_update(). */
+	ibuf->max_size = ((buf_pool_get_curr_size() / UNIV_PAGE_SIZE)
+			  * CHANGE_BUFFER_DEFAULT_SIZE) / 100;
 
 	mutex_create(ibuf_pessimistic_insert_mutex_key,
 		     &ibuf_pessimistic_insert_mutex,
@@ -598,6 +596,26 @@ ibuf_init_at_db_start(void)
 
 	ibuf->index = dict_table_get_first_index(table);
 }
+
+/*********************************************************************//**
+Updates the max_size value for ibuf. */
+UNIV_INTERN
+void
+ibuf_max_size_update(
+/*=================*/
+	ulint	new_val)	/*!< in: new value in terms of
+				percentage of the buffer pool size */
+{
+	ulint	new_size = ((buf_pool_get_curr_size() / UNIV_PAGE_SIZE)
+			    * new_val) / 100;
+	ibuf_enter();
+	mutex_enter(&ibuf_mutex);
+	ibuf->max_size = new_size;
+	mutex_exit(&ibuf_mutex);
+	ibuf_exit();
+}
+
+
 #endif /* !UNIV_HOTBACKUP */
 /*********************************************************************//**
 Initializes an ibuf bitmap page. */
@@ -2660,22 +2678,45 @@ will be merged from ibuf trees to the pa
 empty */
 UNIV_INTERN
 ulint
-ibuf_contract_for_n_pages(
-/*======================*/
-	ibool	sync,	/*!< in: TRUE if the caller wants to wait for the
-			issued read with the highest tablespace address
-			to complete */
-	ulint	n_pages)/*!< in: try to read at least this many pages to
-			the buffer pool and merge the ibuf contents to
-			them */
+ibuf_contract_in_background(
+/*========================*/
+	ibool	full)	/*!< in: TRUE if the caller wants to do a full
+			contract based on PCT_IO(100). If FALSE then
+			the size of contract batch is determined based
+			on the current size of the ibuf tree. */
 {
 	ulint	sum_bytes	= 0;
 	ulint	sum_pages	= 0;
 	ulint	n_bytes;
 	ulint	n_pag2;
+	ulint	n_pages;
+
+	if (full) {
+		/* Caller has requested a full batch */
+		n_pages = PCT_IO(100);
+	} else {
+
+		ibuf_enter();
+		mutex_enter(&ibuf_mutex);
+
+		/* By default we do a batch of 5% of the io_capacity */
+		n_pages = PCT_IO(5);
+
+		/* If the ibuf->size is more than half the max_size
+		then we make more agreesive contraction.
+		+1 is to avoid division by zero. */
+		if (ibuf->size > ibuf->max_size / 2) {
+			ulint diff = ibuf->size - ibuf->max_size / 2;
+			n_pages += PCT_IO((diff * 100)
+					   / (ibuf->max_size + 1));
+		}
+
+		mutex_exit(&ibuf_mutex);
+		ibuf_exit();
+	}
 
 	while (sum_pages < n_pages) {
-		n_bytes = ibuf_contract_ext(&n_pag2, sync);
+		n_bytes = ibuf_contract_ext(&n_pag2, FALSE);
 
 		if (n_bytes == 0) {
 			return(sum_bytes);
@@ -3395,12 +3436,14 @@ ibuf_insert_low(
 	do_merge = FALSE;
 
 	/* Perform dirty reads of ibuf->size and ibuf->max_size, to
-	reduce ibuf_mutex contention. ibuf->max_size remains constant
-	after ibuf_init_at_db_start(), but ibuf->size should be
-	protected by ibuf_mutex. Given that ibuf->size fits in a
-	machine word, this should be OK; at worst we are doing some
-	excessive ibuf_contract() or occasionally skipping a
-	ibuf_contract(). */
+	reduce ibuf_mutex contention. Given that ibuf->max_size and
+	ibuf->size fit in a machine word, this should be OK; at worst
+	we are doing some excessive ibuf_contract() or occasionally
+	skipping an ibuf_contract(). */
+	if (ibuf->max_size == 0) {
+		return(DB_STRONG_FAIL);
+	}
+
 	if (ibuf->size >= ibuf->max_size + IBUF_CONTRACT_DO_NOT_INSERT) {
 		/* Insert buffer is now too big, contract it but do not try
 		to insert */

=== modified file 'storage/innobase/include/ibuf0ibuf.h'
--- a/storage/innobase/include/ibuf0ibuf.h	revid:marko.makela@stripped
+++ b/storage/innobase/include/ibuf0ibuf.h	revid:inaam.rana@stripped
@@ -35,6 +35,10 @@ Created 7/19/1997 Heikki Tuuri
 #ifndef UNIV_HOTBACKUP
 # include "ibuf0types.h"
 
+/** Default value for maximum on-disk size of change buffer in terms
+of percentage of the buffer pool. */
+#define CHANGE_BUFFER_DEFAULT_SIZE	(25)
+
 /* Possible operations buffered in the insert/whatever buffer. See
 ibuf_insert(). DO NOT CHANGE THE VALUES OF THESE, THEY ARE STORED ON DISK. */
 typedef enum {
@@ -98,6 +102,14 @@ void
 ibuf_init_at_db_start(void);
 /*=======================*/
 /*********************************************************************//**
+Updates the max_size value for ibuf. */
+UNIV_INTERN
+void
+ibuf_max_size_update(
+/*=================*/
+	ulint	new_val);	/*!< in: new value in terms of
+				percentage of the buffer pool size */
+/*********************************************************************//**
 Reads the biggest tablespace id from the high end of the insert buffer
 tree and updates the counter in fil_system. */
 UNIV_INTERN
@@ -358,14 +370,12 @@ will be merged from ibuf trees to the pa
 empty */
 UNIV_INTERN
 ulint
-ibuf_contract_for_n_pages(
-/*======================*/
-	ibool	sync,	/*!< in: TRUE if the caller wants to wait for the
-			issued read with the highest tablespace address
-			to complete */
-	ulint	n_pages);/*!< in: try to read at least this many pages to
-			the buffer pool and merge the ibuf contents to
-			them */
+ibuf_contract_in_background(
+/*========================*/
+	ibool	full);	/*!< in: TRUE if the caller wants to do a full
+			contract based on PCT_IO(100). If FALSE then
+			the size of contract batch is determined based
+			on the current size of the ibuf tree. */
 #endif /* !UNIV_HOTBACKUP */
 /*********************************************************************//**
 Parses a redo log record of an ibuf bitmap page init.

=== modified file 'storage/innobase/include/ibuf0ibuf.ic'
--- a/storage/innobase/include/ibuf0ibuf.ic	revid:marko.makela@stripped
+++ b/storage/innobase/include/ibuf0ibuf.ic	revid:inaam.rana@stripped
@@ -104,6 +104,7 @@ ibuf_should_try(
 						decide */
 {
 	if (ibuf_use != IBUF_USE_NONE
+	    && ibuf->max_size != 0
 	    && !dict_index_is_clust(index)
 	    && (ignore_sec_unique || !dict_index_is_unique(index))) {
 

=== modified file 'storage/innobase/include/srv0srv.h'
--- a/storage/innobase/include/srv0srv.h	revid:marko.makela@stripped
+++ b/storage/innobase/include/srv0srv.h	revid:inaam.rana@stripped
@@ -172,7 +172,7 @@ extern ulong    srv_io_capacity;
 /* Returns the number of IO operations that is X percent of the
 capacity. PCT_IO(5) -> returns the number of IO operations that
 is 5% of the max where max is srv_io_capacity.  */
-#define PCT_IO(p) ((ulong) (srv_io_capacity * ((double) p / 100.0)))
+#define PCT_IO(p) ((ulong) (srv_io_capacity * ((double) (p) / 100.0)))
 
 /* The "innodb_stats_method" setting, decides how InnoDB is going
 to treat NULL value when collecting statistics. It is not defined

=== modified file 'storage/innobase/srv/srv0srv.c'
--- a/storage/innobase/srv/srv0srv.c	revid:marko.makela@stripped
+++ b/storage/innobase/srv/srv0srv.c	revid:inaam.rana@stripped
@@ -2306,7 +2306,7 @@ srv_master_do_active_tasks(void)
 
 	/* Do an ibuf merge */
 	srv_main_thread_op_info = "doing insert buffer merge";
-	ibuf_contract_for_n_pages(FALSE, PCT_IO(5));
+	ibuf_contract_in_background(FALSE);
 
 	/* Flush logs if needed */
 	srv_main_thread_op_info = "flushing log";
@@ -2384,7 +2384,7 @@ srv_master_do_idle_tasks(void)
 
 	/* Do an ibuf merge */
 	srv_main_thread_op_info = "doing insert buffer merge";
-	ibuf_contract_for_n_pages(FALSE, PCT_IO(100));
+	ibuf_contract_in_background(TRUE);
 
 	if (srv_shutdown_state > 0) {
 		return;
@@ -2456,7 +2456,7 @@ srv_master_do_shutdown_tasks(
 
 	/* Do an ibuf merge */
 	srv_main_thread_op_info = "doing insert buffer merge";
-	n_bytes_merged = ibuf_contract_for_n_pages(FALSE, PCT_IO(100));
+	n_bytes_merged = ibuf_contract_in_background(TRUE);
 
 	/* Flush logs if needed */
 	srv_sync_log_buffer_in_background();


Attachment: [text/bzr-bundle] bzr/inaam.rana@oracle.com-20110210150101-k8edmdekx2y5nj1k.bundle
Thread
bzr commit into mysql-trunk-innodb branch (inaam.rana:3495) Bug#59214Inaam Rana10 Feb