… so-called “binary log” of events that modify data in the server. The binary log is a real limitation in MySQL. The binary log is necessary not… transaction. And the server performs an XA transaction between InnoDB and the binary log. This adds more fsync calls, and causes mutex contention…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
… InnoDB, it is also important that your transaction logs…mysql> show binary logs; +——————+———–+ | Log_name | File_size | +——————+———–+ … | mysql-bin.000022 | 336797725 | | mysql-bin.000023 | 107 | +——————+———–+ 23 rows in set (0.00 sec) mysql…
Post: Binary log file size matters (sometimes)
May 24, 2012 By 6 Comments
… while I was really puzzled, looking for answers in MySQL status counters, innodb status, memory info and what not. It seemed that… mysql mysql 1073742566 Nov 3 08:35 /var/lib/mysqllogs/db1-bin-log.003234 What is happening here? Apparently, every time current binary log gets…, new binary log is created and, if expire_logs_days is not zeroish, log files older than expire_logs_days are removed. &LOCK_log mutex is…
Post: What is innodb_support_xa?
March 2, 2011 By 3 Comments
… XA, but also for internal XA coordination between the InnoDB transaction logs and the MySQL binary logs, to ensure that they are consistent. Consistent is… InnoDB performance. Having innodb_support_xa enabled on a replication master—or on any MySQL server where binary logging is in use—ensures that the binary log…
Post: Storing MySQL Binary logs on NFS Volume
July 30, 2010 By 19 Comments
… such setup. There are good reasons to have binary logs on NFS volume – binary logs is exactly the thing you want to survive the… of contention issues in MySQL 5.0 rather than any NAS issues. This number was reachable even with binary log stored on NFS volume. This number is for sync_binlog=0 and innodb_flush_log_at_trx_commit…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… the binary log breaking locking semantics and contrary to serialization concepts. For example take a look at the following excerpt from the binary log…(i int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert… int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just fail immediately…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… be serialized for the binary log; otherwise replaying the binary log can result in a different order of execution. The MySQL manual actually says this, but not in the clearest way. It just says If you are using the binary log, concurrent… INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something more elaborate and application…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… to prevent problems with the replication you should change the binary log format to “row”. Depending on the statement, the behavior of… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED…
Post: Just how useful are binary logs for incremental backups?
July 21, 2009 By 6 Comments
… before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of…? If you are using all InnoDB tables, an XtraBackup incremental backup should be much faster than using binary logs. You can understand… restore! Eventually this problem should be lessened by a MySQL Server feature – parallel execution on slaves. Lets hope that…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… be making MySQL Replication to work. You might need to inject MySQL 5.0 in between so it will convert binary log events, as… depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL 4.1 while Innodb storage engine… new release. Also note the default storage engine changed to Innodb in MySQL 5.5, meaning you might need to set storage…
