MySQL and PostgreSQL Data Sources

Query relational databases in Grafana using SQL

MySQL and PostgreSQL Data Sources

Grafana provides native support for querying MySQL and PostgreSQL relational databases. Both data sources share similar features and SQL query capabilities.

Overview

The MySQL and PostgreSQL data sources support:

• Standard SQL queries for time series and table data
• SQL query builder and code editor
• Macros for time-based filtering and grouping
• Template variables from query results
• Multi-value variables

Source: public/app/plugins/datasource/mysql/ and public/app/plugins/datasource/grafana-postgresql-datasource/

Configuration

MySQL Configuration

1. Add Data Source

   Navigate to Configuration > Data Sources > Add data source > MySQL

2. Configure Connection

   Set connection parameters:

   • Host: localhost:3306
   • Database: mydatabase
   • User: Database username
   • Password: Database password
3. Additional Settings

   • Max open connections: Connection pool size (default: 100)
   • Max idle connections: Idle connection pool size (default: 2)
   • Max connection lifetime: Maximum connection duration (default: 14400s)

PostgreSQL Configuration

1. Add Data Source

   Navigate to Configuration > Data Sources > Add data source > PostgreSQL

2. Configure Connection

   Set connection parameters:

   • Host: localhost:5432
   • Database: postgres
   • User: Database username
   • Password: Database password
   • TLS/SSL Mode: disable, require, or verify-full
3. PostgreSQL Specifics

   • Version: Select PostgreSQL version for query compatibility
   • TimescaleDB: Enable if using TimescaleDB extension

Connection Settings

host · string · required

Database host and port (e.g., localhost:3306, db.example.com:5432)

database · string · required

Database name to connect to

user · string · required

Database username

password · string · required

Database password (stored encrypted)

sslmode · string

PostgreSQL only: SSL mode (disable, require, verify-ca, verify-full)

maxOpenConns · number · default: "100"

Maximum number of open connections to the database

maxIdleConns · number · default: "2"

Maximum number of idle connections

connMaxLifetime · number · default: "14400"

Maximum connection lifetime in seconds

Query Requirements

Queries must return specific columns based on visualization type:

Time Series Queries

Time series queries must return:

• A column named time, time_sec, or timestamp column (in UTC)
• One or more columns with numeric data type as values
• Optional: A column named metric for series names

Resultsets must be sorted by time.

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:12-28 and public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:12-27

Table Queries

Table queries can return any set of columns. Grafana displays them in a table panel.

SQL Macros

Grafana provides macros to simplify time-based queries:

MySQL Macros

$__time(column)

Converts a timestamp column to Unix timestamp:

SELECT $__time(created_at), value FROM metrics

Expands to:

SELECT UNIX_TIMESTAMP(created_at) as time_sec, value FROM metrics

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:35

$__timeEpoch(column)

Same as $__time(), returns Unix epoch:

SELECT $__timeEpoch(timestamp) FROM events

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:36

$__timeFilter(column)

Filters by dashboard time range:

SELECT * FROM metrics WHERE $__timeFilter(created_at)

Expands to:

SELECT * FROM metrics WHERE created_at BETWEEN FROM_UNIXTIME(1492750877) AND FROM_UNIXTIME(1492750877)

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:37

$__unixEpochFilter(column)

Filters Unix epoch timestamps:

WHERE $__unixEpochFilter(time_unix_epoch)

Expands to:

WHERE time_unix_epoch > 1492750877 AND time_unix_epoch < 1492750877

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:38

$__timeGroup(column, interval, [fillvalue])

Groups time into intervals:

SELECT 
  $__timeGroup(created_at, '5m'),
  AVG(value)
FROM metrics
GROUP BY time

MySQL expands to:

cast(cast(UNIX_TIMESTAMP(created_at)/(300) as signed)*300 as signed)

Optional fillvalue fills missing intervals:

• Literal value: 0, NULL
• previous: Use previous value

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:42-47

$__timeGroupAlias(column, interval)

Groups time and aliases as time:

SELECT 
  $__timeGroupAlias(created_at, '5m'),
  AVG(value) as avg_value
FROM metrics
GROUP BY time
ORDER BY time

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:48-50

PostgreSQL Macros

$__time(column)

Aliases column as time:

SELECT $__time(created_at), value FROM metrics

Expands to:

SELECT created_at as "time", value FROM metrics

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:34

$__timeEpoch(column)

Converts to Unix epoch:

SELECT $__timeEpoch(created_at) FROM events

Expands to:

SELECT extract(epoch from created_at) as "time" FROM events

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:35

$__timeFilter(column)

Filters by time range:

WHERE $__timeFilter(created_at)

Expands to:

WHERE created_at BETWEEN '2017-04-21T05:01:17Z' AND '2017-04-21T05:01:17Z'

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:36-39

$__timeGroup(column, interval, [fillvalue])

Groups time into buckets:

SELECT 
  $__timeGroup(created_at, '5m'),
  AVG(value)
FROM metrics
GROUP BY time

PostgreSQL expands to:

(extract(epoch from created_at)/300)::bigint*300

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:44-48

$__timeGroupAlias(column, interval)

Groups and aliases as time:

SELECT 
  $__timeGroupAlias(date_time_col, '1h'),
  sum(value) as value
FROM yourtable
GROUP BY time
ORDER BY time

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:56-66

Macro Value Functions

Macros that return values for custom conditionals:

MySQL:

• $__timeFrom() → FROM_UNIXTIME(1492750877)
• $__timeTo() → FROM_UNIXTIME(1492750877)
• $__unixEpochFrom() → 1492750877
• $__unixEpochTo() → 1492750877

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:79-86

