mysql engines_MySQL Engines: InnoDB vs. MyISAM

转发自：http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html

The 2 major types of table storage engines for MySQL databases are

InnoDB and MyISAM. To summarize the differences of features and

performance,

InnoDB is newer while MyISAM is older.

InnoDB is more complex while MyISAM is simpler.

InnoDB is more strict in data integrity while MyISAM is loose.

InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.

InnoDB has transactions while MyISAM does not.

InnoDB has foreign keys and relationship contraints while MyISAM does not.

InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.

MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique

advantages and disadvantages against each other. They each are more

suitable in some scenarios than the other.

Advantages of InnoDB

InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.

Faster in write-intensive (inserts, updates) tables

because it utilizes row-level locking and only hold up changes to the

same row that’s being inserted or updated.

Disadvantages of InnoDB

Because InnoDB has to take care of the different relationships

between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.

Consumes more system resources such as RAM. As a

matter of fact, it is recommended by many that InnoDB engine be turned

off if there’s no substantial need for it after installation of MySQL.

No full-text indexing.

Advantages of MyISAM

Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.

Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.

Full-text indexing.

Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

No data integrity (e.g. relationship constraints)

check, which then comes a responsibility and overhead of the database

administrators and application developers.

Doesn’t support transactions which is essential in critical data applications such as that of banking.

Slower than InnoDB for tables that are frequently

being inserted to or updated, because the entire table is locked for any

insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for

data critical situations that require frequent inserts and updates.

MyISAM, on the other hand, performs better with applications that don’t

quite depend on the data integrity and mostly just select and display

the data.转自：http://linuxers.org/howto/myisam-or-innodb-mysql-engine

Most people don't bother about what MySQL engine they are using.

Generally, it doesn't even matter, the default MyISAM does the job

really well but sometimes switching to some other engine or at least

knowing what others are capable of, could speed up a lot of things for

you. Managing transactions for a Financial website was how I came across

InnoDB engine at first. In this article, we will see the difference

between MyISAM and InnoDB MySQL engines.

So, lets begin by taking a look at all the engines. Type the following command in the mysql prompt.

mysql> SHOW ENGINES\G;

You will see something the following engines, with a whole bunch of other information.

InnoDBMRG_MYISAM

BLACKHOLE

CSV

MEMORY

FEDERATED

ARCHIVE

MyISAM

As you can see they are a lot but we will focus on MyISAM and InnoDB here. Lets start with their limitations:-

MyISAM limitations

No Foriegn keys and cascading deletes and updates

No rollback abilities

No transactional integrity (ACID compliance)

Row limit of 4,284,867,296 rows

Maximum of 64 indexes per row

InnoDB Limitations

No full text indexing

Cannot be compressed for fast, read-only

MyISAM uses table level locking and this can be a great problem if

your database INSERT/DELETE/UPDATE load is very high. If this is the

problem then you should try converting it to INNODB. It manages non

transactional tables. It has fast storage and retrieval, as well as full

text searching capabilities. Its supported by every MySQL and comes as a

default engine.

When to use MyISAM?

MyISAM is designed with the idea that your database is queried far

more than its updated and as a result it performs very fast read

operations. If your read to write(insert|update) ratio is less than 15%

its better to use MyISAM.

When to use InnoDB?

InnoDB uses row level locking, has commit, rollback, and

crash-recovery capabilities to protect user data. It supports

transaction and fault tolerance.

Convert from one type of engine to other.

You can create a table and tell which storage engine to use, if none is specified MyISAM is used

CREATE TABLE table_name (rid INT) ENGINE = INNODB;

CREATE TABLE table_name (rid INT) TYPE = INNODB;

TYPE is older term and is supported for backward compatibility.

You can set the default storage engine to be used during the current session by setting the storage engine variable:

SET storage_engine=INNODB

Converting tables from one storage mechanism to another can be done by using the alter command:

ALTER TABLE table_name ENGINE = INNODB

Ok, it could be difficult if you have many tables to manually run alter query. So here's another way that will work.

DUMP the sql into a file using mysqldump.

Using sed command update to INNODB engine from MYISAM in the sql file.

转自：http://www.ovaistariq.net/460/why-do-i-recommend-switching-over-from-myisam-to-innodb/

MyISAM has been the default storage engine for

MySQL for years now. But that is soon going to change. With MySQL 5.5,

Innodb is going to be the default storage engine (and I am pretty

pleased about that, no more new users complaining about MySQL not being

ACID compliant).

But why do I recommend Innodb over MyISAM. Following are the reasons:

I will start of with reasons not so obviously discussed during

comparisons of Innodb and MyISAM storage engines. But these reasons are

so compelling that I bet you would want to switch over to Innodb after

reading the reasons.

Innodb is being constantly improved and worked upon

There hasn’t been much work put forth into improving the MyISAM

storage engine, and with Oracle acquiring MySQL and owning Innodb, it

