Merge lp:~hrvojem/percona-server/bug898333_5.1 into lp:percona-server/5.1

Proposed by Hrvoje Matijakovic on 2012-01-27
Status: Merged
Approved by: Alexey Kopytov on 2012-01-31
Approved revision: 419
Merged at revision: 422
Proposed branch: lp:~hrvojem/percona-server/bug898333_5.1
Merge into: lp:percona-server/5.1
Diff against target: 256 lines (+148/-20)
6 files modified
doc/source/conf.py (+4/-0)
doc/source/development.rst (+1/-8)
doc/source/glossary.rst (+22/-12)
doc/source/index.rst (+2/-0)
doc/source/management/innodb_extended_fast_index_creation.rst (+83/-0)
doc/source/performance/innodb_lazy_drop_table.rst (+36/-0)
To merge this branch: bzr merge lp:~hrvojem/percona-server/bug898333_5.1
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) 2012-01-27 Approve on 2012-01-31
Review via email: mp+90412@code.launchpad.net
To post a comment you must log in.
419. By Hrvoje Matijakovic on 2012-01-30

 added bugfix for bug897858

Alexey Kopytov (akopytov) wrote :

OK to merge.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'doc/source/conf.py'
2--- doc/source/conf.py 2011-10-07 00:55:06 +0000
3+++ doc/source/conf.py 2012-01-30 16:52:26 +0000
4@@ -104,8 +104,12 @@
5
6 .. |LSN| replace:: *LSN*
7
8+.. |Jenkins| replace:: :term:`Jenkins`
9+
10 .. |XtraBackup| replace:: *XtraBackup*
11
12+.. |myisamchk| replace:: *myisamchk*
13+
14 .. |Percona Server| replace:: *Percona Server*
15
16 .. |Percona| replace:: *Percona*
17
18=== modified file 'doc/source/development.rst'
19--- doc/source/development.rst 2011-10-14 01:06:16 +0000
20+++ doc/source/development.rst 2012-01-30 16:52:26 +0000
21@@ -15,7 +15,7 @@
22 (allowing for a release at any time) and minimizing wasted time by
23 developers due to broken code from somebody else interfering with their day.
24
25-You should also be familiar with our :ref:`Jenkins` setup.
26+You should also be familiar with our |Jenkins| setup.
27
28 Overview
29 ~~~~~~~~
30@@ -166,13 +166,6 @@
31 Jenkins
32 =======
33
34-`Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
35-system that we use to help ensure the continued quality of the
36-software we produce. It helps us achieve the aims of:
37-* no failed tests in trunk on any platform
38-* aid developers in ensuring merge requests build and test on all platforms
39-* no known performance regressions (without a damn good explanation)
40-
41 Our Jenkins instance uses a mixture of VMs on physical hosts that
42 Percona runs and Virtual Machines in Amazon EC2 that are launched on
43 demand.
44
45=== modified file 'doc/source/glossary.rst'
46--- doc/source/glossary.rst 2011-09-26 18:40:15 +0000
47+++ doc/source/glossary.rst 2012-01-30 16:52:26 +0000
48@@ -5,8 +5,9 @@
49 .. glossary::
50
51 ACID
52- :term:`Atomicity` :term:`Consistency` :term:`Isolation`
53- :term:`Durability`
54+ Set of properties that guarantee database transactions are
55+ processed reliably. Stands for: :term:`Atomicity`,
56+ :term:`Consistency`, :term:`Isolation`, :term:`Durability`.
57
58 Atomicity
59 Atomicity means that database operations are applied following a
60@@ -20,21 +21,21 @@
61 Drizzle
62 Drizzle: a database for the cloud.
63
64- Drizzle is a community-driven open source project that is forked
65+ `Drizzle <http://www.drizzle.org/>`_ is a community-driven open source project that is forked
66 from the popular MySQL database. The Drizzle team has removed
67 non-essential code, re-factored the remaining code into a
68 plugin-based architecture and modernized the code base moving to
69 C++.
70
71- Drizzle Charter
72- * A database optimized for Cloud infrastructure and Web applications.
73- * Design for massive concurrency on modern multi-cpu architecture
74- * Optimize memory for increased performance and parallelism
75- * Open source, open community, open design
76- Scope
77- * Re-designed modular architecture providing plugins with defined APIs
78- * Simple design for ease of use and administration
79- * Reliable, ACID transactional
80+ Drizzle Charter:
81+
82+ * A database optimized for Cloud infrastructure and Web applications.
83+ * Design for massive concurrency on modern multi-cpu architecture
84+ * Optimize memory for increased performance and parallelism
85+ * Open source, open community, open design Scope
86+ * Re-designed modular architecture providing plugins with defined APIs
87+ * Simple design for ease of use and administration
88+ * Reliable, ACID transactional
89
90 Durability
91 Once a transaction is committed, it will remain so.
92@@ -56,6 +57,15 @@
93 :term:`MySQL` version 5.5, InnoDB became the default storage engine
94 on all platforms.
95
96+ Jenkins
97+ `Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
98+ system that we use to help ensure the continued quality of the
99+ software we produce. It helps us achieve the aims of:
100+
101+ * no failed tests in trunk on any platform,
102+ * aid developers in ensuring merge requests build and test on all platforms,
103+ * no known performance regressions (without a damn good explanation).
104+
105 LSN
106 Log Serial Number. A term used in relation to the :term:`InnoDB` or
107 :term:`XtraDB` storage engines.
108
109=== modified file 'doc/source/index.rst'
110--- doc/source/index.rst 2011-10-19 22:40:57 +0000
111+++ doc/source/index.rst 2012-01-30 16:52:26 +0000
112@@ -69,6 +69,7 @@
113 :glob:
114
115 performance/innodb_purge_thread
116+ performance/innodb_lazy_drop_table
117 performance/innodb_doublewrite_path
118 performance/query_cache_enhance
119 performance/innodb_fast_checksum
120@@ -116,6 +117,7 @@
121 management/innodb_buffer_pool_shm
122 management/innodb_lru_dump_restore
123 management/innodb_fast_index_creation
124+ management/innodb_extended_fast_index_creation
125 management/innodb_fast_index_renaming
126 management/sql_no_fcache
127 management/udf_maatkit
128
129=== added file 'doc/source/management/innodb_extended_fast_index_creation.rst'
130--- doc/source/management/innodb_extended_fast_index_creation.rst 1970-01-01 00:00:00 +0000
131+++ doc/source/management/innodb_extended_fast_index_creation.rst 2012-01-30 16:52:26 +0000
132@@ -0,0 +1,83 @@
133+.. _extended_innodb_fast_index_creation:
134+
135+============================
136+Extended Fast Index Creation
137+============================
138+
139+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.
140+
141+Enabling Extended Fast Index Creation
142+=====================================
143+
144+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.
145+
146+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.
147+
148+
149+:command:`mysqldump`
150+--------------------
151+
152+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:
153+
154+ * ``KEY``, ``UNIQUE KEY``, and ``CONSTRAINT`` clauses are omitted from ``CREATE TABLE`` statements corresponding to |InnoDB| tables.
155+
156+ * An additional ``ALTER TABLE`` is issued after dumping the data, in order to create the previously omitted keys.
157+
158+
159+``ALTER TABLE``
160+---------------
161+
162+When ``ALTER TABLE`` requires a table copy, secondary keys are now dropped and recreated later, after copying the data. The following restrictions apply:
163+
164+ * Only non-unique keys can be involved in this optimization.
165+
166+ * 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.
167+
168+``OPTIMIZE TABLE``
169+------------------
170+
171+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``.
172+
173+
174+Caveats
175+-------
176+
177+|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.
178+
179+There’s also a number of cases when this optimization is not applicable:
180+ * ``UNIQUE`` indexes in ``ALTER TABLE`` are ignored to enforce uniqueness where necessary when copying the data to a temporary table;
181+
182+ * ``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;
183+
184+ * :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;
185+
186+ * :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;
187+
188+ * :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.
189+
190+Version Specific Information
191+============================
192+
193+ * 5.1.59-13.0
194+ Variable :variable:`expand_fast_index_creation` implemented.
195+ This variable controls whether fast index creation optimizations made by Perocna are used.
196+
197+System Variables
198+================
199+
200+.. variable:: expand_fast_index_creation
201+
202+ :cli: Yes
203+ :conf: No
204+ :scope: Local/Global
205+ :dyn: Yes
206+ :vartype: Boolean
207+ :default: OFF
208+ :range: ON/OFF
209+
210+Other Reading
211+=============
212+
213+ * `Improved InnoDB fast index creation <http://www.mysqlperformanceblog.com/2011/11/06/improved-innodb-fast-index-creation/>`_
214+ * `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/>`_
215+
216
217=== added file 'doc/source/performance/innodb_lazy_drop_table.rst'
218--- doc/source/performance/innodb_lazy_drop_table.rst 1970-01-01 00:00:00 +0000
219+++ doc/source/performance/innodb_lazy_drop_table.rst 2012-01-30 16:52:26 +0000
220@@ -0,0 +1,36 @@
221+.. _innodb_lazy_drop_table_page:
222+
223+======================
224+Drop table performance
225+======================
226+
227+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.
228+
229+This feature allows you to do "background table drop".
230+
231+Version Specific Information
232+============================
233+
234+ * 5.1.56-12-7 Feature added.
235+
236+System Variables
237+================
238+
239+.. variable:: innodb_lazy_drop_table
240+
241+ :cli: Yes
242+ :conf: Yes
243+ :scope: Global
244+ :dyn: Yes
245+ :vartype: BOOL
246+ :default: FALSE
247+ :range: TRUE/FALSE
248+
249+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.
250+
251+When this option is OFF, the usual behavior for dropping tables is in effect.
252+
253+Related Reading
254+===============
255+
256+ * Drop table performance `blog post <http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/>`_.

Subscribers

People subscribed via source and target branches