Interpreting Supabase Grafana CPU charts

[TheOtherBrian1]

Guide for setting up Supabase Grafana

Examples of stressed CPU

Showing high busy kernel CPU (yellow). Symptomatic of extension or connection-related issues.

High busy user CPU (blue). Symptomatic of problematic queries or database overload:

High busy IOWait (red). Even though the amount is relatively low, because disk is so much slower than CPU, a small amount is a sign of inadequate memory or disk IOPS/throughput:

Completely overwhelmed. Symptomatic of misuse and problematic data access patterns

Interpreting charts

The CPU chart shows 4 distinct metrics of interest:

• Yellow: It represents CPU utilized in kernel space (privileged OS operations). If this is high, it may be a sign that your app is connecting/disconnecting too aggressively. It could also be symptomatic of extension-related errors.
• Blue: It represents requests in user space and mainly reflects the CPU usage from regular queries. For optimization tips, check out the links at the end of the page.
• Red: It represents cycles the CPU spent idle because it was waiting on IO tasks. Any amount of red often implies disk or, indirectly, memory problems. The links outline how to address this type of issue.
• Green: These are CPU cycles spent idle.

As the CPU peaks towards 100%, queries and database tasks will begin to throttle, as they won't have enough time or access to the CPU.

Identifying problematic queries:

you should review your query performance advisor. Alternatively, you can access pg_stat_statements view. If you query it directly, you should focus on queries that create extremes:

• largest std_dev
• longest individual runtime
• largest mean runtimes

PostgreSQL will produce a lot of activity in the background, so it's preferable to focus only on requests made by your application. This usually means filtering results from just the postgres role and, if you're using the DB API, the authenticator role, too.

Frequently called queries are the most insightful, so you should limit your observations to only the 30 most called. If this number is too narrow, expand as needed.

Special emphasis should be placed on queries that have a higher likelihood of CPU usage:

• Many joins, especially those without FK indexes on join columns
• Functions that utilize loops
• Window and aggregate functions
• ORDER BY without indexes
• Queries referencing LIKE/ILIKE

Besides the query explorer, queries that generate timeout and duration events in the logs can also be of interest. You can go to the Logs Explorer, and run the following query to identify the events:

select
  cast(postgres_logs.timestamp as datetime) as timestamp,
  event_message,
  parsed.error_severity,
  parsed.user_name,
  parsed.query,
  parsed.detail,
  parsed.hint,
  parsed.sql_state_code,
  parsed.backend_type
from
  postgres_logs
  cross join unnest(metadata) as metadata
  cross join unnest(metadata.parsed) as parsed
where
  regexp_contains(event_message, 'duration|statement timeout')
order by timestamp desc
limit 100;

Optimizing:

1. Optimize your queries.
2. Add indexes if possible.
3. Increasing the compute size
4. Distribute load by using read-replicas
5. Preprocess CPU-intensive queries with materialized views

Other useful Supabase Grafana guides:

• Connections
• Disk
• Memory