Banche dati

Utilizzare in modo efficace MySQL Performance Schema per migliorare le prestazioni

Per una migliore Prestazioni MySQL Utilizzo lo schema delle prestazioni per analizzare direttamente tramite SQL i dati relativi al tempo di esecuzione, alle attese, ai blocchi, alla memoria e all'I/O. In questo modo riesco a individuare più rapidamente le cause alla base delle istruzioni lente e ad adottare misure mirate per Sintonizzazione e monitoraggio da [2][3][15].

Punti centrali

I seguenti punti chiave mi aiutano a utilizzare in modo efficace lo schema delle prestazioni.

Attivazione e una configurazione snella con strumenti e dispositivi di consumo adeguati
Digest delle dichiarazioni utilizzare per individuare modelli costosi e punti caldi
Eventi di attesa, analizzare congiuntamente Locks e I/O per individuare i veri colli di bottiglia
Schema di sistema come abbreviazione di intuizioni rapide e utili per agire
Iterativo Flusso di lavoro: misurare, isolare, modificare, misurare nuovamente

Attivare lo schema delle prestazioni e configurarlo in modo ottimale

Per prima cosa verifico se performance_schema è attivo, poiché le versioni attuali di MySQL lo forniscono di norma già attivato [1][12]. Se manca, lo imposto nel [mysqld]-Blocco del my.cnf la variabile performance_schema=ON e riavvio il server. Dopodiché configuro in modo mirato gli strumenti e i dispositivi, invece di lasciarli sempre al massimo. Mi concentro su dichiarazione/%, wait/% e i percorsi I/O rilevanti, in modo da raccogliere dati significativi senza un sovraccarico superfluo [6]. Per una nuova serie di misurazioni, svuoto le tabelle di cronologia interessate e inizio da zero Base.

Risultati rapidi con lo schema Sys

Per avere una rapida panoramica, ricorro spesso al sys-Schema, poiché aggrega in modo efficace i dati grezzi del Performance Schema [13]. In questo modo riesco a individuare in pochi minuti le query che incidono maggiormente sul tempo di esecuzione. Comincio con le istruzioni principali, controllo le viste File I/O ed esamino i riepiloghi delle attese per i thread. Non appena individuo un punto critico, torno alle tabelle grezze e affino l’analisi. Chi esamina i piani di esecuzione delle query può, con le opportune Consigli per l'Optimizer spesso percepibili già in breve tempo Vincite raggiungere.

Scegliere gli strumenti e i dispositivi giusti

Comincio in modo ampio, ma mantengo l’osservazione sotto controllo: per prima cosa attivo gli elementi più importanti Strumenti per le istruzioni, i tempi di attesa e le operazioni di I/O; dopodiché disattivo tutto ciò che non fornisce informazioni utili [6]. Gli elementi di analisi come l’Event History e le tabelle di riepilogo devono supportare le domande a cui intendo rispondere. Se, ad esempio, si tratta di picchi di latenza, controllo in events_waits_summary_global_per_nome_evento e confrontalo con eventi_dichiarazioni_riepilogo_per_sintesi. Se si verificano tempi di attesa I/O, controllo riepilogo_file_per_nome_evento e table_io_waits_summary_by_table. Questa selezione mirata riduce al minimo i costi generali e fornisce comunque risultati affidabili Dati.

Digest delle dichiarazioni: individuare modelli, ridurre il carico

Grazie agli statement digest riesco a individuare quali modelli risultano costantemente costosi, anche se le singole query contengono valori letterali variabili [17]. Ordino i risultati in base al tempo totale, al numero di esecuzioni e alla latenza media per stabilire le priorità. A tal fine ricorro anche al Analizzare il log delle query lente indietro, per non trascurare eventuali valori anomali. Quando i digest mostrano picchi, controllo gli indici, le strategie JOIN e l'ordine dei filtri con SPIEGARE. Successivamente, verifico l'efficacia effettuando nuove misurazioni nello schema delle prestazioni, in modo che le ottimizzazioni rimangano misurabili.

Interpretazione degli eventi di attesa, dei lock e delle operazioni di I/O

Quando le richieste rimangono in sospeso, interrogo le tabelle "Wait" e "Lock" per individuare la vera e propria Causa da trovare [3]. Se molti thread operano sulle stesse tabelle, ciò indica table_lock-Attendo che si verifichi una situazione di concorrenza. Se gli eventi di I/O sui file presentano latenze elevate, verifico lo storage, il caching e i modelli di interrogazione con scansioni su larga scala. Se rilevo blocchi di riga InnoDB, analizzo i record più accessi, la durata delle transazioni e la copertura degli indici. Solo quando questi pezzi del puzzle combaciano, intervengo sui parametri del server, sullo schema o sul codice.

