Monitoraggio MySQL performance metrics

author
16 minutes, 28 seconds Read

Questo post è parte 1 di una serie in 3 parti sul monitoraggio MySQL. La parte 2 riguarda la raccolta di metriche da MySQL e la parte 3 spiega come monitorare MySQL utilizzando Datadog.

Che cos’è MySQL?

MySQL è il server di database relazionale open source più popolare al mondo. Di proprietà di Oracle, MySQL è disponibile nella Community Edition liberamente scaricabile e nelle edizioni commerciali con funzionalità e supporto aggiunti. Inizialmente rilasciato nel 1995, MySQL da allora ha generato fork di alto profilo per tecnologie concorrenti come MariaDB e Percona.

Statistiche MySQL chiave

Se il database è in esecuzione lentamente, o non riesce a servire le query per qualsiasi motivo, ogni parte dello stack che dipende da quel database subirà anche problemi di prestazioni. Al fine di mantenere il database senza intoppi, è possibile monitorare attivamente le metriche che coprono quattro aree di prestazioni e utilizzo delle risorse:

  • throughput delle query
  • Prestazioni di esecuzione delle query
  • Connessioni
  • Utilizzo del pool di buffer

Gli utenti MySQL possono accedere a centinaia di metriche dal database, quindi in questo articolo ci concentreremo su una manciata di metriche chiave che ti permetteranno di ottenere informazioni in tempo reale sullo stato e le prestazioni del tuo database. Nella seconda parte di questa serie vi mostreremo come accedere e raccogliere tutte queste metriche.

Questo articolo fa riferimento alla terminologia metrica introdotta nella nostra serie Monitoring 101, che fornisce un framework per la raccolta e l’avviso delle metriche.

Compatibilità tra versioni e tecnologie

Alcune delle strategie di monitoraggio discusse in questa serie sono specifiche per le versioni MySQL 5.6 e 5.7. Le differenze tra queste versioni saranno evidenziate lungo la strada.

La maggior parte delle metriche e delle strategie di monitoraggio descritte qui si applicano anche a tecnologie compatibili con MySQL come MariaDB e Percona Server, con alcune differenze notevoli. Ad esempio, alcune delle funzionalità di MySQL Workbench, che è dettagliato nella Parte 2 di questa serie, non sono compatibili con le versioni attualmente disponibili di MariaDB.

Gli utenti di Amazon RDS dovrebbero consultare le nostre guide di monitoraggio specializzate per MySQL su RDS e per Amazon Aurora compatibile con MySQL.

Query throughput

le query MySQL
Nome Descrizione tipo di Metrica Disponibilità
Domande Conteggio di istruzioni eseguite (inviato dal client) Lavoro: Throughput Server variabile di stato
Com_select SELECT Lavoro: Throughput Server variabile di stato
Scrive Inserimenti, aggiornamenti o elimina Lavoro: Throughput Calcolato dalle variabili di stato del server

La vostra preoccupazione principale nel monitoraggio di qualsiasi sistema è fare in modo che il suo lavoro è stato fatto in modo efficace. Il lavoro di un database sta eseguendo query, quindi la prima priorità di monitoraggio dovrebbe essere assicurarsi che MySQL esegua query come previsto.

MySQL ha un contatore interno (una” variabile di stato del server”, nel linguaggio MySQL) chiamato Questions, che viene incrementato per tutte le istruzioni inviate dalle applicazioni client. La vista centrata sul client fornita dalla metrica Questions rende spesso più facile l’interpretazione rispetto al contatore Queries correlato, che conta anche le istruzioni eseguite come parte di programmi memorizzati, nonché i comandi come PREPARE e DEALLOCATE PREPARE eseguiti come parte di istruzioni preparate sul lato server.

Per interrogare una variabile di stato del server come Questions o Com_select:

SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+

È inoltre possibile monitorare la ripartizione dei comandi di lettura e scrittura per comprendere meglio il carico di lavoro del database e identificare potenziali colli di bottiglia. Le query di lettura vengono generalmente catturate dalla metrica Com_select. Scrive incremento di una delle tre variabili di stato, a seconda del comando:

