SEO

Hi All

I know the innodb vs myisam issue comes up quite frequently. I went through
old threads and could not find an answer to my questions.

Generally, is there any reason/scenario not to use innodb?

From a feature perspective, I do not need full text indices, foreign keys
are usefull but not necessary (if I write the applications accordingly),
transactions are also usefull but not entirely necessary.
Basically I have no excluding reasons for the one or the other.

From a speed perspective, I do not have a lot of simultaneous connections
but a lot of data. Some tables have several hundred million records
(growing). I read somewhere that innodb loses performance once the size of
the tables exceed the amount of RAM. Is that true and if yes, how bad is
that loss?

I know this is a very general question but it seems not to make any sense
not to use innodb having such exotic features like foreign keys and
transactions.

Maybe some of you had this dilemma in the past and can offer some insight.

Thanks in advance
Olaf

Size is an issue with InnoDB and deleting records does not reduce the size of the file. In my experience, the performance drop off
is considerable once the table reaches a certain size. And it's not a slight drop off over time.
If your table is going to get very large, I would reccommend using MyISAM. You have many more options for managing a large table.
For instance, you can split the data up into table, say one per month. You can then create merge tables to access those tables. This
gives you the ability to create datasets of varying sizes without the need to change any code. Modifying a merge table is quick and
easy, so you can create a sliding 3, 6 and 12 month tables. The underlying table stay the same.

If you expect many inserts while long searches are going on, MyISAM will be a problem. The searches will block the inserts, forcing
them to queue up. Depending on how busy the box is, this can become a problem. InnoDB won't have this issue, but it is slower.

I've actually done hybrid setups where insert tables are InnoDB and "search" tables are MyISAM. Periodically (i.e. daily) the InnoDB
data is imported into the MyISAM tables, then dropped and recreated. Your code needs to know that it needs to do a UNION between the
MyISAM and InnoDB table to get the most up to date information. While more complicated, it has actually worked fairly well. At least
up to about 450 million rows so far.

----- Original Message -----
From: "Olaf Stein" <steino@ccri.net>
To: "MySql" <mysql@lists.mysql.com>
Sent: Thursday, January 25, 2007 4:17 PM
Subject: Innodb, why not?


> Hi All
>
> I know the innodb vs myisam issue comes up quite frequently. I went through
> old threads and could not find an answer to my questions.
>
> Generally, is there any reason/scenario not to use innodb?
>
> From a feature perspective, I do not need full text indices, foreign keys
> are usefull but not necessary (if I write the applications accordingly),
> transactions are also usefull but not entirely necessary.
> Basically I have no excluding reasons for the one or the other.
>
> From a speed perspective, I do not have a lot of simultaneous connections
> but a lot of data. Some tables have several hundred million records
> (growing). I read somewhere that innodb loses performance once the size of
> the tables exceed the amount of RAM. Is that true and if yes, how bad is
> that loss?
>
> I know this is a very general question but it seems not to make any sense
> not to use innodb having such exotic features like foreign keys and
> transactions.
>
> Maybe some of you had this dilemma in the past and can offer some insight.
>
> Thanks in advance
> Olaf
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>

On Friday 26 January 2007 06:17, Olaf Stein wrote:

> From a feature perspective, I do not need full text indices,

This is about the only reason I've seen MyISAM promoted as table engine of
choice.

> I know this is a very general question but it seems not to make any sense
> not to use innodb having such exotic features like foreign keys and
> transactions.

Foreign keys, imho, are a really good thing for a database. While they may
not seem terribly important at first, consider your statement of "(if I write
the applications accordingly)". I note the plural here and point out that
foreign keys would centralize your data referencing, so you could write your
application in 10 different languages and the data relationships would still
say the same. Given the somewhat heterogeneous web development environment
people see today (I've seen PHP and Java for example), it makes it a good
idea as any to use the table.

Another thing to consider is:


--
Chris White
PHP Programmer
Interfuel

> Another thing to consider is:

heh, silly mail client . Another thing to consider is this:

http://dev.mysql.com/doc/falcon/en/index.html

Though it's "Not recommended for production use", I've heard people still use
it in production environments.

--
Chris White
PHP Programmer
Interfuel

Hi Olaf,

> I know the innodb vs myisam issue comes up quite frequently. I went
through
> old threads and could not find an answer to my questions.
>
> Generally, is there any reason/scenario not to use innodb?
>
> >From a feature perspective, I do not need full text indices, foreign keys
> are usefull but not necessary (if I write the applications accordingly),

If you think this statement makes sense, then by all means, don't use
InnoDB, cause you probably don't need transactions either (if all
your statements fully execute) or primary key constraints (cause you
know there's only 1 row with a given value) or you don't mind another
instance reading rows from your tables that don't have child rows
yet. Or whatever...

> transactions are also usefull but not entirely necessary.
> Basically I have no excluding reasons for the one or the other.
> I know this is a very general question but it seems not to make any sense
> not to use innodb having such exotic features like foreign keys and
> transactions.
>
> Maybe some of you had this dilemma in the past and can offer some insight.

Read a book on database systems.

I can highly recommend:
http://www.amazon.com/dp/0321197844?...M8C0734M9XVNA&


and this website:
http://www.dbdebunk.com/index.html

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Thu, 25 Jan 2007 16:17:41 -0500, Olaf Stein wrote:

> Hi All
>
> I know the innodb vs myisam issue comes up quite frequently. I went through
> old threads and could not find an answer to my questions.
>
> Generally, is there any reason/scenario not to use innodb?

Yes. There are reasons/scenarios not to use innodb.

If you can sacrifice transactional operations, use largely bulk inserts,
and have a system weighed heavily on the "read" side of the equation
(mostly SELECT with infrequent INSERTs) ....
And yes you can design things this way ...
THEN
MyISAM tables will perform *substantially* better than innodb, the special
features of which you will not be using.

The situation changes if you have a high percentage of single record
write operations. But if this is not the case, MyISAM becomes a most
excellent choice.

Thomas Bartkus

At 03:54 PM 1/25/2007, you wrote:
> > Another thing to consider is:
>
>heh, silly mail client . Another thing to consider is this:
>
>http://dev.mysql.com/doc/falcon/en/index.html
>
>Though it's "Not recommended for production use", I've heard people still use
>it in production environments.
>
>--

Chris,
Falcon doesn't currently support RI. And like Innodb, it requires
its own table space so it too may get fragmented.
http://dev.mysql.com/doc/falcon/en/s...-createdb.html and will likely
require packing (sweeping?) from time to time. It would be nice to see some
benchmarks compared to InnoDb and MyISAM.

Mike

Hi,

According to the manuel, Falcon is not yet optimized for performances,
so benchmarking it would not be fair.
And I do not recommand using the binary alpha release in production, you
could corrupt badly your database (some bugs has only been fixed a few
days ago concerning this corruption).

Regards,
Jocelyn Fournier
www.mesdiscussions.net

mos a écrit :
> At 03:54 PM 1/25/2007, you wrote:
>> > Another thing to consider is:
>>
>> heh, silly mail client . Another thing to consider is this:
>>
>> http://dev.mysql.com/doc/falcon/en/index.html
>>
>> Though it's "Not recommended for production use", I've heard people
>> still use
>> it in production environments.
>>
>> --
>
> Chris,
> Falcon doesn't currently support RI. And like Innodb, it requires
> its own table space so it too may get fragmented.
> http://dev.mysql.com/doc/falcon/en/s...-createdb.html and will
> likely require packing (sweeping?) from time to time. It would be nice
> to see some benchmarks compared to InnoDb and MyISAM.
>
> Mike