Tablespaces in MySQL 🐬

Hey folks, everything good? I hope so.

Today I want to cover a simple, practical topic that, just like in Oracle, also exists in MySQL: TABLESPACES.

⚠️ CONTAINS TEXT IMPROVED BY AI, AND THAT IS FINE (IF YOU KNOW HOW TO USE IT 🤭)⚠️

Is this new to you? Or did you already know that MySQL also has TABLESPACES?

Well, new to you or not, it is worth a read to learn or refresh the details on the subject.

In MySQL, there are three main types of tablespaces:

1. System Tablespace: Stores system metadata and data.
2. File-per-Table Tablespace: Each table is stored in its own file .ibd (which I find odd as an Oracle DBA, haha).
3. General Tablespace: Lets multiple tables share the same data file .ibd.

Why use tablespaces in MySQL?

Using tablespaces offers several advantages for the disorganized DBA 🤭:

• Flexibility: You can choose where to store the data (in a specific directory, for example).
• Performance: Tablespaces let you optimize storage for specific scenarios, such as data compression.
• Simplified management: Makes it easier to manage large volumes of data, especially in environments with many tables.
• Compression: Tablespaces support compressed tables, reducing disk space usage.

Types of tablespaces in MySQL

1. System Tablespace

The system tablespace is the heart of MySQL. It stores system metadata, such as the data dictionary and the undo logs. By default, it is stored in the file ibdata1. Although it is essential for MySQL to work, it is not recommended for storing user data, since it can grow indefinitely (yes, some people will try anything).

2. File-per-Table Tablespace

In file-per-table mode, each table is stored in its own file .ibd. This gives you more flexibility, since you can move, copy or delete tables individually. It also makes data recovery easier in case of failures.

Example of creating a table in file-per-table mode:

CREATE TABLE tabela_braba (
    cfp INT PRIMARY KEY,
    dba VARCHAR(100)
) ENGINE=InnoDB;

3. General Tablespace

General tablespaces let multiple tables share the same file .ibd. They are ideal for scenarios where you want to centralize the storage of several tables or use data compression.

Support for the following row formats:

• REDUNDANT
• COMPACT
• DYNAMIC
• COMPRESSED

Example of creating a general tablespace:

CREATE TABLESPACE tablespace_braba
ADD DATAFILE '/u01/para/df_brabo_01.ibd'
ENGINE=InnoDB;

Or you can also just run:

CREATE TABLESPACE tablespace_braba Engine=InnoDB;

If the ADD DATAFILE clause is not specified when creating a tablespace, a datafile with a pretty wild name gets created instead (something along these lines: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee).

And how do you add a table to the new tablespace?

You can simply create the table and assign it to its tablespace:

CREATE TABLE tabela_braba (
    cfp INT PRIMARY KEY,
    dba VARCHAR(100)
) TABLESPACE tablespace_braba;

But you can also attach an existing table to a tablespace:

ALTER TABLE tabela_braba TABLESPACE tablespace_braba;

We can validate, check and understand the table-to-tablespace relationship with this query:

SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,
       INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'tablespace_braba';

+-----------------+--------------------+
| space_name      | table_name         |
+-----------------+--------------------+
| tablespace_braba| teste/tabela_braba |
+-----------------+--------------------+

Also as in Oracle, you can add datafiles to the tablespaces you created:

ALTER TABLESPACE tablespace_braba ADD DATAFILE '/u01/oradata/df_brabo_02.ibd' INITIAL_SIZE 48M ENGINE InnoDB;

In the case above we are already pre-allocating 48M for the datafile, but we can also add a datafile without allocating space up front, like this:

ALTER TABLESPACE tablespace_braba ADD DATAFILE '/u01/oradata/df_brabo_03.ibd' ENGINE InnoDB;

You can also MOVE tables between different types of tablespaces:

