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

Breve descrizione

Gli eventi di attesa SYNCH di MySQL in Performance Insights si verificano quando più sessioni di database accedono agli stessi oggetti o strutture di memoria protetti. In Amazon Relational Database Service (Amazon RDS) per MySQL, Amazon RDS per MariaDB e Amazon Aurora compatibile con MySQL sono protetti i seguenti oggetti:

• Il file di log binario attivo in un'istanza di origine binlog contenente un mutex che consente a una sola sessione alla volta di leggerlo o scriverlo.
• Il dizionario dati protetto per le scritture di istruzioni DCL (Data Control Language) o DDL (Data Definition Language).
• L'indice hash adattivo contenente un mutex che consente a una sola sessione alla volta di leggerlo o scriverlo.
• La cache delle tabelle aperte che consente a una sola sessione di aggiungere o rimuovere una tabella dalla cache.
• Ogni blocco di database nel pool di buffer InnoDB in cui solo una sessione alla volta può modificare il contenuto di un blocco in memoria.

Risoluzione

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

Un numero elevato di sessioni in attesa di eventi SYNCH causa un elevato utilizzo della CPU. Al 100% di utilizzo, il numero di sessioni di attesa aumenta. Per risolvere il problema, aumenta le dimensioni dell'istanza database in modo che la CPU sia sufficiente per elaborare il carico di lavoro aggiuntivo.

Poiché gli eventi SYNCH in genere non durano a lungo, la metrica CPUUtilization di Amazon CloudWatch potrebbe non mostrare correttamente il picco di utilizzo. Questa metrica ha una granularità di soli 60 secondi. Per verificare i picchi di utilizzo, utilizza i contatori con granularità di un secondo di Monitoraggio avanzato nella console Amazon RDS.

Aumenta l'array di attesa mutex o lock di MySQL

MySQL utilizza una struttura dati interna per coordinare i thread con una dimensione di array predefinita di 1. Questa configurazione funziona per computer con una sola CPU, ma può causare problemi su computer con diverse CPU. Se il carico di lavoro presenta un numero elevato di thread in attesa, aumenta la dimensione dell'array. Modifica il parametro innodb_sync_array_size di MySQL in modo che sia uguale o superiore al numero di CPU. Per ulteriori informazioni, consulta innodb_sync_array_size sul sito web di MySQL.

Nota: MySQL utilizza il parametro innodb_sync_array_size solamente all'avvio del database.

Riduci la concorrenza

In generale, il parallelismo migliora il throughput. Tuttavia, quando un numero elevato di sessioni esegue attività uguali o simili, le sessioni devono avere accesso agli stessi oggetti protetti. Maggiore è la quantità di sessioni, maggiore è la CPU utilizzata durante l'attesa.

Per risolvere il problema, distribuisci le attività nel tempo o pianificale in serie. Inoltre, puoi raggruppare diverse operazioni in un'unica istruzione come nel caso degli inserimenti a più righe.

Risolvi il problema in base agli eventi di attesa

Applica il seguente procedimento di risoluzione in base all'evento di attesa che ricevi.

synch/rwlock/innodb/dict sys RW lock o synch/rwlock/innodb/dict_operation_lock

Questi eventi si verificano quando invochi contemporaneamente un numero elevato di istruzioni DCL o DDL simultanee. Per risolvere il problema, riduci la dipendenza dell'applicazione dalle istruzioni DDL durante la normale attività dell'applicazione.

synch/cond/sql/MDL_context::COND_wait_status

Questo evento si verifica quando un numero elevato di SQL, incluse le selezioni, accede a una tabella che un'istruzione DCL o DDL sta modificando. Per risolvere il problema, non 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

Questi eventi si verificano quando il numero di tabelle aperte dalle sessioni supera le dimensioni della cache delle definizioni delle tabelle o della cache delle tabelle aperte. Per risolvere il problema, aumenta la dimensione delle cache. Per ulteriori informazioni, consulta 10.4.3.1 How MySQL opens and closes tables (10.4.3.1 Come MySQL apre e chiude le tabelle) sul sito web di MySQL.

synch/mutex/sql/LOG

Questo evento si verifica quando il database esegue un numero elevato di istruzioni che i metodi di registrazione correnti non sono in grado di supportare. Se utilizzi il metodo di output TABLE, scegli piuttosto FILE. Se utilizzi il log generale, scegli piuttosto l'audit avanzato di Aurora. Se hai impostato il parametro long_query_time su 0 o su un valore inferiore a 1, aumenta il valore.

synch/mutex/innodb/buf_pool_mutex, synch/mutex/innodb/aurora_lock_thread_slot_futex o synch/rwlock/innodb/index_tree_rw_lock

Questi eventi si verificano quando un numero elevato di istruzioni DML (Data Manipulation Language) simili accede contemporaneamente allo stesso oggetto del database. Per risolvere il problema, utilizza istruzioni e partizioni a più righe per distribuire il carico di lavoro su diversi oggetti del database.

synch/mutex/innodb/aurora_lock_thread_slot_futex

Questo evento si verifica quando una sessione blocca una riga per un aggiornamento e un'altra sessione tenta di aggiornare la riga. Risolvi il problema in base agli altri eventi di attesa che ricevi. Individua e rispondi alle istruzioni SQL responsabili dell'evento di attesa oppure individua e rispondi alla sessione bloccante.

synch/cond/sql/MYSQL_BIN_LOG::COND_done, synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit o synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log

Questi eventi si verificano quando si attiva la registrazione binaria ed è presente un grande volume di throughput di commit, transazioni di commit o repliche che leggono i binlog. Per risolvere il problema, utilizza istruzioni a più righe o raggruppa più istruzioni in un'unica transazione.

Per ulteriori informazioni sugli eventi di attesa di Aurora compatibile con MySQL, consulta Regolazione di Aurora MySQL con eventi di attesa e Eventi di attesa di Aurora MySQL.

È consigliabile aggiornare il database a una versione principale compatibile con 8.0 o versioni successive. Quando possibile, utilizza istruzioni a più righe o raggruppa più istruzioni in un'unica transazione. In Aurora, utilizza il database globale di Aurora invece della replica binaria del log o utilizza i parametri aurora_binlog.

Informazioni correlate

Monitoraggio del carico del database con Performance Insights su Amazon RDS

Gruppi di parametri per Amazon RDS