Consigli per le mie SQL funzionalità in Aurora My SQL - Amazon Aurora
Utilizzo della replica multithread in Aurora My SQLInvocando AWS Lambda funzioni che utilizzano le funzioni My SQL nativeEvitare transazioni XA con Amazon Aurora My SQLMantenere le chiavi esterne attivate durante gli estratti conto DMLConfigurazione della frequenza di svuotamento del buffer dei registriRiduzione al minimo e risoluzione dei problemi relativi ai deadlock di Aurora My SQL

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à.

Consigli per le mie SQL funzionalità in Aurora My SQL

Le seguenti funzionalità sono disponibili nella compatibilità con Aurora My SQL for 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 My SQL

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

La replica multithread è supportata in Aurora My versione 3 e in Aurora My SQL versione 2.12.1 e successive. SQL

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

Le versioni precedenti di Aurora My SQL version 2 hanno ereditato diversi problemi relativi alla replica multithread da My Community Edition. SQL Per queste versioni, si consiglia di non utilizzare la replica multithread in produzione.

Se si utilizza la replica multithread, si consiglia di 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 My, vedere. SQL Replica di log binari multithread

Invocando AWS Lambda funzioni che utilizzano le funzioni My SQL native

Consigliamo di utilizzare le SQL funzioni My native lambda_sync e di lambda_async 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 Richiamo di una funzione Lambda da un cluster Amazon Aurora My DB SQL.

Evitare transazioni XA con Amazon Aurora My SQL

Ti consigliamo di non utilizzare le transazioni eXtended Architecture (XA) con Aurora SQL My, poiché possono causare lunghi tempi di ripristino se l'XA si trovasse nello stato. PREPARED Se devi utilizzare transazioni XA con Aurora SQL My, 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 delle transazioni XA con MySQL, consulta Transazioni XA nella documentazione My. SQL

Mantenere le chiavi esterne attivate durante gli estratti conto DML

Ti consigliamo vivamente di non eseguire alcuna istruzione Data Definition Language (DDL) quando la foreign_key_checks variabile è impostata su 0 (off).

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. Apporta modifiche al linguaggio di manipolazione dei dati (DML).

  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 My SQL Community Edition, per rendere durevoli le transazioni, il buffer di log di InnoDB deve essere scaricato su una memoria 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 aiuta a mantenere il database conforme. ACID Ti consigliamo di mantenere il valore predefinito di 1.

La modifica innodb_flush_log_at_trx_commit a un valore non predefinito può aiutare a ridurre la latenza del linguaggio di manipolazione dei dati (DML), ma compromette la durabilità dei record di registro. Questa mancanza di durabilità rende il database non conforme. ACID È consigliabile che i database siano ACID conformi per evitare il rischio di perdita di dati in caso di riavvio del server. Per ulteriori informazioni su questo parametro, consulta innodb_flush_log_at_trx_commit nella mia documentazione. SQL

In Aurora MySQL, l'elaborazione dei redo log viene scaricata sul livello di archiviazione, quindi non si verifica alcun trasferimento dei file di registro sull'istanza DB. 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.

Aurora My SQL non scarica i log nei file di dati come fa My SQL Community Edition. 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 My SQL versione 2:

  • innodb_flush_log_at_trx_commit= 0 o 2 — Il database non attende la conferma che i record di redo log siano stati scritti nel volume del cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 — Il database attende la conferma che i record di redo log siano stati scritti nel volume del cluster Aurora.

Per Aurora My SQL versione 3:

  • innodb_flush_log_at_trx_commit= 0 — Il database non attende la conferma che i record di redo log siano stati scritti nel volume del cluster Aurora.

  • innodb_flush_log_at_trx_commit= 1 o 2 — Il database attende la conferma che i record di redo log siano stati scritti nel volume del cluster Aurora.

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

Sebbene queste impostazioni possano ridurre DML la latenza del 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 si possa verificare una perdita di dati sia in My SQL Community Edition che in Aurora MySQL, il comportamento è diverso in ogni database a causa delle diverse architetture. Queste differenze architetturali possono portare a vari gradi di perdita di dati. Per assicurarvi che il database sia ACID conforme, impostate sempre su 1. innodb_flush_log_at_trx_commit

Nota

In Aurora My SQL versione 3, prima di poter passare innodb_flush_log_at_trx_commit a un valore diverso da 1, è necessario modificare il valore innodb_trx_commit_allow_data_loss di 1. In questo modo, riconosci il rischio di perdita dei dati.

Riduzione al minimo e risoluzione dei problemi relativi ai deadlock di Aurora My SQL

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 in My Community Edition. SQL

Questa correzione è inclusa nelle versioni 5.7.26 e successive di My SQL Community Edition ed è stata trasferita nelle versioni 2.10.3 e successive di SQL Aurora My. La correzione è necessaria per imporre la serializzabilità, implementando un blocco aggiuntivo per questi tipi di operazioni del linguaggio di manipolazione dei dati (DML), sulle modifiche apportate ai record in una tabella InnoDB. Questo problema è stato scoperto nell'ambito di un'indagine sui problemi di deadlock introdotti da una precedente correzione di bug di My Community Edition. SQL

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 sono disponibili nella mia SQL documentazione.

  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 utilizzareREPLACE INTO, riscrivi l'SQListruzione come transazione a più dichiarazioni come la seguente:

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

    3. Invece di utilizzareINSERT...ON DUPLICATE KEY UPDATE, riscrivi l'SQListruzione come transazione a più dichiarazioni 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. Ciò include sessioni interattive di My SQL client che potrebbero rimanere aperte per un periodo prolungato con una transazione non impegnata. 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 loro comportamenti, consulta Livelli di isolamento delle transazioni nella mia documentazione. SQL

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 My SQL quando le transazioni dell'applicazione tentano di applicare 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.