Best practice con Amazon Aurora MySQL - Amazon Aurora
Determinazione dell'istanza database a cui si è connessiBest practice per le prestazioni e il dimensionamento di Aurora MySQLBest practice per l'elevata disponibilità di Aurora MySQLSuggerimenti per Aurora MySQL

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

Best practice con Amazon Aurora MySQL

Questo argomento include informazioni sulle best practice e sulle opzioni per l'utilizzo o la migrazione di dati a un cluster di database Amazon Aurora MySQL. Le informazioni contenute in questo argomento riassumono e ribadiscono alcune linee guida e procedure disponibili in Gestione di un cluster DB Amazon Aurora.

Determinazione dell'istanza database a cui si è connessi

Per determinare a quale istanza database in un cluster di database Aurora MySQL è collegata una connessione, controlla la variabile globale innodb_read_only come mostrato nell'esempio seguente.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

La variabile innodb_read_only è impostata su ON se si è connessi a un'istanza database di lettura. Questa impostazione è OFF se si è connessi a un'istanza database di scrittura, ad esempio un'istanza primaria in un cluster con provisioning.

Questo approccio può essere utile se desideri aggiungere logica al codice dell'applicazione per bilanciare il carico di lavoro o garantire che un'operazione di scrittura utilizzi la connessione corretta.

Best practice per le prestazioni e il dimensionamento di Aurora MySQL

È possibile applicare le best practice seguenti per migliorare le prestazioni e la scalabilità dei cluster Aurora MySQL.

Utilizzo delle classi di istanza T per lo sviluppo e i test

Le istanze Amazon Aurora MySQL che utilizzano le classi di istanza database db.t2, db.t3 o db.t4g sono ideali per applicazioni che non supportano un carico di lavoro elevato per una quantità di tempo significativa. Le istanze T sono progettate per offrire prestazioni di base moderate e garantire prestazioni notevolmente maggiori se il carico di lavoro lo richiede. Sono concepite per carichi di lavoro che non utilizzano completamente la CPU spesso o in maniera regolare, ma che occasionalmente necessitano di un incremento delle prestazioni. Consigliamo di utilizzare le classi di istanza database T solo per i server di sviluppo e test o altri server non di produzione. Per ulteriori informazioni sulle classi di istanze T, consulta Istanze espandibili.

Se il cluster Aurora è più grande di 40 TB, non utilizzare le classi di istanza T. Quando il database dispone di un volume elevato di dati, il sovraccarico di memoria per la gestione degli oggetti dello schema può superare la capacità di un'istanza T.

Non abilitare lo schema di prestazioni MySQL sulle istanze T di Amazon Aurora MySQL. In caso contrario, è possibile che la memoria disponibile per l'istanza T potrebbe esaurirsi.

Suggerimento

Se il database a volte è inattivo e altre volte ha un notevole carico di lavoro, puoi utilizzare Aurora Serverless v2 come alternativa alle istanze T. Con Aurora Serverless v2 puoi definire un intervallo di capacità e Aurora dimensiona automaticamente il database in base al carico di lavoro corrente. Per informazioni dettagliate sull'utilizzo, consulta Uso di Aurora Serverless v2. Per le versioni del motore del database che puoi utilizzare con Aurora Serverless v2, consulta Requisiti e limitazioni per Aurora Serverless v2.

