Author: jdrussel
Date: 2011-05-16 22:45:12 +0200 (Mon, 16 May 2011)
New Revision: 26243
Log:
Modest wording changes in the intro sections of a few SEs, to emphasize use cases.
Wording changes and significant rearranging of subsections within the overall SE chapter:
- InnoDB before MyISAM.
- Other storage engines in rough descending order of usefulness or audience size.
- Architectural info for specialized audience moved to end of chapter.
Modified:
trunk/refman-5.6/se-archive-core.xml
trunk/refman-5.6/se-innodb-core.xml
trunk/refman-5.6/se-memory-core.xml
trunk/refman-5.6/storage-engines-core.xml
Modified: trunk/refman-5.6/se-archive-core.xml
===================================================================
--- trunk/refman-5.6/se-archive-core.xml 2011-05-16 19:23:40 UTC (rev 26242)
+++ trunk/refman-5.6/se-archive-core.xml 2011-05-16 20:45:12 UTC (rev 26243)
Changed blocks: 1, Lines Added: 10, Lines Deleted: 2; 1200 bytes
@@ -18,11 +18,19 @@
<secondary>ARCHIVE</secondary>
</indexterm>
+<!-- JDR: clarify the ultimate purpose of these tables: for backup only, reporting queries with full-table scans, ...? -->
+
+<!-- JDR: do you ALTER TABLE ... ENGINE=ARCHIVE right before moving the data to some inactive storage area? -->
+
<para>
- The <literal>ARCHIVE</literal> storage engine is used for storing
- large amounts of data without indexes in a very small footprint.
+ The <literal>ARCHIVE</literal> storage engine produces
+ special-purpose tables that store large amounts of unindexed data in
+ a very small footprint.
</para>
+<!-- JDR: The feature matrix shouldn't be for "choosing" this SE vs. another, so much as understanding
+what you give up with it. -->
+
<para condition="dynamic:productfeatures:singlesummary" role="5.6:storageengines:archive">
<citetitle><literal>ARCHIVE</literal> Storage Engine
Features</citetitle>
Modified: trunk/refman-5.6/se-innodb-core.xml
===================================================================
--- trunk/refman-5.6/se-innodb-core.xml 2011-05-16 19:23:40 UTC (rev 26242)
+++ trunk/refman-5.6/se-innodb-core.xml 2011-05-16 20:45:12 UTC (rev 26243)
Changed blocks: 2, Lines Added: 15, Lines Deleted: 11; 2119 bytes
@@ -41,18 +41,25 @@
</indexterm>
<para>
- <literal>InnoDB</literal> is a high-reliability and high-performance
- storage engine for MySQL. In MySQL ¤t-series;, it is the
- default MySQL storage engine. Key advantages of InnoDB include:
+ <literal>InnoDB</literal> is a general-purpose storage engine that
+ balances high reliability and high performance. In MySQL
+ ¤t-series;, issuing the <literal role="stmt">CREATE
+ TABLE</literal> statement with no <literal>ENGINE=</literal> clause
+ creates an InnoDB table.
</para>
+ <para>
+ Key advantages of InnoDB tables include:
+ </para>
+
<itemizedlist>
<listitem>
<para>
- Its design follows the <link linkend="glos_acid">ACID</link>
- model, with <link linkend="glos_transaction">transactions</link>
- featuring <link linkend="glos_commit">commit</link>,
+ Its <link linkend="glos_dml">DML</link> operations follow the
+ <link linkend="glos_acid">ACID</link> model, with
+ <link linkend="glos_transaction">transactions</link> featuring
+ <link linkend="glos_commit">commit</link>,
<link linkend="glos_rollback">rollback</link>, and
<link linkend="glos_crash_recovery">crash-recovery</link>
capabilities to protect user data.
@@ -70,11 +77,8 @@
<listitem>
<para>
<literal>InnoDB</literal> tables arrange your data on disk to
- optimize common queries based on
- <link linkend="glos_primary_key">primary keys</link>. Each
- <literal>InnoDB</literal> table has a primary key index called
- the <link linkend="glos_clustered_index">clustered index</link>
- that organizes the data to minimize I/O for primary key lookups.
+ optimize queries based on
+ <link linkend="glos_primary_key">primary keys</link>.
</para>
</listitem>
Modified: trunk/refman-5.6/se-memory-core.xml
===================================================================
--- trunk/refman-5.6/se-memory-core.xml 2011-05-16 19:23:40 UTC (rev 26242)
+++ trunk/refman-5.6/se-memory-core.xml 2011-05-16 20:45:12 UTC (rev 26243)
Changed blocks: 4, Lines Added: 26, Lines Deleted: 14; 3644 bytes
@@ -28,13 +28,24 @@
</indexterm>
<para>
- The <literal>MEMORY</literal> storage engine creates tables with
- contents that are stored in memory. Formerly, these were known as
- <literal>HEAP</literal> tables. <literal>MEMORY</literal> is the
- preferred term, although <literal>HEAP</literal> remains supported
- for backward compatibility.
+ The <literal>MEMORY</literal> storage engine (formerly known as
+ <literal>HEAP</literal>) creates special-purpose tables with
+ contents that are stored in memory. Because the data is vulnerable
+ to crashes, hardware issues, or power outages, only use these tables
+ as temporary work areas or read-only caches for data pulled from
+ other tables.
</para>
+<!-- JDR: What's the reason for the MEMORY vs. Cluster emphasis throughout this section? Need InnoDB in the mix. -->
+
+<!-- JDR: Need to find a way to introduce USING HASH vs. USING BTREE early in this section, without a lot of technical detail. -->
+
+ <para>
+ Despite the in-memory processing for <literal>MEMORY</literal>
+ tables, they are not necessarily faster than InnoDB tables on a busy
+ server, for general-purpose queries, or under a read/write workload.
+ </para>
+
<para condition="dynamic:productfeatures:singlesummary" role="5.6:storageengines:memory">
<citetitle><literal>MEMORY</literal> Storage Engine
Features</citetitle>
@@ -58,19 +69,20 @@
<listitem>
<para>
- Operations such as session management or caching
+ Operations such as session management or caching.
</para>
</listitem>
<listitem>
<para>
- In-memory storage for fast access and low latency
+ In-memory storage for fast access and low latency.
</para>
</listitem>
<listitem>
<para>
- A read-only or read-mostly data access pattern (limited updates)
+ A read-only or read-mostly data access pattern (limited
+ updates).
</para>
</listitem>
@@ -97,33 +109,33 @@
<listitem>
<para>
Row-level locking and multiple-thread operation for low
- contention between clients
+ contention between clients.
</para>
</listitem>
<listitem>
<para>
- Scalability even with statement mixes that include writes
+ Scalability even with statement mixes that include writes.
</para>
</listitem>
<listitem>
<para>
- Optional disk-backed operation for data durability
+ Optional disk-backed operation for data durability.
</para>
</listitem>
<listitem>
<para>
Shared-nothing architecture and multiple-host operation with no
- single point of failure, enabling 99.999% availability
+ single point of failure, enabling 99.999% availability.
</para>
</listitem>
<listitem>
<para>
Automatic data distribution across nodes; application developers
- need not craft custom sharding or partitioning solutions
+ need not craft custom sharding or partitioning solutions.
</para>
</listitem>
@@ -132,7 +144,7 @@
Support for variable-length data types (including
<literal role="type">BLOB</literal> and
<literal role="type">TEXT</literal>) not supported by
- <literal role="se">MEMORY</literal>
+ <literal role="se">MEMORY</literal>.
</para>
</listitem>
Modified: trunk/refman-5.6/storage-engines-core.xml
===================================================================
--- trunk/refman-5.6/storage-engines-core.xml 2011-05-16 19:23:40 UTC (rev 26242)
+++ trunk/refman-5.6/storage-engines-core.xml 2011-05-16 20:45:12 UTC (rev 26243)
Changed blocks: 16, Lines Added: 134, Lines Deleted: 110; 18062 bytes
@@ -69,10 +69,13 @@
</indexterm>
<para>
- MySQL supports several storage engines that act as handlers for
- different table types. MySQL storage engines include both those that
- handle transaction-safe tables and those that handle
- nontransaction-safe tables.
+ Storage engines are MySQL components that handle the SQL operations
+ for different table types. <literal role="se">InnoDB</literal> is
+ the most general-purpose storage engine, and Oracle recommends using
+ it for tables except for specialized use cases. (The
+ <literal role="stmt">CREATE TABLE</literal> statement in MySQL
+ ¤t-series; creates <literal>InnoDB</literal> tables by
+ default.)
</para>
<para>
@@ -82,7 +85,7 @@
</para>
<para>
- To determine which storage engines your server supports by using the
+ To determine which storage engines your server supports, use the
<literal role="stmt">SHOW ENGINES</literal> statement. The value in
the <literal>Support</literal> column indicates whether an engine
can be used. A value of <literal>YES</literal>,
@@ -91,6 +94,9 @@
set as the default storage engine.
</para>
+<!-- JDR: not helpful to show output only with these non-default engines. -->
+
+<!--
<programlisting>
mysql> <userinput>SHOW ENGINES\G</userinput>
*************************** 1. row ***************************
@@ -116,33 +122,37 @@
Savepoints: NO
...
</programlisting>
+-->
<para>
- This chapter describes each of the MySQL storage engines except for
- <literal>NDBCLUSTER</literal>, which is covered in
- <xref linkend="refman-5.1:mysql-cluster"/>. It also contains a
- description of the pluggable storage engine architecture (see
- <xref linkend="pluggable-storage-overview"/>).
+ This chapter primarily describes the features and performance
+ characteristics of <literal>InnoDB</literal> tables. It also covers
+ the use cases for the special-purpose MySQL storage engines, except
+ for <literal>NDBCLUSTER</literal> which is covered in
+ <xref linkend="refman-5.1:mysql-cluster"/>. For advanced users, it
+ also contains a description of the pluggable storage engine
+ architecture (see <xref linkend="pluggable-storage-overview"/>).
</para>
<para>
For information about storage engine support offered in commercial
MySQL Server binaries, see
<ulink url="&base-url-enterprise;server.html"><citetitle>MySQL
- Enterprise Server 5.1</citetitle></ulink>, on the MySQL Web site.
+ Enterprise Server 5.6</citetitle></ulink>, on the MySQL Web site.
The storage engines available might depend on which edition of
Enterprise Server you are using.
</para>
+<!-- JDR: Review FAQs and see if any make sense to embed here. -->
+
<para>
For answers to some commonly asked questions about MySQL storage
engines, see <xref linkend="faqs-storage-engines"/>.
</para>
- <para>
- <emphasis role="bold">MySQL ¤t-series; supported storage
- engines</emphasis>
- </para>
+ <bridgehead>
+ MySQL ¤t-series; Supported storage Engines
+ </bridgehead>
<itemizedlist>
@@ -160,16 +170,16 @@
<literal>InnoDB</literal> also supports <literal>FOREIGN
KEY</literal> referential-integrity constraints.
<literal>InnoDB</literal> is the default storage engine in MySQL
- ¤t-series;
+ ¤t-series;.
</para>
</listitem>
<listitem>
<para>
<link linkend="myisam-storage-engine"><literal>MyISAM</literal></link>:
- The MySQL storage engine that is used the most in Web, data
- warehousing, and other application environments.
- <literal>MyISAM</literal> is supported in all MySQL
+ These tables have a small footprint. Table-level locking limits
+ the performance in read/write workloads, so it is often used in
+ read-only or read-mostly workloads in Web and data warehousing
configurations.
</para>
</listitem>
@@ -177,78 +187,90 @@
<listitem>
<para>
<link linkend="memory-storage-engine"><literal>Memory</literal></link>:
- Stores all data in RAM for extremely fast access in environments
- that require quick lookups of reference and other like data.
- This engine was formerly known as the <literal>HEAP</literal>
- engine.
+ Stores all data in RAM, for fast access in environments that
+ require quick lookups of non-critical data. This engine was
+ formerly known as the <literal>HEAP</literal> engine. Its use
+ cases are decreasing; <literal>InnoDB</literal> with its buffer
+ pool memory area provides a general-purpose and durable way to
+ keep most or all data in memory, and
+ <literal>NDBCLUSTER</literal> provides fast key-value lookups
+ for huge distributed data sets.
</para>
</listitem>
<listitem>
<para>
- <link linkend="merge-storage-engine"><literal>Merge</literal></link>:
- Enables a MySQL DBA or developer to logically group a series of
- identical <literal>MyISAM</literal> tables and reference them as
- one object. Good for VLDB environments such as data warehousing.
+ <link linkend="csv-storage-engine"><literal>CSV</literal></link>:
+ Its tables are really text files with comma-separated values.
+ CSV tables let you import or dump data in CSV format, to
+ exchange data with scripts and applications that read and write
+ that same format. Because CSV tables are not indexed, you
+ typically keep the data in <literal>InnoDB</literal> tables
+ during normal operation, and only use CSV tables during the
+ import or export stage.
</para>
</listitem>
<listitem>
<para>
<link linkend="archive-storage-engine"><literal>Archive</literal></link>:
- Provides the perfect solution for storing and retrieving large
- amounts of seldom-referenced historical, archived, or security
- audit information.
+ These compact, unindexed tables are intended for storing and
+ retrieving large amounts of seldom-referenced historical,
+ archived, or security audit information.
</para>
</listitem>
<listitem>
<para>
- <link linkend="federated-storage-engine"><literal>Federated</literal></link>:
- Offers the ability to link separate MySQL servers to create one
- logical database from many physical servers. Very good for
- distributed or data mart environments.
+ <link linkend="blackhole-storage-engine"><literal>Blackhole</literal></link>:
+ The Blackhole storage engine accepts but does not store data,
+ similar to the Unix <literal>/dev/null</literal> device. Queries
+ always return an empty set. These tables can be used in
+ replication configurations where DML statements are sent to
+ slave servers, but the master server does not keep its own copy
+ of the data.
</para>
</listitem>
<listitem>
<para>
- <link linkend="csv-storage-engine"><literal>CSV</literal></link>:
- The CSV storage engine stores data in text files using
- comma-separated values format. You can use the CSV engine to
- easily exchange data between other software and applications
- that can import and export in CSV format.
+ <link linkend="merge-storage-engine"><literal>Merge</literal></link>:
+ Enables a MySQL DBA or developer to logically group a series of
+ identical <literal>MyISAM</literal> tables and reference them as
+ one object. Good for VLDB environments such as data warehousing.
</para>
</listitem>
<listitem>
<para>
- <link linkend="blackhole-storage-engine"><literal>Blackhole</literal></link>:
- The Blackhole storage engine accepts but does not store data and
- retrievals always return an empty set. The functionality can be
- used in distributed database design where data is automatically
- replicated, but not stored locally.
+ <link linkend="federated-storage-engine"><literal>Federated</literal></link>:
+ Offers the ability to link separate MySQL servers to create one
+ logical database from many physical servers. Very good for
+ distributed or data mart environments.
</para>
</listitem>
<listitem>
<para>
<link linkend="example-storage-engine"><literal>Example</literal></link>:
- The Example storage engine is <quote>stub</quote> engine that
- does nothing. You can create tables with this engine, but no
- data can be stored in them or retrieved from them. The purpose
- of this engine is to serve as an example in the MySQL source
- code that illustrates how to begin writing new storage engines.
- As such, it is primarily of interest to developers.
+ This engine serves as an example in the MySQL source code that
+ illustrates how to begin writing new storage engines. It is
+ primarily of interest to developers. The storage engine is a
+ <quote>stub</quote> that does nothing. You can create tables
+ with this engine, but no data can be stored in them or retrieved
+ from them.
</para>
</listitem>
</itemizedlist>
<para>
- It is important to remember that you are not restricted to using the
- same storage engine for an entire server or schema: you can use a
- different storage engine for each table in your schema.
+ You are not restricted to using the same storage engine for an
+ entire server or schema. You can specify the storage engine for any
+ table. For example, an application might use mostly
+ <literal>InnoDB</literal> tables, with one <literal>CSV</literal>
+ table for exporting data to a spreadsheet and a few
+ <literal>MEMORY</literal> tables for temporary workspaces.
</para>
<para>
@@ -257,11 +279,8 @@
<para>
The various storage engines provided with MySQL are designed with
- different use cases in mind. To use the pluggable storage
- architecture effectively, it is good to have an idea of the
- advantages and disadvantages of the various storage engines. The
- following table provides an overview of some storage engines
- provided with MySQL:
+ different use cases in mind. The following table provides an
+ overview of some storage engines provided with MySQL:
</para>
<para condition="dynamic:productfeatures:fullsummary" role="5.6:storageengines:myisam,memory,innodb,archive,ndb">
@@ -272,6 +291,8 @@
<title>Comparing Transaction and Nontransaction Engines</title>
+<!-- JDR: emphasize InnoDB rather than new acronyms like TST / NTST. -->
+
<para>
Transaction-safe tables (TSTs) have several advantages over
nontransaction-safe tables (NTSTs):
@@ -333,6 +354,8 @@
storage engines, see <xref linkend="commit"/>.
</para>
+<!-- JDR: emphasize the use cases (temp tables, read-only / read-mostly) rather than nebulous "advantages". -->
+
<para>
Nontransaction-safe tables have several advantages of their own,
all of which occur because there is no transaction overhead:
@@ -342,19 +365,19 @@
<listitem>
<para>
- Much faster
+ Much faster.
</para>
</listitem>
<listitem>
<para>
- Lower disk space requirements
+ Lower disk space requirements.
</para>
</listitem>
<listitem>
<para>
- Less memory required to perform updates
+ Less memory required to perform updates.
</para>
</listitem>
@@ -472,18 +495,26 @@
<title>Setting the Storage Engine</title>
<para>
- When you create a new table, you can specify which storage engine
- to use by adding an <literal>ENGINE</literal> table option to the
- <literal role="stmt">CREATE TABLE</literal> statement:
+ When you create a new table, you can specify a special-purpose
+ storage engine to use by adding an <literal>ENGINE</literal> table
+ option to the <literal role="stmt">CREATE TABLE</literal>
+ statement:
</para>
-<programlisting>CREATE TABLE t (i INT) ENGINE = INNODB;</programlisting>
+<programlisting>
+-- ENGINE=INNODB not needed unless you have set a different default storage engine.
+CREATE TABLE t1 (i INT) ENGINE = INNODB;
+-- Simple table definitions can be switched from one to another.
+CREATE TABLE t2 (i INT) ENGINE = CSV;
+-- Some storage engines have their own specific clauses in CREATE TABLE syntax.
+CREATE TABLE t3 (i INT) ENGINE = MEMORY USING BTREE;
+</programlisting>
<para>
- If you omit the <literal>ENGINE</literal> option, the default
+ When you omit the <literal>ENGINE</literal> option, the default
storage engine is used. The default engine is
- <literal role="se">InnoDB</literal> as in MySQL ¤t-series;.
- You can specify the default engine by using the
+ <literal role="se">InnoDB</literal> in MySQL ¤t-series;. You
+ can specify the default engine by using the
<option role="mysqld">--default-storage-engine</option> server
startup option, or by setting the
<literal>default-storage-engine</literal> option in the
@@ -491,13 +522,13 @@
</para>
<para>
- You can set the default storage engine to be used during the
- current session by setting the
- <literal role="sysvar">storage_engine</literal> variable:
+ You can set the default storage engine for the current session by
+ setting the <literal role="sysvar">storage_engine</literal>
+ variable:
</para>
<programlisting>
-SET storage_engine=MYISAM;
+SET storage_engine=NDBCLUSTER;
</programlisting>
<para>
@@ -514,7 +545,7 @@
</para>
<programlisting>
-ALTER TABLE t ENGINE = MYISAM;
+ALTER TABLE t ENGINE = InnoDB;
</programlisting>
<para>
@@ -531,24 +562,22 @@
<para>
If you try to use a storage engine that is not compiled in or that
is compiled in but deactivated, MySQL instead creates a table
- using the default storage engine. This behavior is convenient when
- you want to copy tables between MySQL servers that support
- different storage engines. (For example, in a replication setup,
- perhaps your master server supports transactional storage engines
- for increased safety, but the slave servers use only
- nontransactional storage engines for greater speed.)
+ using the default storage engine. For example, in a replication
+ setup, perhaps your master server uses <literal>InnoDB</literal>
+ tables for maximum safety, but the slave servers use other storage
+ engines for speed at the expense of durability or concurrency.
</para>
<para>
- This automatic substitution of the default storage engine for
- unavailable engines can be confusing for new MySQL users. A
- warning is generated whenever a storage engine is automatically
- changed. To prevent this from happening if the desired engine is
- unavailable, enable the
+ By default, a warning is generated whenever
+ <literal role="stmt">CREATE TABLE</literal> or
+ <literal role="stmt">ALTER TABLE</literal> cannot use the default
+ storage engine. To prevent confusing, unintended behavior if the
+ desired engine is unavailable, enable the
<literal role="sqlmode">NO_ENGINE_SUBSTITUTION</literal> SQL mode.
- In this case, an error occurs instead of a warning and the table
- is not created or altered if the desired engine is unavailable.
- See <xref linkend="server-sql-mode"/>.
+ If the desired engine is unavailable, this setting produces an
+ error instead of a warning, and the table is not created or
+ altered. See <xref linkend="server-sql-mode"/>.
</para>
<para>
@@ -564,13 +593,26 @@
<xref linkend="identifier-mapping"/>.
</para>
- <para>
- A database may contain tables of different types. That is, tables
- need not all be created with the same storage engine.
- </para>
-
</section>
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-innodb.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-myisam.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-memory.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-csv.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-archive.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-blackhole.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-merge.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-federated.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-example.xml"/>
+
<section id="pluggable-storage-overview">
<title>Overview of MySQL Storage Engine Architecture</title>
@@ -823,22 +865,4 @@
</section>
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-myisam.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-innodb.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-merge.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-memory.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-example.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-federated.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="dynxml-local-se-archive.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-csv.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="se-blackhole.xml"/>
-
</chapter>
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r26243 - trunk/refman-5.6 | john.russell | 16 May |
