Tag Archives: replication

Replication enhancements in MySql 5.5

Recently we upgraded all our databases from MySql 5.1 to 5.5 Version and we see Couple of good enhancements done on the Replication side, couple of them i noticed are.

1) Ignore any DDL commands if they doesn’t comply with master : For ex: in a  Master-Master Setup environment

Master1 (MySql 5.1 Version)  – Slave of Master2
Master2 (MySql 5.1 Version)  – Slave of Master 1
Replica 1 (Mysql 5.5 Version)
Replica 2 (MySql 5.5 Version)

i)  we had 2 temporary tables created on Master and replicated to all the slaves where slaves are upgraded to Mysql 5.5 and Both Masters running MySql 5.1 version (bad scenario),
ii) I deleted 2 temporary tables with set Sql_log_bin=0  on master1 (not Master 2)
iii) I ran the same script on all Replication Slaves separately and Deleted temp tables.
iv) i forgot to set SQL_LOG_BIN=0 on Master2 and executed the script.  Master1  slave stopped with an error “unable to locate temp tables 1 and 2. but on Replication Slaves never been stopped or had any errors.  MySql 5.5 Version simply ignored those commands when tables doesn’t exist.

This is great enhancement.

Read the rest of this entry »


Tags: , , , , , , , , , ,

Improving Replication Performance

Have  you ever seen your replica not catching upto speed even though you have everything setup properly in your config file, i faced this situation when i setup a new replication slave and replication lag keeps increasing or not catching up fast,  tried many ways to tweak the my.cnf  configuration file, increased memory, modified buffer_pool_size to the max limit of the memory, increased additional buffer, added more cpu’s does not helped me much to fix the replication lag. finally after reading to some other blogs i found  innodb_flush_log_at_trx_commit =1 is the reason for replication lag,

When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file

Solution: Set the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.

innodb_flush_log_at_trx_commit = 1

Hope this helps..


Tags: , , , , , , , , ,

%d bloggers like this: