InnoDB / XtraDB 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:

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.

Whenever you change the value on the innodb_encrypt_tables system variable, InnoDB's background encryption threads perform the necessary encryption or decryption operations. Because of this, you must have a non-zero value set for the innodb_encryption_threads system variable. InnoDB also considers these operations to be key rotations internally. Because of this, you must have a non-zero value set for the innodb_encryption_rotate_key_age system variable. 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 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.

InnoDB and XtraDB do not permit manual encryption changes to tables in the system tablespace using ALTER TABLE. Encryption of the system tablespace can only be configured by setting the value of the innodb_encrypt_tables system variable. This means that when you want to encrypt or decrypt the system tablespace, you must also set a non-zero value for the innodb_encryption_threads system variable, and you must also set the innodb_system_rotate_key_age system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See MDEV-14398 for more information.

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;

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.