Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

Chapter 14. Storage Engines and Table Types

Table of Contents

14.1. The MyISAM Storage Engine
14.1.1. MyISAM Startup Options
14.1.2. Space Needed for Keys
14.1.3. MyISAM Table Storage Formats
14.1.4. MyISAM Table Problems
14.2. The InnoDB Storage Engine
14.2.1. InnoDB Overview
14.2.2. InnoDB Contact Information
14.2.3. InnoDB Configuration
14.2.4. InnoDB Startup Options and System Variables
14.2.5. Creating the InnoDB Tablespace
14.2.6. Creating and Using InnoDB Tables
14.2.7. Adding and Removing InnoDB Data and Log Files
14.2.8. Backing Up and Recovering an InnoDB Database
14.2.9. Moving an InnoDB Database to Another Machine
14.2.10. InnoDB Transaction Model and Locking
14.2.11. InnoDB Performance Tuning Tips
14.2.12. Implementation of Multi-Versioning
14.2.13. InnoDB Table and Index Structures
14.2.14. InnoDB File Space Management and Disk I/O
14.2.15. InnoDB Error Handling
14.2.16. Restrictions on InnoDB Tables
14.2.17. InnoDB Troubleshooting
14.3. The MERGE Storage Engine
14.3.1. MERGE Table Problems
14.4. The MEMORY (HEAP) Storage Engine
14.5. The BDB (BerkeleyDB) Storage Engine
14.5.1. Operating Systems Supported by BDB
14.5.2. Installing BDB
14.5.3. BDB Startup Options
14.5.4. Characteristics of BDB Tables
14.5.5. Things We Need to Fix for BDB
14.5.6. Restrictions on BDB Tables
14.5.7. Errors That May Occur When Using BDB Tables
14.6. The EXAMPLE Storage Engine
14.7. The FEDERATED Storage Engine
14.7.1. Description of the FEDERATED Storage Engine
14.7.2. How to use FEDERATED Tables
14.7.3. Limitations of the FEDERATED Storage Engine
14.8. The ARCHIVE Storage Engine
14.9. The CSV Storage Engine
14.10. The BLACKHOLE Storage Engine

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 non-transaction-safe tables:

  • MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.

  • The MEMORY storage engine provides in-memory tables. The MERGE storage engine allows a collection of identical MyISAM tables to be handled as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both are also included in MySQL by default.

    Note: The MEMORY storage engine formerly was known as the HEAP engine.

  • The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB is also included by default in all MySQL 5.1 binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

  • The EXAMPLE storage engine is a “stub” 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.

  • NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in MySQL-Max 5.1 binary distributions. This storage engine is currently supported on Linux, Solaris, and Mac OS X only. We intend to add support for this engine on other platforms, including Windows, in future MySQL releases.

  • The ARCHIVE storage engine is used for storing large amounts of data without indexes with a very small footprint.

  • The CSV storage engine stores data in text files using comma-separated values format.

  • The BLACKHOLE storage engine accepts but does not store data and retrievals always return an empty set.

  • The FEDERATED storage engine stores data in a remote database. Currently, it works with MySQL only, using the MySQL C Client API. In future releases, we intend to enable it to connect to other data sources using other drivers or client connection methods.

This chapter describes each of the MySQL storage engines except for NDB Cluster, which is covered in Chapter 16, MySQL Cluster.

When you create a new table, you can specify which storage engine to use by adding an ENGINE table option to the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = INNODB;

If you omit the ENGINE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the storage_engine or table_type system variable.

When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB storage engine can be selected as the default instead of MyISAM. See Section 2.3.4.6, “The Database Usage Dialog”.

To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

ALTER TABLE t ENGINE = MYISAM;

See Section 13.1.5, “CREATE TABLE Syntax”, and Section 13.1.2, “ALTER TABLE Syntax”.

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, usually MyISAM. 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 non-transactional storage engines for greater speed.)

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.

For new tables, MySQL always creates an .frm file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the storage engine. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.

A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):

  • They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

  • You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).

  • You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

  • If an update fails, all of your changes are reverted. (With non-transaction-safe tables, all changes that have taken place are permanent.)

  • Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. For information about this and other problems that can occur in transactions that use mixed storage engines, see Section 13.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:

  • Much faster

  • Lower disk space requirements

  • Less memory required to perform updates


 
 
  Published under the terms of the GNU General Public License Design by Interspire