[question]: How can I better tune my kube_pg_cluster to avoid temp size issues

wesbragagt commented 2 months ago

Prior Search

[X] I have already searched this project's issues to determine if a similar question has already been asked.

What is your question?

We are facing an issue with a query that keeps logging in the primary pod. I've attempted to restart the pod to clear some memory usage and verified that our temp_size is set to -1. We have 20gb initial storage set for our database. I'm looking for guidance to better tune the system for these errors as much as possible. I know some query operations can always be more efficient. Perhaps setting a temp_size limit would be a good idea?

{
  “level”: “info”,
  “ts”: “2024-08-30T20:03:54Z”,
  “logger”: “postgres”,
  “msg”: “record”,
  “logging_pod”: “pg-88cc-11",
  “record”: {
    “log_time”: “2024-08-30 20:03:54.679 UTC”,
    “user_name”: “v-oidc-1a5-superuse-dui8IIp1Opyi563ptXA7-1725032208”,
    “database_name”: “app”,
    “process_id”: “85644”,
    “connection_from”: “10.0.251.226:46422”,
    “session_id”: “66d223c2.14e8c”,
    “session_line_num”: “1”,
    “command_tag”: “SELECT”,
    “session_start_time”: “2024-08-30 19:55:46 UTC”,
    “virtual_transaction_id”: “12/13”,
    “transaction_id”: “0”,
    “error_severity”: “ERROR”,
    “sql_state_code”: “53100”,
    “message”: “could not write to file \“base/pgsql_tmp/pgsql_tmp85644.59\“: No space left on device”,
    “query”: “-- Order match rate (# of fulfillments)\n-- total charges / unfavorable charges / favorable charges / net errors\nwith latest_reconciliation_runs as (\n\tselect a.invoice_id,\n\t\t\t\ta.id,\n\t\t\t\ta.start_time\n\tfrom\n\t\t(select \tinvoice_id,\n\t\t\t\t\tid,\n\t\t\t\t\tstart_time,\n\t\t\t\t\trow_number() over (partition by invoice_id order by start_time desc) as row\n\t\tfrom \t\tcdm.reconciliation_run\n\t\twhere \t\tstatus = ‘COMPLETED’) a\n\twhere a.row = 1\n)\nselect \t\tli.number,\n\t\t\tcount(distinct sf.sales_order_id) || ‘/’ || count(distinct f.sales_order_number) as order_match,\n\t\t\tround((100.0 * count(distinct sf.sales_order_id)::decimal / count(distinct f.sales_order_number)::decimal), 2) || ‘%’ as order_match_rate,\n\t\t\tsum(sc1.total_cost)\nfrom \t\tcdm.logistics_invoice li\njoin\t\tcdm.fulfillment f on li.id = f.logistics_invoice_id\nleft join \tcdm.synthetic_fulfillment sf on f.id = sf.real_fulfillment_id\njoin \t\tlatest_reconciliation_runs lrr on li.id = lrr.invoice_id\njoin \t\tcdm.reconciled_service_charge rsc on lrr.id = rsc.reconciliation_run_id\njoin \t\tcdm.service_charge sc1 on rsc.real_service_charge_id = sc1.id\nwhere \t\tli.number = ‘I-40468’\ngroup by \t1\norder by \t1”,
    “application_name”: “DBeaver 24.1.5 - SQLEditor <Script-6.sql>“,
    “backend_type”: “client backend”,
    “query_id”: “0"
  }
}

Query:

WITH latest_reconciliation_runs AS (
    SELECT invoice_id,
           id,
           start_time
    FROM (
        SELECT invoice_id,
               id,
               start_time,
               ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY start_time DESC) AS row
        FROM cdm.reconciliation_run
        WHERE status = 'COMPLETED'
    ) a
    WHERE a.row = 1
)
SELECT li.number,
       COUNT(DISTINCT sf.sales_order_id) || '/' || COUNT(DISTINCT f.sales_order_number) AS order_match,
       ROUND((100.0 * COUNT(DISTINCT sf.sales_order_id)::decimal / NULLIF(COUNT(DISTINCT f.sales_order_number)::decimal, 0)), 2) || '%' AS order_match_rate,
       SUM(sc1.total_cost)
FROM cdm.logistics_invoice li
JOIN cdm.fulfillment f ON li.id = f.logistics_invoice_id
LEFT JOIN cdm.synthetic_fulfillment sf ON f.id = sf.real_fulfillment_id
JOIN latest_reconciliation_runs lrr ON li.id = lrr.invoice_id
JOIN cdm.reconciled_service_charge rsc ON lrr.id = rsc.reconciliation_run_id
JOIN cdm.service_charge sc1 ON rsc.real_service_charge_id = sc1.id
WHERE li.number = 'I-40468'
GROUP BY li.number
ORDER BY li.number;

What primary components of the stack does this relate to?

terraform

Code of Conduct

[X] I agree to follow this project's Code of Conduct

fullykubed commented 2 months ago

@wesbragagt Here is what I'd recommend:

If temp_file_limit is set to -1, postgres thinks that it has unlimited disk space for intermediate calculations. If you are running expensive operations, this can run into issues like you are seeing. I'd recommend you set to a static number (10-25GB) and ensure you have enough disk space allocated for (a) that temporary storage, (b) you WAL logs, and (c) the main database.
Usually lots of disk space is used if you have expensive intermediate calculations. To optimize this, use EXPLAIN (VERBOSE, BUFFERS, ANALYZE) to diagnose bottlenecks and space usage by the query. In particular, you should ensure you have indices on each of the fields that you are sorting, joining, partitioning, and grouping by.
Is it possible that the result set from this query is massive? If so, you might attempt setting a LIMIT.

wesbragagt commented 2 months ago

Noted the suggestions and I'm going to be taking action on tuning it. Thank you for the clear recommendation @fullykubed.

Panfactum / stack