tplytics: analytical queries on the HammerDB TPC-C schema

tplytics is an analytical (OLAP) benchmark that runs against the schema HammerDB builds for its TPROC-C (TPC-C derived) workload. It is meant to run at the same time as HammerDB, so you can see how an OLTP database copes when reporting queries hit the tables it is writing to, and how much each workload slows the other down.

• 14 analytical queries with a business meaning, from millisecond single-table lookups to multi-join, window-function, self-join and whole-table-scan queries that take minutes on a row store.
• Every query has a weight for serial mode and one for concurrent mode, so cheap queries can run more often than expensive ones.
• Parameters are drawn fresh for every execution from the database itself: the number of warehouses, the item range and the time ranges of the stored orders, delivered orders and payments, read again before every round. The same query never runs with the same parameters twice in a row, and the benchmark works on any database size, whether HammerDB has been running for a minute or for a month.
• Failed queries (syntax not supported, other errors) and timeouts (300 s by default) are recorded and reported. They never stop the run.
• Engine variants in the style of ClickBench: a query can carry different SQL for different engines or be marked as not supported by an engine.
• Three modes: demo, serial and concurrent, plus a live dashboard that runs the queries in a loop in the browser.

Supported engines: MySQL / Percona Server (mysql), MariaDB (mariadb), PostgreSQL (postgresql) and the MySQL port of DBTrail (dbtrail), which runs the queries in DuckDB on DBTrail's Parquet copy (see DBTrail). Other engines can be added easily (see Adding an engine).

Quick start

git clone https://github.com/Percona-Lab/tplytics.git
cd tplytics
pip install -r requirements.txt      # PyMySQL, psycopg 3 (Python 3.8+; tomli on < 3.11)

./tplytics.py list                                         # the 22 queries and their weights
./tplytics.py info  --engine mysql --host db1 --user root -p secret
./tplytics.py demo  --engine mysql --host db1 --user root -p secret
./tplytics.py serial --engine postgresql --host db1 --user tpcc -p tpcc --json serial.json
./tplytics.py concurrent --engine mysql --host db1 -u root -p secret \
              --threads 8 --duration 600 --think 10 --json conc.json --raw conc.csv

On Ubuntu/Debian the drivers are also packaged: apt install python3-pymysql python3-psycopg.

Connection defaults follow HammerDB: database tpcc, user root for MySQL and tpcc for PostgreSQL. --defaults-file ~/.my.cnf reads MySQL credentials, and the host and port, from the [client] group of an option file; --host and --port on the command line take precedence. --socket connects through a Unix socket (for PostgreSQL, give the socket directory). Any connection option can also be set as an environment variable: TPLYTICS_ENGINE, TPLYTICS_HOST, TPLYTICS_PORT, TPLYTICS_USER, TPLYTICS_PASSWORD, TPLYTICS_DATABASE or TPLYTICS_SOCKET.

Modes

demo

For every query, demo mode prints what the query answers and how it works, the generated parameters, and the full SQL statement. It then runs the statement and shows the execution time, the number of rows and the first 5 rows of the result. A summary table follows at the end.

./tplytics.py demo -q Q05,Q11 --pause          # selected queries, wait for Enter between them
./tplytics.py demo --variants 3 --show-rows 10 # three parameter variants per query, 10 rows each

====================================================================================================
Q10  Stock-out risk   [complexity 3, weight serial 1 / concurrent 2, tables: district, order_line, stock, item]
----------------------------------------------------------------------------------------------------
Which items will run out first? Measures recent demand per supplying
warehouse and item from the latest orders of a slice of warehouses, ...

Parameters: wh_lo=24, wh_hi=32, wh_count=9, last=1000, cover=3, top=20

WITH demand AS ( ... );

>>> OK: 7.39s, 20 rows
w_id   i_id  i_name                   i_price  on_hand  recent_demand  order_lines  cover_ratio  demand_value
----  -----  -----------------------  -------  -------  -------------  -----------  -----------  ------------
  24  11707  edrxY3oaQRmzbMuW           16.86       11             40            8         0.28        674.40
  31  11710  MlY0fVmiHWrx5WFHOPf18N     35.91       15             50           10         0.30       1795.50
...

serial

