Esempio di analisi delle prestazioni

Per seguire le istruzioni video, apri il report di esempio qui.

Come interpretare il report delle prestazioni

Utilizzando HQbird, oppure, come servizio separato, l’Analisi delle Prestazioni IBSurgeon da cc.ib-aid.com, puoi generare un report delle prestazioni dai log di traccia di Firebird.

Questo report è un potente strumento diagnostico che fornisce informazioni dettagliate sull’esecuzione delle query SQL nei database Firebird. Questa guida spiega come interpretare e utilizzare i report di traccia per identificare e risolvere sistematicamente i colli di bottiglia delle prestazioni.

1. Struttura del Report delle Prestazioni

┌─────────────────────────────────────────┐
│         Report delle Prestazioni        │
├─────────────────────────────────────────┤
│ 1. Grafici di Riepilogo delle Prestazioni│
│    ┌────────────────────────┐           │
│    │  Query principali      │           │
│    │     Riepilogo principale│          │
│    │     Frequenza principale│          │
│    │     Durate             │           │
│    │     Fetch              │           │
│    │     Letture            │           │
│    │     Scritture          │           │
│    │  Grafico Serie Temporale│          │
│    │     Durate             │           │
│    │     Numero di query    │           │
│    │     Fetch              │           │
│    │     Letture/Scritture  │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 2. Analisi delle Query Principali       │
│    ┌────────────────────────┐           │
│    │ Classifiche Query      │           │
│    │                        │           │
│    │  Per Durata────────┐   │           │
│    │                    │   │           │
│    │  Per Tempo─────────┤   │           │
│    │  Riepilogo         │   │           │
│    │                    │   │           │
│    │  Per Piano─────────┤   │           │
│    │  Riepilogo         │   │           │
│    │                    │   │           │
│    │  Per Frequenza─────┤   │           │
│    │                    │   │           │
│    │  Per Piano─────────┤   │           │
│    │  Frequenza         │   │           │
│    │                    │   │           │
│    │  Per Fetch─────────┤   │           │
│    │                    │   │           │
│    │  Per Letture───────┤   │           │
│    │                    │   │           │
│    │  Per Scritture─────┘   │           │
│    │                        │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 3. Riepilogo dei Processi               │
│    ┌────────────────────────┐           │
│    │ Statistiche per Processo│          │
│    │ - Conteggi esecuzioni  │           │
│    │ - Fetch, ecc.          │           │
│    │ - Metriche di durata   │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 4. Riepilogo degli Indirizzi            │
│    ┌────────────────────────┐           │
│    │Statistiche per Indirizzo│          │
│    │ Cliente                │           │
│    │ - Conteggi connessioni │           │
│    │ - Durate               │           │
│    │ - Fetch, ecc.          │           │
│    │ - Nomi dei processi    │           │
│    └────────────────────────┘           │
└─────────────────────────────────────────┘

Struttura dei Dettagli della Query:
┌────────────────────┐
│ Informazioni Query │
├────────────────────┤
│ - Testo SQL        │
│ - Info transazione │
│ - Piano di esecuzione│
│ - Statistiche durata│
│ - Statistiche risorse│
│   * Fetch           │
│   * Letture         │
│   * Scritture       │
│   * Mark            │
│ - Info cliente      │
└────────────────────┘

Il report delle prestazioni fornisce una visione gerarchica dell’attività del database:

1. Grafici di Riepilogo delle Prestazioni

• Rappresentazione visiva delle metriche chiave nel tempo - puoi facilmente vedere i picchi di attività/carico. (È disponibile anche un report di analisi per minuto nel Monitoraggio Avanzato delle Prestazioni in HQbird, la versione abbreviata è disponibile nello strumento Portal - guarda questo video per i dettagli).

• Aiuta a identificare modelli e anomalie: confrontare i grafici dei periodi con buone prestazioni (es. ultima settimana/mese) con i problemi di prestazioni può aiutare a identificare il problema.

2. Analisi delle Query Principali

• Molteplici prospettive di classificazione per un’analisi completa: le query più lunghe, le query più frequenti, le query più dispendiose in termini di tempo (raggruppate per testo o piano), e altro.

• Ogni dimensione rivela diverse opportunità di ottimizzazione

