innodb vs myISAM?

[steve]

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

[Bill Karwin]

steve wrote:

Hi,
Besides transaction capability, is there anything else about innodb
tables that makes it superior to myISAM?
Referential integrity. That is, foreign keys are actually enforced.
MyISAM allows the syntax declaring foreign keys, but does not enforce them.
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?

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.

[Thomas Bartkus]

"steve" <Us************ @dbForumz.com> wrote in message
news:4_******** *************** *************** *@dbforumz.com. ..

Hi,
Besides transaction capability, is there anything else about innodb
tables that makes it superior to myISAM?
No!
I really don't need transactions, ...
If you don't need/use transactions, then stick with MyISAM tables. The
overall performance is clearly superior.
... 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?

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

[steve]

"tom173" wrote:

"steve" <Us************ @dbForumz.com> wrote in message
news:4_******** *************** *************** *@dbforumz.com. ..

Hi,
Besides transaction capability, is there anything else about

innodb

tables that makes it superior to myISAM?

No!

I really don't need transactions, ...

If you don't need/use transactions, then stick with MyISAM
tables. The
overall performance is clearly superior.

... 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?

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

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

[Bill Karwin]

steve wrote:

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.

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.

[steve]

"Bill Karwin1" wrote:

steve wrote:

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.

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.

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