What is Storage engines?
Storage engines used to be called table types.MySQL allows you to choose from any of several storage engines when creating a table. Different table types are managed by different storage engines, each of which has specific characteristics.Locking and concurrency as well as transactions—two concepts that are critical to understanding some of the major differences between the various engines.Storage engines, They’re responsible for the storage and retrieval of all data stored “in” MySQL. Like the various filesystems available for Linux, each storage engine has its own benefits and drawbacks.
All tables managed by MySQL Server have certain similarities. For example, every table in a database has a format (.frm) file in the database directory. This file, which stores the definition of the table's structure, is created by the server. Tables have differences as well, which are tied to the storage engines that the server uses to manage table contents. Each storage engine has a particular set of operational characteristics. For example, engines may create additional disk files to accompany the .frm files, but the types of files that they create to manage data and index storage vary per engine. Storage engines differ in other ways as well, such as in the way that they use locking to manage query contention, or in whether the tables that they provide are transactional or non-transactional. These engine properties have implications for query processing performance, concurrency, and deadlock prevention.
Before you can use a given storage engine, it must be compiled into the server and enabled. MySQL Server uses a modular architecture: Each storage engine is a software module that is compiled into the server. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time.Some storage engines are always available, such as MyISAM, MERGE, and MEMORY. Other engines are optional. Support for optional engines typically can be selected when MySQL is configured and built. Compiled-in optional engines also typically can be enabled or disabled with a server startup option. For example, the InnoDB storage engine is included in all binary distributions. If you build MySQL from source, InnoDB is included by default unless you specify the
--without-innodb configuration option. For a server that has the InnoDB storage engine included, support may be disabled at startup with the --skip-innodb option.
To reduce memory use, don't configure unneeded storage engines into the server. This requires that you compile MySQL from source rather than using a precompiled binary distribution. If you are using a binary distribution that includes compiled-in optional engines that you don't need, disable them at runtime
To see what storage engines are compiled into your server and whether they are available at runtime, use the SHOW ENGINES statement
mysql> SHOW ENGINES\G
Classification of Storage Engines.
1) The MyISAM Engine.
2) The MERGE Engine.
3) The InnoDB Engine.
4) The MEMORY Engine.
5) The FEDERATED Engine.
6) The Cluster Storage Engine.
7) Other Storage Engines.
1) The MyISAM Engine.
The MyISAM storage engine manages tables that have the following characteristics:
*
On disk, MySQL represents each MyISAM table using three files: a format file that stores the definition of the table structure, a data file that stores the contents of table rows, and an index file that stores any indexes on the table. These files are distinguished from one another by their suffixes. For example, the format, data, and index files for a table named mytable are called mytable.frm, mytable.MYD, and mytable.MYI. MySQL normally stores all three files in the database directory for the database that contains the table. On systems that support appropriate symlinking capabilities, MyISAM table data and index files can be placed in a different location than the database directory.
*
MyISAM has the most flexible AUTO_INCREMENT column handling of all the storage engines.
*
MyISAM tables can be used to set up MERGE tables.
*
MyISAM tables can be converted into fast, compressed, read-only tables to save space.
*
MyISAM supports FULLTEXT searching and spatial data types.
*
MySQL manages contention between queries for MyISAM table access using table-level locking. Query performance is very fast for retrievals. Multiple queries can read the same table simultaneously. For a write query, an exclusive table-level lock is used to prevent use of the table by other read or write queries, leading to reduced performance in environments with a mix of read and write queries. Deadlock cannot occur with table-level locking. (Deadlock occurs when two or more queries are blocked, or stopped from completing, because each is waiting for one of the others to finish.)
*
You can influence the scheduling mechanism for queries that use MyISAM tables by using a query modifier such as LOW_PRIORITY or HIGH_PRIORITY. Inserts into a table can be buffered on the server side until the table isn't busy by using INSERT DELAYED; this allows the client to proceed immediately instead of blocking until the insert operation completes.
*
The table storage format is portable, so table files can be copied directly to another host and used by a server there.
*
You can specify that a MyISAM table must be able to hold at least a certain number of rows, which allows MyISAM to adjust the table's internal row pointer size accordingly. It's also possible to configure the default pointer size that the server uses.
*
When loading data into an empty MyISAM table, you can disable updating of non-unique indexes and enable the indexes after loading. This is faster than updating the indexes for each row inserted. In fact, when LOAD DATA INFILE is used for loading an empty MyISAM table, it automatically disables and enables index updating. LOAD DATA INFILE is faster than INSERT anyway, and this optimization speeds it up even more.
*
If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.
MyISAM tables use the indexed sequential access method for indexing, as did the older ISAM table format. MyISAM offers better performance and more features than ISAM, so MyISAM is preferred over ISAM, and ISAM is unavailable as of MySQL 5.
MyISAM was introduced in MySQL 3.23.0 and has been the built-in default storage engine since (although you can change the default engine at server startup or while the server runs). Because MyISAM is the built-in default engine, it is always available and cannot be disabled
MyISAM Locking Characteristics
MyISAM locking occurs at the table level. This is not as desirable as page or row locking for concurrency in a mixed read/write environment. However, deadlock cannot occur with table locking as it can with page or row locking.
When processing queries on MyISAM tables, the server manages contention for the tables by simultaneous clients by implicitly acquiring any locks it needs. You can also lock tables explicitly with the LOCK TABLES and UNLOCK TABLES statements. Explicit table locking has concurrency and performance advantages over implicit locking in certain situations.
MyISAM tables support concurrent inserts. If a MyISAM table has no holes in the middle resulting from deleted or updated records, inserts always take place at the end of the table and can be performed while other clients are reading the table. Concurrent inserts can take place even for a table that has been read-locked explicitly if the locking client acquired a READ LOCAL lock rather than a regular READ lock.
If a table does have holes, concurrent inserts cannot be performed. However, you can remove the holes by using OPTIMIZE TABLE to defragment the table. (Note that a record deleted from the end of the table does not create a hole and does not prevent concurrent inserts.)
For applications that use MyISAM tables, you can change the priority of statements that retrieve or modify data. This can be useful in situations where the normal scheduling priorities do not reflect the application's requirements.
By default, the server schedules queries for execution as follows:
*
Write requests (such as UPDATE and DELETE statements) take priority over read requests (such as SELECT statements).
*
The server tries to perform write requests in the order that it receives them.
However, if a table is being read from when a write request arrives, the write request cannot be processed until all current readers have finished. Any read requests that arrive after the write request must wait until the write request finishes, even if they arrive before the current readers finish. That is, a new read request by default does not jump ahead of a pending write request.
When working with MyISAM tables, certain scheduling modifiers are available to change the priority of requests:
*
The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting.
*
HIGH_PRIORITY may be used with a SELECT statement to move it ahead of updates and ahead of other SELECT statements that do not use the HIGH_PRIORITY modifier.
*
DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they're done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statement rather than waiting until it completes.
Consider an application consisting of a logging process that uses INSERT statements to record information in a log table, and a summary process that periodically issues SELECT queries to generate reports from the log table. Normally, the server will give table updates priority over retrievals, so at times of heavy logging activity, report generation might be delayed. If the application places high importance on having the summary process execute as quickly as possible, it can use scheduling modifiers to alter the usual query priorities. Two approaches are possible:
*
To elevate the priority of the summary queries, use SELECT HIGH_PRIORITY rather than SELECT with no modifier. This will move the SELECT ahead of pending INSERT statements that have not yet begin to execute.
*
To reduce the priority of record logging statements, use INSERT with either the LOW_PRIORITY or DELAYED modifier.
If you use DELAYED, keep the following points in mind:
*
Delayed rows tend to be held for a longer time on a very busy server than on a lightly loaded one.
*
If a crash occurs while the server is buffering delayed rows in memory, those rows are lost.
The implication is that DELAYED is more suitable for applications where loss of a few rows is not a problem, rather than applications for which each row is critical. For example, DELAYED can be appropriate for an application that logs activity for informational purposes only and for which it is not important if a small number of rows is lost.
MyISAM Row-Storage Formats
The MyISAM storage engine has the capability of storing rows in three formats: fixed-row, dynamic-row, and compressed. These formats have differing characteristics:
Fixed-row format:
*
All rows have the same size.
*
Rows are stored within the table at positions that are multiples of the row size, making them easy to look up.
*
Fixed-size rows take more space.
Dynamic-row format:
*
Rows take varying amounts of space.
*
Rows cannot be looked up as efficiently.
*
Dynamic-rows tables usually take less space because rows are not padded to a fixed size.
*
Fragmentation can occur more easily than for fixed-row tables.
Compressed format:
*
Tables are packed to save space.
*
Storage is optimized for quick retrieval.
*
Tables are read-only.
back to top
2) The MERGE Engine
The MERGE storage engine manages tables that have the following characteristics:
*
A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.
*
Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.
*
A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.
*
MySQL manages contention between queries for MERGE table access using table-level locking (including locking of the underlying MyISAM tables). Deadlock cannot occur.
*
A MERGE table is portable because the .MRG file is a text file and the MyISAM tables that it names are portable.
*
The MERGE engine supports SELECT, DELETE, UPDATE, and INSERT statements. For INSERT, the CREATE TABLE statement can specify whether records should be inserted into the first or last table, or disallowed.
MERGE tables do have some disadvantages:
*
They increase the number of file descriptors required because each of the underlying tables must be opened along with the MERGE table.
*
It's slower to read indexes because MySQL has to search the indexes of multiple tables.
The following example demonstrates how to create a MERGE table. It creates MyISAM tables that have the same structure, and populates them with information about countries in North America and South America, respectively. From these tables, a MERGE table is created that can be used to access the combined information:
MERGE Locking Characteristics
The MERGE storage engine uses table-level locking. However, because a MERGE table is defined in terms of other tables, MERGE locking involves locks on those tables as well:
*
When the MERGE engine acquires a lock for a MERGE table, it acquires a lock for all the underlying MyISAM tables. Thus, all the tables are locked together.
*
The underlying MyISAM tables are read-locked when you issue a SELECT statement for a MERGE table.
*
The underlying MyISAM tables are write-locked when you issue a statement that modifies a MERGE table, such as INSERT or DELETE.
*
To explicitly lock a MERGE table with LOCK TABLES, it is sufficient to lock just that table. You need not lock the underlying MyISAM tables as well.
back to top
3)The InnoDB Engine
The InnoDB storage engine manages tables that have the following characteristics:
*
Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. The InnoDB tablespace is a logical single storage area that is made up of one or more files or partitions on disk. By default, InnoDB uses a single tablespace that is shared by all InnoDB tables. The tablespace is stored in machine-independent format. It is implemented such that table sizes can exceed the maximum file size allowed by the filesystem. It is also possible to configure InnoDB to create each table with its own tablespace.
*
InnoDB supports transactions, with commit and rollback. It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another.
*
InnoDB provides auto-recovery after a crash of the MySQL server or the host on which the server runs.
*
MySQL manages query contention for InnoDB tables using multi-versioning and row-level locking. Multi-versioning gives each transaction its own view of the database. This, combined with row-level locking, keeps contention to a minimum. The result is good query concurrency even if clients are performing a mix of reads and writes. However, it's possible for deadlock to occur.
*
InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.
*
The tablespace storage format is portable, so InnoDB files can be copied directly to another host and used by a server there.
Support for the InnoDB storage engine is a standard feature in binary distributions. If you build MySQL from source, InnoDB is included unless you explicitly use the --without-innodb configuration option.
If a given MySQL server has the InnoDB storage engine compiled in, but you're sure that you won't need InnoDB tables, you can disable InnoDB support at runtime by starting the server with the --skip-innodb option. Disabling InnoDB reduces the server's memory re-quirements because it need not allocate any InnoDB-related data structures. Disabling InnoDB also reduces disk requirements because no InnoDB tablespace or log files need be allocated
The InnoDB Tablespace and Logs
InnoDB operates using two primary disk-based resources: a tablespace for storing table contents, and a set of log files for recording transaction activity.
Each InnoDB table has a format (.frm) file in the database directory of the database to which the table belongs. This is the same as tables managed by any other MySQL storage engine, such as MyISAM. However, InnoDB manages table contents (data rows and indexes) on disk differently than does the MyISAM engine. By default, InnoDB uses a shared "tablespace," which is one or more files that form a single logical storage area. All InnoDB tables are stored together within the tablespace. There are no table-specific data files or index files for InnoDB the way there are for MyISAM tables. The tablespace also contains a rollback segment. As transactions modify rows, undo log information is stored in the rollback segment. This information is used to roll back failed transactions.
Although InnoDB treats the shared tablespace as a single logical storage area, it can consist of one file or multiple files. Each file can be a regular file or a raw partition. The final file in the shared tablespace can be configured to be auto-extending, in which case InnoDB expands it automatically if the tablespace fills up. Because the shared tablespace is used for InnoDB tables in all databases (and thus is not database specific), tablespace files are stored by default in the server's data directory, not within a particular database directory.
If you do not want to use the shared tablespace for storing table contents, you can start the server with the --innodb_file_per_table option. In this case, for each new table that InnoDB creates, it sets up an .ibd file in the database directory to accompany the table's .frm file. The .ibd file acts as the table's own tablespace file and InnoDB stores table contents in it. (The shared tablespace still is needed because it contains the InnoDB data dictionary and the rollback segment.)
Use of the --innodb_file_per_table option does not affect accessibility of any InnoDB tables that may already have been created in the shared tablespace. Those tables remain accessible.
In addition to its tablespace files, the InnoDB storage engine manages a set of InnoDB-specific log files that contain information about ongoing transactions. As a client performs a transaction, the changes that it makes are held in the InnoDB log. The more recent log contents are cached in memory. Normally, the cached log information is written and flushed to log files on disk at transaction commit time, though that may also occur earlier.
If a crash occurs while the tables are being modified, the log files are used for auto-recovery: When the MySQL server restarts, it reapplies the changes recorded in the logs, to ensure that the tables reflect all committed transactions.
InnoDB and ACID Compliance
The InnoDB storage engine provides transactional capabilities. A transaction is a logical grouping of statements that is handled by the database server as a single unit. Either all the statements execute successfully to completion or all modifications made by the statements are discarded if an error occurs. Transactional systems often are described as being ACID compliant, where "ACID" stands for the following properties:
*
Atomic. All the statements execute successfully or are canceled as a unit.
*
Consistent. A database that is in a consistent state when a transaction begins is left in a consistent state by the transaction.
*
Isolated. One transaction does not affect another.
*
Durable. All the changes made by a transaction that completes successfully are recorded properly in the database. Changes are not lost.
InnoDB satisfies the conditions for ACID compliance, assuming that its log flushing behavior is set appropriately. InnoDB can be configured for log flushing that provides ACID compliance, or for flushing that gains some in performance at the risk of losing the last few transactions if a crash occurs. By default, InnoDB log flushing is set for ACID compliance.
The InnoDB TRansaction Model
Multiple clients may execute transactions concurrently, but any given client performs transactions serially, one after the other. The client determines when each of its transactions begins and ends by controlling its autocommit mode. MySQL initializes each client to begin with autocommit mode enabled. This causes each statement to be committed immediately. In transactional terms, this means that each statement is a separate transaction. To group multiple statements as a single transaction so that they succeed or fail as a unit, autocommit mode must be disabled. There are two ways to do this:
*
The first method is to disable autocommit mode explicitly:
SET AUTOCOMMIT = 0;
With autocommit disabled, any following statements become part of the current transaction until you end it by issuing a COMMIT statement to accept the transaction and commit its effects to the database, or a ROLLBACK statement to discard the transaction's effects.
When you disable autocommit explicitly, it remains disabled until you enable it again as follows:
SET AUTOCOMMIT = 1;
*
The second method is to suspend the current autocommit mode by beginning a transaction explicitly. Any of the following statements begins a transaction:
START TRANSACTION; BEGIN; BEGIN WORK;
START TRANSACTION is standard SQL syntax. The others are synonyms. (The BEGIN statement that begins a transaction is different from the BEGIN/END syntax that is used to write compound statements in stored routines and triggers. The latter is described in Section 18.5.1, "Compound Statements.")
After beginning a transaction with any of those statements, autocommit remains disabled until you end the transaction by committing it or by rolling it back. The autocommit mode then reverts to the value it had prior to the start of the transaction.
If a client connection closes while the client has a transaction pending, InnoDB rolls back the transaction implicitly. This occurs regardless of whether the connection closes normally or abnormally.
Because a statement that begins a transaction implicitly commits any current transaction, transactions cannot be nested.
Transaction-control statements can affect explicit table locks. Use of START TRANSACTION or its synonyms causes an implicit UNLOCK TABLES.
The MySQL server initializes each client connection to begin with autocommit enabled. Modifications to the autocommit mode made by a client to its connection persist only to the end of the connection. If a client disconnects and reconnects, the second connection begins with autocommit enabled, regardless of its setting at the end of the first connection
InnoDB Locking Characteristics
This section describes how InnoDB uses locks internally and some query modifiers you can use to affect locking.
InnoDB has the following general locking properties:
o
InnoDB does not need to set locks to achieve consistent reads because it uses multi-versioning to make them unnecessary: Transactions that modify rows see their own versions of those rows, and the undo logs allow other transactions to see the original rows. Locking reads may be performed by adding locking modifiers to SELECT statements.
o
When locks are necessary, InnoDB uses row-level locking. In conjunction with multi-versioning, this results in good query concurrency because a given table can be read and modified by different clients at the same time. Row-level concurrency properties are as follows:
+
Different clients can read the same rows simultaneously.
+
Different clients can modify different rows simultaneously.
+
Different clients cannot modify the same row at the same time. If one transaction modifies a row, other transactions cannot modify the same row until the first transaction completes. Other transactions cannot read the modified row, either, unless they are using the READ UNCOMMITTED isolation level. That is, they will see the original unmodified row.
o
During the course of a transaction, InnoDB may acquire row locks as it discovers them to be necessary. However, it never escalates a lock (for example, by converting it to a page lock or table lock). This keeps lock contention to a minimum and improves concurrency.
o
Deadlock can occur. Deadlock is a situation in which each of two transactions is waiting for the release of a lock that the other holds. For example, if two transactions each lock a different row, and then try to modify the row locked by the other, they can deadlock. Deadlock is possible because InnoDB does not acquire locks during a transaction until they are needed. When InnoDB detects a deadlock, it terminates and rolls back one of the deadlocking transactions. It tries to pick the transaction that has modified the smallest number of rows. If InnoDB does not detect deadlock, the deadlocked transactions eventually begin to time out and InnoDB rolls them back as they do.
InnoDB supports two locking modifiers that may be added to the end of SELECT statements. They acquire shared or exclusive locks and convert non-locking reads into locking reads:
+
With LOCK IN SHARE MODE, InnoDB locks each selected row with a shared lock. Other transactions can still read the selected rows, but cannot update or delete them until the first transaction releases the locks, which happens when the transaction finishes. Also, if the SELECT will select rows that have been modified in an uncommitted transaction, IN SHARE MODE will cause the SELECT to block until that transaction commits.
+
With FOR UPDATE, InnoDB locks each selected row with an exclusive lock. This is useful if you intend to select and then modify a set of rows, because it prevents other transactions from reading or writing the rows until the first transaction releases the locks, which happens when the transaction finishes.
In the REPEATABLE READ isolation level, you can add LOCK IN SHARE MODE to SELECT operations to force other transactions to wait for your transaction if they want to modify the selected rows. This is similar to operating at the SERIALIZABLE isolation level, for which InnoDB implicitly adds LOCK IN SHARE MODE to SELECT statements that have no explicit locking modifier.
InnoDB Isolation Levels, Multi-Versioning, and Concurrency
As mentioned earlier, multiple transactions may be executing concurrently within the server, one transaction per client. This has the potential to cause problems: If one client's transaction changes data, should transactions for other clients see those changes or should they be isolated from them? The transaction isolation level determines the level of visibility between transactions—that is, the ways in which simultaneous transactions interact when accessing the same data. This section discusses the problems that can occur and how InnoDB implements isolation levels. Note that isolation level definitions vary among database servers, so the levels as implemented by InnoDB might not correspond exactly to levels as implemented in other database systems.
When multiple clients run transactions concurrently, three problems that may result are dirty reads, non-repeatable reads, and phantoms. These occur under the following circumstances:
+
A dirty read is a read by one transaction of uncommitted changes made by another. Suppose that transaction T1 modifies a row. If transaction T2 reads the row and sees the modification even though T1 has not committed it, that is a dirty read. One reason this is a problem is that if T1 rolls back, the change is undone but T2 does not know that.
+
A non-repeatable read occurs when a transaction performs the same retrieval twice but gets a different result each time. Suppose that T1 reads some rows and that T2 then changes some of those rows and commits the changes. If T1 sees the changes when it reads the rows again, it gets a different result; the initial read is non-repeatable. This is a problem because T1 does not get a consistent result from the same query
+
A phantom is a row that appears where it was not visible before. Suppose that T1 and T2 begin, and T1 reads some rows. If T2 inserts a new row and T1 sees that row when it reads again, the row is a phantom.
InnoDB implements four isolation levels that control the visibility of changes made by one transaction to other concurrently executing transactions:
+
READ UNCOMMITTED allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, non-repeatable reads, and phantoms to occur.
+
READ COMMITTED allows a transaction to see changes made by other transactions only if they've been committed. Uncommitted changes remain invisible. This isolation level allows non-repeatable reads and phantoms to occur.
+
REPEATABLE READ ensures that if a transaction issues the same SELECT twice, it gets the same result both times, regardless of committed or uncommitted changes made by other transactions. In other words, it gets a consistent result from different executions of the same query. In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows in the interval between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level.
+
SERIALIZABLE completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.
The essential difference between REPEATABLE READ and SERIALIZABLE is that with REPEATABLE READ, one transaction cannot modify rows another has modified, whereas with SERIALIZABLE, one transaction cannot modify rows if another has merely even read them.
Isolation levels are relevant only within the context of simultaneously executing transactions. After a given transaction has committed, its changes become visible to any transaction that begins after that.
InnoDB operates by default in REPEATABLE READ mode: Each transaction sees a view of the database that consists of all changes that have been committed by the time the transaction issues its first consistent read (such as a SELECT statement), plus any changes that it makes itself. It does not see any uncommitted changes, or committed changes made by transactions that begin later than itself.
InnoDB makes transaction isolation possible by multi-versioning. As transactions modify rows, InnoDB maintains isolation between them by maintaining multiple versions of the rows, and makes available to each transaction the appropriate version of the rows that it should see. Multiple versions of a row that has been changed can be derived from the current version of the row, plus the undo logs.
With multi-versioning, each transaction sees a view of the contents of the database that is appropriate for its isolation level. For example, with a level of REPEATABLE READ, the snapshot of the database that a transaction sees is the state of the database at its first read. One property of this isolation level is that it provides consistent reads: A given SELECT yields the same results when issued at different times during a transaction. The only changes the transaction sees are those it makes itself, not those made by other transactions. For READ COMMITTED, on the other hand, the behavior is slightly different. The view of the database that the transaction sees is updated at each read to take account of commits that have been made by other transactions since the previous read.
To set the server's default transaction isolation level at startup time, use the --transaction-isolation option. The option value should be READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to put the server in READ COMMITTED mode by default, put these lines in an option file:
4) The MEMORY Engine
The MEMORY storage engine manages tables that have the following characteristics:
+
Each MEMORY table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.
+
In-memory storage results in very fast performance.
+
MEMORY table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.
+
MEMORY tables use up memory (obviously), so they should not be used for large tables.
+
MySQL manages query contention for MEMORY tables using table-level locking. Deadlock cannot occur.
+
MEMORY tables cannot contain TEXT or BLOB columns.
The MEMORY stored engine formerly was called the HEAP engine. You might still see HEAP in older SQL code, and MySQL Server still recognizes HEAP for backward compatibility.
MEMORY Indexing Options
The MEMORY storage engine supports two indexing algorithms, HASH and BTREE:
+
MEMORY tables use hash indexes by default. This index algorithm provides very fast lookups for all operations that use a unique index. However, hash indexes are usable only for comparisons that use the = or <=> operator.
+
The BTREE index algorithm is preferable if the indexed column will be used with comparison operators other than = or <=>. For example, BTREE can be used for range searches such as id < 100 or id BETWEEN 200 AND 300.
5) The FEDERATED Engine
The FEDERATED storage engine is new in MySQL 5. It allows a MySQL server to use tables from other MySQL servers and to make them available to its clients as though the tables were its own. The clients need not connect directly to the other servers to access the tables.
One benefit provided by this capability is that you can use a single query to access tables that are managed by different servers. It's not necessary to connect to each server and retrieve data separately for each one. For example, you can perform a join between tables from different servers. FEDERATED is new, so much remains to be done in terms of optimizing such queries, but the fact that they now can be issued is significant.
The FEDERATED storage engine manages tables that have the following characteristics:
+
Each FEDERATED table is represented on disk only by an .frm format file in the database directory.
+
The FEDERATED storage engine does not support transactions.
+
The FEDERATED storage engine supports SELECT, DELETE, UPDATE, and INSERT statements.
+
MySQL does not use any locking for FEDERATED tables.
Suppose that there is an instance of the world database located on the remote host world.example.com and that its City table has this definition:
CREATE TABLE City ( ID INT NOT NULL AUTO_INCREMENT, Name CHAR(35) NOT NULL, CountryCode CHAR(3) NOT NULL, District CHAR(20) NOT NULL, Population INT NOT NULL, PRIMARY KEY (ID) ) ENGINE = MyISAM;
If the world database on the remote host can be accessed by connecting to the MySQL server there with a username and password of wuser and wpass, a FEDERATED table can be created on the local host that allows the remote City table to be accessed as though it were local. To create a local FEDERATED table, use a definition similar to that of the remote table, but make two changes. First, use an ENGINE = FEDERATED table option. Second, include a COMMENT table option that specifies a connection string. The connection string indicates to the local server where the remote table is located and how to connect to the remote server. Connection string format is as follows, where optional parts are shown in square brackets:
mysql://user_name[:password]@host_name[:port]/db_name/table_name
The username, password, hostname, and port number specify what connection parameters to use for connecting to the remote server. The database and table names indicate which table to access on that server.
6) The Cluster Storage Engine
The NDBCluster storage engine originally appeared in MySQL 4.1. Using the cluster engine is complex, and for the purposes of MySQL 5 certification you are not expected to know the details of how to set up and use NDBCluster. You are, however, expected to know the general properties of the cluster engine as compared to other storage engines.
In literature, you will see the two terms "NDB Cluster" (or just "NDB") and "MySQL Cluster." NDB Cluster refers to the cluster technology and is thus specific to the storage engine itself, whereas MySQL Cluster refers to a group of one or more MySQL servers that works as a "front end" to the NDB Cluster engine. That is, a MySQL Cluster consists of a group of one or more server hosts, each of which is usually running multiple processes that include MySQL servers, NDB management processes, and NDB database storage nodes. Cluster processes are also referred to as "cluster nodes" or just "nodes."
The cluster engine does not run internally in MySQL Server, but is, instead, one or more separate processes running outside MySQL Server (perhaps even on different server hosts). In effect, MySQL Server provides the SQL interface to the cluster processes. From the perspective of the server, however, NDBCluster is just another storage engine, like the MyISAM and the InnoDB engines.
NDB Cluster consists of several database processes (nodes) running on one or more physical server hosts. It manages one or more in-memory databases in a shared-nothing system. In-memory means that all the information in each database is kept in the RAM of the machines making up the cluster. (Updates are written to disk so that they are not lost if problems occur.) Shared-nothing means that the cluster is set up in such a way that no hardware components (such as disks) are shared among two nodes.
The NDB cluster engine is a transactional storage engine, like the InnoDB storage engine.
The following list describes the main reasons to consider using MySQL Cluster:
+
High availability: All records are available on several nodes. If one node fails (for example, because the server host stops working), the same data can be gotten from another node. Spreading copies of the data across multiple nodes also makes it possible to have replicas of the data in two or more widely distributed locations.
+
Scalability: If the load becomes too high for the current set of nodes, extra nodes can be added and the system will reconfigure itself to make data available on more nodes, reducing the load on each individual node.
+
High performance: All records are stored in memory, making data retrieval extremely fast. This does not mean that information is lost if the cluster is shut down (as is the case for tables created with the MEMORY storage engine). All updates are written to disk, and are available when the cluster is restarted.
