Monitoring Business Metrics Directly from the Database with Prometheus SQL Exporter – ITFROMZERO

Table of Contents

When the Boss Asks “What’s Today’s Revenue?” and the Nightmare of Manual SQL Queries

Early in my career, I found myself in a rather comical situation. Every Monday morning, my boss would message me: “Can you check how many new users we had over the weekend? What’s the success rate of the orders?”. So, I would clunkily SSH into the server, open the terminal, type SELECT COUNT(*) until my fingers were sore, and then copy-paste the results into Excel for him.

At that time, I wondered: Why does the system have a fancy Grafana setup for monitoring CPU and RAM, but we aren’t putting these business figures on a dashboard? If we did, the boss could just look at the screen. I would also be free to do other things instead of being a “human reporting machine”.

However, the Developer team is usually busy hitting deadlines for new features. They don’t have 2-3 days to integrate a Prometheus Client into every line of application code. This is where Prometheus SQL Exporter comes in as a quick-and-easy yet extremely effective solution for Ops folks.

Why You Shouldn’t Modify App Code to Get Business Metrics?

The “textbook” way is to use a Prometheus library (like client_golang or client_python) directly in the code. But in reality, implementation often hits three major hurdles:

Legacy Code: Old applications with code so messy that no one dares touch it for fear of a chain-reaction of bugs.
Dev Resource Intensive: Adding metrics, writing test cases, and waiting for deployment can take a whole work week.
Dependency: Every time the boss wants a new metric, you have to ask Devs to modify the code, rebuild the image, and redeploy from scratch.

Business data usually already exists in database tables like MySQL or PostgreSQL. So why not pull it directly from there for speed?

Common Solutions and Their Drawbacks

Before settling on SQL Exporter, I tried a few other methods:

Running Python Cronjobs: A script queries the DB and pushes results to Pushgateway. This works okay at first. However, when the number of metrics reaches 50-100, managing these scripts becomes a maintenance nightmare.
Using Telegraf SQL Input: This is a powerful tool, but the configuration is a bit cumbersome if your team is already used to a pure Prometheus ecosystem.

The Optimal Solution: Prometheus SQL Exporter

This tool acts as a “translator”. It connects to the database, runs the SQL commands you define, and then exposes the data at a /metrics endpoint for Prometheus to scrape.

The biggest plus is that you don’t need to touch a single line of application code. Just write a YAML configuration file and you’re done. It’s extremely safe for stable production systems.

Step 1: Prepare the Database Account

Don’t use the root account. Create a user with only SELECT privileges on the necessary tables to ensure security.

-- Example for MySQL
CREATE USER 'sql_exporter'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON your_db.* TO 'sql_exporter'@'%';
FLUSH PRIVILEGES;

Step 2: Configure SQL Exporter (config.yml)

This is the most important part. Suppose you need to monitor the number of orders by status to see if the payment system is bottlenecked.

# config.yml
jobs:
  - name: "business_metrics"
    interval: '1m' # Run query every 60 seconds
    connections:
      - 'sql_exporter:secure_password@tcp(db_host:3306)/your_db'
    queries:
      - name: "orders_total"
        help: "Total number of orders by status"
        labels:
          - "status"
        values:
          - "count"
        query: |
          SELECT status, count(*) as count 
          FROM orders 
          GROUP BY status;

SQL Exporter will automatically convert the results into metrics like orders_total{status="completed"} or orders_total{status="pending"}.

Step 3: Quick Deployment with Docker Compose

Using Docker helps you manage versions and maintain a consistent environment. When you need to update an SQL query, simply edit the config file and restart the container in seconds.

version: '3'
services:
  sql-exporter:
    image: burningalchemist/sql_exporter:latest
    volumes:
      - ./config.yml:/config.yml
    command:
      - "--config.file=/config.yml"
    ports:
      - "9399:9399"
    restart: always

Real-world Experience: Mistakes That Can Crash Your System

Through many large projects, I’ve learned three painful lessons to avoid service disruptions:

1. Avoid Heavy Queries on Large Tables

The biggest mistake is running COUNT(*) on tables with 50-100 million rows without proper indexing. This can cause Database CPU to spike to 100% immediately.
Advice: Always use EXPLAIN to check your query. If the table is too large, query from a Slave (Read-replica) to avoid affecting end-users.

2. Beware of High Cardinality

Never put user_id or order_id into a metric label. If you have 1 million users, Prometheus will have to store 1 million different time series. This will exhaust the Prometheus server’s RAM in just a few hours.

3. Data Scraping Frequency (Interval)

Business data doesn’t need to be updated every second like CPU metrics. Instead of every 15 seconds, set it to 1 minute or 5 minutes. This reduces database load and makes the dashboard run smoother.

Summary

Depending on your needs, choose the appropriate approach:

If you need to measure the latency of specific functions: Use Prometheus Client.
If you need to quickly track revenue or user counts: SQL Exporter is the #1 choice.

Putting Business Metrics on Grafana doesn’t just free up the Ops team; it also elevates the technical team’s value. When you can show a real-time revenue dashboard to the board of directors, the team’s voice carries much more weight.