Merge lp:~hrvojem/percona-server/bug898333 into lp:percona-server/5.5

[Hrvoje Matijakovic]

[Alexey Kopytov (akopytov)]

Hrvoje,

Please remove this:

> +Delaying foreign key creation does not introduce any additional risks, as :command:`mysqldump` always prepends its output with ``SET FOREIGN_KEY_CHECKS=0`` anyway.

Since adding/dropping foreign keys forces table rebuilding, it doesn't make sense to delay them. I fixed this after the original docs were written.

Otherwise looks good to me.

review: Needs Fixing

[Alexey Kopytov (akopytov)]

OK to merge.

review: Approve

=== modified file 'doc/source/conf.py'
--- doc/source/conf.py	2011-10-07 23:38:41 +0000
+++ doc/source/conf.py	2012-01-30 16:47:24 +0000
@@ -98,6 +98,8 @@
 
 .. |XtraDB|  replace:: *XtraDB*
 
+.. |Jenkins|  replace:: :term:`Jenkins`
+
 .. |InnoDB|  replace:: *InnoDB*
 
 .. |MyISAM|  replace:: *MyISAM*
 
=== modified file 'doc/source/development.rst'
--- doc/source/development.rst	2012-01-25 14:53:47 +0000
+++ doc/source/development.rst	2012-01-30 16:47:24 +0000
@@ -15,7 +15,7 @@
 (allowing for a release at any time) and minimizing wasted time by
 developers due to broken code from somebody else interfering with their day.
 
-You should also be familiar with our :ref:`Jenkin` setup.
+You should also be familiar with our |Jenkins| setup.
 
 Overview
 ~~~~~~~~
@@ -163,19 +163,9 @@
 This way anybody can easily check out an old release by just using bzr
 to branch the specific tag.
 
-
-.. _Jenkin:
-
 Jenkins
 =======
 
-`Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
-system that we use to help ensure the continued quality of the
-software we produce. It helps us achieve the aims of:
-* no failed tests in trunk on any platform
-* aid developers in ensuring merge requests build and test on all platforms
-* no known performance regressions (without a damn good explanation)
-
 Our Jenkins instance uses a mixture of VMs on physical hosts that
 Percona runs and Virtual Machines in Amazon EC2 that are launched on
 demand.
 
=== modified file 'doc/source/glossary.rst'
--- doc/source/glossary.rst	2011-09-26 18:41:24 +0000
+++ doc/source/glossary.rst	2012-01-30 16:47:24 +0000
@@ -5,8 +5,9 @@
 .. glossary::
 
   ACID
-    :term:`Atomicity` :term:`Consistency` :term:`Isolation`
-    :term:`Durability`
+    Set of properties that guarantee database transactions are 
+    processed reliably. Stands for :term:`Atomicity`,
+    :term:`Consistency`, :term:`Isolation`, :term:`Durability`.
 
   Atomicity
     Atomicity means that database operations are applied following a
@@ -20,21 +21,20 @@
   Drizzle
     Drizzle: a database for the cloud.
 
-    Drizzle is a community-driven open source project that is forked
+    `Drizzle <http://www.drizzle.org/>`_ is a community-driven open source project that is forked
     from the popular MySQL database. The Drizzle team has removed
     non-essential code, re-factored the remaining code into a
     plugin-based architecture and modernized the code base moving to
     C++.
 
-    Drizzle Charter
-    * A database optimized for Cloud infrastructure and Web applications.
-    * Design for massive concurrency on modern multi-cpu architecture
-    * Optimize memory for increased performance and parallelism
-    * Open source, open community, open design
-    Scope
-    * Re-designed modular architecture providing plugins with defined APIs
-    * Simple design for ease of use and administration
-    * Reliable, ACID transactional
+  Drizzle Charter
+     * A database optimized for Cloud infrastructure and Web applications.
+     * Design for massive concurrency on modern multi-cpu architecture
+     * Optimize memory for increased performance and parallelism
+     * Open source, open community, open design Scope
+     * Re-designed modular architecture providing plugins with defined APIs
+     * Simple design for ease of use and administration
+     * Reliable, ACID transactional
 
   Durability
     Once a transaction is committed, it will remain so.
@@ -56,6 +56,15 @@
     :term:`MySQL` version 5.5, InnoDB became the default storage engine
     on all platforms.
 
