MyISAM better than innodb for large files?

I'm going to be setting up a MySQL database for a project. My reading
indicates that MyISAM (default) is going to be better than InnoDB for
the project but I want to be sure I have the trade-offs right.


This is going to be a very large data file -- many gigabytes -- only
used internally, and once installed perhaps updated once a year,
queried much more often.

MyISAM apparently has the advantage in memory and time overheads.

InnoDB's advantage seems to be better recovery from disk crashes.

Should I stick with MyISAM (MySQL default), or does the recovery issue
mean I'm better off using InnoDB for an insurance policy?

Inexperienced minds want to know -- ideally, from experienced minds.

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MyISAM better than innodb for large files?

InnoDB won't give you much in terms of disk crash recovery. That's what
backups are for.

Where InnoDB does excel is if your database server dies while updating
rows. If that happens, your database will come back up with sane data.

For both table types, once the data has been flushed to disk, the data
will still be there if your db server crashes.

It does indeed sound like you will be better off using MyISAM. This will
also reduce your disk space usage considerably.

/ Carsten

Mitchell Maltenfort skrev:
> I'm going to be setting up a MySQL database for a project. My reading
> indicates that MyISAM (default) is going to be better than InnoDB for
> the project but I want to be sure I have the trade-offs right.
>
>
> This is going to be a very large data file -- many gigabytes -- only
> used internally, and once installed perhaps updated once a year,
> queried much more often.
>
> MyISAM apparently has the advantage in memory and time overheads.
>
> InnoDB's advantage seems to be better recovery from disk crashes.
>
> Should I stick with MyISAM (MySQL default), or does the recovery issue
> mean I'm better off using InnoDB for an insurance policy?
>
> Inexperienced minds want to know -- ideally, from experienced minds.
>
> Thanks!
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: MyISAM better than innodb for large files?

I disagree. There's nothing about his requirements that sounds like MyIsam=
is a better solution. InnoDB should be your default for all tables, unles=
s you have specific requirements that need myisam. One specific example of=
an appropriate task for myisam is where you need very high insert throughp=
ut, and you're not doing any updates/deletes concurrently.

You want the crash safety and data integrity that comes with InnoDB. Even =
more so as your dataset grows. It's performance is far better than myisam =
tables for most OLTP users, and as your number of concurrent readers and wr=
iters grows, the improvement in performance from using innodb over myisam b=
ecomes more pronounced.

Regards,
Gavin Towey

-----Original Message-----
From: Carsten Pedersen [mailto:carsten@bitbybit.dk]
Sent: Friday, April 02, 2010 12:58 PM
To: Mitchell Maltenfort
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM better than innodb for large files?

InnoDB won't give you much in terms of disk crash recovery. That's what
backups are for.

Where InnoDB does excel is if your database server dies while updating
rows. If that happens, your database will come back up with sane data.

For both table types, once the data has been flushed to disk, the data
will still be there if your db server crashes.

It does indeed sound like you will be better off using MyISAM. This will
also reduce your disk space usage considerably.

/ Carsten

Mitchell Maltenfort skrev:
> I'm going to be setting up a MySQL database for a project. My reading
> indicates that MyISAM (default) is going to be better than InnoDB for
> the project but I want to be sure I have the trade-offs right.
>
>
> This is going to be a very large data file -- many gigabytes -- only
> used internally, and once installed perhaps updated once a year,
> queried much more often.
>
> MyISAM apparently has the advantage in memory and time overheads.
>
> InnoDB's advantage seems to be better recovery from disk crashes.
>
> Should I stick with MyISAM (MySQL default), or does the recovery issue
> mean I'm better off using InnoDB for an insurance policy?
>
> Inexperienced minds want to know -- ideally, from experienced minds.
>
> Thanks!
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

In the last episode (Apr 02), Gavin Towey said:
> I disagree. There's nothing about his requirements that sounds like
> MyIsam is a better solution. InnoDB should be your default for all
> tables, unless you have specific requirements that need myisam. One
> specific example of an appropriate task for myisam is where you need very
> high insert throughput, and you're not doing any updates/deletes
> concurrently.
>
> You want the crash safety and data integrity that comes with InnoDB. Even
> more so as your dataset grows. It's performance is far better than myisam
> tables for most OLTP users, and as your number of concurrent readers and
> writers grows, the improvement in performance from using innodb over
> myisam becomes more pronounced.