Monitoraggio della memoria: memoria e buffer pool

Per risolvere i problemi di memoria, analizzo l'utilizzo delle tabelle di memoria e del buffer InnoDB. Se il fabbisogno di memoria dei singoli componenti aumenta, regolo i limiti e verifico se le cache contengono dati errati. Se la cache InnoDB non è sufficiente, ne aumento la quota o miglioro la località delle query. Chi desidera approfondire l'argomento può consultare Ottimizzare il buffer pool ottenere notevoli miglioramenti in termini di latenza. Confermo l'efficacia con i Sintesi-Tabelle e verifica se gli LRU-hit e i tempi di attesa I/O stanno andando nella direzione giusta.

Flusso di lavoro diagnostico iterativo per l'attività quotidiana

Lavoro sempre seguendo cicli ben definiti, in modo da non perdere tempo e garantire che le modifiche rimangano misurabili [3]. Per prima cosa riproduco il problema sotto carico controllato. Successivamente raccolgo i valori misurati in poche tabelle mirate e isolo i candidati più evidenti. Successivamente modifico ciò che promette il massimo beneficio: indice, query, parametro o codice. Infine, effettuo nuovamente le misurazioni e documento brevemente Prima/dopo-tabelle, in modo che il team possa vedere immediatamente l'effetto.

Esempi di query: dai dati grezzi alle decisioni

Per le domande più comuni ho annotato alcuni frammenti di codice SQL concisi che utilizzo direttamente nel mio lavoro quotidiano. La tabella mostra gli esempi che uso più spesso e a cosa servono. Adatto i filtri come LIMITE oppure ORDER BY a seconda del caso specifico. L’importante è: prima l’ipotesi, poi un’analisi mirata e una decisione chiara. In questo modo mantengo l’analisi mirata ed evito inutili Carico.

Tabella/e dello schema delle prestazioni	Obiettivo	Colonne importanti	Query di esempio
`eventi_dichiarazioni_riepilogo_per_sintesi`	Trovare modelli costosi	`digest_text`, `count_star`, `sum_timer_wait`	`SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;`
`events_waits_summary_global_per_nome_evento`	Punti di attesa	`nome_evento`, `sum_timer_wait`	`SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;`
`table_io_waits_summary_by_table`	Verifica dell'I/O delle tabelle	`schema_oggetto`, `nome_oggetto`, `read_timer_wait`	`SELECT object_schema, object_name, (read_timer_wait+write_timer_wait)/1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;`
`memory_summary_global_per_nome_evento`	Individuare i processi che consumano molta memoria	`nome_evento`, `current_alloc`	`SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;`

Azienda di produzione: ridurre al minimo i costi generali, massimizzare l'efficacia

Durante le sessioni live non attivo gli strumenti a caso, ma seleziono solo quelli che rispondono alla mia domanda [6]. Gestisco con cautela gli eventi ad alta frequenza e mantengo brevi le finestre della cronologia. Per osservazioni più lunghe, preferisco i riepiloghi compressi e salvo le istantanee su un supporto esterno. Presto attenzione alla voce in performance_schema_setup_consumers, in modo da poter gestire le raccolte invece di lasciarle andare alla deriva. Questo approccio mantiene l'analisi efficiente e protegge il server.

Messa a punto: strumenti e dispositivi di monitoraggio nella pratica

Per ottenere rapidamente risultati affidabili, configuro gli strumenti e i carichi in modo mirato. Sono particolarmente importanti dichiarazione/%, wait/%, wait/io/% e – se necessario – selezionati memory/%-Percorsi. Per prima cosa attivo solo lo stretto necessario e poi amplio la configurazione se mi rimangono ancora domande concrete senza risposta. I timer nello schema delle prestazioni misurano in picosecondi; per ottenere i secondi, divido le colonne relative alla latenza per 1e12.

Punto di partenza tipico durante l'esecuzione:

-- Attivare gli strumenti chiave
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
 OR NAME LIKE 'wait/io/%'
 OR NAME LIKE 'wait/lock/%';

-- Selezionare i consumer importanti
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
 'thread_instrumentation',
 'statements_digest',
 'events_statements_current',
                 'events_statements_history',
 'events_waits_current',
 'events_waits_history');