Quando si utilizza un'istanza T come istanza di database in un cluster di database Aurora MySQL, si consiglia quanto segue:

  • Utilizza la stessa classe di istanza database per tutte le istanze nel cluster di database. Ad esempio, se si utilizza db.t2.medium per l'istanza di scrittura, allora si consiglia di usare db.t2.medium anche per le istanze di lettura.

  • Non regolare le impostazioni di configurazione relative alla memoria, ad esempio innodb_buffer_pool_size. Aurora utilizza un insieme altamente sintonizzato di valori di default per i buffer di memoria sulle istanze T. Questi valoridi di default speciali sono necessari per consentire l'esecuzione di Aurora su istanze vincolate dalla memoria. Se si modificano le impostazioni relative alla memoria su un'istanza T, è molto più probabile che si verifichino out-of-memory condizioni, anche se la modifica è intesa ad aumentare le dimensioni del buffer.

  • Eseguire il monitoraggio del saldo del credito CPU (CPUCreditBalance) per garantire che il relativo livello sia sostenibile. In altre parole, i crediti CPU vengono accumulati alla stessa velocità con cui vengono utilizzati.

    Una volta esauriti i crediti CPU per un'istanza, si assisterà a un calo immediato nella CPU disponibile e a un aumento della latenza di lettura e scrittura per l'istanza. Questa situazione provoca un'importante riduzione delle prestazioni complessive dell'istanza.

    Se il livello del saldo del credito CPU non è sostenibile, consigliamo di modificare l'istanza database in modo da utilizzare una delle classi di istanza database R supportate (dimensionamento del calcolo).

    Per ulteriori informazioni sui parametri di monitoraggio, consulta Visualizzazione delle metriche nella console Amazon RDS.

  • Monitora il ritardo di replica (AuroraReplicaLag) tra l'istanza di scrittura e le istanze di lettura.

    Se un'istanza di lettura esaurisce i crediti della CPU prima dell'istanza si scrittura, il ritardo risultante può causare il riavvio frequente dell'istanza di lettura. Si tratta di un risultato comune nei casi in cui un'applicazione dispone di un carico elevato di operazioni di lettura distribuito tra le istanze di lettura, allo stesso tempo che l'istanza di scrittura presenta un carico minimo di operazioni di scrittura.

    Se il ritardo della replica aumenta notevolmente, sarà necessario verificare che il saldo del credito CPU per le istanze di lettura nel cluster DB non sia esaurito.

    Se il livello del saldo del credito CPU non è sostenibile, consigliamo di modificare l'istanza database in modo da utilizzare una delle classi di istanza database R supportate (dimensionamento del calcolo).

  • Mantenere il numero di inserimenti per transazione al di sotto di 1 milione per i cluster di database per cui è abilitata la registrazione binaria.

    Se il gruppo di parametri del cluster DB per il cluster DB ha il binlog_format parametro impostato su un valore diverso daOFF, il cluster di DB potrebbe presentare out-of-memory delle condizioni se il cluster DB riceve transazioni che contengono più di 1 milione di righe da inserire. È possibile monitorare il parametro della memoria da liberare (FreeableMemory) per determinare se la memoria disponibile per il cluster di database sta per esaurire. Controllando quindi il parametro delle operazioni di scrittura (VolumeWriteIOPS) sarà possibile verificare se l'istanza di scrittura riceve un carico elevato di operazioni di scrittura. In tal caso, consigliamo di aggiornare l'applicazione per limitare gli inserimenti in una transazione a un numero inferiore a 1 milione. In alternativa, è possibile modificare l'istanza in modo da utilizzare una delle classi di istanza database R supportate (dimensionamento del calcolo).

Ottimizzazione delle query di join indicizzate Aurora MySQL con prefetch asincrono delle chiavi

Aurora MySQL può utilizzare il prefetch asincrono delle chiavi per migliorare le prestazioni delle query che eseguono il join delle tabelle negli indici. Questa funzionalità migliora le prestazioni anticipando le righe necessarie per eseguire query in cui una query JOIN richieda l'uso dell'algoritmo di join Batched Key Access (BKA) e le funzionalità di ottimizzazione Multi-Range Read (MRR). Per ulteriori informazioni su BKA e MRR, consulta Block Nested-Loop and Batched Key Access Joins e Multi-Range Read Optimization nella documentazione di MySQL.