His scenario is "perhaps updated once a year", though, so crash recovery and
multiple writer performance is not important.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MyISAM better than innodb for large files?

>> You want the crash safety and data integrity that comes with InnoDB. =A0=
Even
>> more so as your dataset grows. =A0It's performance is far better than my=
isam
>> tables for most OLTP users, and as your number of concurrent readers and
>> writers grows, the improvement in performance from using innodb over
>> myisam becomes more pronounced.
>
> His scenario is "perhaps updated once a year", though, so crash recovery =
and
> multiple writer performance is not important.

And the concurrent reader and writer number is set at one, unless I
undergo mitosis or something.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

Ah, if you are single-user and updating really is a special occasion
that is completely in your control, you could even use compressed
MyISAM. That makes the table read-only though, but it does give
performance benefits:
http://dev.mysql.com/doc/refman/4.1/en/myisampack.html

good luck!

Walter Heck
Engineer @ Open Query (http://openquery.com)

On Sat, Apr 3, 2010 at 08:50, Mitchell Maltenfort wrote=
:
>>> You want the crash safety and data integrity that comes with InnoDB. =
 Even
>>> more so as your dataset grows.  It's performance is far better tha=
n myisam
>>> tables for most OLTP users, and as your number of concurrent readers an=
d
>>> writers grows, the improvement in performance from using innodb over
>>> myisam becomes more pronounced.
>>
>> His scenario is "perhaps updated once a year", though, so crash recovery=
and
>> multiple writer performance is not important.
>
> And the concurrent reader and writer number is set at one, unless I
> undergo mitosis or something.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dlists@o=
lindata.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

Didn't even know that one existed. It has an attraction, esp. in
terms of backing up the data.

But the link refers to the performance benefit in accessing one line
at a time. Supposing I was doing a search for all records where a
particular string is present -- what would the overhead be in the
searching of the compressed file?


On Fri, Apr 2, 2010 at 9:24 PM, Walter Heck - OlinData.com
wrote:
> Ah, if you are single-user and updating really is a special occasion
> that is completely in your control, you could even use compressed
> MyISAM. That makes the table read-only though, but it does give
> performance benefits:
> http://dev.mysql.com/doc/refman/4.1/en/myisampack.html
>
> good luck!
>
> Walter Heck
> Engineer @ Open Query (http://openquery.com)
>
> On Sat, Apr 3, 2010 at 08:50, Mitchell Maltenfort wro=
te:
>>>> You want the crash safety and data integrity that comes with InnoDB. =
=A0Even
>>>> more so as your dataset grows. =A0It's performance is far better than =
myisam
>>>> tables for most OLTP users, and as your number of concurrent readers a=
nd
>>>> writers grows, the improvement in performance from using innodb over
>>>> myisam becomes more pronounced.
>>>
>>> His scenario is "perhaps updated once a year", though, so crash recover=
y and
>>> multiple writer performance is not important.
>>
>> And the concurrent reader and writer number is set at one, unless I
>> undergo mitosis or something.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dlists@olinda=
ta.com
>>
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: MyISAM better than innodb for large files?

> From: Gavin Towey
>
> InnoDB should be your default for all tables, unless you have
> specific requirements that need myisam. One specific example of an
> appropriate task for myisam is where you need very high insert
> throughput, and you're not doing any updates/deletes concurrently.

A couple other things: InnoDB does relations better, MyISAM does
search of text fields.


----------------
If we can control fuel we can control the masses; if we can control
food we can control individuals. -- Henry Kissinger
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MyISAM better than innodb for large files?

Also depends on your data access pattern as well.
If you can take advantage of clustering my primary key for your
selects, then InnoDB could do it for you.
My suggestion would be to write some queries based on projected
workload, build 2 tables with lots and lots of data, and do some
isolated testing. For work, I do a lot of query profiling using
maatkit. Be sure to clear out as much of the caching as possible
including the OS cache.
BTW, I've never had much luck storing large docs in MySQL. If you can
compromise on data integrity, consider filesystem storage.
Kyong

On Fri, Apr 2, 2010 at 5:50 PM, Mitchell Maltenfort wro=
te:
>>> You want the crash safety and data integrity that comes with InnoDB. =
=A0Even
>>> more so as your dataset grows. =A0It's performance is far better than m=
yisam
>>> tables for most OLTP users, and as your number of concurrent readers an=
d
>>> writers grows, the improvement in performance from using innodb over
>>> myisam becomes more pronounced.
>>
>> His scenario is "perhaps updated once a year", though, so crash recovery=
and
>> multiple writer performance is not important.
>
> And the concurrent reader and writer number is set at one, unless I
> undergo mitosis or something.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

Also depends on your data access pattern as well.
If you can take advantage of clustering my primary key for your
selects, then InnoDB could do it for you.
My suggestion would be to write some queries based on projected
workload, build 2 tables with lots and lots of data, and do some
isolated testing. For work, I do a lot of query profiling using
maatkit. Be sure to clear out as much of the caching as possible
including the OS cache.

On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman wrote:
>> From: Gavin Towey
>>
>> InnoDB should be your default for all tables, unless you have specific
>> requirements that need myisam. =A0One specific example of an appropriate=
task
>> for myisam is where you need very high insert throughput, and you're not
>> doing any updates/deletes concurrently.
>
> A couple other things: InnoDB does relations better, MyISAM does search o=
f
> text fields.
>
>
> ----------------
> If we can control fuel we can control the masses; if we can control food =
we
> can control individuals. -- Henry Kissinger
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

At 09:10 PM 4/7/2010, you wrote:
>Also depends on your data access pattern as well.
>If you can take advantage of clustering my primary key for your
>selects, then InnoDB could do it for you.
>My suggestion would be to write some queries based on projected
>workload, build 2 tables with lots and lots of data, and do some
>isolated testing. For work, I do a lot of query profiling using
>maatkit. Be sure to clear out as much of the caching as possible
>including the OS cache.

In a related topic, does anyone know how well InnoDb is going to perform if
you have a 250 million row table (100gb) and only 8gb of RAM? It was my
understanding that InnoDb needed to fit as much of the table into memory as
it could for it to be fast. Also, how long is it going to take to load 250
million rows (using Load Data InFile) compared to a MyISAM table? I've
always found InnoDb to be incredibly slow at loading large amounts of data
and nothing I could think of would speed things up. I too would like to
switch to InnoDb but until I can solve these problem I'm sticking with
MyISAM for large tables.

Mike


>On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman wrote:
> >> From: Gavin Towey
> >>
> >> InnoDB should be your default for all tables, unless you have specific
> >> requirements that need myisam. One specific example of an appropriate
> task
> >> for myisam is where you need very high insert throughput, and you're not
> >> doing any updates/deletes concurrently.
> >
> > A couple other things: InnoDB does relations better, MyISAM does search of
> > text fields.
> >
> >
> > ----------------
> > If we can control fuel we can control the masses; if we can control food we
> > can control individuals. -- Henry Kissinger
> > :::: Jan Steinman, EcoReality Co-op ::::
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykimdba@gmail.com
> >
> >
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MyISAM better than innodb for large files?

We've seen good results throwing more RAM to the buffer pool.
It is true that InnoDB data never gets accessed directly on disk.
The only downside I know of with a larger buffer pool is slower restarts.

The load speed depends on the order of the inserts.
Random inserts or updates to primary key will cause result in very
poor performance.
I once ran a test doing completely random insert to InnoDB with a very
small buffer pool on my VM dev machine and it took days to load a
million rows before finally failing. Keep in mind that there may have
been other factors at work as well (we had a rather unusual indexing
strategy which worked for our use case). If you can pre-sort your load
file by primary key order, your load speed should be much better.

In terms of loading data, I doubt you will see better performance with
InnoDB than MyISAM. Our selection was heavily biased towards data
access. I have heard that InnoDB insert buffer scales much more
linearly than MyISAM but I don't know the details. We clustered our
data using a longer composite primary key and saw fairly good data
access performance.

I would caution against InnoDB if you foresee heavy random inserts.
Kyong


On Thu, Apr 8, 2010 at 8:21 AM, mos wrote:
> At 09:10 PM 4/7/2010, you wrote:
>>
>> Also depends on your data access pattern as well.
>> If you can take advantage of clustering my primary key for your
>> selects, then InnoDB could do it for you.
>> My suggestion would be to write some queries based on projected
>> workload, build 2 tables with lots and lots of data, and do some
>> isolated testing. For work, I do a lot of query profiling using
>> maatkit. Be sure to clear out as much of the caching as possible
>> including the OS cache.
>
> In a related topic, does anyone know how well InnoDb is going to perform =
if
> you have a 250 million row table (100gb) and only 8gb of RAM? It was my
> understanding that InnoDb needed to fit as much of the table into memory =
as
> it could for it to be fast. Also, how long is it going to take to load 25=
0
> million rows (using Load Data InFile) compared to a MyISAM table? I've
> always found InnoDb to be incredibly slow at loading large amounts of dat=
a
> and nothing I could think of would speed things up. =A0I too would like t=
o
> switch to InnoDb but until I can solve these problem I'm sticking with
> MyISAM for large tables.
>
> Mike
>
>
>> On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman wrote:
>> >> From: Gavin Towey
>> >>
>> >> InnoDB should be your default for all tables, unless you have specifi=
c
>> >> requirements that need myisam. =A0One specific example of an appropri=
ate
>> >> task
>> >> for myisam is where you need very high insert throughput, and you're
>> >> not
>> >> doing any updates/deletes concurrently.
>> >
>> > A couple other things: InnoDB does relations better, MyISAM does searc=
h
>> > of
>> > text fields.
>> >
>> >
>> > ----------------
>> > If we can control fuel we can control the masses; if we can control fo=
od
>> > we
>> > can control individuals. -- Henry Kissinger
>> > :::: Jan Steinman, EcoReality Co-op ::::
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@g=
mail.com
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmos99@fastma=
il.fm
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MyISAM better than innodb for large files?

Kyong,
Thanks for the feedback on InnoDb. I will tinker with it when I have
more time. I wonder if MySQL will ever release an alternative to Innodb
like Falcon or whether Falcon is dead as a dodo? :-)

Mike

At 11:07 PM 4/8/2010, Kyong Kim wrote:
>We've seen good results throwing more RAM to the buffer pool.
>It is true that InnoDB data never gets accessed directly on disk.
>The only downside I know of with a larger buffer pool is slower restarts.
>
>The load speed depends on the order of the inserts.
>Random inserts or updates to primary key will cause result in very
>poor performance.
>I once ran a test doing completely random insert to InnoDB with a very
>small buffer pool on my VM dev machine and it took days to load a
>million rows before finally failing. Keep in mind that there may have
>been other factors at work as well (we had a rather unusual indexing
>strategy which worked for our use case). If you can pre-sort your load
>file by primary key order, your load speed should be much better.
>
>In terms of loading data, I doubt you will see better performance with
>InnoDB than MyISAM. Our selection was heavily biased towards data
>access. I have heard that InnoDB insert buffer scales much more
>linearly than MyISAM but I don't know the details. We clustered our
>data using a longer composite primary key and saw fairly good data
>access performance.
>
>I would caution against InnoDB if you foresee heavy random inserts.
>Kyong
>
>
>On Thu, Apr 8, 2010 at 8:21 AM, mos wrote:
> > At 09:10 PM 4/7/2010, you wrote:
> >>
> >> Also depends on your data access pattern as well.
> >> If you can take advantage of clustering my primary key for your
> >> selects, then InnoDB could do it for you.
> >> My suggestion would be to write some queries based on projected
> >> workload, build 2 tables with lots and lots of data, and do some
> >> isolated testing. For work, I do a lot of query profiling using
> >> maatkit. Be sure to clear out as much of the caching as possible
> >> including the OS cache.
> >
> > In a related topic, does anyone know how well InnoDb is going to perform if
> > you have a 250 million row table (100gb) and only 8gb of RAM? It was my
> > understanding that InnoDb needed to fit as much of the table into memory as
> > it could for it to be fast. Also, how long is it going to take to load 250
> > million rows (using Load Data InFile) compared to a MyISAM table? I've
> > always found InnoDb to be incredibly slow at loading large amounts of data
> > and nothing I could think of would speed things up. I too would like to
> > switch to InnoDb but until I can solve these problem I'm sticking with
> > MyISAM for large tables.
> >
> > Mike
> >
> >
> >> On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman wrote:
> >> >> From: Gavin Towey
> >> >>
> >> >> InnoDB should be your default for all tables, unless you have specific
> >> >> requirements that need myisam. One specific example of an appropriate
> >> >> task
> >> >> for myisam is where you need very high insert throughput, and you're
> >> >> not
> >> >> doing any updates/deletes concurrently.
> >> >
> >> > A couple other things: InnoDB does relations better, MyISAM does search
> >> > of
> >> > text fields.
> >> >
> >> >
> >> > ----------------
> >> > If we can control fuel we can control the masses; if we can control food
> >> > we
> >> > can control individuals. -- Henry Kissinger
> >> > :::: Jan Steinman, EcoReality Co-op ::::
> >> >
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykimdba@gmail.com
> >> >
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykimdba@gmail.com
> >
> >


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MyISAM better than innodb for large files?

--0-1600018628-1270873257=:28444
Content-Type: text/plain; charset=us-ascii

Which one is more suitable for developing an ERP Application ? MyISAM or InnoDB ?

Are there other tools to backup MySQL Database than Standard GUI Tools which MySQL provide in the website ? So far, I use this GUI tools and setup an automatic backup on 9AM everyday. Is this backup tools is reliable enough ?

Regards,
Lucky.




________________________________
From: mos
To: mysql@lists.mysql.com
Sent: Fri, April 9, 2010 10:03:26 PM
Subject: Re: MyISAM better than innodb for large files?

Kyong,
Thanks for the feedback on InnoDb. I will tinker with it when I have
more time. I wonder if MySQL will ever release an alternative to Innodb
like Falcon or whether Falcon is dead as a dodo? :-)

