ProxySQL

What is ProxySQL?

ProxySQL is a performant proxy that offloads the load from the primary mysql db by multiplexing client connection, allowing slow read queries to be redirected to db replicas, and a bunch of other features. I’m going to be focusing on query redirection and the pitfalls I encountered getting it work.

Query Redirection

There are two main options when it comes to query redirection:

  • Use a different proxysql port or user and have all queries coming from that port or user to use a specific hostgroup
  • Use the query rules to redirect specific queries that match some condition

The first option requires changing the application code to use the redirection port or the other user which requires an extra connection. The second option allows specific queries to be redirected by just adding a rule to ProxySQL. At least that’s what it seemed like.

Turns out it’s not as simple as adding a rule that matches against the query we wanted to redirect. All queries in SQLAlchemy and most ORM’s are within a transaction. And proxySQL doesn’t redirect queries that are part of a transaction; at least that’s what the documentation implied.

The options at this point are:

  1. Use an auto-commit session, which in theory makes the queries standlone
  2. Change proxySQL mysql_user settingtransaction_persistent to 0 for all mysql users that need their queries redirected. If this setting is enabled, transactions started within a hostgroup will remain within that hostgroup regardless of any other rules.

Option 2 isn’t great. It could lead to redirecting a query that’s based on a read/write/read query pattern which leads to an inconsistent read after the write because it got redirected to the replica that doesn’t have the same state.

Option 1, and for some reason it wasn’t working as expected. This lead me down a debugging journey, that ended up with me discovering it did work…but not with the original way I was testing it. The issue was that my test was using a parent transaction, and my auto-commit session was a nested transaction within it, and there were other nested transactions that contained writes. ProxySQL refused to redirect the query since it was part of a transaction that contained writes. When testing outside the test framework, the redirection worked as expected. I was also surprised that redirection worked even without the auto-commit transaction which seemed contrary to the ProxySQL documentation.

Debugging Tips:

How was I testing this?

  • The query rule on ProxySql
mysql_query_rules=
(
  {
    rule_id = 2
    active = 1
    match_pattern = "^SELECT\s*\/\*\s*REDIRECTION\_TEST\s*\*\/"
    apply = 1
    destination_hostgroup = 3
    comment = "Send to read-replica"
  }
)
  • The query that’s sent:
# Login
mycli mysql://root@db.local:3306/classicmodels

# Query to primary
SELECT /* REDIRECTION_TEST */ * from customers;

# Query to replica
SELECT /* REDIRECTION_TEST */ * from customers;

How do I know if a query was redirected?

  • Turn on query logging
#ProxySQL DB Access
mycli -uradmin -pradmin -hproxysql.local -P6032

# Turn on logging
SET mysql-eventslog_filename='/queries.log';
SET mysql-eventslog_default_log=1;
# Log in json format 
SET mysql-eventslog_format=2;
# Load changes to runtime
LOAD MYSQL VARIABLES TO RUNTIME;
  • Sample Log line:
{"client":"172.18.0.128:64022","digest":"0x4F33AE77FCE89DDF","duration_us":11702,"endtime":"2024-04-13 20:15:01.510946","endtime_timestamp_us":1713039301510946,"event":"COM_QUERY","hostgroup_id":3,"query":"SELECT /* REDIRECTION_TEST */ * from customers","rows_sent":122,"schemaname":"classicmodels","server":"db-replica:3306","starttime":"2024-04-13 20:15:01.499244","starttime_timestamp_us":1713039301499244,"thread_id":4,"username":"root"}

  • Fields that matter:

    • “Query”: the query that’s being processed
    • “hostgroup_id”: What hostgroup the query is routed to
    • “server”: The server it was routed to
    • The “server” will tell you definitively if the query was redirected to the replica or not, the “hostgroup_id” will tell you if the query was routed to a particular host-group (based on the rule), but since the hostgroup does include the primary db at a low weight, we can’t rely on this value without also looking at server.

  • ProxySQL Stats:

    • Another method is looking at proxy sql stats to find out how many matches there were against a particular rule id, and also how many queries each server in a hostgroup handled so far. When the query is part of a transaction that contains a write, the query rule match count will increase, but noting will actually be sent to the replica.
# Query rule 
select * from stats_mysql_query_rules

# Connection stats 
select `hostgroup`,`Queries`, `srv_host` from stats_mysql_connection_pool
select * from stats_mysql_connection_pool

Conclusion:

Queries that are part of a transaction that contains a write won’t get redirected unless transaction_persistent is set to 0, all other queries that are part of a transaction that contains no writes will get redirected unless they are nested in a transaction that contains writes.

Check out this repo for a lab environment and a jupyter notebook that tests when a query will be redirected.

References