DBA SQL Scripts

SQL per DBA MySQL

Database GURU Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script SQL piu' utili ed interessanti per la gestione di un database MySQL.

MySQL e' il piu' noto RDBMS Open Source sopratutto per le applicazioni Web ed il SQL e' semplice ed efficace.

Gli esempi riportati riguardano: Sessioni, Lock, Utilizzo di spazio, Performance, Replication, ...

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.
Informazioni di dettaglio si possono trovare nella relativa documentazione.

Introduzione

MySQL fornisce un SQL semplice e veloce per lo sviluppo di applicazioni in architettura LAMP. Lo sviluppo di MySQL e' stato guidato dagli utenti: le prime release hanno privilegiato l'implementazione dei comandi piu' utili senza seguire in modo rigido quanto definito dagli standard o quanto implementato su altre RDBMS. Nelle versioni piu' recenti sono stati trattati anche tali punti ottenendo cosi' un SQL completo anche se il data dictionary di MySQL e' sicuramente meno completo e fornisce meno informazioni rispetto a quello di altri RDBMS.

In questa pagina faremo una scelta sugli statement SQL piu' utili ed interessanti per il DBA MySQL.

Sessioni

Una prima visione sull'utilizzo di una base dati e' quella delle connessioni presenti. Dal punto di vista del sistema operativo le sessioni connesse alla base dati non sono evidenti poiche' MySQL utilizza un solo processo ed un thread per ogni connessione.
Ecco come ottenere l'elenco delle sessioni presenti sulla base dati:

SHOW PROCESSLIST; select id, user,time,state,info,command from processlist order by id;

Il primo comando e' quello storico disponibile prima che esistesse l'INFORMATION_SCHEMA, ma e' sempre molto utilizzato!

Lock

Gli oggetti sulla base dati vengono protetti dall'utilizzo concorrente in MySQL mediante la gestione dei lock. In qualche caso possono verificarsi blocchi dovuti a sessioni che non rilasciano i lock e non permettono la modifica dei dati ad altre sessioni.
L'analisi dei lock con MySQL e' particolare poiche' dipende dall'Engine utilizzato e dal tipo di lock. Con InnoDB si utilizza il comando:
 SHOW ENGINE INNODB STATUS \G