ALTER TABLE tabela_braba TABLESPACE [=] tablespace_name;
ALTER TABLE tabela_braba TABLESPACE [=] innodb_system;
ALTER TABLE tabela_braba TABLESPACE [=] innodb_file_per_table;

Can you rename it? Yes, you can!

ALTER TABLESPACE tablespace_braba RENAME TO tablespace_braba_01;

Jokes aside: during a tablespace rename, every table related to it takes a metadata lock 🚨.

It is also not possible to RENAME while the tables are under: LOCK TABLES or FLUSH TABLES WITH READ LOCK.

And finally, dropping a tablespace in MySQL:

DROP TABLESPACE tablespace_braba;

Advantages of general tablespaces

• Centralized storage: Multiple tables can share the same file, simplifying management.
• Data compression: Supports tables with row format COMPRESSED, reducing disk space usage.
• Efficiency: Ideal for scenarios where several tables have similar access patterns.

Permitted page size combinations for compressed tables

InnoDB Page Size	FILE_BLOCK_SIZE Value	KEY_BLOCK_SIZE Value
64KB	64K (65536)	Compression is not supported
32KB	32K (32768)	Compression is not supported
16KB	16K (16384)	None. If innodb_page_size is equal to FILE_BLOCK_SIZE, the tablespace cannot contain a compressed table.
16KB	8K (8192)	8
16KB	4K (4096)	4
16KB	2K (2048)	2
16KB	1K (1024)	1
8KB	8K (8192)	None. If innodb_page_size is equal to FILE_BLOCK_SIZE, the tablespace cannot contain a compressed table.
8KB	4K (4096)	4
8KB	2K (2048)	2
8KB	1K (1024)	1
4KB	4K (4096)	None. If innodb_page_size is equal to FILE_BLOCK_SIZE, the tablespace cannot contain a compressed table.
4KB	2K (2048)	2
4KB	1K (1024)	1

When to use tablespaces in the dolphin?

• Compression scenarios: If you need to save disk space, compressed tablespaces are a great option.
• Centralized storage: For environments with many related tables, general tablespaces can simplify management.
• Location control: If you need to store data in a specific directory (for example, on a high-performance disk [SSD, NVMe, Optane and so on]), tablespaces give you that control.

REFERENCE GUIDE:

-- ============================================================================
-- COMANDOS CREATE TABLESPACE MAIS COMPLETOS POR ENGINE
-- ============================================================================

-- ============================================================================
-- 1. InnoDB ENGINE - TABLESPACE REGULAR
-- ============================================================================
CREATE TABLESPACE ts_innodb_complete
ADD DATAFILE '/var/lib/mysql/ts_innodb_complete.ibd'
AUTOEXTEND_SIZE = 64M
FILE_BLOCK_SIZE = 16384
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- ============================================================================
-- 2. InnoDB ENGINE - UNDO TABLESPACE
-- ============================================================================
CREATE UNDO TABLESPACE undo_ts_innodb_complete
ADD DATAFILE '/var/lib/mysql/undo_ts_innodb_complete.ibu'
AUTOEXTEND_SIZE = 128M
FILE_BLOCK_SIZE = 16384
ENCRYPTION = 'N'
ENGINE = InnoDB;

-- ============================================================================
-- 3. NDB ENGINE - TABLESPACE COMPLETO
-- ============================================================================
-- Primeiro, criar o LOGFILE GROUP (prerequisito para NDB tablespace)
CREATE LOGFILE GROUP lg_ndb_complete
ADD UNDOFILE 'undo_lg_ndb_complete.dat'
INITIAL_SIZE = 128M
UNDO_BUFFER_SIZE = 64M
ENGINE = NDB;

-- Agora criar o TABLESPACE NDB
CREATE TABLESPACE ts_ndb_complete
ADD DATAFILE 'ts_ndb_complete.dat'
USE LOGFILE GROUP lg_ndb_complete
AUTOEXTEND_SIZE = 32M
EXTENT_SIZE = 1M
INITIAL_SIZE = 256M
MAX_SIZE = 2G
NODEGROUP = 0
WAIT
COMMENT = 'Tablespace NDB completo para cluster'
ENGINE = NDB;

