The API should see MySQL, not the topology

The festival sale is on, and a thousand people open the same listing for a pair of size-8 white sneakers and tap Buy. I add more API boxes so the shop can take the load.

Each box already keeps a few MySQL connections open and reuses them. I wrote that story when a thousand clicks met four sockets. I thought more boxes would just mean more of those small pools, and the database would be fine.

It was not fine. The database saw every box. I put ProxySQL in front. It answers like MySQL and it reads the SQL, so the API does not have to know the map.

Too many doors into MySQL

I ran two hundred API processes. Each one kept ten connections to MySQL. If they all woke up at once, that is two thousand clients.

MySQL has a limit, max_connections. Mine was 400. After 400 open clients, the next one is refused. The shop starts seeing “too many connections.”

Those two thousand clients were not two thousand different questions. Most of them wanted those sneakers. Each process still opened its own connections, and each new connection still pays the handshake: three packets to open, four to close.

Two hundred API processes each hold a pool of ten MySQL connections. MySQL has max_connections of 400 and is out of clients. — Figure 1. Each process brought its own pool. MySQL saw all of them.

A load balancer that only forwards TCP would not have saved me. It never looks at the query. It cannot send a read to one machine and a write to another.

The API still thinks it is talking to MySQL

I put ProxySQL in the middle.

The API did not change. Same driver, same user, same password. ProxySQL answers the MySQL hello. The host in the config is the proxy, not the real database. Catalog still says SELECT for the sneakers. Checkout still says INSERT for o1. I did not add “if this is a read, go to the replica” in the application. The proxy reads the SQL and picks the server.

ProxySQL keeps its own pool already open to the real MySQL. One pool in front of the database, shared by every API box. MySQL now sees forty clients, not two thousand. A new API box does not open forty more.

Buyer to API to ProxySQL to MySQL. MySQL sees forty clients. — Figure 2. The API talks to the proxy. The database only sees the proxy's pool.

This is different from the pool inside one API process. That pool lives on one machine. The next machine cannot use it. The proxy pool sits in front of MySQL, so every API box can use it.

The API connects to host port 3306 with a MySQL user and password. ProxySQL answers the MySQL handshake. The real MySQL servers sit behind it. — Figure 3. One address in the app. The list of real servers stays behind the proxy.

Who knows which machine is which

Topology here just means the map: which machine takes writes (the primary), which machines take reads (the replicas).

The API knows the map. Writes go to primary:3306. Reads go to replica:3306. Fine when the shop is one program. Every new service then copies those two addresses. When the primary dies, every service must change.

The API knows one address. proxy:3306. It looks like MySQL because it talks like MySQL. ProxySQL holds the map. When the primary changes, I update the proxy. I do not redeploy catalog and checkout.

Left: The API has a write host and a read host. Right: The API has one proxy host. The proxy holds primary and replicas. — Figure 4. Same SQL. The question is who owns the map.

Splitting the shop into two services

The shop used to be one program. Then I split it. Catalog serves the sneakers. Checkout writes order o1.

I did not put routing in those services. I defined rules on the proxy: this SELECT goes to the replica group, this INSERT goes to the primary. Catalog and checkout still talk to one MySQL host. They still run the same queries. The business logic did not change.

If each service owned the map instead, I would have copied the primary and the replica into two codebases. After a failover, catalog might still point at a dead machine.

A monolith splits into catalog and checkout. Both connect to ProxySQL, which looks like MySQL. One MySQL cluster sits behind it. — Figure 5. Only the routing rules changed. Catalog and checkout still see one host.

One MySQL connection, many waiting APIs

An API worker often keeps a connection open after it finishes the sneakers. If that connection is a real MySQL connection, it still counts against the 400 limit, even while it sits idle.

ProxySQL does not have to hold a real MySQL connection for an idle API. It reads the query, borrows one of its MySQL connections, sends the answer back, and can lend that same connection to someone else. Many API sessions share fewer MySQL connections. People call that multiplex. It only means: do not save a database connection for a client that is not asking a question.

One MySQL connection still runs one query at a time. Sharing does not make one query faster.

Many API client sessions enter ProxySQL. ProxySQL reads each query and picks a free backend socket. MySQL has two busy sockets and a small live set. — Figure 6. An idle API session does not each own a MySQL connection.

A transaction is different. BEGIN means the next statements must run on the same server, in the same session, so they see their own writes. ProxySQL then sticks that API to one MySQL connection until COMMIT. If I start a transaction and then wait on Redis, I am holding a MySQL connection for no query.

The phone is a read. Buy is a write.

The sneakers listing is a read. Buy is a write. Those should not hit the same machine if I have a primary and a replica.

A TCP-only proxy cannot tell them apart. ProxySQL can, because it reads the SQL. A SELECT for the phone can go to a replica. An INSERT for o1 can go to the primary.

The lists it chooses from are named groups of servers. People call a group a hostgroup. A rule says: this kind of SQL goes to that group.

API to ProxySQL. SELECT sneakers goes to a replica. INSERT order o1 goes to the primary. — Figure 7. A TCP load balancer cannot make this split. It never sees the query.

A replica can be late. The buyer pays, then refreshes, and the read goes to a replica that has not got o1 yet. The confirmation looks empty. For a read that must see the write I just did, I still send it to the primary.

More than one proxy

If I have only one ProxySQL box, and it dies, the shop thinks MySQL is down.

So I run more than one. The API still has one name. Each proxy answers like MySQL. Each one has its own pool to the real database.

That is the trap. Three proxies with forty connections each can mean 120 clients on MySQL, not 40. The database cap does not grow because I added proxies.

They also do not gossip. A routing rule lives on that one process. If I change a rule on proxy a, proxy b and proxy c keep the old one. I apply the same change on every proxy, or I have not changed the rule.

The API can use ProxySQL a, b, or c. Each holds forty backend connections. MySQL sees up to one hundred and twenty clients. — Figure 8. Scale the proxy. Keep one budget on the database.

Figure 9. They do not tell each other. A rule change is a change on every box.

A pool inside one API process saves that process from opening MySQL on every click. It does not save me when I have two hundred processes.

The app keeps one address. Routing lives on the proxy. MySQL should see a limited number of clients, not one connection per API worker. When I add proxy boxes, MySQL sees the sum of their pools.

I still leave a transaction open while I call Redis. I still send every SELECT to a replica and then cannot find the order on the confirmation page. I still change a rule on one box and debug the other two.

A Redis lock on sneakers:8-white is the same split: the key is a hint, the MySQL row is the fact. I wrote that when two passengers booked 12A.

If this is useful, wrong, or incomplete, write to me.