diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-03-22 11:26:38 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-03-22 18:01:29 +0200 |
| commit | 2fb31821deb98d4324b1555ff5ede21feba77f6c (patch) | |
| tree | 8d0ab0e53ed6647d717a45997335eb62c36277ba /mysql-test/suite/encryption/r/innodb_encryption.result | |
| parent | b98d80eb84f9b2e433314fa60c2d3c5b968e580d (diff) | |
| download | mariadb-git-2fb31821deb98d4324b1555ff5ede21feba77f6c.tar.gz | |
MDEV-11984 Avoid accessing SYS_TABLESPACES unnecessarily
The following INFORMATION_SCHEMA views were unnecessarily retrieving
the data from the SYS_TABLESPACES table instead of directly fetching
it from the fil_system cache:
information_schema.innodb_tablespaces_encryption
information_schema.innodb_tablespaces_scrubbing
InnoDB always loads all tablespace metadata into memory at startup
and never evicts it while the tablespace exists.
With this fix, accessing these views will be much faster and use less
memory, and include data about all tablespaces, including undo
tablespaces.
The view information_schema.innodb_sys_tablespaces will still reflect
the contents of the SYS_TABLESPACES table.
Diffstat (limited to 'mysql-test/suite/encryption/r/innodb_encryption.result')
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb_encryption.result | 40 |
1 files changed, 20 insertions, 20 deletions
diff --git a/mysql-test/suite/encryption/r/innodb_encryption.result b/mysql-test/suite/encryption/r/innodb_encryption.result index 72f2632ba26..7b7601a289c 100644 --- a/mysql-test/suite/encryption/r/innodb_encryption.result +++ b/mysql-test/suite/encryption/r/innodb_encryption.result @@ -8,23 +8,23 @@ innodb_encryption_rotation_iops 100 innodb_encryption_threads 4 SET GLOBAL innodb_encrypt_tables = ON; # Wait max 10 min for key encryption threads to encrypt all spaces -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -mysql/innodb_table_stats -mysql/innodb_index_stats innodb_system # Success! # Now turn off encryption and wait for threads to decrypt everything SET GLOBAL innodb_encrypt_tables = off; # Wait max 10 min for key encryption threads to encrypt all spaces -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -mysql/innodb_table_stats -mysql/innodb_index_stats innodb_system -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME # Success! # Shutdown innodb_encryption_threads @@ -33,23 +33,23 @@ SET GLOBAL innodb_encryption_threads=0; # since threads are off tables should remain unencrypted SET GLOBAL innodb_encrypt_tables = on; # Wait 15s to check that nothing gets encrypted -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -mysql/innodb_table_stats -mysql/innodb_index_stats innodb_system -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME # Success! # Startup innodb_encryption_threads SET GLOBAL innodb_encryption_threads=@start_global_value; # Wait max 10 min for key encryption threads to encrypt all spaces -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -mysql/innodb_table_stats -mysql/innodb_index_stats innodb_system # Success! # Restart mysqld --innodb_encrypt_tables=0 --innodb_encryption_threads=0 @@ -60,10 +60,10 @@ innodb_encrypt_tables OFF innodb_encryption_rotate_key_age 15 innodb_encryption_rotation_iops 100 innodb_encryption_threads 0 -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME -mysql/innodb_table_stats -mysql/innodb_index_stats innodb_system -SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats'; NAME |