dfussell at byu.edu
Wed Feb 8 16:38:09 MST 2012
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.
More information about the PLUG