-- ============================================================================
-- 4. NDB ENGINE - UNDO TABLESPACE
-- ============================================================================
CREATE UNDO TABLESPACE undo_ts_ndb_complete
ADD DATAFILE 'undo_ts_ndb_complete.dat'
USE LOGFILE GROUP lg_ndb_complete
AUTOEXTEND_SIZE = 16M
EXTENT_SIZE = 512K
INITIAL_SIZE = 128M
MAX_SIZE = 1G
NODEGROUP = 1
WAIT
COMMENT = 'Undo tablespace NDB para transações'
ENGINE = NDB;

-- ============================================================================
-- EXPLICAÇÃO DOS PARÂMETROS
-- ============================================================================

/*
PARÂMETROS COMUNS (InnoDB e NDB):
- ADD DATAFILE: Especifica o arquivo de dados
- AUTOEXTEND_SIZE: Tamanho do incremento automático
- ENGINE: Engine de armazenamento

PARÂMETROS EXCLUSIVOS InnoDB:
- FILE_BLOCK_SIZE: Tamanho do bloco (512, 1024, 2048, 4096, 8192, 16384, 32768, 65536)
- ENCRYPTION: Criptografia ('Y' ou 'N')

PARÂMETROS EXCLUSIVOS NDB:
- USE LOGFILE GROUP: Grupo de log files (obrigatório)
- EXTENT_SIZE: Tamanho da extensão (32K-2G)
- INITIAL_SIZE: Tamanho inicial
- MAX_SIZE: Tamanho máximo
- NODEGROUP: ID do grupo de nós
- WAIT: Aguarda conclusão da operação
- COMMENT: Comentário descritivo

VALORES RECOMENDADOS:
- FILE_BLOCK_SIZE InnoDB: 16384 (padrão)
- EXTENT_SIZE NDB: 1M (padrão)
- AUTOEXTEND_SIZE: 64M para InnoDB, 32M para NDB
*/

-- ============================================================================
-- MYSQL TABLESPACES COM MÚLTIPLOS DATAFILES
-- ============================================================================

/*
- InnoDB: NÃO suporta múltiplos datafiles no CREATE TABLESPACE
- NDB: SIM, suporta múltiplos datafiles nativamente
*/

-- ============================================================================
-- 1. InnoDB - LIMITAÇÃO: APENAS 1 DATAFILE NO CREATE
-- ============================================================================

-- ❌ ISTO NÃO FUNCIONA NO InnoDB:
-- CREATE TABLESPACE ts_innodb
-- ADD DATAFILE 'file1.ibd', 'file2.ibd', 'file3.ibd'  -- ERRO!
-- ENGINE = InnoDB;

-- ✅ InnoDB: Apenas 1 datafile no CREATE
CREATE TABLESPACE ts_innodb_inicial
ADD DATAFILE '/var/lib/mysql/ts_innodb_file1.ibd'
AUTOEXTEND_SIZE = 64M
FILE_BLOCK_SIZE = 16384
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- Para adicionar mais datafiles no InnoDB, você deve usar ALTER depois:
-- NOTA: ALTER TABLESPACE ADD DATAFILE não é suportado no InnoDB!
-- InnoDB usa auto-extend ao invés de múltiplos arquivos

-- ============================================================================
-- 2. NDB - SUPORTE COMPLETO A MÚLTIPLOS DATAFILES
-- ============================================================================

-- Primeiro criar o LOGFILE GROUP
CREATE LOGFILE GROUP lg_multiple
ADD UNDOFILE 'undo_multiple.dat'
INITIAL_SIZE = 128M
UNDO_BUFFER_SIZE = 64M
ENGINE = NDB;

