Re: need help from the list admin

----- Am 25. Mrz 2016 um 21:54 schrieb shawn l.green [email protected]:

> Hello Bernd,
> 
> Sorry for the delay, I wanted to make sure I had enough time to address
> all of your points.


>> 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.
> 

is semi-synchronous a good idea ? I think we just have several 100 inserts per day, so i believe the lag should not be a problem.

>> 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
> 

I will read that.

> 
>> 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.
> 

What is if the slave is behind and the master is gone ? So he has neither possibility to be up-to-date nor to catch up.


>>
>> 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

I will read that.
> 
> 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.
> 

Is this a big problem ? Something to take care of ? Currently we have a mix. 
I will ask the girl who developed it why we have both kinds. I hope i can convert.

 
>> 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.

So the write to the bin_log does not care about transaction ? It writes directly ? But the write to the db is done after the commit ?


> 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 ?
>>

>>
> 
> 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.

I will try that.


Hi Shawn,

thanks for this very profound and detailed answer. Great !


Bernd
 

Helmholtz Zentrum Muenchen
Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH)
Ingolstaedter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe
Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.)
Registergericht: Amtsgericht Muenchen HRB 6466
USt-IdNr: DE 129521671


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql