MyISAM vs innoDB

[dr_lucas 39]

Hi,

 

My osC DB storage engine is currently MyISAM and I read a lot of good things about the innoDB storage engine, which seems to be quite better than the MyISAM engine. I got innoDB available on my host.

The questions are - which one is better for osC? Are there any disadvantages to converting my osC tables to innoDB? Will my osC store lose any functionality or is it safe to convert?

 

TIA

[MrPhil 648]

I don't think you're going to gain any functionality that osC can make use of. InnoDB supports foreign keys, which osC doesn't use. I don't know what else is gained with InnoDB, and have no idea if reliability, stability, or performance should be any better than with MyISAM. I would doubt that you'd lose any capabilities (that osC needs), but I can't speak with any authority on that. If I were in your shoes, I'd stay put with MyISAM, unless you want to run another application that needs InnoDB and (?) you can't have both at once.

 

Do let us know if you switch over to InnoDB and nothing breaks and something improves.

[dr_lucas 39]

Thanks, Phil, this info is taken from Wikipedia:

 

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 and reliability as database sizes grow.

2. MyISAM relies on the operating system for caching reads and writes to the data rows while InnoDB does this within the engine itself, combining the row caches with the index caches. Dirty (changed) database pages are not immediately sent to the operating system to be written by InnoDB, which can make it substantially faster than MyISAM in some situations.[citation needed]

3. InnoDB will store rows in primary key 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.

4. InnoDB currently does not provide the compression and terse row formats provided by MyISAM, so both the disk and cache RAM required may be larger. A lower overhead format is available for MySQL 5.0, reducing overhead by about 20% and use of page compression is planned for a future version.

5. 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. If you 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 transaction.

6. MyISAM uses table-level locking on writes to any existing row, whereas 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.

7. 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 insert buffer often mean that InnoDB is faster even if concurrency isn't an issue.[citation needed]

8. Unlike InnoDB, MyISAM has built-in full-text search.

 

I know that I won't gain any functionality that osC can make use of, but that is not what I am concerned about.

My store db has over 16,000 products w/ attributes and thousands of orders and customers, its size is currently 150MB. According to the info above - InnoDB supports Row Locking rather than table locking which can speed things up when many customers are visiting the site making changes at the same moment, so here is at least one advantage.

I don't really want to answer my own question, that is why I made this post, to get some opinions from members who are much more experiences than me with MySQL.

[dr_lucas 39]

To clarify my post even more: What I do care about is improving performance and reliability without losing any functionality.

Here is another post I read on mysql.com recommending InnoDB for osC:

http://forums.mysql.com/read.php?34,56892,58170#msg-58170

 

Any more opinions for or against it?

[MrPhil 648]

In the referenced post, Michal is wrong when he says InnoDB should be used instead of MyISAM. osCommerce isn't architected to use transaction and constraint (e.g., foreign keys) features found in InnoDB.

 

Looking at the Wiki article, it sounds like you might get some performance improvements with InnoDB. I have no first-hand experience with that engine, so I can't tell you for sure. It sounds like the tradeoff for better performance may be more RAM usage, so be careful if you are constrained in that department (i.e., already running what your host considers rather large processes).

 

(Famous last words*) I don't see the harm in trying out InnoDB. Of course, you will make a test installation of your store, where MySQL is using InnoDB instead of MyISAM, and play with it for a while to make sure nothing breaks. Be prepared to jump back to MyISAM in a hurry if your live store with InnoDB experiences problems.

 

* The Simpsons episode Lisa the Beauty Queen:

Barney: Hey, can I drive this thing?

Captain of the Duff Blimp: Sure, I can't see any harm in that.

(Barney takes controls of blimp, steers it into a nearby pointy radio tower, and it does a Hindenburg.)

Kent Brockman: Oh, the humanity! Well, anyway...

[dr_lucas 39]

In the referenced post, Michal is wrong when he says InnoDB should be used instead of MyISAM. osCommerce isn't architected to use transaction and constraint (e.g., foreign keys) features found in InnoDB.

 

Looking at the Wiki article, it sounds like you might get some performance improvements with InnoDB. I have no first-hand experience with that engine, so I can't tell you for sure. It sounds like the tradeoff for better performance may be more RAM usage, so be careful if you are constrained in that department (i.e., already running what your host considers rather large processes).

 

(Famous last words*) I don't see the harm in trying out InnoDB. Of course, you will make a test installation of your store, where MySQL is using InnoDB instead of MyISAM, and play with it for a while to make sure nothing breaks. Be prepared to jump back to MyISAM in a hurry if your live store with InnoDB experiences problems.

 

Thanks, Phil.

Yes, I got quite a good VPS server with plenty of RAM so it should be fine.

The Wiki article also suggests that there is greater reliability using InnoDB, in addition to performance. I am really interested to hear from people with first hand experience of osC 2.2 MS2 with InnoDB, most specifically - if there are any downside to converting the entire DB to InnoDB.

 

By the way, this is a quick script that does the conversions in seconds:

 

<?php
#This script will change all the table engine types for a given database!
#All the DB tools I have (GNU/freeware) will not change a list of database
# types, so this script saves time when a CMS or other populates a database
# with tables we cannot use! This can be migrated to InnoDB by changing line
# 23, col 46 from MyISAM to InnoDB (double check the capitals there!).
# Change these variables relative: serverName, userName, password, databaseName

# 20051410 JLynch
# myisamFixer.php

ini_set('display_errors', 'On');
error_reporting(E_ALL);

$link = mysql_connect("HostName","UserName","PassWord")
or die("unable to connect to msql server: " . msql_error());

mysql_select_db("DBname", $link)
or die("unable to select database 'db': " . msql_error());

$result = mysql_query("show tables");
if (!$result) {
die('query failed: ');
}

while ($row = mysql_fetch_array($result)){
mysql_query("ALTER TABLE ".$row[0]." ENGINE=InnoDB; ");
#Command Reference: ALTER TABLE tableName ENGINE=MyISAM
echo $row[0] . ': OK!';
}

?>

[dr_lucas 39]

Any more opinions?

[♥ecartz 694]

You should use MyISAM for product tables and other places where you need full text search, which InnoDB does not have. If you use InnoDB for the order tables, you should modify the queries to take advantage of transactions (primarily in the checkout_process.php file) and foreign key constraints. Without those modifications, InnoDB won't be more reliable than MyISAM.

 

Note that some tables will have trouble with foreign key constraints. Notably both address_book and customers have their primary keys as data in the other table. However, obviously only one could be a foreign key constraint (chicken and egg problem).

 

In general, I would expect MyISAM to be faster for reads. InnoDB may be faster for writes under some circumstances. However, most tables are read more than they are written. As such, it is usually better to optimize for the reading case rather than the writing case. In osCommerce, the order tables may be an example of an exception.