PostgresDB High CPU/Memory Usage

What is PostgresDB High CPU/Memory Usage

Identify Slow Queries:Run this query to find slow queries that might be causing high CPU/memory usage: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';This helps in identifying long-running queries that you might need to kill or optimize.Check for Locks:High CPU/memory can also be due to locks. Use this query to check for them: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;This identifies locked resources that could be causing performance issues.Analyze Index Usage:Use the following to check if indexes are being used effectively: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;This helps in identifying tables where adding an index could reduce CPU/memory usage.Kill Heavy Processes:If you've identified a query that's consuming too much resource and needs to be stopped, use:SELECT pg_cancel_backend(pid);Replace pid with the process ID from your earlier findings.Check for Bloated Tables:Tables can become bloated over time. Check for bloated tables: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;This helps in identifying and addressing bloated tables which can improve performance.VACUUM and ANALYZE:For bloated tables or if the database hasn't been vacuumed in a while, run:VACUUM (VERBOSE, ANALYZE) tablename;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.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.