Serial mode uses one connection. Each query runs iterations × serial weight times (10 iterations by default), each time with new parameters. For every query the report shows the runs, successes, errors, timeouts and success ratio, the min / avg / p50 / p95 / p99 / max / stddev of the execution time, the average number of rows returned, and the response time distribution. The run total, the sum of the per-query averages and their geometric mean summarise the result.

./tplytics.py serial -n 10                     # default: 10 x weight runs of every query
./tplytics.py serial -n 3 --order shuffled     # random order instead of query by query
./tplytics.py serial --warmup-runs 1 -v        # one uncounted run per query; print every execution

--order grouped (the default) runs all executions of a query back to back. interleaved cycles through the queries round robin, and shuffled runs the executions in random order, which is closer to how caches behave in practice.

concurrent

Concurrent mode opens --threads connections. Each one repeatedly picks a random query, with the probability of each query proportional to its concurrent weight, and runs it with fresh parameters until --duration seconds of measurement have passed. An optional --warmup runs before measuring starts. The report has the same per-query statistics plus each query's share of the executions and its QPS, the response time distribution, the overall and successful QPS, and a progress line every --report-interval seconds. These progress lines also go into the JSON file as a timeline.

Think time. --think T makes each thread sleep after every query for T / weight seconds (with ±50 % jitter, set by --think-jitter). A cheap query of weight 10 is followed by a short pause and an expensive query of weight 1 by a long one. The load can therefore stay below saturation with realistic concurrency (many sessions, most of them idle at any moment), and the expensive queries still don't dominate. --think 0 (the default) runs flat out.

While the benchmark runs, the database size and time ranges are read again every --refresh seconds (60 by default). New HammerDB orders are therefore included in the time windows. Statements still running when the time is up are cancelled and left out of the results.

./tplytics.py concurrent --threads 16 --duration 1800 --warmup 60 --think 20

dashboard (live demo in the browser)

The dashboard runs one query at a time in an endless loop, like demo mode, and serves a live web page:

• System load at the top: CPU busy and I/O wait, disk throughput (read and write MB/s), disk IOPS and database transactions/s. Each tile has a sparkline of the last 3 minutes, with a hover tooltip. CPU and disk come from /proc of the machine the dashboard runs on, so run it on the database host to see them. Transactions/s comes from the engine (Com_commit + Com_rollback on MySQL, pg_stat_database on PostgreSQL) and includes HammerDB.
• Current query: the query's description, tables and parameters, and its state. While it is running, the page shows the SQL and a live timer. Once it finishes it shows waiting, with its response time, rows, status, the first 5 rows (or the error) and a countdown to the next query.
• Query geomean (top row): the geometric mean of the per-query average times over everything run so far. It appears once the first round is complete, that is, once every query has run at least once. Its sparkline shows the geomean of each completed round.
• Rounds at the very bottom: the last --rounds (10) completed rounds, newest first. A round is one run of every query (in --order weighted it is the same number of executions, picked at random). Each row shows the start, end and duration of the round, its successes, errors and timeouts, the geomean of its successful execution times, and the change from the previous round. The round in progress is shown above them with its geomean so far.
• Recent queries below the current query: the last --history (10) finished queries, newest first. When a new query starts, the finished one slides in at the top and the oldest drops off the bottom. Click a row for its description, parameters, result rows and SQL.

At most one query starts every --interval seconds (10 by default), and every result stays the current query for at least --min-display seconds (5), so people have time to read it. Pause stops the loop after the current query, and Resume continues it. --order weighted picks queries at random by their concurrent weight instead of going through Q01..Q14 in turn.

./tplytics.py dashboard --defaults-file ~/.my.cnf                         # http://127.0.0.1:8080/
./tplytics.py dashboard --listen 0.0.0.0 --http-port 8080 --interval 8 --timeout 120

The page only shows what the query files and the engine layer provide. Adding a query or an engine variant (see below) needs no change to the dashboard. Engines can supply a transactions counter by setting activity_sql in engines.py. The dashboard has no authentication. It listens on 127.0.0.1 unless you pass --listen, so put it behind a firewall or an SSH tunnel when the database is not a test system.

compare (side by side, for a big screen)

