1

I'm looking into sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 to improve write performance. There is this question, but it doesn't address the specific problems with replication.

I have an application that processes incoming log data with dozens of commits per second, causing a lot of flushing to disk, and losing one second of data is not a problem for the increase in speed it gives. However, The docs state:

For durability and consistency in a replication setup that uses InnoDB with transactions [set both options to 1].

But it doesn't specify the issues with consistency and durability. I would like to know what exactly can go wrong. If you have a hardware crash and the slave becomes live, it may be one second behind the original master. Are there subsequent problems in getting that failed master back online (as slave)? Could there be mismatches in ID's, duplicate rows, etc?

As I said, loosing data is OK, but breaking replication is not.

Edit: BTW, by now, the problem is solved another way. Long story, but an comprehensive schema change made it possible run log processors in parallel. Still slow per process, but many at a time makes it fast.

Improve this question

2 Answers 2

Reset to default
1

Another way to get past these possible problems is to use a Galera Cluster (eg, MariaDB 10 or PXC). With Galera, you can (should) be sloppy about such syncs; instead rely on the cluster's ability to recover automatically and efficiently from a dead machine, whether dead from power, OS, or whatever. One caveat, though: You need to make sure a single failure (such as power) won't kill all nodes in the cluster simultaneously.

With Galera, you can write and read any node. 3 is the minimal number of nodes to provide automatic recovery from the loss of any one node. You can configure it to do all writes to a specific node, thereby pretending to have Master+Slaves configuration. (In some applications, this is actually beneficial.)

Back to your situation. Set both of those settings to 1 or the Master; set them to 50 and 2 for the Slaves, then... If a Slave dies, assume it is corrupt and rebuild it; do not try to recover the data, since the necessary syncs may not have been done. To lighten the load on the Master, don't send standalone reads to it.

Improve this answer
5
  • Thanks for the info. About the last paragraph, I don't have such control over master and slave at Amazon RDS. Also edited my post, because the problem is solved another way.
    – Halfgaar
    Dec 3, 2015 at 9:33
  • So the real problem was ingestion speed? In that case, I would have pointed you to High Speed Ingestion. One principle is to gather rows before doing COMMIT.
    – Rick James
    Dec 3, 2015 at 15:48
  • Actually, our setup is already kind of like that. We have huge a 'fact' table, with data flattened and minimized as much as possible from the php script, inserted with multi-value single statements. There are also aggregation and summary tables. Our previous standard VM with MySQL could do 80 per/s per process. On Amazon RDS, it's about 25 (also because the speed is very dependant on latency; connecting to localhost is faster). It's about 50 without all the safety measures for replication safety. I'll look at the ingestion article in more detail later. Perhaps it will give me some extra ideas.
    – Halfgaar
    Dec 3, 2015 at 20:17
  • Sounds like you know much of what is in my article, plus its two companions (DW and Summary Tables). Let me know if you find any problems with the articles and/or have suggestions for improving them.
    – Rick James
    Dec 3, 2015 at 23:31
  • There some interesting stuff on your site. That comprehensive migration I talked about was converting 500 GB to single table partitions, which you have written about. I'm not sure how, but it prevented deadlocking I had before when running parallel processes. Well, enough said; this is not a forum :).
    – Halfgaar
    Dec 4, 2015 at 8:25
0

So say you're running a transaction and innodb_flush_log_at_trx_commit=2. Say you hit commit and the cursor returns successfully - the "application" thinks it's all good. If your instance crashes at that point it might not have actually committed that transaction to disk (as it only commits to disk every ~1 second) - and as such, might not be picked up by the slave when it reconnects.

It's explained here: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

You have the potential to lose this data, and may need to reestablish the slave.

It's happened to me...

EDIT: sync_binlog=50 seems like a bad idea too - this means that transactions will not be written to disk immediately, taking you further away from ACID compliance. Ideally it should be set to 1. Further reading: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog

When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.

Improve this answer
3
  • I realize and explained that in my question. I even linked to the page you're linking to. I don't care if I loose data. I just want to make sure the replicated server looses it in the same way, kind of... BTW, setting sync_binlog doesn't mean transactions aren't synced to the log, it means the log isn't synced to disk. Big difference.
    – Halfgaar
    Nov 17, 2015 at 13:31
  • Yes, you're right - corrected my answer. Ultimately the transactions are not on disk though. I had a similar situation to this set up, the primary crashed a few times, every time it crashed, replication had to be set up again. The slave was trying to start from a position that the primary had rolled back from.
    – dwjv
    Nov 17, 2015 at 13:46
  • Hmm. That the replication had to be set up again is relevant info. Thanks.
    – Halfgaar
    Nov 17, 2015 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.