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? :-)
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
>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
>I would caution against InnoDB if you foresee heavy random inserts.
>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>
> >> >> From: Gavin Towey <gtowey@stripped>
> >> >>
> >> >> InnoDB should be your default for all tables, unless you have
> >> >> requirements that need myisam. One specific example of an
> >> >> task
> >> >> for myisam is where you need very high insert throughput, and
> >> >> not
> >> >> doing any updates/deletes concurrently.
> >> >
> >> > A couple other things: InnoDB does relations better, MyISAM does
> >> > of
> >> > text fields.
> >> >
> >> >
> >> > ----------------
> >> > If we can control fuel we can control the masses; if we can control
> >> > 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