MySQL InnoDB Tablespace Management Quiz

1. Which file contains the undo logs in a default InnoDB configuration?

Ibdata1

Undo_001

Mysql.ibd

Ib_logfile0

2. In MySQL 8.0, where are undo logs stored by default when using separate undo tablespaces?

In the system tablespace (ibdata1)

In dedicated undo tablespace files (undo_001, undo_002, etc.)

In the temporary tablespace

In the binary log files

3. What does the innodb_undo_tablespaces variable control?

Number of separate undo tablespaces created

Maximum size of each undo tablespace

Compression level for undo logs

Retention period for undo records

4. What is the primary purpose of the innodb_file_per_table setting in MySQL?

Store each table in a separate .ibd file instead of the shared tablespace

Increase query performance by distributing tables across multiple disks

Compress all InnoDB tables automatically

Enable concurrent access to the same table

5. Which command drops a general tablespace that contains no tables?

DROP TABLESPACE tablespace_name

DELETE TABLESPACE tablespace_name

REMOVE TABLESPACE tablespace_name

DESTROY TABLESPACE tablespace_name

6. What is stored in the InnoDB data dictionary within the system tablespace?

User account credentials and privileges

Metadata for all InnoDB tables and indexes

Binary log position information

Temporary query results

7. In a file-per-table setup, where is the table structure information stored?

Only in the .ibd file

In the system tablespace and the .ibd file

In the MySQL data dictionary schema

In the binary log

8. What is the purpose of the innodb_temp_data_file_path parameter?

Defines the location and size of the temporary tablespace

Sets the compression method for temporary tables

Controls the retention time for temporary files

Specifies the backup location for temporary data

9. True or False: You can assign a table to a general tablespace at table creation time using CREATE TABLE.

True

False

10. What is the default page size for InnoDB tablespaces in MySQL?

4 KB

8 KB

16 KB

32 KB

11. True or False: InnoDB page size can be changed after the tablespace is created.

True

False

12. Which tablespace type stores temporary tables created during query execution?

System tablespace

Global temporary tablespace

Session temporary tablespace

General tablespace

13. True or False: Multiple tables can share a single general tablespace.

True

False

14. Which command moves an existing table to a general tablespace?

ALTER TABLE table_name TABLESPACE general_ts

MOVE TABLE table_name TO TABLESPACE general_ts

ALTER TABLE table_name ENGINE=InnoDB TABLESPACE general_ts

MIGRATE TABLE table_name TABLESPACE general_ts

15. What is the minimum number of undo tablespaces recommended for production MySQL servers?

1

2

4

8

16. Which parameter specifies the location where InnoDB data and log files are stored?

Innodb_data_home_dir

Innodb_log_group_home_dir

Datadir

Innodb_file_per_table

17. True or False: The system tablespace (ibdata1) can be reduced in size after tables are moved to file-per-table tablespaces.

True

False

18. Which statement creates a general tablespace in MySQL?

CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd'

CREATE GENERAL TABLESPACE ts1 DATAFILE 'ts1.ibd'

CREATE DATAFILE 'ts1.ibd' FOR TABLESPACE

ALTER TABLESPACE ts1 ADD FILE 'ts1.ibd'

Submit

MySQL DBA InnoDB Tablespace Management Quiz

1. Which file contains the undo logs in a default InnoDB configuration?

2.

What first name or nickname would you like us to use?

2. In MySQL 8.0, where are undo logs stored by default when using separate undo tablespaces?

3. What does the innodb_undo_tablespaces variable control?

4. What is the primary purpose of the innodb_file_per_table setting in MySQL?

5. Which command drops a general tablespace that contains no tables?

6. What is stored in the InnoDB data dictionary within the system tablespace?

7. In a file-per-table setup, where is the table structure information stored?

8. What is the purpose of the innodb_temp_data_file_path parameter?

9. True or False: You can assign a table to a general tablespace at table creation time using CREATE TABLE.

10. What is the default page size for InnoDB tablespaces in MySQL?

11. True or False: InnoDB page size can be changed after the tablespace is created.

12. Which tablespace type stores temporary tables created during query execution?

13. True or False: Multiple tables can share a single general tablespace.

14. Which command moves an existing table to a general tablespace?

15. What is the minimum number of undo tablespaces recommended for production MySQL servers?

16. Which parameter specifies the location where InnoDB data and log files are stored?

17. True or False: The system tablespace (ibdata1) can be reduced in size after tables are moved to file-per-table tablespaces.

18. Which statement creates a general tablespace in MySQL?

19. In MySQL, the ____ tablespace contains the InnoDB data dictionary, undo logs, and change buffer.

20. The innodb_undo_log_truncate variable controls whether ____ logs are automatically truncated.