has put its weight on the Innodb storage engine. While there hasn’t been

much work done on MyISAM side, Innodb has seen improvements and work is

being done aggressively. MySQL 5.5 will ship with a new version of

Innodb – version 1.1, which has major performance improvements. Even if

you are using MySQL 5.1, you can replace the built-in Innodb with the

newer Innodb plugin – version 1.0.8, which has introduced major

performance improvements.

Innodb supports row-level locking

Innodb not only supports table-level locking, but also supports

row-level locking, making it the storage engine of choice for high

concurrency applications.

Innodb supports hot backup

With MyISAM, backing up a live database, is pretty much an issue for

many DBAs because of the way how MyISAM supports locking. MyISAM only

supports table-level locks which pretty much means that the server must

go down during the backup, for sometime depending upon the amount of

data in the database. With Innodb this is no issue at all, Innodb

supports row level locking which allows you to do hot backups.

Innodb is ideal for high-concurrency applications

Yet another big reason for using Innodb is that with Innodb you can

achieve high-user-throughput and high-database-concurrency. That is

again thanks to the row-level mechanism of Innodb, which makes it a big

winner (compared to MyISAM) in cases where you need highly concurrent

read-write operations.

Innodb is crash proof

What I essentially mean by being crash proof is the database,

recovering back into a consistent state in case of a crash (be it an OS

crash or a MySQL server crash). While in the case of MyISAM, a crash

would mean that you would have to deal with recovering tables on the

crash or partially executed statements, in the case of Innodb there

would be essentially no data inconsistency and all the more its more of

an automated recovery, where by Innodb would recover itself by replaying

its logs.

Innodb supports clustered indexes

Innodb stores rows ordered physically in PRIMARY KEY order. Storing

the rows in primary key order corresponds to what is known in MSSQL

Server as “Clustered index” and in Oracle as “index organized tables”.

This makes primary key fetches extremely efficient.

Innodb implies various internal optimizations that improve the performance further

There are many different optimizations that Innodb performs

internally that improve performance drastically. Following are some of

the optimizations:

Adaptive Hash Indexing:

To quote from the MySQL manual: “If a table fits almost entirely in

main memory, the fastest way to perform queries on it is to use hash

indexes rather than B-tree lookups. InnoDB monitors searches on each

index defined for a table. If it notices that certain index values are

being accessed frequently, it automatically builds an in-memory hash

table for that index.

Read Ahead:

A read-ahead request is an I/O request sent by Innodb to pre-fetch data

pages into the innodb buffer pool cache. Innodb sends these read-ahead

requests based on the access patterns of the data pages already in the

buffer pool cache. This pre-fetching can improve read performance

drastically for data that is being fetched linearly.

Innodb Buffer Pool:

While MyISAM relies on the OS cache for caching reads and writes, Innodb

has its own cache which it manages on its own and which is of course

fine-tuned for Innodb operations. And combine this with adaptive hash

indexing and read-aheads, and you can imagine the performance gains.

Innodb change buffering:

When INSERT, UPDATE, and DELETE operations are performed on a table,

Innodb buffers these changes into the Innodb buffer pool instead of

flushing them directly to disk instantly. These changes are flushed to

the disk only when needed. This avoids expensive I/O operations and thus

providing great performance boost for applications that do a lot of

inserts/updates/deletes. To quote the MySQL manual: “Because it can

result in fewer disk reads and writes, this feature is most valuable for

workloads that are I/O-bound, for example applications with a high

volume of DML operations such as bulk inserts.”

Innodb background flushes:

Innodb performs many of the I/O operations using asynchronous background

threads, operations like read-ahead requests, dirty-pages flushing etc.

This ensures that these I/O operations do not block user operations on

the database.

Now come the reasons that are discussed more often, mentioned in

all the comparisons of Innodb and MyISAM. Although you might not need

transactions or foreign key constraints in all of your applications, but

these do make Innodb a complete winner when compared with MyISAM.

Innodb supports transactions

Innodb supports ACID-compliant transactions, what that means is that

you can group SQL statements in a transaction and be sure that the

database will be in a consistent state, either you commit the

transaction or rollback, or even more so the server crashes in the

middle of a SQL statement.

Innodb supports foreign key constraints

Innodb supports foreign keys, so that you can enforce parent-child

relationships. Operations such as deleting child rows when parent is

deleted, or updating the child rows on updating of parent row are

supported and enforced. This ensures that the database takes care of

ensuring that the relationships are consistent, without the onus being

on the application developer.

Conclusion:

If you are still not convinced about Innodb and have some questions in mind you may discuss them out with me.

What’s coming?

I had written a post about tuning MySQL server settings applicable to

the MyISAM storage engine, I will soon be posting about tuning Innodb

related MySQL server settings. Besides that, I will soon be deploying

MySQL 5.5 on the server hosting this blog and I will be sharing my

findings soon. Besides that I plan on posting some benchmarking results

for MyISAM vs Innodb.