49 lines
2.5 KiB
Markdown
49 lines
2.5 KiB
Markdown
# LiteLLM Spend & Cost Analysis Queries
|
|
|
|
When investigating high model costs or checking the effective price of models routed through `admin-ai`, you can query the `LiteLLM_SpendLogs` table directly on the AI server (178.156.167.181).
|
|
|
|
*Note: Column names in this table use camelCase (e.g., `"startTime"`), which require double quotes in Postgres.*
|
|
|
|
## 1. Effective Cost per 1 Million Tokens (Last 72 Hours)
|
|
|
|
This query groups by model and provider, calculates the actual effective cost per 1M tokens based on recorded spend, and filters out trivial usage (<10k tokens). This is the best way to compare real-world pricing for models like `deepseek-v4-pro` vs `deepseek-chat` to make cost-effective primary model recommendations.
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra root@178.156.167.181 \
|
|
"docker exec litellm_postgres psql -U litellm -d litellm_db -At -F '|' -c \"
|
|
SELECT
|
|
COALESCE(model_group,'') as model,
|
|
COALESCE(custom_llm_provider,'') as provider,
|
|
COUNT(*) as calls,
|
|
ROUND(SUM(spend)::numeric,6) as total_spend_usd,
|
|
SUM(total_tokens) as tokens,
|
|
ROUND((SUM(spend)/NULLIF(SUM(total_tokens),0)*1000000)::numeric,6) AS effective_usd_per_1M_tokens
|
|
FROM \\\"LiteLLM_SpendLogs\\\"
|
|
WHERE \\\"startTime\\\" >= NOW() - INTERVAL '72 hours' AND model_group <> ''
|
|
GROUP BY model_group, custom_llm_provider
|
|
HAVING SUM(total_tokens) > 10000
|
|
ORDER BY effective_usd_per_1M_tokens ASC NULLS LAST;\""
|
|
```
|
|
|
|
## 2. Daily Spend by User & Session (Context Size Investigation)
|
|
|
|
High API costs are often driven by enormous prompt contexts sent repeatedly. This query finds the exact sessions and models burning the most spend today.
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra root@178.156.167.181 \
|
|
"docker exec litellm_postgres psql -U litellm -d litellm_db -At -F '|' -c \"
|
|
SELECT
|
|
COALESCE(session_id,'') as session,
|
|
COALESCE(\\\"user\\\",'') as \\\"user\\\",
|
|
model_group,
|
|
COUNT(*) as calls,
|
|
ROUND(SUM(spend)::numeric,4) as spend,
|
|
SUM(prompt_tokens) as prompt_tokens,
|
|
SUM(completion_tokens) as completion_tokens
|
|
FROM \\\"LiteLLM_SpendLogs\\\"
|
|
WHERE \\\"startTime\\\" >= DATE_TRUNC('day', NOW())
|
|
GROUP BY session_id, \\\"user\\\", model_group
|
|
ORDER BY SUM(spend) DESC NULLS LAST LIMIT 20;\""
|
|
```
|
|
|
|
If you see a session averaging 250,000+ `prompt_tokens` per call, the agent's conversation history has grown too large for expensive models like `gpt-5.5` or `claude-sonnet-4-6`. The immediate fix is to start a `/new` session to drop context, or swap the primary model to a cheaper alternative. |