• Statistiche dettagliate per ogni query, inclusi:

• Metriche di durata (min, max, media, mediana)

• Consumo di risorse (fetch, letture, scritture)

• Modelli di esecuzione - numero di origini per le query principali.

3. Riepilogo dei Processi

• Raggruppa le statistiche per processo di esecuzione
• Aiuta a identificare applicazioni problematiche
• Mostra il consumo di risorse e le operazioni sul database (connessioni, query, ecc.) e le metriche (fetch, letture, ecc.) per processo

4. Riepilogo degli Indirizzi

• Raggruppa le statistiche per connessione client
• Rivela la distribuzione del carico tra i client
• Aiuta a identificare problemi specifici della connessione

Ogni sezione supporta l’analisi delle prestazioni a diversi livelli:

• Modelli a livello di sistema (Grafici) - vedi quando e dove si verificano i problemi in generale.
• Impatto più evidente dalle query (Query Principali) - identifica le query che dovrebbero essere ottimizzate per prime.
• Problemi a livello di applicazione (Riepilogo dei Processi) - identifica le applicazioni che producono problemi di prestazioni.
• Problemi a livello di client (Riepilogo degli Indirizzi) - identifica gli indirizzi IP (workstation, computer client) con il maggior flusso di query.

2. Analisi del Riepilogo Temporale e del Riepilogo per Piano

Si consiglia di iniziare l’analisi della situazione delle prestazioni con le sezioni di Riepilogo. Clicca qui per aprire la sezione Riepilogo per Piano nel report di esempio.

Il Riepilogo Temporale aggrega il tempo totale di esecuzione per ogni modello di istruzione SQL univoco.

Pensalo come un report dei “centri di costo” che mostra quali query consumano più risorse del database nel tempo.

Se le query non sono parametrizzate, cioè contengono esplicitamente i valori dei parametri nel testo SQL invece del segnaposto dei parametri (:myparam1), è necessario utilizzare la sezione "Riepilogo per Piano" per identificare le query con la frequenza più alta.
Esempio di query non parametrizzata: 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Esempio di query parametrizzata: 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

Ogni query nella sezione Riepilogo ha un’intestazione con le seguenti parti chiave:

• Riepilogo: Percentuale del tempo totale, mostra quale porzione del tempo complessivo del database consuma una query.

• Frequenza: Quante volte appare il modello di query

• Fetch, Letture, Scritture: Metriche delle risorse

Ad esempio, se c’è

Riepilogo: 19.08% (3920272 di 20541791 ms)

Questo ci dice che questo modello di query sta consumando quasi il 20% del tempo totale del database - una porzione significativa che richiede attenzione immediata.

Sotto l’intestazione nella sezione Riepilogo per Piano vedremo il piano di esecuzione SQL, che è stato utilizzato per raggruppare le query, nel Riepilogo Temporale sarà il testo della query stessa.

Poiché il modello di query rappresenta più di una query specifica, le informazioni relative alla connessione sono prese dalla prima query che corrisponde al modello:

Nello screenshot sopra puoi vedere l’intestazione dell’istruzione di esempio per il modello, consiste nel nome dell’applicazione che ha avviato questa SQL, l’ID della connessione e l’ID della transazione, oltre all’indirizzo IP e ai dettagli della transazione.

Sotto va il piano (per il Riepilogo Temporale, per il Riepilogo per Piano viene saltato perché è già mostrato all’inizio), i valori dei parametri (nell’ordine di apparizione) e le statistiche per tabella:

Ricorda, nel Riepilogo per Piano raggruppiamo le SQL utilizzando il piano di esecuzione, il che significa che solo il piano è persistente per il modello, e per il Riepilogo Temporale raggruppiamo utilizzando il testo dell’istruzione SQL, e altre cose (valori dei parametri, tempi di esecuzione, ecc.) possono essere diverse. Usa queste informazioni come esempio del modello di esecuzione (nel 99% dei casi è sufficiente per riprodurre il problema).

Sotto abbiamo un grafico individuale con le esecuzioni di questa query specifica. Come puoi vedere, questa query è stata avviata nel periodo di alto carico che abbiamo notato nel grafico di panoramica.

