List:General Discussion« Previous MessageNext Message »
From:mos Date:April 9 2010 3:03pm
Subject:Re: MyISAM better than innodb for large files?
View as plain text  
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 <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