[DRBD-user] MySQL table type and configuration for DRBD/HA?

Lars Ellenberg Lars.Ellenberg at linbit.com
Thu Sep 30 02:30:30 CEST 2004

Note: "permalinks" may not be as permanent as we would like,
direct links of old sources may well be a few messages off.


/ 2004-09-29 11:56:25 -0600
\ Guy Davis:
> Hi all,
> 
> I have a question for the DRBD/HA/MySQL users on list.  Which table type 
> are you using?  Our dataset is currently all in MyISAM.  We've thought 
> about using InnoDB, but we're looking at about a 2x disk usage jump over 
> MyISAM along with slower insert speed.  Insert rate is very important 
> for us.
> 
> The reason I ask is that we're able to simulate a failover with DRBD and 
> HA in which the primary doesn't fully write the index files to disk 
> before failure.  The secondary starts MySQL and it complains about 
> corrupted index files.
> 
> Is anyone using MySQL like this?  I think if we configure MySQL to flush 
> buffers after every write this risk will be minimized, but I'm worried 
> about the performance hit.  Any other ideas?

after a hard crash and failover,
from the application point of view,
the situation is the same as if you have just a single server box,
it crashed hard, and you rebooted it.
that means your services are expected to treat it as such.
this includes for example replaying
the journal of the file systems on mount.

but it also includes doing all the stuff that your application suggest
you to do to recover from a hard crash.
in mysql with myisam, this *must* include running myisamchk
at least in --fast --check mode, more likely --recover.
because the tables can not have been properly closed.
and yes, if you want "transactional" with myisam, then you need to at
least "flush tables" before you consider an update on-disk.
for performance reasons you may chose to have a periodic task doing
flush tables with write read lock, and then update something special
e.g. timestamp of "last_flushed", just to have a sync point in the
binary log, which is always written transactional ...  that way you can
make sure that you have to replay no more than (your choice) updates in
the worst case...

strictly speaking, all those recovery steps are neccessary with
every hard crash of your application, thus with each non-gracefull
failover.
 * check the tables, if necessary recover
 * check whether you lost some updates on the way,
   if necessary replay them from the log
whether you use "transactional" or not.
no way around that.


	Lars Ellenberg

-- 
please use the "List-Reply" function of your email client.



More information about the drbd-user mailing list