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

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

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:

È 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:

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:

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

Abilitazione di hash join

Per abilitare gli hash join:

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';

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';

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:

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, consultare 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.

Errori di overflow dell'ID dell'indice virtuale

Aurora MySQL limita i valori per l'indice virtuale IDs a 8 bit per prevenire un problema causato dal formato di annullamento in MySQL. Se un indice supera il limite di ID dell'indice virtuale, il cluster potrebbe non essere disponibile. Quando un indice si avvicina al limite di ID dell'indice virtuale o quando si tenta di creare un indice superiore al limite di ID dell'indice virtuale, RDS potrebbe generare un codice di errore 63955 o un codice di avviso. 63955 Per risolvere un errore relativo al limite degli ID dell'indice virtuale, ti consigliamo di ricreare il database con un dump logico e ripristinarlo.

Per ulteriori informazioni sul dump e il ripristino logici per Amazon Aurora MySQL, consulta Migrare database di grandi dimensioni su Amazon Aurora MySQL usando and. MyDumper MyLoader Per ulteriori informazioni sull'accesso ai log degli errori in Amazon Aurora, consulta. Monitoraggio dei file di log di Aurora