Merge lp:~hrvojem/percona-server/doc-28 into lp:percona-server/5.6

Proposed by Hrvoje Matijakovic
Status: Merged
Approved by: Laurynas Biveinis
Approved revision: no longer in the source branch.
Merged at revision: 375
Proposed branch: lp:~hrvojem/percona-server/doc-28
Merge into: lp:percona-server/5.6
Diff against target: 2374 lines (+751/-859)
35 files modified
doc/source/changed_in_56.rst (+4/-4)
doc/source/conf.py (+3/-1)
doc/source/diagnostics/innodb_deadlock_count.rst (+1/-1)
doc/source/diagnostics/innodb_show_status.rst (+135/-164)
doc/source/diagnostics/misc_info_schema_tables.rst (+40/-0)
doc/source/diagnostics/mysql_syslog.rst (+2/-2)
doc/source/diagnostics/show_engines.rst (+1/-7)
doc/source/diagnostics/slow_extended.rst (+355/-0)
doc/source/diagnostics/slow_extended_55.rst (+0/-377)
doc/source/diagnostics/thread_based_profiling.rst (+2/-2)
doc/source/diagnostics/user_stats.rst (+5/-5)
doc/source/flexibility/buff_read_ahead_area.rst (+0/-36)
doc/source/flexibility/innodb_files_extend.rst (+0/-25)
doc/source/flexibility/log_warnings_suppress.rst (+3/-11)
doc/source/flexibility/max_binlog_files.rst (+2/-2)
doc/source/index.rst (+18/-4)
doc/source/index_info_schema_tables.rst (+8/-36)
doc/source/installation.rst (+1/-1)
doc/source/management/changed_page_tracking.rst (+4/-5)
doc/source/management/enforce_engine.rst (+1/-1)
doc/source/management/expanded_program_option_modifiers.rst (+1/-1)
doc/source/management/innodb_fake_changes.rst (+12/-2)
doc/source/management/innodb_fast_index_creation.rst (+0/-63)
doc/source/management/innodb_kill_idle_trx.rst (+1/-1)
doc/source/management/pam_plugin.rst (+1/-1)
doc/source/management/secure_file_priv_extended.rst (+1/-1)
doc/source/management/udf_maatkit.rst (+0/-49)
doc/source/management/udf_percona_toolkit.rst (+54/-0)
doc/source/management/utility_user.rst (+4/-4)
doc/source/performance/innodb_numa_support.rst (+1/-1)
doc/source/performance/query_cache_enhance.rst (+8/-42)
doc/source/reliability/error_pad.rst (+6/-6)
doc/source/reliability/log_connection_error.rst (+2/-2)
doc/source/reliability/show_slave_status_nolock.rst (+4/-2)
doc/source/scalability/innodb_io.rst (+71/-0)
To merge this branch: bzr merge lp:~hrvojem/percona-server/doc-28
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Approve
Review via email: mp+166285@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

- Please rebase on the latest trunk that has the file-id fixes. They have touched docs as well.

- Please also use https://code.launchpad.net/~laurynas-biveinis/percona-server/5.6-merge-4 as a prerequisite branch. It merges more 5.5 revisions, including doc ones.

review: Needs Resubmitting
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

    - the list of status variables itself does not look updated?
      i.e. AFAIK it should not have 1 or 10 second loops one but
      should have some new ones like background or active loops.
    - innodb_show_status.rst: just "feature ported ... ", no need for
      the other changes under version-specific change list.
    - doc/source/diagnostics/innodb_stats.rst - I think it's
      redundant, it should be fully replaced by the upstream
      features, so it needs upgrade documentation.
      - innodb_stats_method is an upstream feature.
      - other variables do not exist.
      - INNODB_SYS_TABLES, INNODB_SYS_TABLESTATS, INNODB_SYS_INDEXES,
        INNODB_SYS_COLUMNS, INNODB_SYS_FIELDS, INNODB_SYS_FOREIGN,
        INNODB_SYS_FOREIGN_COLS, INNODB_TABLE_STATS,
        INNODB_INDEX_STATS are upstream tables. Their schemas are
        different too IIRC.
    - doc/source/diagnostics/slow_extended.rst:
      - Maciej is no longer with Percona
      - No need to document upstream variables
        log_slow_admin_statements and log_slow_slave_statements
      - The log entry example is outdated, i.e. rows_read: is not
        there anymore, needs updating to the actual current output.
      - The profiling one too.
    - doc/source/diagnostics/user_stats.rst:
      - 5.6.11 changes: "Feature ported from PS 5.5"
    - doc/source/index_info_schema_tables.rst:
      - XTRADB_INTERNAL_HASH_TABLES, XTRADB_READ_VIEW, XTRADB_RSEG
        tables do not exist?
    - doc/source/installation.rst:
      ":ref:`pam_plugin` is not built with the server by
      default. ... "
    - doc/source/management/innodb_fast_index_creation.rst has been
      ported to 5.6.
    - doc/source/management/pam_plugin.rst: "integrated with |Percona
      Server|."

review: Needs Fixing
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

    - Line 59: bug 29123/29126 conflict, probably in the lower
      versions as well.
    - Line 102: should be fixed in lower versions as well?
    - Line 702: can be removed (also from 5.5 too I guess).
    - doc/source/diagnostics/thread_based_profiling.rst has been
      ported to 5.6.
    - Where are innodb_log_block_size docs?

Should I report the lower version issues separately?

review: Needs Fixing
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

    - innodb_io.rst: innodb_flush_method=ALL_O_DIRECT (dropped),
      innodb_log_file_size (replaced in upstream) have not been
      ported. innodb_purge_threads default has not been ported
      (replaced in upstream). That leaves ported status vars,
      innodb_log_block_size, and
      innodb_use_global_flush_log_at_trx_commit. The introduction
      needs updating, it doesn't seem to have received any since 5.0,
      i.e. "Note that some of these parameters are already available
      in the |InnoDB| plugin." should be removed, the rest rewritten
      perhaps.

review: Needs Fixing
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) :
review: Approve
Revision history for this message
Stewart Smith (stewart) wrote :

Hrvoje Matijakovic <email address hidden> writes:
> - * 5.5.8-20.0:
> + * :rn:`5.6.11-60.3`:

I wonder if we should keep the 5.5 introduction number in the 5.6 docs
too (perhaps as well as the 5.6 introduction version).

--
Stewart Smith

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Stewart -

What would be the advantage? The 5.5 docs are there too. I'd prefer to have only 5.6 versions in 5.6, I think I already asked Hrvoje to remove the 5.5 versions earlier

Revision history for this message
Stewart Smith (stewart) wrote :

Laurynas Biveinis <email address hidden> writes:
> What would be the advantage? The 5.5 docs are there too. I'd prefer to
> have only 5.6 versions in 5.6, I think I already asked Hrvoje to
> remove the 5.5 versions earlier

I guess it's fine either way....