Scrive = Com_insert + Com_update + Com_delete

Metrica di avviso: Domande

L’attuale tasso di query, verrà naturalmente ascesa e la caduta, e, come tale, non è sempre una metrica strategica basata su soglie fisse. Ma vale la pena avvisare di improvvisi cambiamenti nel volume delle query: drastiche gocce di throughput, in particolare, possono indicare un problema serio.

le prestazioni delle Query

MySQL latenza grafico
Nome Descrizione tipo di Metrica Disponibilità
di esecuzione della Query tempo Medio di esecuzione, per schema Lavoro: Prestazioni Prestazioni di query dello schema
gli errori di Query Numero di istruzioni SQL che ha generato errori Lavoro: Errore Prestazioni di query dello schema
Slow_queries Numero di richieste superiore configurabile long_query_time limite Lavoro: Prestazioni Server variabile di stato

MySQL utenti hanno un numero di opzioni per il monitoraggio query di latenza, sia facendo uso di MySQL integrato di metriche e interrogando le prestazioni dello schema. Abilitate di default da MySQL 5.6.6, le tabelle del database performance_schema all’interno di MySQL memorizzano statistiche di basso livello sugli eventi del server e sull’esecuzione delle query.

Performance schema statement digest

Molte metriche chiave sono contenute nella tabella events_statements_summary_by_digest performance schema, che acquisisce informazioni su latenza, errori e volume di query per ogni istruzione normalizzata. Una riga di esempio della tabella mostra un’istruzione che è stata eseguita due volte e che ha richiesto in media 325 millisecondi per l’esecuzione (tutte le misurazioni del timer sono in picosecondi):

*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55

digest tabella normalizza tutte le dichiarazioni (come si è visto nel DIGEST_TEXT campo di cui sopra), ignorando i valori dei dati e standardizzazione di spazi e di capitalizzazione, in modo che le seguenti due query potrebbe essere considerato lo stesso:

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

Per estrarre un per-schema tempo medio di esecuzione in microsecondi, è possibile richiedere le prestazioni dello schema:

SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+

allo stesso modo, per contare il numero totale di istruzioni per schema che ha generato errori:

SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+

Lo schema sys

Interrogando lo schema delle prestazioni come mostrato sopra funziona alla grande per il recupero a livello di codice delle metriche dal database. Per query e indagini ad hoc, tuttavia, di solito è più facile utilizzare lo schema sys di MySQL. Lo schema sys fornisce un insieme organizzato di metriche in un formato più leggibile, rendendo le query corrispondenti molto più semplici. Ad esempio, per trovare le istruzioni più lente (quelle nel 95 ° percentile per runtime):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

O per vedere quali istruzioni normalizzate hanno generato errori:

SELECT * FROM sys.statements_with_errors_or_warnings;

Molti altri esempi utili sono dettagliati nella documentazione dello schema sys. Lo schema sys è incluso in MySQL a partire dalla versione 5.7.7, ma gli utenti di MySQL 5.6 possono installarlo con pochi comandi. Vedere la parte 2 di questa serie per le istruzioni.

Query lente

Oltre alla ricchezza di dati sulle prestazioni disponibili nello schema prestazioni e nello schema sys, MySQL dispone di un contatore Slow_queries, che aumenta ogni volta che il tempo di esecuzione di una query supera il numero di secondi specificato dal parametro long_query_time. La soglia è impostata su 10 secondi per impostazione predefinita:

SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

Il parametro long_query_time può essere regolato con un solo comando. Ad esempio, per impostare la soglia di query lenta su cinque secondi:

SET GLOBAL long_query_time = 5;

(Si noti che potrebbe essere necessario chiudere la sessione e riconnettersi al database per la modifica da applicare a livello di sessione.)

Indagare sui problemi di prestazioni delle query

