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.
>> 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
>> >> 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