… this with care: you MUST use row-based binlogging and replication, and remember that the execution is not serializable. PZ: Cool… Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty pages per flush. That means that under a heavy write load, a new flush… way to get good performance is to set innodb_flush_log_at_trx_commit=2, that is, to flush the log to the disk only once per second…
Post: Jeremy Cole on MySQL Replication
May 27, 2006 By 12 Comments
… operations to 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: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… unless you’re piping large blobs to Innodb in this case increase it a bit. innodb_flush_log_at_trx_commit=2 If you’re not concern… your application and default which is 8 is decent start innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure… row level replication. There are bunch of other options you may want to tune but lets focus only on Innodb ones today…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
… problems with replication lag (and performance in general). Typical problems for replication include setting sync_binlog=1, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1… to analyze your replication traffic regularly with mk-query-digest (Percona Server marks Slave Thread in slow query log so it is…
Post: Storing MySQL Binary logs on NFS Volume
July 30, 2010 By 19 Comments
… binary log stored on NFS volume. This number is for sync_binlog=0 and innodb_flush_log_at_trx_commit=2 I noted however if I enable replication…. If I would pause replication for prolonged period of time and let few GB of binary logs to accumulate the performance on Master will be high even with replication running, but it will…
Post: Maximal write througput in MySQL
February 28, 2010 By 10 Comments
…trx_commit=0 : innodb_flush_log_at_trx_commit = 0 and no binary logs trx_commit=1 : innodb_flush_log_at_trx_commit = 1 and no binary logs 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
…Innodb could do is at least to make innodb_flush_log_at_trx_commit a session variable, this is really where it belongs as decision how to commit…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_flush_log_at_trx_commitblogpercona.commanual innodb_flush_methodblogpercona.commanual innodb_force_recoveryblogpercona.commanual Innodb_have_atomic_builtinsblogpercona.commanual innodb_io_capacityblogpercona.commanual innodb_lock_wait_timeoutblogpercona.commanual innodb…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
… have innodb_flush_log_at_trx_commit=0 or 2 or if you use MyISAM tables you would not have the problem, if you have innodb_flush_log_at_trx_commit=1 log flushes… mysql instance (or simply the database which is not replicated) to log updates in heap/myisam/archive table and aggregate it in…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
… innodb_flush_logs_at_trx_commit=1 so transactions are truly durable. You have to have log-bin enabled to get replication or point in time recovery, but at… Disable Binary Logging This allows to get group commit back but obviously you loose point in time recovery and replication. Use innodb_flush_log_at_trx_commit=2 This…
