[go: up one dir, main page]
Scribd
Upload
4 views3 pages

MySQL Tablespaces Complete POC

This document provides a comprehensive overview of MySQL tablespaces, including their types and functions, such as System, Undo, General, Temporary, Redo Log, File-Per-Table, and Blob Tablespaces. It includes real-time scenarios demonstrating the use of Undo and Temporary Tablespaces, as well as commands for checking and managing these spaces. Additionally, the document features interview questions related to tablespaces for further understanding.

Uploaded by

ljana6601
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views3 pages

MySQL Tablespaces Complete POC

This document provides a comprehensive overview of MySQL tablespaces, including their types and functions, such as System, Undo, General, Temporary, Redo Log, File-Per-Table, and Blob Tablespaces. It includes real-time scenarios demonstrating the use of Undo and Temporary Tablespaces, as well as commands for checking and managing these spaces. Additionally, the document features interview questions related to tablespaces for further understanding.

Uploaded by

ljana6601
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

MySQL Tablespaces with Undo Logs and Rollback - POC Documentation

MySQL Tablespaces with Undo Logs and Rollback - Full POC Documentation

Version: MySQL 5.7 / 8.x

DBA Level: Beginner to Advanced

Prepared by: Alien Thoughts

1. Introduction

Tablespaces are physical storage units in MySQL that organize how data is stored on disk.

2. Types of Tablespaces

- System Tablespace: Stores metadata, doublewrite buffer, and internal data.

- Undo Tablespace: Stores undo logs for rollback operations.

- General Tablespace: Custom user tables storage, introduced in MySQL 5.7.

- Temporary Tablespace: Stores temporary tables and intermediate query results.

- Redo Log Tablespace: Stores redo logs for crash recovery.

- File-Per-Table Tablespace: Stores each table data and indexes separately.

- Blob Tablespace: Stores large binary objects separately (MySQL 8.x).

3. Real-Time Scenario - Undo Tablespace

Step 1: Start Transaction.

Step 2: Update Employee Salary.

Step 3: Old data is stored in Undo Tablespace.

Step 4: If rollback is issued, old data will be restored.

4. How to Check Tablespaces


SQL Query:

SELECT tablespace_name, file_name, file_size

FROM information_schema.files;

5. How to Shrink Undo Tablespace

SQL Command:

SET GLOBAL innodb_undo_log_truncate = ON;

6. Real-Time Scenario - Temporary Tablespace

Step 1: Execute large JOIN query.

Step 2: Temporary data is stored in Temporary Tablespace.

Step 3: After query completion, the space is automatically cleaned.

7. Real-Time Scenario - File-Per-Table Tablespace

Step 1: Create Table with ROW_FORMAT=Dynamic.

Step 2: MySQL creates a separate .ibd file.

Step 3: Dropping the table will delete the .ibd file.

8. Real-Time Scenario - Redo Log Tablespace

Step 1: Insert multiple records.

Step 2: MySQL writes redo logs for crash recovery.

9. Interview Questions

1. What is Undo Tablespace?

It stores old data for rollback operations.

2. How to enable File-Per-Table Tablespace?

Set innodb_file_per_table=ON in my.cnf.


3. How many redo log files are created by default?

Two files.

4. What is the role of Temporary Tablespace?

Stores intermediate query results.

You might also like