Jason Van Patten
jason at infogenix.com
Wed Feb 8 16:49:07 MST 2012
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
>> 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.
> ;-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.
> 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
More information about the PLUG