Se le query vengono eseguite più lentamente del previsto, è spesso il caso che una query modificata di recente sia il colpevole. Se nessuna query è determinata a essere eccessivamente lenta, le prossime cose da valutare sono le metriche a livello di sistema per cercare i vincoli nelle risorse principali (CPU, I/O del disco, memoria e rete). Saturazione della CPU e I / O colli di bottiglia sono colpevoli comuni. Potresti anche voler controllare la metrica Innodb_row_lock_waits, che conta quanto spesso il motore di archiviazione InnoDB ha dovuto aspettare per acquisire un blocco su una particolare riga. InnoDB è stato il motore di archiviazione predefinito dalla versione 5.5 di MySQL e MySQL utilizza il blocco a livello di riga per le tabelle InnoDB.

Per aumentare la velocità delle operazioni di lettura e scrittura, molti utenti vorranno sintonizzare le dimensioni del pool di buffer utilizzato da InnoDB per memorizzare nella cache i dati della tabella e dell’indice. Ulteriori informazioni sul monitoraggio e il ridimensionamento del pool di buffer di seguito.

Metriche da avvisare su

  • Tempo di esecuzione della query: la gestione della latenza per i database chiave è fondamentale. Se il tempo di esecuzione medio per le query in un database di produzione inizia a salire, cercare vincoli di risorse sulle istanze del database, possibili contese per blocchi di righe o tabelle e modifiche nei modelli di query sul lato client.
  • Errori di query: Un improvviso aumento degli errori di query può indicare un problema con l’applicazione client o il database stesso. È possibile utilizzare lo schema sys per esplorare rapidamente quali query potrebbero causare problemi. Ad esempio, per elencare le 10 istruzioni normalizzate che hanno restituito il maggior numero di errori:
      SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;  
  • Slow_queries: Il modo in cui si definisce una query lenta (e quindi il modo in cui si configura il parametro long_query_time) dipende dal caso d’uso. Qualunque sia la tua definizione di “lento”, probabilmente vorrai indagare se il conteggio delle query lente supera i livelli di base. Per identificare le query effettive eseguite lentamente, è possibile interrogare lo schema sys o immergersi nel registro di query lento opzionale di MySQL, che è disabilitato per impostazione predefinita. Ulteriori informazioni sull’abilitazione e l’accesso al log delle query lente sono disponibili nella documentazione di MySQL.

Connessioni

connessioni MySQL
Nome Descrizione tipo di Metrica Disponibilità
Threads_connected connessioni attive Risorse: Utilizzo Server variabile di stato
Threads_running Attualmente in esecuzione connessioni Risorse: Utilizzo Server variabile di stato
Connection_errors_ interno Conteggio di connessioni rifiutato a causa di errore del server Risorsa: Errore Server variabile di stato
Aborted_connects Conteggio di falliti tentativi di connessione al server Risorse: Errore Server variabile di stato
Connection_errors_ max_connections Conteggio di connessioni rifiutato a causa di max_connections limite Risorsa: Errore Server variabile di stato

Controllo e regolazione del limite di connessione

Monitoraggio delle connessioni client è fondamentale, perché una volta che si sono esaurite le connessioni disponibili, le connessioni con il client rifiutato. Il limite di connessione MySQL è predefinito su 151, ma può essere verificato con una query:

SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+

MySQL documentazione suggerisce robusto server dovrebbe essere in grado di gestire le connessioni in alta centinaia o migliaia:

Linux o Solaris dovrebbe essere in grado di sostenere da 500 a 1000 connessioni simultanee di routine e più di 10.000 connessioni se si dispone di molti gigabyte di RAM disponibile e il carico di lavoro da ciascuno è basso o il tempo di risposta di destinazione senza pretese. Windows è limitato a (tabelle aperte × 2 + connessioni aperte) < 2048 a causa del livello di compatibilità Posix utilizzato su quella piattaforma.”

Il limite di connessione può essere regolato al volo:

SET GLOBAL max_connections = 200;

Tale impostazione tornerà al valore predefinito al riavvio del server. Per impostare in modo permanente il limite di connessione, aggiungi una riga come questa al file di configurazione my.cnf (vedi questo post per aiuto nel localizzare il file di configurazione):

max_connections = 200

Monitoraggio dell’utilizzo della connessione

