Author: paul
Date: 2006-01-09 17:06:44 +0100 (Mon, 09 Jan 2006)
New Revision: 740
Log:
r5991@frost: paul | 2006-01-09 09:02:33 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/innodb.xml
trunk/refman-5.0/innodb.xml
trunk/refman-5.1/innodb.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:5990
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:5991
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
Modified: trunk/refman-4.1/innodb.xml
===================================================================
--- trunk/refman-4.1/innodb.xml 2006-01-09 16:02:35 UTC (rev 739)
+++ trunk/refman-4.1/innodb.xml 2006-01-09 16:06:44 UTC (rev 740)
@@ -1015,9 +1015,9 @@
<para>
Starting from MySQL 3.23.41, you can use raw disk partitions as
- tablespace data files. By using a raw disk, you can perform
- non-buffered I/O on Windows and on some Unix systems without
- filesystem overhead, which might improve performance.
+ data files in the shared tablespace. By using a raw disk, you
+ can perform non-buffered I/O on Windows and on some Unix systems
+ without filesystem overhead, which might improve performance.
</para>
<para>
@@ -1026,7 +1026,7 @@
in <literal>innodb_data_file_path</literal>. The partition must
be at least as large as the size that you specify. Note that 1MB
in <literal>InnoDB</literal> is 1024 × 1024 bytes, whereas
- 1MB usually means 1,000,000 bytes in disk specifications.
+ 1MB in disk specifications usually means 1,000,000 bytes.
</para>
<programlisting>
@@ -1041,10 +1041,10 @@
the new partition. However, do not create or change any
<literal>InnoDB</literal> tables yet. Otherwise, when you next
restart the server, <literal>InnoDB</literal> reinitializes the
- partition and your changes are lost. (Starting from 3.23.44, as
- a safety measure <literal>InnoDB</literal> prevents users from
- modifying data when any partition with <literal>newraw</literal>
- is specified.)
+ partition and your changes are lost. (Starting from MySQL
+ 3.23.44, as a safety measure <literal>InnoDB</literal> prevents
+ users from modifying data when any partition with
+ <literal>newraw</literal> is specified.)
</para>
<para>
@@ -1949,7 +1949,7 @@
<para>
The SQL statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
- that consists of the data files you specified in
+ that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
@@ -5581,9 +5581,9 @@
<para>
If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes.
- <literal>InnoDB</literal> has an automatic mechanism that
- monitors index searches made to the indexes defined for a table.
- If <literal>InnoDB</literal> notices that queries could benefit
+ <literal>InnoDB</literal> has a mechanism that monitors index
+ searches made to the indexes defined for a table. If
+ <literal>InnoDB</literal> notices that queries could benefit
from building a hash index, it does so automatically.
</para>
@@ -5602,7 +5602,7 @@
<para>
In a sense, <literal>InnoDB</literal> tailors itself through the
adaptive hash index mechanism to ample main memory, coming
- closer to the architecture of main memory databases.
+ closer to the architecture of main-memory databases.
</para>
</section>
@@ -5620,9 +5620,9 @@
<listitem>
<para>
- Each index record in <literal>InnoDB</literal> contains a
- header of six bytes. The header is used to link consecutive
- records together, and also in row-level locking.
+ Each index record contains a six-byte header. The header is
+ used to link together consecutive records, and also in
+ row-level locking.
</para>
</listitem>
@@ -5682,10 +5682,10 @@
column. In a fixed-length column, it reserves the fixed
length of the column in the data part of the record. The
motivation behind reserving the fixed space for
- <literal>NULL</literal> values is that then an update of the
- column from <literal>NULL</literal> to a
- non-<literal>NULL</literal> value can be done in place and
- does not cause fragmentation of the index page.
+ <literal>NULL</literal> values is that it enables an update
+ of the column from <literal>NULL</literal> to a
+ non-<literal>NULL</literal> value to be done in place
+ without causing fragmentation of the index page.
</para>
</listitem>
@@ -5763,13 +5763,13 @@
<title>&title-innodb-file-space;</title>
<para>
- The data files you define in the configuration file form the
- tablespace of <literal>InnoDB</literal>. The files are simply
- concatenated to form the tablespace. There is no striping in
- use. Currently you cannot define where in the tablespace your
- tables are allocated. However, in a newly created tablespace,
- <literal>InnoDB</literal> allocates space starting from the
- first data file.
+ The data files that you define in the configuration file form
+ the tablespace of <literal>InnoDB</literal>. The files are
+ simply concatenated to form the tablespace. There is no striping
+ in use. Currently you cannot define where within the tablespace
+ your tables are allocated. However, in a newly created
+ tablespace, <literal>InnoDB</literal> allocates space starting
+ from the first data file.
</para>
<para>
@@ -5789,11 +5789,6 @@
the data.
</para>
- <remark role="todo">
- [js] What is meant by "extents" here? Find out the right word
- and replace.
- </remark>
-
<para>
When a segment grows inside the tablespace,
<literal>InnoDB</literal> allocates the first 32 pages to it
@@ -5812,7 +5807,7 @@
<para>
When you ask for available free space in the tablespace by
- issuing a <literal>SHOW TABLE STATUS</literal>,
+ issuing a <literal>SHOW TABLE STATUS</literal> statement,
<literal>InnoDB</literal> reports the extents that are
definitely free in the tablespace. <literal>InnoDB</literal>
always reserves some extents for cleanup and other internal
@@ -5822,14 +5817,15 @@
<para>
When you delete data from a table, <literal>InnoDB</literal>
- contracts the corresponding B-tree indexes. It depends on the
- pattern of deletes whether that frees individual pages or
- extents to the tablespace, so that the freed space becomes
- available for other users. Dropping a table or deleting all rows
- from it is guaranteed to release the space to other users, but
- remember that deleted rows are physically removed only in an
- (automatic) purge operation after they are no longer needed for
- transaction rollbacks or consistent reads.
+ contracts the corresponding B-tree indexes. Whether the freed
+ space becomes available for other users depends on whether the
+ pattern of deletes frees individual pages or extents to the
+ tablespace. Dropping a table or deleting all rows from it is
+ guaranteed to release the space to other users, but remember
+ that deleted rows are physically removed only in an (automatic)
+ purge operation after they are no longer needed for transaction
+ rollbacks or consistent reads. (See
+ <xref linkend="innodb-multi-versioning"/>.)
</para>
</section>
@@ -5853,7 +5849,8 @@
difficult to determine. All <literal>InnoDB</literal> data and
indexes are stored in B-trees, and their fill factor may vary
from 50% to 100%. Another symptom of fragmentation is that a
- table scan such as:
+ table scan such as this takes more time than it
+ <quote>should</quote> take:
</para>
<programlisting>
@@ -5861,11 +5858,10 @@
</programlisting>
<para>
- takes more time than it <quote>should</quote> take. (In the
- query above we are <quote>fooling</quote> the SQL optimizer into
- scanning the clustered index, rather than a secondary index.)
- Most disks can read 10 to 50 MB/s, which can be used to estimate
- how fast a table scan should run.
+ (In the preceding query, we are <quote>fooling</quote> the SQL
+ optimizer into scanning the clustered index, rather than a
+ secondary index.) Most disks can read 10 to 50 MB/s, which can
+ be used to estimate how fast a table scan should run.
</para>
<para>
@@ -5913,8 +5909,8 @@
<listitem>
<para>
- If you run out of file space in the tablespace, you get the
- MySQL <literal>Table is full</literal> error and
+ If you run out of file space in the tablespace, a MySQL
+ <literal>Table is full</literal> error occurs and
<literal>InnoDB</literal> rolls back the SQL statement.
</para>
</listitem>
@@ -5927,11 +5923,11 @@
<para>
When a transaction rollback occurs due to a deadlock or lock
- wait timeout, it cancels the effect of the statements in the
- transaction. But if the transaction was begun with a
+ wait timeout, it cancels the effect of the statements within
+ the transaction. But if the start-transaction statement was
<literal>START TRANSACTION</literal> or
- <literal>BEGIN</literal> statement, it does not cancel that
- statement. Further SQL statements become part of the
+ <literal>BEGIN</literal> statement, rollback does not cancel
+ that statement. Further SQL statements become part of the
transaction until the occurrence of <literal>COMMIT</literal>,
<literal>ROLLBACK</literal>, or some SQL statement that causes
an implicit commit.
@@ -6004,7 +6000,7 @@
<para>
Cannot find the <literal>InnoDB</literal> table from the
- <literal>InnoDB</literal> data files though the
+ <literal>InnoDB</literal> data files, although the
<filename>.frm</filename> file for the table exists. See
<xref linkend="innodb-troubleshooting-datadict"/>.
</para>
@@ -6080,6 +6076,10 @@
distribution.
</para>
+ <remark role="todo">
+ CHECK URL
+ </remark>
+
<para>
The following table provides a list of some common Linux system
error codes. For a more complete list, see
@@ -6792,14 +6792,12 @@
</para>
<para>
- The parameter is incorrect. (If you get this error in
- MySQL-4.1.9 on Windows, and you have set
- <literal>innodb_file_per_table</literal> in
- <filename>my.cnf</filename> or <filename>my.ini</filename>,
- this is Bug #8021, and a workaround is to put the line
- <literal>innodb_flush_method=unbuffered</literal> to your
- <filename>my.cnf</filename> or <filename>my.ini</filename>
- file.)
+ The parameter is incorrect. (If this error occurs on MySQL
+ 4.1.9 on Windows and you have set
+ <literal>innodb_file_per_table</literal> in a server option
+ file, this is Bug #8021, and a workaround is to add the line
+ <literal>innodb_flush_method=unbuffered</literal> to the
+ file as well.)
</para>
</listitem>
@@ -6847,8 +6845,25 @@
<itemizedlist>
+ <remark role="todo">
+ Ask whether this is still true.
+ </remark>
+
<listitem>
<para>
+ <emphasis role="bold">Warning:</emphasis> Do
+ <emphasis>not</emphasis> convert MySQL system tables in the
+ <literal>mysql</literal> database from
+ <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
+ This is an unsupported operation. If you do this, MySQL does
+ not restart until you restore the old system tables from a
+ backup or re-generate them with the
+ <command>mysql_install_db</command> script.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
A table cannot contain more than 1000 columns.
</para>
</listitem>
@@ -6879,8 +6894,11 @@
<listitem>
<para>
- On some older operating systems, data files must be less than
- 2GB.
+ On some older operating systems, files must be less than 2GB.
+ This is not a limitation of <literal>InnoDB</literal> itself,
+ but if you require a large tablespace, you will need to
+ configure it using several smaller data files rather than one
+ or a file large data files.
</para>
</listitem>
@@ -6915,67 +6933,67 @@
<listitem>
<para>
- <literal>ANALYZE TABLE</literal> counts
- <literal>cardinality</literal> by doing eight random dives to
- each of the index trees and updating index cardinality
- estimates accordingly. Note that because these are only
- estimates, repeated runs of <literal>ANALYZE TABLE</literal>
- may produce different numbers. This makes <literal>ANALYZE
- TABLE</literal> fast on <literal>InnoDB</literal> tables but
- not 100% accurate as it doesn't take all rows into account.
+ <literal>ANALYZE TABLE</literal> determines index cardinality
+ (as displayed in the <literal>Cardinality</literal> column of
+ <literal>SHOW INDEX</literal> output) by doing eight random
+ dives to each of the index trees and updating index
+ cardinality estimates accordingly. Note that because these are
+ only estimates, repeated runs of <literal>ANALYZE
+ TABLE</literal> may produce different numbers. This makes
+ <literal>ANALYZE TABLE</literal> fast on
+ <literal>InnoDB</literal> tables but not 100% accurate as it
+ doesn't take all rows into account.
</para>
<para>
MySQL uses index cardinality estimates only in join
optimization. If some join is not optimized in the right way,
- you may try using <literal>ANALYZE TABLE</literal>. In the few
+ you can try using <literal>ANALYZE TABLE</literal>. In the few
cases that <literal>ANALYZE TABLE</literal> doesn't produce
values good enough for your particular tables, you can use
<literal>FORCE INDEX</literal> with your queries to force the
- usage of a particular index, or set
- <literal>max_seeks_for_key</literal> to ensure that MySQL
- prefers index lookups over table scans. See
- <xref linkend="server-system-variables"/>. See
+ use of a particular index, or set the
+ <literal>max_seeks_for_key</literal> system variable to ensure
+ that MySQL prefers index lookups over table scans. See
+ <xref linkend="server-system-variables"/>, and
<xref linkend="optimizer-issues"/>.
</para>
</listitem>
<listitem>
<para>
- On Windows, <literal>InnoDB</literal> always stores database
- and table names internally in lowercase. To move databases in
- binary format from Unix to Windows or from Windows to Unix,
- you should have all database and table names in lowercase.
+ <literal>SHOW TABLE STATUS</literal> does not give accurate
+ statistics on <literal>InnoDB</literal> tables, except for the
+ physical size reserved by the table. The row count is only a
+ rough estimate used in SQL optimization.
</para>
</listitem>
<listitem>
<para>
- <emphasis role="bold">Warning:</emphasis> Do
- <emphasis>not</emphasis> convert MySQL system tables in the
- <literal>mysql</literal> database from
- <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
- This is an unsupported operation. If you do this, MySQL does
- not restart until you restore the old system tables from a
- backup or re-generate them with the
- <command>mysql_install_db</command> script.
+ <literal>InnoDB</literal> does not keep an internal count of
+ rows in a table. (In practice, this would be somewhat
+ complicated due to multi-versioning.) To process a
+ <literal>SELECT COUNT(*) FROM t</literal> statement,
+ <literal>InnoDB</literal> must scan an index of the table,
+ which takes some time if the index is not entirely in the
+ buffer pool. To get a fast count, you have to use a counter
+ table you create yourself and let your application update it
+ according to the inserts and deletes it does. If your table
+ does not change often, using the MySQL query cache is a good
+ solution. <literal>SHOW TABLE STATUS</literal> also can be
+ used if an approximate row count is sufficient. See
+ <xref linkend="innodb-tuning"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>InnoDB</literal> does not keep an internal count of
- rows in a table. (This would actually be somewhat complicated
- because of multi-versioning.) To process a <literal>SELECT
- COUNT(*) FROM t</literal> statement, <literal>InnoDB</literal>
- must scan an index of the table, which takes some time if the
- index is not entirely in the buffer pool. To get a fast count,
- you have to use a counter table you create yourself and let
- your application update it according to the inserts and
- deletes it does. If your table does not change often, using
- the MySQL query cache is a good solution. <literal>SHOW TABLE
- STATUS</literal> also can be used if an approximate row count
- is sufficient. See <xref linkend="innodb-tuning"/>.
+ On Windows, <literal>InnoDB</literal> always stores database
+ and table names internally in lowercase. To move databases in
+ binary format from Unix to Windows or from Windows to Unix,
+ you should always use explicitly lowercase names when creating
+ databases and tables.
</para>
</listitem>
@@ -7006,9 +7024,10 @@
<listitem>
<para>
When you restart the MySQL server, <literal>InnoDB</literal>
- may reuse an old value for an
- <literal>AUTO_INCREMENT</literal> column (that is, a value
- that was assigned to an old transaction that was rolled back).
+ may reuse an old value that was generated for an
+ <literal>AUTO_INCREMENT</literal> column but never stored
+ (that is, a value that was generated during an old transaction
+ that was rolled back).
</para>
</listitem>
@@ -7040,26 +7059,17 @@
<listitem>
<para>
- <literal>TRUNCATE
- <replaceable>tbl_name</replaceable></literal> is mapped to
- <literal>DELETE FROM
- <replaceable>tbl_name</replaceable></literal> for
- <literal>InnoDB</literal> and doesn't reset the
- <literal>AUTO_INCREMENT</literal> counter.
+ Under some conditions, <literal>TRUNCATE
+ <replaceable>tbl_name</replaceable></literal> for an
+ <literal>InnoDB</literal> table is mapped to <literal>DELETE
+ FROM+ <replaceable>tbl_name</replaceable></literal> and
+ doesn't reset the <literal>AUTO_INCREMENT</literal> counter.
+ See <xref linkend="truncate"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>SHOW TABLE STATUS</literal> does not give accurate
- statistics on <literal>InnoDB</literal> tables, except for the
- physical size reserved by the table. The row count is only a
- rough estimate used in SQL optimization.
- </para>
- </listitem>
-
- <listitem>
- <para>
Before MySQL 4.0.14 or 4.1.0, if you tried to create a unique
index on a prefix of a column you got an error:
</para>
@@ -7151,7 +7161,9 @@
<literal>InnoDB</literal> tables. A workaround is to alter the
table to <literal>MyISAM</literal> on the master, do then the
load, and after that alter the master table back to
- <literal>InnoDB</literal>.
+ <literal>InnoDB</literal>. Do not do this if the tables use
+ <literal>InnoDB</literal>-specific features such as foreign
+ keys.
</para>
</listitem>
@@ -7159,7 +7171,7 @@
<para>
The default database page size in <literal>InnoDB</literal> is
16KB. By recompiling the code, you can set it to values
- ranging from 8KB to 64KB. You have to update the values of
+ ranging from 8KB to 64KB. You must update the values of
<literal>UNIV_PAGE_SIZE</literal> and
<literal>UNIV_PAGE_SIZE_SHIFT</literal> in the
<filename>univ.i</filename> source file.
@@ -7174,15 +7186,18 @@
<title>&title-innodb-troubleshooting;</title>
+ <para>
+ The following general guidelines apply to troubleshooting
+ <literal>InnoDB</literal> problems:
+ </para>
+
<itemizedlist>
<listitem>
<para>
- A general rule is that when an operation fails or you suspect
- a bug, you should look at the MySQL server error log, which
- typically has a name something like
- <filename><replaceable>hostname</replaceable>.err</filename>,
- or possibly <filename>mysql.err</filename> on Windows.
+ When an operation fails or you suspect a bug, you should look
+ at the MySQL server error log, which is the file in the data
+ directory that has a suffix of <filename>.err</filename>.
</para>
</listitem>
@@ -7202,7 +7217,8 @@
<listitem>
<para>
Use the <literal>InnoDB</literal> Monitors to obtain
- information about a problem. If the problem is
+ information about a problem (see
+ <xref linkend="innodb-monitor"/>). If the problem is
performance-related, or your server appears to be hung, you
should use <literal>innodb_monitor</literal> to print
information about the internal state of
@@ -7230,13 +7246,14 @@
<para>
A specific issue with tables is that the MySQL server keeps data
dictionary information in <filename>.frm</filename> files it
- stores in the database directories, while
+ stores in the database directories, whereas
<literal>InnoDB</literal> also stores the information into its
own data dictionary inside the tablespace files. If you move
<filename>.frm</filename> files around, or use <literal>DROP
DATABASE</literal> in MySQL versions before 3.23.44, or the
server crashes in the middle of a data dictionary operation, the
- <filename>.frm</filename> files may end up out of sync with the
+ locations of the <filename>.frm</filename> files may end up out
+ of sync with the locations recorded in the
<literal>InnoDB</literal> internal data dictionary.
</para>
@@ -7273,7 +7290,7 @@
with the <option>--disable-auto-rehash</option> option and try
<literal>DROP TABLE</literal> again. (With name completion on,
<command>mysql</command> tries to construct a list of table
- names, which doesn't work when a problem such as just described
+ names, which fails when a problem such as just described
exists.)
</para>
@@ -7313,7 +7330,7 @@
whose name is <filename>#sql-...</filename>. Starting from MySQL
4.0.6, you can perform SQL statements also on tables whose name
contains the character ‘<literal>#</literal>’ if you
- enclose the name in backticks. Thus, you can drop such an
+ enclose the name within backticks. Thus, you can drop such an
orphaned table like any other orphaned table using the method
described above. Note that to copy or rename a file in the Unix
shell, you need to put the file name in double quotes if the
Modified: trunk/refman-5.0/innodb.xml
===================================================================
--- trunk/refman-5.0/innodb.xml 2006-01-09 16:02:35 UTC (rev 739)
+++ trunk/refman-5.0/innodb.xml 2006-01-09 16:06:44 UTC (rev 740)
@@ -902,10 +902,10 @@
<title>&title-innodb-raw-devices;</title>
<para>
- You can also use raw disk partitions as tablespace data files.
- By using a raw disk, you can perform non-buffered I/O on Windows
- and on some Unix systems without filesystem overhead, which may
- improve performance.
+ You can also use raw disk partitions as data files in the shared
+ tablespace. By using a raw disk, you can perform non-buffered
+ I/O on Windows and on some Unix systems without filesystem
+ overhead, which may improve performance.
</para>
<para>
@@ -914,7 +914,7 @@
in <literal>innodb_data_file_path</literal>. The partition must
be at least as large as the size that you specify. Note that 1MB
in <literal>InnoDB</literal> is 1024 × 1024 bytes, whereas
- 1MB usually means 1,000,000 bytes in disk specifications.
+ 1MB in disk specifications usually means 1,000,000 bytes.
</para>
<programlisting>
@@ -929,10 +929,9 @@
the new partition. However, do not create or change any
<literal>InnoDB</literal> tables yet. Otherwise, when you next
restart the server, <literal>InnoDB</literal> reinitializes the
- partition and your changes are lost. (Starting from 3.23.44, as
- a safety measure <literal>InnoDB</literal> prevents users from
- modifying data when any partition with <literal>newraw</literal>
- is specified.)
+ partition and your changes are lost. (As a safety measure
+ <literal>InnoDB</literal> prevents users from modifying data
+ when any partition with <literal>newraw</literal> is specified.)
</para>
<para>
@@ -1220,27 +1219,6 @@
<literal>innodb_file_per_table</literal>
</para>
- <remark role="todo">
- Determine if this bug exists in 5.0.x and handle the following
- accordingly
- </remark>
-
-<!--
- <para>
- <emphasis role="bold">NOTE</emphasis>: A bug in versions <= 4.1.8
- if you specify <literal>innodb_file_per_table</literal> in
- <filename>my.cnf</filename>! If you shut down
- <command>mysqld</command>, then records may disappear from the
- secondary indexes of a table. See (Bug #7496) for more information
- and workarounds. This is fixed in 4.1.9, but another bug (Bug #8021)
- bit the Windows version in 4.1.9, and in the Windows version of
- 4.1.9 you must put the line
- <literal>innodb_flush_method=unbuffered</literal> to your
- <filename>my.cnf</filename> or <filename>my.ini</filename> to get
- <command>mysqld</command> to work.
- </para>
--->
-
<para>
This option causes <literal>InnoDB</literal> to create each
new table using its own <filename>.ibd</filename> file for
@@ -1983,7 +1961,7 @@
<para>
The SQL statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
- that consists of the data files you specified in
+ that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
@@ -5527,9 +5505,9 @@
<para>
If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes.
- <literal>InnoDB</literal> has an automatic mechanism that
- monitors index searches made to the indexes defined for a table.
- If <literal>InnoDB</literal> notices that queries could benefit
+ <literal>InnoDB</literal> has a mechanism that monitors index
+ searches made to the indexes defined for a table. If
+ <literal>InnoDB</literal> notices that queries could benefit
from building a hash index, it does so automatically.
</para>
@@ -5548,7 +5526,7 @@
<para>
In a sense, <literal>InnoDB</literal> tailors itself through the
adaptive hash index mechanism to ample main memory, coming
- closer to the architecture of main memory databases.
+ closer to the architecture of main-memory databases.
</para>
</section>
@@ -5566,9 +5544,9 @@
<listitem>
<para>
- Each index record in <literal>InnoDB</literal> contains a
- header of six bytes. The header is used to link consecutive
- records together, and also in row-level locking.
+ Each index record contains a six-byte header. The header is
+ used to link together consecutive records, and also in
+ row-level locking.
</para>
</listitem>
@@ -5628,10 +5606,10 @@
column. In a fixed-length column, it reserves the fixed
length of the column in the data part of the record. The
motivation behind reserving the fixed space for
- <literal>NULL</literal> values is that then an update of the
- column from <literal>NULL</literal> to a
- non-<literal>NULL</literal> value can be done in place and
- does not cause fragmentation of the index page.
+ <literal>NULL</literal> values is that it enables an update
+ of the column from <literal>NULL</literal> to a
+ non-<literal>NULL</literal> value to be done in place
+ without causing fragmentation of the index page.
</para>
</listitem>
@@ -5709,13 +5687,13 @@
<title>&title-innodb-file-space;</title>
<para>
- The data files you define in the configuration file form the
- tablespace of <literal>InnoDB</literal>. The files are simply
- concatenated to form the tablespace. There is no striping in
- use. Currently you cannot define where in the tablespace your
- tables are allocated. However, in a newly created tablespace,
- <literal>InnoDB</literal> allocates space starting from the
- first data file.
+ The data files that you define in the configuration file form
+ the tablespace of <literal>InnoDB</literal>. The files are
+ simply concatenated to form the tablespace. There is no striping
+ in use. Currently you cannot define where within the tablespace
+ your tables are allocated. However, in a newly created
+ tablespace, <literal>InnoDB</literal> allocates space starting
+ from the first data file.
</para>
<para>
@@ -5735,11 +5713,6 @@
the data.
</para>
- <remark role="todo">
- [js] What is meant by "extents" here? Find out the right word
- and replace.
- </remark>
-
<para>
When a segment grows inside the tablespace,
<literal>InnoDB</literal> allocates the first 32 pages to it
@@ -5758,7 +5731,7 @@
<para>
When you ask for available free space in the tablespace by
- issuing a <literal>SHOW TABLE STATUS</literal>,
+ issuing a <literal>SHOW TABLE STATUS</literal> statement,
<literal>InnoDB</literal> reports the extents that are
definitely free in the tablespace. <literal>InnoDB</literal>
always reserves some extents for cleanup and other internal
@@ -5768,14 +5741,15 @@
<para>
When you delete data from a table, <literal>InnoDB</literal>
- contracts the corresponding B-tree indexes. It depends on the
- pattern of deletes whether that frees individual pages or
- extents to the tablespace, so that the freed space becomes
- available for other users. Dropping a table or deleting all rows
- from it is guaranteed to release the space to other users, but
- remember that deleted rows are physically removed only in an
- (automatic) purge operation after they are no longer needed for
- transaction rollbacks or consistent reads.
+ contracts the corresponding B-tree indexes. Whether the freed
+ space becomes available for other users depends on whether the
+ pattern of deletes frees individual pages or extents to the
+ tablespace. Dropping a table or deleting all rows from it is
+ guaranteed to release the space to other users, but remember
+ that deleted rows are physically removed only in an (automatic)
+ purge operation after they are no longer needed for transaction
+ rollbacks or consistent reads. (See
+ <xref linkend="innodb-multi-versioning"/>.)
</para>
</section>
@@ -5799,7 +5773,8 @@
difficult to determine. All <literal>InnoDB</literal> data and
indexes are stored in B-trees, and their fill factor may vary
from 50% to 100%. Another symptom of fragmentation is that a
- table scan such as:
+ table scan such as this takes more time than it
+ <quote>should</quote> take:
</para>
<programlisting>
@@ -5807,11 +5782,10 @@
</programlisting>
<para>
- takes more time than it <quote>should</quote> take. (In the
- query above we are <quote>fooling</quote> the SQL optimizer into
- scanning the clustered index, rather than a secondary index.)
- Most disks can read 10 to 50 MB/s, which can be used to estimate
- how fast a table scan should run.
+ (In the preceding query, we are <quote>fooling</quote> the SQL
+ optimizer into scanning the clustered index, rather than a
+ secondary index.) Most disks can read 10 to 50 MB/s, which can
+ be used to estimate how fast a table scan should run.
</para>
<para>
@@ -5859,8 +5833,8 @@
<listitem>
<para>
- If you run out of file space in the tablespace, you get the
- MySQL <literal>Table is full</literal> error and
+ If you run out of file space in the tablespace, a MySQL
+ <literal>Table is full</literal> error occurs and
<literal>InnoDB</literal> rolls back the SQL statement.
</para>
</listitem>
@@ -5877,11 +5851,11 @@
<para>
When a transaction rollback occurs due to a deadlock or lock
- wait timeout, it cancels the effect of the statements in the
- transaction. But if the transaction was begun with a
+ wait timeout, it cancels the effect of the statements within
+ the transaction. But if the start-transaction statement was
<literal>START TRANSACTION</literal> or
- <literal>BEGIN</literal> statement, it does not cancel that
- statement. Further SQL statements become part of the
+ <literal>BEGIN</literal> statement, rollback does not cancel
+ that statement. Further SQL statements become part of the
transaction until the occurrence of <literal>COMMIT</literal>,
<literal>ROLLBACK</literal>, or some SQL statement that causes
an implicit commit.
@@ -5953,7 +5927,7 @@
<para>
Cannot find the <literal>InnoDB</literal> table from the
- <literal>InnoDB</literal> data files though the
+ <literal>InnoDB</literal> data files, although the
<filename>.frm</filename> file for the table exists. See
<xref linkend="innodb-troubleshooting-datadict"/>.
</para>
@@ -6029,6 +6003,10 @@
distribution.
</para>
+ <remark role="todo">
+ CHECK URL
+ </remark>
+
<para>
The following table provides a list of some common Linux system
error codes. For a more complete list, see
@@ -6741,14 +6719,12 @@
</para>
<para>
- The parameter is incorrect. (If you get this error on
- Windows, and you have set
- <literal>innodb_file_per_table</literal> in
- <filename>my.cnf</filename> or <filename>my.ini</filename>,
- and then add the line
- <literal>innodb_flush_method=unbuffered</literal> to your
- <filename>my.cnf</filename> or <filename>my.ini</filename>
- file.)
+ The parameter is incorrect. (If this error occurs on Windows
+ and you have enabled
+ <literal>innodb_file_per_table</literal> in a server option
+ file, add the line
+ <literal>innodb_flush_method=unbuffered</literal> to the
+ file as well.)
</para>
</listitem>
@@ -6800,8 +6776,25 @@
<itemizedlist>
+ <remark role="todo">
+ Ask whether this is still true.
+ </remark>
+
<listitem>
<para>
+ <emphasis role="bold">Warning:</emphasis> Do
+ <emphasis>not</emphasis> convert MySQL system tables in the
+ <literal>mysql</literal> database from
+ <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
+ This is an unsupported operation. If you do this, MySQL does
+ not restart until you restore the old system tables from a
+ backup or re-generate them with the
+ <command>mysql_install_db</command> script.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
A table cannot contain more than 1000 columns.
</para>
</listitem>
@@ -6844,7 +6837,7 @@
-->
mysql> <userinput>CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),</userinput>
-> <userinput>c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),</userinput>
- -> <userinput>f VARCHAR(10000), g VARCHAR(10000));</userinput>
+ -> <userinput>f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;</userinput>
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
@@ -6853,8 +6846,11 @@
<listitem>
<para>
- On some older operating systems, data files must be less than
- 2GB.
+ On some older operating systems, files must be less than 2GB.
+ This is not a limitation of <literal>InnoDB</literal> itself,
+ but if you require a large tablespace, you will need to
+ configure it using several smaller data files rather than one
+ or a file large data files.
</para>
</listitem>
@@ -6889,67 +6885,67 @@
<listitem>
<para>
- <literal>ANALYZE TABLE</literal> counts
- <literal>cardinality</literal> by doing eight random dives to
- each of the index trees and updating index cardinality
- estimates accordingly. Note that because these are only
- estimates, repeated runs of <literal>ANALYZE TABLE</literal>
- may produce different numbers. This makes <literal>ANALYZE
- TABLE</literal> fast on <literal>InnoDB</literal> tables but
- not 100% accurate as it doesn't take all rows into account.
+ <literal>ANALYZE TABLE</literal> determines index cardinality
+ (as displayed in the <literal>Cardinality</literal> column of
+ <literal>SHOW INDEX</literal> output) by doing eight random
+ dives to each of the index trees and updating index
+ cardinality estimates accordingly. Note that because these are
+ only estimates, repeated runs of <literal>ANALYZE
+ TABLE</literal> may produce different numbers. This makes
+ <literal>ANALYZE TABLE</literal> fast on
+ <literal>InnoDB</literal> tables but not 100% accurate as it
+ doesn't take all rows into account.
</para>
<para>
MySQL uses index cardinality estimates only in join
optimization. If some join is not optimized in the right way,
- you may try using <literal>ANALYZE TABLE</literal>. In the few
+ you can try using <literal>ANALYZE TABLE</literal>. In the few
cases that <literal>ANALYZE TABLE</literal> doesn't produce
values good enough for your particular tables, you can use
<literal>FORCE INDEX</literal> with your queries to force the
- usage of a particular index, or set
- <literal>max_seeks_for_key</literal> to ensure that MySQL
- prefers index lookups over table scans. See
- <xref linkend="server-system-variables"/>. See
+ use of a particular index, or set the
+ <literal>max_seeks_for_key</literal> system variable to ensure
+ that MySQL prefers index lookups over table scans. See
+ <xref linkend="server-system-variables"/>, and
<xref linkend="optimizer-issues"/>.
</para>
</listitem>
<listitem>
<para>
- On Windows, <literal>InnoDB</literal> always stores database
- and table names internally in lowercase. To move databases in
- binary format from Unix to Windows or from Windows to Unix,
- you should have all database and table names in lowercase.
+ <literal>SHOW TABLE STATUS</literal> does not give accurate
+ statistics on <literal>InnoDB</literal> tables, except for the
+ physical size reserved by the table. The row count is only a
+ rough estimate used in SQL optimization.
</para>
</listitem>
<listitem>
<para>
- <emphasis role="bold">Warning:</emphasis> Do
- <emphasis>not</emphasis> convert MySQL system tables in the
- <literal>mysql</literal> database from
- <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
- This is an unsupported operation. If you do this, MySQL does
- not restart until you restore the old system tables from a
- backup or re-generate them with the
- <command>mysql_install_db</command> script.
+ <literal>InnoDB</literal> does not keep an internal count of
+ rows in a table. (In practice, this would be somewhat
+ complicated due to multi-versioning.) To process a
+ <literal>SELECT COUNT(*) FROM t</literal> statement,
+ <literal>InnoDB</literal> must scan an index of the table,
+ which takes some time if the index is not entirely in the
+ buffer pool. To get a fast count, you have to use a counter
+ table you create yourself and let your application update it
+ according to the inserts and deletes it does. If your table
+ does not change often, using the MySQL query cache is a good
+ solution. <literal>SHOW TABLE STATUS</literal> also can be
+ used if an approximate row count is sufficient. See
+ <xref linkend="innodb-tuning"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>InnoDB</literal> does not keep an internal count of
- rows in a table. (This would actually be somewhat complicated
- because of multi-versioning.) To process a <literal>SELECT
- COUNT(*) FROM t</literal> statement, <literal>InnoDB</literal>
- must scan an index of the table, which takes some time if the
- index is not entirely in the buffer pool. To get a fast count,
- you have to use a counter table you create yourself and let
- your application update it according to the inserts and
- deletes it does. If your table does not change often, using
- the MySQL query cache is a good solution. <literal>SHOW TABLE
- STATUS</literal> also can be used if an approximate row count
- is sufficient. See <xref linkend="innodb-tuning"/>.
+ On Windows, <literal>InnoDB</literal> always stores database
+ and table names internally in lowercase. To move databases in
+ binary format from Unix to Windows or from Windows to Unix,
+ you should always use explicitly lowercase names when creating
+ databases and tables.
</para>
</listitem>
@@ -6980,9 +6976,10 @@
<listitem>
<para>
When you restart the MySQL server, <literal>InnoDB</literal>
- may reuse an old value for an
- <literal>AUTO_INCREMENT</literal> column (that is, a value
- that was assigned to an old transaction that was rolled back).
+ may reuse an old value that was generated for an
+ <literal>AUTO_INCREMENT</literal> column but never stored
+ (that is, a value that was generated during an old transaction
+ that was rolled back).
</para>
</listitem>
@@ -7014,26 +7011,17 @@
<listitem>
<para>
- <literal>TRUNCATE
- <replaceable>tbl_name</replaceable></literal> is mapped to
- <literal>DELETE FROM
- <replaceable>tbl_name</replaceable></literal> for
- <literal>InnoDB</literal> and doesn't reset the
- <literal>AUTO_INCREMENT</literal> counter.
+ Under some conditions, <literal>TRUNCATE
+ <replaceable>tbl_name</replaceable></literal> for an
+ <literal>InnoDB</literal> table is mapped to <literal>DELETE
+ FROM <replaceable>tbl_name</replaceable></literal> and doesn't
+ reset the <literal>AUTO_INCREMENT</literal> counter. See
+ <xref linkend="truncate"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>SHOW TABLE STATUS</literal> does not give accurate
- statistics on <literal>InnoDB</literal> tables, except for the
- physical size reserved by the table. The row count is only a
- rough estimate used in SQL optimization.
- </para>
- </listitem>
-
- <listitem>
- <para>
In MySQL ¤t-series;, the MySQL <literal>LOCK
TABLES</literal> operation acquires two locks on each table if
<literal>innodb_table_locks=1</literal>, with 1 being the
@@ -7079,7 +7067,9 @@
<literal>InnoDB</literal> tables. A workaround is to alter the
table to <literal>MyISAM</literal> on the master, do then the
load, and after that alter the master table back to
- <literal>InnoDB</literal>.
+ <literal>InnoDB</literal>. Do not do this if the tables use
+ <literal>InnoDB</literal>-specific features such as foreign
+ keys.
</para>
</listitem>
@@ -7087,7 +7077,7 @@
<para>
The default database page size in <literal>InnoDB</literal> is
16KB. By recompiling the code, you can set it to values
- ranging from 8KB to 64KB. You have to update the values of
+ ranging from 8KB to 64KB. You must update the values of
<literal>UNIV_PAGE_SIZE</literal> and
<literal>UNIV_PAGE_SIZE_SHIFT</literal> in the
<filename>univ.i</filename> source file.
@@ -7096,8 +7086,8 @@
<listitem>
<para>
- In MySQL ¤t-series;, triggers are not activated by
- cascaded foreign key actions.
+ Currently, triggers are not activated by cascaded foreign key
+ actions.
</para>
</listitem>
@@ -7109,15 +7099,18 @@
<title>&title-innodb-troubleshooting;</title>
+ <para>
+ The following general guidelines apply to troubleshooting
+ <literal>InnoDB</literal> problems:
+ </para>
+
<itemizedlist>
<listitem>
<para>
- A general rule is that when an operation fails or you suspect
- a bug, you should look at the MySQL server error log, which
- typically has a name something like
- <filename><replaceable>hostname</replaceable>.err</filename>,
- or possibly <filename>mysql.err</filename> on Windows.
+ When an operation fails or you suspect a bug, you should look
+ at the MySQL server error log, which is the file in the data
+ directory that has a suffix of <filename>.err</filename>.
</para>
</listitem>
@@ -7137,7 +7130,8 @@
<listitem>
<para>
Use the <literal>InnoDB</literal> Monitors to obtain
- information about a problem. If the problem is
+ information about a problem (see
+ <xref linkend="innodb-monitor"/>). If the problem is
performance-related, or your server appears to be hung, you
should use <literal>innodb_monitor</literal> to print
information about the internal state of
@@ -7165,13 +7159,14 @@
<para>
A specific issue with tables is that the MySQL server keeps data
dictionary information in <filename>.frm</filename> files it
- stores in the database directories, while
+ stores in the database directories, whereas
<literal>InnoDB</literal> also stores the information into its
own data dictionary inside the tablespace files. If you move
<filename>.frm</filename> files around, or if the server crashes
- in the middle of a data dictionary operation, the
- <filename>.frm</filename> files may end up out of sync with
- <literal>InnoDB</literal>'s internal data dictionary.
+ in the middle of a data dictionary operation, the locations of
+ the <filename>.frm</filename> files may end up out of sync with
+ the locations recorded in the <literal>InnoDB</literal> internal
+ data dictionary.
</para>
<para>
@@ -7207,7 +7202,7 @@
with the <option>--disable-auto-rehash</option> option and try
<literal>DROP TABLE</literal> again. (With name completion on,
<command>mysql</command> tries to construct a list of table
- names, which doesn't work when a problem such as just described
+ names, which fails when a problem such as just described
exists.)
</para>
@@ -7247,7 +7242,7 @@
a table whose name is <filename>#sql-...</filename>. You can
perform SQL statements on tables whose name contains the
character ‘<literal>#</literal>’ if you enclose the
- name in backticks. Thus, you can drop such an orphaned table
+ name within backticks. Thus, you can drop such an orphaned table
like any other orphaned table using the method described above.
Note that to copy or rename a file in the Unix shell, you need
to put the file name in double quotes if the file name contains
Modified: trunk/refman-5.1/innodb.xml
===================================================================
--- trunk/refman-5.1/innodb.xml 2006-01-09 16:02:35 UTC (rev 739)
+++ trunk/refman-5.1/innodb.xml 2006-01-09 16:06:44 UTC (rev 740)
@@ -902,10 +902,10 @@
<title>&title-innodb-raw-devices;</title>
<para>
- You can also use raw disk partitions as tablespace data files.
- By using a raw disk, you can perform non-buffered I/O on Windows
- and on some Unix systems without filesystem overhead, which may
- improve performance.
+ You can also use raw disk partitions as data files in the shared
+ tablespace. By using a raw disk, you can perform non-buffered
+ I/O on Windows and on some Unix systems without filesystem
+ overhead, which may improve performance.
</para>
<para>
@@ -914,7 +914,7 @@
in <literal>innodb_data_file_path</literal>. The partition must
be at least as large as the size that you specify. Note that 1MB
in <literal>InnoDB</literal> is 1024 × 1024 bytes, whereas
- 1MB usually means 1,000,000 bytes in disk specifications.
+ 1MB in disk specifications usually means 1,000,000 bytes.
</para>
<programlisting>
@@ -929,10 +929,9 @@
the new partition. However, do not create or change any
<literal>InnoDB</literal> tables yet. Otherwise, when you next
restart the server, <literal>InnoDB</literal> reinitializes the
- partition and your changes are lost. (Starting from 3.23.44, as
- a safety measure <literal>InnoDB</literal> prevents users from
- modifying data when any partition with <literal>newraw</literal>
- is specified.)
+ partition and your changes are lost. (As a safety measure
+ <literal>InnoDB</literal> prevents users from modifying data
+ when any partition with <literal>newraw</literal> is specified.)
</para>
<para>
@@ -1217,27 +1216,6 @@
<literal>innodb_file_per_table</literal>
</para>
- <remark role="todo">
- Determine if this bug exists in 5.1.x and handle the following
- accordingly
- </remark>
-
-<!--
- <para>
- <emphasis role="bold">NOTE</emphasis>: A bug in versions <= 4.1.8
- if you specify <literal>innodb_file_per_table</literal> in
- <filename>my.cnf</filename>! If you shut down
- <command>mysqld</command>, then records may disappear from the
- secondary indexes of a table. See (Bug #7496) for more information
- and workarounds. This is fixed in 4.1.9, but another bug (Bug #8021)
- bit the Windows version in 4.1.9, and in the Windows version of
- 4.1.9 you must put the line
- <literal>innodb_flush_method=unbuffered</literal> to your
- <filename>my.cnf</filename> or <filename>my.ini</filename> to get
- <command>mysqld</command> to work.
- </para>
--->
-
<para>
This option causes <literal>InnoDB</literal> to create each
new table using its own <filename>.ibd</filename> file for
@@ -1960,7 +1938,7 @@
<para>
The SQL statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
- that consists of the data files you specified in
+ that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
@@ -5488,9 +5466,9 @@
<para>
If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes.
- <literal>InnoDB</literal> has an automatic mechanism that
- monitors index searches made to the indexes defined for a table.
- If <literal>InnoDB</literal> notices that queries could benefit
+ <literal>InnoDB</literal> has a mechanism that monitors index
+ searches made to the indexes defined for a table. If
+ <literal>InnoDB</literal> notices that queries could benefit
from building a hash index, it does so automatically.
</para>
@@ -5509,7 +5487,7 @@
<para>
In a sense, <literal>InnoDB</literal> tailors itself through the
adaptive hash index mechanism to ample main memory, coming
- closer to the architecture of main memory databases.
+ closer to the architecture of main-memory databases.
</para>
</section>
@@ -5527,9 +5505,9 @@
<listitem>
<para>
- Each index record in <literal>InnoDB</literal> contains a
- header of six bytes. The header is used to link consecutive
- records together, and also in row-level locking.
+ Each index record contains a six-byte header. The header is
+ used to link together consecutive records, and also in
+ row-level locking.
</para>
</listitem>
@@ -5585,10 +5563,10 @@
column. In a fixed-length column, it reserves the fixed
length of the column in the data part of the record. The
motivation behind reserving the fixed space for
- <literal>NULL</literal> values is that then an update of the
- column from <literal>NULL</literal> to a
- non-<literal>NULL</literal> value can be done in place and
- does not cause fragmentation of the index page.
+ <literal>NULL</literal> values is that it enables an update
+ of the column from <literal>NULL</literal> to a
+ non-<literal>NULL</literal> value to be done in place
+ without causing fragmentation of the index page.
</para>
</listitem>
@@ -5666,13 +5644,13 @@
<title>&title-innodb-file-space;</title>
<para>
- The data files you define in the configuration file form the
- tablespace of <literal>InnoDB</literal>. The files are simply
- concatenated to form the tablespace. There is no striping in
- use. Currently you cannot define where in the tablespace your
- tables are allocated. However, in a newly created tablespace,
- <literal>InnoDB</literal> allocates space starting from the
- first data file.
+ The data files that you define in the configuration file form
+ the tablespace of <literal>InnoDB</literal>. The files are
+ simply concatenated to form the tablespace. There is no striping
+ in use. Currently you cannot define where within the tablespace
+ your tables are allocated. However, in a newly created
+ tablespace, <literal>InnoDB</literal> allocates space starting
+ from the first data file.
</para>
<para>
@@ -5692,11 +5670,6 @@
the data.
</para>
- <remark role="todo">
- [js] What is meant by "extents" here? Find out the right word
- and replace.
- </remark>
-
<para>
When a segment grows inside the tablespace,
<literal>InnoDB</literal> allocates the first 32 pages to it
@@ -5715,7 +5688,7 @@
<para>
When you ask for available free space in the tablespace by
- issuing a <literal>SHOW TABLE STATUS</literal>,
+ issuing a <literal>SHOW TABLE STATUS</literal> statement,
<literal>InnoDB</literal> reports the extents that are
definitely free in the tablespace. <literal>InnoDB</literal>
always reserves some extents for cleanup and other internal
@@ -5725,14 +5698,15 @@
<para>
When you delete data from a table, <literal>InnoDB</literal>
- contracts the corresponding B-tree indexes. It depends on the
- pattern of deletes whether that frees individual pages or
- extents to the tablespace, so that the freed space becomes
- available for other users. Dropping a table or deleting all rows
- from it is guaranteed to release the space to other users, but
- remember that deleted rows are physically removed only in an
- (automatic) purge operation after they are no longer needed for
- transaction rollbacks or consistent reads.
+ contracts the corresponding B-tree indexes. Whether the freed
+ space becomes available for other users depends on whether the
+ pattern of deletes frees individual pages or extents to the
+ tablespace. Dropping a table or deleting all rows from it is
+ guaranteed to release the space to other users, but remember
+ that deleted rows are physically removed only in an (automatic)
+ purge operation after they are no longer needed for transaction
+ rollbacks or consistent reads. (See
+ <xref linkend="innodb-multi-versioning"/>.)
</para>
</section>
@@ -5756,7 +5730,8 @@
difficult to determine. All <literal>InnoDB</literal> data and
indexes are stored in B-trees, and their fill factor may vary
from 50% to 100%. Another symptom of fragmentation is that a
- table scan such as:
+ table scan such as this takes more time than it
+ <quote>should</quote> take:
</para>
<programlisting>
@@ -5764,11 +5739,10 @@
</programlisting>
<para>
- takes more time than it <quote>should</quote> take. (In the
- query above we are <quote>fooling</quote> the SQL optimizer into
- scanning the clustered index, rather than a secondary index.)
- Most disks can read 10 to 50 MB/s, which can be used to estimate
- how fast a table scan should run.
+ (In the preceding query, we are <quote>fooling</quote> the SQL
+ optimizer into scanning the clustered index, rather than a
+ secondary index.) Most disks can read 10 to 50 MB/s, which can
+ be used to estimate how fast a table scan should run.
</para>
<para>
@@ -5816,8 +5790,8 @@
<listitem>
<para>
- If you run out of file space in the tablespace, you get the
- MySQL <literal>Table is full</literal> error and
+ If you run out of file space in the tablespace, a MySQL
+ <literal>Table is full</literal> error occurs and
<literal>InnoDB</literal> rolls back the SQL statement.
</para>
</listitem>
@@ -5832,11 +5806,11 @@
<para>
When a transaction rollback occurs due to a deadlock or lock
- wait timeout, it cancels the effect of the statements in the
- transaction. But if the transaction was begun with a
+ wait timeout, it cancels the effect of the statements within
+ the transaction. But if the start-transaction statement was
<literal>START TRANSACTION</literal> or
- <literal>BEGIN</literal> statement, it does not cancel that
- statement. Further SQL statements become part of the
+ <literal>BEGIN</literal> statement, rollback does not cancel
+ that statement. Further SQL statements become part of the
transaction until the occurrence of <literal>COMMIT</literal>,
<literal>ROLLBACK</literal>, or some SQL statement that causes
an implicit commit.
@@ -5908,7 +5882,7 @@
<para>
Cannot find the <literal>InnoDB</literal> table from the
- <literal>InnoDB</literal> data files though the
+ <literal>InnoDB</literal> data files, although the
<filename>.frm</filename> file for the table exists. See
<xref linkend="innodb-troubleshooting-datadict"/>.
</para>
@@ -5984,6 +5958,10 @@
distribution.
</para>
+ <remark role="todo">
+ CHECK URL
+ </remark>
+
<para>
The following table provides a list of some common Linux system
error codes. For a more complete list, see
@@ -6696,14 +6674,12 @@
</para>
<para>
- The parameter is incorrect. (If you get this error on
- Windows, and you have set
- <literal>innodb_file_per_table</literal> in
- <filename>my.cnf</filename> or <filename>my.ini</filename>,
- and then add the line
- <literal>innodb_flush_method=unbuffered</literal> to your
- <filename>my.cnf</filename> or <filename>my.ini</filename>
- file.)
+ The parameter is incorrect. (If this error occurs on Windows
+ and you have enabled
+ <literal>innodb_file_per_table</literal> in a server option
+ file, add the line
+ <literal>innodb_flush_method=unbuffered</literal> to the
+ file as well.)
</para>
</listitem>
@@ -6755,8 +6731,25 @@
<itemizedlist>
+ <remark role="todo">
+ Ask whether this is still true.
+ </remark>
+
<listitem>
<para>
+ <emphasis role="bold">Warning:</emphasis> Do
+ <emphasis>not</emphasis> convert MySQL system tables in the
+ <literal>mysql</literal> database from
+ <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
+ This is an unsupported operation. If you do this, MySQL does
+ not restart until you restore the old system tables from a
+ backup or re-generate them with the
+ <command>mysql_install_db</command> script.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
A table cannot contain more than 1000 columns.
</para>
</listitem>
@@ -6799,7 +6792,7 @@
-->
mysql> <userinput>CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),</userinput>
-> <userinput>c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),</userinput>
- -> <userinput>f VARCHAR(10000), g VARCHAR(10000));</userinput>
+ -> <userinput>f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;</userinput>
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
@@ -6808,8 +6801,11 @@
<listitem>
<para>
- On some older operating systems, data files must be less than
- 2GB.
+ On some older operating systems, files must be less than 2GB.
+ This is not a limitation of <literal>InnoDB</literal> itself,
+ but if you require a large tablespace, you will need to
+ configure it using several smaller data files rather than one
+ or a file large data files.
</para>
</listitem>
@@ -6837,67 +6833,67 @@
<listitem>
<para>
- <literal>ANALYZE TABLE</literal> counts
- <literal>cardinality</literal> by doing eight random dives to
- each of the index trees and updating index cardinality
- estimates accordingly. Note that because these are only
- estimates, repeated runs of <literal>ANALYZE TABLE</literal>
- may produce different numbers. This makes <literal>ANALYZE
- TABLE</literal> fast on <literal>InnoDB</literal> tables but
- not 100% accurate as it doesn't take all rows into account.
+ <literal>ANALYZE TABLE</literal> determines index cardinality
+ (as displayed in the <literal>Cardinality</literal> column of
+ <literal>SHOW INDEX</literal> output) by doing eight random
+ dives to each of the index trees and updating index
+ cardinality estimates accordingly. Note that because these are
+ only estimates, repeated runs of <literal>ANALYZE
+ TABLE</literal> may produce different numbers. This makes
+ <literal>ANALYZE TABLE</literal> fast on
+ <literal>InnoDB</literal> tables but not 100% accurate as it
+ doesn't take all rows into account.
</para>
<para>
MySQL uses index cardinality estimates only in join
optimization. If some join is not optimized in the right way,
- you may try using <literal>ANALYZE TABLE</literal>. In the few
+ you can try using <literal>ANALYZE TABLE</literal>. In the few
cases that <literal>ANALYZE TABLE</literal> doesn't produce
values good enough for your particular tables, you can use
<literal>FORCE INDEX</literal> with your queries to force the
- usage of a particular index, or set
- <literal>max_seeks_for_key</literal> to ensure that MySQL
- prefers index lookups over table scans. See
- <xref linkend="server-system-variables"/>. See
+ use of a particular index, or set the
+ <literal>max_seeks_for_key</literal> system variable to ensure
+ that MySQL prefers index lookups over table scans. See
+ <xref linkend="server-system-variables"/>, and
<xref linkend="optimizer-issues"/>.
</para>
</listitem>
<listitem>
<para>
- On Windows, <literal>InnoDB</literal> always stores database
- and table names internally in lowercase. To move databases in
- binary format from Unix to Windows or from Windows to Unix,
- you should have all database and table names in lowercase.
+ <literal>SHOW TABLE STATUS</literal> does not give accurate
+ statistics on <literal>InnoDB</literal> tables, except for the
+ physical size reserved by the table. The row count is only a
+ rough estimate used in SQL optimization.
</para>
</listitem>
<listitem>
<para>
- <emphasis role="bold">Warning:</emphasis> Do
- <emphasis>not</emphasis> convert MySQL system tables in the
- <literal>mysql</literal> database from
- <literal>MyISAM</literal> to <literal>InnoDB</literal> tables!
- This is an unsupported operation. If you do this, MySQL does
- not restart until you restore the old system tables from a
- backup or re-generate them with the
- <command>mysql_install_db</command> script.
+ <literal>InnoDB</literal> does not keep an internal count of
+ rows in a table. (In practice, this would be somewhat
+ complicated due to multi-versioning.) To process a
+ <literal>SELECT COUNT(*) FROM t</literal> statement,
+ <literal>InnoDB</literal> must scan an index of the table,
+ which takes some time if the index is not entirely in the
+ buffer pool. To get a fast count, you have to use a counter
+ table you create yourself and let your application update it
+ according to the inserts and deletes it does. If your table
+ does not change often, using the MySQL query cache is a good
+ solution. <literal>SHOW TABLE STATUS</literal> also can be
+ used if an approximate row count is sufficient. See
+ <xref linkend="innodb-tuning"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>InnoDB</literal> does not keep an internal count of
- rows in a table. (This would actually be somewhat complicated
- because of multi-versioning.) To process a <literal>SELECT
- COUNT(*) FROM t</literal> statement, <literal>InnoDB</literal>
- must scan an index of the table, which takes some time if the
- index is not entirely in the buffer pool. To get a fast count,
- you have to use a counter table you create yourself and let
- your application update it according to the inserts and
- deletes it does. If your table does not change often, using
- the MySQL query cache is a good solution. <literal>SHOW TABLE
- STATUS</literal> also can be used if an approximate row count
- is sufficient. See <xref linkend="innodb-tuning"/>.
+ On Windows, <literal>InnoDB</literal> always stores database
+ and table names internally in lowercase. To move databases in
+ binary format from Unix to Windows or from Windows to Unix,
+ you should always use explicitly lowercase names when creating
+ databases and tables.
</para>
</listitem>
@@ -6915,9 +6911,10 @@
<listitem>
<para>
When you restart the MySQL server, <literal>InnoDB</literal>
- may reuse an old value for an
- <literal>AUTO_INCREMENT</literal> column (that is, a value
- that was assigned to an old transaction that was rolled back).
+ may reuse an old value that was generated for an
+ <literal>AUTO_INCREMENT</literal> column but never stored
+ (that is, a value that was generated during an old transaction
+ that was rolled back).
</para>
</listitem>
@@ -6949,26 +6946,17 @@
<listitem>
<para>
- <literal>TRUNCATE
- <replaceable>tbl_name</replaceable></literal> is mapped to
- <literal>DELETE FROM
- <replaceable>tbl_name</replaceable></literal> for
- <literal>InnoDB</literal> and doesn't reset the
- <literal>AUTO_INCREMENT</literal> counter.
+ Under some conditions, <literal>TRUNCATE
+ <replaceable>tbl_name</replaceable></literal> for an
+ <literal>InnoDB</literal> table is mapped to <literal>DELETE
+ FROM <replaceable>tbl_name</replaceable></literal> and doesn't
+ reset the <literal>AUTO_INCREMENT</literal> counter. See
+ <xref linkend="truncate"/>.
</para>
</listitem>
<listitem>
<para>
- <literal>SHOW TABLE STATUS</literal> does not give accurate
- statistics on <literal>InnoDB</literal> tables, except for the
- physical size reserved by the table. The row count is only a
- rough estimate used in SQL optimization.
- </para>
- </listitem>
-
- <listitem>
- <para>
In MySQL ¤t-series;, the MySQL <literal>LOCK
TABLES</literal> operation acquires two locks on each table if
<literal>innodb_table_locks=1</literal>, with 1 being the
@@ -7014,7 +7002,9 @@
<literal>InnoDB</literal> tables. A workaround is to alter the
table to <literal>MyISAM</literal> on the master, do then the
load, and after that alter the master table back to
- <literal>InnoDB</literal>.
+ <literal>InnoDB</literal>. Do not do this if the tables use
+ <literal>InnoDB</literal>-specific features such as foreign
+ keys.
</para>
</listitem>
@@ -7022,7 +7012,7 @@
<para>
The default database page size in <literal>InnoDB</literal> is
16KB. By recompiling the code, you can set it to values
- ranging from 8KB to 64KB. You have to update the values of
+ ranging from 8KB to 64KB. You must update the values of
<literal>UNIV_PAGE_SIZE</literal> and
<literal>UNIV_PAGE_SIZE_SHIFT</literal> in the
<filename>univ.i</filename> source file.
@@ -7031,8 +7021,8 @@
<listitem>
<para>
- In MySQL ¤t-series;, triggers are not activated by
- cascaded foreign key actions.
+ Currently, triggers are not activated by cascaded foreign key
+ actions.
</para>
</listitem>
@@ -7044,15 +7034,18 @@
<title>&title-innodb-troubleshooting;</title>
+ <para>
+ The following general guidelines apply to troubleshooting
+ <literal>InnoDB</literal> problems:
+ </para>
+
<itemizedlist>
<listitem>
<para>
- A general rule is that when an operation fails or you suspect
- a bug, you should look at the MySQL server error log, which
- typically has a name something like
- <filename><replaceable>hostname</replaceable>.err</filename>,
- or possibly <filename>mysql.err</filename> on Windows.
+ When an operation fails or you suspect a bug, you should look
+ at the MySQL server error log, which is the file in the data
+ directory that has a suffix of <filename>.err</filename>.
</para>
</listitem>
@@ -7072,7 +7065,8 @@
<listitem>
<para>
Use the <literal>InnoDB</literal> Monitors to obtain
- information about a problem. If the problem is
+ information about a problem (see
+ <xref linkend="innodb-monitor"/>). If the problem is
performance-related, or your server appears to be hung, you
should use <literal>innodb_monitor</literal> to print
information about the internal state of
@@ -7100,13 +7094,14 @@
<para>
A specific issue with tables is that the MySQL server keeps data
dictionary information in <filename>.frm</filename> files it
- stores in the database directories, while
+ stores in the database directories, whereas
<literal>InnoDB</literal> also stores the information into its
own data dictionary inside the tablespace files. If you move
<filename>.frm</filename> files around, or if the server crashes
- in the middle of a data dictionary operation, the
- <filename>.frm</filename> files may end up out of sync with
- <literal>InnoDB</literal>'s internal data dictionary.
+ in the middle of a data dictionary operation, the locations of
+ the <filename>.frm</filename> files may end up out of sync with
+ the locations recorded in the <literal>InnoDB</literal> internal
+ data dictionary.
</para>
<para>
@@ -7142,7 +7137,7 @@
with the <option>--disable-auto-rehash</option> option and try
<literal>DROP TABLE</literal> again. (With name completion on,
<command>mysql</command> tries to construct a list of table
- names, which doesn't work when a problem such as just described
+ names, which fails when a problem such as just described
exists.)
</para>
@@ -7182,7 +7177,7 @@
a table whose name is <filename>#sql-...</filename>. You can
perform SQL statements on tables whose name contains the
character ‘<literal>#</literal>’ if you enclose the
- name in backticks. Thus, you can drop such an orphaned table
+ name within backticks. Thus, you can drop such an orphaned table
like any other orphaned table using the method described above.
Note that to copy or rename a file in the Unix shell, you need
to put the file name in double quotes if the file name contains
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r740 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 9 Jan |