Per trarre vantaggio dalla funzionalità di prefetch asincrono delle chiavi, è necessario che una query utilizzi sia gli algoritmi BKA sia le funzionalità MRR. Tale query viene in genere eseguita quando la clausola JOIN di una query utilizza un indice secondario, ma richiede anche alcune colonne dell'indice principale. Puoi ad esempio utilizzare il prefetch asincrono delle chiavi quando una clausola JOIN rappresenta un equijoin sui valori di indice tra una tabella esterna di piccole dimensioni e una interna di dimensioni maggiori, in cui l'indice è altamente selettivo nella tabella più grande. Il prefetch asincrono delle chiavi interagisce con BKA e MRR per eseguire una ricerca nell'indice da secondario a principale durante la valutazione di una clausola JOIN. Il prefetch asincrono delle chiavi identifica le righe necessarie per eseguire la query durante la valutazione della clausola JOIN. Utilizza quindi un thread in background per caricare in modo asincrono le pagine contenenti le righe in memoria prima di eseguire la query.

Il prefetch asincrono delle chiavi è disponibile per Aurora MySQL versione 2.10 e successive e per la versione 3. Per ulteriori informazioni sulle versioni di Aurora MySQL, consulta Aggiornamenti del motore di database per Amazon Aurora My SQL.

Abilitazione del prefetch asincrono delle chiavi

È possibile abilitare la funzionalità di prefetch asincrono delle chiavi impostando aurora_use_key_prefetch, una variabile del server MySQL, su on. Per impostazione predefinita, questo valore è impostato su on. È tuttavia possibile abilitare il prefetch asincrono delle chiavi solo dopo aver abilitato anche l'algoritmo di join BKA e aver disabilitato la funzionalità MRR basata sui costi. A tale scopo, è necessario impostare i seguenti valori per optimizer_switch, una variabile di server MySQL:

  • Imposta batched_key_access su on. Questo valore controlla l'utilizzo dell'algoritmo di join BKA. Per impostazione predefinita, questo valore è impostato su off.

  • Imposta mrr_cost_based su off. Questo valore controlla l'utilizzo della funzionalità MRR basata sui costi. Per impostazione predefinita, questo valore è impostato su on.

È attualmente possibile impostare questi valori solo a livello di sessione. Il seguente esempio illustra come impostare questi valori in modo da abilitare la funzionalità di prefetch asincrono delle chiavi per la sessione corrente eseguendo le istruzioni SET.


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

In modo analogo, è possibile utilizzare le istruzioni SET per disabilitare la funzionalità di prefetch asincrono delle chiavi e l'algoritmo di join BKA e riabilitare la funzionalità MRR basata sui costi per la sessione corrente, come mostrato nell'esempio seguente.


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

Per ulteriori informazioni sulle opzioni di ottimizzazione batched_key_access e mrr_cost_based, consulta Switchable Optimizations nella documentazione di MySQL.

Ottimizzazione delle query per il prefetch asincrono delle chiavi

È possibile verificare se una query può trarre vantaggio dalla funzionalità di prefetch asincrono delle chiavi. A tale scopo, utilizza l'istruzione EXPLAIN per profilare la query prima di eseguirla. L'istruzione EXPLAIN fornisce informazioni sul piano di esecuzione da utilizzare per una query specificata.

Nell'output dell'istruzione EXPLAIN la colonna Extra descrive le informazioni aggiuntive incluse nel piano di esecuzione. Se la funzionalità di prefetch asincrono delle chiavi si applica a una tabella utilizzata nella query, questa colonna include uno dei seguenti valori:

  • Using Key Prefetching

  • Using join buffer (Batched Key Access with Key Prefetching)

Il seguente esempio illustra l'utilizzo di EXPLAIN per visualizzare il piano di esecuzione per una query che può sfruttare il prefetch asincrono delle chiavi.


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

Per ulteriori informazioni sul formato di output EXPLAIN, consulta l'argomento relativo al formato di output EXPLAIN esteso nella documentazione di MySQL.

Ottimizzazione di grandi query di join Aurora MySQL con hash join

Se devi eseguire il join di una grande quantità di dati tramite una query equijoin, l'utilizzo di un hash join può migliorare le prestazioni della query. Puoi abilitare gli hash join per Aurora MySQL.

