Web lists-archives.com

Re: need help from the list admin




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


--
Bernd Lentes

Systemadministration
institute of developmental genetics

Wer Visionen hat soll zum Hausarzt gehen
Helmut Schmidt


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