Database Storage engines

Islam Al Zatary@islamzatary

Transaction1. reliable units(recovery from failures and keep

a database consistent).

2. isolation between programs accessing a

database concurrently.

A database transaction, must be atomic, consistent, isolated and durable.

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

Storage Engine Types:

1. MyIsam•Absence of transactions support.

Characteristics:

1. data values are stored with the low byte first.

2. All numeric key values are stored with the high byte first.

3. Large files (up to 63-bit file length).

4. There is a limit of 232 (~4.295E+09) rows in a MyISAM table.

5. The maximum number of indexes per MyISAM table is 64.

6. The maximum key length is 1000 bytes.

7. Internal handling of one AUTO_INCREMENT column per table is

supported.

8. MyISAM supports concurrent inserts.

9. You can put the data file and index file in different directories

on different physical devices.

10. BLOB and TEXT columns can be indexed.

11. NULL values are allowed in indexed columns.

12. MyIsam, flags:

a. mysqld: indicates whether the table was closed

correctly.

b. myisamchk: marks tables as checked if you run it with

the --update-state option.

c. myisamchk --analyze stores statistics for portions of

keys, as well as for entire keys.

d. myisampack can pack BLOB and VARCHAR columns.

2. InnoDB* Transaction-safe (ACID compliant)• stores user data in clustered indexes.• supports FOREIGN KEY referential-integrity constraints.• designed for maximum performance.• large database sites requiring high performance, Example: Slashdot.org• stores more than 1TB of data and handles an average load of 800 inserts/updates per second.• increase multi-user concurrency and performance(row-locking).

3. MemoryStores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data. This engine was formerly known as the HEAP engine.

4. MergeAllows a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

5. ArchiveProvides the perfect solution for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

6. FederatedOffers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

7. NDB-ClusterThis clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

8. CSVThe CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

9. Blackhole The Blackhole storage engine accepts but does not store data and retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.

Choosing a Storage Engine

1. Is your table is going to be inserted, deleted, and

updated much much more than it is going to be

selected????

2. If you need full-text search?????

3. If you prefer/require relational database design?????

4. Is disk-space or ram an issue??????

5. In Doubt?????

Thank you

Islam Al-Zatary - Front End EngineerTwitter: islamzataryEmail: islamzatary@yahoo.com