I metadata lock sono le protezioni che regolano la modifica concorrente
alle strutture dati MySQL come le tabelle.
Sono molto importanti perche' molte operazioni di DDL in MySQL sono sospensive.
Dalla versione 5.7 e' possibile controllare in modo semplice
i metadata lock utilizzando la vista performance_schema.metadata_locks
ed in questo breve documento vediamo come.
Nota: i lock a livello di riga e di tabella che proteggono i dati, e non le strutture dati come i metadata lock, sono un altro argomento e non sono trattati in questa paginetta.
I metadata lock sono le protezioni che regolano la modifica concorrente agli oggetti MySQL come tabelle, indici e partizioni. In pratica regolano l'accesso alle tabelle quando vengono effettuati comandi di DDL come le ALTER TABLE e simili.
Non e' mai stato semplice con MySQL individuare i lock attivi...
come ben sa chi ha utilizzato il famigerato:
SHOW ENGINE INNODB STATUS \G
Il comando da' una risposta molto esaustiva...
ma certo di non facile lettura.
Per fortuna, versione dopo versione, sono state introdotti in MySQL strumenti
per il controllo sempre piu' sofisticati ed utili.
Dalla versione 5.7 e' possibile controllare in modo semplice
i metadata lock utilizzando una specifica vista.
La vista e' contenuta nel performance_schema
e si chiama metadata_locks.
Anche se la vista metadata_locks e' sempre presente in 5.7 per default e' vuota perche' non e' abilitata la collezione dei dati.
La configurazione del performace schema si effettua su due tabelle: setup_consumers e setup_instruments. Ecco i comandi da utilizzare:
use performance_schema; -- Non strettamente necessario: gia' abilitato per default in 5.7 UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation'; -- Da abilitare in 5.7, gia' abilitato per default in 8.0 UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
A questo punto sono abilitati gli eventi che consentono di popolare la vista metadata_locks.
Utilizzare la vista sui metadata locks e' facile:
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();
Utilizzando un join con i thread sono in questo modo visibili tutti i metadata lock attivi ed i dettagli sulle relative sessioni di database.
Volendo selezionare solo i lock in blocco basta aggiungere una condizione:
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID()
AND lock_status<>'GRANTED';
Analizzare i metadata lock e' effettivamente molto facile in 5.7!
Riprendiamo le tabelle di configurazione del performance schema...
Prima la setup_consumers:
mysql> select * from setup_consumers; +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | | events_transactions_current | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
Quindi la setup_instruments:
mysql> select * from setup_instruments; +--------------------------------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +--------------------------------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | NO | NO | ... | wait/synch/cond/myisam/keycache_thread_var::suspend | NO | NO | | wait/io/file/sql/map | YES | YES | | wait/io/file/sql/binlog | YES | YES | | wait/io/file/sql/binlog_cache | YES | YES | ... | wait/io/table/sql/handler | YES | YES | | wait/lock/table/sql/handler | YES | YES | | stage/sql/After create | NO | NO | | stage/sql/allocating local table | NO | NO | ... | wait/io/socket/sql/client_connection | NO | NO | | idle | YES | YES | | memory/performance_schema/mutex_instances | YES | NO | | memory/performance_schema/rwlock_instances | YES | NO | ... | wait/lock/metadata/sql/mdl | YES | NO | +--------------------------------------------------------------------------------+---------+-------+ 1021 rows in set (0.00 sec)
Il numero di righe presenti nelle tabelle setup_consumers e setup_instruments
dipendono dalle versioni di MySQL e dalla configurazione presente.
Ad esempio in setup_consumers vi sono 12 righe in 5.6 e 15 in 5.7
mentre in setup_instruments vi sono 554 rows in 5.6 ed oltre 1000 in 5.7.
Importante: anche se tecnicamente e' possibile abilitare ogni possibile
controllo impostando a YES le colonne ENABLED e TIMED
questo comporterebbe un significativo degrado prestazionale.
Le impostazioni di default sono un buon compromesso
che consente un monitoraggio dettagliato della base dati
con un impatto quasi non rilevabile sulle prestazioni.
In caso di problemi e' possibile attivare ulteriori consumer ed instrument
ma, una volta eseguita la diagnosi, conviene reimpostarli al valore di default.
Su MySQL e sul fork Percona il performance schema e' abilitato per default. Sul fork MariaDB ed i vari servizi MySQL Amazon RDS per default il performance schema non e' abilitato.
A mio avviso l'abilitazione del performance schema e la configurazione suggerita in questa pagina (abilitazione di wait/lock/metadata/sql/mdl) non ha un impatto significativo e deve essere mantenuta negli ambienti di produzione. In effetti nella versione MySQL 8.0 e' stata poi inserita tra i default.
Le differenti versioni di MySQL sono riportate in questo documento.
Titolo: MySQL Metadata Locks
Livello: Esperto
Data:
14 Febbraio 2018
Versione: 1.0.0 - 14 Febbraio 2018 ❤️ San Valentino
Autore: mail [AT] meo.bogliolo.name