MySQL espone una metrica Threads_connected che conta i thread di connessione: un thread per connessione. Monitorando questa metrica insieme al limite di connessione configurato, puoi assicurarti di avere una capacità sufficiente per gestire nuove connessioni. MySQL espone anche la metrica Threads_running per isolare quale di questi thread sta elaborando attivamente le query in un dato momento, al contrario delle connessioni aperte ma attualmente inattive.

Se il server raggiunge il limite max_connections, inizierà a rifiutare le connessioni. In tal caso, la metrica Connection_errors_max_connections verrà incrementata, così come la metrica Aborted_connects che tiene traccia di tutti i tentativi di connessione falliti.

MySQL espone una serie di altre metriche sugli errori di connessione, che possono aiutarti a indagare sui problemi di connessione. La metrica Connection_errors_internal è buona da guardare, perché viene incrementata solo quando l’errore proviene dal server stesso. Gli errori interni possono riflettere una condizione di memoria insufficiente o l’impossibilità del server di avviare un nuovo thread.

Metriche su cui avvisare

  • Threads_connected: Se un client tenta di connettersi a MySQL quando tutte le connessioni disponibili sono in uso, MySQL restituirà un errore “Troppe connessioni” e incrementerà Connection_errors_max_connections. Per evitare questo scenario, è necessario monitorare il numero di connessioni aperte e assicurarsi che rimanga al di sotto del limite max_connections configurato.
  • Aborted_connects: Se questo contatore è in aumento, i tuoi clienti stanno provando e non riescono a connettersi al database. Esaminare l’origine del problema con metriche di connessione a grana fine come Connection_errors_max_connections e Connection_errors_internal.

pool di Buffer di utilizzo

MySQL pool di buffer di utilizzo
Nome Descrizione tipo di Metrica Disponibilità
Innodb_buffer_pool_pages_total numero Totale di pagine nel pool di buffer Risorse: Utilizzo Server variabile di stato
pool di Buffer di utilizzo > Rapporto di usato per le pagine totali del pool di buffer Risorse: Utilizzo Calcolata da variabili di stato del server
Innodb_buffer_pool_read_requests Richieste per il pool di buffer Risorse: Utilizzo Server variabile di stato
Innodb_buffer_pool_reads Richieste del pool di buffer non potrebbe soddisfare Risorse: Saturazione Variabile di stato del server

Il motore di archiviazione predefinito di MySQL, InnoDB, utilizza un’area di memoria chiamata buffer pool per memorizzare nella cache i dati per tabelle e indici. Le metriche del pool di buffer sono metriche delle risorse rispetto alle metriche di lavoro e, in quanto tali, sono principalmente utili per indagare (piuttosto che rilevare) i problemi di prestazioni. Se le prestazioni del database iniziano a scorrere mentre l’I / O del disco è in aumento, l’espansione del pool di buffer può spesso fornire vantaggi.

Dimensionamento del pool di buffer

Il pool di buffer predefinito è di 128 mebibyte relativamente piccoli, ma MySQL consiglia di aumentarlo fino all ‘ 80% della memoria fisica su un server di database dedicato. MySQL aggiunge anche alcune note di cautela, tuttavia, poiché il sovraccarico di memoria di InnoDB può aumentare l’ingombro di memoria di circa il 10% oltre la dimensione del pool di buffer assegnato. E se si esaurisce la memoria fisica, il sistema ricorrerà al paging e le prestazioni ne risentiranno in modo significativo.

Il pool di buffer può anche essere diviso in regioni separate, note come istanze. L’utilizzo di più istanze può migliorare la concorrenza per i pool di buffer nell’intervallo multi-GiB.

Le operazioni di ridimensionamento del pool di buffer vengono eseguite in blocchi e la dimensione del pool di buffer deve essere impostata su un multiplo della dimensione del blocco per il numero di istanze:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

La dimensione del blocco è predefinita a 128 MiB ma è configurabile a partire da MySQL 5.7.5. Il valore di entrambi i parametri può essere controllato come segue:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

Se la query innodb_buffer_pool_chunk_size non restituisce risultati, il parametro non è sintonizzabile nella versione di MySQL e si può presumere che sia 128 MiB.

