Merge lp:~hrvojem/percona-server/rn-5.6.10-60.2 into lp:percona-server/5.6
- rn-5.6.10-60.2
- Merge into 5.6
Proposed by
Hrvoje Matijakovic
Status: | Merged |
---|---|
Approved by: | Stewart Smith |
Approved revision: | no longer in the source branch. |
Merged at revision: | 319 |
Proposed branch: | lp:~hrvojem/percona-server/rn-5.6.10-60.2 |
Merge into: | lp:percona-server/5.6 |
Diff against target: |
3790 lines (+263/-3185) 46 files modified
doc/source/changed_in_56.rst (+12/-14) doc/source/compatibility.rst (+0/-12) doc/source/conf.py (+1/-1) doc/source/diagnostics/index_info_schema_tables.rst (+0/-22) doc/source/diagnostics/innodb_deadlock_count.rst (+0/-57) doc/source/diagnostics/innodb_show_status.rst (+0/-475) doc/source/diagnostics/innodb_stats.rst (+0/-160) doc/source/diagnostics/mysql_syslog.rst (+0/-43) doc/source/diagnostics/response_time_distribution.rst (+0/-266) doc/source/diagnostics/show_engines.rst (+0/-40) doc/source/diagnostics/slow_extended_55.rst (+0/-377) doc/source/diagnostics/user_stats.rst (+0/-242) doc/source/flexibility/buff_read_ahead_area.rst (+0/-36) doc/source/flexibility/improved_memory_engine.rst (+0/-190) doc/source/flexibility/innodb_fast_shutdown.rst (+0/-36) doc/source/flexibility/innodb_files_extend.rst (+0/-25) doc/source/flexibility/log_warnings_suppress.rst (+0/-49) doc/source/flexibility/mysql_remove_eol_carret.rst (+0/-31) doc/source/index.rst (+5/-48) doc/source/management/innodb_expanded_fast_index_creation.rst (+82/-0) doc/source/management/innodb_fake_changes.rst (+0/-70) doc/source/management/innodb_kill_idle_trx.rst (+0/-25) doc/source/management/innodb_recovery_patches.rst (+0/-155) doc/source/management/sql_no_fcache.rst (+0/-34) doc/source/management/udf_maatkit.rst (+0/-49) doc/source/performance/innodb_doublewrite_path.rst (+0/-87) doc/source/performance/innodb_fast_checksum.rst (+0/-28) doc/source/performance/innodb_lazy_drop_table.rst (+0/-36) doc/source/performance/innodb_opt_lru_count.rst (+0/-7) doc/source/performance/innodb_thread_concurrency_timer_based.rst (+0/-20) doc/source/performance/query_cache_enhance.rst (+0/-123) doc/source/performance/remove_fcntl_excessive_calls.rst (+0/-35) doc/source/performance/threadpool.rst (+113/-0) doc/source/release-notes/Percona-Server-5.6.10-60.2.rst (+23/-0) doc/source/release-notes/Percona-Server-5.6.5-60.0.rst (+1/-0) doc/source/release-notes/release-notes_index.rst (+3/-1) doc/source/reliability/error_pad.rst (+0/-55) doc/source/reliability/innodb_corrupt_table_action.rst (+4/-23) doc/source/reliability/log_connection_error.rst (+0/-14) doc/source/reliability/show_slave_status_nolock.rst (+0/-23) doc/source/scalability/innodb_adaptive_hash_index_partitions.rst (+0/-38) doc/source/scalability/innodb_expand_undo_slots.rst (+0/-44) doc/source/scalability/innodb_extra_rseg.rst (+0/-49) doc/source/scalability/innodb_insert_buffer.rst (+0/-68) doc/source/scalability/innodb_split_buf_pool_mutex.rst (+0/-77) doc/source/upstream-bug-fixes.rst (+19/-0) |
To merge this branch: | bzr merge lp:~hrvojem/percona-server/rn-5.6.10-60.2 |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Laurynas Biveinis (community) | Needs Fixing | ||
Stewart Smith (community) | Approve | ||
Review via email: mp+150508@code.launchpad.net |
Commit message
Description of the change
To post a comment you must log in.
Revision history for this message
Stewart Smith (stewart) : | # |
review:
Approve
Revision history for this message
Stewart Smith (stewart) wrote : | # |
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote : | # |
- s/beta/alpha
- Line 38 and innodb_
- please add http://
of bugs fixed in PS 5.6. (upstream fixed in 5.7.0)
review:
Needs Fixing
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote : | # |
Also, please include the corrupt table action patch in this release (the milestone will be updated shortly).
review:
Needs Fixing
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'doc/source/changed_in_56.rst' |
2 | --- doc/source/changed_in_56.rst 2013-02-08 16:56:30 +0000 |
3 | +++ doc/source/changed_in_56.rst 2013-03-08 07:34:35 +0000 |
4 | @@ -8,13 +8,11 @@ |
5 | |
6 | Some features that were present in |Percona Server| 5.5 have been removed in |Percona Server| 5.6. These are: |
7 | |
8 | - * optimizer_fix |
9 | - * fast_index_creation (use MySQL 5.6's ALGORITHM= option instead) |
10 | - * HandlerSocket (may return when HandlerSocket supports MySQL 5.6) |
11 | + * `fast_index_creation <http://www.percona.com/doc/percona-server/5.5/management/innodb_fast_index_creation.html>`_ (use MySQL 5.6's ALGORITHM= option instead) |
12 | + * `HandlerSocket <http://www.percona.com/doc/percona-server/5.5/performance/handlersocket.html>`_ (may return when HandlerSocket supports MySQL 5.6) |
13 | * SHOW [GLOBAL] TEMPORARY TABLES functionality is now only available via the INFORMATION_SCHEMA tables TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES. |
14 | - * thread concurrency timer based |
15 | - * innodb_recovery_stats |
16 | - * innodb_show_lock_name.patch - replaced by PERFORMANCE_SCHEMA |
17 | + * `InnoDB timer-based Concurrency Throttling <http://www.percona.com/doc/percona-server/5.5/performance/innodb_thread_concurrency_timer_based.html>`_ |
18 | + * `InnoDB Recovery Stats <http://www.percona.com/doc/percona-server/5.5/management/innodb_recovery_patches.html>`_ |
19 | |
20 | Some features that were present in |Percona Server| 5.5 have been replaced by a different implementation of the same/similar functionality in |Percona Server| 5.6. These are: |
21 | |
22 | @@ -24,11 +22,11 @@ |
23 | |
24 | Some |Percona Server| 5.5 features have been replaced by similar or equivalent MySQL 5.6 features, so we now keep the MySQL 5.6 implementations in |Percona Server| 5.6. These are: |
25 | |
26 | - * innodb_recovery_update_relay_log (replaced by MySQL crash safe replication) |
27 | - * innodb_io_patches (replaced by improvements and changes in MySQL 5.6, although Percona may make improvements in the future) |
28 | - * innodb_dict_size_limit has been replaced by MySQL 5.6 using the existing table-definition-cache variable to limit the size of the InnoDB data dictionary. |
29 | - * split innodb buffer pool mutex. This has been replaced by improvements in upstream InnoDB |
30 | - * expanded IMPORT TABLESPACE has been replaced by MySQL "InnoDB transportable tablespaces" |
31 | - * The InnoDB data dictionary INFORMATION_SCHEMA tables have been superseded by the MySQL implementations |
32 | - * XtraDB SYS_STATS persistent table and index statistics has been replaced by the MySQL 5.6 implementation |
33 | - * lru dump/restore is now available in MySQL 5.6, so we have replaced the Percona Server implementation with the MySQL one. |
34 | + * `Show Lock Names <http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_lock_names.html>`_ has been replaced by PERFORMANCE_SCHEMA |
35 | + * `Crash-Resistant Replication <http://www.percona.com/doc/percona-server/5.5/reliability/crash_resistant_replication.html>`_ has been replaced by |MySQL| crash safe replication |
36 | + * `Improved InnoDB I/O Scalability <http://www.percona.com/doc/percona-server/5.5/scalability/innodb_io_55.html>`_ patches have been replaced by improvements and changes in MySQL 5.6, although Percona may make improvements in the future |
37 | + * `InnoDB Data Dictionary Size Limit <http://www.percona.com/doc/percona-server/5.5/management/innodb_dict_size_limit.html>`_ has been replaced by |MySQL| 5.6 using the existing table-definition-cache variable to limit the size of the |InnoDB| data dictionary. |
38 | + * `Expand Table Import <http://www.percona.com/doc/percona-server/5.5/management/innodb_expand_import.html>`_ has been replaced by |MySQL| "InnoDB transportable tablespaces" |
39 | + * The |InnoDB| data dictionary INFORMATION_SCHEMA tables have been superseded by the |MySQL| implementations |
40 | + * |XtraDB| SYS_STATS persistent table and index statistics has been replaced by the MySQL 5.6 implementation |
41 | + * `Dump/Restore of the Buffer Pool <http://www.percona.com/doc/percona-server/5.5/management/innodb_lru_dump_restore.html>`_ is now available in |MySQL| 5.6, so we have replaced the |Percona Server| implementation with the MySQL one. |
42 | |
43 | === modified file 'doc/source/compatibility.rst' |
44 | --- doc/source/compatibility.rst 2013-03-05 12:46:43 +0000 |
45 | +++ doc/source/compatibility.rst 2013-03-08 07:34:35 +0000 |
46 | @@ -4,18 +4,6 @@ |
47 | Options that make XtraDB tablespaces not compatible with MySQL |
48 | ============================================================== |
49 | |
50 | -Fast checksums |
51 | -============== |
52 | - |
53 | -Enabling :variable:`innodb_fast_checksum` will use more CPU-efficient algorithm, based on 4-byte words which can be beneficial for some workloads. Once enabled, turning it off will require table to be dump/imported again, since |Percona Server| will fail to start on data files created when :variable:`innodb_fast_checksums` was enabled. |
54 | - |
55 | -In case you've migrated from |Percona Server| to |MySQL| you could get the "corrupted checksum" error message. In order to recover that table you'll need to: |
56 | - |
57 | - 1) Reinstall Percona Server to read your tables that were created with fast checksums. |
58 | - 2) Dump the tables (or temporarily convert them to MyISAM). |
59 | - 3) Install stock MySQL (or at least disable fast checksums). |
60 | - 4) Restore the InnoDB tables (or convert back from MyISAM). |
61 | - |
62 | Page sizes other than 16KiB |
63 | =========================== |
64 | |
65 | |
66 | === modified file 'doc/source/conf.py' |
67 | --- doc/source/conf.py 2013-02-08 16:56:30 +0000 |
68 | +++ doc/source/conf.py 2013-03-08 07:34:35 +0000 |
69 | @@ -54,7 +54,7 @@ |
70 | # The short X.Y version. |
71 | version = '5.6' |
72 | # The full version, including alpha/beta/rc tags. |
73 | -release = '5.6.5-60.0' |
74 | +release = '5.6.10-60.2' |
75 | |
76 | # The language for content autogenerated by Sphinx. Refer to documentation |
77 | # for a list of supported languages. |
78 | |
79 | === modified file 'doc/source/diagnostics/index_info_schema_tables.rst' |
80 | --- doc/source/diagnostics/index_info_schema_tables.rst 2011-10-07 23:38:41 +0000 |
81 | +++ doc/source/diagnostics/index_info_schema_tables.rst 2013-03-08 07:34:35 +0000 |
82 | @@ -8,30 +8,8 @@ |
83 | |
84 | * :table:`GLOBAL_TEMPORARY_TABLES` |
85 | |
86 | - * :table:`INNODB_CMP` |
87 | - |
88 | - * :table:`INNODB_CMP_RESET` |
89 | - |
90 | - * :table:`INNODB_CMPMEM` |
91 | - |
92 | - * :table:`INNODB_CMPMEM_RESET` |
93 | - |
94 | - * :table:`INNODB_INDEX_STATS` |
95 | - |
96 | - * :table:`INNODB_LOCK_WAITS` |
97 | - |
98 | - * :table:`INNODB_LOCKS` |
99 | - |
100 | - * :table:`INNODB_RSEG` |
101 | - |
102 | - * :table:`INNODB_TABLE_STATS` |
103 | - |
104 | - * :table:`INNODB_TRX` |
105 | - |
106 | * :table:`PROCESSLIST` |
107 | |
108 | - * :table:`QUERY_RESPONSE_TIME` |
109 | - |
110 | * :table:`TEMPORARY_TABLES` |
111 | |
112 | |
113 | |
114 | === removed file 'doc/source/diagnostics/innodb_deadlock_count.rst' |
115 | --- doc/source/diagnostics/innodb_deadlock_count.rst 2013-03-05 12:46:43 +0000 |
116 | +++ doc/source/diagnostics/innodb_deadlock_count.rst 1970-01-01 00:00:00 +0000 |
117 | @@ -1,57 +0,0 @@ |
118 | -.. _innodb_deadlocks_page: |
119 | - |
120 | -========================== |
121 | - Count |InnoDB| Deadlocks |
122 | -========================== |
123 | - |
124 | -When running a transactional application you have to live with deadlocks. They are not problematic as long as they do not occur too frequently. The standard ``SHOW INNODB STATUS`` gives information on the latest deadlocks but it is not very useful when you want to know the total number of deadlocks or the number of deadlocks per unit of time. |
125 | - |
126 | -This change adds a status variable that keeps track of the number of deadlocks since the server startup, opening the way to a better knowledge of your deadlocks. |
127 | - |
128 | -This feature was provided by Eric Bergen under BSD license (see `InnoDB Deadlock Count Patch <http://ebergen.net/wordpress/2009/08/27/innodb-deadlock-count-patch/>`_). |
129 | - |
130 | -It adds a new global status variable (:variable:`innodb_deadlocks`) showing the number of deadlocks.* |
131 | - |
132 | -You can use it with ``SHOW GLOBAL STATUS``, e.g.: :: |
133 | - |
134 | - mysql> SHOW GLOBAL STATUS LIKE 'innodb_deadlocks'; |
135 | - +------------------+-------+ |
136 | - | Variable_name | Value | |
137 | - +------------------+-------+ |
138 | - | innodb_deadlocks | 323 | |
139 | - +------------------+-------+ |
140 | - |
141 | -or with ``INFORMATION_SCHEMA``, e.g.: :: |
142 | - |
143 | - mysql> SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_deadlocks'; |
144 | - +----------------+ |
145 | - | VARIABLE_VALUE | |
146 | - +----------------+ |
147 | - | 323 | |
148 | - +----------------+ |
149 | - |
150 | -A deadlock will occur when at least two transactions are mutually waiting for the other to finish, thus creating a circular dependency that lasts until something breaks it. |InnoDB| is quite good at detecting deadlocks and generally returns an error instantly. Most transactional systems have no way to prevent deadlocks from occurring and must be designed to handle them, for instance by retrying the transaction that failed. |
151 | - |
152 | - |
153 | -Version Specific Information |
154 | -============================ |
155 | - |
156 | - * 5.5.8-20.0: |
157 | - Full functionality available. |
158 | - |
159 | -Status Variables |
160 | -================ |
161 | - |
162 | -One new status variable was introduced by this feature. |
163 | - |
164 | -.. variable:: innodb_deadlocks |
165 | - |
166 | - :vartype: LONG |
167 | - :scope: Global |
168 | - |
169 | - |
170 | -Related Reading |
171 | -=============== |
172 | - |
173 | - * `Original post by Eric Bergen <http://ebergen.net/wordpress/2009/08/27/|InnoDB|-deadlock-count-patch/>`_ |
174 | - |
175 | |
176 | === removed file 'doc/source/diagnostics/innodb_show_status.rst' |
177 | --- doc/source/diagnostics/innodb_show_status.rst 2013-03-05 12:46:43 +0000 |
178 | +++ doc/source/diagnostics/innodb_show_status.rst 1970-01-01 00:00:00 +0000 |
179 | @@ -1,475 +0,0 @@ |
180 | -.. _innodb_show_status: |
181 | - |
182 | -====================== |
183 | - Show |InnoDB| Status |
184 | -====================== |
185 | - |
186 | -This feature reorganizes the output of ``SHOW INNODB STATUS`` for a better readability and prints the amount of memory used by the internal hash tables. In addition, new variables are available to control the output. |
187 | - |
188 | -This feature modified the ``SHOW INNODB STATUS`` command as follows: |
189 | - |
190 | - * ``TRANSACTION`` section was moved to the end of the output, so that important information is not overlooked when the there is a large amount of it. |
191 | - |
192 | - * Added two variables to control ``SHOW INNODB STATUS`` information presented (bugfix for `#29123 <http://bugs.mysql.com/bug.php?id=29126>`_): |
193 | - |
194 | - * :variable:`innodb_show_verbose_locks` - Whether to show records locked |
195 | - |
196 | - * :variable:`innodb_show_locks_held` - Number of locks held to print for each |InnoDB| transaction |
197 | - |
198 | - * Added extended information about |InnoDB| internal hash table sizes (in bytes) in the ``BUFFER POOL AND MEMORY`` section; also added buffer pool size in bytes. |
199 | - |
200 | - * Added additional LOG section information (beginning in release 5.5.8-20.0). |
201 | - |
202 | -Version Specific Information |
203 | -============================ |
204 | - |
205 | - * 5.5.8-20.0 |
206 | - Added status variables showing information from ``SHOW INNODB STATUS``. |
207 | - |
208 | - * 5.5.8-20.0 |
209 | - Added additional information in the LOG section. |
210 | - |
211 | - * 5.5.10-20.1: |
212 | - Renamed status variable :variable:`innodb_row_lock_numbers` to :variable:`innodb_current_row_locks`. |
213 | - |
214 | -Other Information |
215 | -================= |
216 | - |
217 | - * Author / Origin: |
218 | - Baron Schwartz, http://lists.mysql.com/internals/35174 |
219 | - |
220 | - |
221 | -System Variables |
222 | -================ |
223 | - |
224 | -.. variable:: innodb_show_verbose_locks |
225 | - |
226 | - :cli: Yes |
227 | - :conf: Yes |
228 | - :scope: Global |
229 | - :dyn: Yes |
230 | - :vartype: ULONG |
231 | - :default: 0 |
232 | - :range: 0 - 1 |
233 | - |
234 | -Specifies to show records locked in ``SHOW INNODB STATUS``. The default is 0, which means only the higher-level information about the lock (which table and index is locked, etc.) is printed. If set to 1, then traditional |InnoDB| behavior is enabled: the records that are locked are dumped to the output. |
235 | - |
236 | -.. variable:: innodb_show_locks_held |
237 | - |
238 | - :cli: Yes |
239 | - :conf: Yes |
240 | - :scope: Global |
241 | - :dyn: Yes |
242 | - :vartype: ULONG |
243 | - :default: 10 |
244 | - :range: 0 - 1000 |
245 | - |
246 | -Specifies the number of locks held to print for each |InnoDB| transaction in SHOW INNODB STATUS. |
247 | - |
248 | - |
249 | -Status Variables |
250 | -================ |
251 | - |
252 | -The status variables here contain information available in the output of ``SHOW INNODB STATUS``, organized by the sections ``SHOW INNODB STATUS`` displays. If you are familiar with the output of ``SHOW INNODB STATUS``, you will probably already recognize the information these variables contain. |
253 | - |
254 | - |
255 | -BACKGROUND THREAD |
256 | ------------------ |
257 | - |
258 | -The following variables contain information in the BACKGROUND THREAD section of the output from ``SHOW INNODB STATUS``. An example of that output is: |
259 | - |
260 | - Insert an example of BACKGROUND THREAD section output here. |
261 | - |
262 | -.. variable:: innodb_master_thread_1_second_loops |
263 | - |
264 | - :version 5.5.8-20.0: Introduced. |
265 | - :vartype: Numeric |
266 | - :scope: Global |
267 | - |
268 | -.. variable:: innodb_master_thread_10_second_loops |
269 | - |
270 | - :version 5.5.8-20.0: Introduced. |
271 | - :vartype: Numeric |
272 | - :scope: Global |
273 | - |
274 | -.. variable:: innodb_master_thread_background_loops |
275 | - |
276 | - :version 5.5.8-20.0: Introduced. |
277 | - :vartype: Numeric |
278 | - :scope: Global |
279 | - |
280 | -.. variable:: innodb_master_thread_main_flush_loops |
281 | - |
282 | - :version 5.5.8-20.0: Introduced. |
283 | - :vartype: Numeric |
284 | - :scope: Global |
285 | - |
286 | -.. variable:: innodb_master_thread_sleeps |
287 | - |
288 | - :version 5.5.8-20.0: Introduced. |
289 | - :vartype: Numeric |
290 | - :scope: Global |
291 | - |
292 | -.. variable:: innodb_background_log_sync |
293 | - |
294 | - :version 5.5.8-20.0: Introduced. |
295 | - :vartype: Numeric |
296 | - :scope: Global |
297 | - |
298 | -SEMAPHORES |
299 | ----------- |
300 | - |
301 | -The following variables contain information in the SEMAPHORES section of the output from ``SHOW INNODB STATUS``. An example of that output is: :: |
302 | - |
303 | - ---------- |
304 | - SEMAPHORES |
305 | - ---------- |
306 | - OS WAIT ARRAY INFO: reservation count 9664, signal count 11182 |
307 | - Mutex spin waits 20599, rounds 223821, OS waits 4479 |
308 | - RW-shared spins 5155, OS waits 1678; RW-excl spins 5632, OS waits 2592 |
309 | - Spin rounds per wait: 10.87 mutex, 15.01 RW-shared, 27.19 RW-excl |
310 | - |
311 | -.. variable:: innodb_mutex_os_waits |
312 | - |
313 | - :version 5.5.8-20.0: Introduced. |
314 | - :vartype: Numeric |
315 | - :scope: Global |
316 | - |
317 | -.. variable:: innodb_mutex_spin_rounds |
318 | - |
319 | - :version 5.5.8-20.0: Introduced. |
320 | - :vartype: Numeric |
321 | - :scope: Global |
322 | - |
323 | -.. variable:: innodb_mutex_spin_waits |
324 | - |
325 | - :version 5.5.8-20.0: Introduced. |
326 | - :vartype: Numeric |
327 | - :scope: Global |
328 | - |
329 | -.. variable:: innodb_s_lock_os_waits |
330 | - |
331 | - :version 5.5.8-20.0: Introduced. |
332 | - :vartype: Numeric |
333 | - :scope: Global |
334 | - |
335 | -.. variable:: innodb_s_lock_spin_rounds |
336 | - |
337 | - :version 5.5.8-20.0: Introduced. |
338 | - :vartype: Numeric |
339 | - :scope: Global |
340 | - |
341 | -.. variable:: innodb_s_lock_spin_waits |
342 | - |
343 | - :version 5.5.8-20.0: Introduced. |
344 | - :vartype: Numeric |
345 | - :scope: Global |
346 | - |
347 | -.. variable:: innodb_x_lock_os_waits |
348 | - |
349 | - :version 5.5.8-20.0: Introduced. |
350 | - :vartype: Numeric |
351 | - :scope: Global |
352 | - |
353 | -.. variable:: innodb_x_lock_spin_rounds |
354 | - |
355 | - :version 5.5.8-20.0: Introduced. |
356 | - :vartype: Numeric |
357 | - :scope: Global |
358 | - |
359 | -.. variable:: innodb_x_lock_spin_waits |
360 | - |
361 | - :version 5.5.8-20.0: Introduced. |
362 | - :vartype: Numeric |
363 | - :scope: Global |
364 | - |
365 | -INSERT BUFFER AND ADAPTIVE HASH INDEX |
366 | -------------------------------------- |
367 | - |
368 | -The following variables contain information in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the output from SHOW |InnoDB| STATUS. An example of that output is: :: |
369 | - |
370 | - ------------------------------------- |
371 | - INSERT BUFFER AND ADAPTIVE HASH INDEX |
372 | - ------------------------------------- |
373 | - Ibuf: size 1, free list len 6089, seg size 6091, |
374 | - 44497 inserts, 44497 merged recs, 8734 merges |
375 | - Hash table size 276707, node heap has 1 buffer(s) |
376 | - 0.00 hash searches/s, 0.00 non-hash searches/s |
377 | - |
378 | -.. variable:: innodb_ibuf_discarded_delete_marks |
379 | - |
380 | - :version 5.5.8-20.0: Introduced. |
381 | - :vartype: Numeric |
382 | - :scope: Global |
383 | - |
384 | -.. variable:: innodb_ibuf_discarded_deletes |
385 | - |
386 | - :version 5.5.8-20.0: Introduced. |
387 | - :vartype: Numeric |
388 | - :scope: Global |
389 | - |
390 | -.. variable:: innodb_ibuf_discarded_inserts |
391 | - |
392 | - :version 5.5.8-20.0: Introduced. |
393 | - :vartype: Numeric |
394 | - :scope: Global |
395 | - |
396 | -.. variable:: innodb_ibuf_free_list |
397 | - |
398 | - :version 5.5.8-20.0: Introduced. |
399 | - :vartype: Numeric |
400 | - :scope: Global |
401 | - |
402 | -.. variable:: innodb_ibuf_merged_delete_marks |
403 | - |
404 | - :version 5.5.8-20.0: Introduced. |
405 | - :vartype: Numeric |
406 | - :scope: Global |
407 | - |
408 | -.. variable:: innodb_ibuf_merged_deletes |
409 | - |
410 | - :version 5.5.8-20.0: Introduced. |
411 | - :vartype: Numeric |
412 | - :scope: Global |
413 | - |
414 | -.. variable:: innodb_ibuf_merged_inserts |
415 | - |
416 | - :version 5.5.8-20.0: Introduced. |
417 | - :vartype: Numeric |
418 | - :scope: Global |
419 | - |
420 | -.. variable:: innodb_ibuf_merges |
421 | - |
422 | - :version 5.5.8-20.0: Introduced. |
423 | - :vartype: Numeric |
424 | - :scope: Global |
425 | - |
426 | -.. variable:: innodb_ibuf_segment_size |
427 | - |
428 | - :version 5.5.8-20.0: Introduced. |
429 | - :vartype: Numeric |
430 | - :scope: Global |
431 | - |
432 | -.. variable:: innodb_ibuf_size |
433 | - |
434 | - :version 5.5.8-20.0: Introduced. |
435 | - :vartype: Numeric |
436 | - :scope: Global |
437 | - |
438 | -.. variable:: innodb_adaptive_hash_cells |
439 | - |
440 | - :version 5.5.8-20.0: Introduced. |
441 | - :vartype: Numeric |
442 | - :scope: Global |
443 | - |
444 | -.. variable:: innodb_adaptive_hash_heap_buffers |
445 | - |
446 | - :version 5.5.8-20.0: Introduced. |
447 | - :vartype: Numeric |
448 | - :scope: Global |
449 | - |
450 | -.. variable:: innodb_adaptive_hash_hash_searches |
451 | - |
452 | - :version 5.5.8-20.0: Introduced. |
453 | - :vartype: Numeric |
454 | - :scope: Global |
455 | - |
456 | -.. variable:: innodb_adaptive_hash_non_hash_searches |
457 | - |
458 | - :version 5.5.8-20.0: Introduced. |
459 | - :vartype: Numeric |
460 | - :scope: Global |
461 | - |
462 | -LOG |
463 | ---- |
464 | - |
465 | -The following variables contain information in the LOG section of the output from ``SHOW INNODB STATUS``. An example of that output is: :: |
466 | - |
467 | - --- |
468 | - LOG |
469 | - --- |
470 | - Log sequence number 28219393219 |
471 | - Log flushed up to 28219393219 |
472 | - Last checkpoint at 28212583337 |
473 | - Max checkpoint age 7782360 |
474 | - Checkpoint age target 7539162 |
475 | - Modified age 6809882 |
476 | - Checkpoint age 6809882 |
477 | - 0 pending log writes, 0 pending chkp writes |
478 | - 8570 log i/o's done, 2000.00 log i/o's/second |
479 | - |
480 | -.. variable:: innodb_lsn_current |
481 | - |
482 | - :version 5.5.8-20.0: Introduced. |
483 | - :vartype: Numeric |
484 | - :scope: Global |
485 | - |
486 | -.. variable:: innodb_lsn_flushed |
487 | - |
488 | - :version 5.5.8-20.0: Introduced. |
489 | - :vartype: Numeric |
490 | - :scope: Global |
491 | - |
492 | -.. variable:: innodb_lsn_last_checkpoint |
493 | - |
494 | - :version 5.5.8-20.0: Introduced. |
495 | - :vartype: Numeric |
496 | - :scope: Global |
497 | - |
498 | -.. variable:: innodb_checkpoint_age |
499 | - |
500 | - :version 5.5.8-20.0: Introduced. |
501 | - :vartype: Numeric |
502 | - :scope: Global |
503 | - |
504 | -.. variable:: innodb_checkpoint_max_age |
505 | - |
506 | - :version 5.5.8-20.0: Introduced. |
507 | - :vartype: Numeric |
508 | - :scope: Global |
509 | - |
510 | -.. variable:: innodb_checkpoint_target_age |
511 | - |
512 | - :version 5.5.8-20.0: Introduced. |
513 | - :vartype: Numeric |
514 | - :scope: Global |
515 | - |
516 | -BUFFER POOL AND MEMORY |
517 | ----------------------- |
518 | - |
519 | -The following variables contain information in the BUFFER POOL AND MEMORY section of the output from ``SHOW INNODB STATUS``. An example of that output is: :: |
520 | - |
521 | - ---------------------- |
522 | - BUFFER POOL AND MEMORY |
523 | - ---------------------- |
524 | - Total memory allocated 137625600; in additional pool allocated 0 |
525 | - Internal hash tables (constant factor + variable factor) |
526 | - Adaptive hash index 3774352 (2213656 + 1560696) |
527 | - Page hash 139144 |
528 | - Dictionary cache 629811 (554864 + 74947) |
529 | - File system 83536 (82672 + 864) |
530 | - Lock system 380792 (332872 + 47920) |
531 | - Recovery system 0 (0 + 0) |
532 | - Threads 84040 (82696 + 1344) |
533 | - Dictionary memory allocated 74947 |
534 | - Buffer pool size 8192 |
535 | - Buffer pool size, bytes 134217728 |
536 | - Free buffers 0 |
537 | - Database pages 8095 |
538 | - Old database pages 2968 |
539 | - Modified db pages 5914 |
540 | - Pending reads 0 |
541 | - Pending writes: LRU 0, flush list 129, single page 0 |
542 | - Pages made young 372084, not young 0 |
543 | - 2546000.00 youngs/s, 0.00 non-youngs/s |
544 | - Pages read 103356, created 154787, written 979572 |
545 | - 469000.00 reads/s, 78000.00 creates/s, 138000.00 writes/s |
546 | - Buffer pool hit rate 994 / 1000, young-making rate 34 / 1000 not 0 / 1000 |
547 | - Pages read ahead 0.00/s, evicted without access 15000.00/s |
548 | - |
549 | -.. variable:: innodb_mem_adaptive_hash |
550 | - |
551 | - :version 5.5.8-20.0: Introduced. |
552 | - :vartype: Numeric |
553 | - :scope: Global |
554 | - |
555 | -.. variable:: innodb_mem_dictionary |
556 | - |
557 | - :version 5.5.8-20.0: Introduced. |
558 | - :vartype: Numeric |
559 | - :scope: Global |
560 | - |
561 | -.. variable:: innodb_mem_total |
562 | - |
563 | - :version 5.5.8-20.0: Introduced. |
564 | - :vartype: Numeric |
565 | - :scope: Global |
566 | - |
567 | -.. variable:: innodb_buffer_pool_pages_LRU_flushed |
568 | - |
569 | - :version 5.5.8-20.0: Introduced. |
570 | - :vartype: Numeric |
571 | - :scope: Global |
572 | - |
573 | -.. variable:: innodb_buffer_pool_pages_made_not_young |
574 | - |
575 | - :version 5.5.8-20.0: Introduced. |
576 | - :vartype: Numeric |
577 | - :scope: Global |
578 | - |
579 | -.. variable:: innodb_buffer_pool_pages_made_young |
580 | - |
581 | - :version 5.5.8-20.0: Introduced. |
582 | - :vartype: Numeric |
583 | - :scope: Global |
584 | - |
585 | -.. variable:: innodb_buffer_pool_pages_old |
586 | - |
587 | - :version 5.5.8-20.0: Introduced. |
588 | - :vartype: Numeric |
589 | - :scope: Global |
590 | - |
591 | -TRANSACTIONS |
592 | ------------- |
593 | - |
594 | -The following variables contain information in the TRANSACTIONS section of the output from ``SHOW INNODB STATUS``. An example of that output is: :: |
595 | - |
596 | - ------------ |
597 | - TRANSACTIONS |
598 | - ------------ |
599 | - Trx id counter F561FD |
600 | - Purge done for trx's n:o < F561EB undo n:o < 0 |
601 | - History list length 19 |
602 | - LIST OF TRANSACTIONS FOR EACH SESSION: |
603 | - ---TRANSACTION 0, not started, process no 993, OS thread id 140213152634640 |
604 | - mysql thread id 15933, query id 32109 localhost root |
605 | - show innodb status |
606 | - ---TRANSACTION F561FC, ACTIVE 29 sec, process no 993, OS thread id 140213152769808 updating or deleting |
607 | - mysql tables in use 1, locked 1 |
608 | - |
609 | -.. variable:: innodb_history_list_length |
610 | - |
611 | - :version 5.5.8-20.0: Introduced. |
612 | - :vartype: Numeric |
613 | - :scope: Global |
614 | - |
615 | -.. variable:: innodb_max_trx_id |
616 | - |
617 | - :version 5.5.8-20.0: Introduced. |
618 | - :vartype: Numeric |
619 | - :scope: Global |
620 | - |
621 | -.. variable:: innodb_oldest_view_low_limit_trx_id |
622 | - |
623 | - :version 5.5.8-20.0: Introduced. |
624 | - :vartype: Numeric |
625 | - :scope: Global |
626 | - |
627 | -.. variable:: innodb_purge_trx_id |
628 | - |
629 | - :version 5.5.8-20.0: Introduced. |
630 | - :vartype: Numeric |
631 | - :scope: Global |
632 | - |
633 | -.. variable:: innodb_purge_undo_no |
634 | - |
635 | - :version 5.5.8-20.0: Introduced. |
636 | - :vartype: Numeric |
637 | - :scope: Global |
638 | - |
639 | -.. variable:: innodb_current_row_locks |
640 | - |
641 | - :version 5.5.8-20.0: Introduced. |
642 | - :version 5.5.10-20.1: Renamed. |
643 | - :vartype: Numeric |
644 | - :scope: Global |
645 | - |
646 | - This variable was named :variable:`innodb_row_lock_numbers` in release 5.5.8-20.0. |
647 | - |
648 | - |
649 | -Other reading |
650 | -============= |
651 | - |
652 | - * `SHOW INNODB STATUS walk through <http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/>`_ |
653 | - |
654 | - * `Table locks in SHOW INNODB STATUS <http://www.mysqlperformanceblog.com/2010/06/08/table-locks-in-show-innodb-status/>`_ |
655 | |
656 | === removed file 'doc/source/diagnostics/innodb_stats.rst' |
657 | --- doc/source/diagnostics/innodb_stats.rst 2013-03-05 12:46:43 +0000 |
658 | +++ doc/source/diagnostics/innodb_stats.rst 1970-01-01 00:00:00 +0000 |
659 | @@ -1,160 +0,0 @@ |
660 | -.. _innodb_stats: |
661 | - |
662 | -===================== |
663 | - |InnoDB| Statistics |
664 | -===================== |
665 | - |
666 | -This feature provides new startup options (control method and collection of index statistics estimation) and information schema views to confirm the statistics. |
667 | - |
668 | -This implements the fix for `MySQL Bug #30423 <http://bugs.mysql.com/bug.php?id=30423>`_. |
669 | - |
670 | -Version Specific Information |
671 | -============================ |
672 | - |
673 | - * 5.5.8-20.0: |
674 | - Renamed three fields in table ``INNODB_INDEX_STATS``. |
675 | - |
676 | - |
677 | -System Variables |
678 | -================ |
679 | - |
680 | -Four new system variables were introduced by this feature. |
681 | - |
682 | -.. variable:: innodb_stats_method |
683 | - |
684 | - :cli: YES |
685 | - :configfile: YES |
686 | - :scope: GLOBAL |
687 | - :dyn: YES |
688 | - :type: STRING |
689 | - :default: ``nulls_equal`` |
690 | - :allowed: ``nulls_equal``, ``nulls_unequal``, ``nulls_ignored`` |
691 | - |
692 | -The values and meanings are almost same to ``myisam_stats_method`` option of native |MySQL| (``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``). But |InnoDB| doesn't have several patterns of statistics currently. So, though this option able to be changed dynamically, we need re-calculate statistics to change the method for the table. |
693 | - |
694 | -(reference: `MyISAM Index Statistics Collection <http://dev.mysql.com/doc/refman/5.1/en/myisam-index-statistics.html>`_) |
695 | - |
696 | -**Note:** Beginning in release 5.1.56-12.7, a variable with the same and functionality was implemented in the upstream |InnoDB|. |
697 | - |
698 | -.. variable:: innodb_stats_auto_update |
699 | - |
700 | - :type: BOOLEAN |
701 | - :default: 1 |
702 | - |
703 | -|InnoDB| updates the each index statistics automatically (many updates were done, some information_schema is accessed, table monitor, etc.). Setting this option 0 can stop these automatic recalculation of the statistics except for “first open” and “ANALYZE TABLE command”. |
704 | - |
705 | - |
706 | -.. variable:: innodb_stats_update_need_lock |
707 | - |
708 | - :type: BOOLEAN |
709 | - :default: 1 |
710 | - |
711 | -If you meet contention of ``&dict_operation_lock``, setting 0 reduces the contention. But 0 disables to update ``Data_free:`` of ``SHOW TABLE STATUS``. |
712 | - |
713 | - |
714 | -.. variable:: innodb_use_sys_stats_table |
715 | - |
716 | - :type: BOOLEAN |
717 | - :default: 0 |
718 | - |
719 | - |
720 | -If this option is enabled, |XtraDB| uses the ``SYS_STATS`` system table to store statistics of table indexes. Also, when |InnoDB| opens a table for the first time, it loads the statistics from ``SYS_STATS`` instead of sampling index pages. If you use a high ``stats_sample_pages`` value, the first open of a table is expensive. In such a case, this option will help. Note: This option may cause less frequent updating of statistics. So, you should intentionally use the ``ANALYZE TABLE`` command more often. |
721 | - |
722 | -(This variable was introduced in release 5.1.50-11.4.) |
723 | - |
724 | - |
725 | -INFORMATION_SCHEMA Tables |
726 | -========================= |
727 | - |
728 | -Two new tables were introduced by this feature. |
729 | - |
730 | -.. table:: INFORMATION_SCHEMA.INNODB_TABLE_STATS |
731 | - |
732 | - Shows table statistics information of dictionary cached. |
733 | - |
734 | - :column table_schema: Database name of the table. |
735 | - :column table_name: Table name. |
736 | - :column rows: estimated number of all rows. |
737 | - :column clust_size: cluster index (table/primary key) size in number of pages. |
738 | - :column other_size: Other index (non primary key) size in number of pages. |
739 | - :column modified: Internal counter to judge whether statistics recalculation should be done. |
740 | - |
741 | -If the value of modified column exceeds “rows / 16” or 2000000000, the statistics recalculation is done when ``innodb_stats_auto_update == 1``. We can estimate the oldness of the statistics by this value. |
742 | - |
743 | -.. table:: INFORMATION_SCHEMA.INNODB_INDEX_STATS |
744 | - |
745 | - Shows index statistics information of dictionary cached. |
746 | - |
747 | - :column table_schema: Database name of the table. |
748 | - :column table_name: Table name. |
749 | - :column index_name: Index name. |
750 | - :column fields: How many fields the index key has. (it is internal structure of |InnoDB|, it may be larger than the ``CREATE TABLE``). |
751 | - :column rows_per_key: Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...). |
752 | - :column index_total_pages: Number of index pages. |
753 | - :column index_leaf_pages: Number of leaf pages. |
754 | - |
755 | -In releases before 5.5.8-20.0, these fields had different names: |
756 | - |
757 | - * ``rows_per_key`` was ``row_per_keys`` |
758 | - |
759 | - * ``index_total_pages`` was ``index_size`` |
760 | - |
761 | - * ``index_leaf_pages`` was ``leaf_pages`` |
762 | - |
763 | -Example |
764 | -======= |
765 | - |
766 | -This example uses the same data to Bug #30423 of |MySQL|. |
767 | - |
768 | -``[innodb_stats_method = nulls_equal (default behavior of |InnoDB|)]`` :: |
769 | - |
770 | - mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id; |
771 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ |
772 | - | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
773 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ |
774 | - | 1 | SIMPLE | orgs | index | NULL | orgs$org_id | 4 | NULL | 128 | Using index | |
775 | - | 1 | SIMPLE | sa_opportunities | ref | sa_opp$org_id | sa_opp$org_id | 5 | test2.orgs.org_id | 5751 | Using index | |
776 | - | 1 | SIMPLE | contacts | ref | contacts$org_id | contacts$org_id | 5 | test2.orgs.org_id | 23756 | Using index | |
777 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+ |
778 | - 3 rows in set (0.00 sec) |
779 | - |
780 | -``[innodb_stats_method = nulls_unequal or nulls_ignored]`` :: |
781 | - |
782 | - mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id; |
783 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ |
784 | - | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
785 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ |
786 | - | 1 | SIMPLE | orgs | index | NULL | orgs$org_id | 4 | NULL | 128 | Using index | |
787 | - | 1 | SIMPLE | sa_opportunities | ref | sa_opp$org_id | sa_opp$org_id | 5 | test2.orgs.org_id | 1 | Using index | |
788 | - | 1 | SIMPLE | contacts | ref | contacts$org_id | contacts$org_id | 5 | test2.orgs.org_id | 1 | Using index | |
789 | - +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+ |
790 | - 3 rows in set (0.00 sec) |
791 | - <example of information_schema> |
792 | - |
793 | - mysql> select * from information_schema.innodb_table_stats; |
794 | - +------------------------+-------+------------+------------+----------+ |
795 | - | table_name | rows | clust_size | other_size | modified | |
796 | - +------------------------+-------+------------+------------+----------+ |
797 | - | test/sa_opportunities2 | 11175 | 21 | 11 | 0 | |
798 | - | test/orgs2 | 128 | 1 | 0 | 0 | |
799 | - | test/contacts2 | 47021 | 97 | 97 | 0 | |
800 | - +------------------------+-------+------------+------------+----------+ |
801 | - 3 rows in set (0.00 sec) |
802 | - |
803 | - mysql> select * from information_schema.innodb_index_stats; |
804 | - +------------------------+-----------------+--------+--------------+------------+------------+ |
805 | - | table_name | index_name | fields | row_per_keys | index_size | leaf_pages | |
806 | - +------------------------+-----------------+--------+--------------+------------+------------+ |
807 | - | test/sa_opportunities2 | GEN_CLUST_INDEX | 1 | 1 | 21 | 20 | |
808 | - | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 | |
809 | - | test/orgs2 | orgs$org_id | 1 | 1 | 1 | 1 | |
810 | - | test/contacts2 | GEN_CLUST_INDEX | 1 | 1 | 97 | 80 | |
811 | - | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 | |
812 | - +------------------------+-----------------+--------+--------------+------------+------------+ |
813 | - 5 rows in set (0.00 sec) |
814 | - |
815 | -Other reading |
816 | -============= |
817 | - |
818 | - * `InnoDB Table/Index stats <http://www.mysqlperformanceblog.com/2010/03/20/|InnoDB|-tableindex-stats/>`_ |
819 | - |
820 | |
821 | === removed file 'doc/source/diagnostics/mysql_syslog.rst' |
822 | --- doc/source/diagnostics/mysql_syslog.rst 2013-03-05 12:46:43 +0000 |
823 | +++ doc/source/diagnostics/mysql_syslog.rst 1970-01-01 00:00:00 +0000 |
824 | @@ -1,43 +0,0 @@ |
825 | -.. _mysql_syslog: |
826 | - |
827 | -====================================== |
828 | - Log All Client Commands (``syslog``) |
829 | -====================================== |
830 | - |
831 | -When enabled, this feature causes all commands run by the command line client to be logged to syslog. If you want to enable this option permanently, add it to the [mysql] group in my.cnf. |
832 | - |
833 | -Version Specific Information |
834 | -============================ |
835 | - |
836 | - * :rn:`5.5.8-20.0`: |
837 | - Full functionality available. |
838 | - |
839 | -Other Information |
840 | -================= |
841 | - |
842 | - * Author / Origin: |
843 | - Percona |
844 | - |
845 | -Client Variables |
846 | -================ |
847 | - |
848 | -.. variable:: syslog |
849 | - |
850 | - :cli: Yes |
851 | - :conf: Yes |
852 | - :server: No |
853 | - :scope: Global |
854 | - :dyn: Yes |
855 | - :vartype: Boolean |
856 | - :default: OFF |
857 | - :range: ON/OFF |
858 | - |
859 | -The variable enables (ON)/disables (OFF) logging to syslog. |
860 | - |
861 | - |
862 | -Other Reading |
863 | -============= |
864 | - |
865 | - * http://en.wikipedia.org/wiki/Syslog |
866 | - |
867 | - * http://tools.ietf.org/html/rfc5424 |
868 | |
869 | === removed file 'doc/source/diagnostics/response_time_distribution.rst' |
870 | --- doc/source/diagnostics/response_time_distribution.rst 2013-03-05 12:46:43 +0000 |
871 | +++ doc/source/diagnostics/response_time_distribution.rst 1970-01-01 00:00:00 +0000 |
872 | @@ -1,266 +0,0 @@ |
873 | -.. _response_time_distribution: |
874 | - |
875 | -============================ |
876 | - Response Time Distribution |
877 | -============================ |
878 | - |
879 | -The slow query log provides exact information about queries that take a long time to execute. However, sometimes there are a large number of queries that each take a very short amount of time to execute. This feature provides a tool for analyzing that information by counting and displaying the number of queries according to the the length of time they took to execute. The user can define time intervals that divide the range 0 to positive infinity into smaller intervals and then collect the number of commands whose execution times fall into each of those intervals. |
880 | - |
881 | -Note that in a replication environment, the server will not take into account *any* queries executed by the slave's SQL thread (whether they are slow or not) for the time distribution unless the log_slow_slave_statements variable is set. |
882 | - |
883 | -The feature isn't implemented in all versions of the server. The variable :variable:`have_response_time_distribution` indicates whether or not it is implemented in the server you are running. |
884 | - |
885 | -Each interval is described as: :: |
886 | - |
887 | -(range_base ^ n; range_base ^ (n+1)] |
888 | - |
889 | -The range_base is some positive number (see Limitations). The interval is defined as the difference between two nearby powers of the range base. |
890 | - |
891 | -For example, if the range base=10, we have the following intervals: :: |
892 | - |
893 | - (0; 10 ^ -6], (10 ^ -6; 10 ^ -5], (10 ^ -5; 10 ^ -4], ..., (10 ^ -1; 10 ^1], (10^1; 10^2]...(10^7; positive infinity] |
894 | - |
895 | -or :: |
896 | - |
897 | - (0; 0.000001], (0.000001; 0.000010], (0.000010; 0.000100], ..., (0.100000; 1.0]; (1.0; 10.0]...(1000000; positive infinity] |
898 | - |
899 | -For each interval, a count is made of the queries with execution times that fell into that interval. |
900 | - |
901 | -You can select the range of the intervals by changing the range base. For example, for base range=2 we have the following intervals: :: |
902 | - |
903 | - (0; 2 ^ -19], (2 ^ -19; 2 ^ -18], (2 ^ -18; 2 ^ -17], ..., (2 ^ -1; 2 ^1], (2 ^ 1; 2 ^ 2]...(2 ^ 25; positive infinity] |
904 | - |
905 | -or :: |
906 | - |
907 | - (0; 0.000001], (0.000001, 0.000003], ..., (0.25; 0.5], (0.5; 2], (2; 4]...(8388608; positive infinity] |
908 | - |
909 | -Small numbers look strange (i.e., don't look like powers of 2), because we lose precision on division when the ranges are calculated at runtime. In the resulting table, you look at the high boundary of the range. |
910 | - |
911 | -For example, you may see: :: |
912 | - |
913 | - +----------------+-------+------------+ |
914 | - | time | count | total | |
915 | - +----------------+-------+------------| |
916 | - | 0.000001 | 0 | 0.000000 | |
917 | - | 0.000010 | 17 | 0.000094 | |
918 | - | 0.000100 | 4301 | 0.236555 | |
919 | - | 0.001000 | 1499 | 0.824450 | |
920 | - | 0.010000 | 14851 | 81.680502 | |
921 | - | 0.100000 | 8066 | 443.635693 | |
922 | - | 1.000000 | 0 | 0.000000 | |
923 | - | 10.000000 | 0 | 0.000000 | |
924 | - | 100.000000 | 1 | 55.937094 | |
925 | - | 1000.000000 | 0 | 0.000000 | |
926 | - | 10000.000000 | 0 | 0.000000 | |
927 | - | 100000.000000 | 0 | 0.000000 | |
928 | - | 1000000.000000 | 0 | 0.000000 | |
929 | - | TOO LONG QUERY | 0 | 0.000000 | |
930 | - +----------------+-------+------------+ |
931 | - |
932 | -This means there were: :: |
933 | - |
934 | - * 17 queries with 0.000001 < query execution time < = 0.000010 seconds; total execution time of the 17 queries = 0.000094 seconds |
935 | - |
936 | - * 4301 queries with 0.000010 < query execution time < = 0.000100 seconds; total execution time of the 4301 queries = 0.236555 seconds |
937 | - |
938 | - * 1499 queries with 0.000100 < query execution time < = 0.001000 seconds; total execution time of the 1499 queries = 0.824450 seconds |
939 | - |
940 | - * 14851 queries with 0.001000 < query execution time < = 0.010000 seconds; total execution time of the 14851 queries = 81.680502 seconds |
941 | - |
942 | - * 8066 queries with 0.010000 < query execution time < = 0.100000 seconds; total execution time of the 8066 queries = 443.635693 seconds |
943 | - |
944 | - * 1 query with 10.000000 < query execution time < = 100.0000 seconds; total execution time of the 1 query = 55.937094 seconds |
945 | - |
946 | -Usage |
947 | -===== |
948 | - |
949 | -SELECT |
950 | ------- |
951 | - |
952 | -You can get the distribution using the query: :: |
953 | - |
954 | - > SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME |
955 | - time count total |
956 | - 0.000001 0 0.000000 |
957 | - 0.000010 0 0.000000 |
958 | - 0.000100 1 0.000072 |
959 | - 0.001000 0 0.000000 |
960 | - 0.010000 0 0.000000 |
961 | - 0.100000 0 0.000000 |
962 | - 1.000000 0 0.000000 |
963 | - 10.000000 8 47.268416 |
964 | - 100.000000 0 0.000000 |
965 | - 1000.000000 0 0.000000 |
966 | - 10000.000000 0 0.000000 |
967 | - 100000.000000 0 0.000000 |
968 | - 1000000.000000 0 0.000000 |
969 | - TOO LONG QUERY 0 0.000000 |
970 | - |
971 | -You can write a complex query like: :: |
972 | - |
973 | - SELECT c.count, c.time, |
974 | - (SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, |
975 | - (SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, |
976 | - (SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count |
977 | - FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; |
978 | - |
979 | -**Note:** If :variable:`query_response_time_stats` is ON, the execution times for these two ``SELECT`` queries will also be collected. |
980 | - |
981 | -SHOW |
982 | ----- |
983 | - |
984 | -Also, you can use this syntax: :: |
985 | - |
986 | - > SHOW QUERY_RESPONSE_TIME; |
987 | - time count total |
988 | - 0.000001 0 0.000000 |
989 | - 0.000010 0 0.000000 |
990 | - 0.000100 1 0.000072 |
991 | - 0.001000 0 0.000000 |
992 | - 0.010000 0 0.000000 |
993 | - 0.100000 0 0.000000 |
994 | - 1.000000 0 0.000000 |
995 | - 10.000000 8 47.268416 |
996 | - 100.000000 0 0.000000 |
997 | - 1000.000000 0 0.000000 |
998 | - 10000.000000 0 0.000000 |
999 | - 100000.000000 0 0.000000 |
1000 | - 1000000.000000 0 0.000000 |
1001 | - TOO LONG QUERY 0 0.000000 |
1002 | - |
1003 | -**Note:** The execution time for the SHOW query will also be collected. |
1004 | - |
1005 | -FLUSH |
1006 | ------ |
1007 | - |
1008 | -Flushing can be done with: :: |
1009 | - |
1010 | - FLUSH QUERY_RESPONSE_TIME; |
1011 | - |
1012 | -``FLUSH`` does two things: |
1013 | - |
1014 | - * Clears the collected times from the :table:`QUERY_RESPONSE_TIME` table |
1015 | - |
1016 | - * Reads the value of :variable:`query_response_time_range_base` and uses it to set the range base for the table |
1017 | - |
1018 | -**Note:** The execution time for the ``FLUSH`` query will also be collected. |
1019 | - |
1020 | -Stored procedures |
1021 | ------------------ |
1022 | - |
1023 | -Stored procedure calls count as single query. |
1024 | - |
1025 | -Collect time point |
1026 | ------------------- |
1027 | - |
1028 | -Time is collected after query execution completes (before clearing data structures). |
1029 | - |
1030 | -Limitations |
1031 | -=========== |
1032 | - |
1033 | - * ``String width for seconds`` |
1034 | - |
1035 | - * Value: 7 |
1036 | - |
1037 | - * Compile-time variable: ``QUERY_RESPONSE_TIME_STRING_POSITIVE_POWER_LENGTH`` |
1038 | - |
1039 | - * ``String width for microseconds`` |
1040 | - |
1041 | - * Value: 6 |
1042 | - |
1043 | - * Compile-time variable: ``QUERY_RESPONSE_TIME_STRING_NEGATIVE_POWER_LENGTH`` |
1044 | - |
1045 | - * Minimum range base |
1046 | - |
1047 | - * Value: 2 |
1048 | - |
1049 | - * Compile-time variable: ``QUERY_RESPONSE_TIME_MINIMUM_BASE`` |
1050 | - |
1051 | - * Minimum range base |
1052 | - |
1053 | - * Value: 1000 |
1054 | - |
1055 | - * Compile-time variable: ``QUERY_RESPONSE_TIME_MAXIMUM_BASE`` |
1056 | - |
1057 | - * Minimum time interval |
1058 | - |
1059 | - * Value: 1 microsecond |
1060 | - |
1061 | - * Maximum time interval |
1062 | - |
1063 | - * Value: 9999999 seconds |
1064 | - |
1065 | -Version Specific Information |
1066 | -============================ |
1067 | - |
1068 | - * 5.5.8-20.0: |
1069 | - Introduced variable variable:`have_response_time_distribution`. |
1070 | - |
1071 | - * 5.5.8-20.0: |
1072 | - Introduced variable variable:`query_response_time_stats`. |
1073 | - |
1074 | -System Variables |
1075 | -================ |
1076 | - |
1077 | -.. variable:: have_response_time_distribution |
1078 | - |
1079 | - :version 5.5.8-20.0: Introduced. |
1080 | - :scope: Global |
1081 | - :dyn: No |
1082 | - :vartype: Boolean |
1083 | - :default: YES |
1084 | - :range: YES/NO |
1085 | - |
1086 | -Contains the value YES if the server you're running supports this feature; contains NO if the feature is not supported. It is enabled by default. |
1087 | - |
1088 | - |
1089 | -.. variable:: query_response_time_range_base |
1090 | - |
1091 | - :cli: Yes |
1092 | - :conf: Yes |
1093 | - :scope: Global |
1094 | - :dyn: Yes |
1095 | - :vartype: Numeric |
1096 | - :default: 10 |
1097 | - :range: 2-1000 |
1098 | - |
1099 | -Sets up the logarithm base for the scale. |
1100 | - |
1101 | -**Note:** The variable takes effect only after this command has been executed: :: |
1102 | - |
1103 | - FLUSH QUERY_RESPONSE_TIME; |
1104 | - |
1105 | -.. variable:: query_response_time_stats |
1106 | - |
1107 | - :version 5.5.8-20.0: Introduced. |
1108 | - :cli: Yes |
1109 | - :conf: Yes |
1110 | - :scope: Global |
1111 | - :dyn: Yes |
1112 | - :vartype: Boolean |
1113 | - :default: OFF |
1114 | - :range: ON/OFF |
1115 | - |
1116 | -This variable enables and disables collection of query times if the feature is available in the server that's running. If the value of variable :variable:`have_response_time_distribution` is YES, then you can enable collection of query times by setting this variable to ON using ``SET GLOBAL``. |
1117 | - |
1118 | - Prior to release 5.5.8-20.0, this variable was named :variable:`enable_query_response_time_stats`. |
1119 | - |
1120 | - |
1121 | -INFORMATION_SCHEMA Tables |
1122 | -========================= |
1123 | - |
1124 | -.. table:: INFORMATION_SCHEMA.QUERY_RESPONSE_TIME |
1125 | - |
1126 | - :column VARCHAR TIME: |
1127 | - :column INT(11) COUNT: |
1128 | - :column VARCHAR TOTAL: |
1129 | - |
1130 | -Implementation Details |
1131 | -====================== |
1132 | - |
1133 | -Implementation details on this feature are provided here. |
1134 | - |
1135 | -Related Reading |
1136 | -=============== |
1137 | - |
1138 | - * `Blueprint about Response Time Distribution <https://blueprints.launchpad.net/percona-server/+spec/response-time-distribution>`_ |
1139 | |
1140 | === removed file 'doc/source/diagnostics/show_engines.rst' |
1141 | --- doc/source/diagnostics/show_engines.rst 2013-03-05 12:46:43 +0000 |
1142 | +++ doc/source/diagnostics/show_engines.rst 1970-01-01 00:00:00 +0000 |
1143 | @@ -1,40 +0,0 @@ |
1144 | -.. _show_engines: |
1145 | - |
1146 | -====================== |
1147 | - Show Storage Engines |
1148 | -====================== |
1149 | - |
1150 | -This feature changes the comment field displayed when the ``SHOW STORAGE ENGINES`` command is executed and |XtraDB| is the storage engine. |
1151 | - |
1152 | -Before the Change: :: |
1153 | - |
1154 | - mysql> show storage engines; |
1155 | - +------------+---------+----------------------------------------------------------------+--------------+------+------------+ |
1156 | - | Engine | Support | Comment | Transactions | XA | Savepoints | |
1157 | - +------------+---------+----------------------------------------------------------------+--------------+------+------------+ |
1158 | - | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | |
1159 | - ... |
1160 | - +------------+---------+----------------------------------------------------------------+--------------+------+------------+ |
1161 | - |
1162 | -After the Change: :: |
1163 | - |
1164 | - mysql> show storage engines; |
1165 | - +------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |
1166 | - | Engine | Support | Comment | Transactions | XA | Savepoints | |
1167 | - +------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |
1168 | - | InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | |
1169 | - ... |
1170 | - +------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |
1171 | - |
1172 | -Version-Specific Information |
1173 | -============================ |
1174 | - |
1175 | - * 5.5.8-20.0: |
1176 | - Full functionality available. |
1177 | - |
1178 | -Other Information |
1179 | -================= |
1180 | - |
1181 | - * Author / Origin: |
1182 | - Percona |
1183 | - |
1184 | |
1185 | === removed file 'doc/source/diagnostics/slow_extended_55.rst' |
1186 | --- doc/source/diagnostics/slow_extended_55.rst 2013-03-05 12:46:43 +0000 |
1187 | +++ doc/source/diagnostics/slow_extended_55.rst 1970-01-01 00:00:00 +0000 |
1188 | @@ -1,377 +0,0 @@ |
1189 | -.. _slow_extended_55: |
1190 | - |
1191 | -================ |
1192 | - Slow Query Log |
1193 | -================ |
1194 | - |
1195 | -This feature adds microsecond time resolution and additional statistics to the slow query log output. It lets you enable or disable the slow query log at runtime, adds logging for the slave SQL thread, and adds fine-grained control over what and how much to log into the slow query log. |
1196 | - |
1197 | -The ability to log queries with microsecond precision is essential for measuring the work the |MySQL| server performs. The standard slow query log in |MySQL| 5.0 has only 1-second granularity, which is too coarse for all but the slowest queries. |MySQL| 5.1 has microsecond resolution, but does not have the extra information about query execution that is included in the |Percona Server|. |
1198 | - |
1199 | -You can use Maatkit``s mk-query-digest tool to aggregate similar queries together and report on those that consume the most execution time. |
1200 | - |
1201 | - |
1202 | -Version Specific Information |
1203 | -============================ |
1204 | - |
1205 | - * 5.5.8-20.0: |
1206 | - Added values ``profiling`` and ``profiling_use_getrusage`` to variable log_slow_verbosity. |
1207 | - |
1208 | - * 5.5.10-20.1: |
1209 | - * Renamed variable :variable:`slow_query_log_timestamp_always` to :variable:`slow_query_log_timestamp_always`. |
1210 | - |
1211 | - * Renamed variable :variable:`slow_query_log_microseconds_timestamp` to :variable:`slow_query_log_timestamp_precision`. |
1212 | - |
1213 | - * Renamed variable :variable:`use_global_log_slow_control` to :variable:`slow_query_log_use_global_control`. |
1214 | - |
1215 | -Other Information |
1216 | -================= |
1217 | - |
1218 | - * Author / Origin: |
1219 | - Maciej Dobrzanski, Percona |
1220 | - |
1221 | -System Variables |
1222 | -================ |
1223 | - |
1224 | -.. variable:: log_slow_filter |
1225 | - |
1226 | - :cli: Yes |
1227 | - :conf: Yes |
1228 | - :scope: Global, Session |
1229 | - :dyn: Yes |
1230 | - |
1231 | -Filters the slow log by the query's execution plan. The value is a comma-delimited string, and can contain any combination of the following values: |
1232 | - |
1233 | - * ``qc_miss``: |
1234 | - The query was not found in the query cache. |
1235 | - |
1236 | - * ``full_scan``: |
1237 | - The query performed a full table scan. |
1238 | - |
1239 | - * ``full_join``: |
1240 | - The query performed a full join (a join without indexes). |
1241 | - |
1242 | - * ``tmp_table``: |
1243 | - The query created an implicit internal temporary table. |
1244 | - |
1245 | - * ``tmp_table_on_disk``: |
1246 | - The query``s temporary table was stored on disk. |
1247 | - |
1248 | - * ``filesort``: |
1249 | - The query used a filesort. |
1250 | - |
1251 | - * ``filesort_on_disk``: |
1252 | - The filesort was performed on disk. |
1253 | - |
1254 | -Values are OR'ed together. If the string is empty, then the filter is disabled. If it is not empty, then queries will only be logged to the slow log if their execution plan matches one of the types of plans present in the filter. |
1255 | - |
1256 | -For example, to log only queries that perform a full table scan, set the value to ``full_scan``. To log only queries that use on-disk temporary storage for intermediate results, set the value to ``tmp_table_on_disk,filesort_on_disk``. |
1257 | - |
1258 | -.. variable:: log_slow_rate_type |
1259 | - |
1260 | - :cli: Yes |
1261 | - :conf: Yes |
1262 | - :scope: Global |
1263 | - :dyn: Yes |
1264 | - :vartype: Enumerated |
1265 | - :default: ``session`` |
1266 | - :range: ``session``, ``query`` |
1267 | - |
1268 | -Specifies semantic of :variable:`log_slow_rate_limit` - ``session`` or ``query``. |
1269 | - |
1270 | -.. variable:: log_slow_rate_limit |
1271 | - |
1272 | - :cli: Yes |
1273 | - :conf: Yes |
1274 | - :scope: Global, session |
1275 | - :dyn: Yes |
1276 | - |
1277 | -Behavior of this variable depends from :variable:`log_slow_rate_type`. |
1278 | - |
1279 | -Specifies that only a fraction of ``session/query`` should be logged. Logging is enabled for every nth ``session/query``. By default, n is 1, so logging is enabled for every ``session/query``. Please note: when :variable:`log_slow_rate_type` is ``session`` rate limiting is disabled for the replication thread. |
1280 | - |
1281 | -Logging all queries might consume I/O bandwidth and cause the log file to grow large. |
1282 | - * When :variable:`log_slow_rate_type` is ``session``, this option lets you log full sessions, so you have complete records of sessions for later analysis; but you can rate-limit the number of sessions that are logged. Note that this feature will not work well if your application uses any type of connection pooling or persistent connections. Note that you change :variable:`log_slow_rate_limit` in ``session`` mode, you should reconnect for get effect. |
1283 | - |
1284 | - * When :variable:`log_slow_rate_type` is ``query``, this option lets you log just some queries for later analysis. For example, if you set the value to 100, then one percona of queryies will logged. |
1285 | - |
1286 | -Note that every query has global unique ``query_id`` and every connection can has it own (session) :variable:``log_slow_rate_limit``. |
1287 | -Decision "log or no" calculated in following manner: |
1288 | - |
1289 | - * if ``log_slow_rate_limit`` is 0 - log every query |
1290 | - |
1291 | - * If ``log_slow_rate_limit`` > 0 - log query when (``query_id`` % ``log_slow_rate_limit``) is zero. |
1292 | - |
1293 | -This allows flexible setup logging behavior. |
1294 | - |
1295 | -For example, if you set the value to 100, then one percent of ``sessions/queries`` will be logged. |
1296 | - |
1297 | -.. variable:: log_slow_slave_statements |
1298 | - |
1299 | - :cli: Yes |
1300 | - :conf: Yes |
1301 | - :scope: Global, session |
1302 | - :dyn: Yes (in 5.1 releases only) |
1303 | - |
1304 | -Specifies that queries replayed by the slave SQL thread on a |MySQL| slave will be logged. The standard |MySQL| server will not log any queries executed by the slave``s SQL thread. |
1305 | - |
1306 | -To start the logging from the slave thread, you should change the global value: set global :variable:`log_slow_slave_statements` ``=ON``; and then execute: ``STOP SLAVE; START SLAVE;``. This will destroy and recreate the slave SQL thread, so it will see the newly set global value. |
1307 | - |
1308 | -To stop the logging from the slave thread, you should just change the global value: set global :variable:`log_slow_slave_statements` ``=OFF``; the logging stops immediately. |
1309 | - |
1310 | - |
1311 | -.. variable:: log_slow_sp_statements |
1312 | - |
1313 | - :cli: Yes |
1314 | - :conf: Yes |
1315 | - :scope: Global |
1316 | - :dyn: Yes |
1317 | - :vartype: Boolean |
1318 | - :default: TRUE |
1319 | - :range: TRUE/FALSE |
1320 | - |
1321 | -If ``TRUE``, statements executed by stored procedures are logged to the slow if it is open. |
1322 | - |
1323 | -.. variable:: log_slow_verbosity |
1324 | - |
1325 | - :cli: Yes |
1326 | - :conf: Yes |
1327 | - :scope: Global, session |
1328 | - :dyn: Yes |
1329 | - :version 5.5.8-20.0: Added ``profiling`` and ``profiling_use_getrusage`` |
1330 | - |
1331 | -Specifies how much information to include in your slow log. The value is a comma-delimited string, and can contain any combination of the following values: |
1332 | - |
1333 | - * ``microtime``: |
1334 | - Log queries with microsecond precision (mandatory). |
1335 | - |
1336 | - * ``query_plan``: |
1337 | - Log information about the query``s execution plan (optional). |
1338 | - |
1339 | - * ``innodb``: |
1340 | - Log |InnoDB| statistics (optional). |
1341 | - |
1342 | - * ``full``: |
1343 | - Equivalent to all other values OR``ed together. |
1344 | - |
1345 | - * ``profiling``: |
1346 | - Enables profiling of all queries in all connections. |
1347 | - |
1348 | - * ``profiling_use_getrusage``: |
1349 | - Enables usage of the getrusage function. |
1350 | - |
1351 | -Values are OR``ed together. |
1352 | - |
1353 | -For example, to enable microsecond query timing and |InnoDB| statistics, set this option to ``microtime,innodb``. To turn all options on, set the option to ``full``. |
1354 | - |
1355 | -.. variable:: long_query_time |
1356 | - |
1357 | - :cli: Yes |
1358 | - :conf: Yes |
1359 | - :scope: Global, session |
1360 | - :dyn: Yes |
1361 | - |
1362 | -Specifies the time threshold for filtering queries out of the slow query log. The unit of time is seconds. This option has the same meaning as in a standard |MySQL| server, with the following changes: |
1363 | - |
1364 | -The option accepts fractional values. If set to 0.5, for example, queries longer than 1/2 second will be logged. |
1365 | - |
1366 | -If the value is set to 0, then all queries are logged. This is different from the standard |MySQL| build, where a value of 0 disables logging. |
1367 | -Before version 1.01 of this feature, the value was an integer, and the unit of time was microseconds, not seconds. |
1368 | - |
1369 | -.. variable:: slow_query_log_timestamp_always |
1370 | - |
1371 | - :cli: Yes |
1372 | - :conf: Yes |
1373 | - :scope: Global |
1374 | - :dyn: Yes |
1375 | - :vartype: Boolean |
1376 | - :default: FALSE |
1377 | - :range: TRUE/FALSE |
1378 | - :version 5.5.10-20.1: Introduced (renamed from :variable:`log_slow_timestamp_every`) |
1379 | - |
1380 | -If ``TRUE``, a timestamp is printed on every slow log record. Multiple records may have the same time. |
1381 | - |
1382 | -**NOTE:** This variable has been renamed from log_slow_timestamp_every since 5.5.10-20.1. |
1383 | - |
1384 | -.. variable:: slow_query_log_timestamp_precision |
1385 | - |
1386 | - :version 5.5.10-20.1: Introduced (renamed from ``slow_query_log_microseconds_timestamp``) |
1387 | - :cli: Yes |
1388 | - :conf: Yes |
1389 | - :scope: Global |
1390 | - :dyn: Yes |
1391 | - :vartype: Enumerated |
1392 | - :default: ``second`` |
1393 | - :range: ``second``, ``microsecond`` |
1394 | - |
1395 | -Normally, entries to the slow query log are in seconds precision, in this format: :: |
1396 | - |
1397 | - # Time: 090402 9:23:36 # User@Host: XXX @ XXX [10.X.X.X] |
1398 | - |
1399 | -If :variable:`slow_query_log_timestamp_precision` ``=microsecond``, entries to the slow query log are in microsecond precision, in this format: :: |
1400 | - |
1401 | - # Time: 090402 9:23:36.123456 # User@Host: XXX @ XXX [10.X.X.X] |
1402 | - |
1403 | -**NOTE:** This variable has been renamed from :variable:`slow_query_log_microseconds_timestamp` since 5.5.10-20.1. |
1404 | - |
1405 | - |
1406 | -.. variable:: slow_query_log_use_global_control |
1407 | - |
1408 | - :cli: Yes |
1409 | - :conf: Yes |
1410 | - :scope: Global |
1411 | - :dyn: Yes |
1412 | - :default: None |
1413 | - :version 5.5.10-20.1: Introduced (renamed from :variable:`log_slow_timestamp_every`) |
1414 | - |
1415 | -Specifies which variables have global scope instead of local. Value is a “flag” variable - you can specify multiple values separated by commas |
1416 | - |
1417 | - * ``none``: |
1418 | - All variables use local scope |
1419 | - |
1420 | - * ``log_slow_filter``: |
1421 | - Global variable :variable:`log_slow_filter` has effect (instead of local) |
1422 | - |
1423 | - * ``log_slow_rate_limit``: |
1424 | - Global variable :variable:`log_slow_rate_limit` has effect (instead of local) |
1425 | - |
1426 | - * ``log_slow_verbosity``: |
1427 | - Global variable :variable:`log_slow_verbosity` has effect (instead of local) |
1428 | - |
1429 | - * ``long_query_time``: |
1430 | - Global variable :variable:`long_query_time` has effect (instead of local) |
1431 | - |
1432 | - * ``min_examined_row_limit``: |
1433 | - Global variable ``min_examined_row_limit`` has effect (instead of local) |
1434 | - |
1435 | - * ``all`` |
1436 | - Global variables has effect (instead of local) |
1437 | - |
1438 | -**NOTE:** This variable has been renamed from :variable:`log_slow_timestamp_every` since 5.5.10-20.1. |
1439 | - |
1440 | - |
1441 | -Other Information |
1442 | -================= |
1443 | - |
1444 | -Changes to the Log Format |
1445 | -------------------------- |
1446 | - |
1447 | -The feature adds more information to the slow log output. Here is a sample log entry: :: |
1448 | - |
1449 | - # User@Host: mailboxer[mailboxer] @ [192.168.10.165] |
1450 | - # Thread_id: 11167745 Schema: board |
1451 | - # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No |
1452 | - # Filesort: Yes Disk_filesort: No Merge_passes: 0 |
1453 | - # Query_time: 0.000659 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 30 Rows_affected: 0 Rows_read: 30 |
1454 | - # innodb_IO_r_ops: 1 innodb_IO_r_bytes: 16384 innodb_IO_r_wait: 0.028487 |
1455 | - # innodb_rec_lock_wait: 0.000000 innodb_queue_wait: 0.000000 |
1456 | - # innodb_pages_distinct: 5 |
1457 | - select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = ``710575`` |
1458 | - |
1459 | -Another example (:variable:`log_slow_verbosity` ``=profiling``): :: |
1460 | - |
1461 | - # Query_time: 4.555235 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1 |
1462 | - # Profile_starting: 4.554799 Profile_starting_cpu: 0.000000 Profile_checking_permissions: 0.000095 Profile_checking_permissions_cpu: 0.000000 Profile_Opening_tables: 0.000088 Profile_Opening_tables_cpu: 0.000000 Profile_init: 0.000056 Profile_init_cpu: 0.000000 Profile_optimizing: 0.000046 Profile_optimizing_cpu: 0.000000 Profile_executing: 0.000098 Profile_executing_cpu: 0.000000 Profile_end: 0.000049 Profile_end_cpu: 0.000000 Profile_query_end: 0.000045 Profile_query_end_cpu: 0.000000 Profile_freeing_items: 0.000084 Profile_freeing_items_cpu: 0.000000 Profile_logging_slow_query: 0.000045 Profile_logging_slow_query_cpu: 0.000000 |
1463 | - # Profile_total: 4.555405 Profile_total_cpu: 0.000000 |
1464 | - insert into teee4 select * from teee4 limit 10000000; |
1465 | - |
1466 | -Connection and Schema Identifier |
1467 | --------------------------------- |
1468 | - |
1469 | -Each slow log entry now contains a connection identifier, so you can trace all the queries coming from a single connection. This is the same value that is shown in the Id column in ``SHOW FULL PROCESSLIST`` or returned from the ``CONNECTION_ID()`` function. |
1470 | - |
1471 | -Each entry also contains a schema name, so you can trace all the queries whose default database was set to a particular schema. :: |
1472 | - |
1473 | - # Thread_id: 11167745 Schema: board |
1474 | - |
1475 | -Microsecond Time Resolution and Extra Row Information |
1476 | ------------------------------------------------------ |
1477 | - |
1478 | -This is the original functionality offered by the ``microslow`` feature. ``Query_time`` and ``Lock_time`` are logged with microsecond resolution. |
1479 | - |
1480 | -The feature also adds information about how many rows were examined for ``SELECT`` queries, and how many were analyzed and affected for ``UPDATE``, ``DELETE``, and ``INSERT`` queries, :: |
1481 | - |
1482 | - # Query_time: 0.000659 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 30 Rows_affected: 0 Rows_read: 30 |
1483 | - |
1484 | -Values and context: |
1485 | - |
1486 | - * ``Rows_examined``: |
1487 | - Number of rows scanned - ``SELECT`` |
1488 | - |
1489 | - * ``Rows_affected``: |
1490 | - Number of rows changed - ``UPDATE``, ``DELETE``, ``INSERT`` |
1491 | - |
1492 | - * ``Rows_read``: |
1493 | - Number of rows read - ``UPDATE``, ``DELETE``, ``INSERT`` |
1494 | - |
1495 | -Memory Footprint |
1496 | ----------------- |
1497 | - |
1498 | -The feature provides information about the amount of bytes sent for the result of the query and the number of temporary tables created for its execution - differentiated by whether they were created on memory or on disk - with the total number of bytes used by them. :: |
1499 | - |
1500 | - # Bytes_sent: 8053 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 950528 |
1501 | - |
1502 | -Values and context: |
1503 | - |
1504 | - * ``Bytes_sent``: |
1505 | - The amount of bytes sent for the result of the query |
1506 | - |
1507 | - * ``Tmp_tables``: |
1508 | - Number of temporary tables created on memory for the query |
1509 | - |
1510 | - * ``Tmp_disk_tables``: |
1511 | - Number of temporary tables created on disk for the query |
1512 | - |
1513 | - * ``Tmp_table_sizes``: |
1514 | - Total Size in bytes for all temporary tables used in the query |
1515 | - |
1516 | - |
1517 | -Query Plan Information |
1518 | ----------------------- |
1519 | - |
1520 | -Each query can be executed in various ways. For example, it may use indexes or do a full table scan, or a temporary table may be needed. These are the things that you can usually see by running ``EXPLAIN`` on the query. The feature will now allow you to see the most important facts about the execution in the log file. :: |
1521 | - |
1522 | - # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No |
1523 | - # Filesort: Yes Disk_filesort: No Merge_passes: 0 |
1524 | - |
1525 | -The values and their meanings are documented with the :variable:`log_slow_filter` option. |
1526 | - |
1527 | -|InnoDB| Usage Information |
1528 | --------------------------- |
1529 | - |
1530 | -The final part of the output is the |InnoDB| usage statistics. |MySQL| currently shows many per-session statistics for operations with ``SHOW SESSION STATUS``, but that does not include those of |InnoDB|, which are always global and shared by all threads. This feature lets you see those values for a given query. :: |
1531 | - |
1532 | - # innodb_IO_r_ops: 1 innodb_IO_r_bytes: 16384 innodb_IO_r_wait: 0.028487 |
1533 | - # innodb_rec_lock_wait: 0.000000 innodb_queue_wait: 0.000000 |
1534 | - # innodb_pages_distinct: 5 |
1535 | - |
1536 | -Values: |
1537 | - |
1538 | - * ``innodb_IO_r_ops``: |
1539 | - Counts the number of page read operations scheduled. The actual number of read operations may be different, but since this can be done asynchronously, there is no good way to measure it. |
1540 | - |
1541 | - * ``innodb_IO_r_bytes``: |
1542 | - Similar to innodb_IO_r_ops, but the unit is bytes. |
1543 | - |
1544 | - * ``innodb_IO_r_wait``: |
1545 | - Shows how long (in seconds) it took |InnoDB| to actually read the data from storage. |
1546 | - |
1547 | - * ``innodb_rec_lock_wait``: |
1548 | - Shows how long (in seconds) the query waited for row locks. |
1549 | - |
1550 | - * ``innodb_queue_wait``: |
1551 | - Shows how long (in seconds) the query spent either waiting to enter the |InnoDB| queue or inside that queue waiting for execution. |
1552 | - |
1553 | - * ``innodb_pages_distinct``: |
1554 | - Counts approximately the number of unique pages the query accessed. The approximation is based on a small hash array representing the entire buffer pool, because it could take a lot of memory to map all the pages. The inaccuracy grows with the number of pages accessed by a query, because there is a higher probability of hash collisions. |
1555 | - |
1556 | -If the query did not use |InnoDB| tables, that information is written into the log instead of the above statistics. |
1557 | - |
1558 | -Related Reading |
1559 | -=============== |
1560 | - |
1561 | - * http://www.mysqlperformanceblog.com/2009/02/10/impact-of-logging-on-mysql%E2%80%99s-performance/ |
1562 | - |
1563 | - * `log_slow_filter Usage <http://www.mysqlperformanceblog.com/2008/09/22/finding-what-created_tmp_disk_tables-with-log_slow_filter/>`_ |
1564 | - |
1565 | - * `Blueprint in Launchpad <https://blueprints.launchpad.net/percona-server/+spec/microseconds-in-query-log>`_ |
1566 | |
1567 | === removed file 'doc/source/diagnostics/user_stats.rst' |
1568 | --- doc/source/diagnostics/user_stats.rst 2013-03-05 12:46:43 +0000 |
1569 | +++ doc/source/diagnostics/user_stats.rst 1970-01-01 00:00:00 +0000 |
1570 | @@ -1,242 +0,0 @@ |
1571 | -.. _user_stats: |
1572 | - |
1573 | -================= |
1574 | - User Statistics |
1575 | -================= |
1576 | - |
1577 | -This feature adds several ``INFORMATION_SCHEMA`` tables, several commands, and the userstat variable. The tables and commands can be used to understand the server activity better and identify the source of the load. |
1578 | - |
1579 | -The functionality is disabled by default, and must be enabled by setting ``userstat`` to ``ON``. It works by keeping several hash tables in memory. To avoid contention over global mutexes, each connection has its own local statistics, which are occasionally merged into the global statistics, and the local statistics are then reset to 0. |
1580 | - |
1581 | - |
1582 | -Version Specific Information |
1583 | -============================ |
1584 | - |
1585 | - * :rn:`5.5.10-20.1`: |
1586 | - Renamed variable :variable:`userstat_running` to :variable:`userstat`. |
1587 | - |
1588 | -Other Information |
1589 | -================= |
1590 | - |
1591 | - * Author/Origin: |
1592 | - *Google*; *Percona* added the ``INFORMATION_SCHEMA`` tables and the :variable:`userstat_running` variable. |
1593 | - |
1594 | -System Variables |
1595 | -================ |
1596 | - |
1597 | -.. variable:: userstat_running |
1598 | - |
1599 | - :version 5.5.10-20.1: Renamed to :variable:`userstat` |
1600 | - :cli: Yes |
1601 | - :conf: Yes |
1602 | - :scope: Global |
1603 | - :dyn: Yes |
1604 | - :vartype: BOOLEAN |
1605 | - :default: OFF |
1606 | - :range: ON/OFF |
1607 | - |
1608 | -Enables or disables collection of statistics. The default is ``OFF``, meaning no statistics are gathered. This is to ensure that the statistics collection doesn't cause any extra load on the server unless desired. |
1609 | - |
1610 | - |
1611 | -INFORMATION_SCHEMA Tables |
1612 | -========================= |
1613 | - |
1614 | -.. table:: INFORMATION_SCHEMA.CLIENT_STATISTICS |
1615 | - |
1616 | - :column CLIENT: The IP address or hostname from which the connection originated. |
1617 | - :column TOTAL_CONNECTIONS: The number of connections created for this client. |
1618 | - :column CONCURRENT_CONNECTIONS: The number of concurrent connections for this client. |
1619 | - :column CONNECTED_TIME: The cumulative number of seconds elapsed while there were connections from this client. |
1620 | - :column BUSY_TIME: The cumulative number of seconds there was activity on connections from this client. |
1621 | - :column CPU_TIME: The cumulative CPU time elapsed, in seconds, while servicing this client``s connections. |
1622 | - :column BYTES_RECEIVED: The number of bytes received from this client's connections. |
1623 | - :column BYTES_SENT: The number of bytes sent to this client's connections. |
1624 | - :column BINLOG_BYTES_WRITTEN: The number of bytes written to the binary log from this client's connections. |
1625 | - :column ROWS_FETCHED: The number of rows fetched by this client's connections. |
1626 | - :column ROWS_UPDATED: The number of rows updated by this client's connections. |
1627 | - :column TABLE_ROWS_READ: The number of rows read from tables by this client's connections. (It may be different from ``ROWS_FETCHED``.) |
1628 | - :column SELECT_COMMANDS: The number of ``SELECT`` commands executed from this client's connections. |
1629 | - :column UPDATE_COMMANDS: The number of ``UPDATE`` commands executed from this client's connections. |
1630 | - :column OTHER_COMMANDS: The number of other commands executed from this client's connections. |
1631 | - :column COMMIT_TRANSACTIONS: The number of ``COMMIT`` commands issued by this client's connections. |
1632 | - :column ROLLBACK_TRANSACTIONS: The number of ``ROLLBACK`` commands issued by this client's connections. |
1633 | - :column DENIED_CONNECTIONS: The number of connections denied to this client. |
1634 | - :column LOST_CONNECTIONS: The number of this client's connections that were terminated uncleanly. |
1635 | - :column ACCESS_DENIED: The number of times this client's connections issued commands that were denied. |
1636 | - :column EMPTY_QUERIES: The number of times this client's connections sent empty queries to the server. |
1637 | - |
1638 | -This table holds statistics about client connections. The Percona version of the feature restricts this table's visibility to users who have the ``SUPER`` or ``PROCESS`` privilege. |
1639 | - |
1640 | -Example: :: |
1641 | - |
1642 | - mysql> SELECT * FROM INFORMATION_SCHEMA.CLIENT_STATISTICS\G |
1643 | - *************************** 1. row *************************** |
1644 | - CLIENT: 10.1.12.30 |
1645 | - TOTAL_CONNECTIONS: 20 |
1646 | - CONCURRENT_CONNECTIONS: 0 |
1647 | - CONNECTED_TIME: 0 |
1648 | - BUSY_TIME: 93 |
1649 | - CPU_TIME: 48 |
1650 | - BYTES_RECEIVED: 5031 |
1651 | - BYTES_SENT: 276926 |
1652 | - BINLOG_BYTES_WRITTEN: 217 |
1653 | - ROWS_FETCHED: 81 |
1654 | - ROWS_UPDATED: 0 |
1655 | - TABLE_ROWS_READ: 52836023 |
1656 | - SELECT_COMMANDS: 26 |
1657 | - UPDATE_COMMANDS: 1 |
1658 | - OTHER_COMMANDS: 145 |
1659 | - COMMIT_TRANSACTIONS: 1 |
1660 | - ROLLBACK_TRANSACTIONS: 0 |
1661 | - DENIED_CONNECTIONS: 0 |
1662 | - LOST_CONNECTIONS: 0 |
1663 | - ACCESS_DENIED: 0 |
1664 | - EMPTY_QUERIES: 0 |
1665 | - |
1666 | - |
1667 | -.. table:: INFORMATION_SCHEMA.INDEX_STATISTICS |
1668 | - |
1669 | - :column TABLE_SCHEMA: The schema (database) name. |
1670 | - :column TABLE_NAME: The table name. |
1671 | - :column INDEX_NAME: The index name (as visible in ``SHOW CREATE TABLE``). |
1672 | - :column ROWS_READ: The number of rows read from this index. |
1673 | - |
1674 | -This table shows statistics on index usage. An older version of the feature contained a single column that had the ``TABLE_SCHEMA``, ``TABLE_NAME`` and ``INDEX_NAME`` columns concatenated together. The |Percona| version of the feature separates these into three columns. Users can see entries only for tables to which they have ``SELECT`` access. |
1675 | - |
1676 | -This table makes it possible to do many things that were difficult or impossible previously. For example, you can use it to find unused indexes and generate DROP commands to remove them. |
1677 | - |
1678 | -Example: :: |
1679 | - |
1680 | - mysql> SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS |
1681 | - WHERE TABLE_NAME='tables_priv'; |
1682 | - +--------------+-----------------------+--------------------+-----------+ |
1683 | - | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | |
1684 | - +--------------+-----------------------+--------------------+-----------+ |
1685 | - | mysql | tables_priv | PRIMARY | 2 | |
1686 | - +--------------+-----------------------+--------------------+-----------+ |
1687 | - |
1688 | - |
1689 | - |
1690 | -.. table:: INFORMATION_SCHEMA.TABLE_STATISTICS |
1691 | - |
1692 | - :column TABLE_SCHEMA: The schema (database) name. |
1693 | - :column TABLE_NAME: The table name. |
1694 | - :column ROWS_READ: The number of rows read from the table. |
1695 | - :column ROWS_CHANGED: The number of rows changed in the table. |
1696 | - :column ROWS_CHANGED_X_INDEXES: The number of rows changed in the table, multiplied by the number of indexes changed. |
1697 | - |
1698 | -This table is similar in function to the ``INDEX_STATISTICS`` table. |
1699 | - |
1700 | -Example: :: |
1701 | - |
1702 | - mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS |
1703 | - WHERE TABLE_NAME=``tables_priv``; |
1704 | - +--------------+-------------------------------+-----------+--------------+------------------------+ |
1705 | - | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES | |
1706 | - +--------------+-------------------------------+-----------+--------------+------------------------+ |
1707 | - | mysql | tables_priv | 2 | 0 | 0 | |
1708 | - +--------------+-------------------------------+-----------+--------------+------------------------+ |
1709 | - |
1710 | - |
1711 | -.. table:: INFORMATION_SCHEMA.THREAD_STATISTICS |
1712 | - |
1713 | - :column THREAD_ID: int(21) |
1714 | - :column TOTAL_CONNECTIONS: int(21) |
1715 | - :column CONCURRENT_CONNECTIONS: int(21) |
1716 | - :column CONNECTED_TIME: int(21) |
1717 | - :column BUSY_TIME: int(21) |
1718 | - :column CPU_TIME: int(21) |
1719 | - :column BYTES_RECEIVED: int(21) |
1720 | - :column BYTES_SENT: int(21) |
1721 | - :column BINLOG_BYTES_WRITTEN: int(21) |
1722 | - :column ROWS_FETCHED: int(21) |
1723 | - :column ROWS_UPDATED: int(21) |
1724 | - :column TABLE_ROWS_READ: int(21) |
1725 | - :column SELECT_COMMANDS: int(21) |
1726 | - :column UPDATE_COMMANDS: int(21) |
1727 | - :column OTHER_COMMANDS: int(21) |
1728 | - :column COMMIT_TRANSACTIONS: int(21) |
1729 | - :column ROLLBACK_TRANSACTIONS: int(21) |
1730 | - :column DENIED_CONNECTIONS: int(21) |
1731 | - :column LOST_CONNECTIONS: int(21) |
1732 | - :column ACCESS_DENIED: int(21) |
1733 | - :column EMPTY_QUERIES: int(21) |
1734 | - |
1735 | -.. table:: INFORMATION_SCHEMA.USER_STATISTICS |
1736 | - |
1737 | - :column USER: The username. The value ``#mysql_system_user#`` appears when there is no username (such as for the slave SQL thread). |
1738 | - :column TOTAL_CONNECTIONS: The number of connections created for this user. |
1739 | - :column CONCURRENT_CONNECTIONS: The number of concurrent connections for this user. |
1740 | - :column CONNECTED_TIME: The cumulative number of seconds elapsed while there were connections from this user. |
1741 | - :column BUSY_TIME: The cumulative number of seconds there was activity on connections from this user. |
1742 | - :column CPU_TIME: The cumulative CPU time elapsed, in seconds, while servicing this user's connections. |
1743 | - :column BYTES_RECEIVED: The number of bytes received from this user's connections. |
1744 | - :column BYTES_SENT: The number of bytes sent to this user's connections. |
1745 | - :column BINLOG_BYTES_WRITTEN: The number of bytes written to the binary log from this user's connections. |
1746 | - :column ROWS_FETCHED: The number of rows fetched by this user's connections. |
1747 | - :column ROWS_UPDATED: The number of rows updated by this user's connections. |
1748 | - :column TABLE_ROWS_READ: The number of rows read from tables by this user's connections. (It may be different from ``ROWS_FETCHED``.) |
1749 | - :column SELECT_COMMANDS: The number of ``SELECT`` commands executed from this user's connections. |
1750 | - :column UPDATE_COMMANDS: The number of ``UPDATE`` commands executed from this user's connections. |
1751 | - :column OTHER_COMMANDS: The number of other commands executed from this user's connections. |
1752 | - :column COMMIT_TRANSACTIONS: The number of ``COMMIT`` commands issued by this user's connections. |
1753 | - :column ROLLBACK_TRANSACTIONS: The number of ``ROLLBACK`` commands issued by this user's connections. |
1754 | - :column DENIED_CONNECTIONS: The number of connections denied to this user. |
1755 | - :column LOST_CONNECTIONS: The number of this user's connections that were terminated uncleanly. |
1756 | - :column ACCESS_DENIED: The number of times this user's connections issued commands that were denied. |
1757 | - :column EMPTY_QUERIES: The number of times this user's connections sent empty queries to the server. |
1758 | - |
1759 | -This table contains information about user activity. The |Percona| version of the patch restricts this table's visibility to users who have the ``SUPER`` or ``PROCESS`` privilege. |
1760 | - |
1761 | -The table gives answers to questions such as which users cause the most load, and whether any users are being abusive. It also lets you measure how close to capacity the server may be. For example, you can use it to find out whether replication is likely to start falling behind. |
1762 | - |
1763 | -Example: :: |
1764 | - |
1765 | - mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G |
1766 | - *************************** 1. row *************************** |
1767 | - USER: root |
1768 | - TOTAL_CONNECTIONS: 5592 |
1769 | - CONCURRENT_CONNECTIONS: 0 |
1770 | - CONNECTED_TIME: 6844 |
1771 | - BUSY_TIME: 179 |
1772 | - CPU_TIME: 72 |
1773 | - BYTES_RECEIVED: 603344 |
1774 | - BYTES_SENT: 15663832 |
1775 | - BINLOG_BYTES_WRITTEN: 217 |
1776 | - ROWS_FETCHED: 9793 |
1777 | - ROWS_UPDATED: 0 |
1778 | - TABLE_ROWS_READ: 52836023 |
1779 | - SELECT_COMMANDS: 9701 |
1780 | - UPDATE_COMMANDS: 1 |
1781 | - OTHER_COMMANDS: 2614 |
1782 | - COMMIT_TRANSACTIONS: 1 |
1783 | - ROLLBACK_TRANSACTIONS: 0 |
1784 | - DENIED_CONNECTIONS: 0 |
1785 | - LOST_CONNECTIONS: 0 |
1786 | - ACCESS_DENIED: 0 |
1787 | - EMPTY_QUERIES: 0 |
1788 | - |
1789 | -Commands Provided |
1790 | -================= |
1791 | - |
1792 | - * ``FLUSH CLIENT_STATISTICS`` |
1793 | - |
1794 | - * ``FLUSH INDEX_STATISTICS`` |
1795 | - |
1796 | - * ``FLUSH TABLE_STATISTICS`` |
1797 | - |
1798 | - * ``FLUSH THREAD_STATISTICS`` |
1799 | - |
1800 | - * ``FLUSH USER_STATISTICS`` |
1801 | - |
1802 | -These commands discard the specified type of stored statistical information. |
1803 | - |
1804 | - * ``SHOW CLIENT_STATISTICS`` |
1805 | - * ``SHOW INDEX_STATISTICS`` |
1806 | - * ``SHOW TABLE_STATISTICS`` |
1807 | - * ``SHOW THREAD_STATISTICS`` |
1808 | - * ``SHOW USER_STATISTICS`` |
1809 | - |
1810 | -These commands are another way to display the information you can get from the ``INFORMATION_SCHEMA`` tables. The commands accept ``WHERE`` clauses. They also accept but ignore ``LIKE`` clauses. |
1811 | - |
1812 | - |
1813 | |
1814 | === removed file 'doc/source/flexibility/buff_read_ahead_area.rst' |
1815 | --- doc/source/flexibility/buff_read_ahead_area.rst 2013-03-05 12:46:43 +0000 |
1816 | +++ doc/source/flexibility/buff_read_ahead_area.rst 1970-01-01 00:00:00 +0000 |
1817 | @@ -1,36 +0,0 @@ |
1818 | -.. _buff_read_ahead_area: |
1819 | - |
1820 | -==================================== |
1821 | - Fixed Size for the Read Ahead Area |
1822 | -==================================== |
1823 | - |
1824 | -|InnoDB| dynamically calculates the size of the read-ahead area in case it has to trigger its read-ahead algorithm. When the workload involves heavy I/O operations, this size is computed so frequently that it has a non-negligeable impact on the CPU usage. |
1825 | - |
1826 | -This variable only depends on the size of the buffer pool set by the :variable:`innodb_buffer_pool_size` variable, and as soon as the buffer pool has a size properly greater than 1024 pages (or 16 MB), it is always 64. With this change, its value is fixed to 64, thus removing a bottleneck experienced by some users. |
1827 | - |
1828 | -Please note that the minimum allowed value for the |InnoDB| buffer pool is de facto set to 32 MB. |
1829 | - |
1830 | -This change is a port of the feature from Facebook: |
1831 | - |
1832 | - * http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3538 |
1833 | - |
1834 | - |
1835 | -Version Specific Information |
1836 | -============================ |
1837 | - |
1838 | - * :rn:`5.5.8-20.0` : |
1839 | - Full functionality available. |
1840 | - |
1841 | -Other Information |
1842 | -================= |
1843 | - |
1844 | - * Author/Origin: |
1845 | |
1846 | - |
1847 | - * Bugs fixed: |
1848 | - :bug:`606811` |
1849 | - |
1850 | -Other Reading |
1851 | -============= |
1852 | - |
1853 | - * `BUF_READ_AHEAD_AREA Bottleneck <http://www.facebook.com/notes/|MySQL|facebook/using-pmp-to-double-|MySQL|-throughput-part-2/405092575932>`_ |
1854 | |
1855 | === removed file 'doc/source/flexibility/improved_memory_engine.rst' |
1856 | --- doc/source/flexibility/improved_memory_engine.rst 2013-03-05 12:46:43 +0000 |
1857 | +++ doc/source/flexibility/improved_memory_engine.rst 1970-01-01 00:00:00 +0000 |
1858 | @@ -1,190 +0,0 @@ |
1859 | -.. _improved_memory_engine: |
1860 | - |
1861 | -==================================== |
1862 | - ``Improved MEMORY`` Storage Engine |
1863 | -==================================== |
1864 | - |
1865 | -As of ``MySQL`` 5.5.15, a *Fixed Row Format* (``FRF``) is still being used in the ``MEMORY`` storage engine. The fixed row format imposes restrictions on the type of columns as it assigns on advance a limited amount of memory per row. This renders a ``VARCHAR`` field in a ``CHAR`` field in practice and makes impossible to have a ``TEXT`` or ``BLOB`` field with that engine implementation. |
1866 | - |
1867 | -To overcome this limitation, the *Improved MEMORY Storage Engine* is introduced in this release for supporting **true** ``VARCHAR``, ``VARBINARY``, ``TEXT`` and ``BLOB`` fields in ``MEMORY`` tables. |
1868 | - |
1869 | -This implementation is based on the *Dynamic Row Format* (``DFR``) introduced by the `mysql-heap-dynamic-rows <http://code.google.com/p/mysql-heap-dynamic-rows/>`_ patch. |
1870 | - |
1871 | -``DFR`` is used to store column values in a variable-length form, thus helping to decrease memory footprint of those columns and making possible ``BLOB`` and ``TEXT`` fields and real ``VARCHAR`` and ``VARBINARY``. |
1872 | - |
1873 | -Unlike the fixed implementation, each column value in ``DRF`` only uses as much space as required. This is, for variable-length values, up to 4 bytes is used to store the actual value length, and then only the necessary number of blocks is used to store the value. |
1874 | - |
1875 | -Rows in ``DFR`` are represented internally by multiple memory blocks, which means that a single row can consist of multiple blocks organized into one set. Each row occupies at least one block, there can not be multiple rows within a single block. Block size can be configured when creating a table (see below). |
1876 | - |
1877 | -This ``DFR`` implementation has two caveats regarding to ordering and indexes. |
1878 | - |
1879 | -Caveats |
1880 | -======= |
1881 | - |
1882 | -Ordering of Rows |
1883 | ----------------- |
1884 | - |
1885 | -In the absence of ``ORDER BY``, records may be returned in a different order than the previous ``MEMORY`` implementation. |
1886 | - |
1887 | -This is not a bug. Any application relying on a specific order without an ``ORDER BY`` clause may deliver unexpected results. A specific order without ``ORDER BY`` is a side effect of a storage engine and query optimizer implementation which may and will change between minor |MySQL| releases. |
1888 | - |
1889 | - |
1890 | -Indexing |
1891 | --------- |
1892 | - |
1893 | -It is currently impossible to use indexes on ``BLOB`` columns due to some limitations of the *Dynamic Row Format*. Trying to create such an index will fail with the following error: :: |
1894 | - |
1895 | - BLOB column '<name>' can't be used in key specification with the used table type. |
1896 | - |
1897 | -Restrictions |
1898 | -============ |
1899 | - |
1900 | -For performance reasons, a mixed solution is implemented: the fixed format is used at the beginning of the row, while the dynamic one is used for the rest of it. |
1901 | - |
1902 | -All values for columns used in indexes are stored in fixed format at the first block of the row, then the following columns are handled with ``DRF``. |
1903 | - |
1904 | -This sets two restrictions to tables: |
1905 | - |
1906 | - * the order of the fields and therefore, |
1907 | - |
1908 | - * the minimum size of the block used in the table. |
1909 | - |
1910 | -Ordering of Columns |
1911 | -------------------- |
1912 | - |
1913 | -The columns used in fixed format must be defined before the dynamic ones in the ``CREATE TABLE`` statement. If this requirement is not met, the engine will not be able to add blocks to the set for these fields and they will be treated as fixed. |
1914 | - |
1915 | -Minimum Block Size |
1916 | ------------------- |
1917 | - |
1918 | -The block size has to be big enough to store all fixed-length information in the first block. If not, the ``CREATE TABLE`` or ``ALTER TABLE`` statements will fail (see below). |
1919 | - |
1920 | -Setting Row Format |
1921 | -================== |
1922 | - |
1923 | -Taking the restrictions into account, the *Improved MEMORY Storage Engine* will choose ``DRF`` over ``FRF`` at the moment of creating the table according to following criteria: |
1924 | - |
1925 | - * There is an implicit request of the user in the column types **OR** |
1926 | - |
1927 | - * There is an explicit request of the user **AND** the overhead incurred by ``DFR`` is beneficial. |
1928 | - |
1929 | -Implicit Request |
1930 | ----------------- |
1931 | - |
1932 | -The implicit request by the user is taken when there is at least one ``BLOB`` or ``TEXT`` column in the table definition. If there are none of these columns and no relevant option is given, the engine will choose ``FRF``. |
1933 | - |
1934 | -For example, this will yield the use of the dynamic format: :: |
1935 | - |
1936 | - mysql> CREATE TABLE t1 (f1 VARCHAR(32), f2 TEXT, PRIMARY KEY (f1)) ENGINE=HEAP; |
1937 | - |
1938 | -While this will not: :: |
1939 | - |
1940 | - mysql> CREATE TABLE t1 (f1 VARCHAR(16), f2 VARCHAR(16), PRIMARY KEY (f1)) ENGINE=HEAP; |
1941 | - |
1942 | -Explicit Request |
1943 | ----------------- |
1944 | - |
1945 | -The explicit request is set with one of the following options in the ``CREATE TABLE`` statement: |
1946 | - |
1947 | - * ``KEY_BLOCK_SIZE = <value>`` |
1948 | - |
1949 | - * Requests the DFR with the specified block size (in bytes) |
1950 | - |
1951 | - * ``ROW_FORMAT = DYNAMIC`` |
1952 | - |
1953 | - * Requests the dynamic format with the default block size (256 bytes) |
1954 | - |
1955 | -Despite its name, the ``KEY_BLOCK_SIZE`` option refers to a block size used to store data rather then indexes. The reason for this is that an existing ``CREATE TABLE`` option is reused to avoid introducing new ones. |
1956 | - |
1957 | -*The Improved MEMORY Engine* checks whether the specified block size is large enough to keep all key column values. If it is too small, table creation will abort with an error. |
1958 | - |
1959 | -After ``DRF`` is requested explicitly and there are no ``BLOB`` or ``TEXT`` columns in the table definition, the *Improved MEMORY Engine* will check if using the dynamic format provides any space saving benefits as compared to the fixed one: |
1960 | - |
1961 | - * if the fixed row length is less than the dynamic block size (plus the dynamic row overhead - platform dependent) **OR** |
1962 | - |
1963 | - * there isn't any variable-length columns in the table or ``VARCHAR`` fields are declared with length 31 or less, |
1964 | - |
1965 | -the engine will revert to the fixed format as it is more space efficient in such case. The row format being used by the engine can be checked using ``SHOW TABLE STATUS``. |
1966 | - |
1967 | -Examples |
1968 | -======== |
1969 | - |
1970 | -On a 32-bit platform: :: |
1971 | - |
1972 | - mysql> CREATE TABLE t1 (f1 VARCHAR(32), f2 VARCHAR(32), f3 VARCHAR(32), f4 VARCHAR(32), |
1973 | - PRIMARY KEY (f1)) KEY_BLOCK_SIZE=124 ENGINE=HEAP ROW_FORMAT=DYNAMIC; |
1974 | - |
1975 | - mysql> SHOW TABLE STATUS LIKE 't1'; |
1976 | - Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment |
1977 | - t1 MEMORY 10 Dynamic 0 X 0 X 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL row_format=DYNAMIC KEY_BLOCK_SIZE=124 |
1978 | - |
1979 | -On a 64-bit platform: :: |
1980 | - |
1981 | - mysql> CREATE TABLE t1 (f1 VARCHAR(32), f2 VARCHAR(32), f3 VARCHAR(32), f4 VARCHAR(32), |
1982 | - PRIMARY KEY (f1)) KEY_BLOCK_SIZE=124 ENGINE=HEAP ROW_FORMAT=DYNAMIC; |
1983 | - |
1984 | - mysql> SHOW TABLE STATUS LIKE 't1'; |
1985 | - Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment |
1986 | - t1 MEMORY 10 Fixed 0 X 0 X 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL row_format=DYNAMIC KEY_BLOCK_SIZE=124 |
1987 | - |
1988 | -Implementation Details |
1989 | -====================== |
1990 | - |
1991 | -|MySQL| *MEMORY* tables keep data in arrays of fixed-size chunks. These chunks are organized into two groups of ``HP_BLOCK`` structures: |
1992 | - |
1993 | - * ``group1`` contains indexes, with one ``HP_BLOCK`` per key (part of ``HP_KEYDEF``), |
1994 | - |
1995 | - * ``group2`` contains record data, with a single ``HP_BLOCK`` for all records. |
1996 | - |
1997 | -While columns used in indexes are usually small, other columns in the table may need to accommodate larger data. Typically, larger data is placed into ``VARCHAR`` or ``BLOB`` columns. |
1998 | - |
1999 | -*The Improved MEMORY Engine* implements the concept of dataspace, ``HP_DATASPACE``, which incorporates the ``HP_BLOCK`` structures for the record data, adding more information for managing variable-sized records. |
2000 | - |
2001 | -Variable-size records are stored in multiple “chunks”, which means that a single record of data (a database “row”) can consist of multiple chunks organized into one “set”, contained in ``HP_BLOCK`` structures. |
2002 | - |
2003 | -In variable-size format, one record is represented as one or many chunks depending on the actual data, while in fixed-size mode, one record is always represented as one chunk. The index structures would always point to the first chunk in the chunkset. |
2004 | - |
2005 | -Variable-size records are necessary only in the presence of variable-size columns. The *Improved Memory Engine* will be looking for ``BLOB`` or ``VARCHAR`` columns with a declared length of 32 or more. If no such columns are found, the table will be switched to the fixed-size format. You should always put such columns at the end of the table definition in order to use the variable-size format. |
2006 | - |
2007 | -Whenever data is being inserted or updated in the table, the *Improved Memory Engine* will calculate how many chunks are necessary. |
2008 | - |
2009 | -For ``INSERT`` operations, the engine only allocates new chunksets in the recordspace. For ``UPDATE`` operations it will modify the length of the existing chunkset if necessary, unlinking unnecessary chunks at the end, or allocating and adding more if a larger length is needed. |
2010 | - |
2011 | -When writing data to chunks or copying data back to a record, fixed-size columns are copied in their full format, while ``VARCHAR`` and ``BLOB`` columns are copied based on their actual length, skipping any ``NULL`` values. |
2012 | - |
2013 | -When allocating a new chunkset of N chunks, the engine will try to allocate chunks one-by-one, linking them as they become allocated. For allocating a single chunk, it will attempt to reuse a deleted (freed) chunk. If no free chunks are available, it will try to allocate a new area inside a ``HP_BLOCK``. |
2014 | - |
2015 | -When freeing chunks, the engine will place them at the front of a free list in the dataspace, each one containing a reference to the previously freed chunk. |
2016 | - |
2017 | -The allocation and contents of the actual chunks varies between fixed and variable-size modes: |
2018 | - |
2019 | - * Format of a fixed-size chunk: |
2020 | - |
2021 | - * ``uchar[]`` |
2022 | - |
2023 | - * With ``sizeof=chunk_dataspace_length``, but at least ``sizeof(uchar*)`` bytes. It keeps actual data or pointer to the next deleted chunk, where ``chunk_dataspace_length`` equals to full record length |
2024 | - |
2025 | - * ``uchar`` |
2026 | - |
2027 | - * Status field (1 means “in use”, 0 means “deleted”) |
2028 | - |
2029 | - * Format of a variable-size chunk: |
2030 | - |
2031 | - * ``uchar[]`` |
2032 | - |
2033 | - * With ``sizeof=chunk_dataspace_length``, but at least ``sizeof(uchar*)`` bytes. It keeps actual data or pointer to the next deleted chunk, where ``chunk_dataspace_length`` is set according to table's ``key_block_size`` |
2034 | - |
2035 | - * ``uchar*`` |
2036 | - |
2037 | - * Pointer to the next chunk in this chunkset, or NULL for the last chunk |
2038 | - |
2039 | - * ``uchar`` |
2040 | - |
2041 | - * Status field (1 means “first”, 0 means “deleted”, 2 means “linked”) |
2042 | - |
2043 | -Total chunk length is always aligned to the next ``sizeof(uchar*)``. |
2044 | - |
2045 | -See Also |
2046 | -======== |
2047 | - |
2048 | - * `Dynamic row format for MEMORY tables <http://www.mysqlperformanceblog.com/2011/09/06/dynamic-row-format-for-memory-tables/>`_ |
2049 | |
2050 | === removed file 'doc/source/flexibility/innodb_fast_shutdown.rst' |
2051 | --- doc/source/flexibility/innodb_fast_shutdown.rst 2013-03-05 12:46:43 +0000 |
2052 | +++ doc/source/flexibility/innodb_fast_shutdown.rst 1970-01-01 00:00:00 +0000 |
2053 | @@ -1,36 +0,0 @@ |
2054 | -.. _innodb_fast_shutdown: |
2055 | - |
2056 | -=============== |
2057 | - Fast Shutdown |
2058 | -=============== |
2059 | - |
2060 | -Some |InnoDB| / |XtraDB| threads which perform various background activities are in the sleep state most of the time. They only wake up every few seconds to perform their tasks. They also check whether the server is in the shutdown phase, and if not, they go to the sleep state again. That means there could be a noticeable delay (up to 10 seconds) after a shutdown command and before all |InnoDB| / |XtraDB| threads actually notice this and terminate. This is not a big problem for most production servers, because a shutdown of a heavily loaded server normally takes much longer than 10 seconds. |
2061 | - |
2062 | -The problem, however, had a significant impact on running the regression test suite, because it performs a lot of server restarts during its execution and also because there is not so much to do when shutting a test server. So it makes even less sense to wait up to 10 seconds. |
2063 | - |
2064 | -This change modifies that behavior to make the sleep waiting interruptible, so that when the server is told to shutdown, threads no longer wait until the end of their sleep interval. This results in a measurably faster test suite execution (~40% in some cases). |
2065 | - |
2066 | -The change was contributed by Kristian Nielsen. |
2067 | - |
2068 | -Version Specific Information |
2069 | -============================ |
2070 | - |
2071 | - * 5.5.8-20 |
2072 | - Full functionality available. |
2073 | - |
2074 | -Other Information |
2075 | -================= |
2076 | - |
2077 | - * Author / Origin: |
2078 | - Kristian Nielsen |
2079 | - |
2080 | - * Bugs fixed: |
2081 | - :bug:`643463` |
2082 | - |
2083 | -Other reading |
2084 | -============= |
2085 | - |
2086 | - * `How to decrease InnoDB shutdown times <http://www.mysqlperformanceblog.com/2009/04/15/how-to-decrease-innodb-shutdown-times/>`_ |
2087 | - |
2088 | - * `How long InnoDB shutdown may take <http://www.mysqlperformanceblog.com/2010/09/02/how-long-innodb-shutdown-may-take/>`_ |
2089 | - |
2090 | |
2091 | === removed file 'doc/source/flexibility/innodb_files_extend.rst' |
2092 | --- doc/source/flexibility/innodb_files_extend.rst 2013-03-05 12:46:43 +0000 |
2093 | +++ doc/source/flexibility/innodb_files_extend.rst 1970-01-01 00:00:00 +0000 |
2094 | @@ -1,25 +0,0 @@ |
2095 | -.. _innodb_files_extend: |
2096 | - |
2097 | -================================ |
2098 | - Support of Multiple Page Sizes |
2099 | -================================ |
2100 | - |
2101 | -In standard |InnoDB|, the size of the read-ahead area is computed dynamically, but it always has the same value. This change makes the size fixed, avoiding unuseful computations. |
2102 | - |
2103 | -This feature adds a new system variable for setting it. |
2104 | - |
2105 | - |
2106 | -System Variables |
2107 | -================ |
2108 | - |
2109 | -.. variable:: innodb_page_size |
2110 | - |
2111 | - :cli: Yes |
2112 | - :conf: Yes |
2113 | - :scope: |
2114 | - :dyn: No |
2115 | - :vartype: ULONG |
2116 | - :default: 1 « 14 |
2117 | - :range: 1 « 12 to 1 « ``UNIV_PAGE_SIZE_SHIFT_MAX`` |
2118 | - |
2119 | -**EXPERIMENTAL**: The universal page size of the database. Changing for an existing database is not supported. Use at your own risk! |
2120 | |
2121 | === removed file 'doc/source/flexibility/log_warnings_suppress.rst' |
2122 | --- doc/source/flexibility/log_warnings_suppress.rst 2013-03-05 12:46:43 +0000 |
2123 | +++ doc/source/flexibility/log_warnings_suppress.rst 1970-01-01 00:00:00 +0000 |
2124 | @@ -1,49 +0,0 @@ |
2125 | -.. _log_warning_suppress: |
2126 | - |
2127 | -=========================== |
2128 | - Suppress Warning Messages |
2129 | -=========================== |
2130 | - |
2131 | -This feature is intended to provide a general mechanism (using ``log_warnings_silence``) to disable certain warning messages to the log file. Currently, it is only implemented for disabling message #1592 warnings. |
2132 | - |
2133 | - |
2134 | -Version Specific Information |
2135 | -============================ |
2136 | - |
2137 | - * 5.5.8-20.0: |
2138 | - System variable :variable:`log_warnings_silence` introduced. |
2139 | - |
2140 | - * 5.5.10-20.1: |
2141 | - Renamed variable :variable:`log_warnings_silence` to :variable:`log_warnings_suppress`. |
2142 | - |
2143 | -.. variable:: log_warnings_suppress |
2144 | - |
2145 | - :version 5.5.8-20.0: Introduced. |
2146 | - :version 5.5.10-20.1: Renamed. |
2147 | - :cli: Yes |
2148 | - :conf: Yes |
2149 | - :scope: Global |
2150 | - :dyn: Yes |
2151 | - :vartype: SET |
2152 | - :default: ``(empty string)`` |
2153 | - :range: ``(empty string)``, ``1592`` |
2154 | - |
2155 | -This variable was added in beta release ``5.5.8-20.0`` as :variable:`log_warnings_silence` and renamed in release 5.5.10-20.1. |
2156 | - |
2157 | -It is intended to provide a more general mechanism for disabling warnings than existed previously with variable suppress_log_warning_1592. |
2158 | - |
2159 | -When set to the empty string, no warnings are disabled. When set to ``1592``, warning #1592 messages (unsafe statement for binary logging) are suppressed. |
2160 | - |
2161 | -In the future, the ability to optionally disable additional warnings may also be added. |
2162 | - |
2163 | - |
2164 | -Related Reading |
2165 | -=============== |
2166 | - |
2167 | - * `MySQL bug 42851 <http://bugs.mysql.com/bug.php?id=42851>`_ |
2168 | - |
2169 | - * `MySQL InnoDB replication <http://dev.mysql.com/doc/refman/5.1/en/innodb-and-mysql-replication.html>`_ |
2170 | - |
2171 | - * `InnoDB Startup Options and System Variables <http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html>`_ |
2172 | - |
2173 | - * `InnoDB Error Handling <http://dev.mysql.com/doc/refman/5.1/en/innodb-error-handling.html>`_ |
2174 | |
2175 | === removed file 'doc/source/flexibility/mysql_remove_eol_carret.rst' |
2176 | --- doc/source/flexibility/mysql_remove_eol_carret.rst 2013-03-05 12:46:43 +0000 |
2177 | +++ doc/source/flexibility/mysql_remove_eol_carret.rst 1970-01-01 00:00:00 +0000 |
2178 | @@ -1,31 +0,0 @@ |
2179 | -.. _mysql_remove_eol_carret: |
2180 | - |
2181 | -============================= |
2182 | - Handle ``BLOB`` End of Line |
2183 | -============================= |
2184 | - |
2185 | -At some point in the past, the |MySQL| command line client was modified to remove ``\r`` before ``\n`` in its input. |
2186 | - |
2187 | -This caused problems in some workloads, specifically when loading ``BLOB`` fields containing ``\r`` characters. |Percona Server| solves this by implementing a new command line client option, :variable:`no-remove-eol-carret`. |
2188 | - |
2189 | -When the :variable:`no-remove-eol-carret` option is specified, ``\r`` before ``\n`` is not removed. |
2190 | - |
2191 | - |
2192 | -Version Specific Information |
2193 | -============================ |
2194 | - |
2195 | - * 5.5.8-20.0: |
2196 | - Full functionality. |
2197 | - |
2198 | -Client Command Line Parameter |
2199 | -============================= |
2200 | - |
2201 | -.. variable:: no-remove-eol-carret |
2202 | - |
2203 | - :cli: Yes |
2204 | - :conf: Yes |
2205 | - :scope: Local |
2206 | - :dyn: No |
2207 | - :vartype: Boolean |
2208 | - :default: Off |
2209 | - :range: On/Off |
2210 | |
2211 | === modified file 'doc/source/index.rst' |
2212 | --- doc/source/index.rst 2013-02-08 16:56:30 +0000 |
2213 | +++ doc/source/index.rst 2013-03-08 07:34:35 +0000 |
2214 | @@ -9,12 +9,9 @@ |
2215 | Percona Server 5.6 - Documentation |
2216 | =================================== |
2217 | |
2218 | -WARNING: Alpha quality software ahead |
2219 | -===================================== |
2220 | -Please note: |Percona Server| 5.6 is ALPHA quality software. It should *NOT* be used in production environments. This documentation may be out of date for Percona Server 5.6 before the final release, please check the release notes closely. |
2221 | +.. warning:: |
2222 | |
2223 | -Introduction |
2224 | -============ |
2225 | + Please note: |Percona Server| 5.6 is ALPHA quality software. It should *NOT* be used in production environments. |
2226 | |
2227 | |Percona Server| is an enhanced drop-in replacement for |MySQL|. With |Percona Server|, |
2228 | |
2229 | @@ -54,20 +51,6 @@ |
2230 | installation |
2231 | upgrading_guide_55_56 |
2232 | |
2233 | - |
2234 | -Scalability Improvements |
2235 | -======================== |
2236 | - |
2237 | -.. toctree:: |
2238 | - :maxdepth: 1 |
2239 | - :glob: |
2240 | - |
2241 | - scalability/innodb_split_buf_pool_mutex |
2242 | - scalability/innodb_insert_buffer |
2243 | - scalability/innodb_expand_undo_slots |
2244 | - scalability/innodb_adaptive_hash_index_partitions |
2245 | - scalability/innodb_extra_rseg |
2246 | - |
2247 | Performance Improvements |
2248 | ======================== |
2249 | |
2250 | @@ -75,13 +58,7 @@ |
2251 | :maxdepth: 1 |
2252 | :glob: |
2253 | |
2254 | - performance/innodb_lazy_drop_table |
2255 | - performance/innodb_doublewrite_path |
2256 | - performance/query_cache_enhance |
2257 | - performance/innodb_fast_checksum |
2258 | - performance/remove_fcntl_excessive_calls |
2259 | - performance/innodb_opt_lru_count |
2260 | - performance/innodb_thread_concurrency_timer_based |
2261 | + performance/threadpool |
2262 | |
2263 | Flexibility Improvements |
2264 | ======================== |
2265 | @@ -90,12 +67,6 @@ |
2266 | :maxdepth: 1 |
2267 | :glob: |
2268 | |
2269 | - flexibility/innodb_files_extend |
2270 | - flexibility/log_warnings_suppress |
2271 | - flexibility/mysql_remove_eol_carret |
2272 | - flexibility/buff_read_ahead_area |
2273 | - flexibility/innodb_fast_shutdown |
2274 | - flexibility/improved_memory_engine |
2275 | flexibility/mysqldump_ignore_create_error |
2276 | |
2277 | Reliability Improvements |
2278 | @@ -105,10 +76,7 @@ |
2279 | :maxdepth: 1 |
2280 | :glob: |
2281 | |
2282 | - reliability/log_connection_error |
2283 | - reliability/error_pad |
2284 | reliability/innodb_corrupt_table_action |
2285 | - reliability/show_slave_status_nolock |
2286 | |
2287 | Management Improvements |
2288 | ======================= |
2289 | @@ -117,11 +85,7 @@ |
2290 | :maxdepth: 1 |
2291 | :glob: |
2292 | |
2293 | - management/innodb_recovery_patches |
2294 | - management/sql_no_fcache |
2295 | - management/udf_maatkit |
2296 | - management/innodb_fake_changes |
2297 | - management/innodb_kill_idle_trx |
2298 | + management/innodb_expanded_fast_index_creation |
2299 | |
2300 | Diagnostics Improvements |
2301 | ======================== |
2302 | @@ -131,14 +95,6 @@ |
2303 | :glob: |
2304 | |
2305 | diagnostics/index_info_schema_tables |
2306 | - diagnostics/innodb_stats |
2307 | - diagnostics/user_stats |
2308 | - diagnostics/slow_extended_55 |
2309 | - diagnostics/innodb_show_status |
2310 | - diagnostics/innodb_deadlock_count |
2311 | - diagnostics/mysql_syslog |
2312 | - diagnostics/response_time_distribution |
2313 | - diagnostics/show_engines |
2314 | diagnostics/process_list |
2315 | diagnostics/misc_info_schema_tables |
2316 | |
2317 | @@ -149,6 +105,7 @@ |
2318 | :maxdepth: 1 |
2319 | :glob: |
2320 | |
2321 | + upstream-bug-fixes |
2322 | development |
2323 | trademark-policy |
2324 | faq |
2325 | |
2326 | === added file 'doc/source/management/innodb_expanded_fast_index_creation.rst' |
2327 | --- doc/source/management/innodb_expanded_fast_index_creation.rst 1970-01-01 00:00:00 +0000 |
2328 | +++ doc/source/management/innodb_expanded_fast_index_creation.rst 2013-03-08 07:34:35 +0000 |
2329 | @@ -0,0 +1,82 @@ |
2330 | +.. _expanded_innodb_fast_index_creation: |
2331 | + |
2332 | +============================ |
2333 | +Expanded Fast Index Creation |
2334 | +============================ |
2335 | + |
2336 | +Percona has implemented several changes related to |MySQL|'s fast index creation feature. This feature expands the ``ALTER TABLE`` command by adding a new clause that provides online index renaming capability, that is renaming indexes without rebuilding the whole table. |
2337 | + |
2338 | +Enabling Expanded Fast Index Creation |
2339 | +===================================== |
2340 | + |
2341 | +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. |
2342 | + |
2343 | +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. |
2344 | + |
2345 | + |
2346 | +:command:`mysqldump` |
2347 | +-------------------- |
2348 | + |
2349 | +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: |
2350 | + |
2351 | + * ``KEY``, ``UNIQUE KEY``, and ``CONSTRAINT`` clauses are omitted from ``CREATE TABLE`` statements corresponding to |InnoDB| tables. |
2352 | + |
2353 | + * An additional ``ALTER TABLE`` is issued after dumping the data, in order to create the previously omitted keys. |
2354 | + |
2355 | +``ALTER TABLE`` |
2356 | +--------------- |
2357 | + |
2358 | +When ``ALTER TABLE`` requires a table copy, secondary keys are now dropped and recreated later, after copying the data. The following restrictions apply: |
2359 | + |
2360 | + * Only non-unique keys can be involved in this optimization. |
2361 | + |
2362 | + * 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. |
2363 | + |
2364 | +``OPTIMIZE TABLE`` |
2365 | +------------------ |
2366 | + |
2367 | +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``. |
2368 | + |
2369 | + |
2370 | +Caveats |
2371 | +------- |
2372 | + |
2373 | +|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. |
2374 | + |
2375 | +There’s also a number of cases when this optimization is not applicable: |
2376 | + * ``UNIQUE`` indexes in ``ALTER TABLE`` are ignored to enforce uniqueness where necessary when copying the data to a temporary table; |
2377 | + |
2378 | + * ``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; |
2379 | + |
2380 | + * :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; |
2381 | + |
2382 | + * :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; |
2383 | + |
2384 | + * :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. |
2385 | + |
2386 | +Version Specific Information |
2387 | +============================ |
2388 | + |
2389 | + * :rn:`5.6.10-60.2` |
2390 | + Variable :variable:`expand_fast_index_creation` implemented. |
2391 | + This variable is controling whether fast index creation optimizations made by Perocna are used. |
2392 | + |
2393 | +System Variables |
2394 | +================ |
2395 | + |
2396 | +.. variable:: expand_fast_index_creation |
2397 | + |
2398 | + :cli: Yes |
2399 | + :conf: No |
2400 | + :scope: Local/Global |
2401 | + :dyn: Yes |
2402 | + :vartype: Boolean |
2403 | + :default: OFF |
2404 | + :range: ON/OFF |
2405 | + |
2406 | +Other Reading |
2407 | +============= |
2408 | + |
2409 | + * `Improved InnoDB fast index creation <http://www.mysqlperformanceblog.com/2011/11/06/improved-innodb-fast-index-creation/>`_ |
2410 | + * `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/>`_ |
2411 | + |
2412 | |
2413 | === removed file 'doc/source/management/innodb_fake_changes.rst' |
2414 | --- doc/source/management/innodb_fake_changes.rst 2013-03-05 12:46:43 +0000 |
2415 | +++ doc/source/management/innodb_fake_changes.rst 1970-01-01 00:00:00 +0000 |
2416 | @@ -1,70 +0,0 @@ |
2417 | -.. _innodb_fake_changes_page: |
2418 | - |
2419 | -========================== |
2420 | - Support for Fake Changes |
2421 | -========================== |
2422 | - |
2423 | -Restarting a slave server in a replication environment or setting up new slave server can cause a replication reads slower. This is happening because replication in |MySQL| is single-threaded and because it needs to read the data before it can execute the queries. The process can be speeded up by having prefetch threads to warm the server: replay statements and then rollback at commit. |
2424 | - |
2425 | -That makes prefetch simple but has high overhead from locking rows only to undo changes at rollback. |
2426 | - |
2427 | -Using this approach, support for *Fake Changes* have been implemented in order to remove the overhead and make it faster. |
2428 | - |
2429 | -By reading the rows for ``INSERT``, ``UPDATE`` and ``DELETE`` statements but not updating them (*Fake Changes*), the rollback is very fast as in most cases there is nothing to do. |
2430 | - |
2431 | -Caveats |
2432 | -======= |
2433 | - |
2434 | -``DML`` operations **are supported** |
2435 | ------------------------------------- |
2436 | - |
2437 | -Currently only ``DML`` operations **are supported**, i.e. ``UPDATE``, ``INSERT``, ``REPLACE`` and ``DELETE`` (set deleted flag). |
2438 | - |
2439 | -``DDL`` operations **are not supported** |
2440 | ----------------------------------------- |
2441 | - |
2442 | -``DDL`` operations **are not supported**, i.e. ``ALTER TABLE`` and ``TRUNCATE TABLE``. Fake Changes should be disabled temporally if ``DDL`` statements are going to be executed. Otherwise, data may be lost. |
2443 | - |
2444 | -Explicit ``COMMIT`` will lead to an error |
2445 | ------------------------------------------ |
2446 | - |
2447 | -From the viewpoint of transactional RDBMS, ``COMMIT`` should not be "fake" anytime. ``ROLLBACK`` must be used to terminate the fake transaction. |
2448 | - |
2449 | -System Variables |
2450 | -================ |
2451 | - |
2452 | -.. variable:: innodb_fake_changes |
2453 | - |
2454 | - :version 5.5.16-22.0: Introduced |
2455 | - :scope: ``GLOBAL`` |
2456 | - :type: ``BOOLEAN`` |
2457 | - :dyn: ``YES`` |
2458 | - :default: ``FALSE`` |
2459 | - |
2460 | - This variable enables the *Fake Changes* feature. |
2461 | - |
2462 | -Implementation Details |
2463 | -====================== |
2464 | - |
2465 | - * The fake session is used as a prefetch of the replication, it should not affect to later replication SQL execution. |
2466 | - |
2467 | - * The effective unit is each transaction. The behavior is decided at the start of the each one and never changed during the transaction |
2468 | - |
2469 | - * ``INSERT`` operations doesn't use the ``INSERT BUFFER``, it always causes the reading of the page actually for the option. ``DELETE`` also doesn't use the ``INSERT BUFFER``. |
2470 | - |
2471 | - * It never acquires ``X_LOCK`` from tables or records, only ``S_LOCK``. |
2472 | - |
2473 | - * The auto increment values behaves as usual. |
2474 | - |
2475 | - * It reserves free pages as usual. |
2476 | - |
2477 | - * Existed only ``root ~ leaf`` pages, which are accessed in the ``DML`` operation. |
2478 | - |
2479 | - * It will not prefetch allocate/free, split/merge, ``INODE``, ``XDES`` or other management pages. The same is for extern pages, i.e. large ``BLOB`` s). |
2480 | - |
2481 | - * Foreign key constraints are checked (for causing IO), but passed always. |
2482 | - |
2483 | -Related Reading |
2484 | -=============== |
2485 | - |
2486 | - * `on MySQL replication prefetching <http://dom.as/2011/12/03/replication-prefetching/>`_ |
2487 | |
2488 | === removed file 'doc/source/management/innodb_kill_idle_trx.rst' |
2489 | --- doc/source/management/innodb_kill_idle_trx.rst 2013-03-05 12:46:43 +0000 |
2490 | +++ doc/source/management/innodb_kill_idle_trx.rst 1970-01-01 00:00:00 +0000 |
2491 | @@ -1,25 +0,0 @@ |
2492 | -.. _innodb_kill_idle_trx: |
2493 | - |
2494 | -======================== |
2495 | - Kill Idle Transactions |
2496 | -======================== |
2497 | - |
2498 | -**NOTE:** This feature is currently considered **BETA** quality and may not yet be suitable for use in production environments. |
2499 | - |
2500 | -This feature limits the age of idle |XtraDB| transactions. If a transaction is idle for more seconds than the threshold specified, it will be killed. This prevents users from blocking purge by mistake. |
2501 | - |
2502 | -System Variables |
2503 | -================ |
2504 | - |
2505 | -.. variable:: innodb_kill_idle_transaction |
2506 | - |
2507 | - :version 5.5.16-22.0: Introduced |
2508 | - :scope: ``GLOBAL`` |
2509 | - :config: ``YES`` |
2510 | - :dyn: ``YES`` |
2511 | - :vartype: ``INTEGER`` |
2512 | - :default: 0 (disabled) |
2513 | - :unit: Seconds |
2514 | - |
2515 | - To enable this feature, set this variable to the desired seconds wait until the transaction is killed. |
2516 | - |
2517 | |
2518 | === removed file 'doc/source/management/innodb_recovery_patches.rst' |
2519 | --- doc/source/management/innodb_recovery_patches.rst 2013-03-05 12:46:43 +0000 |
2520 | +++ doc/source/management/innodb_recovery_patches.rst 1970-01-01 00:00:00 +0000 |
2521 | @@ -1,155 +0,0 @@ |
2522 | -.. _innodb_recovery_patches: |
2523 | - |
2524 | -================================ |
2525 | - Fast |InnoDB| Recovery Process |
2526 | -================================ |
2527 | - |
2528 | -This feature implements several changes related to the recovery process (bug fixes and speed changes). The |InnoDB| plugin subsequently implemented similar functionality, so the feature is currently unused. The system variable it implemented now does nothing and has been kept to maintain compatibility. |
2529 | - |
2530 | - * ``recv_apply_hashed_log_recs()`` may hang up when meets DB_TABLESPACE_DELETED pages |
2531 | - |
2532 | - * Insert buffer operation may destroy the page during its recovery process adjustment of smaller sleep period in loop |
2533 | - |
2534 | - * ``buf_flush_insert_sorted_into_flush_list()`` change to don't sort (new variable ``innodb_fast_recovery`` (default false(~1.0.5), true(1.0.6~)) - if set true, sorting is enabled.) |
2535 | - |
2536 | -Output statistics of recovery process after it :: |
2537 | - |
2538 | - ------------------- |
2539 | - RECOVERY STATISTICS |
2540 | - ------------------- |
2541 | - Recovery time: 18 sec. (1 turns) |
2542 | - |
2543 | - Data page IO statistics |
2544 | - Requested pages: 9126 |
2545 | - Read pages: 9126 |
2546 | - Written pages: 7957 |
2547 | - (Dirty blocks): 1156 |
2548 | - Grouping IO [times]: |
2549 | - number of pages, |
2550 | - read request neighbors (in 32 pages chunk), |
2551 | - combined read IO, |
2552 | - combined write IO |
2553 | - 1, 32, 335, 548 |
2554 | - 2, 0, 121, 97 |
2555 | - 3, 7, 49, 44 |
2556 | - 4, 4, 43, 26 |
2557 | - .... |
2558 | - 64, 0, 2, 25 |
2559 | - |
2560 | - Recovery process statistics |
2561 | - Checked pages by doublewrite buffer: 128 |
2562 | - Overwritten pages from doublewrite: 0 |
2563 | - Recovered pages by io_thread: 9145 |
2564 | - Recovered pages by main thread: 0 |
2565 | - Parsed log records to apply: 2572491 |
2566 | - Sum of the length: 71274689 |
2567 | - Applied log records: 2376356 |
2568 | - Sum of the length: 68098300 |
2569 | - Pages which are already new enough: 93 |
2570 | - Oldest page's LSN: 926917970 |
2571 | - Newest page's LSN: 1526578232 |
2572 | - |
2573 | - |
2574 | -Other Information |
2575 | -================= |
2576 | - |
2577 | -Is the ``buf_flush_insert_sorted_into_flush_list()`` change correct? |
2578 | --------------------------------------------------------------------- |
2579 | - |
2580 | -|InnoDB| recovers the each pages not in order of the oldest un-flushed change``s :term:`LSN`. But flush_list must has un-flushed blocks in order of the :term:`LSN`. So, normal |InnoDB| does bubble sorting for each inserting block to ``flush_lsn`` … It is very expensive operation… |
2581 | - |
2582 | -Then, why the order must be kept? |
2583 | ---------------------------------- |
2584 | - |
2585 | -|InnoDB| should get the LSN of the oldest un-flushed change in all blocks for checkpointing. It is the oldest un-flushed change's :term:`LSN` of the last block in the ``flush_list``. |
2586 | - |
2587 | -So, it may be correct that the last blocks' ``oldest_modification`` only have to keep the oldest ``oldest_modification`` in the buffer pool. |
2588 | - |
2589 | -This change is simple. If the new page's ``oldest_modification`` is... :: |
2590 | - |
2591 | - [newer than any oldest_modification in flushlist] |
2592 | - |
2593 | -add to first of the ``flush_list`` :: |
2594 | - |
2595 | - [older than any oldest_modification in flushlist] |
2596 | - |
2597 | -add to last of the ``flush_list`` :: |
2598 | - |
2599 | - [else] |
2600 | - |
2601 | -overwrite ``oldest_modification`` by the oldest ``oldest_modification`` in ``flush_list`` |
2602 | - |
2603 | -add to last of the ``flush_list`` |
2604 | - |
2605 | -These operation should not break consistency of ``flush_list``. However, it may cause same-LSN-aged cluster of many pages and much flushing operation. But anyway, the most of the flushing should be done during the recovery process. :: |
2606 | - |
2607 | - --- innodb_plugin-1.0.3_orig/buf/buf0flu.c 2009-07-07 18:03:24.000000000 +0900 |
2608 | - +++ innodb_plugin-1.0.3_tmp/buf/buf0flu.c 2009-07-07 18:06:47.000000000 +0900 |
2609 | - @@ -108,6 +108,17 @@ |
2610 | - prev_b = NULL; |
2611 | - b = UT_LIST_GET_FIRST(buf_pool->flush_list); |
2612 | - |
2613 | - + if (b == NULL || b->oldest_modification < block->page.oldest_modification) { |
2614 | - + UT_LIST_ADD_FIRST(flush_list, buf_pool->flush_list, &block->page); |
2615 | - + } else { |
2616 | - + b = UT_LIST_GET_LAST(buf_pool->flush_list); |
2617 | - + if (b->oldest_modification < block->page.oldest_modification) { |
2618 | - + /* align oldest_modification not to sort */ |
2619 | - + block->page.oldest_modification = b->oldest_modification; |
2620 | - + } |
2621 | - + UT_LIST_ADD_LAST(flush_list, buf_pool->flush_list, &block->page); |
2622 | - + } |
2623 | - +/* |
2624 | - while (b && b->oldest_modification > block->page.oldest_modification) { |
2625 | - ut_ad(b->in_flush_list); |
2626 | - prev_b = b; |
2627 | - @@ -120,6 +131,7 @@ |
2628 | - UT_LIST_INSERT_AFTER(flush_list, buf_pool->flush_list, |
2629 | - prev_b, &block->page); |
2630 | - } |
2631 | - +*/ |
2632 | - |
2633 | - #if defined UNIV_DEBUG || defined UNIV_BUF_DEBUG |
2634 | - ut_a(buf_flush_validate_low()); |
2635 | - |
2636 | -System Variables |
2637 | -================ |
2638 | - |
2639 | -One new system variable was introduced by this feature. |
2640 | - |
2641 | - |
2642 | -.. variable:: innodb_fast_recovery |
2643 | - |
2644 | - :cli: Yes |
2645 | - :conf: Yes |
2646 | - :scope: Global |
2647 | - :dyn: No |
2648 | - :vartype: BOOL |
2649 | - :default: FALSE |
2650 | - :range: TRUE/FALSE |
2651 | - :default: false(~1.0.5), true(1.0.6~)) - if set true, the change is enabled. |
2652 | - |
2653 | -.. variable:: innodb_recovery_stats |
2654 | - |
2655 | - :cli: No |
2656 | - :conf: |
2657 | - :scope: |
2658 | - :dyn: |
2659 | - :vartype: BOOL |
2660 | - :default: FALSE |
2661 | - :range: TRUE/FALSE |
2662 | - |
2663 | - |
2664 | - |
2665 | -Other reading |
2666 | -============= |
2667 | - |
2668 | - * `How to estimate time it takes InnoDB to recover? <http://www.mysqlperformanceblog.com/2007/05/09/how-to-estimate-time-it-takes-innodb-to-recover/>`_ |
2669 | - |
2670 | - * `InnoDB recovery - is large buffer pool always better? <http://www.mysqlperformanceblog.com/2007/07/17/innodb-recovery-is-large-buffer-pool-always-better/>`_ |
2671 | - |
2672 | - * `What is the longest part of InnoDB recovery process? <http://www.mysqlperformanceblog.com/2007/12/20/what-is-the-longest-part-of-innodb-recovery-process/>`_ |
2673 | - |
2674 | - * `Improving InnoDB recovery time <http://www.mysqlperformanceblog.com/2009/07/07/improving-innodb-recovery-time/>`_ |
2675 | - |
2676 | - * `How long is recovery from 8G innodb_log_file <http://www.mysqlperformanceblog.com/2010/12/22/how-long-is-recovery-from-8g-innodb_log_file/>`_ |
2677 | |
2678 | === removed file 'doc/source/management/sql_no_fcache.rst' |
2679 | --- doc/source/management/sql_no_fcache.rst 2012-02-13 08:49:21 +0000 |
2680 | +++ doc/source/management/sql_no_fcache.rst 1970-01-01 00:00:00 +0000 |
2681 | @@ -1,34 +0,0 @@ |
2682 | -.. _sql_no_fcache: |
2683 | - |
2684 | -============================== |
2685 | -Prevent Caching to FlashCache |
2686 | -============================== |
2687 | - |
2688 | -*FlashCache* increases performance by caching data in SSDs. It works even better when only hot data is cached. This feature prevents unwanted blocks of data to be cached. |
2689 | - |
2690 | -Better utilization of *FlashCache* partitions is achieved when caching of rarely used data is avoided. Use of this feature prevents blocks of data from being cached to *FlashCache* during a query. |
2691 | - |
2692 | -Usage of the feature is as follows: :: |
2693 | - |
2694 | - SELECT /* sql_no_fcache */ ... |
2695 | - |
2696 | -The :command:`mysqldump` binary was changed to use this option. |
2697 | - |
2698 | - |
2699 | -Version-Specific Information |
2700 | -============================ |
2701 | - |
2702 | - * 5.5.8-20.0: |
2703 | - Full functionality available. |
2704 | - |
2705 | -Other Information |
2706 | -================= |
2707 | - |
2708 | -The feature is a port of the original *Facebook* change. |
2709 | - |
2710 | -Other reading |
2711 | -============= |
2712 | - |
2713 | - * `Releasing Flashcache <http://www.facebook.com/note.php?note_id=388112370932>`_ |
2714 | - |
2715 | - * `Level 2 Flash cache is there <http://www.mysqlperformanceblog.com/2010/04/27/level-2-flash-cache-is-there/>`_ |
2716 | |
2717 | === removed file 'doc/source/management/udf_maatkit.rst' |
2718 | --- doc/source/management/udf_maatkit.rst 2013-03-05 12:46:43 +0000 |
2719 | +++ doc/source/management/udf_maatkit.rst 1970-01-01 00:00:00 +0000 |
2720 | @@ -1,49 +0,0 @@ |
2721 | -.. _udf_maatkit: |
2722 | - |
2723 | -====================================== |
2724 | - *Percona Toolkit* and *Maatkit* UDFs |
2725 | -====================================== |
2726 | - |
2727 | -Three *Percona Toolkit* and *Maatkit* UDFs that provide faster checksums are provided: |
2728 | - |
2729 | - * ``fnv_udf`` |
2730 | - |
2731 | - * ``fnv1a_udf`` |
2732 | - |
2733 | - * ``murmur_udf`` |
2734 | - |
2735 | -Version Specific Information |
2736 | -============================ |
2737 | - |
2738 | - * 5.1.53-12.4: |
2739 | - Began distributing ``fnv_udf``, ``fnv1a_udf``, and ``murmur_udf``. |
2740 | - |
2741 | -Other Information |
2742 | -================= |
2743 | - |
2744 | - * Author / Origin: |
2745 | - Baron Schwartz |
2746 | - |
2747 | -Installation |
2748 | -============ |
2749 | - |
2750 | -Use of the Percona Software Repositories simplifies the installation of *Percona Toolkit*. Once the repository has been set up on your system, *Percona Toolkit* can be installed by executing: :: |
2751 | - |
2752 | - $ yum install percona-toolkit |
2753 | - |
2754 | -This will place the *Percona Toolkit* UDFs onto your system. However, they will not yet be installed into the |MySQL| server. To install one of the UDF``s into the server, execute one of the following commands, depending on which UDF you want to install: :: |
2755 | - |
2756 | - $ mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ``fnv_udf.so``" |
2757 | - $ mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME ``fnv1a_udf.so``" |
2758 | - $ mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME ``murmur_udf.so``" |
2759 | - |
2760 | -Executing each of these commands will install its respective UDF into the server. |
2761 | - |
2762 | -If you have any difficulty, or require more detailed information, refer to the *Maatkit* `Installing UDFs <http://code.google.com/p/maatkit/wiki/InstallingUdfs>`_ documentation. |
2763 | - |
2764 | - |
2765 | -Other Reading |
2766 | -============= |
2767 | - |
2768 | - * *Percona Toolkit* - http://www.percona.com/doc/percona-toolkit |
2769 | - * *Maatkit* - http://www.maatkit.org/ |
2770 | |
2771 | === removed file 'doc/source/performance/innodb_doublewrite_path.rst' |
2772 | --- doc/source/performance/innodb_doublewrite_path.rst 2012-02-13 08:49:21 +0000 |
2773 | +++ doc/source/performance/innodb_doublewrite_path.rst 1970-01-01 00:00:00 +0000 |
2774 | @@ -1,87 +0,0 @@ |
2775 | -.. _innodb_doublewrite_path: |
2776 | - |
2777 | -========================================= |
2778 | - Configuration of the Doublewrite Buffer |
2779 | -========================================= |
2780 | - |
2781 | -|InnoDB| and |XtraDB| use a special feature called the doublewrite buffer to provide a strong guarantee against data corruption. The idea is to write the data to a sequential log in the main tablespace before writing to the data files. If a partial page write happens (in other words, a corrupted write), |InnoDB| and |XtraDB| will use the buffer to recover the data. Even if the data is written twice the performance impact is usually small, but in some heavy workloads the doublewrite buffer becomes a bottleneck. Now we have an option to put the buffer on a dedicated disk in order to parallelize I/O activity on the buffer and on the tablespace. |
2782 | - |
2783 | -This feature allows you to move the doublewrite buffer from the main tablespace to a separate location. |
2784 | - |
2785 | -This option is for advanced users only. See the discussion below to fully understand whether you really need to use it. |
2786 | - |
2787 | - |
2788 | -Detailed Information |
2789 | -==================== |
2790 | - |
2791 | -The following discussion will clarify the improvements made possible by this feature. |
2792 | - |
2793 | -Goal of the Doublewrite Buffer |
2794 | ------------------------------- |
2795 | - |
2796 | -|InnoDB| and |XtraDB| use many structures, some on disk and others in memory, to manage data as efficiently as possible. To have an overview of the different components see this post. Let``s now focus on the doublewrite buffer. |
2797 | - |
2798 | -|InnoDB| / |XtraDB| uses a reserved area in its main tablespace, called the doublewrite buffer, to prevent data corruption that could occur with partial page writes. When the data in the buffer pool is flushed to disk, |InnoDB| / |XtraDB| will flush whole pages at a time (by default 16KB pages) and not just the records that have changed within a page. It means that, if anything unexpected happens during the write, the page can be partially written leading to corrupt data. |
2799 | - |
2800 | -With the doublewrite buffer feature, |InnoDB| / |XtraDB| first writes the page in the doublewrite buffer and then to the data files. |
2801 | - |
2802 | -If a partial page write occurs in the data files, |InnoDB| / |XtraDB| will check on recovery if the checksum of the page in the data file is different from the checksum of the page in the doublewrite buffer and thus will know if the page is corrupt or not. If it is corrupt, the recovery process will use the page stored in the doublewrite buffer to restore the correct data. |
2803 | - |
2804 | -If a partial write occurs in the doublewrite buffer, the original page is untouched and can be used with the redo logs to recover the data. For further information on the doublewrite buffer, you can see this post. |
2805 | - |
2806 | -Performance Impact of the Doublewrite Buffer |
2807 | --------------------------------------------- |
2808 | - |
2809 | -In usual workloads the performance impact is low-5% or so. As a consequence, you should always enable the doublewrite buffer because the strong guarantee against data corruption is worth the small performance drop. |
2810 | - |
2811 | -But if you experience a heavy workload, especially if your data does not fit in the buffer pool, the writes in the doublewrite buffer will compete against the random reads to access the disk. In this case, you can see a sharp performance drop compared to the same workload without the doublewrite buffer-a 30% performance degradation is not uncommon. |
2812 | - |
2813 | -Another case when you can see a big performance impact is when the doublewrite buffer is full. Then new writes must wait until entries in the doublewrite buffer are freed. |
2814 | - |
2815 | -What's New with This Feature |
2816 | ----------------------------- |
2817 | - |
2818 | -In a standard |InnoDB| / |XtraDB| installation, the doublewrite buffer is located in the main tablespace (whether you activate the ``innodb_file_per_table`` or not) and you have no option to control anything about it. |
2819 | - |
2820 | -The feature adds an option (``innodb_doublewrite_file``) to have a dedicated location for the doublewrite buffer. |
2821 | - |
2822 | -How to Choose a Good Location for the Doublewrite Buffer |
2823 | --------------------------------------------------------- |
2824 | - |
2825 | -Basically if you want to improve the I/O activity, you will put the doublewrite buffer on a different disk. But is it better on an SSD or a more traditional HDD? First you should note that pages are written in a circular fashion in the doublewrite buffer and only read on recovery. So the doublewrite buffer performs mostly sequential writes and a few sequential reads. Second HDDs are very good at sequential write if a write cache is enabled, which is not the case of SSDs. Therefore you should choose a fast HDD if you want to see performance benefits from this option. For instance, you could place the redo logs (also written in a sequential way) and the doublewrite buffer on the same disk. |
2826 | - |
2827 | -Version Specific Information |
2828 | -============================ |
2829 | - |
2830 | - * 5.5.8-20.0 |
2831 | - Full functionality available. |
2832 | - |
2833 | - |
2834 | -System Variables |
2835 | -================ |
2836 | - |
2837 | -The following system variable was introduced. |
2838 | - |
2839 | - |
2840 | -.. variable:: innodb_doublewrite_file |
2841 | - |
2842 | - :cli: Yes |
2843 | - :conf: Yes |
2844 | - :scope: Global |
2845 | - :dyn: No |
2846 | - :vartype: STR |
2847 | - :def: NULL |
2848 | - |
2849 | -Use this option to create a dedicated tablespace for the doublewrite buffer. |
2850 | - |
2851 | -This option expects a filename which can be specified either with an absolute or a relative path. A relative path is relative to the data directory. |
2852 | - |
2853 | - |
2854 | -Related Reading |
2855 | -=============== |
2856 | - |
2857 | - * `XtraDB / InnoDB internals in drawing <http://www.mysqlperformanceblog.com/2010/04/26/xtradb-innodb-internals-in-drawing/>`_ |
2858 | - |
2859 | - * `InnoDB Double Write <http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/>`_ |
2860 | - |
2861 | - * `SSD and HDD for InnoDB <http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html>`_ |
2862 | |
2863 | === removed file 'doc/source/performance/innodb_fast_checksum.rst' |
2864 | --- doc/source/performance/innodb_fast_checksum.rst 2013-03-05 12:46:43 +0000 |
2865 | +++ doc/source/performance/innodb_fast_checksum.rst 1970-01-01 00:00:00 +0000 |
2866 | @@ -1,28 +0,0 @@ |
2867 | -.. _innodb_fast_checksum_page: |
2868 | - |
2869 | -======================== |
2870 | - Fast |InnoDB| Checksum |
2871 | -======================== |
2872 | - |
2873 | -|InnoDB| writes a checksum at the end of each data page in order to detect data files corruption. However computing this checksum requires CPU cycles and in some circumstances this extra overhead can become significant. |
2874 | - |
2875 | -|XtraDB| can use a more CPU-efficient algorithm, based on 4-byte words, which can be beneficial for some workloads (for instance write-heavy workloads on servers that can perform lots of IO). |
2876 | - |
2877 | -The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums. If you want to have the entire benefit of this change, you will need to recreate all your |InnoDB| tables, for instance by dumping and reloading all |InnoDB| tables. |
2878 | - |
2879 | -Once enabled, turning it off will require table(s) to be dump/imported, since it will fail to start on data files created when ``innodb_fast_checksums`` was enabled. In this case ALTER TABLE won't work due to its implementation. |
2880 | - |
2881 | - |
2882 | -System Variables |
2883 | -================ |
2884 | - |
2885 | -.. variable:: innodb_fast_checksum |
2886 | - |
2887 | - :cli: Yes |
2888 | - :conf: Yes |
2889 | - :scope: Global |
2890 | - :dyn: No |
2891 | - :vartype: BOOL |
2892 | - :default: 0 |
2893 | - |
2894 | - |
2895 | |
2896 | === removed file 'doc/source/performance/innodb_lazy_drop_table.rst' |
2897 | --- doc/source/performance/innodb_lazy_drop_table.rst 2013-03-05 12:46:43 +0000 |
2898 | +++ doc/source/performance/innodb_lazy_drop_table.rst 1970-01-01 00:00:00 +0000 |
2899 | @@ -1,36 +0,0 @@ |
2900 | -.. _innodb_lazy_drop_table_page: |
2901 | - |
2902 | -====================== |
2903 | -Drop table performance |
2904 | -====================== |
2905 | - |
2906 | -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. |
2907 | - |
2908 | -This feature allows you to do "background table drop". |
2909 | - |
2910 | -Version Specific Information |
2911 | -============================ |
2912 | - |
2913 | - * 5.5.10-20.1 Feature added. |
2914 | - |
2915 | -System Variables |
2916 | -================ |
2917 | - |
2918 | -.. variable:: innodb_lazy_drop_table |
2919 | - |
2920 | - :cli: Yes |
2921 | - :conf: Yes |
2922 | - :scope: Global |
2923 | - :dyn: Yes |
2924 | - :vartype: BOOL |
2925 | - :default: FALSE |
2926 | - :range: TRUE/FALSE |
2927 | - |
2928 | -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. |
2929 | - |
2930 | -When this option is OFF, the usual behavior for dropping tables is in effect. |
2931 | - |
2932 | -Related Reading |
2933 | -=============== |
2934 | - |
2935 | - * Drop table performance `blog post <http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/>`_. |
2936 | |
2937 | === removed file 'doc/source/performance/innodb_opt_lru_count.rst' |
2938 | --- doc/source/performance/innodb_opt_lru_count.rst 2013-03-05 12:46:43 +0000 |
2939 | +++ doc/source/performance/innodb_opt_lru_count.rst 1970-01-01 00:00:00 +0000 |
2940 | @@ -1,7 +0,0 @@ |
2941 | -.. _innodb_opt_lru_count: |
2942 | - |
2943 | -====================================== |
2944 | - Reduced Buffer Pool Mutex Contention |
2945 | -====================================== |
2946 | - |
2947 | -We removed ``buffer_pool`` mutex operations on counting blocks on LRU list where it is safe to delete. As drawback we may have some inaccurate information of LRU list, but it does not affect storage engine operations. As result we have decreased contention on ``buffer_pool`` mutex. |
2948 | |
2949 | === removed file 'doc/source/performance/innodb_thread_concurrency_timer_based.rst' |
2950 | --- doc/source/performance/innodb_thread_concurrency_timer_based.rst 2011-10-07 23:38:41 +0000 |
2951 | +++ doc/source/performance/innodb_thread_concurrency_timer_based.rst 1970-01-01 00:00:00 +0000 |
2952 | @@ -1,20 +0,0 @@ |
2953 | -.. _innodb_thread_concurrency_timer_based_page: |
2954 | - |
2955 | -============================================= |
2956 | - |InnoDB| timer-based Concurrency Throttling |
2957 | -============================================= |
2958 | - |
2959 | -System Variables |
2960 | -================ |
2961 | - |
2962 | -.. variable:: innodb_thread_concurrency_timer_based |
2963 | - |
2964 | - :cli: Yes |
2965 | - :conf: Yes |
2966 | - :scope: |
2967 | - :dyn: |
2968 | - :vartype: BOOL |
2969 | - :default: FALSE |
2970 | - :range: TRUE/FALSE |
2971 | - |
2972 | -Use |InnoDB| timer based concurrency throttling. |
2973 | |
2974 | === removed file 'doc/source/performance/query_cache_enhance.rst' |
2975 | --- doc/source/performance/query_cache_enhance.rst 2013-03-05 12:46:43 +0000 |
2976 | +++ doc/source/performance/query_cache_enhance.rst 1970-01-01 00:00:00 +0000 |
2977 | @@ -1,123 +0,0 @@ |
2978 | -.. _query_cache_enhance: |
2979 | - |
2980 | -========================== |
2981 | - Query Cache Enhancements |
2982 | -========================== |
2983 | - |
2984 | -This page describes the enhancements for the query cache. At the moment three features are available: |
2985 | - |
2986 | - * Disabling the cache completely |
2987 | - |
2988 | - * Diagnosing contention more easily |
2989 | - |
2990 | - * Ignoring comments |
2991 | - |
2992 | -Disabling the cache completely |
2993 | -============================== |
2994 | - |
2995 | -This feature allows the user to completely disable use of the query cache. When the server is compiled with the query cache enabled, the query cache is locked during use by the query cache mutex. This lock can cause performance to decrease in some situations. By disabling use of the query cache altogether when the server is started, any possibility of locking it is eliminated, and performance may be improved. |
2996 | - |
2997 | -The query cache can now be disabled at server startup or in an option file by: :: |
2998 | - |
2999 | - --query_cache_type=0 |
3000 | - |
3001 | -The default is 1 (query cache enabled). |
3002 | - |
3003 | -**Note:** This variable already exists in standard |MySQL|, but when setting query_cache_type=0, the query cache mutex will still be in used. Setting query_cache_type=0 in |Percona Server| ensures that both the cache is disabled and the mutex is not used. |
3004 | - |
3005 | -If query caching is off and a user tries to turn it on from within a session, the following error will be reported: :: |
3006 | - |
3007 | - SET GLOBAL query_cache_type=ON; |
3008 | - ERROR 1651(HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it |
3009 | - |
3010 | -**Note:** This variable is implemented in standard |MySQL| from version 5.5.0. |
3011 | - |
3012 | - |
3013 | -Diagnosing contention more easily |
3014 | -================================= |
3015 | - |
3016 | -This features provides a new thread state - ``Waiting on query cache mutex``. It has always been difficult to spot query cache bottlenecks because these bottlenecks usually happen intermittently and are not directly reported by the server. This new thread state appear in the output of SHOW PROCESSLIST, easing diagnostics. |
3017 | - |
3018 | -Imagine that we run three queries simultaneously (each one in a separate thread): |
3019 | - |
3020 | - > SELECT number from t where id > 0; |
3021 | - > SELECT number from t where id > 0; |
3022 | - > SELECT number from t where id > 0; |
3023 | - |
3024 | -If we experience query cache contention, the output of SHOW PROCESSLIT will look like this: :: |
3025 | - |
3026 | - > SHOW PROCESSLIST; |
3027 | - Id User Host db Command Time State Info |
3028 | - 2 root localhost test Sleep 2 NULL |
3029 | - 3 root localhost test Query 2 Waiting on query cache mutex SELECT number from t where id > 0; |
3030 | - 4 root localhost test Query 1 Waiting on query cache mutex SELECT number from t where id > 0; |
3031 | - 5 root localhost test Query 0 NULL |
3032 | - |
3033 | -Ignoring comments |
3034 | -================= |
3035 | - |
3036 | -This feature adds an option to make the server ignore comments when checking for a query cache hit. For example, consider these two queries: :: |
3037 | - |
3038 | - /* first query */ select name from users where users.name like 'Bob%'; |
3039 | - /* retry search */ select name from users where users.name like 'Bob%'; |
3040 | - |
3041 | -By default (option off), the queries are considered different, so the server will execute them both and cache them both. |
3042 | - |
3043 | -If the option is enabled, the queries are considered identical, so the server will execute and cache the first one and will serve the second one directly from the query cache. |
3044 | - |
3045 | - |
3046 | -.. Version Specific Information |
3047 | -.. ---------------------------- |
3048 | - |
3049 | -.. Disabling the query cache completely |
3050 | - |
3051 | -.. Percona Server Version Comments |
3052 | -.. 5.1.49-12.0 Full functionality available. |
3053 | -.. Diagnosing contention more easily |
3054 | - |
3055 | -.. Percona Server Version Comments |
3056 | -.. 5.1.49-12.0 Full functionality available. |
3057 | -.. Ignoring comments |
3058 | - |
3059 | -.. Percona Server Version Comments |
3060 | -.. 5.1.47-11.0 Critical bug (see MySQL bug 55032). Release was recalled. |
3061 | -.. 5.1.47-11.1 Fixed critical bug from previous release. MySQL bug 55032 actual. Bug b603618 actual. Bug 603619 actual. |
3062 | -.. 5.1.47-11.2 Full functionality available. |
3063 | -.. 5.1.48-12.0 Full functionality available. |
3064 | - |
3065 | -.. Other Information |
3066 | - |
3067 | -.. Disabling the query cache completely |
3068 | - |
3069 | -.. Author/Origin Percona |
3070 | -.. Bugs fixed LP bug 609027, MySQL bug 38551 |
3071 | -.. Diagnosing contention more easily |
3072 | - |
3073 | -.. Author/Origin Percona |
3074 | -.. Bugs fixed LP bug589484 |
3075 | - |
3076 | -System Variables |
3077 | -================ |
3078 | - |
3079 | -.. variable:: query_cache_strip_comments |
3080 | - |
3081 | - :cli: Yes |
3082 | - :conf: Yes |
3083 | - :scope: Global |
3084 | - :dyn: Yes |
3085 | - :vartype: Boolean |
3086 | - :default: Off |
3087 | - |
3088 | - |
3089 | -Makes the server ignore comments when checking for a query cache hit. |
3090 | - |
3091 | -Other Reading |
3092 | -------------- |
3093 | - |
3094 | - * `MySQL general thread states <http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html>`_ |
3095 | - |
3096 | - * `RAII <http://en.wikibooks.org/wiki/More_C%2B%2B_Idioms/Resource_Acquisition_Is_Initialization>`_ |
3097 | - |
3098 | - * `Scope guard <http://en.wikibooks.org/wiki/More_C%2B%2B_Idioms/Scope_Guard>`_ |
3099 | - |
3100 | - * `Query cache freezes <http://www.mysqlperformanceblog.com/2009/03/19/mysql-random-freezes-could-be-the-query-cache/>`_ |
3101 | |
3102 | === removed file 'doc/source/performance/remove_fcntl_excessive_calls.rst' |
3103 | --- doc/source/performance/remove_fcntl_excessive_calls.rst 2013-03-05 12:46:43 +0000 |
3104 | +++ doc/source/performance/remove_fcntl_excessive_calls.rst 1970-01-01 00:00:00 +0000 |
3105 | @@ -1,35 +0,0 @@ |
3106 | -.. _remove_fcntl_excessive_calls: |
3107 | - |
3108 | -============================================= |
3109 | - Remove Excessive Function Calls (``fcntl``) |
3110 | -============================================= |
3111 | - |
3112 | -This change removes a bottleneck at the client/server protocol level for high concurrency workloads. |
3113 | - |
3114 | -When reading a packet from a socket, the read can be performed either in non-blocking mode or in blocking mode. The non-blocking mode was originally chosen because it avoids the cost of setting up an alarm in case of a timeout: thus the first attempt to read is done in non-blocking mode, and only if it fails, the next attempts are done in blocking mode. |
3115 | - |
3116 | -However, this behavior can hurt performance as the switch from non-blocking mode to blocking mode is expensive, requiring calls to the fcntl function and calls into the kernel. |
3117 | - |
3118 | -The solution is to use socket timeouts, with the ``SO_RCVTIMEO`` and ``SO_SNDTIMEO`` options. This way, the timeouts are automatically handled by the operating system, which means that all reads can be done in blocking mode. |
3119 | - |
3120 | - |
3121 | -.. Version Specific Information |
3122 | - |
3123 | -.. Percona Server Version Comments |
3124 | -.. 5.1.49-12.0 Ported from Facebook; full functionality available. |
3125 | -.. 5.1.55-12.6, 5.5.10-20.1 Ported an updated version in which several incorrect lines in the original implementation changed. |
3126 | - |
3127 | -.. Other Information |
3128 | - |
3129 | -.. Author/Origin Facebook |
3130 | -.. Bugs fixed #606810, #724674 |
3131 | -.. This is a port of the official MySQL version of the original Facebook change. You can see the commits for both the original implementation and the updated version in Bazaar. |
3132 | - |
3133 | - |
3134 | -Other Reading |
3135 | -============= |
3136 | - |
3137 | - * `fcntl Bottleneck <http://www.facebook.com/note.php?note_id=404965725932>`_ |
3138 | - |
3139 | - * `Use of non-blocking mode for sockets limits performance <http://bugs.mysql.com/bug.php?id=54790>`_ |
3140 | - |
3141 | |
3142 | === added file 'doc/source/performance/threadpool.rst' |
3143 | --- doc/source/performance/threadpool.rst 1970-01-01 00:00:00 +0000 |
3144 | +++ doc/source/performance/threadpool.rst 2013-03-08 07:34:35 +0000 |
3145 | @@ -0,0 +1,113 @@ |
3146 | +.. _threadpool: |
3147 | + |
3148 | +============= |
3149 | + Thread Pool |
3150 | +============= |
3151 | + |
3152 | +.. note:: |
3153 | + |
3154 | + This feature implementation is considered BETA quality. |
3155 | + |
3156 | +|MySQL| executes statements using one thread per client connection. Once the number of connections increases past a certain point performance will degrade. |
3157 | + |
3158 | +This feature enables the server to keep the top performance even with large number of client connections by introducing a dynamic thread pool. By using the thread pool server would decrease the number of threads, which will then reduce the context switching and hot locks contentions. Using the thread pool will have the most effect with ``OLTP`` workloads (relatively short CPU-bound queries). |
3159 | + |
3160 | +In order to enable the thread pool variable :variable:`thread_handling` should be set up to ``pool-of-threads`` value. This can be done by adding: :: |
3161 | + |
3162 | + thread_handling=pool-of-threads |
3163 | + |
3164 | +to the |MySQL| configuration file :file:`my.cnf`. |
3165 | + |
3166 | +Although the default values for the thread pool should provide good performance, additional `tuning <https://kb.askmonty.org/en/threadpool-in-55/#optimizing-server-variables-on-unix>`_ can be performed with the dynamic system variables described below. |
3167 | + |
3168 | +.. note:: |
3169 | + |
3170 | + Current implementation of the thread pool is built in the server, unlike the upstream version which is implemented as a plugin. Another significant implementation difference is that this implementation doesn't try to minimize the number of concurrent transactions like the ``MySQL Enterprise Threadpool``. Because of these things this implementation isn't compatible with the upstream one. |
3171 | + |
3172 | +Version Specific Information |
3173 | +============================ |
3174 | + |
3175 | + * :rn:`5.6.10-60.2` |
3176 | + ``Thread Pool`` feature implemented. This feature was ported from |MariaDB|. |
3177 | + |
3178 | +System Variables |
3179 | +================ |
3180 | + |
3181 | +.. variable:: thread_pool_idle_timeout |
3182 | + |
3183 | + :cli: Yes |
3184 | + :conf: Yes |
3185 | + :scope: Global |
3186 | + :dyn: Yes |
3187 | + :vartype: Numeric |
3188 | + :default: 60 (seconds) |
3189 | + |
3190 | +This variable can be used to limit the time an idle thread should wait before exiting. |
3191 | + |
3192 | +.. variable:: thread_pool_max_threads |
3193 | + |
3194 | + :cli: Yes |
3195 | + :conf: Yes |
3196 | + :scope: Global |
3197 | + :dyn: Yes |
3198 | + :vartype: Numeric |
3199 | + :default: 500 |
3200 | + |
3201 | +This variable can be used to limit the maximum number of threads in the pool. Once this number is reached no new threads will be created. |
3202 | + |
3203 | +.. variable:: thread_pool_oversubscribe |
3204 | + |
3205 | + :cli: Yes |
3206 | + :conf: Yes |
3207 | + :scope: Global |
3208 | + :dyn: Yes |
3209 | + :vartype: Numeric |
3210 | + :default: 3 |
3211 | + |
3212 | +The higher the value of this parameter the more threads can be run at the same time, if the values is lower than ``3`` it could lead to more sleeps and wake-ups. |
3213 | + |
3214 | +.. variable:: thread_pool_size |
3215 | + |
3216 | + :cli: Yes |
3217 | + :conf: Yes |
3218 | + :scope: Global |
3219 | + :dyn: Yes |
3220 | + :vartype: Numeric |
3221 | + :default: Number of processors |
3222 | + |
3223 | +This variable can be used to define the number of threads that can use the CPU at the same time. |
3224 | + |
3225 | +.. variable:: thread_pool_stall_limit |
3226 | + |
3227 | + :cli: Yes |
3228 | + :conf: No |
3229 | + :scope: Global |
3230 | + :dyn: No |
3231 | + :vartype: Numeric |
3232 | + :default: 500 (ms) |
3233 | + |
3234 | +The number of milliseconds before a running thread is considered stalled. When this limit is reached thread pool will wake up or create another thread. This is being used to prevent a long-running query from monopolizing the pool. |
3235 | + |
3236 | +Status Variables |
3237 | +===================== |
3238 | + |
3239 | +.. variable:: Threadpool_idle_threads |
3240 | + |
3241 | + :cli: Yes |
3242 | + :vartype: Numeric |
3243 | + |
3244 | +This status variable shows the number of idle threads in the pool. |
3245 | + |
3246 | +.. variable:: Threadpool_threads |
3247 | + |
3248 | + :cli: Yes |
3249 | + :vartype: Numeric |
3250 | + |
3251 | +This status variable shows the number of threads in the pool. |
3252 | + |
3253 | +Other Reading |
3254 | +============= |
3255 | + |
3256 | + * `Thread pool in MariaDB 5.5 <https://kb.askmonty.org/en/threadpool-in-55/>`_ |
3257 | + |
3258 | + * `Thread pool implementation in Oracle MySQL <http://mikaelronstrom.blogspot.com/2011_10_01_archive.html>`_ |
3259 | |
3260 | === added file 'doc/source/release-notes/Percona-Server-5.6.10-60.2.rst' |
3261 | --- doc/source/release-notes/Percona-Server-5.6.10-60.2.rst 1970-01-01 00:00:00 +0000 |
3262 | +++ doc/source/release-notes/Percona-Server-5.6.10-60.2.rst 2013-03-08 07:34:35 +0000 |
3263 | @@ -0,0 +1,23 @@ |
3264 | +.. rn:: 5.6.10-60.2 |
3265 | + |
3266 | +============================== |
3267 | + |Percona Server| 5.6.10-60.2 |
3268 | +============================== |
3269 | + |
3270 | +Percona is glad to announce the Alpha release of |Percona Server| 5.6.10-60.2 on March 11, 2013 (Downloads are available `here <http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.10-60.2/>`_ and from the EXPERIMENTAL `Percona Software Repositories <http://www.percona.com/docs/wiki/repositories:start>`_). |
3271 | + |
3272 | +Based on `MySQL 5.6.10 <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-10.html>`_, including all the bug fixes in it, |Percona Server| 5.6.10-60.2 is the third ALPHA release in the |Percona Server| 5.6 series. All of |Percona|'s software is open-source and free, all the details of the release can be found in the `5.6.10-60.2 milestone at Launchpad <https://launchpad.net/percona-server/+milestone/5.6.10-60.2>`_. |
3273 | + |
3274 | +New Features |
3275 | +============= |
3276 | + |
3277 | + :ref:`expanded_innodb_fast_index_creation` has been ported from |Percona Server| 5.5 |
3278 | + |
3279 | + Ported the :ref:`threadpool` patch from |MariaDB|. This feature enables the server to keep the top performance even with the increased number of client connections. |
3280 | + |
3281 | + :ref:`innodb_corrupt_table_action_page` has been ported from |Percona Server| 5.5 |
3282 | + |
3283 | +Bug Fixes |
3284 | +========== |
3285 | + |
3286 | + Fixed the upstream :mysqlbug:`68116` that caused the server crash with assertion error when |InnoDB| monitor with verbose lock info was used under heavy load. This bug is affecting only ``-debug`` builds. Bug fixed :bug:`1100178` (*Laurynas Biveinis*). |
3287 | |
3288 | === modified file 'doc/source/release-notes/Percona-Server-5.6.5-60.0.rst' |
3289 | --- doc/source/release-notes/Percona-Server-5.6.5-60.0.rst 2013-02-08 16:56:30 +0000 |
3290 | +++ doc/source/release-notes/Percona-Server-5.6.5-60.0.rst 2013-03-08 07:34:35 +0000 |
3291 | @@ -14,6 +14,7 @@ |
3292 | ================= |
3293 | |
3294 | * Fixed upstream |MySQL| bug :mysqlbug:`49336`, mysqlbinlog couldn't handle ``stdin`` when "|" was used. Bug fixed: :bug:`933969` (*Sergei Glushchenko*). |
3295 | + * Fixed upstream |MySQL| bug :mysqlbug:`65946`, which could cause the server to segfault if if using ``libeatmydata`` (and possibly other LD_PRELOADs). Bug fixed: :bug:`933969` (*Stewart Smith*). |
3296 | * Added the ``TIME_MS`` column in the :table:`PROCESSLIST` table |
3297 | * :ref:`mysqldump_ignore_create_error` feature has been ported from |Percona Server| 5.5 |
3298 | * :variable:`fast_index_creation` feature has been ported from |Percona Server| 5.5 |
3299 | |
3300 | === modified file 'doc/source/release-notes/release-notes_index.rst' |
3301 | --- doc/source/release-notes/release-notes_index.rst 2013-03-05 12:46:43 +0000 |
3302 | +++ doc/source/release-notes/release-notes_index.rst 2013-03-08 07:34:35 +0000 |
3303 | @@ -6,4 +6,6 @@ |
3304 | :maxdepth: 1 |
3305 | :glob: |
3306 | |
3307 | - Percona-Server-5.6.* |
3308 | + Percona-Server-5.6.10-60.2 |
3309 | + Percona-Server-5.6.6-60.1 |
3310 | + Percona-Server-5.6.5-60.0 |
3311 | |
3312 | === removed file 'doc/source/reliability/error_pad.rst' |
3313 | --- doc/source/reliability/error_pad.rst 2013-03-05 12:46:43 +0000 |
3314 | +++ doc/source/reliability/error_pad.rst 1970-01-01 00:00:00 +0000 |
3315 | @@ -1,55 +0,0 @@ |
3316 | -.. _error_pad: |
3317 | - |
3318 | -========================== |
3319 | - Error Code Compatibility |
3320 | -========================== |
3321 | - |
3322 | -|Percona Server| with |XtraDB| has error code incompatibilities with |MySQL| 5.5. It is important to maintain compatibility in the error codes used by the servers. For example, scripts that may be run on both servers could contain references to error codes. |
3323 | - |
3324 | -The reasons for the current incompatibilities are: |
3325 | - |
3326 | - * |Percona Server| with |XtraDB| contains features that have been backported from MyQL 5.5. Some of the |MySQL| 5.5 features added new error codes. |
3327 | - |
3328 | - * Some |Percona Server| with |XtraDB| features have added new error codes. |
3329 | - |
3330 | -The solution to the first problem is to preserve |MySQL| 5.5 error codes in the |Percona Server|. An example of where this has been done is |Percona Server| feature Query Cache Enhancements. This feature adds error ``ER_QUERY_CACHE_DISABLED`` to the |Percona Server|, which is defined as error code 1651 in |MySQL| 5.5. |
3331 | - |
3332 | -After migrating |Percona Server| / |XtraDB| to |MySQL| 5.5, users might experience troubles because of this. |
3333 | - |
3334 | -The solution to the second problem is to insure that unique error codes are chosen, when adding new ones to |Percona Server|, that will never be duplicated during |MySQL| development. |
3335 | - |
3336 | -For example, |MySQL| has a tool ``comp_err`` that generates: |
3337 | - |
3338 | - - :file:`errmsg.sys` files |
3339 | - |
3340 | - - header file :file:`include/mysqld_error.h` |
3341 | - |
3342 | - - header file :file:`include/mysqld_ername.h` |
3343 | - |
3344 | -from the file :file:`errmsg.txt`. |
3345 | - |
3346 | -To keep error numbers consistent, we should add some fictive errors to :file:`errmsg.txt`, because ``comp_err`` assigns error code numbers sequentially, without gaps. |
3347 | - |
3348 | -I propose patch to ``comp_err``. |
3349 | - |
3350 | -This patch allows usage of a new syntax, with prefix ``PADD``, for example: :: |
3351 | - |
3352 | - PADD_QUERY_CACHE_DISABLED 1651 |
3353 | - eng "ER_QUERY_CACHE_DISABLED padding to 1651 error" |
3354 | - ER_QUERY_CACHE_DISABLED |
3355 | - eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" |
3356 | - |
3357 | -comp_err with my patch padds empty intervals (from last error code number to 1651) by error message ``ER_QUERY_CACHE_DISABLED padding to 1651 error``, i.e. and ``ER_QUERY_CACHE_DISABLED`` now has error code 1651 (as desired). I propose to use this patch for Percona errors, for example: :: |
3358 | - |
3359 | - PADD_PERCONA_NEW_ERROR_CODE 4000 |
3360 | - end "Padd empty space to error code number 4000 (Percona error codes)" |
3361 | - ...some percona error codes... |
3362 | - |
3363 | -Patch only adds prefix ``PADD_`` and padds error in sys files. All other |MySQL| code (load*.sys files, my_error, etc) works as old one. |
3364 | - |
3365 | - |
3366 | -Version-Specific Information |
3367 | -============================ |
3368 | - |
3369 | - * 5.5.8-20.0 |
3370 | - Full functionality available. |
3371 | |
3372 | === modified file 'doc/source/reliability/innodb_corrupt_table_action.rst' |
3373 | --- doc/source/reliability/innodb_corrupt_table_action.rst 2013-03-05 12:46:43 +0000 |
3374 | +++ doc/source/reliability/innodb_corrupt_table_action.rst 2013-03-08 07:34:35 +0000 |
3375 | @@ -11,32 +11,15 @@ |
3376 | Version Specific Information |
3377 | ============================ |
3378 | |
3379 | - * 5.5.10-20.1: |
3380 | - Renamed variable :variable:`innodb_pass_corrupt_table` to :variable:`innodb_corrupt_table_action`. |
3381 | + * 5.6.10-60.2: |
3382 | + Feature ported from |Percona Server| 5.5 |
3383 | |
3384 | System Variables |
3385 | ================ |
3386 | |
3387 | -.. variable:: innodb_pass_corrupt_table |
3388 | - |
3389 | - :version 5.5.10-20.1: Renamed. |
3390 | - :cli: Yes |
3391 | - :conf: Yes |
3392 | - :scope: Global |
3393 | - :dyn: Yes |
3394 | - :vartype: ULONG |
3395 | - :default: 0 |
3396 | - :range: 0 - 1 |
3397 | - |
3398 | - |
3399 | -Pass corruptions of user tables as ``corrupt table`` instead of crashing itself, when used with innodb_file_per_table. All file I/O for the datafile after detected as corrupt is disabled, except for the deletion. |
3400 | - |
3401 | - This variable was renamed to innodb_corrupt_table_action, beginning in release 5.5.10-20.1. It still exists as :variable:`innodb_pass_corrupt_table` in versions prior to that. |
3402 | - |
3403 | - |
3404 | .. variable:: innodb_corrupt_table_action |
3405 | |
3406 | - :version 5.5.10-20.1: Introduced. |
3407 | + :version 5.6.10-60.2: Introduced. |
3408 | :cli: Yes |
3409 | :conf: Yes |
3410 | :scope: Global |
3411 | @@ -45,6 +28,4 @@ |
3412 | :default: ``assert`` |
3413 | :range: ``assert``, ``warn`` |
3414 | |
3415 | -Pass corruptions of user tables as ``corrupt table`` instead of not crashing itself, when used with file_per_table. All file I/O for the datafile after detected as corrupt is disabled, except for the deletion. |
3416 | - |
3417 | - This variable was added in release 5.5.10-20.1. Prior to that, it was named :variable:`innodb_pass_corrupt_table`, which still exists in earlier versions. |
3418 | +When the default value is used InnoDB will stop the server if it finds a checksum mismatch on the tables. If warn values is used it will pass corruption of the table as corrupt table instead of crashing itself. For this to work variable :variable:`innodb_file_per_table` should be enabled. All file I/O for the datafile after detected as corrupt is disabled, except for the deletion. |
3419 | |
3420 | === removed file 'doc/source/reliability/log_connection_error.rst' |
3421 | --- doc/source/reliability/log_connection_error.rst 2013-03-05 12:46:43 +0000 |
3422 | +++ doc/source/reliability/log_connection_error.rst 1970-01-01 00:00:00 +0000 |
3423 | @@ -1,14 +0,0 @@ |
3424 | -.. _log_connection_error: |
3425 | - |
3426 | -============================== |
3427 | - Too Many Connections Warning |
3428 | -============================== |
3429 | - |
3430 | - |
3431 | -This feature issues the warning ``Too many connections`` to the log, if ``log_warnings`` is enabled. |
3432 | - |
3433 | -Version-Specific Information |
3434 | -============================ |
3435 | - |
3436 | - * 5.5.8-20.0: |
3437 | - Full functionality available. |
3438 | |
3439 | === removed file 'doc/source/reliability/show_slave_status_nolock.rst' |
3440 | --- doc/source/reliability/show_slave_status_nolock.rst 2012-02-13 08:49:21 +0000 |
3441 | +++ doc/source/reliability/show_slave_status_nolock.rst 1970-01-01 00:00:00 +0000 |
3442 | @@ -1,23 +0,0 @@ |
3443 | -.. _show_slave_status_nolock: |
3444 | - |
3445 | -================================= |
3446 | - Lock-Free ``SHOW SLAVE STATUS`` |
3447 | -================================= |
3448 | - |
3449 | -The ``STOP SLAVE`` and ``SHOW SLAVE STATUS`` commands can conflict due to a global lock in the situation where one thread on a slave attempts to execute a ``STOP SLAVE`` command, while a second thread on the slave is already running a command that takes a long time to execute. |
3450 | - |
3451 | -If a ``STOP SLAVE`` command is given in this situation, it will wait and not complete execution until the long-executing thread has completed its task. If another thread now executes a ``SHOW SLAVE STATUS`` command while the STOP SLAVE command is waiting to complete, the ``SHOW SLAVE STATUS`` command will not be able to execute while the ``STOP SLAVE`` command is waiting. |
3452 | - |
3453 | -This features modifies the ``SLOW SLAVE STATUS`` syntax to allow: :: |
3454 | - |
3455 | - SLOW SLAVE STATUS NOLOCK |
3456 | - |
3457 | -This will display the slave's status as if there were no lock, allowing the user to detect and understand the situation that is occurring. |
3458 | - |
3459 | -**NOTE:** The information given when ``NOLOCK`` is used may be slightly inconsistent with the actual situation while the lock is being held. |
3460 | - |
3461 | - |
3462 | -Version Specific Information |
3463 | -============================ |
3464 | - |
3465 | - * 5.5.8-20.0: |
3466 | |
3467 | === removed file 'doc/source/scalability/innodb_adaptive_hash_index_partitions.rst' |
3468 | --- doc/source/scalability/innodb_adaptive_hash_index_partitions.rst 2013-03-05 12:46:43 +0000 |
3469 | +++ doc/source/scalability/innodb_adaptive_hash_index_partitions.rst 1970-01-01 00:00:00 +0000 |
3470 | @@ -1,38 +0,0 @@ |
3471 | -.. _innodb_adaptive_hash_index_partitions_page: |
3472 | - |
3473 | -========================================== |
3474 | - Multiple Adaptive Hash Search Partitions |
3475 | -========================================== |
3476 | - |
3477 | -The |InnoDB| adaptive hash index can have contention issues on multi-core systems when you run a mix of read and write queries that need to scan secondary indexes. This feature splits the adaptive hash index across several partitions to avoid such problems. |
3478 | - |
3479 | -The number of adaptive hash partitions specified by the variable ``innodb_adaptive_hash_index_partitions`` are created, and hash indexes are assigned to each one based on ``index_id``. This should help to solve contention problems in the adaptive hash search process when they occur. |
3480 | - |
3481 | - |
3482 | -Version Specific Information |
3483 | - |
3484 | -|Percona Server| Version Comments |
3485 | -5.5.8-20.0 Initially released. |
3486 | - |
3487 | -System Variables |
3488 | ----------------- |
3489 | - |
3490 | -.. variable:: innodb_adaptive_hash_index_partitions |
3491 | - |
3492 | - :version 5.5.8-20.0: Introduced |
3493 | - :cli: Yes |
3494 | - :conf: Yes |
3495 | - :scope: Global |
3496 | - :dyn: No |
3497 | - :vartype: Numeric |
3498 | - :def: 1 |
3499 | - :range: 0-MAXINT |
3500 | - |
3501 | -Specifies the number of partitions to use in the adaptive hash search process. |
3502 | - |
3503 | -When set to zero, no extra partitions are created and the normal process is in effect. When greater than zero, the specified number of partitions are created across which to perform the adaptive search. |
3504 | - |
3505 | -Other reading |
3506 | -------------- |
3507 | - |
3508 | - * `Index lock and adaptive search <http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search-next-two-biggest-innodb-problems/>`_ |
3509 | |
3510 | === removed file 'doc/source/scalability/innodb_expand_undo_slots.rst' |
3511 | --- doc/source/scalability/innodb_expand_undo_slots.rst 2012-02-16 10:24:41 +0000 |
3512 | +++ doc/source/scalability/innodb_expand_undo_slots.rst 1970-01-01 00:00:00 +0000 |
3513 | @@ -1,44 +0,0 @@ |
3514 | -.. _innodb_expand_undo_slots: |
3515 | - |
3516 | -====================================== |
3517 | -More Concurrent Transactions Available |
3518 | -====================================== |
3519 | - |
3520 | -|InnoDB| provides a fixed number of 1024 undo slots in its rollback segment, leaving room for 1024 transactions to run in parallel. If all the slots are used any new transaction will fail until a slot is freed, which can cause strange behaviors. This change provides a variable to expand the number of undo slots, up to 4072. |
3521 | - |
3522 | -This option is provided for servers that run out of undo slots. Use it if you find the following warning in the error log: ``Warning: cannot find a free slot for an undo log``. |
3523 | - |
3524 | -We discourage its use unless you get this warning, because it breaks compatibility with other programs. Specifically, it makes the datafiles unusable for ibbackup or for a |MySQL| server that is not run with this option. |
3525 | - |
3526 | -When you enable the option, the maximum number of undo slots is extended to 4072, instead of the default fixed value of 1024. |
3527 | - |
3528 | -You can then check whether the expanded slots (1025-4072) are used by starting :command:`mysqld` with ``innodb_extra_undoslots=OFF``: |
3529 | - |
3530 | - * If the expanded slots are used: :command:`mysqld` refuses to start and prints an error in the error log: :: |
3531 | - |
3532 | - InnoDB: Error: innodb_extra_undoslots option is disabled, but it was enabled before. |
3533 | - InnoDB: The datafile is not normal for |MySQL|d and disabled innodb_extra_undoslots. |
3534 | - InnoDB: Enable innodb_extra_undoslots if it was enabled before, and |
3535 | - InnoDB: ### don't use this datafile with other |MySQL|d or ibbackup! ### |
3536 | - InnoDB: Cannot continue operation for the safety. Calling exit(1). |
3537 | - |
3538 | - * If the expanded slots are not used: :command:`mysqld` starts and prints only a warning in the error log: :: |
3539 | - |
3540 | - InnoDB: Warning: innodb_extra_undoslots option is disabled, but it was enabled before. |
3541 | - InnoDB: But extended undo slots seem not used, so continue operation. |
3542 | - |
3543 | - |
3544 | -System Variables |
3545 | ----------------- |
3546 | - |
3547 | -.. psdom:variable:: innodb_extra_undoslots |
3548 | - |
3549 | - :cli: Yes |
3550 | - :Conf: Yes |
3551 | - :scope: Global |
3552 | - :dyn: No |
3553 | - :vartype: BOOL |
3554 | - :def: OFF |
3555 | - :range: ON/OFF |
3556 | - |
3557 | -If ``ON``, expands the number of undo slots to 4072. |
3558 | |
3559 | === removed file 'doc/source/scalability/innodb_extra_rseg.rst' |
3560 | --- doc/source/scalability/innodb_extra_rseg.rst 2013-03-05 12:46:43 +0000 |
3561 | +++ doc/source/scalability/innodb_extra_rseg.rst 1970-01-01 00:00:00 +0000 |
3562 | @@ -1,49 +0,0 @@ |
3563 | -.. _innodb_extra_rseg: |
3564 | - |
3565 | -============================ |
3566 | - Multiple Rollback Segments |
3567 | -============================ |
3568 | - |
3569 | -In Percona Server 5.1, an improvement was provided for write-intensive |
3570 | -workloads that allowed multiple rollback segments to be used. It has |
3571 | -been removed from |Percona Server| 5.5.11-20.2 because an equivalent |
3572 | -variable, ``innodb_rollback_segment``, has been implemented in |MySQL| |
3573 | -5.5, and so the MySQL 5.5 implementation is available in Percona |
3574 | -Server 5.5. |
3575 | - |
3576 | -Percona Server, in addition to the upstream multiple rollback segment implementation, provides the additonal Information Schema table: INFORMATION_SCHEMA.INNODB_RSEG. |
3577 | - |
3578 | -``INFORMATION_SCHEMA`` Tables |
3579 | -============================= |
3580 | - |
3581 | -This feature provides the following table: |
3582 | - |
3583 | -.. table:: INFORMATION_SCHEMA.INNODB_RSEG |
3584 | - |
3585 | - :column rseg_id: rollback segment id |
3586 | - :column space_id: space where the segment placed |
3587 | - :column zip_size: compressed page size in bytes if compressed otherwise 0 |
3588 | - :column page_no: page number of the segment header |
3589 | - :column max_size: max size in pages |
3590 | - :column curr_size: current size in pages |
3591 | - |
3592 | -This table shows information about all the rollback segments (the default segment and the extra segments). |
3593 | - |
3594 | -Here is an example of output with ``innodb_extra_rsegments = 8`` :: |
3595 | - |
3596 | - mysql> select * from information_schema.innodb_rseg; |
3597 | - +---------+----------+----------+---------+------------+-----------+ |
3598 | - | rseg_id | space_id | zip_size | page_no | max_size | curr_size | |
3599 | - +---------+----------+----------+---------+------------+-----------+ |
3600 | - | 0 | 0 | 0 | 6 | 4294967294 | 1 | |
3601 | - | 1 | 0 | 0 | 13 | 4294967294 | 2 | |
3602 | - | 2 | 0 | 0 | 14 | 4294967294 | 1 | |
3603 | - | 3 | 0 | 0 | 15 | 4294967294 | 1 | |
3604 | - | 4 | 0 | 0 | 16 | 4294967294 | 1 | |
3605 | - | 5 | 0 | 0 | 17 | 4294967294 | 1 | |
3606 | - | 6 | 0 | 0 | 18 | 4294967294 | 1 | |
3607 | - | 7 | 0 | 0 | 19 | 4294967294 | 1 | |
3608 | - | 8 | 0 | 0 | 20 | 4294967294 | 1 | |
3609 | - +---------+----------+----------+---------+------------+-----------+ |
3610 | - 9 rows in set (0.00 sec) |
3611 | - |
3612 | |
3613 | === removed file 'doc/source/scalability/innodb_insert_buffer.rst' |
3614 | --- doc/source/scalability/innodb_insert_buffer.rst 2013-03-05 12:46:43 +0000 |
3615 | +++ doc/source/scalability/innodb_insert_buffer.rst 1970-01-01 00:00:00 +0000 |
3616 | @@ -1,68 +0,0 @@ |
3617 | -.. _innodb_insert_buffer: |
3618 | - |
3619 | -========================== |
3620 | -Configurable Insert Buffer |
3621 | -========================== |
3622 | - |
3623 | -Percona has implemented several changes related to MySQL's InnoDB Insert Buffer. These features enable adjusting the insert buffer to the different workloads and hardware configurations. |
3624 | - |
3625 | -System variables: |
3626 | -================= |
3627 | - |
3628 | -.. variable:: innodb_ibuf_active_merge |
3629 | - |
3630 | - :version 5.5.8-20.0: Introduced |
3631 | - :cli: Yes |
3632 | - :conf: Yes |
3633 | - :scope: Global |
3634 | - :dyn: Yes |
3635 | - :vartype: Numeric |
3636 | - :default: 0 (~1.0.5), 1 (1.0.6~) |
3637 | - :range: 0 - 1 |
3638 | - |
3639 | -This variable specifies whether the insert buffer can be processed before it reaches its maximum size. The following values are allowed: |
3640 | - |
3641 | - * 0: |
3642 | - the insert buffer is not processed until it is full. This is the standard |InnoDB| behavior. |
3643 | - |
3644 | - * 1: |
3645 | - the insert buffer can be processed even it is not full. |
3646 | - |
3647 | -.. variable:: innodb_ibuf_merge_rate |
3648 | - |
3649 | - :version 5.5.8-20.0: Introduced |
3650 | - :cli: Yes |
3651 | - :conf: Yes |
3652 | - :scope: Global |
3653 | - :dyn: Yes |
3654 | - :default: 100 |
3655 | - :range: 100 - 999999999 |
3656 | - |
3657 | -This variable allows better control of the background thread processing the insert buffer. Each time the thread is called, its activity is altered by the value of both ``innodb_io_capacity`` and ``innodb_ibuf_merge_rate`` this way: :: |
3658 | - |
3659 | - [real activity] = [default activity] * (innodb_io_capacity/100) * (innodb_ibuf_merge_rate/100) |
3660 | - |
3661 | -By increasing the value of ``innodb_ibuf_merge_rate``, you will increase the insert buffer activity. |
3662 | - |
3663 | -.. variable:: innodb_ibuf_max_size |
3664 | - |
3665 | - :cli: Yes |
3666 | - :conf: Yes |
3667 | - :scope: Global |
3668 | - :dyn: No |
3669 | - :vartype: Numeric |
3670 | - :default: Half the size of the |InnoDB| buffer pool |
3671 | - :range: 0 - Half the size of the |InnoDB| buffer pool |
3672 | - :unit: Bytes |
3673 | - |
3674 | -This variable specifies the maximum size of the insert buffer. By default the insert buffer is half the size of the buffer pool so if you have a very large buffer pool, the insert buffer will be very large too and you may want to restrict its size with this variable. |
3675 | - |
3676 | -Setting this variable to 0 is equivalent to disabling the insert buffer. But then all changes to secondary indexes will be performed synchronously which will probably cause performance degradation. Likewise a too small value can hurt performance. |
3677 | - |
3678 | -If you have very fast storage (ie storage with RAM-level speed, not just a RAID with fast disks), a value of a few MB may be the best choice for maximum performance. |
3679 | - |
3680 | -Other Reading |
3681 | -============= |
3682 | - |
3683 | -* `Some little known facts about InnoDB Insert Buffer <http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/>`_ |
3684 | -* `5.0.75-build12 Percona binaries <http://www.mysqlperformanceblog.com/2009/01/23/5075-build12-percona-binaries/>`_ |
3685 | |
3686 | === removed file 'doc/source/scalability/innodb_split_buf_pool_mutex.rst' |
3687 | --- doc/source/scalability/innodb_split_buf_pool_mutex.rst 2013-03-05 12:46:43 +0000 |
3688 | +++ doc/source/scalability/innodb_split_buf_pool_mutex.rst 1970-01-01 00:00:00 +0000 |
3689 | @@ -1,77 +0,0 @@ |
3690 | -.. _innodb_split_buf_pool_mutex: |
3691 | - |
3692 | -================================== |
3693 | - Improved Buffer Pool Scalability |
3694 | -================================== |
3695 | - |
3696 | -The |InnoDB| buffer pool is a well known point of contention when many queries are executed concurrently. In |XtraDB|, the global mutex protecting the buffer pool has been split into several mutexes to decrease contention. |
3697 | - |
3698 | -This feature splits the single global InnoDB buffer pool mutex into several mutexes: |
3699 | - |
3700 | -.. list-table:: |
3701 | - :widths: 20 40 |
3702 | - :header-rows: 1 |
3703 | - |
3704 | - * - Name |
3705 | - - Protects |
3706 | - * - buf_pool_mutex |
3707 | - - flags about IO |
3708 | - * - LRU_list_mutex |
3709 | - - LRU list of blocks in buffer pool |
3710 | - * - flush_list_mutex |
3711 | - - flush list of dirty blocks to flush |
3712 | - * - page_hash_latch |
3713 | - - hash table to search blocks in buffer pool |
3714 | - * - free_list_mutex |
3715 | - - list of free blocks in buffer pool |
3716 | - * - zip_free_mutex |
3717 | - - lists of free area to treat compressed pages |
3718 | - * - zip_hash_mutex |
3719 | - - hash table to search compressed pages |
3720 | - |
3721 | -The goal of this change is to reduce mutex contention, which can be very impacting when the working set does not fit in memory. |
3722 | - |
3723 | -Other Information |
3724 | -================= |
3725 | - |
3726 | -Detecting Mutex Contention |
3727 | --------------------------- |
3728 | - |
3729 | -You can detect when you suffer from mutex contention in the buffer pool by reading the information provided in the SEMAPHORES section of the output of SHOW INNODB STATUS: |
3730 | - |
3731 | -Under normal circumstances this section should look like this: |
3732 | - |
3733 | -.. code-block:: guess |
3734 | - |
3735 | - SEMAPHORES |
3736 | - ---------- |
3737 | - OS WAIT ARRAY INFO: reservation count 50238, signal count 17465 |
3738 | - Mutex spin waits 0, rounds 628280, OS waits 31338 |
3739 | - RW-shared spins 38074, OS waits 18900; RW-excl spins 0, OS waits 0 |
3740 | - |
3741 | -If you have a high-concurrency workload this section may look like this: |
3742 | - |
3743 | -.. code-block:: guess |
3744 | - |
3745 | - 1 ---------- |
3746 | - 2 SEMAPHORES |
3747 | - 3 ---------- |
3748 | - 4 OS WAIT ARRAY INFO: reservation count 36255, signal count 12675 |
3749 | - 5 --Thread 10607472 has waited at buf/buf0rea.c line 420 for 0.00 seconds the semaphore: |
3750 | - 6 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0 |
3751 | - 7 waiters flag 0 |
3752 | - 8 --Thread 3488624 has waited at buf/buf0buf.c line 1177 for 0.00 seconds the semaphore: |
3753 | - 9 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0 |
3754 | - 10 waiters flag 0 |
3755 | - 11 --Thread 6896496 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: |
3756 | - 12 S-lock on RW-latch at 0x8800244 created in file buf/buf0buf.c line 547 |
3757 | - 13 a writer (thread id 14879600) has reserved it in mode exclusive |
3758 | - 14 number of readers 0, waiters flag 1 |
3759 | - 15 Last time read locked in file btr/btr0cur.c line 442 |
3760 | - 16 Last time write locked in file buf/buf0buf.c line 1797 |
3761 | - [...] |
3762 | - 17 Mutex spin waits 0, rounds 452650, OS waits 22573 |
3763 | - 18 RW-shared spins 27550, OS waits 13682; RW-excl spins 0, OS waits 0 |
3764 | - |
3765 | - |
3766 | -Note that in the second case you will see indications that threads are waiting for a mutex created in the file buf/buf0buf.c (lines 5 to 7 or 8 to 10). Such an indication is a sign of buffer pool contention. |
3767 | |
3768 | === added file 'doc/source/upstream-bug-fixes.rst' |
3769 | --- doc/source/upstream-bug-fixes.rst 1970-01-01 00:00:00 +0000 |
3770 | +++ doc/source/upstream-bug-fixes.rst 2013-03-08 07:34:35 +0000 |
3771 | @@ -0,0 +1,19 @@ |
3772 | +.. _upstream_bug_fixes: |
3773 | + |
3774 | +============================================================= |
3775 | + List of upstream |MySQL| bugs fixed in |Percona Server| 5.6 |
3776 | +============================================================= |
3777 | + |
3778 | ++-------------------------------------------------------------------------------------------------------------+ |
3779 | +|:Upstream bug: :mysqlbug:`68116` - InnoDB monitor may hit an assertion error in buf_page_get_gen in debug ...| |
3780 | +|:Launchpad bug: :bug:`1100178` | |
3781 | +|:Upstream state: Analyzing (checked on 2013-02-21) | |
3782 | +|:Fix Released: :rn:`5.6.10-60.2` | |
3783 | +|:Upstream fix: N/A | |
3784 | ++-------------------------------------------------------------------------------------------------------------+ |
3785 | +|:Upstream bug: :mysqlbug:`65946` - Sid_map::Sid_map calls DBUG which may have unitialized THR_KEY_mysys and..| |
3786 | +|:Launchpad bug: :bug:`1050758` | |
3787 | +|:Upstream state: Duplicate | |
3788 | +|:Fix Released: :rn:`5.6.5-60.0` | |
3789 | +|:Upstream fix: N/A | |
3790 | ++-------------------------------------------------------------------------------------------------------------+ |
also merged into release branch