InnoDB / XtraDB Background Encryption Threads

You are viewing an old version of this article. View the current version here.

The innodb_encryption_threads system variable controls the number of threads that the InnoDB storage engine utilizes for its encryption-related background operations, which includes encrypting/decrypting InnoDB pages after key rotations or configuration changes and scrubbing InnoDB data to permanently delete it.

Background Operations

The encryption/decryption operations that are performed by the background encryption threads are:

The latter two points mean that, whenever you change the configured value of innodb_encrypt_tables, then you must ensure that innodb_encryption_threads is set to a non-zero value, so that InnoDB can perform any necessary encryption or decryption operations in the background. innodb_encryption_rotate_key_age must also be set to a non-zero value for the initial encryption/decryption operations to happen in the background. See disabling key rotations for more information about that.

The encryption/decryption operations that are not performed by the background encryption threads are:

  • When a file-per-table tablespace for an InnoDB table is manually encrypted by setting ENCRYPTED=YES table option with an ALTER TABLE statement, then the background encryption threads do not encrypt the table in the background. The table is encrypted by the server thread for the client connection that executed the statement.
  • Likewise, when a file-per-table tablespace for an InnoDB table is manually decrypted by setting ENCRYPTED=NO table option with an ALTER TABLE statement, then the background encryption threads do not decrypt the table in the background. The table is decrypted by the server thread for the client connection that executed the statement.

These two points mean that encryption changes can still be made to InnoDB's file-per-table tablespaces with ALTER TABLE, even when innodb_encryption_threads=0 and/or innodb_encryption_rotate_key_age=0 are set.

Manual encryption changes cannot be made to InnoDB's system tablespace. This means that whenever you change the configured value of innodb_encrypt_tables, you must also set innodb_encryption_threads to a non-zero value and innodb_encryption_rotate_key_age=1 to ensure that the system tablespace is properly encrypted or decrypted by the background encryption threads.

The innodb_encryption_rotation_iops system variable can be used to configure how many I/O operations you want to allow for InnoDB's background encryption operations.

Checking the Status of Background Operations

The status of background encryption operations can be checked by querying the information_schema.INNODB_TABLESPACES_ENCRYPTION table. If the background encryption threads are working on a tablespace, then the output will show ROTATING_OR_FLUSHING=1 for that tablespace.

For example, if you'd like to see which tablespaces the background encryption threads are currently working on, then you could execute:

SELECT SPACE, NAME
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING=1;

Or if you'd like to see how many tablespaces the background encryption threads are currently working on, then you could execute:

<<sql>> SELECT COUNT(*) AS 'Count' FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING=1; <

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.