Per impostare la dimensione del pool di buffer e il numero di istanze all’avvio del server:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

A partire da MySQL 5.7.5, è anche possibile ridimensionare il pool di buffer al volo tramite un comando SET che specifica la dimensione desiderata in byte. Ad esempio, con due istanze del pool di buffer, è possibile impostare ciascuna su 4 Dimensioni GiB impostando la dimensione totale su 8 GiB:

SET GLOBAL innodb_buffer_pool_size=8589934592;

Key InnoDB buffer pool metrics

MySQL espone una manciata di metriche sul buffer pool e il suo utilizzo. Alcuni dei più utili sono le metriche che monitorano la dimensione totale del pool di buffer, quanto è in uso e quanto efficacemente il pool di buffer sta servendo le letture.

Le metriche Innodb_buffer_pool_read_requests e Innodb_buffer_pool_reads sono fondamentali per comprendere l’utilizzo del pool di buffer. Innodb_buffer_pool_read_requests tiene traccia del numero di richieste di lettura logica, mentre Innodb_buffer_pool_reads tiene traccia del numero di richieste che il pool di buffer non poteva soddisfare e quindi doveva essere letto dal disco. Dato che la lettura dalla memoria è generalmente ordini di grandezza più veloce della lettura dal disco, le prestazioni ne risentiranno se Innodb_buffer_pool_reads inizia a salire.

L’utilizzo del pool di buffer è una metrica utile da controllare prima di considerare il ridimensionamento del pool di buffer. La metrica di utilizzo non è disponibile immediatamente ma può essere facilmente calcolata come segue:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

Se il tuo database sta servendo un gran numero di letture dal disco, ma il pool di buffer è tutt’altro che pieno, potrebbe essere che la tua cache sia stata cancellata di recente e si stia ancora riscaldando. Se il pool di buffer non si riempie ma serve efficacemente le letture, il set di dati di lavoro probabilmente si adatta comodamente alla memoria.

L’utilizzo del pool di buffer elevato, d’altra parte, non è necessariamente una cosa negativa in isolamento, poiché i dati vecchi o inutilizzati vengono automaticamente invecchiati dalla cache utilizzando una politica LRU. Ma se il pool di buffer non serve efficacemente il carico di lavoro di lettura, potrebbe essere il momento di scalare la cache.

Conversione delle metriche del pool di buffer in byte

La maggior parte delle metriche del pool di buffer sono segnalate come conteggio di pagine di memoria, ma queste metriche possono essere convertite in byte, il che rende più semplice collegare queste metriche con le dimensioni effettive del pool di buffer. Ad esempio, per trovare la dimensione totale del pool di buffer in byte utilizzando la variabile di stato del server che tiene traccia delle pagine totali nel pool di buffer:

Innodb_buffer_pool_pages_total * innodb_page_size

La dimensione della pagina InnoDB è regolabile ma il valore predefinito è 16 KiB o 16.384 byte. Il suo valore corrente può essere controllato con una query SHOW VARIABLES :

SHOW VARIABLES LIKE "innodb_page_size";

Conclusione

In questo post abbiamo esplorato una manciata delle metriche più importanti che dovresti monitorare per tenere sotto controllo l’attività e le prestazioni di MySQL. Se stai costruendo il tuo monitoraggio MySQL, acquisendo le metriche descritte di seguito ti metterai sulla strada verso la comprensione dei modelli di utilizzo del tuo database e dei potenziali vincoli. Ti aiuteranno anche a identificare quando è necessario ridimensionare o spostare le istanze del database su host più potenti per mantenere buone prestazioni dell’applicazione.

  • throughput delle query
  • Latenza ed errori delle query
  • Connessioni ed errori client
  • Utilizzo del pool di buffer

La parte 2 di questa serie fornisce istruzioni per raccogliere e monitorare tutte le metriche necessarie da MySQL.

Ringraziamenti

Molte grazie a Dave Stokes di Oracle e Ewen Fortune di VividCortex per aver fornito un prezioso feedback su questo articolo prima della pubblicazione.

Similar Posts

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.