Suggerimenti per le funzionalità MySQL in Aurora MySQL

Le seguenti funzionalità sono disponibili in Aurora MySQL per la compatibilità con MySQL. Tuttavia, hanno problemi di prestazioni, scalabilità, stabilità o compatibilità nell'ambiente Aurora. Pertanto, suggeriamo di seguire specifiche linee guida quando si utilizzano queste caratteristiche. Ad esempio, non è consigliabile utilizzare determinate funzionalità per le distribuzioni Aurora di produzione.

Utilizzo della replica multithread in Aurora MySQL

Con la replica del log binario multithread, un thread SQL legge gli eventi dal log di inoltro e li mette in coda per l'applicazione dei thread SQL worker. I thread di lavoro SQL sono gestiti da un thread coordinatore. Gli eventi di log binari vengono applicati in parallelo quando possibile.

La replica multithread è supportata in Aurora MySQL versione 3 e in Aurora MySQL 2.12.1 e versioni successive.

Per le versioni precedenti alla 3.04 di Aurora MySQL, Aurora utilizza la replica a thread singolo per impostazione predefinita quando un cluster di database Aurora MySQL viene utilizzato come replica di lettura per la replica dei log binari.

Le versioni precedenti di Aurora MySQL versione 2 hanno ereditato diversi problemi relativi alla replica multithread da MySQL Community Edition. Per quelle versioni, ti consigliamo di non utilizzare la replica multithread in produzione.

Qualora decidessi di utilizzarla, è consigliabile testarla accuratamente.

Per ulteriori informazioni sull'uso della replica in Amazon Aurora, consulta Replica con Amazon Aurora. Per ulteriori informazioni sulla replica multithread in Aurora MySQL, consulta Replica dei log binari multithread.

Invocazione delle funzioni AWS Lambda usando funzioni MySQL native

Consigliamo di utilizzare le funzioni native di MySQL lambda_sync e lambda_async per richiamare le funzioni Lambda.

In caso di utilizzo della procedura mysql.lambda_async obsoleta, ti consigliamo di eseguire il wrapping delle chiamate alla procedura mysql.lambda_async in una stored procedure. Questa stored procedure può essere chiamata da varie origini, ad esempio trigger o codice client. Questo approccio può evitare problemi di resistenza non corrispondente e rendere più semplice per i programmatori del database richiamare le funzioni Lambda.

Per ulteriori informazioni sul richiamo delle funzioni Lambda da Amazon Aurora, consulta Chiamare una funzione Lambda da un cluster DB Amazon Aurora MySQL.

Evitatre le transazioni XA con Amazon Aurora MySQL

Ti consigliamo di non utilizzare transazioni eXtended Architecture (XA) con Aurora MySQL, poiché possono provocare tempi di ripristino lunghi se XA si trova in stato PREPARED. Se devi utilizzare transazioni XA con Aurora MySQL, segui queste best practice:

• Non lasciare una transazione XA aperta nello stato PREPARED.

• Mantieni le dimensioni delle transazioni XA più ridotte possibile.

Per ulteriori informazioni sull'utilizzo di transazioni XA con MySQL, consulta XA Transactions nella documentazione di MySQL.

Mantenere le chiavi esterne attivate durante le istruzioni DML

Consigliamo fortemente di non eseguire alcuna istruzione DDL (Data Definition Language) quando la variabile foreign_key_checks è impostata su 0 (disattivata).

Per inserire o aggiornare righe che richiedono una violazione temporanea delle chiavi esterne, procedere nel seguente modo:

1. Imposta foreign_key_checks su 0.

2. Apportare le modifiche alle istruzioni DML (Data Manipulation Language).

3. Assicurarsi che le modifiche apportate non violino eventuali vincoli delle chiavi esterne.

4. Impostare foreign_key_checks su 1 (attivato).

È inoltre necessario attenersi alle ulteriori best practice seguenti per i vincoli delle chiavi esterne:

• Assicurarsi che le applicazioni client non impostino la variabile foreign_key_checks su 0 come parte della variabile init_connect.

• Se un ripristino da un backup logico come mysqldump non riesce o è incompleto, assicurarsi che foreign_key_checks sia impostato su 1 prima di avviare eventuali altre operazioni nella stessa sessione. Un backup logico imposta foreign_key_checks su 0 all'avvio.

Configurazione della frequenza di svuotamento del buffer dei registri

In MySQL Community Edition, per rendere le transazioni durevoli, il buffer dei registri InnoDB deve essere svuotato in un'archiviazione durevole. Per configurare la frequenza di svuotamento del buffer dei registri su disco si utilizza il parametro innodb_flush_log_at_trx_commit.

Quando si imposta il parametro innodb_flush_log_at_trx_commit sul valore predefinito di 1, il buffer dei registri viene svuotato ad ogni commit della transazione. Questa impostazione consente di mantenere il database conforme ad ACID. Ti consigliamo di mantenere il valore predefinito di 1.

La modifica del parametro innodb_flush_log_at_trx_commit in un valore non predefinito può contribuire a ridurre la latenza DML (Data Manipulation Language), ma sacrifica la durabilità dei record di log. Questa mancanza di durabilità rende il database non conforme ad ACID. È consigliabile che i database siano conformi ad ACID per evitare il rischio di perdita di dati in caso di riavvio di un server. Per ulteriori informazioni su questo parametro, consultare innodb_flush_log_at_trx_commit nella documentazione di MySQL.

In Aurora MySQL, l'elaborazione dei registri redo continuerà nel livello di archiviazione, pertanto sull'istanza database non si verificherà lo svuotamento nei file di registro. Quando viene emesso un comando di scrittura, i registri redo vengono inviati direttamente dall'istanza database di scrittura al volume del cluster Aurora. Le sole scritture che attraversano la rete sono i record di registro redo. Nessuna pagina viene mai scritta dal livello database.

Per impostazione predefinita, ogni thread che esegue il commit di una transazione attende la conferma dal volume del cluster Aurora. Questa conferma indica che tale record e tutti i record di registro redo precedenti sono stati scritti e hanno raggiunto il quorum. La persistenza dei record di registro e il raggiungimento del quorum rendono la transazione durevole, tramite commit automatico o commit esplicito. Per ulteriori informazioni sull'architettura di archiviazione Aurora, consultare Amazon Aurora storage demystified.

A differenza di MySQL Community Edition, Aurora MySQL non scarica i registri nei file di dati. Tuttavia, è possibile utilizzare il parametro innodb_flush_log_at_trx_commit per allentare i vincoli di durabilità durante la scrittura dei record di registro redo nel volume del cluster Aurora.

Per Aurora MySQL versione 2:

• innodb_flush_log_at_trx_commit = 0 o 2: il database non attende la conferma che i record di log di redo vengono scritti nel volume del cluster Aurora.

• innodb_flush_log_at_trx_commit = 1: il database attende la conferma che i record di log di redo vengono scritti nel volume del cluster Aurora.

Per Aurora MySQL versione 3:

• innodb_flush_log_at_trx_commit = 0: il database non attende la conferma che i record di log di redo vengono scritti nel volume del cluster Aurora.

• innodb_flush_log_at_trx_commit = 1 o 2: il database attende la conferma che i record di log di redo vengono scritti nel volume del cluster Aurora.

Pertanto, per ottenere in Aurora MySQL versione 3 lo stesso comportamento non predefinito che avresti in Aurora MySQL versione 2 con il valore impostato su 0 o 2, imposta il parametro su 0.

Sebbene queste impostazioni possano ridurre la latenza DML al client, possono anche causare la perdita di dati in caso di failover o riavvio. Pertanto, ti consigliamo di mantenere il parametro innodb_flush_log_at_trx_commit impostato sul valore predefinito di 1.

Sebbene la perdita di dati si possa verificare sia in MySQL Community Edition sia in Aurora MySQL, il comportamento varia in ogni database a causa delle diverse architetture. Queste differenze architetturali possono portare a vari gradi di perdita di dati. Per essere certi che il database sia conforme ad ACID, imposta sempre innodb_flush_log_at_trx_commit su 1.

Nota

In Aurora MySQL versione 3, prima di poter impostare innodb_flush_log_at_trx_commit su un valore diverso da 1, devi prima impostare il valore di innodb_trx_commit_allow_data_loss su 1. In questo modo, riconosci l’esistenza del rischio di perdita dei dati.

Contenimento e risoluzione dei problemi di deadlock di Aurora MySQL

Gli utenti che eseguono carichi di lavoro che restituiscono regolarmente violazioni dei vincoli su indici secondari univoci o chiavi esterne quando modificano contemporaneamente i record sulla stessa pagina di dati, possono riscontrare un aumento dei deadlock e dei timeout di attesa dei blocchi. Questi deadlock e timeout sono dovuti a una correzione di bug di MySQL Community Edition.

Questa correzione è inclusa in MySQL Community Edition 5.7.26 e versioni successive ed è stata sottoposta al backport in Aurora MySQL 2.10.3 e versioni successive. La correzione è necessaria per applicare la serializzabilità, implementando per questi tipi di operazioni del linguaggio DML (Data Manipulation Language) un blocco aggiuntivo per le modifiche apportate ai record in una tabella InnoDB. Questo problema è stato individuato nell'ambito di un'indagine sui problemi di deadlock introdotti da una precedente correzione di bug di MySQL Community Edition.

La correzione ha modificato la gestione interna per il rollback parziale dell'aggiornamento di una tupla (riga) nel motore di archiviazione InnoDB. Le operazioni che generano violazioni dei vincoli su chiavi esterne o indici secondari univoci causano un rollback parziale. Sono incluse, a titolo esemplificativo e non esaustivo, le istruzioni INSERT...ON DUPLICATE KEY UPDATE, REPLACE INTO, e INSERT IGNORE simultanee (upsert).

In questo contesto, il rollback parziale non si riferisce alle transazioni a livello di applicazione, ma piuttosto a un rollback interno di InnoDB delle modifiche di un indice cluster, quando si verifica una violazione dei vincoli. Ad esempio, durante un'operazione upsert viene individuato un valore chiave duplicato.

In una normale operazione di inserimento, InnoDB crea atomicamente le voci degli indici secondari e in cluster per ciascun indice. Se InnoDB rileva un valore duplicato in un indice secondario univoco durante un'operazione di upsert, la voce inserita nell'indice in cluster deve essere ripristinata (rollback parziale) e l'aggiornamento deve quindi essere applicato alla riga duplicata esistente. Durante questa fase di rollback parziale interno, InnoDB deve bloccare ogni record considerato come parte dell'operazione. La correzione garantisce la serializzabilità delle transazioni introducendo un blocco aggiuntivo dopo il rollback parziale.

Riduzione al minimo dei deadlock di InnoDB

Puoi utilizzare i seguenti approcci per ridurre la frequenza dei deadlock nell'istanza database. Altri esempi possono sono disponibili nella documentazione di MySQL.

1. Per ridurre le possibilità di deadlock, esegui il commit delle transazioni immediatamente dopo aver apportato una serie di modifiche correlate. Puoi farlo suddividendo le transazioni di grandi dimensioni (aggiornamenti di più righe tra i commit) in transazioni più piccole. Se stai inserendo righe in batch, prova a ridurre le dimensioni degli inserimenti batch, specialmente quando usi le operazioni upsert menzionate in precedenza.

   Per ridurre il numero di possibili rollback parziali, puoi provare alcuni dei seguenti approcci:

   1. Sostituisci le operazioni di inserimento in batch con l'inserimento di una riga alla volta. In tal modo puoi ridurre il periodo di tempo in cui i blocchi vengono mantenuti dalle transazioni che possono avere conflitti.

   2. Invece di usare REPLACE INTO, riscrivi l'istruzione SQL come transazione a più istruzioni come la seguente:

      BEGIN;
      DELETE conflicting rows;
      INSERT new rows;
      COMMIT;

   3. Invece di usare INSERT...ON DUPLICATE KEY UPDATE, riscrivi l'istruzione SQL come transazione a più istruzioni come la seguente:

      BEGIN;
      SELECT rows that conflict on secondary indexes;
      UPDATE conflicting rows;
      INSERT new rows;
      COMMIT;

2. Evita le transazioni di lunga durata, attive o inattive, che potrebbero rimanere bloccate. Sono incluse le sessioni client MySQL interattive che potrebbero essere aperte per un periodo prolungato con una transazione non sottoposta a commit. Quando si ottimizzano le dimensioni delle transazioni o dei batch, l'impatto può variare in base a una serie di fattori come la concorrenza, il numero di duplicati e la struttura delle tabelle. Qualsiasi modifica deve essere implementata e testata in base al carico di lavoro.