-- ✅ NDB: Múltiplos datafiles são suportados via ALTER
CREATE TABLESPACE ts_ndb_multiple
ADD DATAFILE 'ndb_file1.dat'
USE LOGFILE GROUP lg_multiple
INITIAL_SIZE = 256M
EXTENT_SIZE = 1M
MAX_SIZE = 2G
ENGINE = NDB;

-- Adicionar mais datafiles ao tablespace NDB existente
ALTER TABLESPACE ts_ndb_multiple
ADD DATAFILE 'ndb_file2.dat'
INITIAL_SIZE = 256M
ENGINE = NDB;

ALTER TABLESPACE ts_ndb_multiple
ADD DATAFILE 'ndb_file3.dat'
INITIAL_SIZE = 256M
ENGINE = NDB;

ALTER TABLESPACE ts_ndb_multiple
ADD DATAFILE 'ndb_file4.dat'
INITIAL_SIZE = 256M
ENGINE = NDB;

-- ============================================================================
-- 3. VERIFICAR DATAFILES DE UM TABLESPACE
-- ============================================================================

-- Ver informações dos tablespaces e seus arquivos
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    FILE_TYPE,
    TOTAL_EXTENTS,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    ENGINE
FROM INFORMATION_SCHEMA.FILES 
WHERE TABLESPACE_NAME = 'ts_ndb_multiple'
ORDER BY FILE_NAME;

-- Para InnoDB, ver general tablespaces
SELECT 
    SPACE,
    NAME,
    FLAG,
    ROW_FORMAT,
    PAGE_SIZE,
    SPACE_TYPE
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME = 'ts_innodb_inicial';

-- ============================================================================
-- 4. EXEMPLO PRÁTICO: DISTRIBUÇÃO DE CARGA EM NDB
-- ============================================================================

-- Cenário: Tablespace com 4 datafiles em diferentes discos
CREATE LOGFILE GROUP lg_distributed
ADD UNDOFILE '/disk1/mysql/undo_distributed.dat'
INITIAL_SIZE = 256M
UNDO_BUFFER_SIZE = 128M
ENGINE = NDB;

CREATE TABLESPACE ts_distributed
ADD DATAFILE '/disk2/mysql/data_file1.dat'
USE LOGFILE GROUP lg_distributed
INITIAL_SIZE = 1G
EXTENT_SIZE = 1M
MAX_SIZE = 10G
NODEGROUP = 0
ENGINE = NDB;

-- Adicionar datafiles em diferentes discos para performance
ALTER TABLESPACE ts_distributed
ADD DATAFILE '/disk3/mysql/data_file2.dat'
INITIAL_SIZE = 1G
ENGINE = NDB;

ALTER TABLESPACE ts_distributed
ADD DATAFILE '/disk4/mysql/data_file3.dat'
INITIAL_SIZE = 1G
ENGINE = NDB;

ALTER TABLESPACE ts_distributed
ADD DATAFILE '/disk5/mysql/data_file4.dat'
INITIAL_SIZE = 1G
ENGINE = NDB;

-- ============================================================================
-- 5. ALTERNATIVAS PARA InnoDB
-- ============================================================================

/*
Como InnoDB não suporta múltiplos datafiles por tablespace,
as alternativas são:

1. USAR AUTO-EXTEND (recomendado)
   - O arquivo cresce automaticamente conforme necessário
   - Mais simples de gerenciar

2. MÚLTIPLOS TABLESPACES
   - Criar vários tablespaces com 1 datafile cada
   - Distribuir tabelas entre eles

3. PARTICIONAMENTO
   - Particionar tabelas grandes
   - Cada partição pode estar em tablespace diferente
*/

-- Exemplo: Múltiplos tablespaces InnoDB
CREATE TABLESPACE ts_innodb_part1
ADD DATAFILE '/disk1/mysql/ts_part1.ibd'
AUTOEXTEND_SIZE = 64M
ENGINE = InnoDB;

CREATE TABLESPACE ts_innodb_part2
ADD DATAFILE '/disk2/mysql/ts_part2.ibd'
AUTOEXTEND_SIZE = 64M
ENGINE = InnoDB;

