On 2/8/2012 4:38 PM, Daniel Fussell wrote: > On 02/08/2012 03:31 PM, Jason Van Patten wrote: >>> To some degree, that's a myth. It may or may not be slower, depending >>> on the workload. I think in general it's faster these days, and scales >>> better. >>> >>> Steve >> This is true if the DB is properly designed, however most of the >> conversions i've ran from myisam to innodb dramatically and harshly >> affect performance until things get fixed. The main reasons are "Select >> *" statements in the code, lack of constraints and views, and probably >> the biggest cause is allowing null in the column definition and then >> trying to run a search on the column. Most db architects i've worked >> with make these mistakes at least once on mysql. Half of them don't >> learn and just rely on myisam and a watchdog, So generally i would say >> myisam is faster for your novice db admin, but i would agree that if you >> know how to do constraints and views properly you will leave myisam in >> the dust once you hit the 200k+ record range. >> > I understood the performance was backward to that. MyISAM is great for > heavy read loads, but is terrible for multiple concurrent write loads > due to the need to lock the whole table. InnoDB might be slower on > selects, but it's faster for multiple concurrent read/write loads. > > For example, years ago I setup a central global spambayes database with > 400k tokens, each record is perhaps 4 fields long, none of them NULL > (though I admit, I didn't know NULL fields created performance problems; > I was only aware of the potential for casting surprises.) > > Running the spambayes db on MyISAM with 5 mail servers using it was > painfully slow, spamassassin was constantly spinning, and my queues > quickly backed up. I converted the table to InnoDB and all my queues > suddenly cleared. Since then, I've had to increase the number of tokens > stored to 1M, and it still flies. I'm also keeping an AWL > (auto-whitelist) table that had the same problem after about 300k > records or so. I switched that to InnoDB as well, and it's now well > over 1M records, and still going strong. > > So if you have a heavy/mostly read load, use MyISAM. > > If it's a mixed read/write load, or you need foreign keys, transactions, > etc, use InnoDB. > > I've never used InnoDB with files per table option, so I can't comment > on that. I will say that my innodb file is roughly twice the size of > the tables it contains. > > Grazie, > ;-Daniel Fussell Well the difference is that when working with smaller datasets on poorly designed dbs myisam is faster. But like i said once you hit 200K+ records innodb will beat myisam query for query. Optimize it for innodb and you will see the innodb goodness flow. Jason > > /* > PLUG: http://plug.org, #utah on irc.freenode.net > Unsubscribe: http://plug.org/mailman/options/plug > Don't fear the penguin. > */ > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2012.0.1913 / Virus Database: 2112/4796 - Release Date: 02/08/12 > >
/* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
