List:Commits« Previous MessageNext Message »
From:paul.dubois Date:January 14 2011 6:58pm
Subject:svn commit - mysqldoc@docsrva: r24814 - in trunk: . dynamic-docs/changelog refman-5.6 refman-6.0
View as plain text  
Author: paul
Date: 2011-01-14 19:58:55 +0100 (Fri, 14 Jan 2011)
New Revision: 24814

Log:
 r44093@dhcp-213:  paul | 2011-01-14 12:58:45 -0500
 Document WL#5694: change default of optimizer_join_cache_level from 1 to 4


Modified:
   trunk/dynamic-docs/changelog/mysqld-1.xml
   trunk/refman-5.6/dba-mysqld-server-core.xml
   trunk/refman-5.6/optimization.xml
   trunk/refman-6.0/optimization.xml

Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:46284
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:66486
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
ebeeeee4-b232-4669-a521-231442eced53:/mysqldoc-local/mysqldoc/trunk:44090
   + 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:46284
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:66486
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
ebeeeee4-b232-4669-a521-231442eced53:/mysqldoc-local/mysqldoc/trunk:44093


Modified: trunk/dynamic-docs/changelog/mysqld-1.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld-1.xml	2011-01-14 18:23:35 UTC (rev 24813)
+++ trunk/dynamic-docs/changelog/mysqld-1.xml	2011-01-14 18:58:55 UTC (rev 24814)
Changed blocks: 2, Lines Added: 40, Lines Deleted: 8; 2547 bytes

@@ -59360,6 +59360,37 @@
     <tags>
       <manual type="optimizer"/>
       <manual type="joins"/>
+      <manual type="block nested loop"/>
+    </tags>
+
+    <versions>
+      <version ver="5.6.1"/>
+    </versions>
+
+    <message>
+
+      <para>
+        The Block Nested-Loop (BNL) Join algorithm previously used only
+        for inner joins has been extended and can be employed for outer
+        join operations, including nested outer joins. For more
+        information, see <xref linkend="bnl-bka-optimization"/>.
+      </para>
+
+      <para>
+        In conjunction with this work, there is a new system variable,
+        <literal role="sysvar">optimizer_join_cache_level</literal>,
+        that controls how join buffering is done.
+      </para>
+
+    </message>
+
+  </logentry>
+
+  <logentry entrytype="feature">
+
+    <tags>
+      <manual type="optimizer"/>
+      <manual type="joins"/>
       <manual type="batched key access"/>
       <manual type="block nested loop"/>
     </tags>

@@ -59371,15 +59402,16 @@
     <message>
 
       <para>
-        A new algorithm that uses both index access to the joined table
-        and a join buffer has been implemented. It is called the Batched
-        Key Access (BKA) Join algorithm. The algorithm supports inner
-        join, outer join and semi-join operations, including nested
-        outer joins and nested semi-joins. Also, the Block Nested-Loop
-        (BNL) Join algorithm previously used only for inner joins has
-        been extended and can be employed for outer join and semi-join
+        An algorithm that uses both index access to the joined table and
+        a join buffer has been implemented. It is called the Batched Key
+        Access (BKA) Join algorithm. The algorithm supports inner join,
+        outer join and semi-join operations, including nested outer
+        joins and nested semi-joins. Also, the Block Nested-Loop (BNL)
+        Join algorithm previously used only for inner joins has been
+        extended and can be employed for outer join and semi-join
         operations, including nested outer joins and nested semi-joins.
-        For more information, see <xref linkend="optimization"/>.
+        For more information, see
+        <xref linkend="bnl-bka-optimization"/>.
       </para>
 
       <para>


Modified: trunk/refman-5.6/dba-mysqld-server-core.xml
===================================================================
--- trunk/refman-5.6/dba-mysqld-server-core.xml	2011-01-14 18:23:35 UTC (rev 24813)
+++ trunk/refman-5.6/dba-mysqld-server-core.xml	2011-01-14 18:58:55 UTC (rev 24814)
Changed blocks: 3, Lines Added: 10, Lines Deleted: 17; 2730 bytes

@@ -7923,14 +7923,9 @@
 
         <para>
           This variable is used for join buffer managment. It controls
