PostgresDB High CPU/Memory Usage

The database server consuming excessive CPU or memory, often leading to performance issues for other services.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Stuck? Get Expert Help
TensorFlow expert • Under 10 minutes • Starting at $20
Talk Now
What is

PostgresDB High CPU/Memory Usage

 ?
  1. Identify Slow Queries:Run this query to find slow queries that might be causing high CPU/memory usage:
  2. SELECT pid, query_start, query, state, wait_event_type, wait_event
    FROM pg_stat_activity
    WHERE state <> 'idle' AND (now() - query_start) > interval '5 minutes';
  3. This helps in identifying long-running queries that you might need to kill or optimize.
  4. Check for Locks:High CPU/memory can also be due to locks. Use this query to check for them:
  5. SELECT a.datname, l.relation::regclass, l.locktype, page, virtualtransaction, pid, mode, granted
    FROM pg_locks l
    JOIN pg_stat_activity a
    ON a.pid = l.pid
    WHERE NOT granted;
  6. This identifies locked resources that could be causing performance issues.
  7. Analyze Index Usage:Use the following to check if indexes are being used effectively:
  8. SELECT relname, seq_scan, idx_scan, n_live_tup
    FROM pg_stat_user_tables
    WHERE idx_scan < seq_scan AND n_live_tup > 10000
    ORDER BY n_live_tup DESC;
  9. This helps in identifying tables where adding an index could reduce CPU/memory usage.
  10. Kill Heavy Processes:If you've identified a query that's consuming too much resource and needs to be stopped, use:
  11. SELECT pg_cancel_backend(pid);
  12. Replace pid with the process ID from your earlier findings.
  13. Check for Bloated Tables:Tables can become bloated over time. Check for bloated tables:
  14. SELECT schemaname, tablename, pg_size_pretty(total_bytes - COALESCE(index_bytes,0) - COALESCE(toast_bytes,0) - COALESCE(free_space,0)) AS table_bytes
    FROM (
    SELECT *, pg_total_relation_size(schemaname || '.' || tablename) AS total_bytes
    FROM pg_tables
    LEFT OUTER JOIN (
    SELECT table_schema || '.' || table_name AS table_full_name,
    SUM(index_bytes) AS index_bytes, SUM(toast_bytes) AS toast_bytes, SUM(free_space) AS free_space
    FROM (
    SELECT current_database() AS table_catalog, nspname AS table_schema, relname AS table_name,
    c2.relname AS index_name, pg_size_pretty(pg_relation_size(c2.oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(c2.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c2.oid) - pg_relation_size(reltoastrelid)) AS table_bytes,
    pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast_bytes,
    pg_size_pretty(pg_relation_size(reltoastrelid) - pg_relation_size(reltoastidxid)) AS toast_table_bytes,
    pg_size_pretty(pg_relation_size(reltoastidxid)) AS toast_index_bytes,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_bytes,
    pg_size_pretty(pg_total_relation_size(relid) - pg_table_size(relid) - pg_indexes_size(relid)) AS free_space
    FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class c2 ON c2.oid = i.indrelid
    WHERE c.relkind = 'i'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND pg_relation_size(c2.oid) > 0
    ) AS s1
    GROUP BY table_schema, table_name
    ) AS s2 ON table_full_name = schemaname || '.' || tablename
    ) AS s3
    ORDER BY total_bytes DESC LIMIT 10;
  15. This helps in identifying and addressing bloated tables which can improve performance.
  16. VACUUM and ANALYZE:For bloated tables or if the database hasn't been vacuumed in a while, run:
  17. VACUUM (VERBOSE, ANALYZE) tablename;
  18. Replace tablename with the name of the table to be vacuumed. This command will reclaim storage occupied by dead tuples and update statistics for better query planning.
  19. Monitor Load:Use system monitoring tools like top, htop, or vmstat to monitor the CPU and memory usage in real-time. Also, consider using pg_top to specifically monitor PostgreSQL processes.

These steps are immediate actions a developer can take to investigate and mitigate high CPU/memory usage issues in a Postgres DB.

Attached error: 
PostgresDB High CPU/Memory Usage
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Master 

PostgresDB

 debugging in Minutes

— Grab the Ultimate Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Your email is safe with us. No spam, ever.

Thank you for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

PostgresDB

Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Your email is safe with us. No spam, ever.

Thank you for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MORE ISSUES

SOC 2 Type II
certifed
ISO 27001
certified
Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢

Doctor Droid