List:Commits« Previous MessageNext Message »
From:Satya B Date:April 24 2009 11:03am
Subject:bzr commit into mysql-5.0-bugteam branch (satya.bn:2729) Bug#43660
View as plain text  
#At file:///home/satya/WORK/mysql/mysql-5.0-bugteam-innodb/ based on revid:joro@stripped

 2729 Satya B	2009-04-24
      Fix for BUG#43660- SHOW INDEXES/ANALYZE does NOT update cardinality 
                         for indexes of InnoDB table
      
      Fixes by replacing the PRNG that is used to pick random pages with a 
      better one. 
      
      Also adds a configuration option "innodb_use_legacy_cardinality_algorithm"
      to enable the fix only when the option is set.
      
      This patch is from http://bugs.mysql.com/file.php?id=11789
      modified:
        innobase/include/srv0srv.h
        innobase/page/page0cur.c
        innobase/srv/srv0srv.c
        sql/ha_innodb.h
        sql/mysqld.cc
        sql/set_var.cc

=== modified file 'innobase/include/srv0srv.h'
--- a/innobase/include/srv0srv.h	2008-07-31 21:47:57 +0000
+++ b/innobase/include/srv0srv.h	2009-04-24 11:03:50 +0000
@@ -253,6 +253,12 @@ extern ulint srv_read_ahead_seq;
 /* variable to count the number of random read-aheads were done */
 extern ulint srv_read_ahead_rnd;
 
+/* An option to enable the fix for "Bug#43660 SHOW INDEXES/ANALYZE does
+NOT update cardinality for indexes of InnoDB table". By default we are
+running with the fix disabled because MySQL 5.1 is frozen for such
+behavioral changes. */
+extern char srv_use_legacy_cardinality_algorithm;
+
 /* In this structure we store status variables to be passed to MySQL */
 typedef struct export_var_struct export_struc;
 

=== modified file 'innobase/page/page0cur.c'
--- a/innobase/page/page0cur.c	2005-07-05 09:10:20 +0000
+++ b/innobase/page/page0cur.c	2009-04-24 11:03:50 +0000
@@ -15,6 +15,8 @@ Created 10/4/1994 Heikki Tuuri
 #include "mtr0log.h"
 #include "log0recv.h"
 #include "rem0cmp.h"
+#include "srv0srv.h"
+#include "ut0ut.h"
 
 static ulint	page_rnd	= 976722341;
 
@@ -23,6 +25,44 @@ static ulint	page_rnd	= 976722341;
 ulint	page_cur_short_succ	= 0;
 # endif /* UNIV_SEARCH_PERF_STAT */
 
+/***********************************************************************
+This is a linear congruential generator PRNG. Returns a pseudo random
+number between 0 and 2^64-1 inclusive. The formula and the constants
+being used are:
+X[n+1] = (a * X[n] + c) mod m
+where:
+X[0] = ut_usectime()
+a = 1103515245 (3^5 * 5 * 7 * 129749)
+c = 12345 (3 * 5 * 823)
+m = 18446744073709551616 (2^64)
+*/
+#define LCG_a	1103515245
+#define LCG_c	12345
+static
+unsigned long long
+page_cur_lcg_prng()
+/*===============*/
+			/* out: number between 0 and 2^64-1 */
+{
+	static unsigned long long lcg_current = 0;
+	static ibool		initialized = FALSE;
+	ulint			time_sec;
+	ulint			time_ms;
+
+	if (!initialized) {
+		ut_usectime(&time_sec, &time_ms);
+		lcg_current = (unsigned long long) (time_sec * 1000000
+						    + time_ms);
+		initialized = TRUE;
+	}
+
+	/* no need to "% 2^64" explicitly because lcg_current is
+	64 bit and this will be done anyway */
+	lcg_current = LCG_a * lcg_current + LCG_c;
+
+	return(lcg_current);
+}
+
 /********************************************************************
 Tries a search shortcut based on the last insert. */
 UNIV_INLINE
@@ -489,9 +529,13 @@ page_cur_open_on_rnd_user_rec(
 		return;
 	}	
 
-	page_rnd += 87584577;	
+	if (srv_use_legacy_cardinality_algorithm) {
+		page_rnd += 87584577;
 
-	rnd = page_rnd % page_get_n_recs(page);
+		rnd = page_rnd % page_get_n_recs(page);
+	} else {
+		rnd = (ulint) page_cur_lcg_prng() % page_get_n_recs(page);
+	}
 
 	rec = page_get_infimum_rec(page);
 
@@ -1419,3 +1463,30 @@ page_cur_delete_rec(
 		page_dir_balance_slot(page, cur_slot_no);
 	}
 }
+
+#ifdef UNIV_COMPILE_TEST_FUNCS
+
+/***********************************************************************
+Print the first n numbers, generated by page_cur_lcg_prng() to make sure
+(visually) that it works properly. */
+void
+test_page_cur_lcg_prng(
+/*===================*/
+	int	n)	/* in: print first n numbers */
+{
+	int			i;
+	unsigned long long	rnd;
+
+	for (i = 0; i < n; i++) {
+		rnd = page_cur_lcg_prng();
+		printf("%llu\t%%2=%llu %%3=%llu %%5=%llu %%7=%llu %%11=%llu\n",
+		       rnd,
+		       rnd % 2,
+		       rnd % 3,
+		       rnd % 5,
+		       rnd % 7,
+		       rnd % 11);
+	}
+}
+
+#endif /* UNIV_COMPILE_TEST_FUNCS */

