mysql issue

Steve Alligood steve at betterlinux.com
Wed Feb 8 16:02:30 MST 2012


Why would you run performance critical really large data sets on 
spindles? If it is performance critical, put your db on SSDs.

This entire discussion has been from a DBA standpoint.  From a SysAdmin 
standpoint, splitting out your tables into files makes life MUCH better 
when things go south.  In fact, MyISAM tables are MUCH better when 
things go south, as they are both easier and faster to repair and get 
back online.

In fact, if you have a lot of databases (or even more than one) using 
InnoDB tables and one of them has issues bad enough to where the 
autorepair doesn't work, you have to dump them all and re-import them 
all to be sure you got the bad one, as the page dump MySQL so nicely 
gives you does squat for telling you *which* database or even table is 
corrupt and is causing all of MySQL do stay offline.  Or you can try 
hide and seek and randomly try them until MySQL comes back online.  That 
one is fun, too.

I have also found that MyISAM is considerably faster for relatively 
simple datasets, especially if you are mostly doing inserts and not a 
lot of selects.  Constraints, checkpoints, row locking, etc, all add 
overhead.  In fact, if write speed is important, don't even do more 
indexes that you specifically need.

Basically, if you don't specifically need what InnoDB brings to the 
party, stick with MyISAM every time and you will have far less problems 
in a repair situation, simpler management, and faster writes.

-Steve


On 2/8/12 3:44 PM, Jason Van Patten wrote:
> On 2/8/2012 3:13 PM, Paul Seamons wrote:
>> I'd second that, but I'd add a "be sure to add innodb_file_per_table=1
>> to the my.cnf
> Be very careful with that entry. It can wreck your seek times once you
> get really large data sets and can make load balancing moot.
> Jason
>> Paul
>>
>> On 02/08/2012 12:24 PM, Jason Van Patten wrote:
>>> On 2/8/2012 11:50 AM, Merrill Oveson wrote:
>>>> MyISAM
>>>>
>>>>
>>> That might be the reason mysql locked in the first place. You get one
>>> rouge query that takes too long and every other query hitting  that
>>> table and all joins has to wait for it. InnoDB is a little slower, but
>>> it ill only lock rows affected by the query instead of the whole
>>> table/tables. Unless you are index text columns i would recommend
>>> switching to Innodb or setting up a watchdog on your process list. Most
>>> times the watchdog is a good move just so you have time to get innodb
>>> running (select * statements will all need to get paired down to the
>>> minimum)
>>>
>>> /*
>>> PLUG: http://plug.org, #utah on irc.freenode.net
>>> Unsubscribe: http://plug.org/mailman/options/plug
>>> Don't fear the penguin.
>>> */
>>>
>> /*
>> 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.
> */


-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3749 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20120208/d437fd4b/attachment-0001.bin 


More information about the PLUG mailing list