E, alla fine, abbiamo una raccolta molto importante di statistiche per TUTTE le query che corrispondono al modello, e l’elenco degli indirizzi di origine:

In queste statistiche, possiamo vedere i tempi di esecuzione minimi, massimi, medi e mediani, le stesse statistiche per fetch, letture, scritture e mark (operazioni di flush della cache).

2.1. Come Utilizzare il Riepilogo Temporale:

• Identifica prima le query che consumano tempo sproporzionato (sono le prime 3 di questa sezione - #1, 2, 3)

• Confronta il consumo di tempo con la frequenza

• Vedi il tempo medio di esecuzione (tempo totale / frequenza) in fondo alla sezione della query (vedi sotto)

• Cerca modelli dove:

• Tempo alto + Frequenza bassa = Query individuali inefficienti

• Tempo alto + Frequenza alta = Query potenzialmente inefficienti ma molto utilizzate

3. Analisi della Frequenza: Frequenza e Frequenza per Piano

Usa l’analisi della frequenza per capire quanto spesso vengono eseguite le query. Pensala come contare quante volte una particolare strada viene utilizzata durante l’ora di punta.

Se le query non sono parametrizzate, cioè contengono esplicitamente i valori dei parametri nel testo SQL invece del segnaposto dei parametri (:myparam1), è necessario utilizzare la sezione "Riepilogo per Piano" per identificare le query con la frequenza più alta.
Esempio di query non parametrizzata: 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Esempio di query parametrizzata: 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

3.1. Comprendere l’Impatto della Frequenza

La rappresentazione del modello di query per Frequenza è molto simile al Riepilogo per Piano/Temporale:

Le query ad alta frequenza sono come incroci trafficati - anche se ogni auto (query) si muove velocemente, il volume stesso può causare congestione. Questo influisce su:

• Connessioni al database (come posti auto - limitati in numero)

• Larghezza di banda di rete (come la capacità stradale)

• Utilizzo della CPU (come i controllori del traffico che vengono sopraffatti)

• Efficienza della cache (come dover accedere ripetutamente alle stesse informazioni)

Per stimare l'impatto delle query ad alta frequenza, raccogliere una traccia con soglia parametro = 0.

3.2. Categorie di Impatto della Frequenza

3.3. Analisi di Esempio

Esaminiamo un caso reale dal nostro report di traccia:

Frequenza: 4.428 esecuzioni (24,43% del totale)
Impatto: Critico - alto volume di query sulla tabella SALES
Causa Principale: Controlli ripetitivi del saldo cliente
Priorità di Ottimizzazione: Alta

Spiegazione: Questa query viene eseguita migliaia di volte, simile a un
incrocio trafficato. Anche se ogni esecuzione potrebbe essere veloce,
l'impatto cumulativo è significativo. L'applicazione potrebbe controllare
i saldi più spesso del necessario.

4. Analisi delle statistiche delle query principali nelle sezioni xx-Summary e Frequency

Quando si analizzano i report di traccia Firebird, ogni raggruppamento di query contiene statistiche aggregate dettagliate che forniscono informazioni cruciali sui pattern di prestazioni. Analizziamo ogni metrica e comprendiamo la sua importanza per l’ottimizzazione del database.

4.1. Analisi delle Statistiche Aggregate

Esaminiamo questo esempio di set di statistiche:

Totale: 4428 elementi:
Durata: min: 351; max: 3919; media: 457.70; mediana: 455.00; somma: 2026710 (20,29%);
Fetch: min: 7135; max: 7168; media: 7146.86; mediana: 7147.00; somma: 31646289 (0,75%);
Scritture: min: 0; max: 0; media: 0.00; mediana: 0.00; somma: 0 (0,00%);
Letture: min: 0; max: 6995; media: 3.13; mediana: 0.00; somma: 13856 (8,22%);
Mark: min: 0; max: 0; media: 0.00; mediana: 0.00; somma: 0 (0,00%);
Da 1 indirizzo univoco: TCPv6:::1 (4428)

4.2. Analisi del Conteggio delle Esecuzioni

4.2.1. Elementi Totali

Totale: 4428 elementi

Questo rappresenta il numero di volte che questo particolare pattern di query è stato eseguito durante il periodo di traccia.

Comprendere questo numero ti aiuta a:

• Calcolare l’utilizzo delle risorse per esecuzione

• Determinare se la cache delle query potrebbe essere vantaggiosa (o semplicemente eseguirla meno frequentemente)

Conteggi di esecuzione elevati potrebbero indicare opportunità per:

• Implementare istruzioni preparate (e parametrizzate) - la stessa query con la stessa frequenza, quando parametrizzata e preparata per l’esecuzione ripetitiva, richiederà meno risorse

• Aggiungere la cache dei risultati - memorizzare il valore risultante per l’uso durante l’operazione lunga o anche più a lungo, per la sessione dell’utente, può ridurre la necessità di eseguire frequentemente la query

• Operazioni in batch - considerare di eseguire la query per restituire o elaborare molti record in una volta, ciò eliminerà il sovraccarico per l’esecuzione della query (preparazione, trasmissione di rete, ecc.).

4.3. Metriche di Durata

4.3.1. Esempio dei Componenti della Durata

Durata: min: 351; max: 3919; media: 457.70; mediana: 455.00; somma: 2026710 (20,29%);

4.3.2. Analisi della Durata

• Mediana e media vicine (457,70 vs 455,00 ms) suggeriscono prestazioni costanti

• Rapporto max/min (~11x) indica una certa variabilità

• Il 20,29% del tempo totale è significativo - questa query è tra le prime 3 nella sezione Frequency o Plan-Frequency? (sì, lo è.)

4.4. Metriche di Utilizzo delle Risorse

4.4.1. Operazioni di Fetch

Fetch: min: 7135; max: 7168; media: 7146.86; mediana: 7147.00; somma: 31646289 (0,75%);

I fetch rappresentano il recupero delle righe:

• Conteggi di fetch costanti (differenza min/max di soli 33) suggeriscono set di risultati stabili

• Conteggi di fetch relativamente elevati (>7000 per esecuzione) potrebbero indicare:

• Necessità di limitare il set di risultati e/o di paginazione, se vengono restituiti molti record.

• Potenziale per l’ottimizzazione della query - ha particolarmente senso se la query è tra le prime 3 di Frequency/Plan-Frequency.

4.5. Operazioni di Lettura

Letture: min: 0; max: 6995; media: 3.13; mediana: 0.00; somma: 13856 (8,22%);

Le letture fisiche indicano l’accesso al disco:

• Mediana zero con massimo non zero suggerisce occasionali mancate cache

• L'8,22% delle letture totali indica un impatto I/O moderato

• Il grande divario tra min (0) e max (6995) suggerisce un’efficacia variabile della cache.

4.6. Operazioni di Scrittura

Scritture: min: 0; max: 0; media: 0.00; mediana: 0.00; somma: 0 (0,00%);

Se la query non esegue scritture, di solito è un’operazione di sola lettura.

4.7. Operazioni di Mark

Mark: min: 0; max: 0; media: 0.00; mediana: 0.00; somma: 0 (0,00%);

Le operazioni di mark riguardano la gestione della cache delle pagine dati:

• Mark zero indicano che nessuna pagina dati è stata marcata per il flush, comune per semplici query SELECT

• Operazioni di mark non zero con cache

4.8. Analisi della Connessione Client

Da 1 indirizzo univoco: TCPv6:::1 (4428)

Questo mostra la distribuzione della sorgente della query:

• Indirizzo client singolo suggerisce una query specifica dell’applicazione

• Connessione locale (::1 è localhost IPv6)

• Tutte le 4428 esecuzioni dalla stessa sorgente

4.9. Utilizzo di Queste Metriche per l’Ottimizzazione

4.9.1. Analisi dei Pattern di Prestazioni

Coerenza dell’Esecuzione

• Confrontare le durate min/max

• Cercare valori anomali nell’utilizzo delle risorse

• Controllare la mediana rispetto alla media per la variabilità

Pattern di Utilizzo delle Risorse

• Fetch elevati → Rivedere la dimensione del set di risultati

• Letture elevate → Controllare la copertura degli indici

• Mark elevati → Esaminare la contesa sui lock

Analisi dell’Impatto del Client

• Client multipli → Dimensionamento del pool di connessioni

• Client singolo → Ottimizzazione dell’applicazione

4.9.2. Priorità di Ottimizzazione

In base a queste metriche, dare priorità a:

• Dimensione del Set di Risultati

• 7000 fetch per esecuzione

• Considerare l’aggiunta di LIMIT/OFFSET

• Rivedere l’elenco delle colonne SELECT

Strategia di Caching

• Esecuzione frequente (4428 volte)

• Dimensione del risultato costante

• Nessuna scrittura coinvolta

Probabilmente, questa query può essere eseguita meno frequentemente.

Utilizzo degli Indici

• Conteggi di lettura variabili

• Letture con mediana zero ma massimo elevato

• Rivedere la copertura degli indici

5. Applicazione Pratica

Per questo esempio specifico:

Miglioramenti a Breve Termine:

• Implementare la cache dei risultati (conteggio di esecuzione elevato, fetch costanti)

• Rivedere la dimensione del set di risultati (>7000 fetch per esecuzione)

Ottimizzazione a Medio Termine:

• Analizzare i pattern di utilizzo degli indici

• Considerare l’uso di istruzioni preparate

• Rivedere la logica dell’applicazione per la frequenza di esecuzione

Considerazioni a Lungo Termine:

• Monitorare i pattern di esecuzione nel tempo

• Pianificare una strategia di manutenzione degli indici

• Considerare modifiche ai pattern di accesso ai dati

6. Analisi della Durata

L’analisi della durata esamina quanto tempo impiegano le singole query per essere eseguite. Pensa alla durata come a un cronometro che misura ogni query - più a lungo impiega una query, più è probabile che causi problemi di prestazioni.

6.1. Comprendere le Metriche di Durata

Le metriche di durata sono cruciali perché influenzano direttamente l’esperienza degli utenti, cioè gli utenti affermano “il sistema è lento”. Proprio come i clienti si frustrano aspettando in una lunga fila, gli utenti si frustrano quando le query impiegano troppo tempo per completarsi. Le query di lunga durata causano:

• Scarsa esperienza utente quando le schermate impiegano troppo tempo a caricarsi

• Risorse di sistema occupate per periodi prolungati

• Altre query in attesa in coda dietro quelle lente

• Potenziali problemi di timeout nelle applicazioni

6.2. Categorie di Impatto

6.3. Analisi di Esempio

Durata: 77.793ms
Impatto: Critico - singola query che consuma 77,7 secondi
Causa Principale: Aggregazione complessa in PRC_COLLECT_RANKCATEGORY
Priorità di Ottimizzazione: Immediata

Spiegazione: Questa query impiega oltre un minuto per essere eseguita, il che è come
un blocco totale del traffico. La stored procedure sta probabilmente elaborando
troppi dati o utilizzando algoritmi inefficienti.

7. Strategia di Implementazione

Pensa all’ottimizzazione come al miglioramento di un sistema di trasporto - devi identificare i problemi, pianificare le soluzioni e implementare i cambiamenti con attenzione.

7.1. Matrice di Priorità

Questa matrice ti aiuta a decidere cosa richiede attenzione per prima, come il triage dei problemi di traffico in una città:

7.2. Processo di Ottimizzazione Passo-Passo

1. Identificare le Query Critiche

• Cercare i maggiori ingorghi (query lente)

• Trovare gli incroci più trafficati (query ad alta frequenza)

• Individuare percorsi inefficienti (elevato utilizzo di risorse)

2. Analizzare i Piani di Esecuzione

• Studiare i percorsi attuali (utilizzo degli indici)

• Esaminare i pattern di traffico (metodi di join)

• Controllare i colli di bottiglia (operazioni di ordinamento)

3. Implementare le Ottimizzazioni

• Costruire nuove strade (indici)

• Ridisegnare i percorsi (ristrutturare le query)

• Aggiungere scorciatoie (caching)

4. Verificare i Miglioramenti

• Misurare il nuovo flusso di traffico (nuovo report di traccia)

• Confrontare le metriche prima/dopo

• Documentare cosa ha funzionato

Contatta IBSurgeon per qualsiasi domanda

Sentiti libero di contattarci per qualsiasi domanda: [email protected].