-          how join buffers are used for join operations.
-
-<!--
- See
-          <xref linkend="bnl-bka-optimization"/>.
--->
-
-          The following table shows the permissible
+          how join buffers are used for join operations. See
+          <xref linkend="bnl-bka-optimization"/>. The following table
+          shows the permissible
           <literal role="sysvar">optimizer_join_cache_level</literal>
           values.
         </para>

@@ -7955,8 +7950,7 @@
               </row>
               <row>
                 <entry><literal>1</literal></entry>
-                <entry>Join buffers are employed exactly in the same cases as in versions of
-                  MySQL prior to 6.0.9: They are used only for inner
+                <entry>This is the default value. Join buffers are employed only for inner
                   joins that are executed by the original Block
                   Nested-Loop (BNL) join algorithm. When this algorithm
                   is applied, rows of the inner table are accessed

@@ -7971,16 +7965,15 @@
               </row>
               <row>
                 <entry><literal>3</literal></entry>
-                <entry>The BNL algorithm is used for an outer join and semi-join operation with
-                  one inner table, and for inner joins.</entry>
+                <entry>The BNL algorithm is used for outer join operations with one inner table
+                  and for inner joins.</entry>
               </row>
               <row>
                 <entry><literal>4</literal></entry>
-                <entry>This is the default value. The BNL algorithm uses incremental buffers
-                  for inner tables. In this case, the BNL algorithm can
-                  be used for nested outer joins and semi-joins (outer
-                  joins and semi-joins with several inner tables). Such
-                  an operation can be executed only if incremental join
+                <entry>The BNL algorithm uses incremental buffers for inner tables. In this
+                  case, the BNL algorithm can be used for nested outer
+                  joins (outer joins with several inner tables). Such an
+                  operation can be executed only if incremental join
                   buffers are used to join all inner tables but the
                   first one.</entry>
               </row>


Modified: trunk/refman-5.6/optimization.xml
===================================================================
--- trunk/refman-5.6/optimization.xml	2011-01-14 18:23:35 UTC (rev 24813)
+++ trunk/refman-5.6/optimization.xml	2011-01-14 18:58:55 UTC (rev 24814)
Changed blocks: 2, Lines Added: 272, Lines Deleted: 1; 12327 bytes

@@ -2625,7 +2625,10 @@
               <literal role="jointype">index</literal> (in other words,
               when no possible keys can be used, and a full scan is
               done, of either the data or index rows, respectively), or
-              <literal role="jointype">range</literal>.
+              <literal role="jointype">range</literal>. In MySQL
+              &current-series;, use of buffering is extended to be
+              applicable to outer joins, as described in
+              <xref linkend="bnl-bka-optimization"/>.
             </para>
           </listitem>
 

@@ -3607,6 +3610,274 @@
 
       </section>
 