+  Jenkins
+    `Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
+    system that we use to help ensure the continued quality of the
+    software we produce. It helps us achieve the aims of:
+
+     * no failed tests in trunk on any platform,
+     * aid developers in ensuring merge requests build and test on all platforms,
+     * no known performance regressions (without a damn good explanation).
+
   LSN
     Log Serial Number. A term used in relation to the :term:`InnoDB` or
     :term:`XtraDB` storage engines.
 
=== modified file 'doc/source/index.rst'
--- doc/source/index.rst	2012-01-10 00:08:02 +0000
+++ doc/source/index.rst	2012-01-30 16:47:24 +0000
@@ -69,7 +69,7 @@
    :maxdepth: 1
    :glob:
 
-   performance/innodb_purge_thread
+   performance/innodb_lazy_drop_table
    performance/innodb_doublewrite_path
    performance/query_cache_enhance
    performance/innodb_fast_checksum
@@ -118,6 +118,7 @@
    management/innodb_expand_import
    management/innodb_lru_dump_restore
    management/innodb_fast_index_creation
+   management/innodb_extended_fast_index_creation
    management/innodb_fast_index_renaming
    management/sql_no_fcache
    management/udf_maatkit
 
=== added file 'doc/source/management/innodb_extended_fast_index_creation.rst'
--- doc/source/management/innodb_extended_fast_index_creation.rst	1970-01-01 00:00:00 +0000
+++ doc/source/management/innodb_extended_fast_index_creation.rst	2012-01-30 16:47:24 +0000
@@ -0,0 +1,82 @@
+.. _extended_innodb_fast_index_creation:
+
+============================
+Extended Fast Index Creation
+============================
+
+Percona has implemented several changes related to |MySQL|'s fast index creation feature. This feature extends the ``ALTER TABLE`` command by adding a new clause that provides online index renaming capability, that is renaming indexes without rebuilding the whole table.
+
+Enabling Extended Fast Index Creation
+=====================================
+
+Fast index creation was implemented in |MySQL| as a way to speed up the process of adding or dropping indexes on tables with many rows. However, cases have been found in which fast index creation creates an inconsistency between |MySQL| and |InnoDB| data dictionaries.
+
+This feature implements a session variable that enables extended fast index creation. Besides optimizing DDL directly, :variable:`expand_fast_index_creation` may also optimize index access for subsequent DML statements because using it results in much less fragmented indexes.
+
+
+:command:`mysqldump`
+--------------------
+
+A new option, ``--innodb-optimize-keys``, was implemented in :command:`mysqldump`. It changes the way |InnoDB| tables are dumped, so that secondary and foreign keys are created after loading the data, thus taking advantage of fast index creation. More specifically:
+
+  * ``KEY``, ``UNIQUE KEY``, and ``CONSTRAINT`` clauses are omitted from ``CREATE TABLE`` statements corresponding to |InnoDB| tables.
+
+  * An additional ``ALTER TABLE`` is issued after dumping the data, in order to create the previously omitted keys.
+
+``ALTER TABLE``
+---------------
+
+When ``ALTER TABLE`` requires a table copy, secondary keys are now dropped and recreated later, after copying the data. The following restrictions apply:
+
+  * Only non-unique keys can be involved in this optimization.
+
+  * If the table contains foreign keys, or a foreign key is being added as a part of the current ``ALTER TABLE`` statement, the optimization is disabled for all keys.
+
+``OPTIMIZE TABLE``
+------------------
+
+Internally, ``OPTIMIZE TABLE`` is mapped to ``ALTER TABLE ... ENGINE=innodb`` for |InnoDB| tables. As a consequence, it now also benefits from fast index creation, with the same restrictions as for ``ALTER TABLE``.
+
+
+Caveats
+-------
+
+|InnoDB| fast index creation uses temporary files in tmpdir for all indexes being created. So make sure you have enough tmpdir space when using :variable:`expand_fast_index_creation`. It is a session variable, so you can temporarily switch it off if you are short on tmpdir space and/or don’t want this optimization to be used for a specific table. 
+
+There’s also a number of cases when this optimization is not applicable:
+  * ``UNIQUE`` indexes in ``ALTER TABLE`` are ignored to enforce uniqueness where necessary when copying the data to a temporary table;
+
+  * ``ALTER TABLE`` and ``OPTIMIZE TABLE`` always process tables containing foreign keys as if :variable:`expand_fast_index_creation` is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint;
+
+  * :command:`mysqldump --innodb-optimize-keys` ignores foreign keys because |InnoDB| requires a full table rebuild on foreign key changes. So adding them back with a separate ``ALTER TABLE`` after restoring the data from a dump would actually make the restore slower;
+
+  * :command:`mysqldump --innodb-optimize-keys` ignores indexes on ``AUTO_INCREMENT`` columns, because they must be indexed, so it is impossible to temporarily drop the corresponding index;
+
+  * :command:`mysqldump --innodb-optimize-keys` ignores the first UNIQUE index on non-nullable columns when the table has no ``PRIMARY KEY`` defined, because in this case |InnoDB| picks such an index as the clustered one.
+
+Version Specific Information
+============================
+
+  * 5.5.16-22.0
+    Variable :variable:`expand_fast_index_creation` implemented.
+    This variable is controling whether fast index creation optimizations made by Perocna are used.
+
+System Variables
+================
+
+.. variable:: expand_fast_index_creation
+
+     :cli: Yes
+     :conf: No
+     :scope: Local/Global
+     :dyn: Yes
+     :vartype: Boolean
+     :default: OFF
+     :range: ON/OFF
+
+Other Reading
+=============
+
+  * `Improved InnoDB fast index creation <http://www.mysqlperformanceblog.com/2011/11/06/improved-innodb-fast-index-creation/>`_
+  * `Thinking about running OPTIMIZE on your InnoDB Table? Stop! <http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/>`_
+
 
