Skip to content

Features

Database connections

The SQL editor can query your own PostgreSQL and MySQL databases beside Orbit SQL: an admin adds a connection once, and anyone whose role holds databases.query picks it as the editor's source and writes that database's own SQL. Every query runs read-only and lands in the audit trail. The routes and shapes are CONTRACT.md Addendum v0.24.

Supported engines:

  • PostgreSQL 12 or later, Amazon RDS for PostgreSQL and Aurora PostgreSQL;
  • MySQL 8.0 or later, MariaDB 10.5 or later, Amazon RDS for MySQL and Aurora MySQL.

Before you start: ORBIT_SEAL_KEY

orbit-api keeps each connection's password and CA certificate encrypted with AES-256-GCM, under a key derived with HKDF-SHA256 from ORBIT_SEAL_KEY. Without that variable it keeps no password: the database routes answer 503 not_configured, GET /api/v1/databases answers configured: false, and the Databases page explains how to set it.

The key must be at least 32 bytes. Generate it once and keep it in your secret store:

bash
SEAL_KEY=$(openssl rand -base64 48)helm upgrade sreorbit deploy/helm/sreorbit --reuse-values \  --set-string secrets.sealKey="$SEAL_KEY"

When you give no secrets.sealKey, the chart keeps the key its live Secret already holds, or draws one at install, and orbit-api reads it as ORBIT_SEAL_KEY. A tool that renders the chart without the cluster (helm template, Argo CD) draws a new key on every render: set secrets.sealKey or keep the Secret yourself there. If you keep the Secret yourself, add an ORBIT_SEAL_KEY key to it. A different key makes every stored password unreadable: there is no key rotation, so after a change an admin enters each connection's password again.

A read-only user

The editor is read-only in three layers, and the last one is yours:

  1. orbit-api checks the statement before it touches the database: exactly one statement, whose first keyword is SELECT, WITH, EXPLAIN, SHOW, VALUES, TABLE, DESCRIBE or DESC, no writing keyword outside quotes and comments, and none of the functions that touch the server (pg_terminate_backend, pg_read_file, dblink, set_config, load_file, get_lock, every pg_*advisory* lock and the rest of the list in the contract). A read that locks rows is refused too: FOR UPDATE and FOR NO KEY UPDATE as writes, FOR SHARE, FOR KEY SHARE and LOCK IN SHARE MODE because MySQL takes their row locks inside a read-only transaction, and a PostgreSQL advisory lock outlives the transaction on the pooled connection. A refusal is 400 sql_error and says what was refused.
  2. Each query runs in a read-only transaction that is rolled back afterwards.
  3. The database user can only read. Create one for Orbit, and nothing else uses it.

PostgreSQL, as a user that can create roles (on RDS, the master user):

sql
CREATE ROLE orbit_ro LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE orders TO orbit_ro;
GRANT USAGE ON SCHEMA public TO orbit_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO orbit_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO orbit_ro;
ALTER ROLE orbit_ro SET default_transaction_read_only = on;

MySQL and MariaDB:

sql
CREATE USER 'orbit_ro'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT, SHOW VIEW ON orders.* TO 'orbit_ro'@'%';

Replace orders with your database and change-me with a generated password. Repeat the GRANT lines for every schema or database the editor should see.

The connection test tells you when the user can write. On PostgreSQL that is a superuser, any INSERT, UPDATE, DELETE or TRUNCATE grant in information_schema.role_table_grants, or CREATE on the database; on MySQL it is INSERT, UPDATE, DELETE, CREATE, DROP, ALTER or ALL in SHOW GRANTS. The page then says so in red. Saving is still allowed, and the first two layers still hold, but fix it.

Adding a connection

Admin → Databases (/deck/admin/databases, behind settings.manage) lists the connections and adds, edits, tests and deletes them. The form asks for:

  • a name, 1 to 80 characters and unique, which is how the editor lists it;
  • the engine, host, port, database and user;
  • the password, which is never shown again, never logged and never audited; on an edit, leaving it empty keeps the stored one, unless the edit changes the engine, host, port, user, TLS mode or CA: the stored password goes only to the server it was typed for, so that edit takes it again (400 invalid_request). The monitoring user's password follows the same rule;
  • the TLS mode, and an optional CA certificate in PEM for a database signed by a private CA;
  • who may query it: roles ticked here, or, with none ticked, every role that holds databases.query. A role that is not listed does not see the connection at all. A role can also list the connections it reaches, from Admin → Roles: see "A role for a database team".

Saving tests the connection first, with a 10 s timeout and SELECT 1, and refuses with the database's reason when the test fails. orbit-api resolves the host before every connect and refuses one that resolves to a link-local or cloud metadata address (169.254.0.0/16, fe80::/10, fd00:ec2::254, fd00:ec2::23, 168.63.129.16, 100.100.100.200), an unspecified, multicast or broadcast address: no database lives there, and the metadata service hands out the node's credentials. A loopback, private (10/8, 172.16/12, 192.168/16, fc00::/7) or shared (100.64/10) address is refused too, unless you tick On a private network on the connection: tick it for a database in the cluster, in your VPC or behind a sidecar proxy. orbit-api resolves the name once per connect and connects to the address it checked, so a name that changes its answer a moment later reaches nothing new. Creating, changing and deleting a connection are audited as database.create, database.update (with the names of the fields that changed) and database.delete, with the target type database.

TLS modes:

ModeWhat it does
verify-fullThe default. Encrypted, with the certificate and the hostname checked against the public roots, the Amazon RDS global CA bundle and the CA you gave
requireEncrypted, without checking the certificate
disablePlaintext: the password and every result cross the network in the clear

Amazon RDS and Aurora

  • verify-full works as is: orbit-api carries the RDS global CA bundle, so no CA is needed.
  • The database's security group must allow orbit-api on the database port: from the worker nodes' security group, or from the pod's own security group when you use security groups for pods.
  • The chart's NetworkPolicy lets orbit-api out only where it has to go. Add the database's address range, with its port, to components.orbit-api.databases.egress:
