mysql issue

Daniel Fussell 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
>> 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


More information about the PLUG mailing list