CREATE TABLESPACE ts_innodb_part3
ADD DATAFILE '/disk3/mysql/ts_part3.ibd'
AUTOEXTEND_SIZE = 64M
ENGINE = InnoDB;

-- Tabela particionada usando múltiplos tablespaces
CREATE TABLE vendas_particionada (
    id INT AUTO_INCREMENT,
    data_venda DATE,
    valor DECIMAL(10,2),
    PRIMARY KEY (id, data_venda)
)
PARTITION BY RANGE (YEAR(data_venda)) (
    PARTITION p2023 VALUES LESS THAN (2024) TABLESPACE ts_innodb_part1,
    PARTITION p2024 VALUES LESS THAN (2025) TABLESPACE ts_innodb_part2,
    PARTITION p2025 VALUES LESS THAN (2026) TABLESPACE ts_innodb_part3
);

-- ============================================================================
-- RESUMO FINAL
-- ============================================================================

/*
CAPACIDADES POR ENGINE:

InnoDB:
✗ Não suporta múltiplos datafiles no CREATE TABLESPACE
✗ Não suporta ALTER TABLESPACE ADD DATAFILE
✓ Suporta auto-extend (recomendado)
✓ Alternativa: múltiplos tablespaces + particionamento

NDB:
✓ Suporta múltiplos datafiles via ALTER TABLESPACE
✓ Ideal para distribuição de carga em cluster
✓ Permite adicionar datafiles dinamicamente
✓ Melhor controle sobre localização dos arquivos

RECOMENDAÇÃO:
- InnoDB: Use auto-extend ou particionamento
- NDB: Use múltiplos datafiles conforme necessário
*/

-- ============================================================================
-- COMANDOS ALTER TABLESPACE MAIS COMPLETOS POR ENGINE
-- ============================================================================

-- ============================================================================
-- 1. NDB ENGINE - OPERAÇÕES COM DATAFILES
-- ============================================================================

-- Adicionar datafile ao tablespace NDB
ALTER TABLESPACE ts_ndb_complete
ADD DATAFILE 'ts_ndb_additional_01.dat'
INITIAL_SIZE = 512M
WAIT
ENGINE = NDB;

-- Adicionar múltiplos datafiles sequencialmente
ALTER TABLESPACE ts_ndb_complete
ADD DATAFILE 'ts_ndb_additional_02.dat'
INITIAL_SIZE = 1G
WAIT
ENGINE = NDB;

ALTER TABLESPACE ts_ndb_complete
ADD DATAFILE 'ts_ndb_additional_03.dat'
INITIAL_SIZE = 1G
WAIT
ENGINE = NDB;

-- Remover datafile do tablespace NDB
ALTER TABLESPACE ts_ndb_complete
DROP DATAFILE 'ts_ndb_additional_01.dat'
WAIT
ENGINE = NDB;

-- Renomear tablespace NDB
ALTER TABLESPACE ts_ndb_complete
RENAME TO ts_ndb_renamed
ENGINE = NDB;

-- ============================================================================
-- 2. NDB ENGINE - UNDO TABLESPACE OPERATIONS
-- ============================================================================

-- Adicionar datafile ao UNDO tablespace NDB
ALTER UNDO TABLESPACE undo_ts_ndb_complete
ADD DATAFILE 'undo_ts_ndb_additional.dat'
INITIAL_SIZE = 256M
WAIT
ENGINE = NDB;

-- Remover datafile do UNDO tablespace NDB
ALTER UNDO TABLESPACE undo_ts_ndb_complete
DROP DATAFILE 'undo_ts_ndb_additional.dat'
WAIT
ENGINE = NDB;

-- Renomear UNDO tablespace NDB
ALTER UNDO TABLESPACE undo_ts_ndb_complete
RENAME TO undo_ts_ndb_renamed
ENGINE = NDB;

