Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Lavorare con i tablespace InnoDB per migliorare i tempi di ripristino dopo un arresto anomalo per RDS per MySQL

Ogni tabella in MySQL è formata da una definizione della tabella, dati e indici. Il motore di storage InnoDB MySQL salva gli indici e i dati della tabella in un tablespace. InnoDB crea un tablespace globale condiviso che contiene un dizionario di dati e altri metadati rilevanti e che può inoltre contenere indici e dati della tabella. InnoDB può anche creare degli spazi tabella per ciascuna tabella e partizione. Questi spazi tabella separati vengono salvati in file con estensione .ibd e l'intestazione di ciascuno spazio tabella contiene un numero identificativo univoco.

Amazon RDS fornisce un parametro in un gruppo di parametri MySQL denominato innodb_file_per_table. Questo parametro controlla se InnoDB aggiunge nuovi dati e indici di tabella allo spazio di tabella condiviso (impostando il valore del parametro su 0) o a singoli spazi di tabella (impostando il valore del parametro su 1). Amazon RDS imposta il valore predefinito per il parametro innodb_file_per_table su 1, che consente di eliminare singole tabelle InnoDB e recuperare l'archiviazione utilizzata da tali tabelle per l'istanza database. Nella maggior parte dei casi d'uso l'impostazione del parametro innodb_file_per_table su 1 rappresenta l'opzione consigliata.

Dovresti impostare il parametro innodb_file_per_table su 0 quando hai un numero elevato di tabelle, ad esempio oltre 1.000 tabelle quando utilizzi lo storage standard (magnetico) o lo storage General Purpose SSD oppure oltre 10.000 tabelle quando utilizzi lo storage Provisioned IOPS Quando imposti questo parametro su 0, non vengono creati singoli spazi tabella, pertanto il ripristino dopo un arresto anomalo del database viene completato in minor tempo.

MySQL elabora ciascun file dei metadati, che include spazi tabella durante il ciclo di recupero dopo un arresto anomalo. Il tempo richiesto da MySQL per elaborare le informazioni dei metadati negli spazi tabella condivisi è trascurabile rispetto al tempo necessario per elaborare migliaia di file di spazi tabella quando sono presenti più spazi tabella. Poiché il numero di spazi tabella viene salvato nell'intestazione di ciascun file, il tempo complessivo per leggere tutti i file degli spazi tabella può essere di diverse ore. Ad esempio, per elaborare un milione di spazi tabella InnoDB nello storage standard per un ciclo di ripristino dopo un arresto anomalo potrebbero essere necessarie da cinque a otto ore. In alcuni casi, InnoDB può reputare la necessità di una pulizia aggiuntiva dopo un ciclo di ripristino dopo un arresto anomalo che attiverà un altro ciclo di ripristino dopo un arresto anomalo, rendendo più lungo il tempo di ripristino. Ricorda che un ciclo di ripristino dopo un arresto anomalo, oltre all'elaborazione delle informazioni degli spazi tabella, implica transazioni di rollback, riparazioni delle pagine non funzionanti e altre operazioni.

Poiché il parametro innodb_file_per_table risiede in un gruppo di parametri, puoi cambiare il valore del parametro modificando il gruppo di parametri utilizzato dalla tua istanza database senza riavviarla. Dopo aver cambiato l'impostazione, ad esempio da 1 (per creare tabelle individuali) a 0 (per utilizzare gli spazi tabella condivisi), allo spazio tabella condiviso saranno aggiunte altre tabelle InnoDB, mentre quelle esistenti continueranno ad avere degli spazi tabella individuali. Per spostare una tabella InnoDB in uno spazio tabella condiviso, devi utilizzare il comando ALTER TABLE.

Migrazione di più spazi tabella in uno spazio tabella condiviso

Puoi spostare i metadati di una tabella InnoDB dal loro spazio tabella allo spazio tabella condiviso che ricompilerà i metadati della tabella secondo l'impostazione del parametro innodb_file_per_table. Connettiti innanzitutto alla tua istanza database MySQL, quindi utilizza i comandi appropriati come mostrato di seguito. Per ulteriori informazioni, consulta Connessione all’istanza database MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Ad esempio, la seguente query restituisce un’istruzione ALTER TABLE per ogni tabella InnoDB non presente nel tablespace condiviso.

Per le istanze database MySQL 5.7:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Per le istanze database MySQL 8.4 e 8.0:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

La ricompilazione di una tabella MySQL per spostare i metadati della tabella nel tablespace condiviso richiede uno spazio di archiviazione ulteriore disponibile nell’istanza database. Durante la ricompilazione, la tabella è bloccata e inaccessibile alle query. Per le tabelle di piccole dimensioni o le tabelle a cui non si accede di frequente, questo potrebbe non essere un problema. Per tabelle di grandi dimensioni o tabelle a cui si accede di frequente in un ambiente con molti processi simultanei, puoi ricompilare le tabelle su una replica di lettura.

Puoi creare una replica di lettura e migrare i metadati della tabella nello spazio tabella condiviso sulla replica di lettura. Mentre l'istruzione ALTER TABLE blocca l'accesso sulla replica di lettura, l'istanza database di origine non viene interessata. L'istanza database di origine continuerà a generare i suoi log binari, mentre la replica di lettura sarà in ritardo durante il processo di ricompilazione della tabella. Poiché la ricompilazione richiede spazio di storage aggiuntivo e il file di log di riproduzione può essere di grandi dimensioni, dovresti creare una replica di lettura con uno storage allocato più grande rispetto all'istanza database di origine.

Per creare una replica di lettura e ricompilare le tabelle InnoDB per utilizzare lo spazio tabella condiviso, procedere come indicato di seguito: