Managing read/write transactions with ProxySQL

[Daniele Armellini]

Hi all,

I'm starting to learn how to use ProxySQL and I'm a perfect newbie...

I made few tests using a Spring Boot application with HikariCP pointing to a ProxySQL configured to use a Percona XtraDB Cluster. ProxySQL was configured automatically by proxysql-admin with a single writer and 2 readers (I need a master-slave configuration with synchronous replication).

Running these tests I noticed that with the default configuration only queries starting with "SELECT..." get directed toward the slaves (excluding "SELECT... FOR UPDATE" that are directed toward the write master).

The problem is that even selects inside a "write" transaction get directed to the slaves. I'm afraid that this behavior will get tricky results when a select is used after an insert or an update in the same transaction, because that select will be directed to a slave, where the updated data is missing because the transaction isn't yet committed.

I don't really understand how I can configure proxysql to route all read_only transaction to the slaves and all other transactions to the master (even if they are select queries)... Is there some documentation I can look at to achieve this? I'd like something very close to how MySQL Replication Driver works (I can't use it because HikariCP doesn't support it).

Thanks,

Daniele

[Daniele Armellini]

Here are some other info...

I noticed that with jpa+hibernate+jdbc, transactions are delimited with "SET AUTOCOMMIT=0; ... ; COMMIT;".

Does ProxySQL support these kind of "transactions"? I even tried with transaction_persistent inside mysql_users, but transactions aren't traced correctly.

In stats_mysql_query_digest I see queries like "set autocommit=?": it's possible that parametrized queries are blocking transaction detection?

[René Cannaò]

Hi Daniele,

If you are using jpa+hibernate+jdbc, most probably everything is wrapped around a "set autocommit=0" + commit.

If that's correct, I think you don't need read/write split: everything should go to the master.

ProxySQL supports transactions delimited with "SET AUTOCOMMIT=0; ... ; COMMIT;" . The problem is: when "set autocommit=0" is executed, a transaction is not started yet. (you can try that yourself: run "set autocommit=0" directly on mysql, and check "show engine innodb status\G" : there is no transaction yet).

For this reason, ProxySQL doesn't forward ""SET AUTOCOMMIT=0" , but tracks it internally and set it when a query is routed.

If you want set autocommit to be sent to the backend, you should use ProxySQL 1.4.x that has a new variable "mysql-forward_autocommit" .

To reiterate, if I remember correctly, jpa+hibernate+jdbc wraps everything with "SET AUTOCOMMIT=0; ... ; COMMIT;" , and if this is the case you should disable read/write split if you want that all SELECTs executed between "SET AUTOCOMMIT=0; ... ; COMMIT;" (again, all iirc) should be sent to master.

Finally, I recommend reading this article: http://www.proxysql.com/blog/configure-read-write-split

Setting a generic query rule to send SELECTs to slaves is generally a bad practice: you should decide which SELECTs should go to slaves, without a too generic "catch all".

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.