Hello Bernd,
Sorry for the delay, I wanted to make sure I had enough time to address
all of your points.
On 3/22/2016 7:07 AM, william drescher wrote:
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).
This is wise advice. We (MySQL Support) often recommend exactly the same
setup: a master + one(or more) slave(s) using replication to keep the
slaves in relative sync. I say "relative" because replication is
asynchronous.
All writes are directed at the master. Clients that can tolerate the
natural lag of the replication system can use any available slave for
read-only queries.
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 ?
We have a page on that in the manual (with a diagram):
http://dev.mysql.com/doc/refman/5.6/en/replication-solutions-switch.html
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.
Remember to wait for the slave to catch up to the master it lost contact
with. That way its data is as current as possible. Then redirect your
clients to the new read-write node in your replication topology.
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 ?
"Unsafe" in that sense replies to the fact that certain commands can
have a different effect when processed from the Binary Log than they did
when they were executed originally on the system that wrote the Binary
Log. This would be true for both a point-in-time recovery situation and
for replication. The topic of unsafe commands is covered rather well on
these pages:
http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
http://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html
This is particularly true for commands that may cross transactional
boundaries and change non-transactional tables. The effect of those
commands are apparent immediately to any other user of the server. They
do not rely on the original transaction to complete with a COMMIT. The
workaround we employed was to keep the non-transactional table locked
(to keep others from altering it) until the transaction completes
(COMMIT or ROLLBACK). That way we do our best to make all changes
"permanent" at the same time.
"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 ?
You would be better served by first converting your MyISAM tables to
InnoDB to stop mixing storage engine behaviors (transactional and
non-transactional) within the scope of a single transaction. But if you
cannot convert them, using MIXED will be a good compromise.
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 ?
Again "unsafe" in this means "may cause a different effect on the slave
due to the Binary log being a serial record of data changing events
recorded as those events become permanent". The slave executes the
Binary Log in sequence but blending parallel transactional and
non-transactional behaviors on the master can create changes that are
out of sequence than what the slave can achieve using the data available
to the slave.
It's like time travel.
Look at this sequence and think what would happen without that "stronger
locking" you mentioned earlier.
(connection 1)
begin transaction
INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ...
(connection 2)
DELETE myisam_table WHERE ... (this removes one of the rows that
connection 1 just added)
(connection 1)
COMMIT
When the slave sees this sequence, it will get the command from
Connection2 first (it completed first so it winds up in the Binary Log).
It removed 8 rows on the master but it would only see 7 on the slave.
Why? The 8th row has not been added to the MyISAM table on the slave
because the transaction that does it hasn't been recorded to the Binary
Log yet.
That's why there is stronger locking comes into play. If we had not
blocked connection 2 until connection 1 completed things would be out of
temporally speaking. It's still possible for things to happen out of
sequence on the slave when mixing transactional and non-transactional
tables in the same transaction.
This takes us to the next point you have...
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
Because the commands ("events" in the terminology used to describe the
Binary Log) are not recorded until their transaction completes it is
possible for other earlier-finishing transactions looking at
non-transactional tables to make changes that are out of sequence.
It's possible for the two storage engines to co-exist but you need to be
careful or you will create the database equivalent to the "grandfather
paradox". This kind of self-protection needs to happen in your
application logic.
Only using one type of storage engine per transaction is the other way
to avoid the problem.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