Mike

At 11:07 PM 4/8/2010, Kyong Kim wrote:
>We've seen good results throwing more RAM to the buffer pool.
>It is true that InnoDB data never gets accessed directly on disk.
>The only downside I know of with a larger buffer pool is slower restarts.
>
>The load speed depends on the order of the inserts.
>Random inserts or updates to primary key will cause result in very
>poor performance.
>I once ran a test doing completely random insert to InnoDB with a very
>small buffer pool on my VM dev machine and it took days to load a
>million rows before finally failing. Keep in mind that there may have
>been other factors at work as well (we had a rather unusual indexing
>strategy which worked for our use case). If you can pre-sort your load
>file by primary key order, your load speed should be much better.
>
>In terms of loading data, I doubt you will see better performance with
>InnoDB than MyISAM. Our selection was heavily biased towards data
>access. I have heard that InnoDB insert buffer scales much more
>linearly than MyISAM but I don't know the details. We clustered our
>data using a longer composite primary key and saw fairly good data
>access performance.
>
>I would caution against InnoDB if you foresee heavy random inserts.
>Kyong
>
>
>On Thu, Apr 8, 2010 at 8:21 AM, mos wrote:
> > At 09:10 PM 4/7/2010, you wrote:
> >>
> >> Also depends on your data access pattern as well.
> >> If you can take advantage of clustering my primary key for your
> >> selects, then InnoDB could do it for you.
> >> My suggestion would be to write some queries based on projected
> >> workload, build 2 tables with lots and lots of data, and do some
> >> isolated testing. For work, I do a lot of query profiling using
> >> maatkit. Be sure to clear out as much of the caching as possible
> >> including the OS cache.
> >
> > In a related topic, does anyone know how well InnoDb is going to perform if
> > you have a 250 million row table (100gb) and only 8gb of RAM? It was my
> > understanding that InnoDb needed to fit as much of the table into memory as
> > it could for it to be fast. Also, how long is it going to take to load 250
> > million rows (using Load Data InFile) compared to a MyISAM table? I've
> > always found InnoDb to be incredibly slow at loading large amounts of data
> > and nothing I could think of would speed things up. I too would like to
> > switch to InnoDb but until I can solve these problem I'm sticking with
> > MyISAM for large tables.
> >
> > Mike
> >
> >
> >> On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman wrote:
> >> >> From: Gavin Towey
> >> >>
> >> >> InnoDB should be your default for all tables, unless you have specific
> >> >> requirements that need myisam. One specific example of an appropriate
> >> >> task
> >> >> for myisam is where you need very high insert throughput, and you're
> >> >> not
> >> >> doing any updates/deletes concurrently.
> >> >
> >> > A couple other things: InnoDB does relations better, MyISAM does search
> >> > of
> >> > text fields.
> >> >
> >> >
> >> > ----------------
> >> > If we can control fuel we can control the masses; if we can control food
> >> > we
> >> > can control individuals. -- Henry Kissinger
> >> > :::: Jan Steinman, EcoReality Co-op ::::
> >> >
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykimdba@gmail.com
> >> >
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykimdba@gmail.com
> >
> >


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=luckyx_cool_boy@yahoo.com



--0-1600018628-1270873257=:28444--