InnoDB / XtraDB Background Encryption Threads
InnoDB and XtraDB handle the encryption and decryption process with background threads. The innodb_encryption_threads
system variable control the number of threads that the storage engine uses for encryption-related background operations, including encrypting and decrypting pages after key rotations or configuration changes, and scrubbing data to permanently delete it.
Contents
Background Operations
InnoDB and XtraDB perform the following encryption and decryption operations using background encryption threads:
- When rotating encryption keys, background encryption threads re-encrypt pages from the old key to the new key.
- When changing the
innodb_encrypt_tables
system variable toFORCE
, InnoDB encrypts with background threads the system tablespace as well as all unencrypted file-per-table tablespaces that have theENCRYPTED
table option set toDEFAULT
. - When changing the
innodb_encrypt_tables
system variable toOFF
, InnoDB decrypts with background threads the system tablespace as well as all encrypted file-per-table tablespacs that have theENCRYPTED
table option set toDEFAULT
.
Whenever you change the value on the innodb_encrypt_tables
system variable, InnoDB implements the change using background threads. Because of this, you must have a non-zero value set for the innodb_encryption_threads
system variable, ensuring that InnoDB has the threads allocated to perform the necessary encryption or decryption operations. For more information, see disabling key rotations.
Non-background Operations
InnoDB and XtraDB perform the following encryption and decryption operations without using background encryption threads:
- When using file-per-table tablespaces and using
ALTER TABLE
to manually set theENCRYPTED
table option toYES
, InnoDB does not use background threads to encrypt the tablespaces. - Similarly, when using file-per-table tablespaces and using
ALTER TABLE
to manually set theENCRYPTED
table option toNO
, InnoDB does not use background threads to decrypt the tablespaces.
In these cases, InnoDB performs the encryption or decryption operation using the server thread for the client thread that executes the statement. This means that you can update encryption on file-per-table tablespaces with an ALTER TABLE
statement, even when the innodb_encryption_threads
and/or the innodb_rotate_key_age
system variables are set to 0
.
InnoDB and XtraDB do not permit manual changes to the system tablespace. Whenever you update the configuration value on the innodb_encrypt_tables
system variable, you must also set a non-zero value for the innodb_encryption_threads
system variable, and innodb_system_rotate_key_age
to 1
to ensure that the system tablespace is properly encrypted or decrypted by the background 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
InnoDB records the status of background encryption operations in the Information Schema, using the INNODB_TABLESPACES_ENCRYPTION
table. When InnoDB has background encryption threads working on a tablespace, the output for that tablespace shows a value of 1
for the ROTATING_OR_FLUSHING
column.
For example, to see which tablespaces currently have background encryption threads at work,
SELECT SPACE, NAME FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING = 1;
To see how many tablespaces have background encryption threads currently at work, use the COUNT()
function.
SELECT COUNT(*) AS 'Count' FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING = 1;