compare is the dashboard for several targets at once, typically a MySQL replica and the DBTrail copy of the same primary. Every round runs one query, with the same parameters, on all targets at the same time; the parameters are drawn from the data every target has (the overlap of their time ranges, read from every target at the start of each full round), so the statements and the data they read are the same.

With more than two targets (say a MySQL replica, DBTrail and Doris), every query still runs on all of them in parallel, and the page shows two at a time: pick them in the header ("Compare A vs B", ⇄ swaps the sides). The choice is kept in the URL (/?a=MySQL replica&b=Doris), so each screen can be bookmarked on its own pair, and remembered per browser. Speedups, the per-query table and the verdict are computed for the chosen pair from the rounds where both succeeded; the server keeps them for every pairing, so switching is instant and loses nothing.

The page, sized to fill a 16:9 screen from 1080p to 4K without scrolling, shows for each of the two targets:

• replication lag, from the heartbeat;
• the geometric mean of every successful execution so far, and the counts of executions, errors and timeouts;
• the target host's CPU, disk throughput and IOPS;
• the current query's time, status and first rows;

plus the overall verdict (the geometric mean of the per-round time ratio over rounds where both targets succeeded), a per-query table of average times and ratios, the latest rounds (click an error to read it, or SQL to see the statement a target ran), the primary host's load, queries/s and transactions/s, and a long-term chart: the geometric mean of every full pass over the queries per target, for the last keep_hours (24 by default; a timeout counts as the timeout). With history_file set those points are appended to a JSON-lines file and reloaded after a restart.

Click a query in the per-query table (or in Recent rounds) for its details: the description, the SQL each engine runs (once when it is the same text, otherwise side by side with the differing lines and words highlighted), and every execution of that query on every target over the last keep_hours, on a log scale with timeouts and errors marked. Executions are kept in a second file next to history_file (...-executions.jsonl), so this chart survives restarts too.

tplytics compare --config compare.toml          # http://127.0.0.1:8080/

/performance (linked from the page header) shows every node side by side over the last 15 minutes: the operating system (CPU split with steal, load and run queue, pressure stall information, memory and swap, disk IOPS / MB/s / busy %, network, context switches and major faults), the database (for MySQL: queries and transactions, rows read and changed, buffer-pool reads and hit ratio, redo, InnoDB data I/O, threads, history list and lock waits; for DBTrail: changes indexed, capture lag, index commit latency, batch flushes, index size and SQL waits, read from its Prometheus /metrics), and the CPU and memory of every systemd service and Docker container, from their cgroups. The primary is sampled by compare itself; each target by its heartbeat monitor started with --perf (and --prometheus-url for DBTrail), which serves /api/perf next to /api/heartbeat.

Targets are described in a TOML file (see scripts/compare.example.toml): connection keys as on the command line (engine, host, port, defaults_file, ...), a label, and a status_url: a heartbeat monitor running on the target's host with --http-port and --system-stats, which serves the lag and the host load. Without status_url the lag is read from the heartbeat table through the target itself and host load is not shown. Keys under [dashboard] set compare's options (listen, http_port, interval, timeout, title, ...). scripts/systemd/tplytics-compare.service runs it as a service with /etc/tplytics/compare.toml.

/dbtrail is a page about the DBTrail target itself:

• version, container image and running since;
• capture state (changes indexed, freshness, continuity), when the copy for SQL was last updated and the last full read, end-to-end lag, and this dashboard's failure rate on DBTrail;
• the settings that shape it: copy schedule and full reads, SQL memory per statement and unmerged-changes limit, statements at once, change history kept, index size, growth and free disk;
• the latest copy jobs (full reads, refreshes, compactions) with duration and outcome, as a timeline and a table;
• the latest 10 failed benchmark executions on DBTrail (errors and timeouts); click one to see the SQL that ran, its parameters and the error DBTrail returned. They are kept in <history_file>-failures.jsonl, so they survive restarts;
• charts (15 min to 24 h) of changes indexed, capture lag, index size and commit latency, SQL statements/s and time per statement by phase, the DBTrail process's CPU and memory, and the DBTrail containers.

The DBTrail web interface and metrics listen on localhost only, so the heartbeat monitor on the DBTrail host reads them and serves /api/dbtrail:

tplytics heartbeat monitor ... --perf --prometheus-url http://127.0.0.1:9090/metrics \
    --dbtrail-console http://127.0.0.1:8090 --dbtrail-token-file /opt/dbtrail/.env \
    --dbtrail-history /var/lib/docker/volumes/dbtrail_bintrail-state/_data/console-baseline-history.json

The token file is DBTrail's .env (its CONSOLE_TOKEN= line) or a file with the token alone; the history file is DBTrail's own record of copy runs.

Other commands

command	what it does
list	queries, complexity, tables, weights, concurrent probability, and support per engine
info	what the benchmark discovers about the database (size, time range)
sql [ids]	print queries with freshly generated parameters, without running them
compare --config FILE	side-by-side dashboard for several targets, see compare
heartbeat update|monitor|check	replication lag from a heartbeat row, see Heartbeat

Heartbeat: replication lag

Analytical queries often run on a replica, so how fresh the replica is matters as much as how fast it answers. tplytics heartbeat measures that end to end, the way pt-heartbeat does, on any engine tplytics supports, including the DBTrail copy:

• update runs against the primary. It creates the table tplytics_heartbeat if needed and, every --interval seconds (1 by default, on round multiples of the interval), updates row --id with a sequence number, its own clock in UTC microseconds and the interval.
• monitor reads that row from a replica, by default every second at half an interval after the writes (--skew), and reports the lag. It logs a summary line every --report-interval seconds, rewrites --status-file after every read and, with --http-port, serves the same JSON on /api/heartbeat.
• check reads once and exits 0 (lag at or under --max-lag), 1 (over) or 2 (cannot be measured), for alerting.
• With --system-stats, the monitor also serves its host's CPU and disk load on /api/system, which is where compare gets each target's host load.

tplytics heartbeat update  --defaults-file /etc/tplytics/primary.cnf
tplytics heartbeat monitor --defaults-file ~/.my.cnf --label replica --http-port 8091
tplytics heartbeat monitor --engine dbtrail --defaults-file dbtrail.cnf --label dbtrail --interval 5
tplytics heartbeat check   --defaults-file ~/.my.cnf --max-lag 10

Lag is how long the replica has been missing the beat after the one it shows: max(0, read time - (beat time + writer interval)). A replica that has every beat already written reports 0; one that is behind reports its delay to within one interval. Age is the plain age of the row it read. The monitor's clock is compared with the writer's, so keep the hosts in sync with NTP. The timestamp is written as a number by tplytics, not taken from a database function, so the same table and statements work everywhere; the monitor's interval does not have to match the writer's, which it reads from the row.

The writer needs only CREATE, SELECT, INSERT, UPDATE on the heartbeat table (on MySQL: GRANT CREATE, SELECT, INSERT, UPDATE ON tpcc.tplytics_heartbeat TO ...). Use a separate --id for each writer. On DBTrail the table appears in the copy with the next snapshot or scheduled refresh after it is created, and the lag then shows how fresh the copy is, refresh interval included.

scripts/install-heartbeat.sh NAME update|monitor [options] installs either side as the systemd service tplytics-heartbeat@NAME (a copy in /opt/tplytics, settings in /etc/tplytics/heartbeat-NAME.env, monitor status in /run/tplytics/NAME.json):

scripts/install-heartbeat.sh primary update --defaults-file /etc/tplytics/primary.cnf
scripts/install-heartbeat.sh replica monitor --defaults-file /root/.my.cnf --http-port 8091
journalctl -fu tplytics-heartbeat@replica

Running next to HammerDB

tplytics only reads data, and the only thing it needs is a HammerDB TPROC-C schema, so you can start it on any machine while HammerDB is running. For a single command, scripts/with-hammerdb.sh starts a HammerDB timed run with the settings in scripts/hammerdb/*.tcl, waits for the rampup to finish, and then runs tplytics in concurrent mode for the rest of the HammerDB run. The logs of both tools go into results/<timestamp>/.

HDB_DB=mysql HDB_USER=root HDB_PASS=secret HDB_VU=16 HDB_DURATION=30 \
  scripts/with-hammerdb.sh /opt/HammerDB-6.0 --engine mysql -u root -p secret --threads 4 --think 10

To measure the effect, run HammerDB once on its own and once with tplytics, and compare NOPM. Then compare the tplytics latencies with those of a run on an idle database.

Use HammerDB … allwarehouse true (the default in the helper scripts) or enough virtual users that new orders reach every warehouse. Otherwise many time windows will cover warehouses that HammerDB never touches.

Queries

id	name	cx	tables	serial w	conc w	question answered
Q01	Warehouse leaderboard	1	district	3	10	Which warehouses take the most orders / payments? (2,000-row table: milliseconds)
Q02	Catalog price bands	1	item	3	10	How is the catalog priced; share of brand items per band (100 K rows)
Q03	Order line fulfilment status	1	order_line	2	4	Delivered vs pending value, remote supply share (5–25 % of warehouses)
Q04	Sales trend	2	orders, order_line	2	5	Orders, revenue and order size per time bucket
Q05	Best sellers	2	district, order_line, item	2	5	Top items in the latest orders
Q06	Customer value segments	3	orders, order_line, customer	1	2	Customers per spend segment and credit rating (LEFT JOIN keeps inactive customers)
Q07	Top customers per district	3	orders, order_line, customer	1	2	Top-K spenders per district (ROW_NUMBER), at most 500 rows
Q08	Customer churn	3	orders, customer	1	2	Who ordered in the first half of a window but not the second (NOT EXISTS)
Q09	Order value deciles	3	orders, order_line	1	2	Order value distribution (NTILE)
Q10	Stock-out risk	3	district, order_line, stock, item	1	2	Items whose stock covers less than recent demand
Q11	Market basket analysis	4	district, order_line, item	1	1	Item pairs bought together: support, confidence, lift
Q12	Company-wide supplier scorecard	4	order_line	0.5	0.5	Which warehouses fulfil the most demand (full scan of order_line)
Q13	Company-wide order intake trend	4	orders	0.5	0.5	Orders per time bucket for the whole company, running total (full scan of orders)
Q14	Company-wide item demand vs stock	4	order_line, stock, item	0.5	0.5	Items with the lowest stock cover (full scans of order_line and stock)

Complexity (cx): 1 = single table, 2 = join, 3 = CTEs / window functions / correlated subqueries over joins, 4 = heavy (self-join or full scan of a large table).

The set covers each execution pattern once or twice instead of repeating one: Q01 and Q02 are fast lookups where a row store answers in milliseconds, Q03–Q11 are range scans, joins, CTEs, window functions, an anti-join and a self-join over slices of the data, and Q12–Q14 read whole tables. Measured on the lab (Percona Server 9.7 replica vs DBTrail 0.102, 200 warehouses, 5 runs each): MySQL is faster on Q01 (5 ms vs 0.15 s) and about equal on Q02, and DBTrail is 3.5–30× faster on the rest; geomean 8.8 s vs 1.3 s.

How parameters adapt to the database

At start-up and again before every round (one run of every query: each pass of the dashboards, every N executions in serial mode, where N is the number of queries), tplytics reads the warehouse, district, item and customer counts and the time ranges the data covers. Concurrent mode has no rounds and re-reads them every --refresh seconds instead. Every execution of a round draws its parameters from that state, so they always fall where the data is. There are three time ranges, because on a running TPROC-C database they differ:

source	range	found from
orders	entry time of the stored orders	first and last order of a sample of districts (primary-key lookups)
delivered	entry time of the orders already delivered	the oldest order still in new_order of the same districts: Delivery works oldest first, so with a backlog the delivered orders can be days older than the newest ones
history	time of the stored payments	first and last row by id when history has one (Parma Ham adds it, and purges old payments), otherwise one scan of history

A query's time window can name its source (source = "history" for a window over payments, "delivered" for one over delivered orders; the default is orders). Without it, a window could fall in hours whose payments were purged or whose orders were never delivered, and the query would read data to find nothing. Other parameters:

• warehouse slices are a random contiguous range covering a fraction of all warehouses (e.g. 5–25 %), with a minimum and, for expensive queries, a maximum. The work per query therefore grows with the database but stays bounded.
• time windows are a fraction of their source's time range. Half of them end at its newest row ("recent", like a dashboard) and the other half fall anywhere in the range. Their bucket size (minute, 5 minutes, hour, …) is chosen to give about 24 buckets.
• "latest N orders per district" is based on d_next_o_id, so it always looks at current activity.
• top-N, thresholds, sort columns and other choices are drawn from lists.

Three queries deliberately read whole tables, and their cost grows with the size of the database and with the length of the HammerDB run:

• Q12 scans all of order_line, the largest table (about 77 M rows at 200 warehouses after two days of Parma Ham; 1.5–4 min on an 8 GB / 4 vCPU MySQL replica).
• Q13 scans all of orders (7.7 M rows; ~11 s).
• Q14 aggregates all of order_line and all of stock (20 M rows) and joins them on the item (~3.5 min on the same replica).

They have the lowest weights (0.5), so in concurrent mode they run about once for every 20 executions of the cheapest queries.

HammerDB specifics the queries respect: the loader writes ol_amount = 0 for the orders delivered at load time, and all remote supply lines come from the benchmark run. HammerDB's MySQL stored procedures do not maintain c_ytd_payment, c_payment_cnt, c_delivery_cnt, s_ytd, s_order_cnt or s_remote_cnt, so demand and payments are taken from order_line and history instead. On a freshly loaded database that HammerDB has not run against yet, some queries legitimately return no rows.

DBTrail

DBTrail keeps a Parquet copy of a MySQL server, fed from its binlog, and its daemon can serve that copy on a MySQL protocol port (turn it on in the web interface under Connect a SQL client; the Docker Compose stack publishes it on 127.0.0.1:3309). Statements on that port run in DuckDB, so --engine dbtrail tries [sql].dbtrail, then [sql].duckdb, then the MySQL text. Q04 and Q13 have DuckDB variants (time_bucket instead of FROM_UNIXTIME(UNIX_TIMESTAMP())); the other queries run unchanged. DBTrail refuses results of more than 1,000 rows, which is why Q07 ends with LIMIT 500.

# user = the server's id in DBTrail, password = the port's password
./tplytics.py serial --engine dbtrail --host 127.0.0.1 --port 3309 \
              -u '<server id>' -p '<port password>' --timeout 60

What differs from a MySQL server:

• The copy is as fresh as DBTrail's last snapshot or scheduled refresh, so info shows where its data ends, and the time windows end there.
• The port runs one statement at a time per server, stops a statement after 60 s (reported as a timeout) and refuses results over 1,000 rows (error 1104). Concurrent mode therefore measures the queue in front of DuckDB.
• It has no CONNECTION_ID(), max_execution_time or KILL QUERY. The watchdog cancels a statement by closing the connection, which DBTrail treats as a cancel, and opens a new one.

Timeouts and errors

--timeout (300 s by default) is enforced twice:

1. The server is asked to stop the statement: max_execution_time on MySQL, max_statement_time on MariaDB and statement_timeout on PostgreSQL. DBTrail has its own fixed cap instead.
2. A client-side watchdog cancels the statement 2 s later if it is still running (KILL QUERY <id> on a separate connection for MySQL, a cancel request for PostgreSQL, closing the connection for DBTrail). This also covers engines or statements the server timeout does not apply to.

Such an execution is reported as timeout. Any other database error is reported as error and shown in the "Errors and timeouts" part of the report. A broken connection is re-opened, and the run continues in all cases.

Output

• Human readable report on stdout.
• --json FILE: everything in the report plus the settings, the server version, the discovered database state, a per-query response time distribution, error samples and (in concurrent mode) the timeline.
• --raw FILE (serial and concurrent): CSV with one row per execution, giving the query, thread, start time, elapsed time, status, rows, error and parameters.

Apache Doris

--engine doris connects to a Doris frontend's MySQL-compatible port (9030 by default). Doris parses the MySQL text of every query as is; a query file can still add [sql].doris where Doris should get a text of its own. The time limit is set with SET query_timeout, and a statement past it is cancelled with KILL QUERY like on MySQL.

To compare Doris with a MySQL replica of the same primary, Doris can keep a copy itself: a streaming job (Doris 4.1+) reads the primary's binlog after a full snapshot, creating the tables on first sync (every table needs a primary key):

CREATE DATABASE tpcc;
USE tpcc;
CREATE JOB tpcc_cdc ON STREAMING
FROM MYSQL (
    "jdbc_url" = "jdbc:mysql://<primary>:3306",
    "driver_url" = "mysql-connector-j-8.4.0.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver",
    "user" = "<user with REPLICATION SLAVE, REPLICATION CLIENT, SELECT>",
    "password" = "<password>",
    "database" = "tpcc",
    "offset" = "initial"
)
TO DATABASE tpcc ("table.create.properties.replication_num" = "1");

The heartbeat row replicates like any other, so tplytics heartbeat monitor --engine doris measures the end-to-end lag of that job.

Query files and engine variants

Every query is a TOML file in tplytics/queries/:

id = "Q42"
name = "Carrier delivery performance"
complexity = 2
tables = ["orders", "order_line"]
description = """What the query answers and how it works."""
weight = { serial = 2, concurrent = 4 }
unsupported = []                      # engines that cannot run it at all

[params]
wh  = { type = "warehouse_range", fraction = [0.05, 0.25], min = 1, max = 50 }
win = { type = "time_window", fraction = [0.05, 0.5], anchor = "mixed" }

[sql]
default    = """ ...portable SQL... """          # used when no engine-specific text exists
mysql      = """ ... TIMESTAMPDIFF(SECOND, a, b) ... """
postgresql = """ ... EXTRACT(EPOCH FROM (b - a)) ... """
# mariadb  = false                    # false (or "UNSUPPORTED") marks the query as not supported

The SQL for an engine is looked up along the engine's dialect chain. For example, mariadb tries [sql].mariadb, then [sql].mysql, then [sql].default. Unsupported queries are listed and skipped in every mode. Placeholders such as {wh_lo} are replaced with literals of the right type for the engine (timestamps become TIMESTAMP '…').

Parameter types:

type	placeholders	options
warehouse_range	{x_lo} {x_hi} {x_count}	fraction = [lo, hi], min, max
warehouse	{x}
district	{x}
item_range	{x_lo} {x_hi} {x_count}	fraction, min, max
time_window	{x_start} {x_mid} {x_end} {x_seconds} {x_bucket}	fraction, source = orders / delivered / history, anchor = recent / any / mixed, min_seconds, buckets
int	{x}	range = [lo, hi]
choice	{x}	values = [...]; raw = true inserts an identifier unquoted

Use --query-dir to run your own set of query files. To change weights without editing the files, use --set-weight Q11=0 --set-weight Q01=20.

Adding an engine

Engines live in tplytics/engines.py. An engine that speaks the MySQL or PostgreSQL protocol is usually a small subclass that sets its name, its dialect chain and its timeout statement:

class TiDBEngine(MySQLEngine):
    name = "tidb"
    dialects = ["tidb", "mysql", "default"]

ENGINES["tidb"] = TiDBEngine

Then add [sql].tidb variants, or unsupported = ["tidb"], wherever the engine needs them. A new protocol needs a Session that implements open, execute, cancel and set_timeout.

Validation run

Tested on a 4 GB / 2 vCPU Linode (Ubuntu 24.04), with HammerDB 6.0 running on the same machine, with the earlier 22-query set (ids differ from today's):

	Percona Server 9.7, 40 warehouses	PostgreSQL 16, 20 warehouses
demo, Q01–Q20	all OK, 10 ms – 50 s (Q20 before its slice was capped)	all OK, 5 ms – 2.7 s
serial -n 3 with HammerDB 4 VU	111/111 OK in 227 s, geomean of averages 0.84 s
concurrent 6 threads, saturating, with HammerDB 4 VU	1.75 QPS, p50 0.94 s, p95 13.7 s, 100 % OK
concurrent 8 threads, --think 20 --timeout 10	92.7 % OK, 16 timeouts (Q09, Q12, Q13, Q19, Q20)
HammerDB 4 VU alone		42,662 NOPM
HammerDB 4 VU + tplytics 4 threads --think 5		21,311 NOPM (−50 %), tplytics 1.23 QPS, 565/565 OK

Tests

python3 -m unittest discover tests          # offline: queries render for all engines; heartbeat on SQLite
./tplytics.py serial --query-dir tests/fixtures/queries --timeout 5 -n 2 -v
                                             # live: error, timeout, unsupported and OK cases

License

Apache License 2.0