3. In alcune situazioni, i deadlock possono verificarsi quando due transazioni tentano di accedere agli stessi set di dati, in una o più tabelle, in ordini diversi. Per evitare queste situazioni, è possibile modificare le transazioni per accedere ai dati nello stesso ordine, serializzando così l'accesso. Ad esempio, crea una coda di transazioni da completare. Questo approccio può aiutare a evitare deadlock quando si verificano più transazioni contemporaneamente.

4. L'aggiunta di indici scelti con attenzione alle tabelle può migliorare la selettività e ridurre la necessità di accedere alle righe, con conseguente riduzione dei blocchi.

5. Se riscontri un blocco di intervallo, puoi impostare il livello di isolamento della transazione su READ COMMITTED per la sessione o la transazione per evitarlo. Per ulteriori informazioni sui livelli di isolamento di InnoDB e sui relativi comportamenti, consulta Livelli di isolamento delle transazioni nella documentazione di MySQL.

Nota

Sebbene sia possibile prendere precauzioni per ridurre la possibilità che si verifichino, i deadlock sono un comportamento previsto del database e possono comunque verificarsi. Le applicazioni devono disporre della logica necessaria per gestire i deadlock quando si verificano. Ad esempio, implementa la logica dei tentativi e del backup nell'applicazione. È consigliabile risolvere la causa principale del problema, ma se si verifica un deadlock, l'applicazione ha la possibilità di attendere e ritentare.

Monitoraggio dei deadlock di InnoDB

I deadlock possono verificarsi in MySQL quando le transazioni delle applicazioni cercano di accettare blocchi a livello di tabella e di riga in un modo che si traduce in un'attesa circolare. Un deadlock occasionale di InnoDB non è necessariamente un problema, perché il motore di archiviazione InnoDB rileva immediatamente la condizione e ripristina automaticamente le transazioni. Se riscontri spesso dei deadlock, ti consigliamo di rivedere e modificare l'applicazione per contenere i problemi di prestazioni ed evitare i deadlock. Quando il rilevamento dei deadlock è attivato (impostazione predefinita), InnoDB rileva automaticamente i deadlock delle transazioni e ripristina una o più transazioni per interrompere il deadlock. InnoDB cerca di selezionare piccole transazioni da ripristinare, in cui la dimensione della transazione è determinata dal numero di righe inserite, aggiornate o eliminate.

• Istruzione SHOW ENGINE: l'istruzione SHOW ENGINE INNODB STATUS \G contiene i dettagli del deadlock più recente riscontrato nel database dall'ultimo riavvio.

• Log degli errori MySQL: se si verificano frequenti deadlock in cui l'output dell'istruzione SHOW ENGINE è inadeguato, puoi attivare il parametro del cluster di database innodb_print_all_deadlocks.

  Quando questo parametro è attivo, le informazioni su tutti i deadlock nelle transazioni utente InnoDB vengono registrate nel log degli errori di Aurora MySQL.

• Metriche di Amazon CloudWatch: ti consigliamo inoltre di monitorare in modo proattivo i deadlock utilizzando la metrica CloudWatch Deadlocks. Per ulteriori informazioni, consulta Parametri a livello di istanza per Amazon Aurora.

• File di log Amazon CloudWatch: con i file di log CloudWatch puoi esaminare le metriche, eseguire analisi dei dati di log e creare allarmi in tempo reale. Per ulteriori informazioni, consulta Monitorare gli errori in Amazon Aurora MySQL e Amazon RDS per MySQL con Amazon CloudWatch e inviare notifiche tramite Amazon SNS.

  Utilizzando File di log CloudWatch con innodb_print_all_deadlocks attivo, puoi configurare gli allarmi per avvisare quando il numero di deadlock supera una determinata soglia. Per definire una soglia, ti consigliamo di osservare le tendenze e utilizzare un valore basato sul normale carico di lavoro.

• Approfondimenti sulle prestazioni: quando utilizzi Approfondimenti sulle prestazioni, puoi monitorare le metriche innodb_deadlocks e innodb_lock_wait_timeout. Per ulteriori informazioni su tali parametri, consulta Contatori non nativi per Aurora MySQL.