Comparison of MySQL database engines

Comparison of MySQL database engines

This a comparison between the two primary database engines (InnoDB and MyISAM) for the MySQL database management system (DBMS). A database engine (or "storage engine") is the underlying software component that a DBMS uses to create, read, update and delete (CRUD) data from a database.

Comparison between InnoDB and MyISAM

  1. InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater availability as database sizes grow.
  2. InnoDB, with innodb_flush_log_at_trx_commit set to 1, flushes the transaction log after each transaction, greatly improving reliability[1]. MyISAM has to be run on top of a fully journaled filesystem, such as ext4 mounted with data=journal, to provide the same resilience against data file corruption. (The journal can be put on an SSD device for improved MyISAM performance, similarly, the InnoDB log can be placed on a non-journaled filesystem such as ext2 running on an SSD for a similar performance boost. Reliability is not sacrificed in either case.)
  3. InnoDB can be run in a mode where it has lower reliability but in some cases higher performance. Setting innodb_flush_log_at_trx_commit to 0 switches to a mode where transactions are not committed to disk before control is returned to the caller. Instead, disk flushes happen on a timer.[1]
  4. InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.[2]
  5. MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches.[3]
  6. InnoDB will store rows in primary key order if present, else first unique key order. This can be significantly faster if the key is chosen to be good for common operations.[citation needed] If there is no primary key or unique key InnoDB will use an internally generated unique integer key and will physically store records in roughly insert order, as MyISAM does. Alternatively, an autoincrementing primary key field can be used to achieve the same effect.
  7. InnoDB provides updatable LZW compressed page storage for both data and indexes. MyISAM compressed tables can't be updated.[4]
  8. When operating in fully ACID-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. For applications which require higher transaction rates, disk controllers with write caching and battery backup will be required in order to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity guarantees, though still retaining greater reliability than MyISAM. MyISAM has none of this overhead, but only because it does not support transactions.
  9. MyISAM uses table-level locking on updates and deletes to any existing row, with an option to append new rows instead of taking a lock and inserting them into free space. InnoDB uses row-level locking. For large database applications where many rows are often updated, row-level locking is crucial because a single table-level lock significantly reduces concurrency in the database.
  10. MyISAM is still widely used in web applications as it has traditionally been perceived as faster than InnoDB in situations where most DB access is reads. Features like the adaptive hash index and change buffer often mean that InnoDB is faster even if concurrency isn't an issue.[citation needed]
  11. Unlike InnoDB, MyISAM has built-in full-text search.[5]


External links

Wikimedia Foundation. 2010.

Игры ⚽ Поможем решить контрольную работу

Look at other dictionaries:

  • MySQL — Developer(s) MySQL AB (A subsidiary of Oracle) Initial release May 23, 1995 (1995 05 23 …   Wikipedia

  • Database — A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports… …   Wikipedia

  • Comparison of reference management software — The following tables compare reference management software. Contents 1 General 2 Operating system support 3 Export file formats 4 Import file formats …   Wikipedia

  • Comparison of business integration software — This article is a comparison of business integration and business process automation software. Contents 1 General 1.1 Scope 1.2 General information 2 Compatibility and intero …   Wikipedia

  • Comparison of wiki software — The following tables compare general and technical information for a number of wiki software packages. Contents 1 General information 2 Target audience 3 Features 1 4 Features 2 …   Wikipedia

  • Comparison of server-side JavaScript solutions — This is a list of Server side JavaScript solutions. Contents 1 Server side JavaScript use 2 See also 3 External links 4 References …   Wikipedia

  • Comparison of social networking software — Social Network Engine Comparison Social Network Engines or Social Engines provide the basis for community driven content and social/business networking. Category Tiki Wiki CMS Groupware BoonEx Dolphin BuddyPress EngineY PeopleAggregator ELGG… …   Wikipedia

  • Comparison of web application frameworks — This is a comparison of notable web application frameworks. Contents 1 General 1.1 Perl 1.2 PHP 1.3 Java 1.4 Python …   Wikipedia

  • Comparison of Tryton and Open ERP — Tryton is a community fork of the commercial open source project OpenERP and began development in November 2008. This article compares both projects from technical, business and conceptual perspectives. Subject Tryton OpenERP Language Python… …   Wikipedia

  • Drizzle (database server) — Drizzle Developer(s) Brian Aker and others Preview release 2011.11.29 / November 13, 2011; 6 days ago ( …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”