-- ============================================================================
-- 3. InnoDB ENGINE - ADICIONAR DATAFILES E CONFIGURAÇÕES
-- ============================================================================

-- ✅ FUNCIONA: Adicionar datafile ao tablespace InnoDB
-- (Suportado em versões específicas do MySQL/MariaDB)
ALTER TABLESPACE ts_innodb_complete
ADD DATAFILE '/u01/oradata/df_innodb_02.ibd'
INITIAL_SIZE = 48M
ENGINE = InnoDB;

-- Adicionar múltiplos datafiles ao InnoDB
ALTER TABLESPACE ts_innodb_complete
ADD DATAFILE '/u01/oradata/df_innodb_03.ibd'
INITIAL_SIZE = 64M
ENGINE = InnoDB;

ALTER TABLESPACE ts_innodb_complete
ADD DATAFILE '/u01/oradata/df_innodb_04.ibd'
INITIAL_SIZE = 128M
ENGINE = InnoDB;

-- Alterar AUTOEXTEND_SIZE do tablespace InnoDB
ALTER TABLESPACE ts_innodb_complete
AUTOEXTEND_SIZE = 128M
ENGINE = InnoDB;

-- Ativar criptografia no tablespace InnoDB
ALTER TABLESPACE ts_innodb_complete
ENCRYPTION = 'Y'
AUTOEXTEND_SIZE = 64M
ENGINE = InnoDB;

-- Desativar criptografia no tablespace InnoDB
ALTER TABLESPACE ts_innodb_complete
ENCRYPTION = 'N'
ENGINE = InnoDB;

-- Renomear tablespace InnoDB
ALTER TABLESPACE ts_innodb_complete
RENAME TO ts_innodb_renamed
AUTOEXTEND_SIZE = 64M
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- ============================================================================
-- 4. InnoDB ENGINE - UNDO TABLESPACE STATUS
-- ============================================================================

-- Definir UNDO tablespace como ATIVO
ALTER UNDO TABLESPACE undo_ts_innodb_complete
SET ACTIVE
AUTOEXTEND_SIZE = 256M
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- Definir UNDO tablespace como INATIVO
ALTER UNDO TABLESPACE undo_ts_innodb_complete
SET INACTIVE
AUTOEXTEND_SIZE = 128M
ENCRYPTION = 'N'
ENGINE = InnoDB;

-- Renomear UNDO tablespace InnoDB com todas as opções
ALTER UNDO TABLESPACE undo_ts_innodb_complete
RENAME TO undo_ts_innodb_renamed
SET ACTIVE
AUTOEXTEND_SIZE = 512M
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- ============================================================================
-- 5. OPERAÇÕES COMBINADAS COMPLEXAS
-- ============================================================================

-- NDB: Adicionar datafile com todas opções disponíveis
ALTER TABLESPACE ts_ndb_production
ADD DATAFILE '/data/mysql/ndb/ts_prod_extra_01.dat'
INITIAL_SIZE = 2G
WAIT
RENAME TO ts_ndb_production_v2
ENGINE = NDB;

-- InnoDB: Modificar todas configurações simultaneamente
ALTER TABLESPACE ts_innodb_production
RENAME TO ts_innodb_production_v2
AUTOEXTEND_SIZE = 256M
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- InnoDB UNDO: Configuração completa de produção
ALTER UNDO TABLESPACE undo_production
RENAME TO undo_production_primary
SET ACTIVE
AUTOEXTEND_SIZE = 1G
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- ============================================================================
-- 6. CENÁRIOS PRÁTICOS DE MANUTENÇÃO
-- ============================================================================

-- Cenário 1: Expansão de capacidade NDB
-- Adicionar múltiplos datafiles para aumentar capacidade
ALTER TABLESPACE ts_app_data
ADD DATAFILE '/storage/fast/mysql/app_data_ssd_01.dat'
INITIAL_SIZE = 5G
WAIT
ENGINE = NDB;

