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
?
- 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
pidwith 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
tablenamewith 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, orvmstatto monitor the CPU and memory usage in real-time. Also, consider usingpg_topto 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
Oops! Something went wrong while submitting the form.
PostgresDB
Cheatsheet
(Perfect for DevOps & SREs)
Most-used commands
Oops! Something went wrong while submitting the form.
MORE ISSUES
Backed by
SOC 2 Type II
certifed
certifed
ISO 27001
certified
certified
Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