MySQL InnoDB Tablespace Management Quiz

1. 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

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

True

False

Submit

4. 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

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

4 KB

8 KB

16 KB

32 KB

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

True

False

Submit

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

System tablespace

Global temporary tablespace

Session temporary tablespace

General tablespace

9. 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

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

True

False

11. 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

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

1

2

4

8

13. 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

14. 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

15. 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

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

True

False

17. 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'

18. 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

19. 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

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

Ibdata1

Undo_001

Mysql.ibd

Ib_logfile0

MySQL DBA InnoDB Tablespace Management Quiz

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

2.

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

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

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

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

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

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

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

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

9. What is the purpose of the innodb_temp_data_file_path parameter?

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

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

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

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

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

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

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

17. Which statement creates a general tablespace in MySQL?

18. What does the innodb_undo_tablespaces variable control?

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

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