mysql issue

Steve Alligood steve at betterlinux.com
Wed Feb 8 16:37:39 MST 2012


OK, I see that we are all ultimately saying the same thing: Know your 
setup and customize for it.

I agree with you 100%.  InnoDB will go down less, especially if you set 
it up correctly, and for system critical setups you should definitely 
have replicas, and backups, and load balancers, etc.  When the data is 
important enough, the money will be there.  And yes, sometimes you have 
to slap the in-house developers.

I also agree with Steve Meyers on the other emails in this thread.  
InnoDb has a lot of neat stuff in it, and is a lot better than it used 
to be (there are reasons Oracle bought it, some even have to do with the 
code).  When it makes sense, I definitely use it.  Customer tables, yes; 
general business data, yes; high volume audit log, no.  Customer's pet 
blog, depends on the software.

In the past, I have done a lot of work with ISPs and shared hosting, 
etc, and most of their setups are single server, doing mysql for their 
pet blog.  A lot of this opensource stuff used InnoDB simply because it 
looks cool or the book they bought says to use it, and it causes a lot 
of issues due to not being setup very well, which causes issues for 
other users as MySQl doesn't expect to have thousands of databases all 
doing InnoDB poorly, and when the inevitable crash comes it doesn't 
bother to tell you which user, which database, or which table, just a 
memory pagedump, which can be very annoying playing "to whom does the 
wordpress data belong to".

-Steve

On 2/8/12 4:17 PM, Jason Van Patten wrote:
> On 2/8/2012 4:02 PM, Steve Alligood wrote:
>> 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
> And then your socket volume spikes and your down. Once you get to the
> point that you are having crashes you should at least look at switching.
> That and/or slap your developers for not optimizing queries. as for
> fixing things when they go south yes myisam is easier, but your risk of
> going down in the first place is higher. Furthermore a competent backup
> system and recovery code can make innodb down time negligible.
>
> Jason
>>
>> 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)
>>>>>
>>>>>


-------------- 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/9abcbda0/attachment-0001.bin 


More information about the PLUG mailing list