DBTrail Routes Heavy MySQL Reports to a Columnar Copy
- Vadim Tkachenko
- 870 reads
DBTrail sends demanding MySQL reports to a columnar copy while keeping transactional queries on the live server. Benchmark results show faster reports with lower application latency.

Move the heavy reads off the transactional server
DBTrail’s appeal is straightforward: applications can keep sending familiar MySQL queries while a routing layer directs demanding reports to a separate, columnar copy of the database. In a benchmark published by the DBTrail project, 12 of 14 reports ran faster on that copy. The slowest fell from 225 seconds on a Percona Server for MySQL replica to 7.94 seconds on DBTrail.
That improvement comes with a tradeoff. The two lightest reports favored Percona Server; one took 4.0 milliseconds there and 103 milliseconds on the copy. The results suggest DBTrail is aimed at queries that scan and aggregate substantial data, rather than quick lookups.
The benchmark ran 14 reports, averaging 317 executions each, against a 200-warehouse order-entry database under continuous HammerDB writes. Both the replica and DBTrail had four CPUs, and each engine ran the reports by itself. DBTrail’s copy lagged the source by one to five minutes, while the replica had no lag. Across 4,436 paired rounds, the project reported a 6.5x speedup by geometric mean. These figures describe one workload; teams should compare their own reports before deciding where the routing helps.
Why a full scan changes the equation
The slowest query, Q14 from the tplytics report set, groups order lines by item, summarizes stock, joins the results to item details, then sorts by stock coverage. Its inputs include every matching row in both the order-line and stock tables. An index can help queries that locate a narrow slice of data, but it can’t avoid reading these tables end to end.
That makes storage layout and parallel scanning the relevant difference. DBTrail keeps a columnar copy, so a report can read the fields it needs across many rows without running the aggregation on the transactional server. The benchmark’s 225-second versus 7.94-second result is a large gap, but it applies to this workload and query shape. It doesn’t imply that every report, or every database, will see the same gain.
The report varies its minimum order quantity and result limit between runs. Its core workload remains the same: aggregate demand and supply by item, calculate a coverage ratio, and return the lowest coverage values. For a query that must process broad portions of multiple tables, adding an index alone isn’t a likely fix.
Routing without changing SQL
DBTrail presents a MySQL-compatible endpoint. An application can connect using its existing MySQL driver and issue its usual SQL; it doesn’t have to tag reports or rewrite queries. For most SELECT statements, DBTrail asks the MySQL server for an execution plan, then uses that plan to choose where the query runs.
Small, indexed reads go to Percona Server, where they see the latest writes. Queries with a high estimated cost or a large full-table read can go to the columnar copy. DBTrail sends queries back to the server when it can’t reproduce their behavior, including some queries using functions such as GROUP_CONCAT, DATE_FORMAT, or JSON functions. SELECT statements inside transactions also stay on the server so a transaction can read its own writes. Queries returning more than 10,000 rows are routed there too; DBTrail positions the copy for reports rather than exports. The project’s routing guide describes the rules.
This plan-based approach means teams don’t have to maintain a fixed list of report queries. A query that becomes more expensive as data grows may be routed to the copy when its plan crosses the configured threshold. Result column names remain those expected from the server, and the sql-compare command can identify queries that produce different answers on each side before an application is moved. A web console shows which engine handled each query.
Keeping application traffic together
DBTrail’s own MySQL port is read-only. Applications that use one connection for reads and writes can put ProxySQL in front: writes, transactions, and locking reads go to the database, while other SELECT statements go to DBTrail for its plan-based routing. The example configuration uses two server entries and two query rules, with the application’s default hostgroup set to 10. The application can retain its existing host, port, and login.
A separate test measured application performance while reports ran. Using a MariaDB source, ProxySQL 3.1, and sysbench, the workload held the application at 400 transactions per second and looped three reports. With no reports running, application p95 latency was 13.7 ms. Running the reports on the database raised it to 6,960 ms, with 12 reports completed in five minutes. Routing them through DBTrail kept p95 latency at 13.2 ms and completed 266 reports in the same period. The copy lagged by 0.9 to 3.0 seconds.
One report paged five million rows into an ordered result using ORDER BY k LIMIT 20 OFFSET 5000000. It took 92 seconds on the database and 0.7 seconds through DBTrail. Deep pagination is an especially costly pattern for a transactional server; this result shows why moving the work can matter even when the final page contains only 20 rows.
Deployment needs and open questions
DBTrail requires a separate machine alongside the database. The benchmark used four CPUs for the copy, matching the replica, and stores its columnar files on local disk or in S3. The database needs binary logging in ROW format with full row images, a primary key on every InnoDB table, and two users. The project says nothing needs to be installed inside the database.
The initial copy reads every table and takes a short lock, so the setup guide recommends choosing a quiet period. After that, operators enable copy updates and the MySQL port. The project describes DBTrail as Apache 2.0 licensed. Its quick start uses Docker Compose, and the documentation lists MySQL and Percona Server for MySQL 8.0 and 8.4, plus MariaDB 10.11 and later. The project also says it works with Oracle MySQL, including RDS and Aurora.
The copy’s lag is the practical constraint to weigh against faster reporting. A report that needs the newest write may belong on the live server, while historical summaries can often tolerate some delay. DBTrail’s routing rules account for transactions and live reads, but teams still need to check query results, lag, and resource use with their own traffic. A useful first trial is to point a separate DBTrail machine at a replica, compare the slowest reports on both systems, and inspect which engine answers each query.
DBTrail’s code is available on GitHub. The next useful evidence will come from workload-specific comparisons: which query plans cross the routing threshold, how much lag reporting tolerates, and whether the copy’s hardware and storage keep pace as data grows.


