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

Le migliori pratiche per Aurora My: SQL prestazioni e scalabilità

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

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

Amazon Aurora Le mie SQL istanze che utilizzano le classi di istanze db.t2db.t3, o db.t4g DB sono più adatte per applicazioni che non supportano un carico di lavoro elevato per un periodo di tempo prolungato. Le istanze T sono progettate per offrire prestazioni di base moderate e garantire prestazioni notevolmente maggiori se il carico di lavoro lo richiede. Sono destinate a carichi di lavoro che non vengono utilizzati completamente CPU spesso o costantemente, ma che a volte devono esplodere. 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 My SQL Performance Schema sulle istanze My SQL T di Amazon Aurora. In caso contrario, è possibile che la memoria disponibile per l'istanza T potrebbe esaurirsi.

Quando si utilizza un'istanza T come istanza DB in un cluster Aurora My SQL DB, si consiglia quanto segue:

Ottimizzazione delle query di join indicizzate di Aurora SQL My con prefetch a chiave asincrona

Aurora My SQL può utilizzare la funzionalità a chiave asincrona prefetch (AKP) per migliorare le prestazioni delle query che uniscono le tabelle tra gli indici. Questa funzionalità migliora le prestazioni anticipando le righe necessarie per eseguire le query in cui una JOIN query richiede l'uso dell'algoritmo Batched Key Access () Join e delle funzionalità di ottimizzazione Multi-Range Read (). BKA MRR Per ulteriori informazioni su BKA eMRR, vedete Block nested-loop e batched key access join e Multi-range read optimization nella mia documentazione. SQL

Per sfruttare AKP questa funzionalità, una query deve utilizzare entrambi e. BKA MRR In genere, tale query si verifica quando la JOIN clausola di una query utilizza un indice secondario, ma richiede anche alcune colonne dell'indice primario. Ad esempio, è possibile utilizzare AKP quando una JOIN clausola rappresenta un equijoin sui valori di indice tra una piccola tabella esterna e una grande tabella interna e l'indice è altamente selettivo sulla tabella più grande. AKPcollabora BKA ed MRR esegue una ricerca dell'indice secondario rispetto a quello primario durante la valutazione della clausola. JOIN AKPidentifica le righe necessarie per eseguire l'interrogazione 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.

AKPè disponibile per Aurora My SQL versione 2.10 e successive e versione 3. Per ulteriori informazioni sulle SQL versioni di Aurora My, consulta. Aggiornamenti del motore di database per Amazon Aurora My SQL

Abilitazione del prefetch asincrono delle chiavi

È possibile abilitare la AKP funzionalità aurora_use_key_prefetch impostando una variabile My SQL server suon. Per impostazione predefinita, questo valore è impostato su on. Tuttavia, non AKP può essere abilitata finché non abiliti anche l'algoritmo BKA Join e disabiliti la MRR funzionalità basata sui costi. A tale scopo, è necessario impostare i seguenti valori per optimizer_switch una variabile My SQL server:

È attualmente possibile impostare questi valori solo a livello di sessione. L'esempio seguente illustra come impostare questi valori in modo che siano abilitati AKP per la sessione corrente SET eseguendo istruzioni.

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

Analogamente, è possibile utilizzare SET le istruzioni per disabilitare AKP l'algoritmo BKA Join e riattivare la MRR funzionalità basata sui costi per la sessione corrente, come illustrato 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 sugli switch di ottimizzazione batched_key_access e mrr_cost_based, vedere Ottimizzazioni commutabili nella documentazione personale. SQL

Ottimizzazione delle query per il prefetch asincrono delle chiavi

È possibile confermare se una query può sfruttare AKP la funzionalità. 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 AKP funzionalità si applica a una tabella utilizzata nella query, questa colonna include uno dei seguenti valori:

L'esempio seguente mostra l'utilizzo di EXPLAIN per visualizzare il piano di esecuzione di una query di cui è possibile trarre vantaggioAKP.

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 EXPLAIN di output, vedere Formato EXPLAIN di output esteso nella SQL documentazione personale.

Ottimizzazione delle query Aurora SQL My join di grandi dimensioni 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 My. SQL

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:

Le seguenti restrizioni si applicano agli hash join per Aurora My: SQL

Abilitazione di hash join

Per abilitare gli hash join:

Gli hash join sono attivati per impostazione predefinita in Aurora My SQL versione 3 e disattivati per impostazione predefinita in Aurora My versione 2. SQL L'esempio seguente illustra come abilitare gli hash join per Aurora My versione 3. SQL È 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';

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, Puoi farlo hash_join_cost_based impostando una variabile My SQL server su. off L'esempio seguente illustra come imporre all'ottimizzatore di scegliere un hash join.

mysql> SET optimizer_switch='hash_join_cost_based=off';

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:

L'esempio seguente mostra l'utilizzo di EXPLAIN per visualizzare il piano di esecuzione di una query 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 EXPLAIN output esteso, vedere Extended EXPLAIN Output Format nella documentazione del mio SQL prodotto.

In Aurora My SQL 2.08 e versioni successive, è possibile utilizzare i SQL suggerimenti per determinare se una query utilizza o meno l'hash join e quali tabelle utilizzare per i lati build e probe del join. Per informazioni dettagliate, consultare Aurora I miei suggerimenti SQL.

Utilizzo di Amazon Aurora per ridimensionare le letture per il tuo database My SQL

Puoi usare Amazon Aurora con la tua istanza My SQL DB per sfruttare le funzionalità di scalabilità di lettura di Amazon Aurora ed espandere il carico di lavoro di lettura per la tua istanza My DB. SQL Per usare Aurora per scalare in lettura la tua istanza My SQL DB, crea un cluster Aurora My SQL DB e rendilo una replica di lettura della tua istanza My DB. SQL Quindi connettiti al SQL cluster Aurora My per elaborare le query di lettura. Il database di origine può essere un'istanza RDS for My SQL DB o un SQL database My in esecuzione all'esterno di AmazonRDS. 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 suSYSTEM, ogni chiamata alla SQL funzione My che richiede un calcolo del fuso orario effettua una chiamata alla libreria di sistema. Quando si eseguono SQL istruzioni che restituiscono o modificano tali TIMESTAMP valori con una elevata contemporaneità, è possibile che si verifichino un aumento della latenza, del conflitto di blocchi e dell'utilizzo. CPU Per ulteriori informazioni, consulta time_zone nella documentazione personale. SQL

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.