List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:April 9 2010 4:07am
Subject:Re: MyISAM better than innodb for large files?
View as plain text  
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 <mos99@stripped> 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 <Jan@stripped> wrote:
>> >> From: Gavin Towey <gtowey@stripped>
>> >>
>> >> 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=1
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
MyISAM better than innodb for large files?Mitchell Maltenfort2 Apr
  • Re: MyISAM better than innodb for large files?Carsten Pedersen2 Apr
    • RE: MyISAM better than innodb for large files?Gavin Towey2 Apr
      • Re: MyISAM better than innodb for large files?Dan Nelson2 Apr
        • Re: MyISAM better than innodb for large files?Mitchell Maltenfort3 Apr
          • Re: MyISAM better than innodb for large files?Walter Heck - OlinData.com3 Apr
            • Re: MyISAM better than innodb for large files?Mitchell Maltenfort3 Apr
          • Re: MyISAM better than innodb for large files?Kyong Kim6 Apr
RE: MyISAM better than innodb for large files?Jan Steinman5 Apr
  • Re: MyISAM better than innodb for large files?Kyong Kim8 Apr
    • Re: MyISAM better than innodb for large files?mos8 Apr
      • Re: MyISAM better than innodb for large files?Kyong Kim9 Apr
        • Re: MyISAM better than innodb for large files?mos9 Apr
          • Re: MyISAM better than innodb for large files?Lucky Wijaya10 Apr