yaml
components:
  orbit-api:
    databases:
      egress:
        - cidr: 192.0.2.0/24
          ports: [5432, 3306]

Use the VPC's private range, or the database subnets' ranges for a tighter rule. Without an entry the connection test fails with a timeout, because the policy drops the packets.

Querying

In the SQL editor, the source picker lists SRE Orbit first, then the connections you may query. On a connection:

  • the SQL is the database's own dialect, run natively, and the answer has the same shape as Orbit SQL, so the grid, the chart and the CSV export work unchanged;
  • the schema panel and completion come from the database's information_schema, without its system schemas: at most 2,000 tables and 20,000 columns, cached 5 minutes;
  • the examples gallery and "Write with Nocturne" are hidden: they are written for Orbit's tables;
  • a saved query keeps its connection, and a share link carries it.

Subqueries work as each database runs them: IN (SELECT ...), EXISTS and NOT EXISTS, derived tables in FROM, scalar subqueries in the select list or a comparison, ANY and ALL, LATERAL on PostgreSQL, and WITH, recursive or not. The statement is still one statement, in the read-only transaction, under the time limit, and the same words are refused inside a subquery as outside one.

Types come back as Orbit SQL's six: integers as int (as a string past ±2^53), real, double and numeric as float, bool, dates and times as timestamp in UTC, json, jsonb and arrays as json, and everything else as string, bytea as hex. NULL is null.

Limits:

  • a query returns 1,000 rows unless you raise it, up to 10,000, and says when it was truncated;
  • it may run for 30 s, or up to 60 s if you allow it; past that the database and orbit-api both cancel it and the answer is 408 timeout;
  • a text cell stops at 64 KiB, and an answer over 32 MiB is 413 too_large;
  • a connection runs at most 2 queries at once, and orbit-api at most 4 across all of them.

Errors: the database's own error is 400 sql_error, with its position when PostgreSQL gives one; a connect, TLS or sign-in failure is 502 database_unreachable with the reason, never the password; an unknown connection, or one your role cannot see, is 404 not_found.

Every query on a connection, success or failure, is audited as database.query against that connection, with the first 4 KiB of the SQL, the rows, whether it was truncated, the time it took and, on failure, the error.

Nocturne on a database

Nocturne can answer questions about the data in a connection, not only about the cluster: it reads the connection's tables and columns, writes one SELECT in that database's dialect, and reads the answer back. It runs as the person asking, never with credentials of its own, and only where an admin opened the connection to it. The routes and shapes are CONTRACT.md Addendum nocturne-db.

Who can. A person whose role holds both nocturne.use and databases.query, on a connection their role may query (the connection's "Who can query"). With either permission missing the query route answers 403 forbidden; with the nocturne feature off or without its plan, 403 feature_disabled or 403 plan_required.

Opening a connection to Nocturne. Every connection starts closed. In Admin > Databases, the Nocturne column's switch opens one (settings.manage), and its link lists the column names Nocturne always masks there, on top of its own rules, one per line. A closed connection answers 403 nocturne_off, and Nocturne tells the person who to ask. Each change is audited as database.nocturne. On Aurora or with a read replica, add a second connection to the reader endpoint (the cluster-ro- endpoint, or the replica's) and open only that one: Nocturne's queries then never run on the writer.

What Nocturne masks