-- Svuotare i riepiloghi per una nuova serie di misurazioni
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

Quando ho bisogno di analisi della memoria, le attivo in modo selettivo memory/%-Strumenti. Ciò comporta costi aggiuntivi, ma ne vale la pena in caso di perdite o di forte pressione sull'allocatore.

Dimensioni: comprendere utenti, host e schemi

I picchi di potenza spesso non sono globali, ma limitati a determinati Utente, Ospiti o un Schema limitato. Lo schema delle prestazioni fornisce a tal fine dei riepiloghi per ogni account e host. Inoltre, nel riepilogo è inclusa la colonna nome_schema, per limitare gli hotspot per ogni database.

Esempi che uso spesso:

Schemi principali in base alla durata complessiva: SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
Utenti/host che causano la maggiore latenza (per account): SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
Thread con il tempo di attesa più lungo: SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

Grazie a queste viste, posso separare in modo mirato i segmenti di traffico e applicare limitazioni, utilizzare la cache o introdurre varianti di query per ciascun cliente.

Rendere visibili le transazioni di lunga durata e i blocchi dei metadati

Le transazioni di lunga durata o inattive bloccano i checkpoint, le operazioni di purge e le operazioni DML concorrenti. Per questo motivo controllo regolarmente la vista delle transazioni e le attese MDL:

Transazioni attive: SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
Rilevare i blocchi dei metadati (conflitti DDL/DML): SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

Se MDL è predominante, riprogetto le finestre DDL, riduco al minimo i tempi di blocco nel codice (transazioni più brevi) e verifico se ci sono elementi superflui AUTOCOMMIT=0- Lasciare aperte le sessioni dei blocchi per un tempo inutilmente lungo.

Replica, backup ed effetti collaterali sotto la lente d'ingrandimento

I processi di replica e backup compaiono nelle viste relative alle attese e alle operazioni di I/O. È possibile individuare le cause dei ritardi analizzando lo stato dei worker e le attese relative ai file. Esamino i worker dell'Applier, i thread SQL e gli eventi di I/O sui file:

Applier-Worker con elevata latenza: SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
Punti critici di I/O dei file durante i backup: SELECT event_name, (sum_timer_read+sum_timer_write)/1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

Se rilevo dei colli di bottiglia, disaccoppio le fasi di I/O (ad es. windowing, scheduler di I/O, throttling dei backup) oppure aumento il numero di worker applicatori in parallelo, a condizione che il carico di lavoro sia scalabile.

Finestre temporali, istantanee e strategie di ripristino

Le misurazioni richiedono intervalli di tempo ben definiti. Per il confronto „prima/dopo“ utilizzo reset mirati e snapshot:

Reimposta i riepiloghi per ottenere nuovi intervalli: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
Salvare una copia di sicurezza esterna dell'istantanea: CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
Mantenere finestre storiche brevi (consumatori), raccogliere esternamente i trend a lungo termine.

In questo modo posso confrontare e documentare in modo affidabile le ottimizzazioni indipendentemente dai deployment, dalle modifiche ai parametri o alle modifiche allo schema.

Gestire i costi generali e lo spazio di archiviazione

Un pregiudizio diffuso è che lo schema di performance sia „troppo costoso“. Nella pratica, riesco a contenere l’overhead grazie a tre misure: attivare solo gli strumenti rilevanti, mantenere brevi i consumatori di cronologia molto frequentati e scegliere i parametri di memoria adeguati. In caso di elevata varianza dei digest, aumento in modo mirato performance_schema_digests_size nonché – se necessario – performance_schema_max_sql_text_length, affinché le identità rimangano stabili. Se sono necessari strumenti di memoria, li limito ai sottosistemi problematici.

Parametri di regolazione tipici in my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Opzionale, in caso di numerosi pattern:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

Ad ogni modifica, verifico che la CPU, la latenza e l'utilizzo della memoria rimangano stabili. Non appena la diagnosi è completata, riporto la configurazione al „minimo operativo“.

Situazioni ricorrenti e soluzioni rapide

