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.
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 MySQLWhen 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 APIServers → 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 APISettings → 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 terminalmysql -h <DBTrail host> -P 3309 -u shop -p shopSELECT * FROM orders WHERE id = 42; -- answered by your MySQL
SELECT status, COUNT(*) FROM orders GROUP BY status; -- answered by the copy
SHOW ROUTER STATUS;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 applicationChange 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-compareruns them on both sides and lists the ones that would answer differently.
Next
- One connection, with ProxySQL: writes and reads on the same connection.
- How routing decides: who answers what, and how old the copy is.
- Routing in production: errors, limits, and letting writes through.
From your MySQL client
Run reports and heavy queries from your MySQL client, on port 3309, without slowing down your MySQL.
How routing decides
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.