InnoDB Background Encryption Threads

InnoDB and XtraDB perform some encryption and decryption operations with background encryption threads. The innodb_encryption_threads system variable controls 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.

Background Operations

InnoDB and XtraDB perform the following encryption and decryption operations using background encryption threads:

• When rotating encryption keys, InnoDB's background encryption threads re-encrypt pages that use key versions older than innodb_encryption_rotate_key_age to the new key version.
• When changing the innodb_encrypt_tables system variable to FORCE, InnoDB's background encryption threads encrypt the system tablespace and any file-per-table tablespaces that have the ENCRYPTED table option set to DEFAULT.
• When changing the innodb_encrypt_tables system variable to OFF, InnoDB's background encryption threads decrypt the system tablespace and any file-per-table tablespacs that have the ENCRYPTED table option set to DEFAULT.

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

Non-background Operations

InnoDB and XtraDB perform the following encryption and decryption operations without using background encryption threads:

• When a file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to YES, InnoDB does not use background threads to encrypt the tablespaces.
• Similarly, when using file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to NO, 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 connection 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.

Checking the Status of Background Operations

InnoDB records the status of background encryption operations in the INNODB_TABLESPACES_ENCRYPTION table in the information_schema database.

For example, to see which InnoDB tablespaces are currently being decrypted or encrypted on by background encryption, you can check which InnoDB tablespaces have the ROTATING_OR_FLUSHING column set to 1:

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

And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, you can call the COUNT() aggregate function.

SELECT COUNT(*) AS 'encrypting' 
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;

And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, while comparing that to the total number of InnoDB tablespaces and the total number of encrypted InnoDB tablespaces, you can join the table with the INNODB_SYS_TABLESPACES table in the information_schema database:

/* information_schema.INNODB_TABLESPACES_ENCRYPTION does not always have rows for all tablespaces,
  so let's join it with information_schema.INNODB_SYS_TABLESPACES */
WITH tablespace_ids AS (
   SELECT SPACE
   FROM information_schema.INNODB_SYS_TABLESPACES ist
   UNION
   /* information_schema.INNODB_SYS_TABLESPACES doesn't have a row for the system tablespace (MDEV-20802) */
   SELECT 0 AS SPACE
)
SELECT NOW() as 'time', 
   'tablespaces', COUNT(*) AS 'tablespaces', 
   'encrypted', SUM(IF(ite.ENCRYPTION_SCHEME IS NOT NULL, ite.ENCRYPTION_SCHEME, 0)) AS 'encrypted', 
   'encrypting', SUM(IF(ite.ROTATING_OR_FLUSHING IS NOT NULL, ite.ROTATING_OR_FLUSHING, 0)) AS 'encrypting'
FROM tablespace_ids
LEFT JOIN information_schema.INNODB_TABLESPACES_ENCRYPTION ite
   ON tablespace_ids.SPACE = ite.SPACE