e poi si studia in dettaglio il complesso output ottenuto. I lock attivi a livello di tabella si ottengono con show open tables where In_Use > 0; ma in qualche caso si cerca cerca poi il colpevole scegliendo la sessione giusta da un SHOW FULL PROCESSLIST; [NdA a volte la scelta della sessione giusta e'... a caso fino a che non si sblocca]. Per fortuna dalla versione 5.5 e' disponibile una vista sull'INFORMATION SCHEMA!
Ecco come individuare i lock sospensivi InnoDB e come risolverli:

select requesting_trx_id, requested_lock_id, blocking_trx_id ,blocking_lock_id from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; KILL trx_id;

Nota: prima di ammazzare qualcuno... e' importante essere certi di quello che si sta facendo!
Per questo sono utili anche le query: 

SELECT p.id, p.user, p.state,
       r.trx_id trx_id, r.trx_mysql_thread_id thread, r.trx_query query,
       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query
  FROM information_schema.processlist p, information_schema.innodb_lock_waits w,
       information_schema.innodb_trx b, information_schema.innodb_trx r
 WHERE b.trx_id = w.blocking_trx_id 
   AND r.trx_id = w.requesting_trx_id
   AND p.id=r.trx_mysql_thread_id;

SELECT r.trx_mysql_thread_id waiting_ID,
       r.trx_query waiting_query,
       b.trx_mysql_thread_id blocking_ID,
       b.trx_query blocking_query,
       r.trx_id waiting_trx,
       b.trx_id blocking_trx
  FROM information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b
          ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r
          ON r.trx_id = w.requesting_trx_id;

Utilizzo di spazio

In MySQL la gestione dello spazio dipende dall'Engine utilizzato.
Con l'Engine MyISAM viene utilizzato un file per i dati della tabella ed un file per gli indici.
Con l'Engine InnoDB i dati di tutte le tabelle ed indici vengono mantenuti su uno o piu' file preallocati gestiti dall'Engine. Le tabelle sono clusterizzate per chiave primaria e vengono mantenute piu' versioni (MVCC) per consentire la lettura consistente dei dati ed i rollback.
Ecco un esempio:

select table_schema, format(sum(table_rows),0) numero_righe, format(sum(data_length+index_length),0) spazio, format(sum(data_length),0) spazio_dati, format(sum(index_length),0) spazio_indici, format(sum((data_length+index_length)* if(engine='MyISAM',1,0)),0) spazio_MyISAM, format(sum((data_length+index_length)* if(engine='InnoDB',1,0)),0) spazio_InnoDB from information_schema.tables group by table_schema with rollup; select count(*), format(data_free/(1024*1024),0) from tables where engine='InnoDB' group by data_free order by data_free desc limit 8;

La prima query riporta lo spazio per schema suddividendolo per tabelle/indici e per i due Engine principali.
La seconda query riporta lo spazio libero, per tabella, sugli oggetti definiti sull'Engine InnoDB. Le tabelle con maggior spazio libero possono essere oggetto di manutenzione (eg. comando OPTIMIZE).

Le versioni piu' recenti di MySQL utilizzano per default l'Engine InnoDB con tablespace file-per-table, questo rende piu' semplice la gestione dello spazio [NdA considerato il fatto che InnoDB praticamente non libera mai lo spazio].
Sono anche disponibili viste piu' precise sullo spazio occupato:

select SUBSTRING_INDEX(name,'/',1), sum(FILE_SIZE), sum(ALLOCATED_SIZE) from information_schema.INNODB_SYS_TABLESPACES group by SUBSTRING_INDEX(name,'/',1) with rollup;

INNODB_SYS_TABLESPACES e' disponibile nella versione 5.7 di MySQL e dalla versione 10.0 di MariaDB; e' stata rinominata in INNODB_TABLESPACES dalla versione 8.0 di MySQL.

Performance

L'ottimizzazione delle performance e' il leitmotiv di ogni DBA. I punti di vista sono moltissimi: individuare gli statement SQL piu' pesanti, ottimizzare i piani di esecuzione degli statement, determinare eventuali bottleneck, effettuare il tuning sui parametri di configurazione, ...

Per la gestione un database MySQL non fornisce molti strumenti e solo le versioni piu' recenti hanno introdotto una serie di viste che consentono di analizzare con maggior dettaglio le attivita' presenti.
Ecco qualche query di esempio:

select id, user,time,state,info,command from processlist where command <> 'Sleep' order by id; select trx_mysql_thread_id, trx_id, trx_state, trx_started, trx_weight, trx_requested_lock_id, trx_query, trx_operation_state, trx_isolation_level from INFORMATION_SCHEMA.innodb_trx; SELECT THREAD_ID, NUMBER_OF_BYTES FROM events_waits_history WHERE EVENT_NAME LIKE 'wait/io/file/%' AND NUMBER_OF_BYTES IS NOT NULL; select variable_name, variable_value from global_variables where lower(variable_name)='query_cache_type' union select variable_name, format(variable_value,0) from global_variables where lower(variable_name) in ('innodb_flush_log_at_trx_commit', 'innodb_buffer_pool_size','query_cache_size', 'innodb_additional_mem_pool_size','innodb_log_file_size', 'innodb_log_buffer_size','innodb_log_files_in_group', 'innodb_lock_wait_timeout','innodb_thread_concurrency', 'binlog_cache_size','max_connections', 'skip-external-locking','read_buffer_size', 'sort_buffer_size','key_buffer_size', 'table_open_cache','wait_timeout','foo') order by variable_name;

La prima query riporta gli statement attivi: sono quelli su cui tipicamente si agisce nell'analisi delle prestazioni.
La seconda query riporta tutti gli elementi piu' significativi delle transazioni attive sul sistema. Ma attenzione: funziona solo dalla versione 5.5 di MySQL e con l'Engine InnoDB.
L'esempio successivo utilizza il PERFORMANCE schema, funzionalita' opzionale attivabile dalla versione 5.5 e generalmente presente di default nelle versioni successive, per analizzare l'I/O dei singoli thread di connessione alla base dati.
L'ultima query riporta alcuni parametri di configurazione di MYSQL scelti tra quelli piu' significativi per l'ottimizzazione delle prestazioni.

Ma la mia query preferita, quando disponibile il PERFORMANCE SCHEMA, e' sulla vista performance_schema.events_statements_summary_by_digest order by SUM_TIMER_WAIT desc.

Replication

La replicazione dei dati con MySQL e' facile da implementare e da gestire: per questo e' sfruttata in moltissimi database di produzione.
Ecco come controllare lo stato della replicazione:

show master status; show binary logs; show slave status\G

Nelle ultime versioni di MySQL sono disponibili anche query SQL... da sempre si utilizzano i comandi di SHOW.
I primi comandi si utilizzano sul Master. La cosa piu' importante da controllare? Non far scoppiare il file system con i binlog!
L'opzione /G sullo slave serve per ottenere l'output in forma tabellare. Nel caso dello slave e' necessario controllare che siano attivi lo Slave_IO (che si occupa di raccogliere i binlog dal Master) e lo Slave_SQL (che si occupa di applicare i binlog sullo Slave).
Un dato molto utile e' anche il ritardo della replica [NdA Seconds_Behind_Master] che e' sempre stato molto controverso.

Cambiano le versioni e lo Slave_SQL puo' lavorare in multithreading, come ottenere una stima del lag piu' affidabile? 

SELECT coalesce(max(PROCESSLIST_TIME), 0) replication_lag
  FROM performance_schema.threads
 WHERE NAME = 'thread/sql/slave_sql'
    OR (NAME = 'thread/sql/slave_worker'
        AND (PROCESSLIST_STATE IS NULL
             OR PROCESSLIST_STATE != 'Waiting for an event from Coordinator'));

Ulteriori informazioni

Un'introduzione ad MySQL si trova in MySQL. Maggiori dettagli tecnici sulle diverse versioni di MySQL e le date di rilascio di ogni versione sono riportate in questo documento.
Il sito MySQL ufficiale contiene tutta la documentazione ufficiale.

Volete leggere altre pagine come questa? Provate qui!

Titolo: SQL4DBA - SQL per DBA MySQL
Livello: Esperto (4/5)
Data: 15 Agosto 2013
Versione: 1.0.1 - 14 Febbraio 2018 ❤️ San Valentino
Autore: mail [AT] meo.bogliolo.name