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

Proposed by Hrvoje Matijakovic on 2012-01-30
Status: Merged
Approved by: Alexey Kopytov on 2012-01-31
Approved revision: 212
Merged at revision: 210
Proposed branch: lp:~hrvojem/percona-server/bug898333
Merge into: lp:percona-server/5.5
Diff against target: 328 lines (+144/-90)
7 files modified
doc/source/conf.py (+2/-0)
doc/source/development.rst (+1/-11)
doc/source/glossary.rst (+21/-12)
doc/source/index.rst (+2/-1)
doc/source/management/innodb_extended_fast_index_creation.rst (+82/-0)
doc/source/performance/innodb_lazy_drop_table.rst (+36/-0)
doc/source/performance/innodb_purge_thread.rst (+0/-66)
To merge this branch: bzr merge lp:~hrvojem/percona-server/bug898333
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) 2012-01-30 Approve on 2012-01-31
Review via email: mp+90671@code.launchpad.net
To post a comment you must log in.
Alexey Kopytov (akopytov) wrote :

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) 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 23:38:41 +0000
3+++ doc/source/conf.py 2012-01-30 16:47:24 +0000
4@@ -98,6 +98,8 @@
5
6 .. |XtraDB| replace:: *XtraDB*
7
8+.. |Jenkins| replace:: :term:`Jenkins`
9+
10 .. |InnoDB| replace:: *InnoDB*
11
12 .. |MyISAM| replace:: *MyISAM*
13
14=== modified file 'doc/source/development.rst'
15--- doc/source/development.rst 2012-01-25 14:53:47 +0000
16+++ doc/source/development.rst 2012-01-30 16:47:24 +0000
17@@ -15,7 +15,7 @@
18 (allowing for a release at any time) and minimizing wasted time by
19 developers due to broken code from somebody else interfering with their day.
20
21-You should also be familiar with our :ref:`Jenkin` setup.
22+You should also be familiar with our |Jenkins| setup.
23
24 Overview
25 ~~~~~~~~
26@@ -163,19 +163,9 @@
27 This way anybody can easily check out an old release by just using bzr
28 to branch the specific tag.
29
30-
31-.. _Jenkin:
32-
33 Jenkins
34 =======
35
36-`Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
37-system that we use to help ensure the continued quality of the
38-software we produce. It helps us achieve the aims of:
39-* no failed tests in trunk on any platform
40-* aid developers in ensuring merge requests build and test on all platforms
41-* no known performance regressions (without a damn good explanation)
42-
43 Our Jenkins instance uses a mixture of VMs on physical hosts that
44 Percona runs and Virtual Machines in Amazon EC2 that are launched on
45 demand.
46
47=== modified file 'doc/source/glossary.rst'
48--- doc/source/glossary.rst 2011-09-26 18:41:24 +0000
49+++ doc/source/glossary.rst 2012-01-30 16:47:24 +0000
50@@ -5,8 +5,9 @@
51 .. glossary::
52
53 ACID
54- :term:`Atomicity` :term:`Consistency` :term:`Isolation`
55- :term:`Durability`
56+ Set of properties that guarantee database transactions are
57+ processed reliably. Stands for :term:`Atomicity`,
58+ :term:`Consistency`, :term:`Isolation`, :term:`Durability`.
59
60 Atomicity
61 Atomicity means that database operations are applied following a
62@@ -20,21 +21,20 @@
63 Drizzle
64 Drizzle: a database for the cloud.
65
66- Drizzle is a community-driven open source project that is forked
67+ `Drizzle <http://www.drizzle.org/>`_ is a community-driven open source project that is forked
68 from the popular MySQL database. The Drizzle team has removed
69 non-essential code, re-factored the remaining code into a
70 plugin-based architecture and modernized the code base moving to
71 C++.
72
73- Drizzle Charter
74- * A database optimized for Cloud infrastructure and Web applications.
75- * Design for massive concurrency on modern multi-cpu architecture
76- * Optimize memory for increased performance and parallelism
77- * Open source, open community, open design
78- Scope
79- * Re-designed modular architecture providing plugins with defined APIs
80- * Simple design for ease of use and administration
81- * Reliable, ACID transactional
82+ Drizzle Charter
83+ * A database optimized for Cloud infrastructure and Web applications.
84+ * Design for massive concurrency on modern multi-cpu architecture
85+ * Optimize memory for increased performance and parallelism
86+ * Open source, open community, open design Scope
87+ * Re-designed modular architecture providing plugins with defined APIs
88+ * Simple design for ease of use and administration
89+ * Reliable, ACID transactional
90
91 Durability
92 Once a transaction is committed, it will remain so.
93@@ -56,6 +56,15 @@
94 :term:`MySQL` version 5.5, InnoDB became the default storage engine
95 on all platforms.
96
97+ Jenkins
98+ `Jenkins <http://www.jenkins-ci.org>`_ is a continuous integration
99+ system that we use to help ensure the continued quality of the
100+ software we produce. It helps us achieve the aims of:
101+
102+ * no failed tests in trunk on any platform,
103+ * aid developers in ensuring merge requests build and test on all platforms,
104+ * no known performance regressions (without a damn good explanation).
105+
106 LSN
107 Log Serial Number. A term used in relation to the :term:`InnoDB` or
108 :term:`XtraDB` storage engines.
109
110=== modified file 'doc/source/index.rst'
111--- doc/source/index.rst 2012-01-10 00:08:02 +0000
112+++ doc/source/index.rst 2012-01-30 16:47:24 +0000
113@@ -69,7 +69,7 @@
114 :maxdepth: 1
115 :glob:
116
117- performance/innodb_purge_thread
118+ performance/innodb_lazy_drop_table
119 performance/innodb_doublewrite_path
120 performance/query_cache_enhance
121 performance/innodb_fast_checksum
122@@ -118,6 +118,7 @@
123 management/innodb_expand_import
124 management/innodb_lru_dump_restore
125 management/innodb_fast_index_creation
126+ management/innodb_extended_fast_index_creation
127 management/innodb_fast_index_renaming
128 management/sql_no_fcache
129 management/udf_maatkit
130
131=== added file 'doc/source/management/innodb_extended_fast_index_creation.rst'
132--- doc/source/management/innodb_extended_fast_index_creation.rst 1970-01-01 00:00:00 +0000
133+++ doc/source/management/innodb_extended_fast_index_creation.rst 2012-01-30 16:47:24 +0000
134@@ -0,0 +1,82 @@
135+.. _extended_innodb_fast_index_creation:
136+
137+============================
138+Extended Fast Index Creation
139+============================
140+
141+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.
142+
143+Enabling Extended Fast Index Creation
144+=====================================
145+
146+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.
147+
148+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.
149+
150+
151+:command:`mysqldump`
152+--------------------
153+
154+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:
155+
156+ * ``KEY``, ``UNIQUE KEY``, and ``CONSTRAINT`` clauses are omitted from ``CREATE TABLE`` statements corresponding to |InnoDB| tables.
157+
158+ * An additional ``ALTER TABLE`` is issued after dumping the data, in order to create the previously omitted keys.
159+
160+``ALTER TABLE``
161+---------------
162+
163+When ``ALTER TABLE`` requires a table copy, secondary keys are now dropped and recreated later, after copying the data. The following restrictions apply:
164+
165+ * Only non-unique keys can be involved in this optimization.
166+
167+ * 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.
168+
169+``OPTIMIZE TABLE``
170+------------------
171+
172+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``.
173+
174+
175+Caveats
176+-------
177+
178+|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.
179+
180+There’s also a number of cases when this optimization is not applicable:
181+ * ``UNIQUE`` indexes in ``ALTER TABLE`` are ignored to enforce uniqueness where necessary when copying the data to a temporary table;
182+
183+ * ``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;
184+
185+ * :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;
186+
187+ * :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;
188+
189+ * :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.
190+
191+Version Specific Information
192+============================
193+
194+ * 5.5.16-22.0
195+ Variable :variable:`expand_fast_index_creation` implemented.
196+ This variable is controling whether fast index creation optimizations made by Perocna are used.
197+
198+System Variables
199+================
200+
201+.. variable:: expand_fast_index_creation
202+
203+ :cli: Yes
204+ :conf: No
205+ :scope: Local/Global
206+ :dyn: Yes
207+ :vartype: Boolean
208+ :default: OFF
209+ :range: ON/OFF
210+
211+Other Reading
212+=============
213+
214+ * `Improved InnoDB fast index creation <http://www.mysqlperformanceblog.com/2011/11/06/improved-innodb-fast-index-creation/>`_
215+ * `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/>`_
216+
217
218=== added file 'doc/source/performance/innodb_lazy_drop_table.rst'
219--- doc/source/performance/innodb_lazy_drop_table.rst 1970-01-01 00:00:00 +0000
220+++ doc/source/performance/innodb_lazy_drop_table.rst 2012-01-30 16:47:24 +0000
221@@ -0,0 +1,36 @@
222+.. _innodb_lazy_drop_table_page:
223+
224+======================
225+Drop table performance
226+======================
227+
228+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.
229+
230+This feature allows you to do "background table drop".
231+
232+Version Specific Information
233+============================
234+
235+ * 5.5.10-20.1 Feature added.
236+
237+System Variables
238+================
239+
240+.. variable:: innodb_lazy_drop_table
241+
242+ :cli: Yes
243+ :conf: Yes
244+ :scope: Global
245+ :dyn: Yes
246+ :vartype: BOOL
247+ :default: FALSE
248+ :range: TRUE/FALSE
249+
250+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.
251+
252+When this option is OFF, the usual behavior for dropping tables is in effect.
253+
254+Related Reading
255+===============
256+
257+ * Drop table performance `blog post <http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/>`_.
258
259=== removed file 'doc/source/performance/innodb_purge_thread.rst'
260--- doc/source/performance/innodb_purge_thread.rst 2011-10-07 23:38:41 +0000
261+++ doc/source/performance/innodb_purge_thread.rst 1970-01-01 00:00:00 +0000
262@@ -1,66 +0,0 @@
263-.. _innodb_purge_thread:
264-
265-========================
266- Dedicated Purge Thread
267-========================
268-
269-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.
270-
271-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.
272-
273-But there are several reasons that can make the undo space grow very large and go to disk:
274-
275- * long-running transactions
276-
277- * transactions with lots of changes
278-
279- * too many updates for the purge process to keep up
280-
281- * In all cases performance will drop dramatically. In standard |InnoDB| it is difficult to find an efficient solution for this problem.
282-
283-You can now have one or several threads dedicated to the purge. This feature provides several benefits:
284-
285- * more control over the purge process
286-
287- * more stable performance (no more performance drops)
288-
289- * the |InnoDB| main thread does not need to take care of the purge anymore
290-
291-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.
292-
293-
294-System Variables
295-================
296-
297-The following system variable was introduced by this feature:
298-
299-.. variable:: innodb_use_purge_thread
300-
301- :cli: Yes
302- :conf: Yes
303- :scope: Global
304- :dyn: No
305- :type: ULONG
306- :default: 0(~1.0.5), 1(1.0.6~)
307- :range: 0 - 32 (``UNIV_MAX_PARALLELISM``)
308-
309-Using a value greater than 1 is experimental!
310-
311-``UNIV_MAX_PARALLELISM`` is the maximum number of parallel threads in a parallelized operation
312-
313-Other Information
314-=================
315-
316-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.
317-
318-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.
319-
320-
321-Other Reading
322-=============
323-
324- * `Tuning for heavy writing workloads <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_
325-
326- * `Reasons for run-away main |InnoDB| tablespace <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_
327-
328- * `Purge thread spiral of death <http://www.mysqlperformanceblog.com/2009/10/14/tuning-for-heavy-writing-workloads/>`_

Subscribers

People subscribed via source and target branches