DBTrail Documentation

Which statements your MySQL answers and which ones DBTrail's copy answers on port 3309, what the copy cannot answer, what happens when it is busy, and how old the copy is.

With read routing on, every statement that reaches port 3309 is answered by your MySQL or by the copy. This page is how that is decided.

Who answers what

For most SELECTs, DBTrail asks your MySQL one question: how would you run this? MySQL answers with its plan and runs nothing.

Light read

SELECT * FROM orders WHERE id = 42;

your MySQL

The plan finds a few rows with an index. Live, to the last write.

Heavy read

SELECT status, COUNT(*) FROM orders GROUP BY status;

the copy

The plan reads a whole big table. Your MySQL does no work for it.

Write

UPDATE orders SET status = 'paid' WHERE id = 42;

refused · 1290

As it comes, the port only reads. The write never reaches your MySQL.

You do not keep a list. A query that is light today and heavy next year moves over by itself. The rules, in plain words:

Heavy means MySQL's own plan says so: a cost of 10,000 or more, or a whole table of 100,000 rows or more read from end to end. On a MariaDB source the cost is not used: the rows read decide.
Everything that is not a SELECT goes to your MySQL: SHOW, SET, USE, BEGIN.
Inside a transaction, everything goes to your MySQL, so a read after your own write sees that write.
A query the copy would answer differently goes to your MySQL. Being right comes before saving work.
A result over 10,000 rows goes to your MySQL. The copy answers reports, not exports.
Column names are your MySQL's, whoever answers. COUNT(*) is called COUNT(*) either way, so code that reads a result by column name keeps working.
A server with no forwarding user is not routed. The copy answers it alone, the light reads too, and the panel says so.

What the copy cannot answer

The copy runs DuckDB, not MySQL. Your application keeps writing MySQL's SQL and always gets its answer, because what the copy cannot answer correctly, your MySQL answers.

One thing is translated for the copy: names in backticks, which most ORMs write. Nothing else.
What the copy does not have, your MySQL answers: DATE_FORMAT, for example.
What the copy would answer differently, with no error, is kept on your MySQL: GROUP_CONCAT, NOW(), DATEDIFF, JSON functions, and more.
Some tables are kept on your MySQL too: a statement that names a TIME or a YEAR column, or a generated column, and a SELECT * over a table whose columns the copy cannot return as MySQL does.

Correct, but not taken off your MySQL. How much moves to the copy depends on how your reports are written. sql-compare, in step 7 of the setup, tells you which of yours go where, and Why each side on the panel counts them by reason. The known differences: SQL on the copy.

The copy alone, for one connection

Add @copy to the user, and the copy answers everything on that connection, whatever the routing switch says. The other connections stay routed.

mysql -h 127.0.0.1 -P 3309 -u shop@copy -p

Nothing is sent to your MySQL on that connection, and nothing is translated: write DuckDB's SQL there. sql-compare needs such a connection while routing is on.

When it is busy

Heavy queries are run by a small number of workers. DBTrail works out how many the machine can carry. When all of them are busy, the next heavy query waits its turn, in the order it arrived, the way a query waits on a busy MySQL. It is never handed to your MySQL because the copy is busy. Any number of applications can stay connected: 256 connections sending heavy queries to a copy that runs 4 at once are all answered, more slowly. Each connected application also holds one connection to your MySQL, so its max_connections has to allow them.

0wait for lightA light query does not wait in that line. It goes straight to your MySQL.

No limiton the waitA heavy query waits for as long as its application stays connected. If the application leaves, the query leaves the line and runs nowhere.

4,096can waitOne more gets error 1040, too many connections, at once.

2cores per workerAnd about 2.5 GB of memory each. 4 cores and 8 GB run two heavy queries at once, unless you set the number.

With no ceiling set, the service that answers the port takes half of the machine and leaves the rest for capture. To give it more, or less, set a ceiling. It then uses all of what you gave it.

These settings are new in DBTrail 0.104.1. Before it the number could not be set, and a heavy query waited 30 seconds at most.

In .env, next to docker-compose.yml, then docker compose up -d:

ROUTER_CPUS=4
ROUTER_MEMORY=8g

Run sudo systemctl edit bintrail-router and set, under [Service]:

CPUQuota=400%
MemoryMax=8G

Say how many run at once

DBTrail works the number out: one heavy query for every two cores, as many as the memory holds. To set it yourself, from 1 to 64, the way innodb_thread_concurrency does on a MySQL server:

In .env, with the current docker-compose.yml, then docker compose up -d:

ROUTER_CONCURRENCY=8

Run sudo systemctl edit bintrail-router and set, under [Service]:

Environment=BINTRAIL_ROUTER_CONCURRENCY=8

Then sudo systemctl restart bintrail-router.

A number above one for every two cores is taken as you wrote it. Each query then runs with fewer threads, down to one: more run at once, and each one is slower. A high number has three costs:

Memory. Each query may use about 2.5 GB. If the number can ask for more than the port has, the service prints a line that begins Router: WARNING: when it starts, with the sum.
Temporary disk. The disk a query may use when it runs out of memory is shared among them. With many, each gets little or none, and a query that used to finish slowly can fail.
Idle processes. The service keeps that many workers ready, a few tens of MB each.

Put a limit on the wait

There is none by default. To refuse a heavy query that has waited too long, with error 1040, which drivers and pools retry:

ROUTER_MAX_WAIT=30s

Environment=BINTRAIL_ROUTER_MAX_WAIT=30s

Write the time with its unit, from 1s to 24h.

What the copy knows

The copy is your tables as they were at its last update. A heavy query sees that moment, not the last second.

SHOW ROUTER STATUS says how old it is (copy_age_seconds), and so does The router's numbers on the panel. Watch that number the way you would watch a replica's lag.
There is no limit on that age. If the updates stop, heavy queries keep being answered, from older data.
How often it updates is the schedule from step 3 of the setup.
Light queries are not affected. They read your MySQL, live.

Every rule and setting: read routing, in the reference.

How routing decides