The answer goes on to the model's provider, a third party, so orbit-api masks it before it leaves, and the person sees the same masked answer in the chat:

  • By column. A column is masked whole ([masked]) when its name, or the name of the table column it comes from, reads as personal data or a secret: email, phone, mobile, WhatsApp, CPF, CNPJ, RG, CNH, SSN, NIF, passport, document, IBAN, card number, CVV, PAN, password, passphrase, passcode, *_pass, senha, secret, segredo, token, bearer, signature, salt, pepper, OTP, PIN, any *_key, API key, access key, private key, chave, credentials, *_creds, basic auth, authorization, tax and national ids. The source column is the one the database names for each result column, so SELECT email AS contact is masked too. When the statement names such a column, or selects every column with *, a text column computed or renamed on the way (upper(email), a CTE's column list) is masked whole as well.
  • By value, in every other cell, JSON included: an email ([email]), a CPF or CNPJ formatted or as bare digits with a valid check digit ([cpf], [cnpj]), an RG ([document]), a card number that passes the Luhn check ([card]), a phone number written with +, parentheses or separators, or a Brazilian mobile number as bare digits ([phone]), a bcrypt, Argon2 or PBKDF2 hash, a hex string of 32 characters or more, a 24-character token of mixed case and digits ([secret]), and the credential shapes the cluster tools already redact (JWTs, bearer tokens, AWS keys, private keys, a connection string's password, password=...). A JSON key that reads as sensitive masks its value.
  • The admin's names, matched in any case, mask a column whole.
  • Errors. A database error can quote a value (Duplicate entry '...'): its message is masked the same way before it leaves. The SQL itself is the model's own and is not masked.

Masking reads names and shapes; it cannot know that a column called notes holds a customer's address. Add such columns to the connection's list.

What keeps a query from slowing the database

Before the database runs the statement, orbit-api:

  1. holds it to the SQL editor's statement gate, and to SELECT or WITH only, with no locking read (FOR SHARE, LOCK IN SHARE MODE) and none of sleep, benchmark, pg_sleep, get_lock or the advisory lock functions;
  2. gives it a LIMIT of 200 rows, or keeps a smaller one, and refuses an OFFSET past 100,000 rows;
  3. reads its plan with EXPLAIN FORMAT=JSON (MySQL) or EXPLAIN (FORMAT JSON) (PostgreSQL), never ANALYZE, and refuses it with 400 plan_refused when it would read more than 100,000 rows of a table (a full scan, a full index scan, the inner side of a join once per outer row), sort, hash or build a temporary table of more than 100,000 rows, or read more than 100,000 rows in all. The refusal says which table, how many rows, and that table's indexes, so the model rewrites it on an indexed column. On PostgreSQL a full scan's size is the table's reltuples, and a scan with no filter under a small LIMIT counts as the rows it returns;
  4. runs one Nocturne query at a time per connection (a second waits up to 2 s, then 429 busy), for at most 5 s, cancelled by the database and by orbit-api past that, in the same read-only transaction the SQL editor uses.

MySQL's plan does not show that a LIMIT stops a table scan, so SELECT * FROM big LIMIT 10 is refused on MySQL; ordering by an indexed column (ORDER BY id DESC LIMIT 10) passes.

The 100,000 rows and the 5 s come from a measurement on MySQL 8.4 and PostgreSQL 16 in Docker with 2 CPUs, a 4,000,000-row table and a steady load of indexed reads and writes beside the queries. What the guards did to that load:

Beside 400 point reads and writes a second (80% reads, 8 connections), two streams of statements ran at once, each pausing 1 s between statements, as a model's turns do; each run lasted 60 s, three runs per row on MySQL and two on PostgreSQL, interleaved, and the table gives their median. p95 is the background workload's as its client timed it; "MySQL's own p95" is the point read's QUANTILE_95 in performance_schema, which leaves the client and the network out.

RunMySQL p95MySQL p99MySQL's own p95PostgreSQL p95What the streams did
Nothing beside the workload2.48 ms2.93 ms0.263 ms2.59 ms-
Typical questions, through the guards2.58 ms (+4%)2.88 ms0.251 ms (-5%)2.25 ms (-13%)each read under 100,000 rows; 360 answered on MySQL
The same with no pause2.31 ms (-7%)2.73 ms0.251 ms (-5%)2.16 ms (-17%)12,815 answered on MySQL, one at a time
Full scans and sorts of the 4,000,000 rows, no guards (the SQL editor's path)3.27 ms (+32%)5.34 ms (+82%)0.316 ms (+20%)4.35 ms (+68%)each ran about 1.6 s on MySQL and 0.6 s on PostgreSQL, with its parallel workers, two at once
The same statements, through the guards2.22 ms (-11%)2.62 ms0.263 ms (+0%)2.36 ms (-9%)every one refused on its plan, in about 7 ms

The baselines ranged from 2.26 to 2.60 ms on MySQL and from 2.35 to 2.76 ms on PostgreSQL: a change under about 10% is this machine's noise, and every guarded row sits within it or below the baseline. Every unguarded run (3.25 to 3.51 ms on MySQL, 4.21 to 4.48 ms on PostgreSQL) sat above every baseline, and the PostgreSQL p99 went from 2.99 to 6.98 ms.

Before that, one statement every 2 s that read 10,000, 100,000, 1,000,000 or 4,000,000 rows took 6, 39, 328 and 1,343 ms of the database's time each on MySQL. Beside them the background p95 moved by +27%, +14%, +12% and +5%: in no order of the size, because one statement at a time uses one CPU at most. What the size changes is how long each statement holds that CPU, and with the guards an accepted statement reads at most 100,000 rows, about 40 ms here. The 5 s cap is for a plan that misleads the check: the slowest full scan here took about 1.6 s. One size fits both engines, so neither bound is a setting. The databases ran in Docker on 2 dedicated CPUs, MySQL with a 1 GiB buffer pool and PostgreSQL with 1 GiB of shared buffers, both holding the table in memory, and committed without an fsync each so that the shared disk's jitter would not drown the queries. A table larger than memory makes a scan read the disk as well, which these runs do not show.

Every Nocturne query is audited as database.nocturne.query against the connection: the person, the first 4 KiB of the SQL as it ran (with its LIMIT), the plan's verdict, the rows, whether they were cut, how many values were masked, the time and the outcome. A refusal before the database ran the statement is outcome denied, with the reason; a database error is failure.

Over the API

RouteNeedsBody and answer
GET /api/v1/databasesdatabases.query or settings.manage{configured, databases}
POST /api/v1/databasessettings.manageThe connection and its password → 201 {database, test}
PUT /api/v1/databases/{id}settings.manageThe same body → {database, test}
DELETE /api/v1/databases/{id}settings.manage204
POST /api/v1/databases/{id}/testsettings.manage{ok, serverVersion, readOnly, elapsedMs} or {ok, error}
GET /api/v1/databases/{id}/schemadatabases.query{tables, truncated}
POST /api/v1/sql/querydatabases.query{sql, limit, timeoutMs, database} → {columns, rows, stats}
POST /api/v1/sql/explaindatabases.query{sql, database} → {plan}
GET /api/v1/databases/nocturnedatabases.query or settings.manage{databases: [{id, name, engine, enabled, maskColumns}]}
PUT /api/v1/databases/{id}/nocturnesettings.manage{enabled, maskColumns} → {id, enabled, maskColumns}
POST /api/v1/databases/{id}/nocturne/querydatabases.query and nocturne.use{sql} → {columns, rows, stats, sql, limit, timeoutMs, plan, masked}

A caller with only databases.query sees each connection as {id, name, engine, database}.

Demo data

The public demo has one connection, demo-shop, labelled PostgreSQL: a shop with about 200 customers, 50 products and 2,000 orders over the last 90 days, in the tables customers, products, orders and order_items. Its examples open from the editor when it is the source. Adding, changing and testing connections are off in the demo.

Performance monitoring

orbit-api can watch a MySQL connection the way Performance Insights and Datadog Database Monitoring do, from performance_schema: which statements take the database's time, how many sessions are active, and what each one waits on. It works on Amazon RDS for MySQL 8.0 and 8.4, Aurora MySQL 3, and MySQL 8.0 or later that you run yourself. PostgreSQL is not monitored: saving a PostgreSQL connection with monitoring on answers 400. The routes and shapes are CONTRACT.md Addendum v0.28.

Monitoring signs in as a second database user, never the SQL editor's: it reads every session's statements, and the editor's user must not.

On the Free plan the collector runs and the Deck shows DB load, calls, latency and errors. Each statement's text, the live sessions, EXPLAIN and a statement's values need an Enterprise key: their routes answer 403 plan_required until one goes in. See docs/licensing.md.

What Orbit keeps, and what it never keeps

Orbit keeps:

  • counters per statement digest and per wait, on orbit-api's /metrics, which orbit-agent already scrapes as job="sreorbit", component="orbit-api" into orbit-stack;
  • the text MySQL normalized for each digest (DIGEST_TEXT, every literal replaced by ?), in orbit-api's memory only: at most 2,000 per connection, the least recently seen dropped first, and gone after a restart until the next poll;
  • the last second's active sessions, in memory.

Orbit never stores, logs or exports the literal text of a statement: QUERY_SAMPLE_TEXT, SQL_TEXT and PROCESSLIST_INFO. The collector never selects them. EXPLAIN reads one digest's QUERY_SAMPLE_TEXT when you ask for its plan, explains it, replaces the literals in the plan's conditions with ?, and drops the sample; the audit line holds the digest and the schema. A holder of databases.raw_sql can ask for one digest's statements with their values (below): they go to that reader's answer and nowhere else. The integration test runs statements carrying a marker string and greps /metrics, every log line, the audit trail and the routes' answers for it.

The series, all on orbit-api's metrics port:

SeriesLabelsFrom
orbit_dbm_updb1 while the collector's connection answers, else 0
orbit_dbm_performance_schemadb@@performance_schema
orbit_dbm_query_calls_totaldb, schema, digestCOUNT_STAR
orbit_dbm_query_seconds_totaldb, schema, digestSUM_TIMER_WAIT / 1e12
orbit_dbm_query_lock_seconds_totaldb, schema, digestSUM_LOCK_TIME / 1e12
orbit_dbm_query_cpu_seconds_totaldb, schema, digestSUM_CPU_TIME / 1e12, on 8.0.28 or later with the events_statements_cpu consumer on
orbit_dbm_query_errors_totaldb, schema, digestSUM_ERRORS
orbit_dbm_query_rows_sent_totaldb, schema, digestSUM_ROWS_SENT
orbit_dbm_query_rows_examined_totaldb, schema, digestSUM_ROWS_EXAMINED
orbit_dbm_query_rows_affected_totaldb, schema, digestSUM_ROWS_AFFECTED
orbit_dbm_query_no_index_totaldb, schema, digestSUM_NO_INDEX_USED
orbit_dbm_query_full_scans_totaldb, schema, digestSUM_SELECT_SCAN
orbit_dbm_query_tmp_disk_tables_totaldb, schema, digestSUM_CREATED_TMP_DISK_TABLES
orbit_dbm_active_seconds_totaldb, wait1 s samples: the seconds each active session spent, under its wait
orbit_dbm_query_active_seconds_totaldb, schema, digestThe same samples, per statement digest
orbit_dbm_collect_errors_totaldb, stageFailed steps: connect, consumers, digests, activity
orbit_dbm_sampled_seconds_totaldbSeconds the 1 s session sampler covered, its steps added up
orbit_dbm_digests_read_timestamp_secondsdbUnix time of the last digest table read: the per-digest counters hold MySQL's values as of then

db is the connection id, schema the statement's default schema ("" for none), and digest MySQL's 64-hex digest. Only the top ORBIT_DBM_TOP_DIGESTS digests by total time (default 100, from 1 to 1,000) carry per-digest series, so one connection adds about 12 series per digest plus one per wait seen. The _total series read MySQL's own counters: when MySQL truncates its digest table or restarts, rate() and increase() handle the reset.

rate(orbit_dbm_active_seconds_total[5m]) is average active sessions, what Performance Insights calls DB load:

promql
sum by (wait) (rate(orbit_dbm_active_seconds_total{db="db_7Hq2..."}[5m]))
topk(10, sum by (schema, digest) (rate(orbit_dbm_query_seconds_total{db="db_7Hq2..."}[5m])))

The provider's own statements

RDS and Aurora run their heartbeat, replica checks and metrics as rdsadmin, and on a quiet instance that work can be half the calls. Orbit hides by default what a customer account cannot run there and the catalog reads of a session with no default schema:

  • the active time of every rdsadmin session in orbit_dbm_active_seconds_total;
  • every per-digest series of a statement no customer account can run on that instance, while none of its calls has failed;
  • every per-digest series of a catalog read run with no default schema, while none of its calls has failed: a SHOW, or a SELECT of performance_schema, information_schema, mysql or sys tables or of @@GLOBAL or @@aurora_* variables that names no other schema. A session variable read such as SELECT @@session.transaction_read_only, which drivers run on every checkout, shows. Performance Insights reads the digest table, @@GLOBAL variables and the information_schema.rds_* views this way. Your app connects with its database as the default, so its statements carry that schema and show; the same read from your own SQL client with no database selected hides with the provider's.
StatementWhy no customer account runs it
INSERT, REPLACE, UPDATE or DELETE on a mysql.rds_* table, such as the heartbeatAWS locks the mysql schema against writes on Aurora MySQL 3 and on RDS for MySQL 8.0.36 and later. On an older RDS for MySQL these show.
SET @@sql_log_bin, SET sql_log_binA restricted session variable: it needs SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN, and AWS grants none of them to a customer account.
PURGE BINARY LOGSIt needs SUPER or BINLOG_ADMIN, which AWS grants to no customer account either.
SET LOCAL oscar_local_only_replica_host_status, SET @@aurora_enable_dd_accessAurora answers a customer session with error 1193, unknown system variable.

These series carry internal="true". A failed call may be a customer's try, so a digest with an error shows from the next poll on. Everything else shows, whoever runs it: COMMIT, SELECT ?, a SELECT of functions alone such as GET_LOCK or SLEEP, and any statement run from a default schema.

The collector leaves its own statements out of the top digests: it connects with performance_schema as its default schema and drops every digest of that schema. It drops EXPLAIN FORMAT=JSON too, which the Deck's Explain button runs (and so drops yours, if your app runs one). The statements around that plan (SET SESSION MAX_EXECUTION_TIME, START TRANSACTION READ ONLY, ROLLBACK) and the SQL editor's queries run as the editor's user on your schema and show, like any client's.

The collector counts a connection as RDS when its host ends in .rds.amazonaws.com or .rds.amazonaws.com.cn, and as Aurora when SELECT @@aurora_version answers; the version comes from VERSION() (a MariaDB version never counts as 8.0.36 or later) and @@aurora_version. On any other MySQL, Cloud SQL and Azure Database for MySQL included, nothing carries the label. Nothing is learned or kept: every poll decides from a digest's text and error count, so a restart of orbit-api changes nothing. A digest's label changes on its first failed call, and changes back when MySQL resets its digest table (a restart or a TRUNCATE), since the error count starts over at 0. The Deck merges a digest's labels, so no call falls between them.

The Deck leaves tagged series out of the cards, DB load by wait, Top SQL and Live sessions, on the fleet page and on each connection's, until you turn on Show provider statements (rdsadmin) (internal=1 in the URL). A query of your own does the same with internal!="true":

promql
sum by (schema, digest) (rate(orbit_dbm_query_calls_total{db="db_7Hq2...",internal!="true"}[5m]))

The Deck reads these counters raw and never averages rate() steps, which ramp up for a whole rate window after data starts:

  • Calls, time and errors per second are how far each counter grew between the first and the last digest read in the window, over the seconds between those reads (orbit_dbm_digests_read_timestamp_seconds). A hole in collection inside them counts, since MySQL keeps counting through it. A statement first seen after the first read (its digest's firstSeenMs) and no later than its own first read counts from its first call, since its counter started at zero inside the window; one that was older but entered the top digests later counts from its first read only. After a MySQL restart or a TRUNCATE of the digest table, firstSeenMs moves to the first call since then, after the statement's first read: the counter's drop counts as a reset, and only the calls since it add. An Aurora failover moves the endpoint to an instance whose counters ran on their own, with a firstSeenMs from before the drop: that drop adds no calls. If the new instance's counter is higher than the old one's, the page reads the difference as calls and that window's rates come out high. The fleet page's rates and a statement's charts count any drop as a reset.
  • Samples an orbit-api older than these two series wrote read at their step's time, with the session sampler counted as running from one such step to the next.
  • Load, the page's and each statement's, is the growth in active seconds over the growth in orbit_dbm_sampled_seconds_total: a time the session sampler was not running is neither load nor time.

The page names the time the reads cover: a collector that started 11 minutes before the end of a 1-hour window shows those 11 minutes' rates.

What a session waits on

Every second the collector reads the foreground sessions other than its own that are neither idle (Sleep) nor daemons, and counts each under one wait:

waitWhen
row_lockThe session has a pending InnoDB row-lock request in performance_schema.data_lock_waits. Orbit's own label, outside MySQL's instrument names on purpose.
A wait event, such as wait/io/table/sql/handlerThe session's latest wait in events_waits_current is still in progress.
CPUAnything else: the latest wait ended, so the session runs.

Measured on MySQL 8.4.11 with the default instruments, and again with every wait/% instrument on: a session blocked on a row lock reads in events_waits_current as wait/io/table/sql/handler, the same name a full table scan shows while it reads rows. row_lock tells the two apart. With the default instruments a SELECT SLEEP() reads as CPU.

Overhead on the database

performance_schema reserves most of its memory when MySQL starts. Measured on the mysql:8.4 image (MySQL 8.4.11) in Docker with --cpus 1 and max_connections 151, as the container's memory after a restart (docker stats) and as performance_schema's own count (SELECT SUM(CURRENT_NUMBER_OF_BYTES_USED) FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%'):

SettingContainer memoryperformance_schema count
performance_schema = OFF192 MiB0
On, every size at its default436 MiB (+244)224.5 MiB
On, the four *_history_long_size at 0382 MiB (+189)183.9 MiB
On, the three lengths at 4096570 MiB (+378)342.4 MiB

max_connections 60 instead of 151 changed none of these numbers.

db.t4g.micro (1 GiB): leave performance_schema off. RDS gives InnoDB's buffer pool three quarters of the instance's memory by default, and 189 MiB or more on top leaves a 1 GiB instance short of memory. On db.t4g.small (2 GiB), turn it on with the four *_history_long_size parameters at 0. From db.t4g.medium up, the default sizes. Set the three lengths to 4096 only with memory to spare: they keep statements up to 4 KiB whole, for 134 MiB more.

The collector's own cost, measured on the same container. Server time per query is MySQL's own count for the collector's statements (AVG_TIMER_WAIT, QUANTILE_95 and MAX_TIMER_WAIT in events_statements_summary_by_digest):

QueryEveryIdle10 sessions on one row lockDigest table full (9,999)
Active sessions1 s0.82 ms average, 2.2 ms max2.1 ms average, 5.9 ms max1.4 ms average, 6.8 ms max
Top 100 digests10 s1.1 ms average1.5 ms average20 ms average, 69 ms max
setup_consumers10 s0.34 ms average0.42 ms average0.38 ms average

orbit-api (release build, reading a small k3d cluster) on that container: 38 MiB of memory and 0.05% of one core with no connection monitored; 40 MiB and 0.18% with one; 44 MiB and 1.0% with seven (45 MiB and 0.6% with the workload running), each over 60 s from /proc.

1. The parameter group

Every parameter below is static: it takes effect when the instance reboots. Set performance_schema to 1 explicitly. A parameter group that shows 0 with Source system stays off, Performance Insights or not, and the check reads @@performance_schema itself.

RDS for MySQL 8.4 (family mysql8.4), in the instance's own DB parameter group:

ParameterValueWhen
performance_schema1Always
performance_schema_events_statements_history_long_size0db.t4g.small
performance_schema_events_waits_history_long_size0db.t4g.small
performance_schema_events_stages_history_long_size0db.t4g.small
performance_schema_events_transactions_history_long_size0db.t4g.small
max_digest_length, performance_schema_max_digest_length, performance_schema_max_sql_text_length4096Optional, with memory to spare; a statement past 1,024 bytes of tokens then has no link to traces

RDS for MySQL has no parameter for the consumers, and a reboot turns events_waits_current off. The procedure in step 2 turns them on, and Orbit calls it each time it connects.

Aurora MySQL 3 (family aurora-mysql8.0), in the instance's DB parameter group, not the cluster's:

ParameterValue
performance_schema1
performance_schema_consumer_events_statements_current1
performance-schema-consumer-events-waits-currentON (dash spelling, values ON and OFF)
performance_schema_consumer_statements_digest1
performance_schema_consumer_global_instrumentation1
performance_schema_consumer_thread_instrumentation1

Aurora MySQL 3.08 has no parameter for events_statements_cpu, which CPU time per digest needs: create the procedure from step 2 there too, and Orbit turns it on.

MySQL you run yourself, in my.cnf under [mysqld], then restart mysqld:

ini
performance_schema = ON
performance-schema-consumer-events-statements-current = ON
performance-schema-consumer-events-waits-current = ON
performance-schema-consumer-statements-digest = ON
performance-schema-consumer-events-statements-cpu = ON

2. The monitoring user

As the admin user (on RDS, the master user), on the writer:

sql
CREATE USER 'orbit_monitor'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT ON performance_schema.* TO 'orbit_monitor'@'%';
ALTER USER 'orbit_monitor'@'%' WITH MAX_USER_CONNECTIONS 5;

CREATE DATABASE IF NOT EXISTS orbit;
DELIMITER $$
CREATE PROCEDURE orbit.enable_consumers()
    SQL SECURITY DEFINER
BEGIN
    UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
     WHERE NAME IN ('statements_digest', 'events_statements_current', 'events_waits_current', 'events_statements_cpu');
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE orbit.enable_consumers TO 'orbit_monitor'@'%';

Replace change-me with a generated password. The procedure runs with its creator's rights, so the monitoring user turns consumers on without UPDATE on performance_schema.

The user needs no PROCESS. Measured on MySQL 8.4.11: a user with SELECT on performance_schema alone sees every user's sessions in threads, events_waits_current, events_statements_current and data_lock_waits, and the same digest summary, with the same rows as a user that holds PROCESS. PROCESS governs SHOW PROCESSLIST and information_schema.PROCESSLIST, which Orbit never reads.

A read replica receives the user and the procedure through replication. Consumers belong to each instance, and the procedure runs on a replica with read_only and super_read_only on (measured), so add each replica you want to watch as a connection of its own. For Aurora, add the instance endpoint (<instance>.<id>.<region>.rds.amazonaws.com), not the cluster endpoint: a cluster endpoint moves to another instance on failover, and the Deck's CloudWatch tiles match the instance identifier, the endpoint's first label, so a cluster endpoint shows none.

MAX_USER_CONNECTIONS 5 covers the collector's one connection and the moment a check or a plan opens another.

3. Turning it on in Orbit

A connection's create and update bodies take monitor, and its manager view answers it without the password:

json
{"monitor": {"enabled": true, "user": "orbit_monitor", "password": "change-me"}}
  • user must differ from the connection's user. On an edit, an absent or "" password keeps the stored one, and an absent monitor keeps the stored settings.
  • The password is sealed with ORBIT_SEAL_KEY like the editor's, and GET answers {"enabled": true, "user": "orbit_monitor", "hasPassword": true}.
  • With enabled on, saving also connects as the monitoring user, and refuses with 400 connection_failed when that fails. The host, port, TLS mode and CA are the connection's.
  • A change to monitor is audited in database.update's changed fields as monitor.

Then run the check, POST /api/v1/databases/{id}/monitor/check (settings.manage). It connects as the monitoring user, calls orbit.enable_consumers() when a consumer is off, and answers each check with what to do when it fails, worded for RDS for MySQL, Aurora MySQL or MySQL you run yourself:

CheckPasses when
connectThe monitoring user signs in
performance_schema@@performance_schema is 1
consumer_statements_digestThe statements_digest consumer is on
consumer_events_statements_currentThe events_statements_current consumer is on
consumer_events_waits_currentThe events_waits_current consumer is on
grant_select_performance_schemaThe user reads events_statements_summary_by_digest and threads
digest_lengthmax_digest_length, performance_schema_max_digest_length and performance_schema_max_sql_text_length are 1024 or more

How the collector runs

  • One collector per monitored connection, inside orbit-api (one replica, by design), on one MySQL connection of its own to the performance_schema schema, with the connection's TLS and MAX_EXECUTION_TIME 3000 ms on its session. Each query has 5 s.
  • On connect it reads the version, @@performance_schema and whether SUM_CPU_TIME exists, and calls orbit.enable_consumers(); where the procedure does not exist the call fails and the collector carries on.
  • Every 10 s it reads setup_consumers, calling the procedure again when a consumer is off, then the top digests, leaving out statements whose schema is performance_schema, its own among them. Every second it reads the active sessions; with events_waits_current or events_statements_current off it skips them rather than count every session as CPU.
  • After a failure it closes the connection and opens a new one 1 s later, doubling up to 60 s; orbit_dbm_up reads 0 meanwhile and orbit_dbm_collect_errors_total counts the stage. With performance_schema off or a grant missing it tries again every 60 s.
  • Saving the connection restarts its collector on a new connection. Turning monitoring off or deleting the connection stops it and removes its series. Switching the Database connections feature off, or Orbit SQL with it, stops every collector and closes its connection; switching it back on starts them again.

GET /api/v1/databases/monitor gives each connection's state: collecting; setup (connected, but performance_schema or a consumer is off, or a grant is missing; error says which); down (no connection; error says why); or off.

Calls per interval

Top SQL prints each statement's calls per second over the window, and beside it how many times it ran in each whole interval: the average over the window's buckets, a line of every bucket (oldest first) and the busiest bucket. The picker above the table offers the intervals that fit the page's window 2 to 240 times, out of 1m, 5m, 15m and 1h, and starts on the first that fits 30 times or fewer: 1m for 15 minutes, 5m for an hour, 15m for 6 hours, 1h for a day and longer. The choice stays in the URL as ?interval=.

  • The count is sum by (schema, digest) (increase(orbit_dbm_query_calls_total{db="<id>"}[<interval>])) at a step of the interval, over whole buckets: the window's end is rounded down to the interval, so the bucket still running is left out. increase() extrapolates to the bucket's edges and the page rounds to a whole call: on the engine, 4 calls every 15 s read as 16 per 1m, 80 per 5m and 240 per 15m, and 300 calls in one scrape land in the one 5m bucket that holds them.
  • A bucket with no sample (the digest was out of the top ORBIT_DBM_TOP_DIGESTS then, or the collector was down) has no count and is left out of the average; MySQL's counter kept counting, so the next bucket with a sample holds those calls.
  • Sort by either column, or group the table by calls per interval: under 1, 1 to 9, 10 to 99, 100 to 999, 1,000 to 9,999 and 10,000 or more, the busiest group first, each keeping the sort.
  • The provider's own statements stay out unless the page shows them, as everywhere on it.

Explain

POST /api/v1/databases/{id}/explain with {"digest": "...", "schema": "shop"} answers {"plan": {...}, "sampleSeenMs": ...}, the plan as EXPLAIN FORMAT=JSON gives it.

  • The monitoring user reads the digest's QUERY_SAMPLE_TEXT. Only a SELECT or WITH digest is explained, and the sample must also pass the editor's statement gate; EXPLAIN ANALYZE, a write, a withheld function or more than one statement is 400, and so is a digest with no sample.
  • MySQL cuts a sample at performance_schema_max_sql_text_length bytes (1024 by default) and ends it with .... To fit the ..., MySQL drops a character split at the limit and three more, so a sample in 4-byte characters ends up to 12 bytes short (MySQL 8.0.46 and 8.4.11). A cut sample is 400 sample_truncated, with the limit in the message: raise it to 4096 in the instance's parameter group (static: reboot the instance) or in my.cnf, and explain the next sample MySQL keeps.
  • Every refusal names its cause in error.code: monitor_off, not_explainable, no_sample, sample_truncated, sample_refused (the gate), explain_denied (MySQL error 1142), explain_refused, explain_timeout, database_unreachable (502) or not_found (404). The Deck words each one in the reader's language.
  • The plan runs as the SQL editor's user, never the monitoring one, on a connection of its own, in a read-only transaction with the digest's schema as the default and a 10 s limit.
  • Quoted literals in the plan become ?, and numbers too in its conditions. A refusal from MySQL is answered as its error number alone, because MySQL's message can quote the sample.
  • Each request is audited as databases.explain with the digest, the schema and the outcome.

Statements with their values

Top SQL shows each statement normalized, every value a ?. A role that also holds databases.raw_sql (admin does; give it to a custom role for a tech lead) gets a "Show values" button in a statement's drawer: the last runs of that statement as the client sent them, values included, newest first, one row per text with how many runs carried it and the latest run's time, and the slowest run the digest table kept (QUERY_SAMPLE_TEXT).

  • The runs come from MySQL's statement history: events_statements_history (each session's last 10 statements, on by default), events_statements_history_long (the last 10,000 across sessions, when its consumer is on) and events_statements_current, read by the monitoring user, which already holds SELECT on performance_schema. Turn the long history on for more than the last few: performance-schema-consumer-events-statements-history-long = ON in my.cnf, or UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long' (until the next restart; on RDS, add it to orbit.enable_consumers()).
  • MySQL keeps the first performance_schema_max_sql_text_length bytes of each statement (1024 by default) and the answer marks a cut one.
  • Every reveal, granted or refused, is in the audit trail: databases.reveal with the reader, the connection, the digest, the schema and how many statements came back, never a statement. Without the permission the route answers 403 and the trail has an access.denied.
  • The values are read when asked and answered to that reader: Orbit keeps none of them, and none reaches /metrics, a log line or another reader.
  • POST /api/v1/databases/{id}/reveal {digest, schema} needs databases.raw_sql and databases.query on a connection the role reaches, with monitoring on (400 monitor_off otherwise); MySQL only, since PostgreSQL is not monitored.

Traces and logs of a statement

Orbit links a digest to the spans that ran it, the way Datadog connects Database Monitoring and APM. orbit-stack gives every MySQL or MariaDB span with a statement (db.query.text or db.statement, from an SDK or from the eBPF probe) one more attribute, orbit.sql.signature: 16 hex digits computed from the statement the way MySQL 8 computes its digest text. Comments, a sqlcommenter traceparent among them, drop out, while an optimizer hint (/*+ ... */) stays and a versioned comment (/*!40001 ... */) reads as SQL. Literals, user variables and placeholders (?, %s, %(name)s, :name) become ?; an IN list or a run of VALUES rows counts as one, and LIMIT 20 and LIMIT 20, 20 stay apart; keywords and their synonyms read as MySQL writes them. orbit-api computes the same signature from each DIGEST_TEXT it keeps.

  • In a digest's drawer, Traces that ran it opens the trace list, background roots included, filtered by orbit.sql.signature over the same window. A trace opens its logs.
  • In a span's drawer, Database monitoring opens the digest of a monitored connection that signs the same, with its drawer open around the span's time. With several, Orbit picks the span's schema (db.namespace) first, then its server (server.address), then the most recently seen.
  • When the app adds a sqlcommenter traceparent comment, Live sessions shows a Trace link on the running statement. orbit-api reads only the trace id's 32 hex digits from SQL_TEXT; the statement and its literals stay in MySQL. MySQL keeps the first performance_schema_max_sql_text_length bytes (1024 by default), so a long statement loses a comment at its end.
  • GET /api/v1/databases/monitor?signature=<16 hex> lists, for each monitored connection, its kept digests with that signature; GET /api/v1/databases/{id}/digests answers each digest's signature. A digest that cannot link (below) has a null signature and carries signatureCut, the bytes of tokens where the signature stops on that server; the monitor answer carries it on a connection where such a digest is the span's statement.

The link needs the same statement on both ends. By default MySQL hashes and keeps a digest's first 1,024 bytes of tokens (max_digest_length), and the signature stops at the same token, so a long ORM select still links. A statement that goes past 1,024 bytes signs apart from the statement its first tokens make, so its span never opens the digest of a shorter statement. orbit-api reads max_digest_length and performance_schema_max_digest_length each time it connects. Above 1,024, MySQL gives statements that differ only past that point digests of their own: a digest whose text passes the cut gets no signature, and its drawer and the span say why. With performance_schema_max_digest_length below max_digest_length, MySQL cuts the text short of what it hashes, so two digests can show one text; there a digest links only while its text fills 252 bytes of tokens or fewer, since the next token, an alias of 256 characters, can take 772. Under the default, a text past 956 bytes reads as cut: its traces are those of the statements cut there, and the whole statement that ends there still opens it from its span. The probe keeps a statement's first KiB, so a statement it cut before MySQL's limit does not link either, and a span from a database Orbit does not monitor has no digest to open. Under ANSI_QUOTES a double-quoted identifier reads as a string, so that statement can open another digest. Against MySQL's own STATEMENT_DIGEST, on both MySQL 8 release lines Orbit supports, the signature linked 1,424 of 1,424 synthetic statements, 394 of 394 statements the eBPF probe saw in a Laravel app and 69 of 69 statements from ten ORMs and drivers with no wrong link, and 75 of 76 harder ORM and driver statements with one wrong link, the ANSI_QUOTES one. On MySQL 8.0.46, a set of 11 statements, 8 of them long, linked 11 of 11 under the default, and 4 and 3 links, all right, with both lengths at 4096 and with max_digest_length at 4096 over performance_schema_max_digest_length at 1024, where signing every digest gave 25 links, 14 of them wrong. A harder set of 72, with aliases of 200 ASCII and 60 CJK characters right at the cut and charset introducers, gave 64, 16 and 2 links on those three servers, none wrong.

A role for a database team

databases.query alone opens database monitoring and the SQL editor on the connections a role reaches, and nothing else of the console. To give a team only that:

  1. Admin → Roles → New role. Tick databases.query and nothing else.
  2. Under Database connections, tick the connections the team may see, or none for every one.
  3. Give the team's users that role.

They land on Databases after sign-in, see the fleet, each connection's load, top SQL, sessions and plans, and run read-only SQL on those connections from the SQL editor. Orbit SQL, Explore, the estate and the dashboards stay closed, and so does every connection the role does not list.

orbit-api decides, not the Deck: a connection outside the role's list answers 404 not_found on every route under /api/v1/databases and on POST /api/v1/sql/query and /explain, as one outside its allowedRoles does, and the list routes leave it out. A connection's allowedRoles and the role's list both apply. The monitoring charts read GET /api/v1/databases/query_range, which takes databases.query and refuses any expression whose series are not the reached connections' orbit_dbm_* (pinned with db) or their RDS instances' aws_rds_* (pinned with dbinstance_identifier). telemetry.query reads every series in the engine, these included: a role scoped to some connections must not hold it.

A role that lists connections grants no wider reach: a user whose role lists some cannot create, edit or give a role that reaches more of them, or none (403, audited as access.denied with reason: "subset").

Monitoring routes

RouteNeedsAnswer
GET /api/v1/databases/monitordatabases.query, and the connection's allowedRoles{databases: [{id, name, engine, host, enabled, state, version, performanceSchema, lastCollectMs, error}]}
GET /api/v1/databases/query_range?expr=&from=&to=&step=databases.query; every series pinned to a reached connectionquery_range's answer; 404 for a connection the role does not reach, 400 for any other series
POST /api/v1/databases/{id}/monitor/checksettings.manage{checks: [{id, ok, detail, fix}]}
GET /api/v1/databases/{id}/digests?digest=<hex>&digest=...databases.query, allowed{digests: [{digest, schema, text, firstSeenMs, lastSeenMs, statement, explainable}]}, 200 digests at most; none given lists the 200 seen last
GET /api/v1/databases/{id}/activitydatabases.query, allowed{atMs, sessions: [{user, host, schema, command, state, wait, timeMs, digest, text, internal}]}; internal is true for a provider account's session
POST /api/v1/databases/{id}/explaindatabases.query, allowed{plan, sampleSeenMs}
POST /api/v1/databases/{id}/revealdatabases.raw_sql and databases.query, allowed{statements: [{text, durationMs, runs, truncated}], sample: {text, durationMs, truncated, seenMs} or null, maxLength}

A role outside a connection's allowedRoles, or one whose list leaves the connection out, gets 404 not_found from every route above and does not see the connection in the list. The public demo monitors nothing: demo-shop is PostgreSQL and lists as off, and the other routes are 403 there.