Default storage engines shipped in mysql
Mysql runs on different strorage engines , they play different roles as they were developed by different organizations ; so do not limit yourself to one storage engine, go deep and get the maximum of storage engines.
By default, MySQL official binaries include a number of different built-in storage engines. The following
are supplied and enabled by default in a MySQL 5.1 binary distribution:
Some of the different features of storage engines include:
Different index methods
Clustered indexes, primary
Transactional and non- transactional
Persistent and non-persistent
Table and row level
MySQL 5.1, MySQL offers the pluggable storage engine architecture (PSEA), where it is possible for a vendor to provide a runtime storage engine that can be loaded dynamically in an operational environment. While the theory enables this, in practice only a few engines have been able to achieve it.
MySQL as a relational database offers a unique feature in the management of data — it offers different storage engines that define different characteristics for the persistence and retrieval of your information. Each storage engine has relative strengths and weaknesses; the choice of one engine for one task may not be ideal for another. MySQL supports the use of multiple storage engines in a single schema; however, complexity in execution and functionality such as transaction support and backup strategy
are all affected.
Command for checking which engines are available:
This would display all available engines in your server.
The SHOW.ENGINES command and the INFORMATION_SCHEMA.ENGINES table provide information
on what engines are available in your current MySQL instance. This includes details of whether the
engine is supported (for example, look at FEDERATED section in the code that follows) and whether
the engine is transactional or non-transactional.
Characteristics of MyISAM storage engine:
MyISAM is the default storage engine of MySQL 5.1. This engine is an implementation of the welldefi
ned database storage ISAM architecture and has been available since MySQL 3.x.
Key Features of MyISAM:
- Supports FULLTEXT index
- Btree based indexes
- Fast insert rate
- 16k data pages
- 4k index pages
Important Parameters used in myIsam:
key_buffer_size: This buffer is used for holding data from MyISAM indexes. MyISAM can also support the defi nition of multiple index caches and enables the ability for pinning of specifi c table indexes per named buffer.
table_cache: This buffer holds information of open tables that are used when running queries.
While applicable to all storage engines, due to additional fi les, it is important to tune this parameter when there are a lot of tables and complex queries.
bulk_insert_buffer_size: This buffer is used for improving INSERT statements with a large number of VALUES or INSERT…SELECT as well as LOAD DATA INFILE.
myisam_recover: This parameter defines the storage engine default recovery mode. A recommended value is FORCE, BACKUP.
A MyISAM table is represented as three separate fi les in the fi le system located in the defi ned data directory for the MySQL instance. These files are:
table.frm: This is the table format defi nition file.
table.MYD: This is the MyISAM data file.
table.MYI: This is the MyISAM index file.
MyISAM has three different row formats. By default, MyISAM determines whether to use Fixed or
Dynamic format based on the column definitions specified in the table. The Fixed row format provides a calculation to determine the offset of the row within the data file, and can provide a small improvement in performance.
When to Use MyISAM:
MyISAM can be an ideal engine in a high read environment, or in a high write environment, but is not suited for a read/write environment. The key problem is that DML statements cause table-level locking. When this occurs all pending reads are blocked until the DML statement is completed.
You can see this in operation by running a benchmark that executes a large number of repeating SELECT statements. When reviewing the SHOW PROCESS LIST you will not see a state of Locked.
When you introduce a single UPDATE, for example, that takes time to execute, you will see all future SELECT statements also displayed as Locked.
Perhaps the greatest problem with MyISAM is its lack of data integrity during a MySQL instance
crash. MyISAM achieves its high volume write throughput in a number of ways. One way is the lack
of transactions, and therefore the lack of overhead to provide consistency during rollback. The second is the lack of disk synchronization of index data during DML statements. In the example of an INSERT statement, the data is written and synced on disk, but the index data is not. It is held in the key_buffer and written to disk, but not synced.
In the event of a server crash, the index file may be inconsistent, and may require recovery via the
REPAIR TABLE command. There are several problems here. The first is that you generally do not
know the extent of corruption until the table and index is accessed. Although there is a CHECK
TABLE command, this is only an indicator, and can provide a false positive. The second is that recovery time is dependent on your database size. As your database grows, the time for possible recovery also increases. This lack of reproducibility in time can cause great problems in planning and support of a timely recovery.
The future replacement to MyISAM is the Maria storage engine, which provides the benefits of MyISAM and includes full crash recovery.
Though many installations move to using transactional tables for all data, it is not possible to eliminate MyISAM because this is used internally by MySQL in the mysql meta schema.