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.
Indice
- Determinazione dell'istanza database a cui si è connessi
- Best practice per le prestazioni e il dimensionamento di Aurora MySQL
- Utilizzo delle classi di istanza T per lo sviluppo e i test
- Ottimizzazione delle query di join indicizzate Aurora MySQL con prefetch asincrono delle chiavi
- Ottimizzazione di grandi query di join Aurora MySQL con hash join
- Utilizzo di Amazon Aurora per dimensionare le letture per il database MySQL
- Ottimizzazione delle operazioni di timestamp
- Best practice per l'elevata disponibilità di Aurora MySQL
- Suggerimenti per Aurora MySQL
- Utilizzo della replica multithread in Aurora MySQL
- Richiamo di AWS Lambda funzioni utilizzando funzioni MySQL native
- Evitatre le transazioni XA con Amazon Aurora MySQL
- Mantenere le chiavi esterne attivate durante le istruzioni DML
- Configurazione della frequenza di svuotamento del buffer dei registri
- Contenimento e risoluzione dei problemi di deadlock di Aurora MySQL
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.
Argomenti
- Utilizzo delle classi di istanza T per lo sviluppo e i test
- Ottimizzazione delle query di join indicizzate Aurora MySQL con prefetch asincrono delle chiavi
- Ottimizzazione di grandi query di join Aurora MySQL con hash join
- Utilizzo di Amazon Aurora per dimensionare le letture per il database MySQL
- Ottimizzazione delle operazioni di timestamp
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 usaredb.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
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
suon
. Questo valore controlla l'utilizzo dell'algoritmo di join BKA. Per impostazione predefinita, questo valore è impostato suoff
. Imposta
mrr_cost_based
suoff
. Questo valore controlla l'utilizzo della funzionalità MRR basata sui costi. Per impostazione predefinita, questo valore è impostato suon
.
È 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
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
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
ebit
. -
È possibile confrontare qualsiasi elemento nella categoria dei tipi di dati numerici di approssimazione, ad esempio
float
edouble
. -
È 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
su0
. La disattivazione diaurora_disable_hash_join
imposta il valore dioptimizer_switch
suhash_join=on
. -
Aurora MySQL versione 3: imposta il parametro del server MySQL
optimizer_switch
sublock_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
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
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.
Argomenti
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.
Argomenti
- Utilizzo della replica multithread in Aurora MySQL
- Richiamo di AWS Lambda funzioni utilizzando funzioni MySQL native
- Evitatre le transazioni XA con Amazon Aurora MySQL
- Mantenere le chiavi esterne attivate durante le istruzioni DML
- Configurazione della frequenza di svuotamento del buffer dei registri
- Contenimento e risoluzione dei problemi di deadlock di Aurora MySQL
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
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:
-
Imposta
foreign_key_checks
su0
. -
Apportare le modifiche alle istruzioni DML (Data Manipulation Language).
-
Assicurarsi che le modifiche apportate non violino eventuali vincoli delle chiavi esterne.
-
Impostare
foreign_key_checks
su1
(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
su0
come parte della variabileinit_connect
. -
Se un ripristino da un backup logico come
mysqldump
non riesce o è incompleto, assicurarsi cheforeign_key_checks
sia impostato su1
prima di avviare eventuali altre operazioni nella stessa sessione. Un backup logico impostaforeign_key_checks
su0
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
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
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
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
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
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
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
-
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:
-
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.
-
Invece di usare
REPLACE INTO
, riscrivi l'istruzione SQL come transazione a più istruzioni come la seguente:BEGIN; DELETE
conflicting rows
; INSERTnew rows
; COMMIT; -
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
; UPDATEconflicting rows
; INSERTnew rows
; COMMIT;
-
-
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.
-
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.
-
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.
-
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 transazioninella 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
-
Istruzione
SHOW ENGINE
: l'istruzioneSHOW ENGINE INNODB STATUS \G
contiene i dettaglidel 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
einnodb_lock_wait_timeout
. Per ulteriori informazioni su tali parametri, consulta Contatori non nativi per Aurora My SQL.