MySQL DBA training Session 5 Storage Engines in MySQL. To Learn or Teach Linux visit www.theskillpedia.com, A Marketplace for Learners and Trainers.
Session 5 : Storage Engines
Ram N Sangwan
MySQL Storage Engines
A storage engine can be though of as a File System in an Operating System.
Storage engines differ in the way
that they use locking to manage query contention, or
whether the tables that they provide are transactional or non-transactional.
These engine properties have implications for query processing performance, concurrency, and deadlock prevention.
When you create a table, you can choose what storage engine to use.
Specify a storage engine..
Explicitly in a CREATE TABLE statement, use an ENGINE option:
Without using an ENGINE option, the default engine given by storage_engine system variable will be used.
To determine which storage engine is used for a given table, you can use the SHOW CREATE TABLE or the SHOW TABLE STATUS statement:
In most respects the way that you use the table after creating it, is engine independent.
Storage Engine Pre-requisits
• It must be compiled into the server and enabled. 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 InnoDB, MyISAM, MERGE, and MEMORY.
• Support for optional engines typically can be selected when MySQL is configured and built.
• To reduce memory use, don't configure unneeded storage engines into the server.
• To see what storage engines:
MyISAM Storage Engine
• MyISAM is the improved version of the original storage engine of MySQL, ISAM
• After MySQL 3.23, MyISAM replaced ISAM as the default storage engine.
MyISAM Storage Engine Contd..
• MyISAM table allows table level locking only.
• There are no limitations on data file transfer and the data files can be ported from system to system
• The foreign key constraint cannot be defined
• MyISAM is the only storage engine that supports Full-text search
• It also supports one auto increment column per table
• A high-byte-first pattern for saving numeric key values ensures faster indexing
• It can be used where fulltext indexing is needed
• 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.
Files for a table named mytable are called mytable.frm, mytable.MYD, and mytable.MYI.
• MyISAM tables can be used to set up MERGE tables.
• MyISAM tables can be converted into fast, compressed, read-only tables to save space.
• Query performance is very fast for retrievals.
• Multiple queries can read the same table simultaneously.
• You can influence the scheduling mechanism for queries that use MyISAM tables by using LOW_PRIORITY/HIGH_PRIORITY Query Modifier.
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.
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.
Other Storage Engines
The BDB storage engine provides transactional tables.
Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information.
BDB supports transactions with full ACID compliance.
The BDB engine provides auto-recovery after a crash of the MySQL server or the host where the server runs.
BDB uses page-level locking.
It's possible for deadlock to occur.
The ARCHIVE storage engine provides an efficient way to store large amounts of data when you don't need indexes and need to minimize the amount of disk space used.
This engine supports only SELECT and INSERT operations. SELECT scans the entire table, and INSERT performs compressed inserts.
Each ARCHIVE table is represented on disk by an .frm format file, an .ARZ data file, and an .ARM metadata file.
The CSV storage engine stores records as text in the well-known comma-separated values format.
It does not support indexing. Each CSV table is represented on disk by an .frm format file and a .CSV plain text file that contains data rows.
The BLACKHOLE storage engine creates tables that act as "black holes."
That is, what goes in does not come out.