1

I am aware of global configuration "innodb_flush_log_at_trx_commit" in MySQL, but that is server wide.

I am looking for some way of specify that some transactions can be ACI instead of ACID, in order to reduce commit latency in exchange of risk of transaction lost if the database dies unexpectedly.

I would like to be able to specify durability guarantees transaction per transaction or, session/connection per session/connection.

Is that possible with MySQL 8.x?

UPDATE: Postgresql has this functionality via "Asynchronous Commit". For instance: https://www.postgresql.org/docs/current/wal-async-commit.html . I need something like this for MySQL.

1 Answer 1

1

Sorry, no, the redo log durability is a global setting. There is no option to change log flushing per transaction.

You could potentially allow log flushing to be less durable, then force flushing occasionally if you need to by running DDL statements. According to the documentation, DDL causes a redo log flush. I don't have proof of this.

What I would recommend instead is to solve the problem in some other way.

  • Upgrade to faster storage, to reduce latency of log flushing
  • Upgrade to battery-backed cache on RAID controller, so you can flush without waiting for storage devices
  • Use a message queue to make writes to the RDBMS asynchronous, so your application can continue working without waiting for commit

Your Answer

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

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