Merge lp:~akopytov/percona-server/bug858945-5.5 into lp:percona-server/5.5

[Alexey Kopytov]

[Alexey Kopytov (akopytov)]

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/142/

[Laurynas Biveinis (laurynas-biveinis)]

LGTM with
diff line 313: s/InnoDB/the InnoDB
diff line 314: s/fast/the fast

Enable/disable improvements to the InnoDB fast index creation
functionality. Has no effect when the fast index creation is disabled with

review: Approve

[Alexey Kopytov (akopytov)]

Same comments about articles as in https://code.launchpad.net/~akopytov/percona-server/bug858945-5.1/+merge/77544.

=== modified file 'patches/innodb_expand_fast_index_creation.patch'
--- patches/innodb_expand_fast_index_creation.patch	2011-09-23 20:26:34 +0000
+++ patches/innodb_expand_fast_index_creation.patch	2011-09-30 13:21:33 +0000
@@ -596,46 +596,6 @@
 +
 +######################################
 +DROP TABLE t1, t2, t3, t4;
---- a/mysql-test/suite/innodb/r/innodb.result
-+++ b/mysql-test/suite/innodb/r/innodb.result
-@@ -1673,7 +1673,7 @@
- 71
- SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
- variable_value - @innodb_rows_inserted_orig
--1066
-+1108
- SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
- variable_value - @innodb_rows_updated_orig
- 866
---- a/mysql-test/suite/innodb/t/innodb-index.test
-+++ b/mysql-test/suite/innodb/t/innodb-index.test
-@@ -87,6 +87,11 @@
- show create table t1;
- --error ER_MULTIPLE_PRI_KEY
- alter table t1 add primary key (c);
-+# Suppress the error log messages occuring on duplicate key error
-+# during ALTER TABLE when using fast index creation
-+--disable_query_log
-+call mtr.add_suppression("Cannot find index PRIMARY in InnoDB index translation table.");
-+--enable_query_log
- --error ER_DUP_ENTRY
- alter table t1 drop primary key, add primary key (b);
- create unique index c on t1 (c);
---- a/mysql-test/suite/innodb/t/innodb.test
-+++ b/mysql-test/suite/innodb/t/innodb.test
-@@ -21,6 +21,12 @@
- 
- -- source include/have_innodb.inc
- 
-+# Suppress the error log message occuring on duplicate key error
-+# during ALTER TABLE when using fast index creation
-+--disable_query_log
-+call mtr.add_suppression("Cannot find index v_2 in InnoDB index translation table.");
-+--enable_query_log
-+
- let $MYSQLD_DATADIR= `select @@datadir`;
- 
- # Save the original values of some variables in order to be able to
 --- /dev/null
 +++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
 @@ -0,0 +1,155 @@
@@ -847,24 +807,7 @@
      tablespace_op= NO_TABLESPACE_OP;
 --- a/sql/sql_table.cc
 +++ b/sql/sql_table.cc
-@@ -2776,7 +2776,7 @@
-       file                      The handler for the new table.
-       key_info_buffer     OUT   An array of KEY structs for the indexes.
-       key_count           OUT   The number of elements in the array.
--      select_field_count        The number of fields coming from a select table.
-+      select_field_count        The number of fields coming from a select table. 
- 
-   DESCRIPTION
-     Prepares the table and key structures for table creation.
-@@ -3122,7 +3122,6 @@
-   }
- 
-   /* Create keys */
--
-   List_iterator<Key> key_iterator(alter_info->key_list);
-   List_iterator<Key> key_iterator2(alter_info->key_list);
-   uint key_parts=0, fk_key_count=0;
-@@ -3220,6 +3219,14 @@
+@@ -3220,6 +3220,14 @@
    if (!*key_info_buffer || ! key_part_info)
      DBUG_RETURN(TRUE);				// Out of memory
  
@@ -879,7 +822,7 @@
    key_iterator.rewind();
    key_number=0;
    for (; (key=key_iterator++) ; key_number++)
-@@ -3638,6 +3645,22 @@
+@@ -3638,6 +3646,22 @@
        key_info->comment.str= key->key_create_info.comment.str;
      }
  
