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.0or later, MariaDB10.5or 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:
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:
orbit-apichecks the statement before it touches the database: exactly one statement, whose first keyword isSELECT,WITH,EXPLAIN,SHOW,VALUES,TABLE,DESCRIBEorDESC, 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, everypg_*advisory*lock and the rest of the list in the contract). A read that locks rows is refused too:FOR UPDATEandFOR NO KEY UPDATEas writes,FOR SHARE,FOR KEY SHAREandLOCK IN SHARE MODEbecause MySQL takes their row locks inside a read-only transaction, and a PostgreSQL advisory lock outlives the transaction on the pooled connection. A refusal is400 sql_errorand says what was refused.- Each query runs in a read-only transaction that is rolled back afterwards.
- 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):
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:
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:
| Mode | What it does |
|---|---|
verify-full | The default. Encrypted, with the certificate and the hostname checked against the public roots, the Amazon RDS global CA bundle and the CA you gave |
require | Encrypted, without checking the certificate |
disable | Plaintext: the password and every result cross the network in the clear |
Amazon RDS and Aurora
verify-fullworks as is:orbit-apicarries the RDS global CA bundle, so no CA is needed.- The database's security group must allow
orbit-apion 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-apiout only where it has to go. Add the database's address range, with its port, tocomponents.orbit-api.databases.egress:
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-apiboth cancel it and the answer is408 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-apiat 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, soSELECT email AS contactis 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:
- holds it to the SQL editor's statement gate, and to
SELECTorWITHonly, with no locking read (FOR SHARE,LOCK IN SHARE MODE) and none ofsleep,benchmark,pg_sleep,get_lockor the advisory lock functions; - gives it a
LIMITof 200 rows, or keeps a smaller one, and refuses anOFFSETpast 100,000 rows; - reads its plan with
EXPLAIN FORMAT=JSON(MySQL) orEXPLAIN (FORMAT JSON)(PostgreSQL), neverANALYZE, and refuses it with400 plan_refusedwhen 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'sreltuples, and a scan with no filter under a smallLIMITcounts as the rows it returns; - 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 byorbit-apipast 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.
| Run | MySQL p95 | MySQL p99 | MySQL's own p95 | PostgreSQL p95 | What the streams did |
|---|---|---|---|---|---|
| Nothing beside the workload | 2.48 ms | 2.93 ms | 0.263 ms | 2.59 ms | - |
| Typical questions, through the guards | 2.58 ms (+4%) | 2.88 ms | 0.251 ms (-5%) | 2.25 ms (-13%) | each read under 100,000 rows; 360 answered on MySQL |
| The same with no pause | 2.31 ms (-7%) | 2.73 ms | 0.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 guards | 2.22 ms (-11%) | 2.62 ms | 0.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
| Route | Needs | Body and answer |
|---|---|---|
GET /api/v1/databases | databases.query or settings.manage | {configured, databases} |
POST /api/v1/databases | settings.manage | The connection and its password → 201 {database, test} |
PUT /api/v1/databases/{id} | settings.manage | The same body → {database, test} |
DELETE /api/v1/databases/{id} | settings.manage | 204 |
POST /api/v1/databases/{id}/test | settings.manage | {ok, serverVersion, readOnly, elapsedMs} or {ok, error} |
GET /api/v1/databases/{id}/schema | databases.query | {tables, truncated} |
POST /api/v1/sql/query | databases.query | {sql, limit, timeoutMs, database} → {columns, rows, stats} |
POST /api/v1/sql/explain | databases.query | {sql, database} → {plan} |
GET /api/v1/databases/nocturne | databases.query or settings.manage | {databases: [{id, name, engine, enabled, maskColumns}]} |
PUT /api/v1/databases/{id}/nocturne | settings.manage | {enabled, maskColumns} → {id, enabled, maskColumns} |
POST /api/v1/databases/{id}/nocturne/query | databases.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 asjob="sreorbit",component="orbit-api"intoorbit-stack; - the text MySQL normalized for each digest (
DIGEST_TEXT, every literal replaced by?), inorbit-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:
| Series | Labels | From |
|---|---|---|
orbit_dbm_up | db | 1 while the collector's connection answers, else 0 |
orbit_dbm_performance_schema | db | @@performance_schema |
orbit_dbm_query_calls_total | db, schema, digest | COUNT_STAR |
orbit_dbm_query_seconds_total | db, schema, digest | SUM_TIMER_WAIT / 1e12 |
orbit_dbm_query_lock_seconds_total | db, schema, digest | SUM_LOCK_TIME / 1e12 |
orbit_dbm_query_cpu_seconds_total | db, schema, digest | SUM_CPU_TIME / 1e12, on 8.0.28 or later with the events_statements_cpu consumer on |
orbit_dbm_query_errors_total | db, schema, digest | SUM_ERRORS |
orbit_dbm_query_rows_sent_total | db, schema, digest | SUM_ROWS_SENT |
orbit_dbm_query_rows_examined_total | db, schema, digest | SUM_ROWS_EXAMINED |
orbit_dbm_query_rows_affected_total | db, schema, digest | SUM_ROWS_AFFECTED |
orbit_dbm_query_no_index_total | db, schema, digest | SUM_NO_INDEX_USED |
orbit_dbm_query_full_scans_total | db, schema, digest | SUM_SELECT_SCAN |
orbit_dbm_query_tmp_disk_tables_total | db, schema, digest | SUM_CREATED_TMP_DISK_TABLES |
orbit_dbm_active_seconds_total | db, wait | 1 s samples: the seconds each active session spent, under its wait |
orbit_dbm_query_active_seconds_total | db, schema, digest | The same samples, per statement digest |
orbit_dbm_collect_errors_total | db, stage | Failed steps: connect, consumers, digests, activity |
orbit_dbm_sampled_seconds_total | db | Seconds the 1 s session sampler covered, its steps added up |
orbit_dbm_digests_read_timestamp_seconds | db | Unix 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:
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
rdsadminsession inorbit_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 aSELECTofperformance_schema,information_schema,mysqlorsystables or of@@GLOBALor@@aurora_*variables that names no other schema. A session variable read such asSELECT @@session.transaction_read_only, which drivers run on every checkout, shows. Performance Insights reads the digest table,@@GLOBALvariables and theinformation_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.
| Statement | Why no customer account runs it |
|---|---|
INSERT, REPLACE, UPDATE or DELETE on a mysql.rds_* table, such as the heartbeat | AWS 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_bin | A 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 LOGS | It 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_access | Aurora 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":
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'sfirstSeenMs) 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 aTRUNCATEof the digest table,firstSeenMsmoves 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 afirstSeenMsfrom 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:
wait | When |
|---|---|
row_lock | The 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/handler | The session's latest wait in events_waits_current is still in progress. |
CPU | Anything 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/%'):
| Setting | Container memory | performance_schema count |
|---|---|---|
performance_schema = OFF | 192 MiB | 0 |
| On, every size at its default | 436 MiB (+244) | 224.5 MiB |
On, the four *_history_long_size at 0 | 382 MiB (+189) | 183.9 MiB |
On, the three lengths at 4096 | 570 MiB (+378) | 342.4 MiB |
max_connections 60 instead of 151 changed none of these numbers.
db.t4g.micro (1 GiB): leave
performance_schemaoff. 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_sizeparameters at 0. From db.t4g.medium up, the default sizes. Set the three lengths to4096only 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):
| Query | Every | Idle | 10 sessions on one row lock | Digest table full (9,999) |
|---|---|---|---|---|
| Active sessions | 1 s | 0.82 ms average, 2.2 ms max | 2.1 ms average, 5.9 ms max | 1.4 ms average, 6.8 ms max |
| Top 100 digests | 10 s | 1.1 ms average | 1.5 ms average | 20 ms average, 69 ms max |
setup_consumers | 10 s | 0.34 ms average | 0.42 ms average | 0.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:
| Parameter | Value | When |
|---|---|---|
performance_schema | 1 | Always |
performance_schema_events_statements_history_long_size | 0 | db.t4g.small |
performance_schema_events_waits_history_long_size | 0 | db.t4g.small |
performance_schema_events_stages_history_long_size | 0 | db.t4g.small |
performance_schema_events_transactions_history_long_size | 0 | db.t4g.small |
max_digest_length, performance_schema_max_digest_length, performance_schema_max_sql_text_length | 4096 | Optional, 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:
| Parameter | Value |
|---|---|
performance_schema | 1 |
performance_schema_consumer_events_statements_current | 1 |
performance-schema-consumer-events-waits-current | ON (dash spelling, values ON and OFF) |
performance_schema_consumer_statements_digest | 1 |
performance_schema_consumer_global_instrumentation | 1 |
performance_schema_consumer_thread_instrumentation | 1 |
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:
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 = ON2. The monitoring user
As the admin user (on RDS, the master user), on the writer:
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:
{"monitor": {"enabled": true, "user": "orbit_monitor", "password": "change-me"}}usermust differ from the connection'suser. On an edit, an absent or""password keeps the stored one, and an absentmonitorkeeps the stored settings.- The password is sealed with
ORBIT_SEAL_KEYlike the editor's, andGETanswers{"enabled": true, "user": "orbit_monitor", "hasPassword": true}. - With
enabledon, saving also connects as the monitoring user, and refuses with400 connection_failedwhen that fails. The host, port, TLS mode and CA are the connection's. - A change to
monitoris audited indatabase.update's changed fields asmonitor.
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:
| Check | Passes when |
|---|---|
connect | The monitoring user signs in |
performance_schema | @@performance_schema is 1 |
consumer_statements_digest | The statements_digest consumer is on |
consumer_events_statements_current | The events_statements_current consumer is on |
consumer_events_waits_current | The events_waits_current consumer is on |
grant_select_performance_schema | The user reads events_statements_summary_by_digest and threads |
digest_length | max_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 theperformance_schemaschema, with the connection's TLS andMAX_EXECUTION_TIME3000 mson its session. Each query has 5 s. - On connect it reads the version,
@@performance_schemaand whetherSUM_CPU_TIMEexists, and callsorbit.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 isperformance_schema, its own among them. Every second it reads the active sessions; withevents_waits_currentorevents_statements_currentoff it skips them rather than count every session asCPU. - After a failure it closes the connection and opens a new one 1 s later, doubling up to 60 s;
orbit_dbm_upreads 0 meanwhile andorbit_dbm_collect_errors_totalcounts the stage. Withperformance_schemaoff 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 per1m, 80 per5mand 240 per15m, and 300 calls in one scrape land in the one5mbucket that holds them. - A bucket with no sample (the digest was out of the top
ORBIT_DBM_TOP_DIGESTSthen, 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 aSELECTorWITHdigest 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 is400, and so is a digest with no sample. - MySQL cuts a sample at
performance_schema_max_sql_text_lengthbytes (1024by 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 (MySQL8.0.46and8.4.11). A cut sample is400 sample_truncated, with the limit in the message: raise it to4096in the instance's parameter group (static: reboot the instance) or inmy.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 error1142),explain_refused,explain_timeout,database_unreachable(502) ornot_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.explainwith 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) andevents_statements_current, read by the monitoring user, which already holdsSELECTonperformance_schema. Turn the long history on for more than the last few:performance-schema-consumer-events-statements-history-long = ONinmy.cnf, orUPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long'(until the next restart; on RDS, add it toorbit.enable_consumers()). - MySQL keeps the first
performance_schema_max_sql_text_lengthbytes of each statement (1024by default) and the answer marks a cut one. - Every reveal, granted or refused, is in the audit trail:
databases.revealwith the reader, the connection, the digest, the schema and how many statements came back, never a statement. Without the permission the route answers403and the trail has anaccess.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}needsdatabases.raw_sqlanddatabases.queryon a connection the role reaches, with monitoring on (400 monitor_offotherwise); 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.signatureover 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
traceparentcomment, Live sessions shows a Trace link on the running statement. orbit-api reads only the trace id's 32 hex digits fromSQL_TEXT; the statement and its literals stay in MySQL. MySQL keeps the firstperformance_schema_max_sql_text_lengthbytes (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}/digestsanswers each digest'ssignature. A digest that cannot link (below) has a nullsignatureand carriessignatureCut, 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:
- Admin → Roles → New role. Tick
databases.queryand nothing else. - Under Database connections, tick the connections the team may see, or none for every one.
- 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
| Route | Needs | Answer |
|---|---|---|
GET /api/v1/databases/monitor | databases.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 connection | query_range's answer; 404 for a connection the role does not reach, 400 for any other series |
POST /api/v1/databases/{id}/monitor/check | settings.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}/activity | databases.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}/explain | databases.query, allowed | {plan, sampleSeenMs} |
POST /api/v1/databases/{id}/reveal | databases.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.