=== modified file 'innobase/srv/srv0srv.c'
--- a/innobase/srv/srv0srv.c	2008-12-12 22:48:26 +0000
+++ b/innobase/srv/srv0srv.c	2009-04-24 11:03:50 +0000
@@ -239,6 +239,12 @@ ulint srv_read_ahead_seq = 0;
 /* variable to count the number of random read-aheads */
 ulint srv_read_ahead_rnd = 0;
 
+/* An option to enable the fix for "Bug#43660 SHOW INDEXES/ANALYZE does
+NOT update cardinality for indexes of InnoDB table". By default we are
+running with the fix disabled because MySQL 5.1 is frozen for such
+behavioral changes. */
+char srv_use_legacy_cardinality_algorithm = TRUE;
+
 /* structure to pass status variables to MySQL */
 export_struc export_vars;
 

=== modified file 'sql/ha_innodb.h'
--- a/sql/ha_innodb.h	2007-10-03 05:47:30 +0000
+++ b/sql/ha_innodb.h	2009-04-24 11:03:50 +0000
@@ -234,6 +234,11 @@ extern ulong srv_thread_sleep_delay;
 extern ulong srv_thread_concurrency;
 extern ulong srv_commit_concurrency;
 extern ulong srv_flush_log_at_trx_commit;
+/* An option to enable the fix for "Bug#43660 SHOW INDEXES/ANALYZE does
+NOT update cardinality for indexes of InnoDB table". By default we are
+running with the fix disabled because MySQL 5.1 is frozen for such
+behavioral changes. */
+extern char srv_use_legacy_cardinality_algorithm;
 }
 
 bool innobase_init(void);

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2009-03-19 13:44:58 +0000
+++ b/sql/mysqld.cc	2009-04-24 11:03:50 +0000
@@ -5044,7 +5044,8 @@ enum options_mysqld
   OPT_SECURE_FILE_PRIV,
   OPT_KEEP_FILES_ON_CREATE,
   OPT_INNODB_ADAPTIVE_HASH_INDEX,
-  OPT_FEDERATED
+  OPT_FEDERATED,
+  OPT_INNODB_USE_LEGACY_CARDINALITY_ALGORITHM
 };
 
 
@@ -5351,6 +5352,14 @@ Disable with --skip-innodb-doublewrite."
    (gptr*) &global_system_variables.innodb_table_locks,
    (gptr*) &global_system_variables.innodb_table_locks,
    0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0},
+  {"innodb_use_legacy_cardinality_algorithm", 
+   OPT_INNODB_USE_LEGACY_CARDINALITY_ALGORITHM,
+   "Use legacy algorithm for picking random pages during index cardinality "
+   "estimation. Disable this to use a better algorithm, but note that your "
+   "query plans may change (enabled by default).",
+   (gptr*) &srv_use_legacy_cardinality_algorithm,
+   (gptr*) &srv_use_legacy_cardinality_algorithm,
+   0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0},
 #endif /* End HAVE_INNOBASE_DB */
   {"isam", OPT_ISAM, "Obsolete. ISAM storage engine is no longer supported.",
    (gptr*) &opt_isam, (gptr*) &opt_isam, 0, GET_BOOL, NO_ARG, 0, 0, 0,

=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc	2009-02-10 22:47:54 +0000
+++ b/sql/set_var.cc	2009-04-24 11:03:50 +0000
@@ -450,6 +450,9 @@ sys_var_thd_bool	sys_innodb_table_locks(
                                                &SV::innodb_table_locks);
 sys_var_thd_bool	sys_innodb_support_xa("innodb_support_xa",
                                                &SV::innodb_support_xa);
+sys_var_bool_ptr        sys_innodb_use_legacy_cardinality_algorithm(
+                          "innodb_use_legacy_cardinality_algorithm",
+                          &srv_use_legacy_cardinality_algorithm);
 sys_var_long_ptr	sys_innodb_autoextend_increment("innodb_autoextend_increment",
 							&srv_auto_extend_increment);
 sys_var_long_ptr	sys_innodb_sync_spin_loops("innodb_sync_spin_loops",
@@ -804,6 +807,7 @@ sys_var *sys_variables[]=
   &sys_innodb_max_purge_lag,
   &sys_innodb_table_locks,
   &sys_innodb_support_xa,
+  &sys_innodb_use_legacy_cardinality_algorithm,
   &sys_innodb_autoextend_increment,
   &sys_innodb_sync_spin_loops,
   &sys_innodb_concurrency_tickets,
@@ -946,6 +950,8 @@ struct show_var_st init_vars[]= {
   {sys_innodb_table_locks.name, (char*) &sys_innodb_table_locks, SHOW_SYS},
   {sys_innodb_thread_concurrency.name, (char*) &sys_innodb_thread_concurrency, SHOW_SYS},
   {sys_innodb_thread_sleep_delay.name, (char*) &sys_innodb_thread_sleep_delay, SHOW_SYS},
+  {sys_innodb_use_legacy_cardinality_algorithm.name,
+   (char*) &sys_innodb_use_legacy_cardinality_algorithm, SHOW_SYS},
 #endif
   {sys_interactive_timeout.name,(char*) &sys_interactive_timeout,   SHOW_SYS},
   {sys_join_buffer_size.name,   (char*) &sys_join_buffer_size,	    SHOW_SYS},

Thread
bzr commit into mysql-5.0-bugteam branch (satya.bn:2729) Bug#43660Satya B24 Apr