@@ -902,7 +845,7 @@
      key_info++;
    }
    if (!unique_key && !primary_key &&
-@@ -5256,6 +5279,10 @@
+@@ -5256,6 +5280,10 @@
    List<Create_field> new_create_list;
    /* New key definitions are added here */
    List<Key> new_key_list;
@@ -913,7 +856,7 @@
    List_iterator<Alter_drop> drop_it(alter_info->drop_list);
    List_iterator<Create_field> def_it(alter_info->create_list);
    List_iterator<Alter_column> alter_it(alter_info->alter_list);
-@@ -5268,6 +5295,7 @@
+@@ -5268,6 +5296,7 @@
    uint used_fields= create_info->used_fields;
    KEY *key_info=table->key_info;
    bool rc= TRUE;
@@ -921,7 +864,7 @@
  
    DBUG_ENTER("mysql_prepare_alter_table");
  
-@@ -5457,7 +5485,23 @@
+@@ -5457,7 +5486,26 @@
    /*
      Collect all keys which isn't in drop list. Add only those
      for which some fields exists.
@@ -931,6 +874,8 @@
 +    the InnoDB fast index creation. The following conditions must be
 +    met:
 +
++    - fast_index_creation is enabled for the current session
++    - expand_fast_index_creation is enabled for the current session;
 +    - we are going to create an InnoDB table (this is checked later when the
 +      target engine is known);
 +    - the key most be a non-UNIQUE one;
@@ -940,13 +885,14 @@
 +      key constraints in the table.
 +  */
 +
-+  skip_secondary=
++  skip_secondary= thd->variables.online_alter_index &&
++    thd->variables.expand_fast_index_creation &&
 +    !table->file->get_foreign_key_list(thd, &f_key_list) &&
 +    f_key_list.elements == 0;
  
    for (uint i=0 ; i < table->s->keys ; i++,key_info++)
    {
-@@ -5574,6 +5618,8 @@
+@@ -5574,6 +5622,8 @@
                     test(key_info->flags & HA_GENERATED_KEY),
                     key_parts);
        new_key_list.push_back(key);
@@ -955,7 +901,7 @@
      }
    }
    {
-@@ -5581,7 +5627,21 @@
+@@ -5581,7 +5631,21 @@
      while ((key=key_it++))			// Add new keys
      {
        if (key->type != Key::FOREIGN_KEY)
@@ -977,7 +923,7 @@
        if (key->name.str &&
  	  !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
        {
-@@ -5630,12 +5690,104 @@
+@@ -5630,12 +5694,104 @@
    rc= FALSE;
    alter_info->create_list.swap(new_create_list);
    alter_info->key_list.swap(new_key_list);
@@ -1082,7 +1028,7 @@
    Alter table
  
    SYNOPSIS
-@@ -6428,19 +6580,38 @@
+@@ -6428,19 +6584,38 @@
    */
    if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
    {
@@ -1123,7 +1069,7 @@
    {
 --- /dev/null
 +++ b/mysql-test/r/percona_innodb_expand_fast_index_creation.result
-@@ -0,0 +1,32 @@
+@@ -0,0 +1,64 @@
 +CREATE TABLE t1(
 +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 +a CHAR(1) NOT NULL,
@@ -1134,6 +1080,37 @@
 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
 +ALTER TABLE t1 ADD KEY (a);
++affected rows: 0
++info: Records: 0  Duplicates: 0  Warnings: 0
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++id	1
++select_type	SIMPLE
++table	t1
++type	ALL
++possible_keys	a
++key	NULL
++key_len	NULL
++ref	NULL
++rows	16
++Extra	
++id	1
++select_type	SIMPLE
++table	t2
++type	ref
++possible_keys	a
++key	a
++key_len	1
++ref	test.t1.a
++rows	1
++Extra	Using where
++ALTER TABLE t1 DROP KEY a;
++SET expand_fast_index_creation = 1;
++SELECT @@expand_fast_index_creation;
++@@expand_fast_index_creation
++1
++ALTER TABLE t1 ADD KEY (a);
++affected rows: 0
++info: Records: 0  Duplicates: 0  Warnings: 0
 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
 +id	1
 +select_type	SIMPLE
@@ -1155,10 +1132,11 @@
 +ref	NULL
 +rows	16
 +Extra	Using where; Using join buffer
++SET expand_fast_index_creation = 0;
 +DROP TABLE t1;
 --- /dev/null
 +++ b/mysql-test/t/percona_innodb_expand_fast_index_creation.test
-@@ -0,0 +1,23 @@
+@@ -0,0 +1,45 @@
 +--source include/have_innodb.inc
 +
 +########################################################################
@@ -1176,31 +1154,174 @@
 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
 +
-+ALTER TABLE t1 ADD KEY (a);
-+
-+--vertical_results
-+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++# Check that fast index creation is used
++--enable_info
++ALTER TABLE t1 ADD KEY (a);
++--disable_info
++
++# The default (wrong) plan due to bogus statistics
++--vertical_results
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++--horizontal_results
++
++ALTER TABLE t1 DROP KEY a;
++
++SET expand_fast_index_creation = 1;
++SELECT @@expand_fast_index_creation;
++
++# Check that stats are updated with the option enabled
++
++--enable_info
++ALTER TABLE t1 ADD KEY (a);
++--disable_info
++
++--vertical_results
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++--horizontal_results
++
++SET expand_fast_index_creation = 0;
 +
 +DROP TABLE t1;
 --- a/storage/innobase/row/row0merge.c
 +++ b/storage/innobase/row/row0merge.c
-@@ -2672,6 +2672,8 @@
+@@ -56,6 +56,7 @@
+ #include "log0log.h"
+ #include "ut0sort.h"
+ #include "handler0alter.h"
++#include "ha_prototypes.h"
+ 
+ /* Ignore posix_fadvise() on those platforms where it does not exist */
+ #if defined __WIN__
+@@ -2672,6 +2673,9 @@
  		}
  	}
  
-+	dict_update_statistics(new_table, FALSE, TRUE);
++	if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd))
++	    dict_update_statistics(new_table, FALSE, TRUE);
 +
  func_exit:
  	row_merge_file_destroy_low(tmpfd);
  
---- a/mysql-test/suite/innodb/r/innodb-index.result
-+++ b/mysql-test/suite/innodb/r/innodb-index.result
-@@ -877,7 +877,7 @@
- test.t1	check	status	OK
- explain select * from t1 where b like 'adfd%';
- id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
--1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	15	Using where
-+1	SIMPLE	t1	range	b	b	769	NULL	11	Using where
- drop table t1;
- set global innodb_file_per_table=on;
- set global innodb_file_format='Barracuda';
+--- a/sql/sql_class.h
++++ b/sql/sql_class.h
+@@ -556,6 +556,7 @@
+ 
+   double long_query_time_double;
+ 
++  my_bool expand_fast_index_creation;
+ } SV;
+ 
+ 
+--- a/sql/sys_vars.cc
++++ b/sql/sys_vars.cc
+@@ -710,6 +710,14 @@
+        ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update));
+ #endif
+ 
++static Sys_var_mybool Sys_expand_fast_index_creation(
++       "expand_fast_index_creation",
++       "Enable/disable improvements to InnoDB fast index creation "
++       "functionality. Has no effect when fast index creation is disabled with "
++       "the fast-index-creation option",
++       SESSION_VAR(expand_fast_index_creation), CMD_LINE(OPT_ARG),
++       DEFAULT(FALSE));
++
+ static Sys_var_ulong Sys_expire_logs_days(
+        "expire_logs_days",
+        "If non-zero, binary logs will be purged after expire_logs_days "
+--- a/storage/innobase/handler/ha_innodb.cc
++++ b/storage/innobase/handler/ha_innodb.cc
+@@ -989,6 +989,19 @@
+ 	return(THDVAR((THD*) thd, flush_log_at_trx_commit));
+ }
+ 
++/******************************************************************//**
++Returns true if expand_fast_index_creation is enabled for the current
++session.
++@return	the value of the server's expand_fast_index_creation variable */
++extern "C" UNIV_INTERN
++ibool
++thd_expand_fast_index_creation(
++/*================================*/
++	void*	thd)
++{
++	return((ibool) (((THD*) thd)->variables.expand_fast_index_creation));
++}
++
+ /********************************************************************//**
+ Obtain the InnoDB transaction of a MySQL thread.
+ @return	reference to transaction pointer */
+--- a/storage/innobase/include/ha_prototypes.h
++++ b/storage/innobase/include/ha_prototypes.h
+@@ -303,4 +303,15 @@
+ innobase_get_lower_case_table_names(void);
+ /*=====================================*/
+ 
++/******************************************************************//**
++Returns true if innodb_expand_fast_index_creation is enabled for the current
++session.
++@return	the value of the server's innodb_expand_fast_index_creation variable */
++
++ibool
++thd_expand_fast_index_creation(
++/*==================*/
++	void*	thd);	/*!< in: thread handle (THD*) */
++
++
+ #endif
+--- /dev/null
++++ b/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result
+@@ -0,0 +1,6 @@
++SELECT @@global.expand_fast_index_creation;
++@@global.expand_fast_index_creation
++0
++SELECT @@local.expand_fast_index_creation;
++@@local.expand_fast_index_creation
++0
+--- /dev/null
++++ b/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test
+@@ -0,0 +1,2 @@
++SELECT @@global.expand_fast_index_creation;
++SELECT @@local.expand_fast_index_creation;
+--- a/mysql-test/r/mysqld--help-notwin.result
++++ b/mysql-test/r/mysqld--help-notwin.result
+@@ -140,6 +140,10 @@
+  and DISABLED (keep the event scheduler completely
+  deactivated, it cannot be activated run-time)
+  -T, --exit-info[=#] Used for debugging. Use at your own risk.
++ --expand-fast-index-creation 
++ Enable/disable improvements to InnoDB fast index creation
++ functionality. Has no effect when fast index creation is
++ disabled with the fast-index-creation option
+  --expire-logs-days=# 
+  If non-zero, binary logs will be purged after
+  expire_logs_days days; possible purges happen at startup
+@@ -771,6 +775,7 @@
+ div-precision-increment 4
+ engine-condition-pushdown TRUE
+ event-scheduler OFF
++expand-fast-index-creation FALSE
+ expire-logs-days 0
+ external-locking FALSE
+ flush FALSE
+--- a/mysql-test/r/mysqld--help-win.result
++++ b/mysql-test/r/mysqld--help-win.result
+@@ -140,6 +140,10 @@
+  and DISABLED (keep the event scheduler completely
+  deactivated, it cannot be activated run-time)
+  -T, --exit-info[=#] Used for debugging. Use at your own risk.
++ --expand-fast-index-creation 
++ Enable/disable improvements to InnoDB fast index creation
++ functionality. Has no effect when fast index creation is
++ disabled with the fast-index-creation option
+  --expire-logs-days=# 
+  If non-zero, binary logs will be purged after
+  expire_logs_days days; possible purges happen at startup
+@@ -775,6 +779,7 @@
+ div-precision-increment 4
+ engine-condition-pushdown TRUE
+ event-scheduler OFF
++expand-fast-index-creation FALSE
+ expire-logs-days 0
+ external-locking FALSE
+ flush FALSE

=== modified file 'patches/mysql-test.diff'
--- patches/mysql-test.diff	2011-09-25 07:11:59 +0000
+++ patches/mysql-test.diff	2011-09-30 13:21:33 +0000
@@ -1341,6 +1341,7 @@
 +ENGINE_CONDITION_PUSHDOWN
 +ERROR_COUNT
 +EVENT_SCHEDULER
++EXPAND_FAST_INDEX_CREATION
 +EXPIRE_LOGS_DAYS
 +EXTERNAL_USER
 +FAST_INDEX_CREATION
@@ -1716,6 +1717,7 @@
 +ENGINE_CONDITION_PUSHDOWN
 +ERROR_COUNT
 +EVENT_SCHEDULER
++EXPAND_FAST_INDEX_CREATION
 +EXPIRE_LOGS_DAYS
 +EXTERNAL_USER
 +FAST_INDEX_CREATION