PostgreSQL:

• $__timeFrom() → '2017-04-21T05:01:17Z'
• $__timeTo() → '2017-04-21T05:01:17Z'
• $__unixEpochFrom() → 1492750877
• $__unixEpochTo() → 1492750877

Source: public/app/plugins/datasource/grafana-postgresql-datasource/CheatSheet.tsx:68-76

Query Examples

Time Series Example

MySQL:

SELECT
  $__timeGroupAlias(time_date_time, '5m'),
  min(value_double) as value,
  'min' as metric
FROM my_data
WHERE $__timeFilter(time_date_time)
GROUP BY time
ORDER BY time

Source: public/app/plugins/datasource/mysql/CheatSheet.tsx:56-76

PostgreSQL:

SELECT 
  $__timeGroup(date_time_col, '1h'),
  sum(value) as value
FROM yourtable
WHERE $__timeFilter(date_time_col)
GROUP BY time
ORDER BY time

Multiple Series

Use the metric column to create multiple series:

SELECT
  $__timeGroupAlias(timestamp, '1m'),
  avg(response_time) as value,
  endpoint as metric
FROM http_requests
WHERE $__timeFilter(timestamp)
GROUP BY time, endpoint
ORDER BY time

Creates one series per endpoint.

Multiple Values

Return multiple value columns:

SELECT
  $__timeGroupAlias(timestamp, '5m'),
  avg(cpu_usage) as cpu,
  avg(memory_usage) as memory,
  'server-01' as metric
FROM system_metrics
WHERE $__timeFilter(timestamp)
GROUP BY time
ORDER BY time

The metric column is used as a prefix: server-01 cpu, server-01 memory.

Table Query

SELECT
  timestamp,
  hostname,
  status_code,
  response_time
FROM http_logs
WHERE $__timeFilter(timestamp)
ORDER BY timestamp DESC
LIMIT 100

Displays as a table in Grafana.

Aggregation by Label

SELECT
  $__timeGroupAlias(created_at, '10m'),
  status,
  COUNT(*) as value
FROM orders
WHERE $__timeFilter(created_at)
GROUP BY time, status
ORDER BY time

Template Variables

Query Variables

Populate variables from query results:

MySQL:

SELECT DISTINCT hostname FROM servers ORDER BY hostname

PostgreSQL:

SELECT DISTINCT region FROM instances WHERE active = true

Multi-Value Variables

Use IN clause with multi-value variables:

SELECT
  $__timeGroupAlias(timestamp, '5m'),
  AVG(cpu_usage) as value,
  hostname as metric
FROM metrics
WHERE 
  $__timeFilter(timestamp)
  AND hostname IN ($hostname)
GROUP BY time, hostname
ORDER BY time

Variable with Filters

SELECT DISTINCT datacenter 
FROM servers 
WHERE region = '$region' AND active = 1
ORDER BY datacenter

Performance Optimization

Use Indexes

Create indexes on time columns and filter columns:

CREATE INDEX idx_timestamp ON metrics(timestamp);
CREATE INDEX idx_hostname ON metrics(hostname, timestamp);

Limit Time Range

Always use time filters to reduce query scope:

WHERE $__timeFilter(created_at)

Use Connection Pooling

Configure appropriate connection pool sizes:

maxOpenConns: 100
maxIdleConns: 10

Aggregate at Database

Let the database do aggregations:

SELECT 
  $__timeGroup(time, '5m'),
  AVG(value)
GROUP BY time

TimescaleDB Support (PostgreSQL)

For PostgreSQL with TimescaleDB:

Enable TimescaleDB

Check “TimescaleDB” in data source settings for optimized time-series queries.

Hypertable Queries

SELECT 
  time_bucket('5 minutes', time) AS time,
  avg(value) as value
FROM metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY time_bucket('5 minutes', time)
ORDER BY time

Continuous Aggregates

SELECT 
  bucket,
  avg_value
FROM metrics_5min
WHERE bucket >= NOW() - INTERVAL '24 hours'
ORDER BY bucket

Troubleshooting

Connection failed

• Verify host and port are correct
• Check database firewall rules allow Grafana IP
• Confirm database is running: systemctl status mysql / postgresql
• Test connection from Grafana server: mysql -h host -u user -p
• Review database logs for connection errors

No data in time series

• Ensure query returns column named time or time_sec
• Verify time values are in UTC
• Check time range includes data: SELECT MIN(time), MAX(time) FROM table
• Confirm numeric value columns exist
• Results must be ordered by time: ORDER BY time

Query timeout

• Add indexes on filtered columns
• Reduce time range
• Use LIMIT clause for testing
• Check database slow query log
• Increase query timeout in database config

Variable not populating

• Verify query returns results in database client
• Check variable query syntax
• Review Grafana server logs for query errors
• Ensure proper permissions for user

Best Practices

1. Always filter by time: Use $__timeFilter() in WHERE clause
2. Create proper indexes: Index time columns and frequently filtered fields
3. Use macros: Leverage Grafana macros for time operations
4. Limit result sets: Use LIMIT for table queries
5. Use connection pooling: Configure appropriate pool sizes
6. Aggregate in database: Use GROUP BY instead of post-processing
7. Order results by time: Required for time series visualization
8. Monitor database performance: Watch for slow queries and optimize

Security Considerations

• Use read-only database users for Grafana
• Grant SELECT permission only
• Never use root/admin database accounts
• Use SSL/TLS for database connections in production
• Regularly rotate database passwords

Further Reading

• MySQL Official Documentation
• PostgreSQL Official Documentation
• TimescaleDB Documentation
• SQL Query Optimization