MySQL Tablespace | System Tablespace | General Tablespace | InnoDB File per Tablespace

System Tablespace:

• System tablespace store doublewrite buffer and the change buffer
• It could also have table and index data in case tables are created in the system tablespace and not using file-per-table or general tablespaces
• Previous version of MySQL have system tablespace contained the InnoDB data dictionary
• From MySQL 8.0, metadata is stored in the MySQL data dictionary
• The system tablespace can have one or more data files
• By default data file named ibdata1 is created for single system tablespace in the data directory.
• The size and number of system tablespace data files can be configured using parameter innodb_data_file_path in the config file at startup option
  
  Note: There are limitations for increasing and decreasing the size of the System Tablespace. Refer https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html

General Table Space Features:

• Store data for multiple tables, similar to system table space
• It is a shared tablespace.
• Server keeps tablespace metadata in memory for life time of tablespace.
• Multiple tables in fewer general tablespaces require less memory for tablespace metadata compare to file per tables space
• Files can be placed in relative to MySQL data directory or outside of the data directory
• Table space option can be used with CREATE TABLE and ALTER TABLE.
• Creating a general tablespace in a sub directory under the data directory is not supported, to avoid conflicts with implicitly created file-per-table tablespaces
• ALTER TABLE ... TABLESPACE syntax does not supports moving a table from a temporary tablespace to a persistent tablespace.
• Sometimes ALTER TABLE operation may create temporary or intermediate table in General Table space
• It is not belong to any particular database
• A DROP DATABASE can drop tables that belong to a general tablespace but it cannot drop the tablespace, even if the DROP DATABASE operation drops all tables that belong to the tablespace.
• Can be dropped by DROP TABLESPACE tablespace_name.
• Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new InnoDB data.
• Space is not released to operating system like file-per-table tablespace

Limitations of General Tablespace:

• General tablespaces do not support temporary tables
• Tables stored in a general tablespace may only be opened in MySQL releases that support general tablespaces
• A generated or existing tablespace cannot be changed to a general tablespace.
• ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.
• Support for placing table partitions in general tablespaces was deprecated in MySQL 5.7.24 and will be removed in a future MySQL version.

What happen during ALTER TABLE ... TABLESPACE for General Tablespace:

• Cause a full table rebuild
• Does not support moving a table from a temporary tablespace to a persistent tablespace.
• DATA DIRECTORY clause is permitted with CREATE TABLE

 Benefits of General Tablespace:

• Memory advantage compare to file per table space as server keeps table space meta data for life time of the table space.
• Data file outside of the MySQL data directory allow performance management of the critical tables to the system.
• Antelope:
  InnoDB file format. It supports the REDUNDANT and COMPACT row formats, but not the newer DYNAMIC and COMPRESSED row formats available in the Barracuda file format.
• Barracuda:
  InnoDB file format that supports the COMPRESSED row format that enables InnoDB table compression, and the DYNAMIC row format that improves the storage layout for long variable-length columns.

Ref. https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

InnoDB file per table space:

• More flexible alternative, each InnoDB table is stored in its own tablespace data file (.ibd file).
• Controlled by the innodb_file_per_table parameter in configuration file - my.cnf, innodb_file_per_table=1 OR SET GLOBAL innodb_file_per_table=1;
• It is enabled by default
• With innodb_file_per_table enabled, you can store InnoDB tables in a tbl_name.ibd file, in contrast to MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.
• InnoDB always needs the system tablespace because, it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

Benefits of InnoDB file per tablespace:

• Disk space can be reclaimed,  after truncating or dropping a table stored in a file-per-table tablespace.
• Has advantage over SYSTEM tablespace in which  Data files (ibdata files) create free space internally while dropping table, but does not release space on disk.
• ALTER TABLE operation for a table in a shared tablespace require additional space for data and indexes, while copying data and does not release space after completion of operation.
• TRUNCATE TABLE operation is faster for file-per-table tablespaces compared to SYSTEM and General table space.
• You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes.
• OPTIMIZE TABLE can be used to compact or recreate a file-per-table tablespace, which could release more disk space if actual data is portion of the file size. OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space required to store actual data. After optimization completion, InnoDB removes the old .ibd file and replaces it with the new one.
• Individual InnoDB tables can be move, rather than entire databases.
• Copy of individual InnoDB tables from one MySQL instance to another using transportable tablespace is possible
• It uses the Barracuda file format, which enables features such as compressed and dynamic row formats.
• You can enable more efficient storage for tables with large BLOB or TEXT columns using the dynamic row format, in which BLOB and large text is sotred out side the file. Dynamic Row format - https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dynamic_row_format
• May improve recovery chance, save time, when server can not be re started, and backup, binary logs are unavailable.
• Using MEB (MySQL Enterprise Backup), you can backup and restore individual table without interrupting the use of other InnoDB tables.
• File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables.
• Table size can be monitored at a file system level without accessing MySQL.
• Possible performance improvements when using file-per-table tablespaces in conjunction with innodb_flush_method = O_DIRECT. InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files.
• Each table has its own tablespace, which provides room for growth, in contrast to System Tablespace where size is limited by InnoDB tablespace size limits.Disadvantage file per tablespace:
  
• Each table may have unused space, which can only be utilized by rows of the same table. This could lead to wasted space if not properly managed.
• fsync operations must run on each open table rather than on a single file. Because there is a separate fsync operation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations.
• mysqld must keep one open file handle per table, which may impact performance if you have numerous tables in file-per-table tablespaces.
• The buffer pool is scanned when dropping a file-per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected.
  
  Migration to innodb-file per table:
• When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data,but is not reclaimed for use by the operating system.
• When moving large InnoDB tables out of the system tablespace, where disk space is limited, you may prefer to enable innodb_file_per_table and recreate the entire instance using the mysqldump command. As mentioned above, tables added to the system tablespace using CREATE TABLE ... TABLESPACE or ALTER TABLE ... TABLESPACE syntax are not affected by the innodb_file_per_table setting. These tables must be moved individually.

Ref. https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-tablespaces.html