Una somma ingente in eventi_dichiarazioni_riepilogo_per_sintesi, molte scansioni: Verifica gli indici, l'ordine dei filtri e la Sargability; conferma con SPIEGARE e ripeto la misurazione (il tempo di digestione deve diminuire in modo evidente).
Dominante table_io_waits in poche tabelle: Migliorare la localizzazione I/O (accessi agli indici clusterizzati, indici di copertura), ridurre il volume di dati per ogni istruzione; se necessario, ricorrere al batching anziché all'elaborazione dell'intera tabella.
Tempi di attesa per wait/lock/innodb/%: Identificare gli "hot record" e mitigare i conflitti di scrittura tramite transazioni più piccole, indici adeguati o l'utilizzo di code.
Molti wait/lock/metadata/sql/mdl: Progettare le finestre DDL, ONLINE- privilegiare le operazioni compatibili, disaccoppiare Reader/Writer tramite transazioni più brevi.
Aumento delle scorte in memory_summary_global_per_nome_evento: Ottimizzare i limiti, limitare in modo mirato le cache delle query, identificare i componenti problematici con memory/% suddividere in dettaglio.
„Latenza “spiky” con valori medi per il resto nella norma: Utilizzare le viste di sistema con i percentili e, se necessario, misurare separatamente i picchi di carico (finestra più ristretta, cronologia breve, strumenti mirati).

Correlare: da thread a statement e wait

Per individuare rapidamente le cause, metto in relazione performance_schema.threads con le tabelle "Current" e "History" relative agli statement e alle attese. In questo modo posso vedere quali sono state le ultime operazioni eseguite da un thread interessato e cosa sta aspettando. Ecco una breve descrizione del flusso:

Persone interessate PROCESSLIST_ID rispettivamente THREAD_ID da performance_schema.threads prendere.
Ultima dichiarazione tramite cronologia_eventi_dichiarazioni calcolare (secondo THREAD_ID e ordinare per data).
Attese parallele disattivate cronologia_degli_eventi_in_attesa verificare per individuare eventuali cause di blocco o di attesa I/O.

Questo modello „Drilldown & Join“ è il mio standard quando singole sessioni o richieste web non sono più sincronizzate.

Quality Gates e Continuous Performance

Per evitare che le ottimizzazioni vadano sprecate, stabilisco dei “Quality Gates” snelli: query definite dallo schema delle prestazioni vengono eseguite prima e dopo ogni release. Salvo degli snapshot, confronto gli indicatori (Top-Digests, Top-Waits, I/O per tabella) e documento eventuali scostamenti. Nel CI/CD aggiungo profili di carico rappresentativi e valori limite per il 95° percentile. Se una metrica esce dai limiti, c’è un chiaro percorso di feedback: verificare l’ipotesi, concentrarsi sugli strumenti, implementare la correzione, misurare nuovamente.

Prevenire le fonti di errore

Troppi strumenti nel lungo periodo: La diagnosi è temporanea; durante il funzionamento normale, lasciare attivo solo l'insieme minimo.
Periodi di misurazione misti: Svuotare i riepiloghi prima di eseguire nuovi test, altrimenti i dati precedenti ne compromettono la significatività.
Unità di misura errata: I timer sono espressi in picosecondi; in modo coerente 1e12 condividere.
Marea di digest: I valori letterali variabili possono compromettere i modelli; normalizzare l'SQL e performance_schema_max_sql_text_length controllo.
Cronologia troppo lunga: L'elevata frequenza degli eventi e la lunga cronologia generano pressione; mantenere breve la finestra della cronologia, eseguire gli snapshot esternamente.

Lista di controllo pratica

Definire la domanda, formulare l'ipotesi.
Attivare gli strumenti/i consumatori adeguati, mantenere bassi i costi generali.
Svuotare i riepiloghi, selezionare una finestra di misurazione breve.
Verificare Top-Digests, tempi di attesa e I/O; confermare i punti critici.
Personalizzare in modo mirato indice/query/codice/parametri.
Ripetere la misurazione, salvare gli snapshot, documentare la decisione.
Riportare la configurazione al minimo operativo.

In breve: il mio approccio nella pratica

Attivo lo schema di performance in modo mirato, parto con un approccio ampio e poi restringo il campo alle voci più utili Strumenti [1][2][12]. Per una rapida panoramica mi avvalgo dello schema di sistema e, se necessario, approfondisco i dati grezzi [13]. Affronto innanzitutto i punti critici nei digest e negli eventi di attesa, prima di intervenire sui parametri [3][15][17]. Successivamente, verifico ogni modifica con nuove misurazioni, affinché i progressi rimangano visibili e riproducibili. In questo modo garantisco un’affidabilità duratura Tempi di risposta e mi risparmio un lavoro superfluo.