Author: jdrussel
Date: 2011-04-08 23:00:18 +0200 (Fri, 08 Apr 2011)
New Revision: 25815
Log:
Add more detail about persistent stats, and related config options.
Modified:
trunk/refman-5.6/innodb-performance.xml
trunk/refman-5.6/se-innodb-core.xml
Modified: trunk/refman-5.6/innodb-performance.xml
===================================================================
--- trunk/refman-5.6/innodb-performance.xml 2011-04-08 19:37:09 UTC (rev 25814)
+++ trunk/refman-5.6/innodb-performance.xml 2011-04-08 21:00:18 UTC (rev 25815)
Changed blocks: 2, Lines Added: 50, Lines Deleted: 8; 3587 bytes
@@ -60,20 +60,51 @@
<link linkend="glos_query_execution_plan">query execution
plan</link>. Now you can make these statistics persistent, so that
the index usage and join order for a particular query is less
- likely to change. You turn on this feature by enabling the
- configuration option
- <literal role="sysvar">innodb_analyze_is_persistent</literal>. You
- control how much sampling is done to collect the statistics by
- setting the configuration options
+ likely to change.
+ </para>
+
+ <para>
+ To prepare a database instance for the persistent statistics
+ feature, run the script
+ <literal>storage/innobase/scripts/persistent_storage.sql</literal>,
+ which sets up the tables <literal>innodb.table_stats</literal>
+ and<literal>innodb.index_stats</literal>. Currently, this script
+ is available only in the source distribution, and does not run
+ automatically during any installation, upgrade, or build steps.
+ </para>
+
+ <para>
+ You turn on this feature by enabling the configuration option
+ <literal role="sysvar">innodb_analyze_is_persistent</literal>, and
+ then running the <literal role="stmt">ANALYZE TABLE</literal>
+ statement for each applicable table. You control how much sampling
+ is done to collect the statistics by setting the configuration
+ options
<literal role="sysvar">innodb_stats_persistent_sample_pages</literal>
and
<literal role="sysvar">innodb_stats_transient_sample_pages</literal>.
+ </para>
+
+ <para>
With this feature enabled, statistics are only gathered for a
- table by the <literal role="stmt">ANALYZE TABLE</literal> command,
- not the first time the table is accessed after each server
- restart.
+ table by the <literal role="stmt">ANALYZE TABLE</literal>
+ statement, not the first time the table is accessed after each
+ server restart. You might run this statement in your setup scripts
+ after representative data has been loaded into the table, and run
+ it periodically after DML operations significantly change the
+ contents of indexed columns, or on a schedule at times of low
+ activity.
</para>
+ <warning>
+ <para>
+ Because statistics are not automatically gathered when a new
+ index is created, always run <literal role="stmt">ANALYZE
+ TABLE</literal> after creating a new index when the persistent
+ statistics mode is enabled.
+ </para>
+ </warning>
+
<para>
Formerly, these statistics were cleared on each server restart and
after some other operations, and recomputed when the table was
@@ -83,6 +114,17 @@
variations in query performance.
</para>
+ <para>
+ Whenever statistics for a table or index are present in the
+ <literal>InnoDB</literal> system tables, they are used by the
+ optimizer. To revert to the previous method of collecting
+ statistics that are periodically erased, turn off the
+ <literal role="sysvar">innodb_analyze_is_persistent</literal>
+ setting, then remove the contents of the
+ <literal>innodb.table_stats</literal>
+ and<literal>innodb.index_stats</literal> tables.
+ </para>
+
</section>
<section id="innodb-performance-latching">
Modified: trunk/refman-5.6/se-innodb-core.xml
===================================================================
--- trunk/refman-5.6/se-innodb-core.xml 2011-04-08 19:37:09 UTC (rev 25814)
+++ trunk/refman-5.6/se-innodb-core.xml 2011-04-08 21:00:18 UTC (rev 25815)
Changed blocks: 2, Lines Added: 18, Lines Deleted: 6; 1868 bytes
@@ -12033,11 +12033,9 @@
<para condition="dynamic:optvar:item" role="5.6:mysqld:innodb_stats_sample_pages"/>
<para>
- The number of index pages to sample for index distribution
- statistics such as are calculated by
- <literal role="stmt">ANALYZE TABLE</literal>. The default
- value is 8. For more information, see
- <xref linkend="innodb-other-changes"/>.
+ Deprecated, use
+ <literal role="sysvar">innodb_stats_transient_sample_pages</literal>
+ instead.
</para>
</listitem>
@@ -12057,7 +12055,21 @@
<para condition="dynamic:optvar:item" role="5.6:mysqld:innodb_stats_transient_sample_pages"/>
- <para></para>
+ <para>
+ The number of index pages to sample for index distribution
+ statistics, such as those calculated by
+ <literal role="stmt">ANALYZE TABLE</literal>. The default
+ value is 8. This option only applies when the
+ <literal role="sysvar">innodb_analyze_is_persistent</literal>
+ setting is turned off; when
+ <literal role="sysvar">innodb_analyze_is_persistent</literal>
+ is turned on, the
+ <literal role="sysvar">innodb_stats_persistent_sample_pages</literal>
+ setting applies instead. Takes the place of the
+ <literal role="sysvar">innodb_stats_sample_pages</literal>
+ option. For more information, see
+ <xref linkend="innodb-other-changes"/>.
+ </para>
</listitem>
<listitem>
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r25815 - trunk/refman-5.6 | john.russell | 8 Apr |