+      <section id="bnl-bka-optimization">
+
+        <title>Block Nested-Loop Joins</title>
+
+        <indexterm>
+          <primary>Block Nested-Loop</primary>
+          <secondary>optimization</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>optimization</primary>
+          <secondary>Block Nested-Loop</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Batched Key Access</primary>
+          <secondary>optimization</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>optimization</primary>
+          <secondary>Batched Key Access</secondary>
+        </indexterm>
+
+        <para>
+          In MySQL &current-series;, the Block Nested-Loop (BNL) Join
+          algorithm previously used only for inner joins is extended and
+          can be employed for outer join operations, including nested
+          outer joins.
+        </para>
+
+        <para>
+          The following sections discuss the new join buffer management
+          that underlies the reimplementation of the original BNL
+          algorithm and the extended BNL algorithm, and describe how
+          these algorithms operate.
+        </para>
+
+        <section id="join-buffer-management">
+
+          <title>Join Buffer Management for the Block Nested-Loop Algorithm</title>
+
+          <para>
+            In MySQL &current-series;, MySQL Server can employ join
+            buffers to execute not only inner joins without index access
+            to the inner table, but also outer joins that appear after
+            subquery flattening. Moreover, a join buffer now can be
+            effectively used when there is an index access to the inner
+            table.
+          </para>
+
+          <para>
+            The new join buffer management code slightly more
+            efficiently utilizes join buffer space when storing the
+            values of the interesting row columns: No additional bytes
+            are allocated in buffers for a row column if its value is
+            <literal>NULL</literal>, and the minimum number of bytes is
+            allocated for any value of the
+            <literal role="type">VARCHAR</literal> type.
+          </para>
+
+          <para>
+            The new code supports two types of buffers, regular and
+            incremental. Suppose that join buffer <literal>B1</literal>
+            is employed to join tables <literal>t1</literal> and
+            <literal>t2</literal> and the result of this operation is
+            joined with table <literal>t3</literal> using join buffer
+            <literal>B2</literal>:
+          </para>
+
+          <itemizedlist>
+
+            <listitem>
+              <para>
+                A regular join buffer contains columns from each join
+                operand. If <literal>B2</literal> is a regular join
+                buffer, each row <replaceable>r</replaceable> put into
+                <literal>B2</literal> is composed of the columns of a
+                row <replaceable>r1</replaceable> from
+                <literal>B1</literal> and the interesting columns of a
+                matching row <replaceable>r2</replaceable> from table
+                <literal>t2</literal>.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                An incremental join buffer contains only columns from
+                rows of the table produced by the second join operand.
+                That is, it is incremental to a row from the first
+                operand buffer. If <literal>B2</literal> is an
+                incremental join buffer, the interesting columns of the
+                row <replaceable>r2</replaceable> are stored with a link
+                to the row <replaceable>r1</replaceable> from
+                <literal>B1</literal>.
+              </para>
+            </listitem>
+
+          </itemizedlist>
+
+          <para>
+            Incremental join buffers are always incremental relative to
+            a join buffer from an earlier join operation, so the buffer
+            from the first join operation is always a regular buffer. In
+            the example just given, the buffer <literal>B1</literal>
+            used to join tables <literal>t1</literal> and
+            <literal>t2</literal> must be a regular buffer.
+          </para>
+
+          <para>
+            Each row of the incremental buffer used for a join operation
+            contains only the interesting columns of a row from the
+            table to be joined. These columns are augmented with a
+            reference to the interesting columns of the matched row from
+            the table produced by the first join operand. Several rows
+            in the incremental buffer can refer to the same row
+            <replaceable>r</replaceable> whose columns are stored in the
+            previous join buffers insofar as all these rows match row
+            <replaceable>r</replaceable>.
+          </para>
+
+          <para>
+            Incremental buffers enable less frequent copying of columns
+            from buffers used for previous join operations. This
+            provides a savings in buffer space because in the general
+            case a row produced by the first join operand can be matched
+            by several rows produced by the second join operand. It is
+            unnecessary to make several copies of a row from the first
+            operand. Incremental buffers also provide a savings in
+            processing time due to the reduction in copying time.
+          </para>
+
+          <para>
+            The
+            <literal role="sysvar">optimizer_join_cache_level</literal>
+            system variable is used for join buffer managment. It
+            controls how join buffers are used for join operations. The
+            following table shows the permissible
+            <literal role="sysvar">optimizer_join_cache_level</literal>
+            values.
+          </para>
+
+          <informaltable>
+            <tgroup cols="2">
+              <colspec colwidth="15*"/>
+              <colspec colwidth="70*"/>
+              <thead>
+                <row>
+                  <entry>Option</entry>
+                  <entry>Description</entry>
+                </row>
+              </thead>
+              <tbody>
+                <row>
+                  <entry><literal>0</literal></entry>
+                  <entry>No join buffer is used for any join operation. This setting can be
+                    useful for assessing baseline join performance in
+                    comparison to performance with nonzero values that
+                    enable use of join buffering.</entry>
+                </row>
+                <row>
+                  <entry><literal>1</literal></entry>
+                  <entry>This is the default value. Join buffers are employed only for inner
+                    joins that are executed by the original Block
+                    Nested-Loop (BNL) join algorithm. When this
+                    algorithm is applied, rows of the inner table are
+                    accessed through a table scan, a plain index scan,
+                    or a range index scan.</entry>
+                </row>
+                <row>
+                  <entry><literal>2</literal></entry>
+                  <entry>The server employs an incremental join buffer for a join operation if
+                    its first operand is produced by a join operation
+                    that uses a join buffer itself.</entry>
+                </row>
+                <row>
+                  <entry><literal>3</literal></entry>
+                  <entry>The BNL algorithm is used for outer join operations with one inner table
+                    and for inner joins.</entry>
+                </row>
+                <row>
+                  <entry><literal>4</literal></entry>
+                  <entry>The BNL algorithm uses incremental buffers for inner tables. In this
+                    case, the BNL algorithm can be used for nested outer
+                    joins (outer joins with several inner tables). Such
+                    an operation can be executed only if incremental
+                    join buffers are used to join all inner tables but
+                    the first one.</entry>
+                </row>
+              </tbody>
+            </tgroup>
+          </informaltable>
+
+        </section>
+
+        <section id="bnl-optimization">
+
+          <title>Block Nested-Loop Algorithm for Outer Joins and Semi-Joins</title>
+
+          <indexterm>
+            <primary>Block Nested-Loop</primary>
+            <secondary>optimization</secondary>
+          </indexterm>
+
+          <indexterm>
+            <primary>optimization</primary>
+            <secondary>Block Nested-Loop</secondary>
+          </indexterm>
+
+          <para>
+            In MySQL &current-series;, the original implementation of
+            the BNL algorithm is extended to support outer join
+            operations.
+          </para>
+
+          <para>
+            When these operations are executed with a join buffer, each
+            row put into the buffer is supplied with a match flag.
+          </para>
+
+          <para>
+            If an outer join operation is executed using a join buffer,
+            each row of the table produced by the second operand is
+            checked for a match against each row in the join buffer.
+            When a match is found, a new extended row is formed (the
+            original row plus columns from the second operand) and sent
+            for further extensions by the remaining join operations. In
+            addition, the match flag of the matched row in the buffer is
+            enabled. After all rows of the table to be joined have been
+            examined, the join buffer is scanned. Each row from the
+            buffer that does not have its match flag enabled is extended
+            by <literal>NULL</literal> complements
+            (<literal>NULL</literal> values for each column in the
+            second operand) and sent for further extensions by the
+            remaining join operations.
+          </para>
+
+          <para>
+            If the value of
+            <literal role="sysvar">optimizer_join_cache_level</literal>
+            is 3, the BNL algorithm is used for outer join operations
+            with one inner table.
+          </para>
+
+          <para>
+            If the value of
+            <literal role="sysvar">optimizer_join_cache_level</literal>
+            is 4, the BNL algorithm uses incremental buffers for inner
+            tables. In this case, the BNL algorithm can be used for
+            nested outer joins (outer joins with several inner tables).
+            Such operations can be executed only if incremental join
+            buffers are used to join all inner tables but the first one.
+          </para>
+
+          <para>
+            In <literal role="stmt">EXPLAIN</literal> output, use of BNL
+            for a table is signified when the <literal>Extra</literal>
+            value contains <literal>Using join buffer</literal> and the
+            <literal>type</literal> value is
+            <literal role="jointype">ALL</literal>,
+            <literal role="jointype">index</literal>, or
+            <literal role="jointype">range</literal>.
+          </para>
+
+        </section>
+
+      </section>
+
       <section id="order-by-optimization">
 
         <title><literal>ORDER BY</literal> Optimization</title>


Modified: trunk/refman-6.0/optimization.xml
===================================================================
--- trunk/refman-6.0/optimization.xml	2011-01-14 18:23:35 UTC (rev 24813)
+++ trunk/refman-6.0/optimization.xml	2011-01-14 18:58:55 UTC (rev 24814)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 831 bytes

@@ -5540,8 +5540,8 @@
           algorithm supports inner join, outer join and semi-join
           operations, including nested outer joins and nested
           semi-joins. Also, the Block Nested-Loop (BNL) Join algorithm
-          previously used only for inner joins has been extended and can
-          be employed for outer join and semi-join operations, including
+          previously used only for inner joins is extended and can be
+          employed for outer join and semi-join operations, including
           nested outer joins and nested semi-joins.
         </para>
 


Thread
svn commit - mysqldoc@docsrva: r24814 - in trunk: . dynamic-docs/changelog refman-5.6 refman-6.0paul.dubois14 Jan