With innodb_doublewrite OFF, can it cause data corruption in Aurora.

Because we are currently doing MySQL 5.6.3* vs Aurora benchmark.

One of the test was record insertion (2 days ago) -> MySQL 5.6.3* without raid and innodb_doublewrite = 0 and innodb_flush_log_at_trx_commit = 0 performing better than Aurora (We have a SP that does those kind of inserts).

And https://bugs.mysql.com/bug.php?id=78853, is fixed in MySQL 5.6.30 and not in current version of Aurora (Which i myself have tested and verified).

So here we are still thinking between MySQL 5.6.3* vs Aurora.

what are the risk factors in Aurora having innodb_doublewrite = 0? Can it cause data corruption or result in any kind of data loss ?

It would be great if you could please suggest your thoughts on it.

Thanks in Advance,
Harish

It feels very wrong how Amazon is using open-source code to create a closed-source system. While what they’re doing is legally ok, it certainly feels like it violates the spirit of the licensing agreement.

table2 has 2000 records only.

I translated this article for users in Japan
https://yakst.com/ja/posts/3550

If there is any problem, please contact me.
Thank you again.

My read of GPLv2 (I am not a lawyer either, so this should not be taken as a legal advise in anyway, only as my personal opinion)
is that GPLv2 requires to open source code only if you distribute software as by downloads or by hard copies (USB drives, network routers, etc).

The model, where you launch software by a mouse click, does not fit into GPLv2 distribution requirement.

Your MySQL connection id is 82422
Server version: 5.6.10 MySQL Community Server (GPL)

I’m not a lawyer, but I would expect that it is open source (no, I didn’t try to find source code).

The specific example you provide isn’t currently supported by Aurora under physical replication, though of course you can set up bin log replication to accomplish this. There isn’t an intrinsic reason for this to be the case. The selects on the replica are implemented as you’d expect – setting a read view and applying undo to obtain the read view version of a block if the current version is in future of the view – so read committed vs repeatable read is a matter of not resetting the read view with a subsequent statement. We’ll consider it if we hear customer demand.

While performance schema can be enabled for Aurora the current advice from Amazon is to not do that – there appears to be a problem with erroneous failovers in cluster when it’s turned on. I hope you fix this one soon. Vadim, slow query logs are certainly available and were critical in helping us diagnose a problem with binary log purging on Aurora last week. Only FILE logging is supported.

Thank you for all followup comments.

I do not quite follow re: changing an isolation mode on Writer.
It is gives a great flexibility of you can change an isolation mode *per transaction*.
That is some *READ-ONLY* transactions on Reader I would like to run in Repeatable-Read, and some other in Read-Commited. Is it possible?

Vadim, we provide release notes with each patch. I’ll ask my team to ensure they provide sufficient detail to understand which bugs we’ve fixed. And use a better version scheme.

Aurora does support performance schema – we just turn it off by default as it, as currently implemented, has significant overhead in MySQL (IMO).

On transaction isolation, you can change the isolation level on the write-master node and should see all the behavior you’d expect. The anomaly you’re seeing is on the read-replica, which 1) is read-only, and 2) inherits the write-ordering semantics of the write-master node. I would agree that the behavior is not intuitive, and we’ll put in a better error message.

I should add that writes are not synchronous as you assumed and would recommend you take a look at my talk from 2015 reInvent (you can find it on YouTube searching for DAT405), where I describe how write processing works in some detail. Basically, everything is async, including read replicas (which is where the lag comes from), but is all based on redo log records. We’re very focused on building a highly concurrent, high throughput system, and batching and asynchronous processing are basic techniques in doing so.

I also agree that we can do a better job with the version string, and will take a look at updating it.

Thank you for the comment.
As I understand there is no changelog and versions to understand what bugs where fixed in which version?

Aurora is heavily modified InnoDB, so on a Writer it should work InnoDB-like (I did not test this actually). And Reader does not support statements that change data, I do not think we can actually test this. Still it would make sense to make InnoDB-like snapshots…

Buffer page writes are zero because Aurora only writes log pages to the storage tier. That tier generates data block records on its own (similar to how log-structured storage systems work). No checkpointing or writing of dirty data blocks out of cache is required.

We disable flush_log_at_txn_commit modifications because it seemed like just a benchmark performance hack – it’s hard to believe a customer could really tolerate this in a real production system. It’s easy to reenable if it turns out that real customers want it.

We have our own CRC system which is pretty deeply integrated into how we write over the network, store data, scrub blocks etc. Which is why the MySQL checksum approach is disabled.

We’ll look into the other behavior anomalies you encountered.