Perché la mia istanza database MySQL mostra un numero elevato di sessioni attive in attesa degli eventi di attesa SYNCH in Performance Insights?

Breve descrizione

Performance Insights è attivato su uno dei seguenti servizi:

• Amazon Relational Database Service (Amazon RDS) per MySQL.
• Amazon RDS per MariaDB.
• Edizione compatibile con Amazon Aurora MySQL.

Se vedi gli eventi di attesa di MySQL SYNCH in Performance Insights, significa che un gran numero di sessioni nel database sta tentando di accedere agli stessi oggetti protetti o alle stesse strutture di memoria. Gli oggetti protetti in MySQL includono quanto segue:

• Il file di log binario attivo in un'istanza sorgente di binlog: contiene un mutex che consente a una sola sessione di leggerlo o scriverlo alla volta.
• Il dizionario dei dati: per le scritture che di solito sono causate da istruzioni del linguaggio di controllo dei dati (DCL) o del linguaggio di definizione dei dati (DDL).
• L'indice hash adattivo: contiene un mutex che consente a una sola sessione di leggerlo o scriverlo alla volta.
• La cache della tabella aperta: solo una sessione può aggiungere o rimuovere una tabella dalla cache.
• Ogni singolo blocco del database all'interno dell'InnoDB Buffer Pool: solo una sessione può modificare il contenuto di un blocco in memoria alla volta.

Risoluzione

Assicurati che l'istanza database disponga di risorse CPU sufficienti per gestire il carico di lavoro

Se hai un numero elevato di sessioni in attesa di eventi SYNCH, ciò causa un elevato utilizzo della CPU. Se l'utilizzo raggiunge il 100%, il numero di sessioni di attesa aumenta. Durante la risoluzione dei problemi, aumenta le dimensioni dell'istanza database per assicurarti che la CPU sia sufficiente per elaborare il carico di lavoro aggiuntivo.

Poiché questi eventi sono generalmente di breve durata, la metrica di utilizzo della CPU di Amazon CloudWatch potrebbe non mostrare correttamente l'utilizzo di picco. Il modo migliore per verificarlo consiste nell'utilizzare i contatori della CPU da un secondo in RDS Monitoraggio migliorato. Questi contatori sono più specifici e dettagliati.

Aumenta l'array di attesa mutex/lock di MySQL

MySQL utilizza una struttura dati interna per coordinare i thread. Per impostazione predefinita, questo array ha una dimensione pari a uno. È adatto per macchine con CPU singola, ma può causare problemi su macchine con più CPU. Se il carico di lavoro presenta un numero elevato di thread in attesa, aumenta la dimensione dell'array. Imposta il parametro MYSQL innodb_sync_array_size sulla quantità di CPU (o superiore, fino a 1024).

Nota: il parametro innodb_sync_array_size si applica solo all'avvio del database.

Ridurre la concorrenza

In generale, il parallelismo aiuta a migliorare la produttività. Tuttavia, quando un numero elevato di sessioni tenta di eseguire attività uguali o simili, le sessioni devono accedere agli stessi oggetti protetti. Maggiore è il numero di sessioni, maggiore è la CPU utilizzata durante l'attesa.

Distribuisci queste attività nel tempo o programmale in serie. È inoltre possibile raggruppare diverse operazioni in un'unica istruzione, ad esempio gli inserti a più righe.

Esamina eventi di attesa specifici

Usa i seguenti esempi per risolvere il tuo specifico evento di attesa. Per ulteriori informazioni sugli eventi di attesa di Aurora MySQL, consulta Perfezionare Aurora MySQL con eventi di attesa.

• synch/rwlock/innodb/dict sys RW lock, O
  synch/rwlock/innodb/dict_operation_lock: indica che viene attivato un numero elevato di DDL nello stesso momento. Riduci la dipendenza dell'applicazione dall'uso dei DDL durante la normale attività dell'applicazione.
• synch/cond/sql/MDL_context::COND_wait_status: indica un numero elevato di SQL (incluse le selezioni) che cercano di accedere a una tabella modificata da un DCL o DDL. Evita di eseguire istruzioni DDL su tabelle ad alto traffico durante la normale attività dell'applicazione.
• synch/mutex/sql/LOCK_open, O
  synch/mutex/sql/LOCK_table_cache: indica che il numero di tabelle che le sessioni stanno aprendo supera la dimensione della cache delle definizioni delle tabelle o della cache aperta della tabella. Aumenta la dimensione di queste cache.
• synch/mutex/sql/LOG: il tuo database potrebbe eseguire un gran numero di istruzioni e gli attuali metodi di registrazione non sono in grado di supportarlo. Se usi il metodo di output TABELLA, prova invece a utilizzare FILE. Se utilizzi il log generale, utilizza invece l'audit avanzato di Amazon Aurora. Se usi 0 o meno di 1 per il parametro long_query_time, prova ad aumentarlo.
• synch/mutex/innodb/buf_pool_mutex, O synch/mutex/innodb/aurora_lock_thread_slot_futex, O synch/rwlock/innodb/index_tree_rw_lock: un gran numero di DML simili accede contemporaneamente allo stesso oggetto del database. Utilizza istruzioni a più righe e utilizza il partizionamento per distribuire il carico di lavoro su diversi oggetti del database.
• synch/mutex/innodb/aurora_lock_thread_slot_futex: si verifica quando una sessione ha bloccato una riga per un aggiornamento e quindi un'altra sessione tenta di aggiornare la stessa riga. La tua azione dipende dagli altri eventi di attesa che vedi. Trova e rispondi alle istruzioni SQL responsabili di questo evento di attesa oppure trova e rispondi alla sessione di blocco.
• synch/cond/sql/MYSQL_BIN_LOG::COND_done, O
  synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit, O
  synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log: la registrazione binaria è stata attivata e potrebbe essere una delle seguenti:

                         : un'elevata velocità di trasmissione effettiva di commit.

                         : un gran numero di transazioni in corso.

                         : repliche che leggono i binlog.

                         : una combinazione di questi.

Valuta la possibilità di aggiornare il database a una versione principale compatibile con 5.7 o superiore. Inoltre, utilizza istruzioni a più righe o raggruppa più istruzioni in un'unica transazione. In Amazon Aurora, utilizza i database globali anziché la replica dei log binari oppure utilizza i parametri aurora_binlog.

---

Informazioni correlate

Utilizzare gli approfondimenti sulle prestazioni di Amazon RDS

Utilizzare i gruppi di parametri del database

Eventi Aurora MySQL