Una colonna hash join può essere una qualsiasi espressione complessa. Di seguito sono indicati i modi per eseguire il confronto tra i tipi di dati in una colonna hash join:

  • È possibile confrontare qualsiasi elemento nella categoria dei tipi di dati numerici precisi, ad esempio int, bigint, numeric e bit.

  • È possibile confrontare qualsiasi elemento nella categoria dei tipi di dati numerici di approssimazione, ad esempio float e double.

  • È possibile confrontare elementi nei tipi di stringa, se i tipi di stringa dispongono dello stesso set di caratteri e delle stesse regole di confronto.

  • È possibile confrontare elementi con tipi di dati data e timestamp, se i tipi corrispondono.

Nota

Non è possibile confrontare i tipi di dati in categorie differenti.

Di seguito sono indicate le limitazioni che si applicano agli hash join per Aurora MySQL:

  • Gli outer join sinistri-destri non sono supportati per Aurora MySQL versione 2, ma sono supportati per la versione 3.

  • I semi-join come le query secondarie non sono supportati, a meno che non vengano prima materializzate le query secondarie.

  • Aggiornamenti o eliminazioni di più tabelle non sono supportati.

    Nota

    Aggiornamenti o eliminazioni di tabelle singole sono supportati.

  • Le colonne con tipi di dati BLOB e spaziali non possono essere colonne join in un hash join.

Abilitazione di hash join

Per abilitare gli hash join:

  • Aurora MySQL versione 2: imposta il parametro di database o il parametro del cluster di database aurora_disable_hash_join su 0. La disattivazione di aurora_disable_hash_join imposta il valore di optimizer_switch su hash_join=on.

  • Aurora MySQL versione 3: imposta il parametro del server MySQL optimizer_switch su block_nested_loop=on.

Gli hash join sono attivati per impostazione predefinita in Aurora MySQL Versione 3 e Aurora MySQL versione 2. Nell'esempio seguente viene illustrato come abilitare gli hash join per Aurora MySQL versione 3. È possibile rilasciare l’istruzione select @@optimizer_switch per prima cosa per vedere quali altre impostazioni sono presenti nella stringa dei parametri di SET. Aggiornamento di un'impostazione nel parametro di optimizer_switch non cancella o modifica le altre impostazioni.

mysql> SET optimizer_switch='block_nested_loop=on';
Nota

Per Aurora MySQL Versione 3, il supporto hash join è disponibile in tutte le versioni secondarie ed è attivato per impostazione predefinita.

Per Aurora MySQL versione 2, il supporto hash join è disponibile in tutte le versioni secondarie. In Aurora MySQL versione 2, la funzionalità di join hash è sempre controllata dal valore aurora_disable_hash_join.

Con questa impostazione, l'ottimizzatore sceglie di utilizzare un hash join in base al costo, alle caratteristiche della query e alla disponibilità delle risorse. Se la stima dei costi non è corretta, puoi imporre all'ottimizzatore di scegliere un hash join, impostando hash_join_cost_based, una variabile del server MySQL, su off. L'esempio seguente illustra come imporre all'ottimizzatore di scegliere un hash join.


mysql> SET optimizer_switch='hash_join_cost_based=off';
Nota

Questa impostazione sostituisce le decisioni dell'ottimizzatore basato sui costi. Sebbene l'impostazione possa essere utile per il test e lo sviluppo, si consiglia di non utilizzarla in produzione.

Ottimizzazione delle query per gli hash join

Per sapere se una query può utilizzare un hash join, è possibile utilizzare in primo luogo l'istruzione EXPLAIN per profilare la query. L'istruzione EXPLAIN fornisce informazioni sul piano di esecuzione da utilizzare per una query specificata.

Nell'output dell'istruzione EXPLAIN la colonna Extra descrive le informazioni aggiuntive incluse nel piano di esecuzione. Se un hash join si applica alle tabelle utilizzate nella query, questa colonna include valori simili a quelli indicato di seguito:

  • Using where; Using join buffer (Hash Join Outer table table1_name)

  • Using where; Using join buffer (Hash Join Inner table table2_name)

