nnoDB.
innoDB is a table type in mySQL.
InnoDB provides MySQL with a transaction-safe storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB because row-level locks in InnoDB fit in very little space.
advantages of innoDB table type are :
* InnoDB tables are transactional: they provide rollback and commit capabilities.
* InnoDB is the only table type in MySQL which supports foreign key constraints.
* InnoDB tables are fast, even faster than MyISAM tables in many simple benchmarks. See the benchmark page.
* InnoDB tables have row level locking: they allow higher concurrency than MyISAM tables which use table level locking, or BDB tables, which use page level locking. High concurrency is reflected in high multiuser performance.
* InnoDB tables provide an Oracle-style consistent read, also known as multiversioned concurrency control. SELECTs do not need to set any locks and need not interfere with inserts and updates to the same table. No other MySQL table type has this property.
* There is a true hot backup tool available for InnoDB, which allows you to make backups of a running database in background, without setting any locks or disturbing database operation.
* Multiversioning also allows you to dump tables from your database with SELECT INTO OUTFILE without setting locks on the tables: the database can keep working while a backup is made.
* InnoDB tables have automatic crash recovery. You do not need to repair your tables if the operating system or the database server crashes, when there is no disk image corruption.
* InnoDB tables can be any size, also on those operating systems where file size is restricted to < 2 GB.
Enabling and Checking innoDB type in mySQL
innoDB type is supported my all mySQL versions starting from mySQL 4.0.x
Go to the file /etc/my.cnf file and either comment out the line or delete the line ’skip-innodb’ and innodb will be enabled.
You can check either your mySQL supports the innoDB type by giving the following command at mysql prompt.
mysql> show variables like ‘have_innodb’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_innodb | YES |
+—————+——-+
1 row in set (0.00 sec)
MySQL has two primary storange engines: MyISAM and InnoDB. Each has its own performance characteristics and considerations. In the broadest sense MyISAM is good for read-heavy data and InnoDB is good for write-heavy data, though there are cases where the opposite is true. The biggest gotcha is how the two differ with respect to the COUNT function.
MyISAM keeps an internal cache of table meta-data like the number of rows. This means that, generally, COUNT(*) incurs no additional cost for a well-structured query. InnoDB, however, has no such cache. For a concrete example, let’s say we’re trying to paginate a query. If you have a query SELECT * FROM users LIMIT 5,10, let’s say, running SELECT COUNT(*) FROM users LIMIT 5,10 is essentially free with MyISAM but takes the same amount of time as the first query with InnoDB. MySQL has a SQL_CALC_FOUND_ROWS option which tells InnoDB to calculate the number of rows as it runs the query, which can then be retreived by executing SELECT FOUND_ROWS(). This is very MySQL-specific, but can be necessary in certain situations, particularly if you use InnoDB for its other features (e.g., row-level locking, stored procedures, etc.).
Links:
No comments:
Post a Comment