innodb vs myISAM?

Hi,
Besides transaction capability, is there anything else about innodb
tables that makes it superior to myISAM?

I really don’t need transactions, but for example, I need superior
reliability for index (too many index corruptions in the past). Is
innodb better in that area, or any other areas?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-innodb-ftopict224463.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=773646

re: innodb vs myISAM?

steve wrote:[color=blue]
> Hi,
> Besides transaction capability, is there anything else about innodb
> tables that makes it superior to myISAM?[/color]

Referential integrity. That is, foreign keys are actually enforced.
MyISAM allows the syntax declaring foreign keys, but does not enforce them.
[color=blue]
> I really don’t need transactions, but for example, I need superior
> reliability for index (too many index corruptions in the past). Is
> innodb better in that area, or any other areas?[/color]

Hmm. Index corruptions shouldn't be a normal part of MyISAM operation.
I don't know if InnoDB is better in that area, but I would caution you
to examine what is causing index corruption in your current system,
because it could also affect any other system. Are you shutting down
MySQL properly when you shut down your computer? Do you make backups,
and do other periodic table maintenance tasks?

Regards,
Bill K.

re: innodb vs myISAM?

"steve" <UseLinkToEmail@dbForumz.com> wrote in message
news:4_773646_9e634b77710c4ea2ce6cc9968e158e29@dbf orumz.com...[color=blue]
> Hi,
> Besides transaction capability, is there anything else about innodb
> tables that makes it superior to myISAM?[/color]

No!
[color=blue]
> I really don't need transactions, ...[/color]

If you don't need/use transactions, then stick with MyISAM tables. The
overall performance is clearly superior.
[color=blue]
> ... but for example, I need superior
> reliability for index (too many index corruptions in the past). Is
> innodb better in that area, or any other areas?[/color]

I have no reason to believe the indexing would be more reliable on innodb
tables. Let some one else jump in here.

BUT

I find the "(too many index corruptions in the past)" comment a bit
mysterious. Perhaps you should give a few details because indexing should
be *rock solid* for MyISAM tables with better support for maintenance and
repair tools.

Thomas Bartkus

re: innodb vs myISAM?

"tom173" wrote:[color=blue]
> "steve" <UseLinkToEmail@dbForumz.com> wrote in message
> news:4_773646_9e634b77710c4ea2ce6cc9968e158e29@dbf orumz.com...[color=green]
> > Hi,
> > Besides transaction capability, is there anything else about[/color]
> innodb[color=green]
> > tables that makes it superior to myISAM?[/color]
>
> No!
>[color=green]
> > I really don't need transactions, ...[/color]
>
> If you don't need/use transactions, then stick with MyISAM
> tables. The
> overall performance is clearly superior.
>[color=green]
> > ... but for example, I need superior
> > reliability for index (too many index corruptions in the[/color]
> past). Is[color=green]
> > innodb better in that area, or any other areas?[/color]
>
> I have no reason to believe the indexing would be more
> reliable on innodb
> tables. Let some one else jump in here.
>
> BUT
>
> I find the "(too many index corruptions in the past)" comment
> a bit
> mysterious. Perhaps you should give a few details because
> indexing should
> be *rock solid* for MyISAM tables with better support for
> maintenance and
> repair tools.
>
> Thomas Bartkus[/color]

Thanks Tom and Bill,

I was experiencing index corruptions due to stuffing too many inserts
(like a 1000 of them) into a single insert statement (with a hope to
gain performance). mysql did not like that at all, and all kinds of
mysterious and a lot of times unrelated index crashes were popping up.

Since I removed the huge multi-insert, everything is just humming.

Ok, I have gathered that if I don’t need transactions and referential
integrity, there are no benefits to innodb, considering the log
overhead.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-innodb-ftopict224463.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=774570

re: innodb vs myISAM?

steve wrote:[color=blue]
> I was experiencing index corruptions due to stuffing too many inserts
> (like a 1000 of them) into a single insert statement (with a hope to
> gain performance). mysql did not like that at all, and all kinds of
> mysterious and a lot of times unrelated index crashes were popping up.
>
> Since I removed the huge multi-insert, everything is just humming.[/color]

I still say this is not normal behavior for MyISAM. I use the extended
INSERT format in my backups frequently, and restore using INSERT
statements much longer than 1000 records. I don't have problems with
indexes getting corrupted.

I suggest that something else is going on that causes your indexes to
become corrupted. Is your datadir on a networked drive or something?
Is your hard drive old and failing? Do your ATA cables need to be reseated?

Another idea: you could try ALTER TABLE name DISABLE KEYS before doing
your inserts, followed by ALTER TABLE name ENABLE KEYS after the insert.

Regards,
Bill K.

re: innodb vs myISAM?

"Bill Karwin1" wrote:[color=blue]
> steve wrote:[color=green]
> > I was experiencing index corruptions due to stuffing too[/color]
> many inserts[color=green]
> > (like a 1000 of them) into a single insert statement (with a[/color]
> hope to[color=green]
> > gain performance). mysql did not like that at all, and all[/color]
> kinds of[color=green]
> > mysterious and a lot of times unrelated index crashes were[/color]
> popping up.[color=green]
> >
> > Since I removed the huge multi-insert, everything is just[/color]
> humming.
>
> I still say this is not normal behavior for MyISAM. I use
> the extended
> INSERT format in my backups frequently, and restore using
> INSERT
> statements much longer than 1000 records. I don't have
> problems with
> indexes getting corrupted.
>
> I suggest that something else is going on that causes your
> indexes to
> become corrupted. Is your datadir on a networked drive or
> something?
> Is your hard drive old and failing? Do your ATA cables need
> to be reseated?
>
> Another idea: you could try ALTER TABLE name DISABLE KEYS
> before doing
> your inserts, followed by ALTER TABLE name ENABLE KEYS after
> the insert.
>
> Regards,
> Bill K.[/color]

Thanks, Bill. Since I broke up the mult-insert, all the problems have
disappeared. It could also be that I did not set the server settings
related to large packets (since on a semi-shared server), but then I
should have received proper error messages.. but all I got was index
corruption.

The corruption is now gone. Pretty sure related to huge inserts -
somehow.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-innodb-ftopict224463.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=774813