… has no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication? It’s actually much worse… might consider relaxing the fsync: set innodb_flush_log_at_trx_commit to something besides 1 and suddenly you can update much…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 9 Comments
One common theme in the questions our MySQL Support customers ask is MySQL Replication Lag. The story is typically along the lines everything…). Typical problems for replication include setting sync_binlog=1, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1. Also note…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…innodb_commit_…at_trx_…replicate_do_dbblogpercona.commanual replicate_do_tableblogpercona.commanual replicate_ignore_dbblogpercona.commanual replicate_ignore_tableblogpercona.commanual replicate_rewrite_dbblogpercona.commanual replicate_same_server_idblogpercona.commanual replicate…
Post: Jeremy Cole on MySQL Replication
May 27, 2006 By 12 Comments
Jeremy Cole recently posted very nice post about MySQL Replication Performance. There are however few points I should comment on. … single stream so group commit can’t work. Furthermore even if you run with innodb_flush_logs_at_trx_commit=2 you still do not solve all problems. You still will practically have only One CPU to handle replication load…
Post: Common MySQL traps webinar questions followup
January 17, 2013 By 1 Comment
… any technical considerations or best practice tips to have a replicated slave in the cloud, for example on Amazon AWS? Hardware…_log_at_trx_commit) disable binary logging if it’s not useful look at the write queries that may cause replication lag and… nature. Q: Are these recommendations true of all versions of MySQL? If not which versions? The recommendations I made are valid…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication… show engine innodb status Trx read view will not see trx with id >= 83C, sees… Warnings: 0 node3 mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+…
Post: Maximal write througput in MySQL
February 28, 2010 By 10 Comments
… what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end… ( innodb_flush_log_at_trx_commit = 0 or 1 ) ? – do we use binary logs ( I used ROW based replication for 5.1) – do… trx_commit=0 & binlog : innodb_flush_log_at_trx_commit = 0 and binary logs trx_commit=1 & binlog : innodb_flush_log_at_trx_commit = 1 and binary logs trx_commit…
Post: MySQL should have dynamic durability settings
April 8, 2008 By 4 Comments
If you’re using Innodb tables MySQL has innodb_flush_log_at_trx_commit variable which defines how durable your transactions are. If… innodb_flush_log_at_trx_commit a session variable, this is really where it belongs as decision how to commit transaction can be… to flush logs for each transaction. Replication option could be implemented to utilize delayed commit for the replication thread. This is surely not…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
…_flush_logs_at_trx_commit=1 so transactions are truly durable. You have to have log-bin enabled to get replication or point… to get group commit back but obviously you loose point in time recovery and replication. Use innodb_flush_log_at_trx_commit=2 This is probably best solution. In many cases this would be good change to do with MySQL 4…
Post: MongoDB Approach to Availability
April 30, 2010 By 2 Comments
… mean commit to disk, but rather commit to network it does not mean commit to disk with MongoDB, furthermore MongoDB uses Asynchronous replication, meaning… can see it as a hybrid between MySQL Cluster and innodb_flush_log_at_trx_commit=2 mode. The second difference of course… committed data loss with almost instant fail over” but It makes sense for large number of applications. In fact using MySQL Replication for…
