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 <mo...@fastmail.fm> 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 <j...@bytesmiths.com> wrote: >> >> From: Gavin Towey <gto...@ffn.com> >> >> >> >> 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=kykim...@gmail.com >> > >> > >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
