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 in caso di crash per My RDS SQL

Ogni tabella in My SQL è composta da una definizione di tabella, dati e indici. Il motore SQL di archiviazione My InnoDB memorizza i dati e gli indici delle tabelle in un tablespace. InnoDB crea uno spazio tabella 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 SQL parametri chiamatoinnodb_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 innodb_file_per_table parametro su 1, il che consente di eliminare singole tabelle InnoDB e recuperare lo storage utilizzato da tali tabelle per l'istanza DB. Nella maggior parte dei casi d'uso l'impostazione del parametro innodb_file_per_table su 1 rappresenta l'opzione consigliata.

È necessario impostare il innodb_file_per_table parametro su 0 quando si dispone di un numero elevato di tabelle, ad esempio più di 1000 tabelle quando si utilizza uno storage standard (magnetico) o generico o più di 10.000 tabelle quando si utilizza lo SSD storage IOPS Provisioned. 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.

My SQL elabora ogni file di metadati, che include i tablespace, durante il ciclo di ripristino in caso di arresto anomalo. Il tempo impiegato SQL da My per elaborare le informazioni sui metadati nel tablespace condiviso è trascurabile rispetto al tempo impiegato da My per elaborare migliaia di file di tablespace quando sono presenti più tablespace. 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. Per prima cosa connettiti alla tua istanza My SQL DB, quindi esegui i comandi appropriati come illustrato di seguito. Per ulteriori informazioni, consulta Connessione a un'istanza DB che esegue il motore di SQL database My.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Ad esempio, la seguente query restituisce un'istruzione ALTER TABLE per ogni tabella InnoDB non presente nello spazio tabella condiviso.

Per le istanze My SQL 5.7 DB:

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 mie istanze SQL DB 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 ricostruzione di una SQL tabella My per spostare i metadati della tabella nel tablespace condiviso richiede temporaneamente spazio di archiviazione aggiuntivo per ricostruire la tabella, quindi l'istanza DB deve disporre di spazio di archiviazione disponibile. 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. Sebbene l'ALTERTABLEistruzione blocchi l'accesso alla replica di lettura, l'istanza DB di origine non ne risente. 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: