Database Storage Engines:
Database Storage Engines are programs that are integrated into MySQL database management system to manage data tables. Database Storage Engines are also called Table Types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.
For example, if you work with a large amount of temporary data, you may want to make use of the MEMORY storage engine, which stores all of the table data in memory. Alternatively, you may want a database that supports transactions (to ensure data resilience).
Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type). By default, MySQL comes with a number of different storage engines pre-configured and enabled in the MySQL server. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.
Benefits of Database Storage Engines Availability
Reliability and concurrency are enhanced with new algorithms for physical file interaction. These algorithms eliminate the need to run database console commands (DBCCs) as part of regular maintenance. However, DBCC is still available, and the new DBCC CHECK commands can be run without inhibiting online processing.
Scalability
The storage subsystem, which consists of the physical database files and their layout on disk, supports scaling from very small to very large databases. SQL Server can now support up to 64 GB of physical memory (RAM) and up to 32 processors.
Ease of use
Enhanced administration capabilities help the Database Administrator (DBA) to automate and centralize server management. This also allows easy maintenance of remote servers and applications without the necessity of having a DBA visit every site. Server configuration, managed by a sophisticated algorithm, is dynamically responsive to server usage patterns, freeing the DBA to concentrate on database management and optimization tasks.
Here are listed a few types of Database Storage Engines
MyISAM Storage Engine – MySQL default storage engine. Based on ISAM database concept. MyISAM is not transaction safe.
Innodb Storage Engine – A transaction safe storage engine developed by Innobase Oy (an Oracle company).
BDB (BerkeleyDB) Storage Engine – A transaction safe storage engine originally developed at U.C. Berkeley. Sleepycat Software, Inc. was created in 1996 to continue BDB development. Sleepycat is an Oracle company now.
CSV Storage Engine – A simple storage engine storing data as text files using comma-separated values format.
MEMORY (HEAP) Storage Engine – A storage engine storing data in computer main memory.
BLACKHOLE – A /dev/null storage engine (anything you write to it disappears)
We also have many other types of Database Storage Engines available for use.
To see the MySQL storage engines available on your server, use the show engines statement:
show engines;
However, in the output, only if DEFAULT or YES appears in the “Support” column is an engine available for use.
Database Storage Engines are programs that are integrated into MySQL database management system to manage data tables. Database Storage Engines are also called Table Types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.
For example, if you work with a large amount of temporary data, you may want to make use of the MEMORY storage engine, which stores all of the table data in memory. Alternatively, you may want a database that supports transactions (to ensure data resilience).
Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type). By default, MySQL comes with a number of different storage engines pre-configured and enabled in the MySQL server. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.
Benefits of Database Storage Engines Availability
Reliability and concurrency are enhanced with new algorithms for physical file interaction. These algorithms eliminate the need to run database console commands (DBCCs) as part of regular maintenance. However, DBCC is still available, and the new DBCC CHECK commands can be run without inhibiting online processing.
Scalability
The storage subsystem, which consists of the physical database files and their layout on disk, supports scaling from very small to very large databases. SQL Server can now support up to 64 GB of physical memory (RAM) and up to 32 processors.
Ease of use
Enhanced administration capabilities help the Database Administrator (DBA) to automate and centralize server management. This also allows easy maintenance of remote servers and applications without the necessity of having a DBA visit every site. Server configuration, managed by a sophisticated algorithm, is dynamically responsive to server usage patterns, freeing the DBA to concentrate on database management and optimization tasks.
Here are listed a few types of Database Storage Engines
MyISAM Storage Engine – MySQL default storage engine. Based on ISAM database concept. MyISAM is not transaction safe.
Innodb Storage Engine – A transaction safe storage engine developed by Innobase Oy (an Oracle company).
BDB (BerkeleyDB) Storage Engine – A transaction safe storage engine originally developed at U.C. Berkeley. Sleepycat Software, Inc. was created in 1996 to continue BDB development. Sleepycat is an Oracle company now.
CSV Storage Engine – A simple storage engine storing data as text files using comma-separated values format.
MEMORY (HEAP) Storage Engine – A storage engine storing data in computer main memory.
BLACKHOLE – A /dev/null storage engine (anything you write to it disappears)
We also have many other types of Database Storage Engines available for use.
To see the MySQL storage engines available on your server, use the show engines statement:
show engines;
However, in the output, only if DEFAULT or YES appears in the “Support” column is an engine available for use.
No comments:
Post a Comment