Prestazioni lente del database

I sintomi tipici di questo problema sono i seguenti:

• Elevato utilizzo della memoria o delle operazioni di I/O (input/output) del disco
• Query lente

La causa più comune è l'esame delle query lente. Per verificare se questo è il caso, procedere come segue:

1. Elenca le query di lunga durata:

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. Convalida blocco query:

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. Controlla il piano di esecuzione della query:

   Explain <query>;
   

4. Verificare se mancano statistiche del database. Utilizzare la seguente query per la convalida: Azione > Esegui analisi In alternativa, eseguire il seguente comando:

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. Controllare se l'indice è assente dalla tabella. PostgreSQL fornisce metodi di indicizzazione quali B-Tree, hash, GiST,SP-GiST, GIN e BRIN. Utilizza uno di questi metodi per creare l'indice.

6. Controlla se la tabella è gonfia. Esegui la seguente query per verificare. Azione > Esegui aspirapolvere

   In alternativa, utilizzare la seguente istruzione:

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. Verificare se il database o gli oggetti sono stati creati con pg_dump o pg_restore. Utilizza Azione > Aggiorna statistiche per aggiornare.

8. Assicurarsi che sia allocato work_mem spazio sufficiente, se la query sta completando un'operazione di ordinamento.

9. Utilizza pg_stat le istruzioni per ottenere le seguenti informazioni:

   • query tempo medio di esecuzione e numero di chiamate

   • Percentuale di utilizzo della CPU da parte delle query

   • percentuale di utilizzo della memoria da parte delle query

pg_stat_activity, pg_stat_bgwriter, e pg_buffercache sono strumenti importanti in PostgreSQL per monitorare e comprendere le prestazioni del database.

Monitoraggio dell'implementazione e monitoraggio del carico del database

Databases for PostgreSQL Le distribuzioni offrono un'integrazione con il IBM Cloud Monitoring servizio per il monitoraggio dell'utilizzo delle risorse nella tua distribuzione. Utilizza questo servizio per monitorare la tua distribuzione (disco e memoria) e il carico del tuo database.

Utilizza Cloud Databases i dashboard per impostare avvisi sulle soglie relative a CPU, memoria e IOPS del disco. Molte delle metriche disponibili, come l'utilizzo del disco e gli IOPS, sono utili per aiutarti a configurare il ridimensionamento automatico nella tua distribuzione. Il ridimensionamento automatico non è abilitato per impostazione predefinita, pertanto è necessario configurarlo manualmente.

Osservare le tendenze di utilizzo e configurare il ridimensionamento automatico in modo da rispondere a tali tendenze può aiutare ad alleviare i problemi di prestazioni prima che i database diventino instabili a causa dell'esaurimento delle risorse. Ad esempio, modifiche nell'I/O del disco o errori della CPU o della memoria che causano problemi di prestazioni.

IOPS disco

Il numero di operazioni di input/output al secondo (IOPS) è limitato dal tipo e dalle dimensioni del volume di archiviazione. I volumi di archiviazione per Databases for PostgreSQL le distribuzioni sono forniti su volumi Block Storage Endurance nel livello da 10 IOPS per GB.

Se il carico operativo raggiunge o supera il limite IOPS, le richieste e le operazioni del database vengono ritardate fino a quando il sottosistema di archiviazione non riesce a recuperare il ritardo. Periodi prolungati di carico elevato possono impedire alla tua distribuzione di elaborare le query e renderla effettivamente non disponibile. È possibile aumentare il numero di IOPS disponibili per la propria distribuzione aumentando le dimensioni del disco. Ad esempio, per 10 IOPS per livello GB, è possibile aumentare gli IOPS aumentando la dimensione del volume.

Periodi prolungati di utilizzo del disco anche solo del 40-50% possono avere un impatto negativo significativo sulle prestazioni del database. Assegnare almeno 100 GB di disco (1.000 IOPS) per gli ambienti di produzione. IOPS = 10 × GB allocati (ad esempio, 100 GB = 1.000 IOPS)

Utilizzo della memoria

La memoria è il modo più veloce ed efficiente per accedere ed elaborare i dati. Per questo motivo, un database funziona quasi sempre più velocemente utilizzando la memoria invece di leggere i dati dal disco. Ci sono altri parametri da considerare, come i cache hit, i blocchi letti e i blocchi colpiti. Il solo fatto di vedere un utilizzo della memoria al 100% non è di per sé motivo di preoccupazione. Se la memoria è esaurita e vengono utilizzate pagine di swap, le prestazioni possono diminuire in modo significativo.

È possibile impostare la quantità di memoria dedicata al pool di buffer condiviso del database regolando shared_buffers nella Databases for PostgreSQL configurazione. Il valore massimo consigliato è pari al 25% della memoria totale dell'implementazione. Assegnare troppa memoria al pool di buffer condiviso può privare il sistema della memoria necessaria per altri scopi, ostacolare le prestazioni o addirittura disabilitare il database.

Monitoraggio del carico del database

Hai due opzioni per controllare il carico del database:

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ulteriori azioni che puoi intraprendere per migliorare le prestazioni

È inoltre possibile prendere in considerazione le seguenti azioni per risolvere i problemi relativi alle prestazioni:

• Ottimizza le query lente

  • Esegui EXPLAIN (ANALYZE, BUFFERS) sulle query lente. Questo mostra come il database esegue la query e dove è lento. Ad esempio:

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • Cerca:

    • Query che utilizzano molta memoria o spazio su disco

    • Aggiungi IBM Cloud risorse secondo necessità

    • Scala il disco/la memoria per ottenere IOPS e memoria più elevate

• Gli indici mancanti o inefficienti sono una causa comune di query lente. Utilizza EXPLAIN per identificare le scansioni sequenziali e valuta l'aggiunta di indici.

• Esegui VACUUM per facilitare l'analisi dello stato di salute del database.

• Considerare l'utilizzo del pooling delle connessioni per gestire un numero maggiore di connessioni. Databases for PostgreSQL imposta il numero massimo di connessioni al Databases for PostgreSQL database a 115. 15 connessioni sono riservate al superutente per mantenere lo stato e l'integrità del database, mentre 100 connessioni sono disponibili per l'utente e le sue applicazioni. Una volta raggiunto il limite di connessioni, qualsiasi tentativo di avviare una nuova connessione genera un errore. Per evitare di sovraccaricare la distribuzione con connessioni, utilizzare il pooling delle connessioni oppure scalare la distribuzione e aumentare il limite di connessioni. Per ulteriori informazioni, vedere Gestione del PostgreSQL pooling delle connessioni.

• Verificare la presenza di eventuali procedure di backup e caricamento batch dei dati, ad esempio: I backup automatici vengono eseguiti quotidianamente e conservati con un semplice programma di conservazione di 30 giorni. Se un backup è bloccato, è possibile controllare la sezione Backup disponibili e identificare il backup bloccato nella pagina dell'interfaccia utente cloud dell'istanza del database.

• Controlla le tue IBM Cloud notifiche per eventuali interventi di manutenzione. Ad esempio, l'applicazione di patch al database.

• Se ritieni che si tratti di un problema della piattaforma, ad esempio di manutenzione, contatta IBM l'assistenza con il CRN del database.