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