sent for Bernd, and to see if it works from another sender
----------------------------------------------
Lentes, Bernd wrote:
Hi,
i know that there is a list dedicated to replication, but when
you have a look in the archive it's nearly complete empty. Really
not busy.
So i hope it's ok if i ask here.
we have a web app which runs a MySQL DB and dynamic webpages with
perl and apache httpd. Webpages serve reading and writing into
the db. The db is important for our own work flow, so i'd like to
make it HA. I have two HP servers and will use SLES 11 SP4 64bit
as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which
is available in SLES High Availibility Extension. I have
experience in linux, but i'm not a database administrator nor
developer. HA is important for us, we don't have performance
problems.
My first idea was to run the web app and the db in a virtual
machine on the host and in case of a failure of one host
pacemaker would run the vm on the other host. VM would be stored
on a FC SAN. I stopped following this idea. I have bought a book
about HA: "..." from Oliver Liebel. It's only available in
german. But i can recommend it, it's very detailed and well
explained.
He proposed to have two hosts, and on each is running a MySQL
instance as master AND slave. But it's not a "real multi master
solution", because pacemaker takes care that the IP for the web
app just points to one master. So i don't have the multi-master
problems with concurrent inserts (i believe).
His idea is that host A is master for the slave on host B, and
host B is the master for the slave on host A. OK ?
Let's imagining that the IP to the web app points to host A,
inserts are done to the master on host A and replicated to the
slave on host B. Now host A has problems, pacemaker redirects the
IP to host B, and everything should be fine.
What do you think about this setup ? Where is the advantage to a
"classical Master/Slave Replication" ? How should i configure
log-slave-updates in this scenario ?
Let's imagine i have two hosts again: Host A is master, host B is
slave. Nothing else. No real or pseudo "Multi-Master". IP points
to host A. Host A has problems, pacemaker recognizes it, promotes
B to master and pivot the IP. Everything should be fine. Where is
the disadvantage of this setup compared to the "Multi-Master
Replication" in the book ? The OCF ressource agent for mysql
should be able to handle the mysql stuff and the RA for the IP
pivots the IP.
Now some dedicated questions to replication. I read a lot in the
official documentation, but some things are not clear to me.
In our db we have MyISAM and InnoDB tables.
From what i read i'd prefer row based replication. The doc says
is the safest approach. But there seems to be still some problems:
The doc says: "For tables using the MYISAM storage engine, a
stronger lock is required on the slave for INSERT statements when
applying them as row-based events to the binary log than when
applying them as statements. This means that concurrent inserts
on MyISAM tables are not supported when using row-based
replication."
What does this exactly mean ? Concurrent inserts in MyISAM-tables
are not possible if using RBL ? Or unsafe in the meaning they
create inconsistencies ?
"RBL (Row Based Logging) and synchronization of nontransactional
tables. When many rows are affected, the set of changes is split
into several events; when the statement commits, all of these
events are written to the binary log. When executing on the
slave, a table lock is taken on all tables involved, and then
the rows are applied in batch mode. (This may or may not be
effective, depending on the engine used for the slave抯 copy of
the table.)"
What does that mean ? Effective ? Is it creating inconsistencies
? Or just not effective in the sense of slow or inconvinient ?
Or should i prefer MIXED for binlog_format ?
The doc says: " If a statement is logged by row and the session
that executed the statement has any temporary tables, logging by
row is used for all subsequent statements (except for those
accessing temporary tables) until all temporary tables in use by
that session are dropped.
This is true whether or not any temporary tables are actually
logged. Temporary tables cannot be logged using row-based format;
thus, once row-based logging is used, all subsequent statements
using that table are unsafe. The server approximates this
condition by treating all statements executed during the session
as unsafe until the session no longer holds any temporary tables."
What does that mean ? Unsafe ? Causing inconsistencies ? Problem
with SBL or RBL ?
The doc says: "Due to concurrency issues, a slave can become
inconsistent when a transaction contains updates to both
transactional and nontransactional tables. MySQL tries to
preserve causality among these statements by writing
nontransactional statements to the transaction cache, which is
flushed upon commit. However, problems arise when modifications
done to nontransactional tables on behalf of a transaction become
immediately visible to other connections because these changes
may not be written immediately into the binary log.
Beginning with MySQL 5.5.2, the
binlog_direct_non_transactional_updates variable offers one
possible workaround to this issue. By default, this variable is
disabled. Enabling binlog_direct_non_transactional_updates causes
updates to nontransactional tables to be written directly to the
binary log, rather than to the transaction cache.
binlog_direct_non_transactional_updates works only for statements
that are replicated using the statement-based binary logging
format; that is, it works only when the value of binlog_format is
STATEMENT, or when binlog_format is MIXED and a given statement
is being replicated using the statement-based format. This
variable has no effect when the binary log format is ROW, or when
binlog_format is set to MIXED and a given statement is replicated
using the row-based format.
Important:
Before enabling this variable, you must make certain that there
are no dependencies between transactional and nontransactional
tables; an example of such a dependency would be the statement
INSERT INTO myisam_table SELECT * FROM innodb_table. Otherwise,
such statements are likely to cause the slave to diverge from the
master."
Does that mean that "Due to concurrency issues, a slave can
become inconsistent when a transaction contains updates to both
transactional and nontransactional tables" is also a problem for
RBL ?
"... you must make certain that there are no dependencies between
transactional and nontransactional tables; an example of such a
dependency would be the statement INSERT INTO myisam_table SELECT
* FROM innodb_table. Otherwise, such statements are likely to
cause the slave to diverge from the master."
Are these statements problems when using RBL ?
Thanks for any answer.
Bernd
