#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#43660 | Satya B | 24 Apr |