--
Stewart Smith

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'doc/source/changed_in_56.rst'
--- doc/source/changed_in_56.rst 2013-05-10 13:29:38 +0000
+++ doc/source/changed_in_56.rst 2013-06-03 10:03:31 +0000
@@ -10,15 +10,15 @@
1010
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)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)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.13 * SHOW [GLOBAL] TEMPORARY TABLES functionality is now only available via the INFORMATION_SCHEMA tables :table:`TEMPORARY_TABLES` and :table:`GLOBAL_TEMPORARY_TABLES`.
14 * `InnoDB timer-based Concurrency Throttling <http://www.percona.com/doc/percona-server/5.5/performance/innodb_thread_concurrency_timer_based.html>`_14 * `InnoDB timer-based Concurrency Throttling <http://www.percona.com/doc/percona-server/5.5/performance/innodb_thread_concurrency_timer_based.html>`_
15 * `InnoDB Recovery Stats <http://www.percona.com/doc/percona-server/5.5/management/innodb_recovery_patches.html>`_15 * `InnoDB Recovery Stats <http://www.percona.com/doc/percona-server/5.5/management/innodb_recovery_patches.html>`_
1616
17Some 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:17Some 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:
1818
19 * SHOW INNODB STATUS section "OLDEST VIEW" has been replaced by the XTRADB_READ_VIEW INFORMATION_SCHEMA table.19 * ``SHOW ENGINE INNODB STATUS`` section "OLDEST VIEW" has been replaced by the :table:`XTRADB_READ_VIEW` INFORMATION_SCHEMA table.
20 * SHOW INNODB STATUS sections on memory usage for InnoDB/XtraDB hash tables has been replaced by the XTRADB_INTERNAL_HASH_TABLES INFORMATION_SCHEMA table.20 * ``SHOW ENGINE INNODB STATUS`` sections on memory usage for InnoDB/XtraDB hash tables has been replaced by the :table:`XTRADB_INTERNAL_HASH_TABLES` INFORMATION_SCHEMA table.
21 * The INNODB_RSEG table has been renamed to XTRADB_RSEG21 * The :table:`INNODB_RSEG` table has been renamed to :table:`XTRADB_RSEG`.
2222
23Some |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:23Some |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:
2424
2525
=== modified file 'doc/source/conf.py'
--- doc/source/conf.py 2013-05-27 03:01:29 +0000
+++ doc/source/conf.py 2013-06-03 10:03:31 +0000
@@ -108,6 +108,8 @@
108108
109.. |XtraBackup| replace:: *XtraBackup*109.. |XtraBackup| replace:: *XtraBackup*
110110
111.. |Percona XtraBackup| replace:: *Percona XtraBackup*
112
111.. |Percona Server| replace:: *Percona Server*113.. |Percona Server| replace:: *Percona Server*
112114
113.. |Percona| replace:: *Percona*115.. |Percona| replace:: *Percona*
@@ -265,6 +267,6 @@
265intersphinx_mapping = {267intersphinx_mapping = {
266 'ps51' : ('http://www.percona.com/doc/percona-server/5.1/', None),268 'ps51' : ('http://www.percona.com/doc/percona-server/5.1/', None),
267 'ps55' : ('http://www.percona.com/doc/percona-server/5.5/', None),269 'ps55' : ('http://www.percona.com/doc/percona-server/5.5/', None),
268 'xtrabackup' : ('http://www.percona.com/doc/percona-xtrabackup', None),270 'xtrabackup' : ('http://www.percona.com/doc/percona-xtrabackup/2.1/', None),
269 'ptoolkit' : ('http://www.percona.com/doc/percona-toolkit/2.1/', None)271 'ptoolkit' : ('http://www.percona.com/doc/percona-toolkit/2.1/', None)
270 }272 }
271273
=== modified file 'doc/source/diagnostics/innodb_deadlock_count.rst'
--- doc/source/diagnostics/innodb_deadlock_count.rst 2013-05-27 13:59:13 +0000
+++ doc/source/diagnostics/innodb_deadlock_count.rst 2013-06-03 10:03:31 +0000
@@ -36,7 +36,7 @@
36Version Specific Information36Version Specific Information
37============================37============================
3838
39 * 5.5.8-20.0:39 * :rn:`5.6.11-60.3`:
40 Full functionality available.40 Full functionality available.
4141
42Status Variables42Status Variables
4343
=== modified file 'doc/source/diagnostics/innodb_show_status.rst'
--- doc/source/diagnostics/innodb_show_status.rst 2013-05-10 09:39:17 +0000
+++ doc/source/diagnostics/innodb_show_status.rst 2013-06-03 10:03:31 +0000
@@ -1,16 +1,16 @@
1.. _innodb_show_status:1.. _innodb_show_status:
22
3======================3======================================
4 Show |InnoDB| Status4 Extended Show Engine |InnoDB| Status
5======================5======================================
66
7This 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.7This feature reorganizes the output of ``SHOW ENGINE 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.
88
9This feature modified the ``SHOW INNODB STATUS`` command as follows:9This feature modified the ``SHOW ENGINE INNODB STATUS`` command as follows:
1010
11 * ``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.11 * ``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.
1212
13 * Added two variables to control ``SHOW INNODB STATUS`` information presented (bugfix for `#29123 <http://bugs.mysql.com/bug.php?id=29126>`_):13 * Added two variables to control ``SHOW ENGINE INNODB STATUS`` information presented (bugfix for `#29123 <http://bugs.mysql.com/bug.php?id=29123>`_):
1414
15 * :variable:`innodb_show_verbose_locks` - Whether to show records locked15 * :variable:`innodb_show_verbose_locks` - Whether to show records locked
1616
@@ -18,19 +18,15 @@
1818
19 * 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.19 * 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.
2020
21 * Added additional LOG section information (beginning in release 5.5.8-20.0).21 * Added additional LOG section information.
2222
23Version Specific Information23Version Specific Information
24============================24============================
2525
26 * 5.5.8-20.026 * :rn:`5.6.11-60.3`:
27 Added status variables showing information from ``SHOW INNODB STATUS``.27
2828 Feature ported from |Percona Server| 5.5.
29 * 5.5.8-20.029
30 Added additional information in the LOG section.
31
32 * 5.5.10-20.1:
33 Renamed status variable :variable:`innodb_row_lock_numbers` to :variable:`innodb_current_row_locks`.
3430
35Other Information31Other Information
36=================32=================
@@ -52,7 +48,7 @@
52 :default: 048 :default: 0
53 :range: 0 - 149 :range: 0 - 1
5450
55Specifies 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.51Specifies to show records locked in ``SHOW ENGINE 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.
5652
57.. variable:: innodb_show_locks_held53.. variable:: innodb_show_locks_held
5854
@@ -64,62 +60,44 @@
64 :default: 1060 :default: 10
65 :range: 0 - 100061 :range: 0 - 1000
6662
67Specifies the number of locks held to print for each |InnoDB| transaction in SHOW INNODB STATUS.63Specifies the number of locks held to print for each |InnoDB| transaction in ``SHOW ENGINE INNODB STATUS``.
6864
6965
70Status Variables66Status Variables
71================67================
7268
73The 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.69The status variables here contain information available in the output of ``SHOW ENGINE INNODB STATUS``, organized by the sections ``SHOW ENGINE INNODB STATUS`` displays. If you are familiar with the output of ``SHOW ENGINE INNODB STATUS``, you will probably already recognize the information these variables contain.
7470
7571
76BACKGROUND THREAD72BACKGROUND THREAD
77-----------------73-----------------
7874
79The following variables contain information in the BACKGROUND THREAD section of the output from ``SHOW INNODB STATUS``. An example of that output is:75The following variables contain information in the BACKGROUND THREAD section of the output from ``SHOW ENGINE INNODB STATUS``. An example of that output is: ::
8076
81 Insert an example of BACKGROUND THREAD section output here.77 -----------------
8278 BACKGROUND THREAD
83.. variable:: innodb_master_thread_1_second_loops79 -----------------
8480 srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 11844 srv_idle
85 :version 5.5.8-20.0: Introduced.81 srv_master_thread log flush and writes: 11844
86 :vartype: Numeric82
87 :scope: Global83
8884.. variable:: innodb_master_thread_active_loops
89.. variable:: innodb_master_thread_10_second_loops85
9086 :version 5.6.11-60.3: Introduced.
91 :version 5.5.8-20.0: Introduced.87 :vartype: Numeric
92 :vartype: Numeric88 :scope: Global
93 :scope: Global89
9490.. variable:: innodb_master_thread_idle_loops
95.. variable:: innodb_master_thread_background_loops91
9692 :version 5.6.11-60.3: Introduced.
97 :version 5.5.8-20.0: Introduced.93 :vartype: Numeric
98 :vartype: Numeric94 :scope: Global
99 :scope: Global95
100
101.. variable:: innodb_master_thread_main_flush_loops
102
103 :version 5.5.8-20.0: Introduced.
104 :vartype: Numeric
105 :scope: Global
106
107.. variable:: innodb_master_thread_sleeps
108
109 :version 5.5.8-20.0: Introduced.
110 :vartype: Numeric
111 :scope: Global
112
113.. variable:: innodb_background_log_sync
114
115 :version 5.5.8-20.0: Introduced.
116 :vartype: Numeric
117 :scope: Global
11896
119SEMAPHORES97SEMAPHORES
120----------98----------
12199
122The following variables contain information in the SEMAPHORES section of the output from ``SHOW INNODB STATUS``. An example of that output is: ::100The following variables contain information in the SEMAPHORES section of the output from ``SHOW ENGINE INNODB STATUS``. An example of that output is: ::
123101
124 ----------102 ----------
125 SEMAPHORES103 SEMAPHORES
@@ -131,281 +109,255 @@
131109
132.. variable:: innodb_mutex_os_waits110.. variable:: innodb_mutex_os_waits
133111
134 :version 5.5.8-20.0: Introduced.112 :version 5.6.11-60.3: Introduced.
135 :vartype: Numeric113 :vartype: Numeric
136 :scope: Global114 :scope: Global
137115
138.. variable:: innodb_mutex_spin_rounds116.. variable:: innodb_mutex_spin_rounds
139117
140 :version 5.5.8-20.0: Introduced.118 :version 5.6.11-60.3: Introduced.
141 :vartype: Numeric119 :vartype: Numeric
142 :scope: Global120 :scope: Global
143121
144.. variable:: innodb_mutex_spin_waits122.. variable:: innodb_mutex_spin_waits
145123
146 :version 5.5.8-20.0: Introduced.124 :version 5.6.11-60.3: Introduced.
147 :vartype: Numeric125 :vartype: Numeric
148 :scope: Global126 :scope: Global
149127
150.. variable:: innodb_s_lock_os_waits128.. variable:: innodb_s_lock_os_waits
151129
152 :version 5.5.8-20.0: Introduced.130 :version 5.6.11-60.3: Introduced.
153 :vartype: Numeric131 :vartype: Numeric
154 :scope: Global132 :scope: Global
155133
156.. variable:: innodb_s_lock_spin_rounds134.. variable:: innodb_s_lock_spin_rounds
157135
158 :version 5.5.8-20.0: Introduced.136 :version 5.6.11-60.3: Introduced.
159 :vartype: Numeric137 :vartype: Numeric
160 :scope: Global138 :scope: Global
161139
162.. variable:: innodb_s_lock_spin_waits140.. variable:: innodb_s_lock_spin_waits
163141
164 :version 5.5.8-20.0: Introduced.142 :version 5.6.11-60.3: Introduced.
165 :vartype: Numeric143 :vartype: Numeric
166 :scope: Global144 :scope: Global
167145
168.. variable:: innodb_x_lock_os_waits146.. variable:: innodb_x_lock_os_waits
169147
170 :version 5.5.8-20.0: Introduced.148 :version 5.6.11-60.3: Introduced.
171 :vartype: Numeric149 :vartype: Numeric
172 :scope: Global150 :scope: Global
173151
174.. variable:: innodb_x_lock_spin_rounds152.. variable:: innodb_x_lock_spin_rounds
175153
176 :version 5.5.8-20.0: Introduced.154 :version 5.6.11-60.3: Introduced.
177 :vartype: Numeric155 :vartype: Numeric
178 :scope: Global156 :scope: Global
179157
180.. variable:: innodb_x_lock_spin_waits158.. variable:: innodb_x_lock_spin_waits
181159
182 :version 5.5.8-20.0: Introduced.160 :version 5.6.11-60.3: Introduced.
183 :vartype: Numeric161 :vartype: Numeric
184 :scope: Global162 :scope: Global
185163
186INSERT BUFFER AND ADAPTIVE HASH INDEX164INSERT BUFFER AND ADAPTIVE HASH INDEX
187-------------------------------------165-------------------------------------
188166
189The 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: ::167The following variables contain information in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the output from ``SHOW ENGINE INNODB STATUS``. An example of that output is: ::
190168
191 -------------------------------------169 -------------------------------------
192 INSERT BUFFER AND ADAPTIVE HASH INDEX170 INSERT BUFFER AND ADAPTIVE HASH INDEX
193 -------------------------------------171 -------------------------------------
194 Ibuf: size 1, free list len 6089, seg size 6091,172 Ibuf: size 1, free list len 6089, seg size 6091,
195 44497 inserts, 44497 merged recs, 8734 merges173 44497 inserts, 44497 merged recs, 8734 merges
196 Hash table size 276707, node heap has 1 buffer(s)
197 0.00 hash searches/s, 0.00 non-hash searches/s174 0.00 hash searches/s, 0.00 non-hash searches/s
198175
199.. variable:: innodb_ibuf_discarded_delete_marks176.. variable:: innodb_ibuf_discarded_delete_marks
200177
201 :version 5.5.8-20.0: Introduced.178 :version 5.6.11-60.3: Introduced.
202 :vartype: Numeric179 :vartype: Numeric
203 :scope: Global180 :scope: Global
204181
205.. variable:: innodb_ibuf_discarded_deletes182.. variable:: innodb_ibuf_discarded_deletes
206183
207 :version 5.5.8-20.0: Introduced.184 :version 5.6.11-60.3: Introduced.
208 :vartype: Numeric185 :vartype: Numeric
209 :scope: Global186 :scope: Global
210187
211.. variable:: innodb_ibuf_discarded_inserts188.. variable:: innodb_ibuf_discarded_inserts
212189
213 :version 5.5.8-20.0: Introduced.190 :version 5.6.11-60.3: Introduced.
214 :vartype: Numeric191 :vartype: Numeric
215 :scope: Global192 :scope: Global
216193
217.. variable:: innodb_ibuf_free_list194.. variable:: innodb_ibuf_free_list
218195
219 :version 5.5.8-20.0: Introduced.196 :version 5.6.11-60.3: Introduced.
220 :vartype: Numeric197 :vartype: Numeric
221 :scope: Global198 :scope: Global
222199
223.. variable:: innodb_ibuf_merged_delete_marks200.. variable:: innodb_ibuf_merged_delete_marks
224201
225 :version 5.5.8-20.0: Introduced.202 :version 5.6.11-60.3: Introduced.
226 :vartype: Numeric203 :vartype: Numeric
227 :scope: Global204 :scope: Global
228205
229.. variable:: innodb_ibuf_merged_deletes206.. variable:: innodb_ibuf_merged_deletes
230207
231 :version 5.5.8-20.0: Introduced.208 :version 5.6.11-60.3: Introduced.
232 :vartype: Numeric209 :vartype: Numeric
233 :scope: Global210 :scope: Global
234211
235.. variable:: innodb_ibuf_merged_inserts212.. variable:: innodb_ibuf_merged_inserts
236213
237 :version 5.5.8-20.0: Introduced.214 :version 5.6.11-60.3: Introduced.
238 :vartype: Numeric215 :vartype: Numeric
239 :scope: Global216 :scope: Global
240217
241.. variable:: innodb_ibuf_merges218.. variable:: innodb_ibuf_merges
242219
243 :version 5.5.8-20.0: Introduced.220 :version 5.6.11-60.3: Introduced.
244 :vartype: Numeric221 :vartype: Numeric
245 :scope: Global222 :scope: Global
246223
247.. variable:: innodb_ibuf_segment_size224.. variable:: innodb_ibuf_segment_size
248225
249 :version 5.5.8-20.0: Introduced.226 :version 5.6.11-60.3: Introduced.
250 :vartype: Numeric227 :vartype: Numeric
251 :scope: Global228 :scope: Global
252229
253.. variable:: innodb_ibuf_size230.. variable:: innodb_ibuf_size
254231
255 :version 5.5.8-20.0: Introduced.232 :version 5.6.11-60.3: Introduced.
256 :vartype: Numeric
257 :scope: Global
258
259.. variable:: innodb_adaptive_hash_cells
260
261 :version 5.5.8-20.0: Introduced.
262 :vartype: Numeric
263 :scope: Global
264
265.. variable:: innodb_adaptive_hash_heap_buffers
266
267 :version 5.5.8-20.0: Introduced.
268 :vartype: Numeric
269 :scope: Global
270
271.. variable:: innodb_adaptive_hash_hash_searches
272
273 :version 5.5.8-20.0: Introduced.
274 :vartype: Numeric
275 :scope: Global
276
277.. variable:: innodb_adaptive_hash_non_hash_searches
278
279 :version 5.5.8-20.0: Introduced.
280 :vartype: Numeric233 :vartype: Numeric
281 :scope: Global234 :scope: Global
282235
283LOG236LOG
284---237---
285238
286The following variables contain information in the LOG section of the output from ``SHOW INNODB STATUS``. An example of that output is: ::239The following variables contain information in the LOG section of the output from ``SHOW ENGINE INNODB STATUS``. An example of that output is: ::
287240
288 ---
289 LOG241 LOG
290 ---242 ---
291 Log sequence number 28219393219243 Log sequence number 10145937666
292 Log flushed up to 28219393219244 Log flushed up to 10145937666
293 Last checkpoint at 28212583337245 Pages flushed up to 10145937666
294 Max checkpoint age 7782360246 Last checkpoint at 10145937666
295 Checkpoint age target 7539162247 Max checkpoint age 80826164
296 Modified age 6809882248 Checkpoint age target 78300347
297 Checkpoint age 6809882249 Modified age 0
250 Checkpoint age 0
298 0 pending log writes, 0 pending chkp writes251 0 pending log writes, 0 pending chkp writes
299 8570 log i/o's done, 2000.00 log i/o's/second252 9 log i/o's done, 0.00 log i/o's/second
253 Log tracking enabled
254 Log tracked up to 10145937666
255 Max tracked LSN age 80826164
300256
301.. variable:: innodb_lsn_current257.. variable:: innodb_lsn_current
302258
303 :version 5.5.8-20.0: Introduced.259 :version 5.6.11-60.3: Introduced.
304 :vartype: Numeric260 :vartype: Numeric
305 :scope: Global261 :scope: Global
306262
307.. variable:: innodb_lsn_flushed263.. variable:: innodb_lsn_flushed
308264
309 :version 5.5.8-20.0: Introduced.265 :version 5.6.11-60.3: Introduced.
310 :vartype: Numeric266 :vartype: Numeric
311 :scope: Global267 :scope: Global
312268
313.. variable:: innodb_lsn_last_checkpoint269.. variable:: innodb_lsn_last_checkpoint
314270
315 :version 5.5.8-20.0: Introduced.271 :version 5.6.11-60.3: Introduced.
316 :vartype: Numeric272 :vartype: Numeric
317 :scope: Global273 :scope: Global
318274
319.. variable:: innodb_checkpoint_age275.. variable:: innodb_checkpoint_age
320276
321 :version 5.5.8-20.0: Introduced.277 :version 5.6.11-60.3: Introduced.
322 :vartype: Numeric278 :vartype: Numeric
323 :scope: Global279 :scope: Global
324280
325.. variable:: innodb_checkpoint_max_age281.. variable:: innodb_checkpoint_max_age
326282
327 :version 5.5.8-20.0: Introduced.283 :version 5.6.11-60.3: Introduced.
328 :vartype: Numeric
329 :scope: Global
330
331.. variable:: innodb_checkpoint_target_age
332
333 :version 5.5.8-20.0: Introduced.
334 :vartype: Numeric284 :vartype: Numeric
335 :scope: Global285 :scope: Global
336286
337BUFFER POOL AND MEMORY287BUFFER POOL AND MEMORY
338----------------------288----------------------
339289
340The following variables contain information in the BUFFER POOL AND MEMORY section of the output from ``SHOW INNODB STATUS``. An example of that output is: ::290The following variables contain information in the BUFFER POOL AND MEMORY section of the output from ``SHOW ENGINE INNODB STATUS``. An example of that output is: ::
341291
342 ----------------------292 ----------------------
343 BUFFER POOL AND MEMORY293 BUFFER POOL AND MEMORY
344 ----------------------294 ----------------------
345 Total memory allocated 137625600; in additional pool allocated 0295 Total memory allocated 137363456; in additional pool allocated 0
296 Total memory allocated by read views 88
346 Internal hash tables (constant factor + variable factor)297 Internal hash tables (constant factor + variable factor)
347 Adaptive hash index 3774352 (2213656 + 1560696)298 Adaptive hash index 2266736 (2213368 + 53368)
348 Page hash 139144299 Page hash 139112 (buffer pool 0 only)
349 Dictionary cache 629811 (554864 + 74947)300 Dictionary cache 729463 (554768 + 174695)
350 File system 83536 (82672 + 864)301 File system 824800 (812272 + 12528)
351 Lock system 380792 (332872 + 47920)302 Lock system 333248 (332872 + 376)
352 Recovery system 0 (0 + 0)303 Recovery system 0 (0 + 0)
353 Threads 84040 (82696 + 1344)304 Dictionary memory allocated 174695
354 Dictionary memory allocated 74947305 Buffer pool size 8191
355 Buffer pool size 8192306 Buffer pool size, bytes 134201344
356 Buffer pool size, bytes 134217728307 Free buffers 7481
357 Free buffers 0308 Database pages 707
358 Database pages 8095309 Old database pages 280
359 Old database pages 2968310 Modified db pages 0
360 Modified db pages 5914
361 Pending reads 0311 Pending reads 0
362 Pending writes: LRU 0, flush list 129, single page 0312 Pending writes: LRU 0, flush list 0 single page 0
363 Pages made young 372084, not young 0313 Pages made young 0, not young 0
364 2546000.00 youngs/s, 0.00 non-youngs/s314 0.00 youngs/s, 0.00 non-youngs/s
365 Pages read 103356, created 154787, written 979572315 Pages read 707, created 0, written 1
366 469000.00 reads/s, 78000.00 creates/s, 138000.00 writes/s316 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
367 Buffer pool hit rate 994 / 1000, young-making rate 34 / 1000 not 0 / 1000317 No buffer pool page gets since the last printout
368 Pages read ahead 0.00/s, evicted without access 15000.00/s318 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
319 LRU len: 707, unzip_LRU len: 0
320
369321
370.. variable:: innodb_mem_adaptive_hash322.. variable:: innodb_mem_adaptive_hash
371323
372 :version 5.5.8-20.0: Introduced.324 :version 5.6.11-60.3: Introduced.
373 :vartype: Numeric325 :vartype: Numeric
374 :scope: Global326 :scope: Global
375327
376.. variable:: innodb_mem_dictionary328.. variable:: innodb_mem_dictionary
377329
378 :version 5.5.8-20.0: Introduced.330 :version 5.6.11-60.3: Introduced.
379 :vartype: Numeric331 :vartype: Numeric
380 :scope: Global332 :scope: Global
381333
382.. variable:: innodb_mem_total334.. variable:: innodb_mem_total
383335
384 :version 5.5.8-20.0: Introduced.336 :version 5.6.11-60.3: Introduced.
385 :vartype: Numeric337 :vartype: Numeric
386 :scope: Global338 :scope: Global
387339
388.. variable:: innodb_buffer_pool_pages_LRU_flushed340.. variable:: innodb_buffer_pool_pages_LRU_flushed
389341
390 :version 5.5.8-20.0: Introduced.342 :version 5.6.11-60.3: Introduced.
391 :vartype: Numeric343 :vartype: Numeric
392 :scope: Global344 :scope: Global
393345
394.. variable:: innodb_buffer_pool_pages_made_not_young346.. variable:: innodb_buffer_pool_pages_made_not_young
395347
396 :version 5.5.8-20.0: Introduced.348 :version 5.6.11-60.3: Introduced.
397 :vartype: Numeric349 :vartype: Numeric
398 :scope: Global350 :scope: Global
399351
400.. variable:: innodb_buffer_pool_pages_made_young352.. variable:: innodb_buffer_pool_pages_made_young
401353
402 :version 5.5.8-20.0: Introduced.354 :version 5.6.11-60.3: Introduced.
403 :vartype: Numeric355 :vartype: Numeric
404 :scope: Global356 :scope: Global
405357
406.. variable:: innodb_buffer_pool_pages_old358.. variable:: innodb_buffer_pool_pages_old
407359
408 :version 5.5.8-20.0: Introduced.360 :version 5.6.11-60.3: Introduced.
409 :vartype: Numeric361 :vartype: Numeric
410 :scope: Global362 :scope: Global
411363
@@ -427,44 +379,63 @@
427 ---TRANSACTION F561FC, ACTIVE 29 sec, process no 993, OS thread id 140213152769808 updating or deleting379 ---TRANSACTION F561FC, ACTIVE 29 sec, process no 993, OS thread id 140213152769808 updating or deleting
428 mysql tables in use 1, locked 1380 mysql tables in use 1, locked 1
429381
382
430.. variable:: innodb_history_list_length383.. variable:: innodb_history_list_length
431384
432 :version 5.5.8-20.0: Introduced.385 :version 5.6.11-60.3: Introduced.
433 :vartype: Numeric386 :vartype: Numeric
434 :scope: Global387 :scope: Global
435388
436.. variable:: innodb_max_trx_id389.. variable:: innodb_max_trx_id
437390
438 :version 5.5.8-20.0: Introduced.391 :version 5.6.11-60.3: Introduced.
439 :vartype: Numeric392 :vartype: Numeric
440 :scope: Global393 :scope: Global
441394
442.. variable:: innodb_oldest_view_low_limit_trx_id395.. variable:: innodb_oldest_view_low_limit_trx_id
443396
444 :version 5.5.8-20.0: Introduced.397 :version 5.6.11-60.3: Introduced.
445 :vartype: Numeric398 :vartype: Numeric
446 :scope: Global399 :scope: Global
447400
448.. variable:: innodb_purge_trx_id401.. variable:: innodb_purge_trx_id
449402
450 :version 5.5.8-20.0: Introduced.403 :version 5.6.11-60.3: Introduced.
451 :vartype: Numeric404 :vartype: Numeric
452 :scope: Global405 :scope: Global
453406
454.. variable:: innodb_purge_undo_no407.. variable:: innodb_purge_undo_no
455408
456 :version 5.5.8-20.0: Introduced.409 :version 5.6.11-60.3: Introduced.
457 :vartype: Numeric410 :vartype: Numeric
458 :scope: Global411 :scope: Global
459412
460.. variable:: innodb_current_row_locks413.. variable:: innodb_current_row_locks
461414
462 :version 5.5.8-20.0: Introduced.415 :version 5.6.11-60.3: Introduced.
463 :version 5.5.10-20.1: Renamed.
464 :vartype: Numeric416 :vartype: Numeric
465 :scope: Global417 :scope: Global
466418
467 This variable was named :variable:`innodb_row_lock_numbers` in release 5.5.8-20.0.419INFORMATION_SCHEMA Tables
420=========================
421
422The following table contains information about the oldest active transaction in the system.
423
424.. table:: INFORMATION_SCHEMA.XTRADB_READ_VIEW
425
426 :column READ_VIEW_UNDO_NUMBER:
427 :column READ_VIEW_LOW_LIMIT_TRX_NUMBER: This is the highest transactions number at the time the view was created.
428 :column READ_VIEW_UPPER_LIMIT_TRX_ID: This is the highest transactions ID at the time the view was created. This means that it should not see newer transactions with IDs bigger than or equal to that value.
429 :column READ_VIEW_LOW_LIMIT_TRX_ID: This is the latest committed transaction ID at the time the oldest view was created. This means that it should see all transactions with IDs smaller than or equal to that value.
430
431The following table contains information about the memory usage for InnoDB/XtraDB hash tables.
432
433.. table:: INFORMATION_SCHEMA.XTRADB_INTERNAL_HASH_TABLES
434
435 :column INTERNAL_HASH_TABLE_NAME: Hash table name
436 :column TOTAL_MEMORY: Total amount of memory
437 :column CONSTANT_MEMORY: Constant memory
438 :column VARIABLE_MEMORY: Variable memory
468439
469440
470Other reading441Other reading
471442
=== modified file 'doc/source/diagnostics/misc_info_schema_tables.rst'
--- doc/source/diagnostics/misc_info_schema_tables.rst 2013-05-27 13:59:13 +0000
+++ doc/source/diagnostics/misc_info_schema_tables.rst 2013-06-03 10:03:31 +0000
@@ -46,3 +46,43 @@
46 :column UPDATE_TIME: Date and time of the latest update of the temporary table46 :column UPDATE_TIME: Date and time of the latest update of the temporary table
4747
48This table holds information on the temporary tables existing for the running connection.48This table holds information on the temporary tables existing for the running connection.
49
50Multiple Rollback Segments
51==========================
52
53|Percona Server|, in addition to the upstream multiple rollback segment implementation, provides the additional Information Schema table: INFORMATION_SCHEMA.XTRADB_RSEG.
54
55``INFORMATION_SCHEMA`` Tables
56=============================
57
58This feature provides the following table:
59
60.. table:: INFORMATION_SCHEMA.XTRADB_RSEG
61
62 :column rseg_id: rollback segment id
63 :column space_id: space where the segment placed
64 :column zip_size: compressed page size in bytes if compressed otherwise 0
65 :column page_no: page number of the segment header
66 :column max_size: max size in pages
67 :column curr_size: current size in pages
68
69This table shows information about all the rollback segments (the default segment and the extra segments).
70
71Here is an example of output with ``innodb_extra_rsegments = 8`` ::
72
73 mysql> select * from information_schema.innodb_rseg;
74 +---------+----------+----------+---------+------------+-----------+
75 | rseg_id | space_id | zip_size | page_no | max_size | curr_size |
76 +---------+----------+----------+---------+------------+-----------+
77 | 0 | 0 | 0 | 6 | 4294967294 | 1 |
78 | 1 | 0 | 0 | 13 | 4294967294 | 2 |
79 | 2 | 0 | 0 | 14 | 4294967294 | 1 |
80 | 3 | 0 | 0 | 15 | 4294967294 | 1 |
81 | 4 | 0 | 0 | 16 | 4294967294 | 1 |
82 | 5 | 0 | 0 | 17 | 4294967294 | 1 |
83 | 6 | 0 | 0 | 18 | 4294967294 | 1 |
84 | 7 | 0 | 0 | 19 | 4294967294 | 1 |
85 | 8 | 0 | 0 | 20 | 4294967294 | 1 |
86 +---------+----------+----------+---------+------------+-----------+
87 9 rows in set (0.00 sec)
88
4989
=== modified file 'doc/source/diagnostics/mysql_syslog.rst'
--- doc/source/diagnostics/mysql_syslog.rst 2013-05-27 13:46:13 +0000
+++ doc/source/diagnostics/mysql_syslog.rst 2013-06-03 10:03:31 +0000
@@ -9,8 +9,8 @@
9Version Specific Information9Version Specific Information
10============================10============================
1111
12 * :rn:`5.5.8-20.0`:12 * :rn:`5.6.11-60.3`:
13 Full functionality available.13 Feature ported from |Percona Server| 5.5.
1414
15Other Information15Other Information
16=================16=================
1717
=== modified file 'doc/source/diagnostics/show_engines.rst'
--- doc/source/diagnostics/show_engines.rst 2013-05-27 13:59:13 +0000
+++ doc/source/diagnostics/show_engines.rst 2013-06-03 10:03:31 +0000
@@ -29,12 +29,6 @@
29Version-Specific Information29Version-Specific Information
30============================30============================
3131
32 * 5.5.8-20.0:32 * :rn:`5.6.11-60.3`:
33 Full functionality available.33 Full functionality available.
3434
35Other Information
36=================
37
38 * Author / Origin:
39 Percona
40
4135
=== added file 'doc/source/diagnostics/slow_extended.rst'
--- doc/source/diagnostics/slow_extended.rst 1970-01-01 00:00:00 +0000
+++ doc/source/diagnostics/slow_extended.rst 2013-06-03 10:03:31 +0000
@@ -0,0 +1,355 @@
1.. _slow_extended:
2
3================
4 Slow Query Log
5================
6
7This 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.
8
9You can use *Percona-Toolkit*'s `pt-query-digest <http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html>`_ tool to aggregate similar queries together and report on those that consume the most execution time.
10
11
12Version Specific Information
13============================
14
15 * :rn:`5.6.11-60.3`:
16
17 * Feature ported from |Percona Server| 5.5.
18
19Other Information
20=================
21
22 * Author / Origin:
23 Maciej Dobrzanski
24
25System Variables
26================
27
28.. variable:: log_slow_filter
29
30 :cli: Yes
31 :conf: Yes
32 :scope: Global, Session
33 :dyn: Yes
34
35Filters 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:
36
37 * ``qc_miss``:
38 The query was not found in the query cache.
39
40 * ``full_scan``:
41 The query performed a full table scan.
42
43 * ``full_join``:
44 The query performed a full join (a join without indexes).
45
46 * ``tmp_table``:
47 The query created an implicit internal temporary table.
48
49 * ``tmp_table_on_disk``:
50 The query's temporary table was stored on disk.
51
52 * ``filesort``:
53 The query used a filesort.
54
55 * ``filesort_on_disk``:
56 The filesort was performed on disk.
57
58Values 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.
59
60For 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``.
61
62.. variable:: log_slow_rate_type
63
64 :cli: Yes
65 :conf: Yes
66 :scope: Global
67 :dyn: Yes
68 :vartype: Enumerated
69 :default: ``session``
70 :range: ``session``, ``query``
71
72Specifies semantic of :variable:`log_slow_rate_limit` - ``session`` or ``query``.
73
74.. variable:: log_slow_rate_limit
75
76 :cli: Yes
77 :conf: Yes
78 :scope: Global, session
79 :dyn: Yes
80
81Behavior of this variable depends from :variable:`log_slow_rate_type`.
82
83Specifies 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.
84
85Logging all queries might consume I/O bandwidth and cause the log file to grow large.
86 * 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.
87
88 * 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 percent of queries will be logged.
89
90Note that every query has global unique ``query_id`` and every connection can has it own (session) :variable:`log_slow_rate_limit`.
91Decision "log or no" calculated in following manner:
92
93 * if ``log_slow_rate_limit`` is 0 - log every query
94
95 * If ``log_slow_rate_limit`` > 0 - log query when (``query_id`` % ``log_slow_rate_limit``) is zero.
96
97This allows flexible setup logging behavior.
98
99For example, if you set the value to 100, then one percent of ``sessions/queries`` will be logged.
100
101.. variable:: log_slow_sp_statements
102
103 :cli: Yes
104 :conf: Yes
105 :scope: Global
106 :dyn: Yes
107 :vartype: Boolean
108 :default: TRUE
109 :range: TRUE/FALSE
110
111If ``TRUE``, statements executed by stored procedures are logged to the slow if it is open.
112
113.. note::
114
115 Support for logging stored procedures doesn't involve triggers, so they won't be logged even if this feature is enabled.
116
117.. variable:: log_slow_verbosity
118
119 :cli: Yes
120 :conf: Yes
121 :scope: Global, session
122 :dyn: Yes
123
124Specifies 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:
125
126 * ``microtime``:
127 Log queries with microsecond precision.
128
129 * ``query_plan``:
130 Log information about the query's execution plan.
131
132 * ``innodb``:
133 Log |InnoDB| statistics.
134
135 * ``minimal``:
136 Equivalent to enabling just ``microtime``.
137
138 * ``standard``:
139 Equivalent to enabling ``microtime,innodb``.
140
141 * ``full``:
142 Equivalent to all other values OR'ed together.
143
144 * ``profiling``:
145 Enables profiling of all queries in all connections.
146
147 * ``profiling_use_getrusage``:
148 Enables usage of the getrusage function.
149
150Values are OR'ed together.
151
152For example, to enable microsecond query timing and |InnoDB| statistics, set this option to ``microtime,innodb`` or ``standard``. To turn all options on, set the option to ``full``.
153
154.. variable:: slow_query_log_timestamp_always
155
156 :cli: Yes
157 :conf: Yes
158 :scope: Global
159 :dyn: Yes
160 :vartype: Boolean
161 :default: FALSE
162 :range: TRUE/FALSE
163
164If ``TRUE``, a timestamp is printed on every slow log record. Multiple records may have the same time.
165
166.. variable:: slow_query_log_timestamp_precision
167
168 :cli: Yes
169 :conf: Yes
170 :scope: Global
171 :dyn: Yes
172 :vartype: Enumerated
173 :default: ``second``
174 :range: ``second``, ``microsecond``
175
176Normally, entries to the slow query log are in seconds precision, in this format: ::
177
178 # Time: 090402 9:23:36 # User@Host: XXX @ XXX [10.X.X.X]
179
180If :variable:`slow_query_log_timestamp_precision` ``=microsecond``, entries to the slow query log are in microsecond precision, in this format: ::
181
182 # Time: 090402 9:23:36.123456 # User@Host: XXX @ XXX [10.X.X.X]
183
184.. variable:: slow_query_log_use_global_control
185
186 :cli: Yes
187 :conf: Yes
188 :scope: Global
189 :dyn: Yes
190 :default: None
191
192Specifies which variables have global scope instead of local. Value is a "flag" variable - you can specify multiple values separated by commas
193
194 * ``none``:
195 All variables use local scope
196
197 * ``log_slow_filter``:
198 Global variable :variable:`log_slow_filter` has effect (instead of local)
199
200 * ``log_slow_rate_limit``:
201 Global variable :variable:`log_slow_rate_limit` has effect (instead of local)
202
203 * ``log_slow_verbosity``:
204 Global variable :variable:`log_slow_verbosity` has effect (instead of local)
205
206 * ``long_query_time``:
207 Global variable :variable:`long_query_time` has effect (instead of local)
208
209 * ``min_examined_row_limit``:
210 Global variable ``min_examined_row_limit`` has effect (instead of local)
211
212 * ``all``
213 Global variables has effect (instead of local)
214
215
216Other Information
217=================
218
219Changes to the Log Format
220-------------------------
221
222The feature adds more information to the slow log output. Here is a sample log entry: ::
223
224 # Time: 130601 8:01:06.058915
225 # User@Host: root[root] @ localhost [] Id: 42
226 # Schema: imdb Last_errno: 0 Killed: 0
227 # Query_time: 7.725616 Lock_time: 0.000328 Rows_sent: 4 Rows_examined: 1543720 Rows_affected: 0
228 # Bytes_sent: 272 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
229 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
230 # Filesort: No Filesort_on_disk: No Merge_passes: 0
231 SET timestamp=1370073666;
232 SELECT id,title,production_year FROM title WHERE title = 'Bambi';
233
234
235Another example (:variable:`log_slow_verbosity` ``=profiling``): ::
236
237 # Time: 130601 8:03:20.700441
238 # User@Host: root[root] @ localhost [] Id: 43
239 # Schema: imdb Last_errno: 0 Killed: 0
240 # Query_time: 7.815071 Lock_time: 0.000261 Rows_sent: 4 Rows_examined: 1543720 Rows_affected: 0
241 # Bytes_sent: 272
242 # Profile_starting: 0.000125 Profile_starting_cpu: 0.000120
243 Profile_checking_permissions: 0.000021 Profile_checking_permissions_cpu: 0.000021
244 Profile_Opening_tables: 0.000049 Profile_Opening_tables_cpu: 0.000048 Profile_init: 0.000048
245 Profile_init_cpu: 0.000049 Profile_System_lock: 0.000049 Profile_System_lock_cpu: 0.000048
246 Profile_optimizing: 0.000024 Profile_optimizing_cpu: 0.000024 Profile_statistics: 0.000036
247 Profile_statistics_cpu: 0.000037 Profile_preparing: 0.000029 Profile_preparing_cpu: 0.000029
248 Profile_executing: 0.000012 Profile_executing_cpu: 0.000012 Profile_Sending_data: 7.814583
249 Profile_Sending_data_cpu: 7.811634 Profile_end: 0.000013 Profile_end_cpu: 0.000012
250 Profile_query_end: 0.000014 Profile_query_end_cpu: 0.000014 Profile_closing_tables: 0.000023
251 Profile_closing_tables_cpu: 0.000023 Profile_freeing_items: 0.000051
252 Profile_freeing_items_cpu: 0.000050 Profile_logging_slow_query: 0.000006
253 Profile_logging_slow_query_cpu: 0.000006
254 # Profile_total: 7.815085 Profile_total_cpu: 7.812127
255 SET timestamp=1370073800;
256 SELECT id,title,production_year FROM title WHERE title = 'Bambi';
257
258
259Connection and Schema Identifier
260--------------------------------
261
262Each 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.
263
264Each entry also contains a schema name, so you can trace all the queries whose default database was set to a particular schema. ::
265
266 # Id: 43 Schema: imdb
267
268Microsecond Time Resolution and Extra Row Information
269-----------------------------------------------------
270
271This is the original functionality offered by the ``microslow`` feature. ``Query_time`` and ``Lock_time`` are logged with microsecond resolution.
272
273The 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, ::
274
275 # Query_time: 0.962742 Lock_time: 0.000202 Rows_sent: 4 Rows_examined: 1543719 Rows_affected: 0
276
277Values and context:
278
279 * ``Rows_examined``:
280 Number of rows scanned - ``SELECT``
281
282 * ``Rows_affected``:
283 Number of rows changed - ``UPDATE``, ``DELETE``, ``INSERT``
284
285Memory Footprint
286----------------
287
288The 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. ::
289
290 # Bytes_sent: 8053 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 950528
291
292Values and context:
293
294 * ``Bytes_sent``:
295 The amount of bytes sent for the result of the query
296
297 * ``Tmp_tables``:
298 Number of temporary tables created on memory for the query
299
300 * ``Tmp_disk_tables``:
301 Number of temporary tables created on disk for the query
302
303 * ``Tmp_table_sizes``:
304 Total Size in bytes for all temporary tables used in the query
305
306
307Query Plan Information
308----------------------
309
310Each 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. ::
311
312 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
313 # Filesort: No Filesort_on_disk: No Merge_passes: 0
314
315The values and their meanings are documented with the :variable:`log_slow_filter` option.
316
317|InnoDB| Usage Information
318--------------------------
319
320The 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. ::
321
322 # InnoDB_IO_r_ops: 6415 InnoDB_IO_r_bytes: 105103360 InnoDB_IO_r_wait: 0.001279
323 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
324 # InnoDB_pages_distinct: 6430
325
326Values:
327
328 * ``innodb_IO_r_ops``:
329 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.
330
331 * ``innodb_IO_r_bytes``:
332 Similar to innodb_IO_r_ops, but the unit is bytes.
333
334 * ``innodb_IO_r_wait``:
335 Shows how long (in seconds) it took |InnoDB| to actually read the data from storage.
336
337 * ``innodb_rec_lock_wait``:
338 Shows how long (in seconds) the query waited for row locks.
339
340 * ``innodb_queue_wait``:
341 Shows how long (in seconds) the query spent either waiting to enter the |InnoDB| queue or inside that queue waiting for execution.
342
343 * ``innodb_pages_distinct``:
344 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.
345
346If the query did not use |InnoDB| tables, that information is written into the log instead of the above statistics.
347
348Related Reading
349===============
350
351 * `Impact of logging on MySQL's performance <http://www.mysqlperformanceblog.com/2009/02/10/impact-of-logging-on-mysql%E2%80%99s-performance/>`_
352
353 * `log_slow_filter Usage <http://www.mysqlperformanceblog.com/2008/09/22/finding-what-created_tmp_disk_tables-with-log_slow_filter/>`_
354
355 * `Blueprint in Launchpad <https://blueprints.launchpad.net/percona-server/+spec/microseconds-in-query-log>`_
0356
=== removed file 'doc/source/diagnostics/slow_extended_55.rst'
--- doc/source/diagnostics/slow_extended_55.rst 2013-05-10 09:39:17 +0000
+++ doc/source/diagnostics/slow_extended_55.rst 1970-01-01 00:00:00 +0000
@@ -1,377 +0,0 @@
1.. _slow_extended_55:
2
3================
4 Slow Query Log
5================
6
7This 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.
8
9The 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|.
10
11You can use *Percona-Toolkit*'s `pt-query-digest <http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html>`_ tool to aggregate similar queries together and report on those that consume the most execution time.
12
13
14Version Specific Information
15============================
16
17 * 5.5.8-20.0:
18 Added values ``profiling`` and ``profiling_use_getrusage`` to variable log_slow_verbosity.
19
20 * 5.5.10-20.1:
21 * Renamed variable :variable:`slow_query_log_timestamp_always` to :variable:`slow_query_log_timestamp_always`.
22
23 * Renamed variable :variable:`slow_query_log_microseconds_timestamp` to :variable:`slow_query_log_timestamp_precision`.
24
25 * Renamed variable :variable:`use_global_log_slow_control` to :variable:`slow_query_log_use_global_control`.
26
27Other Information
28=================
29
30 * Author / Origin:
31 Maciej Dobrzanski, Percona
32
33System Variables
34================
35
36.. variable:: log_slow_admin_statements
37
38 :cli: Yes
39 :conf: Yes
40 :scope: Global
41 :dyn: yes
42
43When this variable is enabled, administrative statements will be logged to the slow query log. Upstream version of the |MySQL| server has implemented command line option with same name. Significant difference is that this feature is implemented as variable in |Percona Server|, that means it can be enabled/disabled dynamically without restarting the server.
44
45.. variable:: log_slow_filter
46
47 :cli: Yes
48 :conf: Yes
49 :scope: Global, Session
50 :dyn: Yes
51
52Filters 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:
53
54 * ``qc_miss``:
55 The query was not found in the query cache.
56
57 * ``full_scan``:
58 The query performed a full table scan.
59
60 * ``full_join``:
61 The query performed a full join (a join without indexes).
62
63 * ``tmp_table``:
64 The query created an implicit internal temporary table.
65
66 * ``tmp_table_on_disk``:
67 The query``s temporary table was stored on disk.
68
69 * ``filesort``:
70 The query used a filesort.
71
72 * ``filesort_on_disk``:
73 The filesort was performed on disk.
74
75Values 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.
76
77For 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``.
78
79.. variable:: log_slow_rate_type
80
81 :cli: Yes
82 :conf: Yes
83 :scope: Global
84 :dyn: Yes
85 :vartype: Enumerated
86 :default: ``session``
87 :range: ``session``, ``query``
88
89Specifies semantic of :variable:`log_slow_rate_limit` - ``session`` or ``query``.
90
91.. variable:: log_slow_rate_limit
92
93 :cli: Yes
94 :conf: Yes
95 :scope: Global, session
96 :dyn: Yes
97
98Behavior of this variable depends from :variable:`log_slow_rate_type`.
99
100Specifies 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.
101
102Logging all queries might consume I/O bandwidth and cause the log file to grow large.
103 * 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.
104
105 * 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.
106
107Note that every query has global unique ``query_id`` and every connection can has it own (session) :variable:``log_slow_rate_limit``.
108Decision "log or no" calculated in following manner:
109
110 * if ``log_slow_rate_limit`` is 0 - log every query
111
112 * If ``log_slow_rate_limit`` > 0 - log query when (``query_id`` % ``log_slow_rate_limit``) is zero.
113
114This allows flexible setup logging behavior.
115
116For example, if you set the value to 100, then one percent of ``sessions/queries`` will be logged.
117
118.. variable:: log_slow_slave_statements
119
120 :cli: Yes
121 :conf: Yes
122 :scope: Global, session
123 :dyn: Yes (in 5.1 releases only)
124
125Specifies that slow queries replayed by the slave SQL thread on a |MySQL| slave will be logged. Upstream version of the |MySQL| server has implemented command line option with same name. Significant difference is that this feature is implemented as variable in |Percona Server|, that means it can be enabled/disabled dynamically without restarting the server.
126
127To 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.
128
129To 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.
130
131
132.. variable:: log_slow_sp_statements
133
134 :cli: Yes
135 :conf: Yes
136 :scope: Global
137 :dyn: Yes
138 :vartype: Boolean
139 :default: TRUE
140 :range: TRUE/FALSE
141
142If ``TRUE``, statements executed by stored procedures are logged to the slow if it is open.
143
144.. note::
145
146 Support for logging stored procedures doesn't involve triggers, so they won't be logged even if this feature is enabled.
147
148.. variable:: log_slow_verbosity
149
150 :version 5.5.8-20.0: Added ``profiling`` and ``profiling_use_getrusage``
151 :cli: Yes
152 :conf: Yes
153 :scope: Global, session
154 :dyn: Yes
155
156Specifies 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:
157
158 * ``microtime``:
159 Log queries with microsecond precision.
160
161 * ``query_plan``:
162 Log information about the query's execution plan.
163
164 * ``innodb``:
165 Log |InnoDB| statistics.
166
167 * ``minimal``:
168 Equivalent to enabling just ``microtime``.
169
170 * ``standard``:
171 Equivalent to enabling ``microtime,innodb``.
172
173 * ``full``:
174 Equivalent to all other values OR'ed together.
175
176 * ``profiling``:
177 Enables profiling of all queries in all connections.
178
179 * ``profiling_use_getrusage``:
180 Enables usage of the getrusage function.
181
182Values are OR'ed together.
183
184For example, to enable microsecond query timing and |InnoDB| statistics, set this option to ``microtime,innodb`` or ``standard``. To turn all options on, set the option to ``full``.
185
186.. variable:: slow_query_log_timestamp_always
187
188 :version 5.5.10-20.1: Introduced (renamed from :variable:`log_slow_timestamp_every`)
189 :cli: Yes
190 :conf: Yes
191 :scope: Global
192 :dyn: Yes
193 :vartype: Boolean
194 :default: FALSE
195 :range: TRUE/FALSE
196
197If ``TRUE``, a timestamp is printed on every slow log record. Multiple records may have the same time.
198
199.. variable:: slow_query_log_timestamp_precision
200
201 :version 5.5.10-20.1: Introduced (renamed from ``slow_query_log_microseconds_timestamp``)
202 :cli: Yes
203 :conf: Yes
204 :scope: Global
205 :dyn: Yes
206 :vartype: Enumerated
207 :default: ``second``
208 :range: ``second``, ``microsecond``
209
210Normally, entries to the slow query log are in seconds precision, in this format: ::
211
212 # Time: 090402 9:23:36 # User@Host: XXX @ XXX [10.X.X.X]
213
214If :variable:`slow_query_log_timestamp_precision` ``=microsecond``, entries to the slow query log are in microsecond precision, in this format: ::
215
216 # Time: 090402 9:23:36.123456 # User@Host: XXX @ XXX [10.X.X.X]
217
218.. variable:: slow_query_log_use_global_control
219
220 :cli: Yes
221 :conf: Yes
222 :scope: Global
223 :dyn: Yes
224 :default: None
225 :version 5.5.10-20.1: Introduced (renamed from :variable:`log_slow_timestamp_every`)
226
227Specifies which variables have global scope instead of local. Value is a “flag” variable - you can specify multiple values separated by commas
228
229 * ``none``:
230 All variables use local scope
231
232 * ``log_slow_filter``:
233 Global variable :variable:`log_slow_filter` has effect (instead of local)
234
235 * ``log_slow_rate_limit``:
236 Global variable :variable:`log_slow_rate_limit` has effect (instead of local)
237
238 * ``log_slow_verbosity``:
239 Global variable :variable:`log_slow_verbosity` has effect (instead of local)
240
241 * ``long_query_time``:
242 Global variable :variable:`long_query_time` has effect (instead of local)
243
244 * ``min_examined_row_limit``:
245 Global variable ``min_examined_row_limit`` has effect (instead of local)
246
247 * ``all``
248 Global variables has effect (instead of local)
249
250**NOTE:** This variable has been renamed from :variable:`log_slow_timestamp_every` since 5.5.10-20.1.
251
252
253Other Information
254=================
255
256Changes to the Log Format
257-------------------------
258
259The feature adds more information to the slow log output. Here is a sample log entry: ::
260
261 # User@Host: mailboxer[mailboxer] @ [192.168.10.165]
262 # Thread_id: 11167745 Schema: board
263 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No
264 # Filesort: Yes Disk_filesort: No Merge_passes: 0
265 # Query_time: 0.000659 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 30 Rows_affected: 0 Rows_read: 30
266 # innodb_IO_r_ops: 1 innodb_IO_r_bytes: 16384 innodb_IO_r_wait: 0.028487
267 # innodb_rec_lock_wait: 0.000000 innodb_queue_wait: 0.000000
268 # innodb_pages_distinct: 5
269 select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = ``710575``
270
271Another example (:variable:`log_slow_verbosity` ``=profiling``): ::
272
273 # Query_time: 4.555235 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1
274 # 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
275 # Profile_total: 4.555405 Profile_total_cpu: 0.000000
276 insert into teee4 select * from teee4 limit 10000000;
277
278Connection and Schema Identifier
279--------------------------------
280
281Each 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.
282
283Each entry also contains a schema name, so you can trace all the queries whose default database was set to a particular schema. ::
284
285 # Thread_id: 11167745 Schema: board
286
287Microsecond Time Resolution and Extra Row Information
288-----------------------------------------------------
289
290This is the original functionality offered by the ``microslow`` feature. ``Query_time`` and ``Lock_time`` are logged with microsecond resolution.
291
292The 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, ::
293
294 # Query_time: 0.000659 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 30 Rows_affected: 0 Rows_read: 30
295
296Values and context:
297
298 * ``Rows_examined``:
299 Number of rows scanned - ``SELECT``
300
301 * ``Rows_affected``:
302 Number of rows changed - ``UPDATE``, ``DELETE``, ``INSERT``
303
304 * ``Rows_read``:
305 Number of rows read - ``UPDATE``, ``DELETE``, ``INSERT``
306
307Memory Footprint
308----------------
309
310The 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. ::
311
312 # Bytes_sent: 8053 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 950528
313
314Values and context:
315
316 * ``Bytes_sent``:
317 The amount of bytes sent for the result of the query
318
319 * ``Tmp_tables``:
320 Number of temporary tables created on memory for the query
321
322 * ``Tmp_disk_tables``:
323 Number of temporary tables created on disk for the query
324
325 * ``Tmp_table_sizes``:
326 Total Size in bytes for all temporary tables used in the query
327
328
329Query Plan Information
330----------------------
331
332Each 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. ::
333
334 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No
335 # Filesort: Yes Disk_filesort: No Merge_passes: 0
336
337The values and their meanings are documented with the :variable:`log_slow_filter` option.
338
339|InnoDB| Usage Information
340--------------------------
341
342The 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. ::
343
344 # innodb_IO_r_ops: 1 innodb_IO_r_bytes: 16384 innodb_IO_r_wait: 0.028487
345 # innodb_rec_lock_wait: 0.000000 innodb_queue_wait: 0.000000
346 # innodb_pages_distinct: 5
347
348Values:
349
350 * ``innodb_IO_r_ops``:
351 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.
352
353 * ``innodb_IO_r_bytes``:
354 Similar to innodb_IO_r_ops, but the unit is bytes.
355
356 * ``innodb_IO_r_wait``:
357 Shows how long (in seconds) it took |InnoDB| to actually read the data from storage.
358
359 * ``innodb_rec_lock_wait``:
360 Shows how long (in seconds) the query waited for row locks.
361
362 * ``innodb_queue_wait``:
363 Shows how long (in seconds) the query spent either waiting to enter the |InnoDB| queue or inside that queue waiting for execution.
364
365 * ``innodb_pages_distinct``:
366 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.
367
368If the query did not use |InnoDB| tables, that information is written into the log instead of the above statistics.
369
370Related Reading
371===============
372
373 * http://www.mysqlperformanceblog.com/2009/02/10/impact-of-logging-on-mysql%E2%80%99s-performance/
374
375 * `log_slow_filter Usage <http://www.mysqlperformanceblog.com/2008/09/22/finding-what-created_tmp_disk_tables-with-log_slow_filter/>`_
376
377 * `Blueprint in Launchpad <https://blueprints.launchpad.net/percona-server/+spec/microseconds-in-query-log>`_
3780
=== modified file 'doc/source/diagnostics/thread_based_profiling.rst'
--- doc/source/diagnostics/thread_based_profiling.rst 2012-09-18 05:04:43 +0000
+++ doc/source/diagnostics/thread_based_profiling.rst 2013-06-03 10:03:31 +0000
@@ -13,5 +13,5 @@
13Version Specific Information13Version Specific Information
14============================14============================
1515
16 * :rn:`5.5.25a-27.1`:16 * :rn:`5.6.11-60.3`:
17 Thread based profiling introduced17 Thread based profiling ported from |Percona Server| 5.5.
1818
=== modified file 'doc/source/diagnostics/user_stats.rst'
--- doc/source/diagnostics/user_stats.rst 2013-05-27 13:59:13 +0000
+++ doc/source/diagnostics/user_stats.rst 2013-06-03 10:03:31 +0000
@@ -12,21 +12,21 @@
12Version Specific Information12Version Specific Information
13============================13============================
1414
15 * :rn:`5.5.10-20.1`:15 * :rn:`5.6.11-60.3`:
16 Renamed variable :variable:`userstat_running` to :variable:`userstat`.16 Feature ported from |Percona Server| 5.5.
1717
18Other Information18Other Information
19=================19=================
2020
21 * Author/Origin:21 * Author/Origin:
22 *Google*; *Percona* added the ``INFORMATION_SCHEMA`` tables and the :variable:`userstat_running` variable.22 *Google*; *Percona* added the ``INFORMATION_SCHEMA`` tables and the :variable:`userstat` variable.
2323
24System Variables24System Variables
25================25================
2626
27.. variable:: userstat_running27.. variable:: userstat
2828
29 :version 5.5.10-20.1: Renamed to :variable:`userstat`29 :version 5.6.11-60.3: Variable introduced
30 :cli: Yes30 :cli: Yes
31 :conf: Yes31 :conf: Yes
32 :scope: Global32 :scope: Global
3333
=== removed file 'doc/source/flexibility/buff_read_ahead_area.rst'
--- doc/source/flexibility/buff_read_ahead_area.rst 2013-05-27 13:46:13 +0000
+++ doc/source/flexibility/buff_read_ahead_area.rst 1970-01-01 00:00:00 +0000
@@ -1,36 +0,0 @@
1.. _buff_read_ahead_area:
2
3====================================
4 Fixed Size for the Read Ahead Area
5====================================
6
7|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.
8
9This 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.
10
11Please note that the minimum allowed value for the |InnoDB| buffer pool is de facto set to 32 MB.
12
13This change is a port of the feature from Facebook:
14
15 * http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3538
16
17
18Version Specific Information
19============================
20
21 * :rn:`5.5.8-20.0` :
22 Full functionality available.
23
24Other Information
25=================
26
27 * Author/Origin:
28 Facebook
29
30 * Bugs fixed:
31 :bug:`606811`
32
33Other Reading
34=============
35
36 * `BUF_READ_AHEAD_AREA Bottleneck <http://www.facebook.com/notes/mysqlfacebook/using-pmp-to-double-mysql-throughput-part-2/405092575932>`_
370
=== removed file 'doc/source/flexibility/innodb_files_extend.rst'
--- doc/source/flexibility/innodb_files_extend.rst 2013-05-26 07:09:29 +0000
+++ doc/source/flexibility/innodb_files_extend.rst 1970-01-01 00:00:00 +0000
@@ -1,25 +0,0 @@
1.. _innodb_files_extend:
2
3================================
4 Support of Multiple Page Sizes
5================================
6
7In 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.
8
9This feature adds a new system variable for setting it.
10
11
12System Variables
13================
14
15.. variable:: innodb_page_size
16
17 :cli: Yes
18 :conf: Yes
19 :scope:
20 :dyn: No
21 :vartype: ULONG
22 :default: 1 « 14
23 :range: 1 « 12 to 1 « ``UNIV_PAGE_SIZE_SHIFT_MAX``
24
25**EXPERIMENTAL**: The universal page size of the database. Changing for an existing database is not supported. Use at your own risk!
260
=== modified file 'doc/source/flexibility/log_warnings_suppress.rst'
--- doc/source/flexibility/log_warnings_suppress.rst 2013-05-27 13:46:13 +0000
+++ doc/source/flexibility/log_warnings_suppress.rst 2013-06-03 10:03:31 +0000
@@ -10,11 +10,8 @@
10Version Specific Information10Version Specific Information
11============================11============================
1212
13 * :rn:`5.5.8-20.0`:13 * :rn:`5.6.11-60.3`:
14 System variable :variable:`log_warnings_silence` introduced.14 Variable :variable:`log_warnings_suppress` ported from |Percona Server| 5.5.
15
16 * :rn:`5.5.10-20.1`:
17 Renamed variable :variable:`log_warnings_silence` to :variable:`log_warnings_suppress`.
1815
1916
20System Variables17System Variables
@@ -22,8 +19,7 @@
2219
23.. variable:: log_warnings_suppress20.. variable:: log_warnings_suppress
2421
25 :version 5.5.8-20.0: Introduced.22 :version 5.6.11-60.3: Introduced.
26 :version 5.5.10-20.1: Renamed.
27 :cli: Yes23 :cli: Yes
28 :conf: Yes24 :conf: Yes
29 :scope: Global25 :scope: Global
@@ -32,12 +28,8 @@
32 :default: ``(empty string)``28 :default: ``(empty string)``
33 :range: ``(empty string)``, ``1592``29 :range: ``(empty string)``, ``1592``
3430
35This 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.
36
37It is intended to provide a more general mechanism for disabling warnings than existed previously with variable :variable:`suppress_log_warning_1592`.31It is intended to provide a more general mechanism for disabling warnings than existed previously with variable :variable:`suppress_log_warning_1592`.
38
39When set to the empty string, no warnings are disabled. When set to ``1592``, warning #1592 messages (unsafe statement for binary logging) are suppressed.32When set to the empty string, no warnings are disabled. When set to ``1592``, warning #1592 messages (unsafe statement for binary logging) are suppressed.
40
41In the future, the ability to optionally disable additional warnings may also be added.33In the future, the ability to optionally disable additional warnings may also be added.
4234
4335
4436
=== modified file 'doc/source/flexibility/max_binlog_files.rst'
--- doc/source/flexibility/max_binlog_files.rst 2013-01-30 10:01:50 +0000
+++ doc/source/flexibility/max_binlog_files.rst 2013-06-03 10:03:31 +0000
@@ -11,7 +11,7 @@
11Version Specific Information11Version Specific Information
12============================12============================
1313
14 * 5.5.27-29.0:14 * 5.6.11-60.3:
15 Variable :variable:`max_binlog_files` introduced.15 Variable :variable:`max_binlog_files` introduced.
1616
17System Variables17System Variables
@@ -19,7 +19,7 @@
1919
20.. variable:: max_binlog_files20.. variable:: max_binlog_files
2121
22 :version 5.5.27-29.0: Introduced.22 :version 5.6.11-60.3: Introduced.
23 :cli: Yes23 :cli: Yes
24 :conf: Yes24 :conf: Yes
25 :scope: Global25 :scope: Global
2626
=== modified file 'doc/source/index.rst'
--- doc/source/index.rst 2013-05-13 04:25:56 +0000
+++ doc/source/index.rst 2013-06-03 10:03:31 +0000
@@ -51,6 +51,15 @@
51 installation51 installation
52 upgrading_guide_55_5652 upgrading_guide_55_56
5353
54Scalability Improvements
55========================
56
57.. toctree::
58 :maxdepth: 1
59 :glob:
60
61 scalability/innodb_io
62
54Performance Improvements63Performance Improvements
55========================64========================
5665
@@ -69,6 +78,9 @@
69 :maxdepth: 178 :maxdepth: 1
70 :glob:79 :glob:
7180
81 flexibility/log_warnings_suppress
82 flexibility/improved_memory_engine
83 flexibility/max_binlog_files
72 flexibility/mysqldump_ignore_create_error84 flexibility/mysqldump_ignore_create_error
7385
74Reliability Improvements86Reliability Improvements
@@ -81,6 +93,7 @@
81 reliability/log_connection_error93 reliability/log_connection_error
82 reliability/error_pad94 reliability/error_pad
83 reliability/innodb_corrupt_table_action95 reliability/innodb_corrupt_table_action
96 reliability/show_slave_status_nolock
8497
85Management Improvements98Management Improvements
86=======================99=======================
@@ -89,7 +102,7 @@
89 :maxdepth: 1102 :maxdepth: 1
90 :glob:103 :glob:
91104
92 management/udf_maatkit105 management/udf_percona_toolkit
93 management/innodb_fake_changes106 management/innodb_fake_changes
94 management/innodb_kill_idle_trx107 management/innodb_kill_idle_trx
95 management/enforce_engine108 management/enforce_engine
@@ -107,16 +120,16 @@
107 :maxdepth: 1120 :maxdepth: 1
108 :glob:121 :glob:
109122
110 diagnostics/index_info_schema_tables
111 diagnostics/innodb_stats
112 diagnostics/user_stats123 diagnostics/user_stats
113 diagnostics/slow_extended_55124 diagnostics/slow_extended
114 diagnostics/innodb_show_status125 diagnostics/innodb_show_status
115 diagnostics/innodb_deadlock_count126 diagnostics/innodb_deadlock_count
116 diagnostics/mysql_syslog127 diagnostics/mysql_syslog
117 diagnostics/show_engines128 diagnostics/show_engines
129 diagnostics/innodb_show_lock_names
118 diagnostics/process_list130 diagnostics/process_list
119 diagnostics/misc_info_schema_tables131 diagnostics/misc_info_schema_tables
132 diagnostics/thread_based_profiling
120133
121Reference134Reference
122=========135=========
@@ -128,6 +141,7 @@
128 upstream-bug-fixes141 upstream-bug-fixes
129 development142 development
130 trademark-policy143 trademark-policy
144 index_info_schema_tables
131 faq145 faq
132 compatibility146 compatibility
133 release-notes/release-notes_index147 release-notes/release-notes_index
134148
=== modified file 'doc/source/index_info_schema_tables.rst'
--- doc/source/index_info_schema_tables.rst 2012-12-10 15:24:56 +0000
+++ doc/source/index_info_schema_tables.rst 2013-06-03 10:03:31 +0000
@@ -8,11 +8,11 @@
88
9 * :table:`CLIENT_STATISTICS`9 * :table:`CLIENT_STATISTICS`
1010
11 * :table:`GLOBAL_TEMPORARY_TABLES`
12
11 * :table:`INDEX_STATISTICS`13 * :table:`INDEX_STATISTICS`
1214
13 * :table:`GLOBAL_TEMPORARY_TABLES`15 * :table:`INNODB_CHANGED_PAGES`
14
15 * :table:`QUERY_RESPONSE_TIME`
1616
17 * :table:`TABLE_STATISTICS`17 * :table:`TABLE_STATISTICS`
1818
@@ -22,37 +22,9 @@
2222
23 * :table:`USER_STATISTICS`23 * :table:`USER_STATISTICS`
2424
25 * :table:`INNODB_RSEG`25 * :table:`XTRADB_INTERNAL_HASH_TABLES`
2626
27 * :table:`INNODB_UNDO_LOGS`27 * :table:`XTRADB_READ_VIEW`
2828
29 * :table:`INNODB_SYS_TABLESTATS`29 * :table:`XTRADB_RSEG`
30
31 * :table:`INNODB_INDEX_STATS`
32
33 * :table:`INNODB_CHANGED_PAGES`
34
35 * :table:`INNODB_BUFFER_POOL_PAGES`
36
37 * :table:`INNODB_BUFFER_POOL_PAGES_BLOB`
38
39 * :table:`INNODB_BUFFER_POOL_PAGES_INDEX`
40
41 * :table:`INNODB_SYS_TABLES`
42
43 * :table:`INNODB_SYS_FIELDS`
44
45 * :table:`INNODB_SYS_COLUMNS`
46
47 * :table:`INNODB_SYS_STATS`
48
49 * :table:`INNODB_SYS_FOREIGN`
50
51 * :table:`INNODB_SYS_INDEXES`
52
53 * :table:`XTRADB_ADMIN_COMMAND`
54
55 * :table:`INNODB_TABLE_STATS`
56
57 * :table:`INNODB_SYS_FOREIGN_COLS`
5830
5931
=== modified file 'doc/source/installation.rst'
--- doc/source/installation.rst 2013-05-27 13:59:13 +0000
+++ doc/source/installation.rst 2013-06-03 10:03:31 +0000
@@ -201,4 +201,4 @@
201201
202.. note::202.. note::
203203
204 :ref:`pam_plugin` has been merged into |Percona Server| in :rn:`5.5.24-26.0` but it is not built with the server by default. In order to build the Percona Server with PAM plugin, additional option :option:`-DWITH_PAM=ON` should be used.204 :ref:`pam_plugin` is not built with the server by default. In order to build the Percona Server with PAM plugin, additional option :option:`-DWITH_PAM=ON` should be used.
205205
=== modified file 'doc/source/management/changed_page_tracking.rst'
--- doc/source/management/changed_page_tracking.rst 2013-03-06 06:41:38 +0000
+++ doc/source/management/changed_page_tracking.rst 2013-06-03 10:03:31 +0000
@@ -18,7 +18,7 @@
18User statements for handling the XtraDB changed page bitmaps18User statements for handling the XtraDB changed page bitmaps
19============================================================19============================================================
2020
21In |Percona Server| :rn:`5.5.29-30.0` new statements have been introduced for handling the changed page bitmap tracking. All of these statements require ``SUPER`` privilege.21In |Percona Server| :rn:`5.6.11-60.3` new statements have been introduced for handling the changed page bitmap tracking. All of these statements require ``SUPER`` privilege.
2222
23 * ``FLUSH CHANGED_PAGE_BITMAPS`` - this statement can be used for synchronous bitmap write for immediate catch-up with the log checkpoint. This is used by innobackupex to make sure that XtraBackup indeed has all the required data it needs.23 * ``FLUSH CHANGED_PAGE_BITMAPS`` - this statement can be used for synchronous bitmap write for immediate catch-up with the log checkpoint. This is used by innobackupex to make sure that XtraBackup indeed has all the required data it needs.
24 * ``RESET CHANGED_PAGE_BITMAPS`` - this statement will delete all the bitmap log files and restart the bitmap log file sequence.24 * ``RESET CHANGED_PAGE_BITMAPS`` - this statement will delete all the bitmap log files and restart the bitmap log file sequence.
@@ -52,8 +52,7 @@
5252
53.. variable:: innodb_max_changed_pages53.. variable:: innodb_max_changed_pages
5454
55 :version 5.5.27-29.0: Variable :variable:`innodb_changed_pages_limit` introduced55 :version 5.6.11-60.3: Variable :variable:`innodb_max_changed_pages` introduced
56 :version 5.5.29-30.0: Variable renamed to :variable:`innodb_max_changed_pages`
57 :cli: Yes56 :cli: Yes
58 :conf: Yes57 :conf: Yes
59 :scope: Global58 :scope: Global
@@ -64,7 +63,7 @@
6463
65.. variable:: innodb_track_changed_pages64.. variable:: innodb_track_changed_pages
6665
67 :version 5.5.27-29.0: Variable introduced66 :version 5.6.11-60.3: Variable introduced
68 :cli: Yes67 :cli: Yes
69 :conf: Yes68 :conf: Yes
70 :scope: Global69 :scope: Global
@@ -75,7 +74,7 @@
7574
76.. variable:: innodb_max_bitmap_file_size75.. variable:: innodb_max_bitmap_file_size
7776
78 :version 5.5.28-29.2: Variable introduced77 :version 5.6.11-60.3: Variable introduced
79 :cli: Yes78 :cli: Yes
80 :conf: Yes79 :conf: Yes
81 :scope: Global80 :scope: Global
8281
=== modified file 'doc/source/management/enforce_engine.rst'
--- doc/source/management/enforce_engine.rst 2012-12-14 09:58:23 +0000
+++ doc/source/management/enforce_engine.rst 2013-06-03 10:03:31 +0000
@@ -13,7 +13,7 @@
13Version Specific Information13Version Specific Information
14============================14============================
1515
16 * :rn:`5.5.24-26.0`16 * :rn:`5.6.11-60.3`
17 Variable :variable:`enforce_storage_engine` implemented.17 Variable :variable:`enforce_storage_engine` implemented.
1818
19System Variables19System Variables
2020
=== modified file 'doc/source/management/expanded_program_option_modifiers.rst'
--- doc/source/management/expanded_program_option_modifiers.rst 2012-09-04 23:24:39 +0000
+++ doc/source/management/expanded_program_option_modifiers.rst 2013-06-03 10:03:31 +0000
@@ -31,7 +31,7 @@
31Version Specific Information31Version Specific Information
32============================32============================
3333
34 * :rn:`5.5.27-28.0`34 * :rn:`5.6.11-60.3`
35 Expanded program option modifiers implemented35 Expanded program option modifiers implemented
3636
37Examples37Examples
3838
=== modified file 'doc/source/management/innodb_fake_changes.rst'
--- doc/source/management/innodb_fake_changes.rst 2013-05-27 13:59:13 +0000
+++ doc/source/management/innodb_fake_changes.rst 2013-06-03 10:03:31 +0000
@@ -4,7 +4,7 @@
4 Support for Fake Changes4 Support for Fake Changes
5==========================5==========================
66
7Restarting 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.7Restarting 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 sped up by having prefetch threads to warm the server: replay statements and then rollback at commit.
88
9That makes prefetch simple but has high overhead from locking rows only to undo changes at rollback.9That makes prefetch simple but has high overhead from locking rows only to undo changes at rollback.
1010
@@ -35,7 +35,7 @@
3535
36.. variable:: innodb_fake_changes36.. variable:: innodb_fake_changes
37 37
38 :version 5.5.16-22.0: Introduced38 :version 5.6.11-60.3: Introduced
39 :scope: ``GLOBAL``39 :scope: ``GLOBAL``
40 :type: ``BOOLEAN``40 :type: ``BOOLEAN``
41 :dyn: ``YES``41 :dyn: ``YES``
@@ -43,6 +43,16 @@
4343
44 This variable enables the *Fake Changes* feature.44 This variable enables the *Fake Changes* feature.
4545
46.. variable:: innodb_locking_fake_changes
47
48 :version 5.6.11-60.3: Introduced
49 :scope: ``GLOBAL``
50 :type: ``BOOLEAN``
51 :dyn: ``YES``
52 :default: ``TRUE``
53
54 When this variable is set to ``FALSE``, it makes fake transactions not to take any row locks. This feature was implemented because, although fake change transactions downgrade the requested exclusive (X) row locks to shared (S) locks, these S locks prevent X locks from being taken and block the real changes. However, this option is not safe to set to FALSE by default, because the fake changes implementation is not ready for lock-less operation for all workloads. Namely, if a real transaction will remove a row that a fake transaction is doing a secondary index maintenance for, the latter will fail. This option is considered experimental and might be removed in the future if lockless operation mode fixes are implemented.
55
46Implementation Details56Implementation Details
47======================57======================
4858
4959
=== removed file 'doc/source/management/innodb_fast_index_creation.rst'
--- doc/source/management/innodb_fast_index_creation.rst 2012-12-14 09:58:23 +0000
+++ doc/source/management/innodb_fast_index_creation.rst 1970-01-01 00:00:00 +0000
@@ -1,63 +0,0 @@
1.. _innodb_fast_index_creation:
2
3=====================
4 Fast Index Creation
5=====================
6
7Percona has implemented several changes related to |MySQL|'s fast index creation feature. Extended features, besides disabling :variable:`fast_index_creation`, can be enabled with :variable:`expand_fast_index_creation`.
8
9Disabling Fast Index Creation
10=============================
11
12Fast 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.
13
14This feature implements a session variable that disables fast index creation. This causes indexes to be created in the way they were created before fast index creation was implemented. While this is slower, it avoids the problem of data dictionary inconsistency between |MySQL| and |InnoDB|.
15
16
17Tunable buffer size for fast index creation
18===========================================
19
20|Percona Server| supports tunable buffer size for fast index creation in |InnoDB|. This value was calculated based on the merge block size (which was hardcoded to 1 MB) and the minimum index record size. By adding the session variable :variable:`innodb_merge_sort_block_size` block size that is used in the merge sort can now be adjusted for better performance.
21
22
23Version Specific Information
24============================
25
26 * :rn:`5.5.8-20.0`:
27 Variable :variable:`fast_index_creation` implemented.
28
29 * :rn:`5.5.11-20.2`:
30 Expanded the applicability of fast index creation to :command:`mysqldump`, ``ALTER TABLE``, and ``OPTIMIZE TABLE``.
31
32 * :rn:`5.5.27-28.0`
33 Variable :variable:`innodb_merge_sort_block_size` implemented.
34
35System Variables
36================
37
38.. variable:: fast_index_creation
39
40 :cli: Yes
41 :conf: No
42 :scope: Local
43 :dyn: Yes
44 :vartype: Boolean
45 :default: ON
46 :range: ON/OFF
47
48.. variable:: innodb_merge_sort_block_size
49
50 :cli: Yes
51 :conf: Yes
52 :scope: Global
53 :dyn: Yes
54 :vartype: ULONG
55 :default: 1048576 (1M)
56 :range: 1048576 - 1073741824 (1G)
57
58Other Reading
59=============
60
61 * `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/>`_
62
63 * `Building Indexes by Sorting In Innodb <http://www.mysqlperformanceblog.com/2012/06/19/building-indexes-by-sorting-in-innodb-aka-fast-index-creation/>`_
640
=== modified file 'doc/source/management/innodb_kill_idle_trx.rst'
--- doc/source/management/innodb_kill_idle_trx.rst 2013-05-26 13:51:37 +0000
+++ doc/source/management/innodb_kill_idle_trx.rst 2013-06-03 10:03:31 +0000
@@ -11,7 +11,7 @@
1111
12.. variable:: innodb_kill_idle_transaction12.. variable:: innodb_kill_idle_transaction
13 13
14 :version 5.5.16-22.0: Introduced14 :version 5.6.11-60.3: Introduced
15 :scope: ``GLOBAL``15 :scope: ``GLOBAL``
16 :config: ``YES``16 :config: ``YES``
17 :dyn: ``YES``17 :dyn: ``YES``
1818
=== modified file 'doc/source/management/pam_plugin.rst'
--- doc/source/management/pam_plugin.rst 2013-02-06 10:26:45 +0000
+++ doc/source/management/pam_plugin.rst 2013-06-03 10:03:31 +0000
@@ -68,5 +68,5 @@
68Version Specific Information68Version Specific Information
69============================69============================
7070
71 * :rn:`5.5.24-26.0`71 * :rn:`5.6.11-60.3`
72 PAM authentication plugin has been integrated with |Percona Server|.72 PAM authentication plugin has been integrated with |Percona Server|.
7373
=== modified file 'doc/source/management/secure_file_priv_extended.rst'
--- doc/source/management/secure_file_priv_extended.rst 2012-09-04 23:24:39 +0000
+++ doc/source/management/secure_file_priv_extended.rst 2013-06-03 10:03:31 +0000
@@ -10,7 +10,7 @@
10Version Specific Information10Version Specific Information
11============================11============================
1212
13 * :rn:`5.5.25a-27.1`13 * :rn:`5.6.11-60.3`
14 Variable :variable:`secure-file-priv` extended behavior implemented.14 Variable :variable:`secure-file-priv` extended behavior implemented.
1515
16System Variables16System Variables
1717
=== removed file 'doc/source/management/udf_maatkit.rst'
--- doc/source/management/udf_maatkit.rst 2013-05-10 09:39:17 +0000
+++ doc/source/management/udf_maatkit.rst 1970-01-01 00:00:00 +0000
@@ -1,49 +0,0 @@
1.. _udf_maatkit:
2
3======================================
4 *Percona Toolkit* and *Maatkit* UDFs
5======================================
6
7Three *Percona Toolkit* and *Maatkit* UDFs that provide faster checksums are provided:
8
9 * ``fnv_udf``
10
11 * ``fnv1a_udf``
12
13 * ``murmur_udf``
14
15Version Specific Information
16============================
17
18 * 5.1.53-12.4:
19 Began distributing ``fnv_udf``, ``fnv1a_udf``, and ``murmur_udf``.
20
21Other Information
22=================
23
24 * Author / Origin:
25 Baron Schwartz
26
27Installation
28============
29
30Use 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: ::
31
32 $ yum install percona-toolkit
33
34This 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: ::
35
36 $ mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ``fnv_udf.so``"
37 $ mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME ``fnv1a_udf.so``"
38 $ mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME ``murmur_udf.so``"
39
40Executing each of these commands will install its respective UDF into the server.
41
42If you have any difficulty, or require more detailed information, refer to the *Maatkit* `Installing UDFs <http://code.google.com/p/maatkit/wiki/InstallingUdfs>`_ documentation.
43
44
45Other Reading
46=============
47
48 * *Percona Toolkit* - http://www.percona.com/doc/percona-toolkit
49 * *Maatkit* - http://www.maatkit.org/
500
=== added file 'doc/source/management/udf_percona_toolkit.rst'
--- doc/source/management/udf_percona_toolkit.rst 1970-01-01 00:00:00 +0000
+++ doc/source/management/udf_percona_toolkit.rst 2013-06-03 10:03:31 +0000
@@ -0,0 +1,54 @@
1.. _udf_percona_toolkit:
2
3========================
4 *Percona Toolkit* UDFs
5========================
6
7Three *Percona Toolkit* UDFs that provide faster checksums are provided:
8
9 * ``fnv_udf``
10
11 * ``fnv1a_udf``
12
13 * ``murmur_udf``
14
15Version Specific Information
16============================
17
18 * :rn:`5.6.11-60.3`:
19 Began distributing ``fnv_udf``, ``fnv1a_udf``, and ``murmur_udf``.
20
21Other Information
22=================
23
24 * Author / Origin:
25 Baron Schwartz
26
27Installation
28============
29
30Use 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: ::
31
32 $ yum install percona-toolkit
33
34This 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: ::
35
36 $ mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ``fnv_udf.so``"
37 $ mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME ``fnv1a_udf.so``"
38 $ mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME ``murmur_udf.so``"
39
40Executing each of these commands will install its respective UDF into the server.
41
42Troubleshooting
43================
44
45If you get the error: ::
46
47 ERROR 1126 (HY000): Can't open shared library 'fnv_udf.so' (errno: 22 fnv_udf.so: cannot open shared object file: No such file or directory)
48
49Then you may need to copy the .so file to another location in your system. Try both ``/lib`` and ``/usr/lib``. Look at your environment's ``$LD_LIBRARY_PATH`` variable for clues. If none is set, and neither ``/lib`` nor ``/usr/lib`` works, you may need to set ``LD_LIBRARY_PATH`` to ``/lib`` or ``/usr/lib``.
50
51Other Reading
52=============
53
54 * *Percona Toolkit* `documentation <http://www.percona.com/doc/percona-toolkit/>`_
055
=== modified file 'doc/source/management/utility_user.rst'
--- doc/source/management/utility_user.rst 2012-09-04 23:24:39 +0000
+++ doc/source/management/utility_user.rst 2013-06-03 10:03:31 +0000
@@ -26,7 +26,7 @@
2626
27In order to have the ability for a special type of MySQL user, which will have a very limited and special amount of control over the system and can not be see or modified by any other user including the root user, three new options have been added.27In order to have the ability for a special type of MySQL user, which will have a very limited and special amount of control over the system and can not be see or modified by any other user including the root user, three new options have been added.
2828
29Option :variable:`utility_user` specifies the user which the system will create and recognize as the utility user. The host in the utility user specification follows conventions described in the `MySQL manual <http://dev.mysql.com/doc/refman/5.5/en/connection-access.html>`_, i.e. it allows wildcards and IP masks. Anonymous user names are not permitted to be used for the utility user name.29Option :variable:`utility_user` specifies the user which the system will create and recognize as the utility user. The host in the utility user specification follows conventions described in the `MySQL manual <http://dev.mysql.com/doc/refman/5.6/en/connection-access.html>`_, i.e. it allows wildcards and IP masks. Anonymous user names are not permitted to be used for the utility user name.
3030
31This user must not be an exact match to any other user that exists in the mysql.user table. If the server detects that the user specified with this option exactly matches any user within the mysql.user table on start up, the server will report an error and shut down gracefully. If host name wildcards are used and a more specific user specification is identified on start up, the server will report a warning and continue. 31This user must not be an exact match to any other user that exists in the mysql.user table. If the server detects that the user specified with this option exactly matches any user within the mysql.user table on start up, the server will report an error and shut down gracefully. If host name wildcards are used and a more specific user specification is identified on start up, the server will report a warning and continue.
3232
@@ -51,7 +51,7 @@
5151
52.. variable:: utility_user52.. variable:: utility_user
5353
54 :version 5.5.27-28.0: Implemented54 :version 5.6.11-60.3: Implemented
55 :cli: Yes55 :cli: Yes
56 :conf: utility_user=<user@host>56 :conf: utility_user=<user@host>
57 :scope: Global57 :scope: Global
@@ -63,7 +63,7 @@
6363
64.. variable:: utility_user_password64.. variable:: utility_user_password
6565
66 :version 5.5.27-28.0: Implemented66 :version 5.6.11-60.3: Implemented
67 :cli: Yes67 :cli: Yes
68 :conf: utility_user_password=<password>68 :conf: utility_user_password=<password>
69 :scope: Global69 :scope: Global
@@ -75,7 +75,7 @@
7575
76.. variable:: utility_user_schema_access76.. variable:: utility_user_schema_access
7777
78 :version 5.5.27-28.0: Implemented78 :version 5.6.11-60.3: Implemented
79 :cli: Yes79 :cli: Yes
80 :conf: utility_user_schema_access=<schema>,<schema>,<schema>80 :conf: utility_user_schema_access=<schema>,<schema>,<schema>
81 :scope: Global81 :scope: Global
8282
=== modified file 'doc/source/performance/innodb_numa_support.rst'
--- doc/source/performance/innodb_numa_support.rst 2013-05-12 09:13:00 +0000
+++ doc/source/performance/innodb_numa_support.rst 2013-06-03 10:03:31 +0000
@@ -13,7 +13,7 @@
13Version Specific Information13Version Specific Information
14============================14============================
1515
16 * :rn:`5.5.28-29.1`16 * :rn:`5.6.11-60.3`
17 Improved ``NUMA`` support implemented. This feature was ported from Twitter's |MySQL| patches.17 Improved ``NUMA`` support implemented. This feature was ported from Twitter's |MySQL| patches.
1818
19System Variables19System Variables
2020
=== modified file 'doc/source/performance/query_cache_enhance.rst'
--- doc/source/performance/query_cache_enhance.rst 2013-05-26 07:09:29 +0000
+++ doc/source/performance/query_cache_enhance.rst 2013-06-03 10:03:31 +0000
@@ -38,19 +38,19 @@
3838
39This 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.39This 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.
4040
41Imagine that we run three queries simultaneously (each one in a separate thread):41Imagine that we run three queries simultaneously (each one in a separate thread): ::
4242
43 > SELECT number from t where id > 0;43 > SELECT number from t where id > 0;
44 > SELECT number from t where id > 0;44 > SELECT number from t where id > 0;
45 > SELECT number from t where id > 0;45 > SELECT number from t where id > 0;
4646
47If we experience query cache contention, the output of SHOW PROCESSLIT will look like this: ::47If we experience query cache contention, the output of ``SHOW PROCESSLIST`` will look like this: ::
4848
49 > SHOW PROCESSLIST;49 > SHOW PROCESSLIST;
50 Id User Host db Command Time State Info50 Id User Host db Command Time State Info
51 2 root localhost test Sleep 2 NULL51 2 root localhost test Sleep 2 NULL
52 3 root localhost test Query 2 Waiting on query cache mutex SELECT number from t where id > 0;52 3 root localhost test Query 2 Waiting on query cache mutex SELECT number from t where id > 0;
53 4 root localhost test Query 1 Waiting on query cache mutex SELECT number from t where id > 0;53 4 root localhost test Query 1 Waiting on query cache mutex SELECT number from t where id > 0;
54 5 root localhost test Query 0 NULL54 5 root localhost test Query 0 NULL
5555
56Ignoring comments56Ignoring comments
@@ -66,36 +66,6 @@
66If 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.66If 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.
6767
6868
69.. Version Specific Information
70.. ----------------------------
71
72.. Disabling the query cache completely
73
74.. Percona Server Version Comments
75.. 5.1.49-12.0 Full functionality available.
76.. Diagnosing contention more easily
77
78.. Percona Server Version Comments
79.. 5.1.49-12.0 Full functionality available.
80.. Ignoring comments
81
82.. Percona Server Version Comments
83.. 5.1.47-11.0 Critical bug (see MySQL bug 55032). Release was recalled.
84.. 5.1.47-11.1 Fixed critical bug from previous release. MySQL bug 55032 actual. Bug b603618 actual. Bug 603619 actual.
85.. 5.1.47-11.2 Full functionality available.
86.. 5.1.48-12.0 Full functionality available.
87
88.. Other Information
89
90.. Disabling the query cache completely
91
92.. Author/Origin Percona
93.. Bugs fixed LP bug 609027, MySQL bug 38551
94.. Diagnosing contention more easily
95
96.. Author/Origin Percona
97.. Bugs fixed LP bug589484
98
99System Variables69System Variables
100================70================
10171
@@ -116,8 +86,4 @@
11686
117 * `MySQL general thread states <http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html>`_87 * `MySQL general thread states <http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html>`_
11888
119 * `RAII <http://en.wikibooks.org/wiki/More_C%2B%2B_Idioms/Resource_Acquisition_Is_Initialization>`_
120
121 * `Scope guard <http://en.wikibooks.org/wiki/More_C%2B%2B_Idioms/Scope_Guard>`_
122
123 * `Query cache freezes <http://www.mysqlperformanceblog.com/2009/03/19/mysql-random-freezes-could-be-the-query-cache/>`_89 * `Query cache freezes <http://www.mysqlperformanceblog.com/2009/03/19/mysql-random-freezes-could-be-the-query-cache/>`_
12490
=== modified file 'doc/source/reliability/error_pad.rst'
--- doc/source/reliability/error_pad.rst 2013-05-27 13:59:13 +0000
+++ doc/source/reliability/error_pad.rst 2013-06-03 10:03:31 +0000
@@ -4,17 +4,17 @@
4 Error Code Compatibility4 Error Code Compatibility
5==========================5==========================
66
7|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.7|Percona Server| with |XtraDB| has error code incompatibilities with |MySQL| 5.6. 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.
88
9The reasons for the current incompatibilities are:9The reasons for the current incompatibilities are:
1010
11 * |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.11 * |Percona Server| with |XtraDB| contains features that have been backported from MyQL 5.6. Some of the |MySQL| 5.6 features added new error codes.
1212
13 * Some |Percona Server| with |XtraDB| features have added new error codes.13 * Some |Percona Server| with |XtraDB| features have added new error codes.
1414
15The 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.15The solution to the first problem is to preserve |MySQL| 5.6 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.6.
1616
17After migrating |Percona Server| / |XtraDB| to |MySQL| 5.5, users might experience troubles because of this.17After migrating |Percona Server| / |XtraDB| to |MySQL| 5.6, users might experience troubles because of this.
1818
19The 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.19The 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.
2020
@@ -51,5 +51,5 @@
51Version-Specific Information51Version-Specific Information
52============================52============================
5353
54 * 5.5.8-20.054 * :rn:`5.6.11-60.3`
55 Full functionality available.55 Feature ported from |Percona Server| 5.5.
5656
=== modified file 'doc/source/reliability/log_connection_error.rst'
--- doc/source/reliability/log_connection_error.rst 2013-05-27 13:59:13 +0000
+++ doc/source/reliability/log_connection_error.rst 2013-06-03 10:03:31 +0000
@@ -10,5 +10,5 @@
10Version-Specific Information10Version-Specific Information
11============================11============================
1212
13 * 5.5.8-20.0:13 * :rn:`5.6.11-30.3`:
14 Full functionality available.14 Feature ported from |Percona Server| 5.5.
1515
=== modified file 'doc/source/reliability/show_slave_status_nolock.rst'
--- doc/source/reliability/show_slave_status_nolock.rst 2013-05-06 15:43:51 +0000
+++ doc/source/reliability/show_slave_status_nolock.rst 2013-06-03 10:03:31 +0000
@@ -14,10 +14,12 @@
1414
15This will display the slave's status as if there were no lock, allowing the user to detect and understand the situation that is occurring.15This will display the slave's status as if there were no lock, allowing the user to detect and understand the situation that is occurring.
1616
17**NOTE:** The information given when ``NOLOCK`` is used may be slightly inconsistent with the actual situation while the lock is being held.17.. note::
18
19 The information given when ``NOLOCK`` is used may be slightly inconsistent with the actual situation while the lock is being held.
1820
1921
20Version Specific Information22Version Specific Information
21============================23============================
2224
23 * 5.5.8-20.0:25 * :rn:`5.6.11-60.3`: Feature ported from |Percona Server| 5.5.
2426
=== added file 'doc/source/scalability/innodb_io.rst'
--- doc/source/scalability/innodb_io.rst 1970-01-01 00:00:00 +0000
+++ doc/source/scalability/innodb_io.rst 2013-06-03 10:03:31 +0000
@@ -0,0 +1,71 @@
1.. _innodb_io_page:
2
3===================================
4 Improved |InnoDB| I/O Scalability
5===================================
6
7Because |InnoDB| is a complex storage engine it must be configured properly in order to perform at its best. Some points are not configurable in standard |InnoDB|. The goal of this feature is to provide a more exhaustive set of options for |XtraDB|, like ability to change the log block size.
8
9Version Specific Information
10============================
11
12 * :rn:`5.6.11-60.3`
13
14 * Feature ported from |Percona Server| 5.5
15
16System Variables
17================
18
19
20.. variable:: innodb_use_global_flush_log_at_trx_commit
21
22 :cli: Yes
23 :conf: Yes
24 :scope: Global
25 :dyn: Yes
26 :type: Boolean
27 :default: True
28 :range: True/False
29
30This variable is used to control the ability of the user to set the value of the global |MySQL| variable ``innodb_flush_log_at_trx_commit``.
31
32If ``innodb_use_global_flush_log_at_trx_commit=0`` (False), the client can set the global |MySQL| variable, using: ::
33
34 SET innodb_use_global_flush_log_at_trx_commit=N
35
36If ``innodb_use_global_flush_log_at_trx_commit=1`` (True), the user session will use the current value of ``innodb_flush_log_at_trx_commit``, and the user cannot reset the value of the global variable using a ``SET`` command.
37
38.. variable:: innodb_log_block_size
39
40 :cli: Yes
41 :conf: Yes
42 :scope: Global
43 :dyn: No
44 :vartype: Numeric
45 :default: 512
46 :units: Bytes
47
48This variable changes the size of transaction log records. The default size of 512 bytes is good in most situations. However, setting it to 4096 may be a good optimization with SSD cards. While settings other than 512 and 4096 are possible, as a practical matter these are really the only two that it makes sense to use. Clean restart and removal of the old logs is needed for the variable :variable:`innodb_log_block_size` to be changed.
49
50Status Variables
51----------------
52
53The following information has been added to ``SHOW ENGINE INNODB STATUS`` to confirm the checkpointing activity: ::
54
55 The max checkpoint age
56 The current checkpoint age target
57 The current age of the oldest page modification which has not been flushed to disk yet.
58 The current age of the last checkpoint
59 ...
60 ---
61 LOG
62 ---
63 Log sequence number 0 1059494372
64 Log flushed up to 0 1059494372
65 Last checkpoint at 0 1055251010
66 Max checkpoint age 162361775
67 Checkpoint age target 104630090
68 Modified age 4092465
69 Checkpoint age 4243362
70 0 pending log writes, 0 pending chkp writes
71 ...

Subscribers

People subscribed via source and target branches