Author: paul
Date: 2007-03-13 19:00:35 +0100 (Tue, 13 Mar 2007)
New Revision: 5337
Log:
r21459@polar: paul | 2007-03-13 12:58:10 -0500
Add a separate section for external locking. Up to now we have had
only bits of pieces of external locking scattered around the manual.
Modified:
trunk/refman-4.1/client-utility-programs.xml
trunk/refman-4.1/database-administration.xml
trunk/refman-4.1/optimization.xml
trunk/refman-5.0/client-utility-programs.xml
trunk/refman-5.0/database-administration.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.1/client-utility-programs.xml
trunk/refman-5.1/database-administration.xml
trunk/refman-5.1/optimization.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:21458
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:17569
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:14593
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:21459
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:17569
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:14593
Modified: trunk/refman-4.1/client-utility-programs.xml
===================================================================
--- trunk/refman-4.1/client-utility-programs.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-4.1/client-utility-programs.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 7; 1559 bytes
@@ -798,9 +798,9 @@
specify a table by naming its index file (the file with the
<filename>.MYI</filename> suffix). If you do not invoke
<command>myisam_ftdump</command> in the directory where the
- table files are located, the table or index file name
- must be preceded by the pathname to the table's database
- directory. Index numbers begin with 0.
+ table files are located, the table or index file name must be
+ preceded by the pathname to the table's database directory.
+ Index numbers begin with 0.
</para>
<para>
@@ -1328,10 +1328,9 @@
<para>
Instead of terminating with an error if the table is
locked, wait until the table is unlocked before
- continuing. Note that if you are running
- <command>mysqld</command> with external locking disabled,
- the table can be locked only by another
- <command>myisamchk</command> command.
+ continuing. If you are running <command>mysqld</command>
+ with external locking disabled, the table can be locked
+ only by another <command>myisamchk</command> command.
</para>
</listitem>
Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-4.1/database-administration.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 4, Lines Added: 19, Lines Deleted: 46; 3846 bytes
@@ -1158,40 +1158,10 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If you use this
- option to enable updates to <literal>MyISAM</literal>
- tables from many MySQL processes, you must ensure that the
- following conditions are satisfied:
+ For more information about external locking, including
+ conditions under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- You should not use the query cache for queries that
- use tables that are updated by another process.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You should not use
- <option>--delay-key-write=ALL</option> or
- <literal>DELAY_KEY_WRITE=1</literal> on any shared
- tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The easiest way to ensure this is to always use
- <option>--external-locking</option> together with
- <option>--delay-key-write=OFF</option> and
- <option>--query-cache-size=0</option>. (This is not done
- by default because in many setups it is useful to have a
- mixture of the preceding options.)
- </para>
</listitem>
<listitem>
@@ -2251,15 +2221,10 @@
</para>
<para>
- Do not use external locking (system locking). With
- external locking disabled, you must shut down the server
- to use <command>myisamchk</command> or
- <command>isamchk</command>. See
- <xref linkend="system-optimization"/>. As of MySQL 3.23,
- you can use the <literal>CHECK TABLE</literal> and
- <literal>REPAIR TABLE</literal> statements to check and
- repair <literal>MyISAM</literal> tables. This option
- previously was named <option>--skip-locking</option>.
+ Do not use external locking (system locking). For more
+ information about external locking, including conditions
+ under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
<para>
@@ -3648,10 +3613,10 @@
</para>
<para>
- Note that enabling external locking with
- <option>--external-locking</option> offers no protection
- against index corruption for tables that use delayed key
- writes.
+ Note that if you enable external locking with
+ <option>--external-locking</option>, there is no
+ protection against index corruption for tables that use
+ delayed key writes.
</para>
<para>
@@ -21017,6 +20982,14 @@
</itemizedlist>
+ <para>
+ The <replaceable>tbl_name</replaceable> argument can be either
+ the name of a <literal>MyISAM</literal> table or the name of
+ its index file, as described in <xref linkend="myisamchk"/>.
+ Multiple <replaceable>tbl_name</replaceable> arguments can be
+ given.
+ </para>
+
<indexterm>
<primary>examples</primary>
<secondary><command>myisamchk</command> output</secondary>
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-4.1/optimization.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 3, Lines Added: 184, Lines Deleted: 1; 7870 bytes
@@ -4529,15 +4529,48 @@
<title>Locking Issues</title>
+ <para>
+ MySQL manages contention for table contents using locking:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Internal locking; is performed within the MySQL server itself
+ to manage contention for table contents by multiple threads.
+ This type of locking is internal because it is performed
+ entirely by the server and involves no other programs. See
+ <xref linkend="internal-locking"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ External locking occurs when the server and other programs
+ lock table files to coordinate among themselves which program
+ can access the tables at which time. See
+ <xref linkend="external-locking"/>. See
+ <xref linkend="external-locking"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
<section id="internal-locking">
- <title>Locking Methods</title>
+ <title>Internal Locking Methods</title>
<indexterm>
<primary>internal locking</primary>
</indexterm>
<indexterm>
+ <primary>locking</primary>
+ <secondary>internal</secondary>
+ </indexterm>
+
+ <indexterm>
<primary>locking methods</primary>
</indexterm>
@@ -4574,6 +4607,17 @@
</indexterm>
<para>
+ This section discusses internal locking; that is, locking
+ performed within the MySQL server itself to manage contention
+ for table contents by multiple threads. This type of locking is
+ internal because it is performed entirely by the server and
+ involves no other programs. External locking occurs when the
+ server and other programs lock table files to coordinate among
+ themselves which program can access the tables at which time.
+ See <xref linkend="external-locking"/>.
+ </para>
+
+ <para>
MySQL uses table-level locking for <literal>ISAM</literal>,
<literal>MyISAM</literal>, and <literal>MEMORY</literal>
(<literal>HEAP</literal>) tables, page-level locking for
@@ -5207,6 +5251,145 @@
</section>
+ <section id="external-locking">
+
+ <title>External Locking</title>
+
+ <indexterm>
+ <primary>external locking</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>locking</primary>
+ <secondary>external</secondary>
+ </indexterm>
+
+ <para>
+ External locking is the use of filesystem locking to manage
+ contention for database tables by multiple processes. External
+ locking is used in situations where a single process such as the
+ MySQL server cannot be assumed to be the only process that
+ requires access to tables. Here are some examples:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you run multiple servers that use the same database
+ directory (not recommended), each server must have external
+ locking enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you use <command>myisamchk</command> to perform table
+ maintenance operations on <literal>MyISAM</literal> tables,
+ you must either ensure that the server is not running, or
+ that the server has external locking enabled so that it
+ locks table files as necessary to coordinate with
+ <command>myisamchk</command> for access to the tables. The
+ same is true for use of <command>myisampack</command> to
+ pack <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ With external locking in effect, each process that requires
+ access to a table acquires a filesystem lock for the table files
+ before proceeding to access the table. If all necessary locks
+ cannot be acquired, the process is blocked from accessing the
+ table until the locks can be obtained (after the process that
+ currently holds the locks releases them).
+ </para>
+
+ <para>
+ External locking affects server performance because the server
+ must sometimes wait for other processes before it can access
+ tables.
+ </para>
+
+ <para>
+ External locking is unnecessary if you run a single server to
+ access a given data directory (which is the usual case) and if
+ no other programs such as <command>myisamchk</command> need to
+ modify tables while the server is running. If you only
+ <emphasis>read</emphasis> tables with other programs, external
+ locking is not required, although <command>myisamchk</command>
+ might report warnings if the server changes tables while
+ <command>myisamchk</command> is reading them.
+ </para>
+
+ <para>
+ With external locking disabled, to use
+ <command>myisamchk</command>, you must either stop the server
+ while <command>myisamchk</command> executes or else lock and
+ flush the tables before running <command>myisamchk</command>.
+ (See <xref linkend="system-optimization"/>.) To avoid this
+ requirement as of MySQL 3.23, use the <literal>CHECK
+ TABLE</literal> and <literal>REPAIR TABLE</literal> statements
+ to check and repair <literal>MyISAM</literal> tables.
+ </para>
+
+ <para>
+ For <command>mysqld</command>, external locking is controlled by
+ the value of the <literal>skip_external_locking</literal> system
+ variable. (Before MySQL 4.0.3, this variable is named
+ <literal>skip_locking</literal>.) When this variable is enabled,
+ external locking is disabled, and vice versa. From MySQL 4.0 on,
+ external locking is disabled by default. Before MySQL 4.0,
+ external locking is enabled by default on Linux or when MySQL is
+ configured to use MIT-pthreads.
+ </para>
+
+ <para>
+ Use of external locking can be controlled at server startup by
+ using the <option>--external-locking</option> or
+ <option>--skip-external-locking</option> option. (Before MySQL
+ 4.0.3, these options are named <option>--enable-locking</option>
+ and <literal>--skip-locking</literal>.)
+ </para>
+
+ <para>
+ If you do use external locking option to enable updates to
+ <literal>MyISAM</literal> tables from many MySQL processes, you
+ must ensure that the following conditions are satisfied:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You should not use the query cache for queries that use
+ tables that are updated by another process.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You should not start the server with the
+ <option>--delay-key-write=ALL</option> option or use the
+ <literal>DELAY_KEY_WRITE=1</literal> table option for any
+ shared tables. Otherise, index corruption can occur.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The easiest way to satisfy these conditions is to always use
+ <option>--external-locking</option> together with
+ <option>--delay-key-write=OFF</option> and
+ <option>--query-cache-size=0</option>. (This is not done by
+ default because in many setups it is useful to have a mixture of
+ the preceding options.)
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
Modified: trunk/refman-5.0/client-utility-programs.xml
===================================================================
--- trunk/refman-5.0/client-utility-programs.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.0/client-utility-programs.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 7; 1559 bytes
@@ -784,9 +784,9 @@
specify a table by naming its index file (the file with the
<filename>.MYI</filename> suffix). If you do not invoke
<command>myisam_ftdump</command> in the directory where the
- table files are located, the table or index file name
- must be preceded by the pathname to the table's database
- directory. Index numbers begin with 0.
+ table files are located, the table or index file name must be
+ preceded by the pathname to the table's database directory.
+ Index numbers begin with 0.
</para>
<para>
@@ -1292,10 +1292,9 @@
<para>
Instead of terminating with an error if the table is
locked, wait until the table is unlocked before
- continuing. Note that if you are running
- <command>mysqld</command> with external locking disabled,
- the table can be locked only by another
- <command>myisamchk</command> command.
+ continuing. If you are running <command>mysqld</command>
+ with external locking disabled, the table can be locked
+ only by another <command>myisamchk</command> command.
</para>
</listitem>
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.0/database-administration.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 4, Lines Added: 19, Lines Deleted: 44; 3728 bytes
@@ -1249,40 +1249,10 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If you use this
- option to enable updates to <literal>MyISAM</literal>
- tables from many MySQL processes, you must ensure that the
- following conditions are satisfied:
+ For more information about external locking, including
+ conditions under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- You should not use the query cache for queries that
- use tables that are updated by another process.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You should not use
- <option>--delay-key-write=ALL</option> or
- <literal>DELAY_KEY_WRITE=1</literal> on any shared
- tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The easiest way to ensure this is to always use
- <option>--external-locking</option> together with
- <option>--delay-key-write=OFF</option> and
- <option>--query-cache-size=0</option>. (This is not done
- by default because in many setups it is useful to have a
- mixture of the preceding options.)
- </para>
</listitem>
<listitem>
@@ -2384,13 +2354,10 @@
</para>
<para>
- Do not use external locking (system locking). With
- external locking disabled, you must shut down the server
- to use <command>myisamchk</command>. (See
- <xref linkend="system-optimization"/>.) To avoid this
- requirement, use the <literal>CHECK TABLE</literal> and
- <literal>REPAIR TABLE</literal> statements to check and
- repair <literal>MyISAM</literal> tables.
+ Do not use external locking (system locking). For more
+ information about external locking, including conditions
+ under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
<para>
@@ -4096,10 +4063,10 @@
</para>
<para>
- Note that enabling external locking with
- <option>--external-locking</option> offers no protection
- against index corruption for tables that use delayed key
- writes.
+ Note that if you enable external locking with
+ <option>--external-locking</option>, there is no
+ protection against index corruption for tables that use
+ delayed key writes.
</para>
</listitem>
@@ -24961,6 +24928,14 @@
</itemizedlist>
+ <para>
+ The <replaceable>tbl_name</replaceable> argument can be either
+ the name of a <literal>MyISAM</literal> table or the name of
+ its index file, as described in <xref linkend="myisamchk"/>.
+ Multiple <replaceable>tbl_name</replaceable> arguments can be
+ given.
+ </para>
+
<indexterm>
<primary>examples</primary>
<secondary><command>myisamchk</command> output</secondary>
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.0/optimization.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 3, Lines Added: 184, Lines Deleted: 1; 7858 bytes
@@ -6569,15 +6569,48 @@
<title>Locking Issues</title>
+ <para>
+ MySQL manages contention for table contents using locking:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Internal locking; is performed within the MySQL server itself
+ to manage contention for table contents by multiple threads.
+ This type of locking is internal because it is performed
+ entirely by the server and involves no other programs. See
+ <xref linkend="internal-locking"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ External locking occurs when the server and other programs
+ lock table files to coordinate among themselves which program
+ can access the tables at which time. See
+ <xref linkend="external-locking"/>. See
+ <xref linkend="external-locking"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
<section id="internal-locking">
- <title>Locking Methods</title>
+ <title>Internal Locking Methods</title>
<indexterm>
<primary>internal locking</primary>
</indexterm>
<indexterm>
+ <primary>locking</primary>
+ <secondary>internal</secondary>
+ </indexterm>
+
+ <indexterm>
<primary>locking methods</primary>
</indexterm>
@@ -6614,6 +6647,17 @@
</indexterm>
<para>
+ This section discusses internal locking; that is, locking
+ performed within the MySQL server itself to manage contention
+ for table contents by multiple threads. This type of locking is
+ internal because it is performed entirely by the server and
+ involves no other programs. External locking occurs when the
+ server and other programs lock table files to coordinate among
+ themselves which program can access the tables at which time.
+ See <xref linkend="external-locking"/>.
+ </para>
+
+ <para>
MySQL uses table-level locking for <literal>MyISAM</literal> and
<literal>MEMORY</literal> tables, page-level locking for
<literal>BDB</literal> tables, and row-level locking for
@@ -7254,6 +7298,145 @@
</section>
+ <section id="external-locking">
+
+ <title>External Locking</title>
+
+ <indexterm>
+ <primary>external locking</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>locking</primary>
+ <secondary>external</secondary>
+ </indexterm>
+
+ <para>
+ External locking is the use of filesystem locking to manage
+ contention for database tables by multiple processes. External
+ locking is used in situations where a single process such as the
+ MySQL server cannot be assumed to be the only process that
+ requires access to tables. Here are some examples:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you run multiple servers that use the same database
+ directory (not recommended), each server must have external
+ locking enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you use <command>myisamchk</command> to perform table
+ maintenance operations on <literal>MyISAM</literal> tables,
+ you must either ensure that the server is not running, or
+ that the server has external locking enabled so that it
+ locks table files as necessary to coordinate with
+ <command>myisamchk</command> for access to the tables. The
+ same is true for use of <command>myisampack</command> to
+ pack <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ With external locking in effect, each process that requires
+ access to a table acquires a filesystem lock for the table files
+ before proceeding to access the table. If all necessary locks
+ cannot be acquired, the process is blocked from accessing the
+ table until the locks can be obtained (after the process that
+ currently holds the locks releases them).
+ </para>
+
+ <para>
+ External locking affects server performance because the server
+ must sometimes wait for other processes before it can access
+ tables.
+ </para>
+
+ <para>
+ External locking is unnecessary if you run a single server to
+ access a given data directory (which is the usual case) and if
+ no other programs such as <command>myisamchk</command> need to
+ modify tables while the server is running. If you only
+ <emphasis>read</emphasis> tables with other programs, external
+ locking is not required, although <command>myisamchk</command>
+ might report warnings if the server changes tables while
+ <command>myisamchk</command> is reading them.
+ </para>
+
+ <para>
+ With external locking disabled, to use
+ <command>myisamchk</command>, you must either stop the server
+ while <command>myisamchk</command> executes or else lock and
+ flush the tables before running <command>myisamchk</command>.
+ (See <xref linkend="system-optimization"/>.) To avoid this
+ requirement, use the <literal>CHECK TABLE</literal> and
+ <literal>REPAIR TABLE</literal> statements to check and repair
+ <literal>MyISAM</literal> tables.
+ </para>
+
+ <para>
+ For <command>mysqld</command>, external locking is controlled by
+ the value of the <literal>skip_external_locking</literal> system
+ variable. (Before MySQL 4.0.3, this variable is named
+ <literal>skip_locking</literal>.) When this variable is enabled,
+ external locking is disabled, and vice versa. From MySQL 4.0 on,
+ external locking is disabled by default. Before MySQL 4.0,
+ external locking is enabled by default on Linux or when MySQL is
+ configured to use MIT-pthreads.
+ </para>
+
+ <para>
+ Use of external locking can be controlled at server startup by
+ using the <option>--external-locking</option> or
+ <option>--skip-external-locking</option> option. (Before MySQL
+ 4.0.3, these options are named <option>--enable-locking</option>
+ and <literal>--skip-locking</literal>.)
+ </para>
+
+ <para>
+ If you do use external locking option to enable updates to
+ <literal>MyISAM</literal> tables from many MySQL processes, you
+ must ensure that the following conditions are satisfied:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You should not use the query cache for queries that use
+ tables that are updated by another process.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You should not start the server with the
+ <option>--delay-key-write=ALL</option> option or use the
+ <literal>DELAY_KEY_WRITE=1</literal> table option for any
+ shared tables. Otherise, index corruption can occur.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The easiest way to satisfy these conditions is to always use
+ <option>--external-locking</option> together with
+ <option>--delay-key-write=OFF</option> and
+ <option>--query-cache-size=0</option>. (This is not done by
+ default because in many setups it is useful to have a mixture of
+ the preceding options.)
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
Modified: trunk/refman-5.1/client-utility-programs.xml
===================================================================
--- trunk/refman-5.1/client-utility-programs.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.1/client-utility-programs.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 7; 1559 bytes
@@ -784,9 +784,9 @@
specify a table by naming its index file (the file with the
<filename>.MYI</filename> suffix). If you do not invoke
<command>myisam_ftdump</command> in the directory where the
- table files are located, the table or index file name
- must be preceded by the pathname to the table's database
- directory. Index numbers begin with 0.
+ table files are located, the table or index file name must be
+ preceded by the pathname to the table's database directory.
+ Index numbers begin with 0.
</para>
<para>
@@ -1292,10 +1292,9 @@
<para>
Instead of terminating with an error if the table is
locked, wait until the table is unlocked before
- continuing. Note that if you are running
- <command>mysqld</command> with external locking disabled,
- the table can be locked only by another
- <command>myisamchk</command> command.
+ continuing. If you are running <command>mysqld</command>
+ with external locking disabled, the table can be locked
+ only by another <command>myisamchk</command> command.
</para>
</listitem>
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.1/database-administration.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 4, Lines Added: 19, Lines Deleted: 44; 3728 bytes
@@ -1306,40 +1306,10 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If you use this
- option to enable updates to <literal>MyISAM</literal>
- tables from many MySQL processes, you must ensure that the
- following conditions are satisfied:
+ For more information about external locking, including
+ conditions under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- You should not use the query cache for queries that
- use tables that are updated by another process.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You should not use
- <option>--delay-key-write=ALL</option> or
- <literal>DELAY_KEY_WRITE=1</literal> on any shared
- tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The easiest way to ensure this is to always use
- <option>--external-locking</option> together with
- <option>--delay-key-write=OFF</option> and
- <option>--query-cache-size=0</option>. (This is not done
- by default because in many setups it is useful to have a
- mixture of the preceding options.)
- </para>
</listitem>
<listitem>
@@ -2450,13 +2420,10 @@
</para>
<para>
- Do not use external locking (system locking). With
- external locking disabled, you must shut down the server
- to use <command>myisamchk</command>. (See
- <xref linkend="system-optimization"/>.) To avoid this
- requirement, use the <literal>CHECK TABLE</literal> and
- <literal>REPAIR TABLE</literal> statements to check and
- repair <literal>MyISAM</literal> tables.
+ Do not use external locking (system locking). For more
+ information about external locking, including conditions
+ under which it can and cannot be used, see
+ <xref linkend="external-locking"/>.
</para>
<para>
@@ -4140,10 +4107,10 @@
</para>
<para>
- Note that enabling external locking with
- <option>--external-locking</option> offers no protection
- against index corruption for tables that use delayed key
- writes.
+ Note that if you enable external locking with
+ <option>--external-locking</option>, there is no
+ protection against index corruption for tables that use
+ delayed key writes.
</para>
</listitem>
@@ -25389,6 +25356,14 @@
</itemizedlist>
+ <para>
+ The <replaceable>tbl_name</replaceable> argument can be either
+ the name of a <literal>MyISAM</literal> table or the name of
+ its index file, as described in <xref linkend="myisamchk"/>.
+ Multiple <replaceable>tbl_name</replaceable> arguments can be
+ given.
+ </para>
+
<indexterm>
<primary>examples</primary>
<secondary><command>myisamchk</command> output</secondary>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2007-03-13 17:59:59 UTC (rev 5336)
+++ trunk/refman-5.1/optimization.xml 2007-03-13 18:00:35 UTC (rev 5337)
Changed blocks: 3, Lines Added: 184, Lines Deleted: 1; 7838 bytes
@@ -6599,15 +6599,48 @@
<title>Locking Issues</title>
+ <para>
+ MySQL manages contention for table contents using locking:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Internal locking; is performed within the MySQL server itself
+ to manage contention for table contents by multiple threads.
+ This type of locking is internal because it is performed
+ entirely by the server and involves no other programs. See
+ <xref linkend="internal-locking"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ External locking occurs when the server and other programs
+ lock table files to coordinate among themselves which program
+ can access the tables at which time. See
+ <xref linkend="external-locking"/>. See
+ <xref linkend="external-locking"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
<section id="internal-locking">
- <title>Locking Methods</title>
+ <title>Internal Locking Methods</title>
<indexterm>
<primary>internal locking</primary>
</indexterm>
<indexterm>
+ <primary>locking</primary>
+ <secondary>internal</secondary>
+ </indexterm>
+
+ <indexterm>
<primary>locking methods</primary>
</indexterm>
@@ -6635,6 +6668,17 @@
</indexterm>
<para>
+ This section discusses internal locking; that is, locking
+ performed within the MySQL server itself to manage contention
+ for table contents by multiple threads. This type of locking is
+ internal because it is performed entirely by the server and
+ involves no other programs. External locking occurs when the
+ server and other programs lock table files to coordinate among
+ themselves which program can access the tables at which time.
+ See <xref linkend="external-locking"/>.
+ </para>
+
+ <para>
MySQL uses table-level locking for <literal>MyISAM</literal> and
<literal>MEMORY</literal> tables, and row-level locking for
<literal>InnoDB</literal> tables.
@@ -7269,6 +7313,145 @@
</section>
+ <section id="external-locking">
+
+ <title>External Locking</title>
+
+ <indexterm>
+ <primary>external locking</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>locking</primary>
+ <secondary>external</secondary>
+ </indexterm>
+
+ <para>
+ External locking is the use of filesystem locking to manage
+ contention for database tables by multiple processes. External
+ locking is used in situations where a single process such as the
+ MySQL server cannot be assumed to be the only process that
+ requires access to tables. Here are some examples:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you run multiple servers that use the same database
+ directory (not recommended), each server must have external
+ locking enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you use <command>myisamchk</command> to perform table
+ maintenance operations on <literal>MyISAM</literal> tables,
+ you must either ensure that the server is not running, or
+ that the server has external locking enabled so that it
+ locks table files as necessary to coordinate with
+ <command>myisamchk</command> for access to the tables. The
+ same is true for use of <command>myisampack</command> to
+ pack <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ With external locking in effect, each process that requires
+ access to a table acquires a filesystem lock for the table files
+ before proceeding to access the table. If all necessary locks
+ cannot be acquired, the process is blocked from accessing the
+ table until the locks can be obtained (after the process that
+ currently holds the locks releases them).
+ </para>
+
+ <para>
+ External locking affects server performance because the server
+ must sometimes wait for other processes before it can access
+ tables.
+ </para>
+
+ <para>
+ External locking is unnecessary if you run a single server to
+ access a given data directory (which is the usual case) and if
+ no other programs such as <command>myisamchk</command> need to
+ modify tables while the server is running. If you only
+ <emphasis>read</emphasis> tables with other programs, external
+ locking is not required, although <command>myisamchk</command>
+ might report warnings if the server changes tables while
+ <command>myisamchk</command> is reading them.
+ </para>
+
+ <para>
+ With external locking disabled, to use
+ <command>myisamchk</command>, you must either stop the server
+ while <command>myisamchk</command> executes or else lock and
+ flush the tables before running <command>myisamchk</command>.
+ (See <xref linkend="system-optimization"/>.) To avoid this
+ requirement, use the <literal>CHECK TABLE</literal> and
+ <literal>REPAIR TABLE</literal> statements to check and repair
+ <literal>MyISAM</literal> tables.
+ </para>
+
+ <para>
+ For <command>mysqld</command>, external locking is controlled by
+ the value of the <literal>skip_external_locking</literal> system
+ variable. (Before MySQL 4.0.3, this variable is named
+ <literal>skip_locking</literal>.) When this variable is enabled,
+ external locking is disabled, and vice versa. From MySQL 4.0 on,
+ external locking is disabled by default. Before MySQL 4.0,
+ external locking is enabled by default on Linux or when MySQL is
+ configured to use MIT-pthreads.
+ </para>
+
+ <para>
+ Use of external locking can be controlled at server startup by
+ using the <option>--external-locking</option> or
+ <option>--skip-external-locking</option> option. (Before MySQL
+ 4.0.3, these options are named <option>--enable-locking</option>
+ and <literal>--skip-locking</literal>.)
+ </para>
+
+ <para>
+ If you do use external locking option to enable updates to
+ <literal>MyISAM</literal> tables from many MySQL processes, you
+ must ensure that the following conditions are satisfied:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You should not use the query cache for queries that use
+ tables that are updated by another process.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You should not start the server with the
+ <option>--delay-key-write=ALL</option> option or use the
+ <literal>DELAY_KEY_WRITE=1</literal> table option for any
+ shared tables. Otherise, index corruption can occur.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The easiest way to satisfy these conditions is to always use
+ <option>--external-locking</option> together with
+ <option>--delay-key-write=OFF</option> and
+ <option>--query-cache-size=0</option>. (This is not done by
+ default because in many setups it is useful to have a mixture of
+ the preceding options.)
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r5337 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 13 Mar |