ALTER TABLESPACE ts_app_data
ADD DATAFILE '/storage/bulk/mysql/app_data_bulk_01.dat'
INITIAL_SIZE = 10G
WAIT
ENGINE = NDB;

-- Cenário 2: Migração e otimização InnoDB
-- Renomear e otimizar configurações
ALTER TABLESPACE ts_legacy_data
RENAME TO ts_optimized_data
AUTOEXTEND_SIZE = 512M
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- Cenário 3: Gerenciamento de UNDO tablespaces
-- Alternar entre UNDO tablespaces para manutenção
ALTER UNDO TABLESPACE undo_primary
SET INACTIVE
ENGINE = InnoDB;

ALTER UNDO TABLESPACE undo_secondary
SET ACTIVE
AUTOEXTEND_SIZE = 2G
ENCRYPTION = 'Y'
ENGINE = InnoDB;

-- Cenário 4: Rebalanceamento de storage NDB
-- Remover datafiles de storage lento
ALTER TABLESPACE ts_high_performance
DROP DATAFILE 'slow_storage_file.dat'
WAIT
ENGINE = NDB;

-- Adicionar datafiles em storage rápido
ALTER TABLESPACE ts_high_performance
ADD DATAFILE '/nvme/mysql/high_perf_01.dat'
INITIAL_SIZE = 8G
WAIT
ENGINE = NDB;

-- ============================================================================
-- 7. VERIFICAÇÃO E MONITORAMENTO
-- ============================================================================

-- Verificar status dos tablespaces após alterações
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    FILE_TYPE,
    TOTAL_EXTENTS,
    FREE_EXTENTS,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    ENGINE
FROM INFORMATION_SCHEMA.FILES 
WHERE ENGINE IN ('NDB', 'InnoDB')
ORDER BY TABLESPACE_NAME, FILE_NAME;

-- Verificar UNDO tablespaces InnoDB
SELECT 
    SPACE,
    NAME,
    STATE,
    SPACE_TYPE
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo'
ORDER BY NAME;

-- ============================================================================
-- EXPLICAÇÃO DETALHADA DOS PARÂMETROS
-- ============================================================================

/*
PARÂMETROS POR ENGINE:

NDB EXCLUSIVO:
- ADD DATAFILE: Adiciona novo arquivo de dados
- DROP DATAFILE: Remove arquivo de dados (deve estar vazio)
- INITIAL_SIZE: Tamanho inicial do novo datafile
- WAIT: Aguarda conclusão da operação

InnoDB (DEPENDE DA VERSÃO):
- ADD DATAFILE: ✅ SUPORTADO em versões específicas
- INITIAL_SIZE: Tamanho inicial do novo datafile
- AUTOEXTEND_SIZE: Tamanho do incremento automático
- SET ACTIVE/INACTIVE: Estado do UNDO tablespace
- ENCRYPTION: Ativação/desativação da criptografia

PARÂMETROS COMUNS:
- RENAME TO: Renomeia o tablespace
- ENGINE: Especifica a engine (pode ser omitido)

SUPORTE POR VERSÃO:

MySQL 8.0 (documentação oficial):
❌ ADD DATAFILE não suportado para InnoDB

Versões específicas/MariaDB:
✅ ADD DATAFILE suportado para InnoDB
✅ INITIAL_SIZE funciona com InnoDB

IMPORTANTE: 
- O suporte pode variar entre versões do MySQL/MariaDB
- Sempre teste em ambiente de desenvolvimento primeiro
- Verifique a documentação da sua versão específica

VALORES RECOMENDADOS:
- AUTOEXTEND_SIZE InnoDB: 64M-1G (dependendo do uso)
- INITIAL_SIZE: 48M-1G (dependendo do datafile)
- ENCRYPTION: 'Y' para dados sensíveis
- Sempre usar WAIT em operações NDB críticas
*/

Well, that is it, folks, one more dolphin post for you. I hope you enjoy it and get organized like good Oracle DBAs, I mean, MySQL DBAs👀