----- Am 25. Mrz 2016 um 21:54 schrieb shawn l.green shawn.l.gr...@oracle.com:
> 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
