DBTrail
Fast queries from MySQL

Fast queries on your app's connection

Point your application's reads at port 3309. Heavy queries come back in seconds from DBTrail's copy, lookups are still answered by your MySQL, and you do not change a query.

It does not suit a heavy query that must see a write made a second ago: the copy is a few seconds behind, like a replica with some lag, or minutes in fast mode. A server can also have a lag limit, past which your MySQL answers.

What each engine is good at

We ran the same 14 reports, with the same parameters, on a MySQL replica and on DBTrail's copy of the same database, round after round for a day.

225 s → 7.9 sthe heaviest reportItem demand against stock, over every order line. On the MySQL replica, then on the copy.
6.5×faster on the copyAcross the 14 reports: the geometric mean of 4,436 paired rounds.
4 msthe lightest, on MySQLThe copy needs 103 ms for it. A lookup is what MySQL is best at.
Company-wide item demand vs stock225 s7.94 s28×
Market basket analysis92.15 s3.26 s28×
Company-wide order intake trend12.50 s566 ms22×
Company-wide supplier scorecard189 s10.53 s17×
Best sellers37.96 s3.21 s11×
Order line fulfilment status16.28 s1.42 s11×
Stock-out risk64.94 s5.77 s11×
Order value deciles20.94 s1.87 s11×
Customer churn9.19 s868 ms10×
Customer value segments17.44 s1.96 s8×
Sales trend19.35 s2.38 s8×
Top customers per district11.04 s1.99 s5×
Catalog price bands101 ms113 msMySQL, 1.1×
Warehouse leaderboard4.0 ms103 msMySQL, 25×

Twelve of the fourteen were faster on the copy: 6.5 times faster overall, and the heaviest went from 225 seconds to 7.9. Two were faster on MySQL, and they are the two lightest: one takes 4 milliseconds on MySQL and 103 on the copy. Neither engine wins everything. That is the reason to keep both behind one port, and not to move every query to the copy.

What was measured: each engine answering the reports by itself, not the routing. A 200-warehouse order-entry database built and kept under constant writes by HammerDB, 4 CPUs on each side, a MySQL 9.7 replica. Times are the average of 317 runs of each report, and each row's figure is one average divided by the other. The copy ran between one and five minutes behind the source. The replica had no lag.

Set it up

You need the port turned on (From your MySQL client) and a copy that keeps updating. Each step has the web interface and the API; the API calls are the ones in In production.

Create a forwarding user

On your MySQL

When the copy should not answer, DBTrail passes the query to your MySQL as this user. Give it your application's grants and no more.

CREATE USER 'shop_fwd'@'%' IDENTIFIED BY '<a password>';
GRANT SELECT ON shop.* TO 'shop_fwd'@'%';

Give it to DBTrail

Web interface or API

Servers → Edit → Forwarding user and Forwarding password. Or:

curl -s -X PUT -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  $API/servers/$ID -d '{"name": "shop", "route_user": "shop_fwd", "route_password": "<a password>"}'

Turn routing on

Web interface or API

Settings → MySQL clients → Turn routing on. Or:

curl -s -X PUT -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  $API/flashback -d '{"routing": true}'

It is one switch for the port: every server with a forwarding user is routed. Open connections are closed and reconnect.

Try it

A terminal
mysql -h <DBTrail host> -P 3309 -u shop -p shop
SELECT * FROM orders WHERE id = 42;                  -- answered by your MySQL
SELECT status, COUNT(*) FROM orders GROUP BY status; -- answered by the copy
SHOW ROUTER STATUS;
You should see

Both answers, as your MySQL would give them, and statements_copy up by one in SHOW ROUTER STATUS. Still at zero? The table was small enough for MySQL to answer cheaply: try a bigger one.

Move your application's reads

In your application

Change the host (the DBTrail machine), the port (3309), the user (the server's name) and the password (the port's) of the connection it reads with.

  • Writes stay on your MySQL. An application with one connection for everything needs a second one, or ProxySQL in front.
  • Keep autocommit on. Inside a transaction every query goes to your MySQL. Python's PyMySQL starts with it off: open it with autocommit=True.
  • Check your queries first. bintrail-console sql-compare runs them on both sides and lists the ones that would answer differently.

Next

On this page