=== added file 'doc/source/performance/innodb_lazy_drop_table.rst'
--- doc/source/performance/innodb_lazy_drop_table.rst	1970-01-01 00:00:00 +0000
+++ doc/source/performance/innodb_lazy_drop_table.rst	2012-01-30 16:47:24 +0000
@@ -0,0 +1,36 @@
+.. _innodb_lazy_drop_table_page:
+
+======================
+Drop table performance
+======================
+
+When  *innodb_file_per_table* is set to 1, doing a DROP TABLE can take a long time on servers with a large buffer pool, even on an empty |InnoDB| table. This is because InnoDB has to scan through the buffer pool to purge pages that belong to the corresponding tablespace. Furthermore, no other queries can start while that scan is in progress.
+
+This feature allows you to do "background table drop".
+
+Version Specific Information
+============================
+
+  * 5.5.10-20.1 Feature added.
+
+System Variables
+================
+
+.. variable:: innodb_lazy_drop_table
+
+   :cli: Yes
+   :conf: Yes
+   :scope: Global       
+   :dyn: Yes   
+   :vartype: BOOL
+   :default: FALSE
+   :range: TRUE/FALSE
+
+When this option is ON, XtraDB optimizes that process by only marking the pages corresponding to the tablespace being deleted. It defers the actual work of evicting those pages until it needs to find some free pages in the buffer pool.
+
+When this option is OFF, the usual behavior for dropping tables is in effect.
+
+Related Reading
+===============
+
+   * Drop table performance `blog post <http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/>`_.
 
=== removed file 'doc/source/performance/innodb_purge_thread.rst'
--- doc/source/performance/innodb_purge_thread.rst	2011-10-07 23:38:41 +0000
+++ doc/source/performance/innodb_purge_thread.rst	1970-01-01 00:00:00 +0000
@@ -1,66 +0,0 @@
-.. _innodb_purge_thread:
-
-========================
- Dedicated Purge Thread
-========================
-
-With |InnoDB|, data modified by a transaction is written to an undo space in the main tablespace, so that the system can provide read consistency. When a transaction is finished, the corresponding area in the undo space is freed. But if there are so many transactions that the purge thread cannot free space quickly enough, the main tablespace will grow dramatically. This will make performance decrease severely and will possibly consume all the available space on disk. This feature lets you use a dedicated purge thread so that the purge activity will be much quicker. And even if the overall performance will decrease when the purge thread is enabled, performance will be more stable which is often highly desirable.
-
-Purge of the undo space is periodically done by the |InnoDB| main thread, along with other maintenance tasks. In most cases for an OLTP application, the transactions are small and short-running so the undo space can fit in memory in the buffer pool. The purge is then quick and efficient.
-
-But there are several reasons that can make the undo space grow very large and go to disk:
-
-  * long-running transactions
-
-  * transactions with lots of changes
-
-  * too many updates for the purge process to keep up
-
-  * In all cases performance will drop dramatically. In standard |InnoDB| it is difficult to find an efficient solution for this problem.
-
-You can now have one or several threads dedicated to the purge. This feature provides several benefits:
-
-  * more control over the purge process
-
-  * more stable performance (no more performance drops)
-
-  * the |InnoDB| main thread does not need to take care of the purge anymore
-
-But be aware that this feature comes at a cost: it reduces the overall performance because purging adds a non-negligible overhead. However we think it is better to have slightly worse but stable performance over time than to have better peak performance but unpredictable sharp drops.
-
-
-System Variables
-================
-
-The following system variable was introduced by this feature:
-
-.. variable:: innodb_use_purge_thread
-
-   :cli: Yes
-   :conf: Yes
-   :scope: Global
-   :dyn: No
-   :type: ULONG
-   :default: 0(~1.0.5), 1(1.0.6~)
-   :range: 0 - 32 (``UNIV_MAX_PARALLELISM``)
-
-Using a value greater than 1 is experimental!
-
-``UNIV_MAX_PARALLELISM`` is the maximum number of parallel threads in a parallelized operation
-
-Other Information
-=================
-
-With ``SHOW INNODB STATUS`` you can monitor the number of unpurged transactions: look at History list length in the ``TRANSACTIONS`` section. This counter increases when a transaction commits and decreases when the purge process removes old versions of rows.
-
-If this counter keeps increasing, it shows that the purge process cannot keep up, so you should use this option to create a dedicated purge thread.
-
-
-Other Reading
-=============
-
-  * `Tuning for heavy writing workloads <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_
-
-  * `Reasons for run-away main |InnoDB| tablespace <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_
-
-  * `Purge thread spiral of death <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_