InnoDB / XtraDB Background Encryption Threads
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:
- When a new version of an encryption key is rotated, then the background encryption threads will re-encrypt pages that were previously encrypted with the older version of that encryption key.
- When the configured value of
innodb_encrypt_tables
changes toON
orFORCE
, then the background encryption threads will encrypt InnoDB's system tablespace and currently unencrypted file-per-table tablespaces for InnoDB tables that haveENCRYPTED=DEFAULT
set as a table option. - When the configured value of
innodb_encrypt_tables
changes toOFF
, then the background encryption threads will decrypt InnoDB's system tablespace and currently encrypted file-per-table tablespaces for InnoDB tables that haveENCRYPTED=DEFAULT
set as a table option.
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 anALTER 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 anALTER 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;
<