Il seguente esempio illustra l'utilizzo di EXPLAIN per visualizzare il piano di esecuzione per una query con hash join.


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

Nell'output Hash Join Inner table indica la tabella utilizzata per creare la tabella hash e Hash Join Outer table indica la tabella utilizzata per il probe della tabella hash.

Per ulteriori informazioni sul formato di output EXPLAIN esteso, consulta Formato di output EXPLAIN esteso nella documentazione di MySQL.

In Aurora MySQL 2.08 e versioni successive, è possibile utilizzare gli hint SQL per determinare se una query utilizza o meno hash join e quali tabelle utilizzare per i lati di build e probe del join. Per informazioni dettagliate, vedi Aurora I miei suggerimenti SQL.

Utilizzo di Amazon Aurora per dimensionare le letture per il database MySQL

Puoi utilizzare Amazon Aurora con l'istanza database MySQL per usufruire delle funzionalità di dimensionamento della lettura di Amazon Aurora ed espandere il reale carico di lavoro della tua istanza database MySQL. Per utilizzare Aurora per ridimensionare la lettura dell'istanza database MySQL, crea un cluster di database Aurora MySQL e rendilo una replica di lettura per l'istanza database MySQL. Quindi connettiti al cluster Aurora MySQL per elaborare le query di lettura. Il database di origine può essere un'istanza database RDS for MySQL o un database MySQL in esecuzione esternamente a Amazon RDS. Per ulteriori informazioni, consulta Ridimensionamento delle letture per il tuo SQL database My con Amazon Aurora.

Ottimizzazione delle operazioni di timestamp

Quando il valore della variabile di sistema time_zone è impostato su SYSTEM, ogni chiamata di funzione MySQL che richiede un calcolo del fuso orario effettua una chiamata alla libreria di sistema. Quando esegui istruzioni SQL che restituiscono o modificano tali valori TIMESTAMP in caso di elevata concorrenza, è possibile che si verifichi un aumento della latenza, dei conflitti di blocco e dell'utilizzo della CPU. Per ulteriori informazioni, consulta time_zone nella documentazione di MySQL.

Per evitare questo comportamento, consigliamo di modificare il valore del parametro time_zone del cluster database impostandolo su UTC. Per ulteriori informazioni, consulta Modifica dei parametri in un gruppo di parametri del cluster DB in Amazon Aurora.

Sebbene il parametro time_zone sia dinamico (non richiede il riavvio del server di database), il nuovo valore viene utilizzato solo per le nuove connessioni. Per assicurarti che tutte le connessioni siano aggiornate in modo che venga utilizzato il nuovo valore time_zone, consigliamo di riciclare le connessioni alle applicazioni dopo aver aggiornato il parametro del cluster database.

Best practice per l'elevata disponibilità di Aurora MySQL

È possibile applicare le best practice seguenti per migliorare la disponibilità dei cluster Aurora MySQL.

Utilizzo di Amazon Aurora per il disaster recovery con i database MySQL

Puoi utilizzare Amazon Aurora con l'istanza database MySQL per creare un backup offsite per il disaster recovery. Per utilizzare Aurora per il disaster recovery dell'istanza database MySQL è necessario creare un cluster di database Amazon Aurora e renderlo uno slave di replica dell'istanza database MySQL. Ciò si applica a un'istanza database RDS for MySQL o a un database MySQL in esecuzione esternamente a Amazon RDS.

Importante

Quando si imposta la replica tra un'istanza database MySQL e un cluster di database Amazon Aurora MySQL è necessario monitorare la replica per assicurarsi che venga mantenuta l'integrità e se necessario eseguirne la riparazione.

Per istruzioni su come creare un cluster di database Amazon Aurora MySQL e impostarlo come slave di lettura dell'istanza database MySQL, segui la procedura in Utilizzo di Amazon Aurora per dimensionare le letture per il database MySQL.

