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 5 15516
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.
"steve" <Us************@dbForumz.com> wrote in message
news:4_***************************************@dbf orumz.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
"tom173" wrote: "steve" <Us************@dbForumz.com> wrote in message news:4_***************************************@dbf orumz.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
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.
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Morten Gulbrandsen |
last post by:
Dear MySQL developers,
Could some experienced Database developer please take a look at this ?
It is supposed to be plain SQL2.
How can it be coded under MySQL
Especially all referential...
|
by: Morten Gulbrandsen |
last post by:
Hi Programmers,
after this legal statement
ALTER TABLE EMPLOYEE TYPE =3D InnoDB;
I get=20
Warnings: 0 =20
and still MySQL claims to have to use Type =3D MyISAM,
|
by: Andy |
last post by:
I'm using Mysql Max-4.1.4-gamma for Linux.
I have a question related to a problem with my database server.
If on my db server I have 2 different database, say DB1 & DB2, I
create two INNODB tables...
|
by: Stefan van Roosmalen |
last post by:
I want to use constraints, so I will have to use innodb tables (according to
the website mysql.com).
But, what is the difference between myisam and innodb tables ??
And why is there a comment...
|
by: Mike Ho |
last post by:
I'm wondering if there is a way to retrieve Foreign Key declaration
information on both MyISAM and InnoDB tables.
Of course, I understand that MyISAM doesn't support Foreign Keys,
however, it...
|
by: Reuben Pearse |
last post by:
Hi all,
I've just converted the tables in a big database (approx 27 million
records) from MyISAM to InnoDB. When I was using MyISAM I backed up
the database by stopping MySQL and then copying...
|
by: Eddie |
last post by:
I have a MySQL-server running Innodb. We have installed ~ 2GB of
memory in the server. In spite of this MySQL keeps crashing due to
out-of-memory errors. The server is a dual xeon i686 running...
|
by: Good Man |
last post by:
Hi there
I have a database with about 20 or so tables, maybe a few thousand rows
in each. I am starting to do more complex things with my insertions etc,
and I want to start to use...
|
by: crescent_au |
last post by:
I've read articles and postings about MyISAM vs InnoDB but I am still a
bit unsure about which storage engine to use for my new project.
I am developing a website in PHP/MySQL, which includes...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |