MySQL 8.0 Architecture and Enhancement Webinar: Q & A

MySQL 8.0 Architecture and EnhancementIn this blog, I will provide answers to the Q & A for the MySQL 8.0 Architecture and Enhancement webinar.

First, I want to thank everybody for attending my April 9, 2019, webinar. The recording and slides are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What kind of Encryption levels does MySQL 8.0 provide?

The MySQL data-at-rest encryption feature supports the Advanced Encryption Standard (AES) block-based encryption algorithm and aes_256_cbc encryption algorithm for data at rest is hard coded.

Q: At what frequency does the redo log buffer flush the changes to disk? When is the commit variable set to zero?

Here’s an overview:

  • innodb_flush_log_at_trx_commit variable can be configured to set flush frequency in MySQL 5.7 and 8.0 . Its default setting is 1.
  • innodb_flush_log_at_trx_commit =0  logs are written and flushed to disk once per second. In the event of a crash, you could lose transactions if the logs have not been flushed.
  • innodb_flush_log_at_trx_commit =1 is required for full ACID compliance. MySQL writes and flushes logs to disk at each transaction commit. Default Setting.
  • innodb_flush_log_at_trx_commit =2 logs are written after each transaction commit and flushed to disk once per second. In a crash, you could lose transactions where the logs have not been flushed.

Q: How do you reset persistent variables?

Using the RESET PERSIST command we can remove persisted global system variable settings from the mysqld-auto.cnf.


To reset all persistent variables use following command.

Q: Does pt-config-diff work with these persistent vs. my.cnf variable settings?

No, it will not work. Due to config format differences in these files.

Q: Regarding the UNDO Tablespace, do we have any specific retention to follow?

This is not required because in MySQL 8.0, the innodb_undo_log_truncate variable is enabled by default. It will perform an automatic truncate operation on the UNDO tablespace. When undo tablespaces exceed the threshold value defined by innodb_max_undo_log_size (default value is 1024 MiB) they are marked for truncation.

Truncating the undo tablespace performs the following action:

  1. Performs deactivation of undo tablespace
  2. Truncation of undo tablespace
  3. Reactivation of undo tablespaces

Q: Corrupted data on disk where the secondary indexes don’t match to the primary?

I’ll be happy to respond to this, but I’ll need a little bit more information… feel free to add more detail to the comments section and I’ll see if I can provide some insight.

Thanks for attending this webinar on MySQL 8.0 Architecture and Enhancement Webinar . You can find the slides and a recording here.


Share this post

Leave a Reply