Per ulteriori informazioni sui modelli di ripristino di emergenza, consulta How to choose the best disaster recovery option for your Amazon Aurora MySQL cluster.

Migrazione da MySQL ad Amazon Aurora MySQL con tempi di inattività ridotti

Durante l'importazione dei dati da un database MySQL che supporta un'applicazione live a un cluster di database Amazon Aurora MySQL, è possibile che voglia ridurre il periodo durante il quale il servizio risulta interrotto in fase di migrazione. Per farlo è possibile utilizzare la procedura documentata in Importazione dei dati in un'istanza database MySQL o MariaDB riducendo i tempi di inattività nella Guida per l'utente di Amazon Relational Database Service. che può essere particolarmente utile se utilizzi un database di dimensioni particolarmente elevate. La procedura ti consente di ridurre il costo dell'importazione tramite la riduzione della quantità di dati passati in rete ad AWS.

La procedura elenca le fasi per trasferire una copia dei dati del database a un'istanza di Amazon EC2 e importare i dati in una nuova istanza database di RDS for MySQL. Poiché Amazon Aurora è compatibile con MySQL, puoi utilizzare un cluster di database Amazon Aurora per l'istanza database Amazon RDS MySQL.

Evitare il rallentamento delle prestazioni, il riavvio automatico e il failover per istanze database Aurora MySQL

Se si esegue uno o più carichi di lavoro pesanti che eccedono le risorse allocate dell'istanza database, è possibile che le risorse su cui si esegue l'applicazione e il database Aurora si esauriscano. Per ottenere parametri sulla tua istanza di database come l'utilizzo della CPU, l'utilizzo della memoria e il numero di connessioni al database utilizzate, puoi fare riferimento ai parametri forniti da Amazon, Performance CloudWatch Insights e Enhanced Monitoring. Per ulteriori informazioni sul monitoraggio dell'istanza database, consultare Monitoraggio dei parametri in un cluster di database Amazon Aurora.

Se il carico di lavoro esaurisce le risorse in uso, è possibile che l'istanza database rallenti, venga riavviata o esegua un failover su un'altra istanza database. Per evitare che questo si verifichi, occorre monitorare l'utilizzo delle risorse, esaminare il carico di lavoro in esecuzione sull'istanza database ed eseguire le ottimizzazioni dove necessario. Se le ottimizzazioni non migliorano le metriche dell'istanza e non mitigano l'esaurimento delle risorse, valutare la possibilità di aumentare l'istanza database prima che raggiunga i suoi limiti. Per ulteriori informazioni sulle classi di istanza database disponibili e le relative specifiche, consultare Classi di istanze DB Amazon Aurora.

Suggerimenti per 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 versione 2.12.1 e successive.

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

Le versioni precedenti di Aurora MySQL versione 2 ereditavano diversi problemi relativi alla replica multithread da MySQL Community Edition. 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 MySQL, vedere. Replica di log binari multithread

Richiamo di AWS Lambda funzioni utilizzando 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 innodb_flush_log_at_trx_commit a un valore non predefinito può aiutare a ridurre la latenza del linguaggio DML (Data Manipulation Language), ma compromette la durabilità dei record di registro. 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 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 MySQL 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 MySQL versione 3 che avresti con il valore impostato su 0 o 2 in Aurora MySQL versione 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 passare innodb_flush_log_at_trx_commit a un valore diverso da 1, è necessario modificare il valore da 1 a 1. innodb_trx_commit_allow_data_loss In questo modo, riconosci il 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.

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

  • Amazon CloudWatch Logs: con CloudWatch Logs puoi visualizzare metriche, analizzare i dati di log e creare allarmi in tempo reale. Per ulteriori informazioni, consulta Monitoraggio degli errori in Amazon Aurora MySQL e Amazon RDS for MySQL tramite Amazon e invio di notifiche tramite Amazon SNS. CloudWatch

    Utilizzando CloudWatch Logs with innodb_print_all_deadlocks turned on, puoi configurare allarmi per avvisarti 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 My SQL.