AUTOCOMMIT=0 Transaction Pattern not working on ProxySQL 1.4

Toruk

unread,

Jul 26, 2017, 6:02:29 AM7/26/17

to proxysql

Hi,

Background:

Our application has two traffic patterns:

--> Read Transaction is sent as "SET autocommit = 1; SELECT "

--> Write Transaction is sent as "SET autocommit = 0; SELECT|INSERT|UPDATE|DELETE; COMMIT;"

Problem Statement:

I am trying to set up Read/Write Split using ProxySQL's latest version 1.4 using above transaction patterns but I am not able to do so.

ProxySQL Configuration:

mysql> SELECT @@version;

+------------------+

| @@version |

+------------------+

| 1.4.0-0-ge2f0cc4 |

+------------------+

1 row in set (0.00 sec)

mysql> \! uname -a

Linux ip-172-21-35-79.ap-south-1.compute.internal 2.6.32-642.15.1.el6.x86_64 #1 SMP Fri Feb 24 14:31:22 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

mysql> SELECT *

-> FROM runtime_global_variables

-> WHERE variable_name = 'mysql-forward_autocommit';

+--------------------------+----------------+

| variable_name | variable_value |

+--------------------------+----------------+

| mysql-forward_autocommit | true |

+--------------------------+----------------+

1 row in set (0.00 sec)

mysql> SELECT *

-> FROM runtime_mysql_servers;

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 601 | 172.21.35.79 | 3310 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |

| 600 | 172.21.35.133 | 3310 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |

| 601 | 172.21.35.133 | 3310 | ONLINE | 200 | 0 | 1000 | 10 | 0 | 0 | |

| 601 | 172.21.35.94 | 3310 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

mysql> SELECT *

-> FROM runtime_mysql_replication_hostgroups;

+------------------+------------------+-------------------------+

| writer_hostgroup | reader_hostgroup | comment |

+------------------+------------------+-------------------------+

| 600 | 601 | replication host groups |

+------------------+------------------+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT username,active,use_ssl,default_hostgroup,default_schema,schema_locked,transaction_persistent,fast_forward,backend,frontend,max_connections

-> FROM runtime_mysql_users

-> WHERE username = 'mha_RW';

+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

| mha_RW | 1 | 0 | 600 | | 0 | 1 | 0 | 1 | 0 | 10000 |

| mha_RW | 1 | 0 | 600 | | 0 | 1 | 0 | 0 | 1 | 10000 |

+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

2 rows in set (0.00 sec)

mysql> SELECT *

-> FROM runtime_mysql_query_rules

-> WHERE active = 1\G

*************************** 1. row ***************************

rule_id: 892

active: 1

username: mha_RW

schemaname: NULL

flagIN: 0

client_addr: NULL

proxy_addr: NULL

proxy_port: NULL

digest: NULL

match_digest: NULL

match_pattern: ^SELECT

negate_match_pattern: 0

re_modifiers: CASELESS

flagOUT: NULL

replace_pattern: NULL

destination_hostgroup: 601

cache_ttl: NULL

reconnect: NULL

timeout: NULL

retries: NULL

delay: NULL

next_query_flagIN: NULL

mirror_flagOUT: NULL

mirror_hostgroup: NULL

error_msg: NULL

sticky_conn: NULL

multiplex: NULL

log: NULL

apply: 1

comment: NULL

1 row in set (0.00 sec)

Question:

I have set up the user "mha_RW" with transaction_persistent = 1 which means that commands within a transaction should not be sent on slave.

I have also set up a Query rule which sends the regex pattern '^SELECT' on Read Hostgroup, I am expecting that transaction_persistent=1 will take precedence over the Query rule but it is not happening. SELECTS in the write transaction "SET autocommit = 0; SELECT; COMMIT;" are served from the Read Hostgroup even though they are enclosed within a transaction.

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'set autocommit=0; select @@hostname; commit';

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------------------------------------+

| @@hostname |

+---------------------------------------------+

| ip-172-21-35-79.ap-south-1.compute.internal |

+---------------------------------------------+

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'set autocommit=0; select @@hostname; commit';

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------------------------------------+

| @@hostname |

+---------------------------------------------+

| ip-172-21-35-94.ap-south-1.compute.internal |

+---------------------------------------------+

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'set autocommit=0; select @@hostname; commit';

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------------------------------------+

| @@hostname |

+---------------------------------------------+

| ip-172-21-35-94.ap-south-1.compute.internal |

+---------------------------------------------+

Interesting thing is that if I create transactions using "BEGIN;SELECT;COMMIT" format then SELECT statements are always directed to Write Hostgroup:

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'BEGIN;SELECT @@hostname;COMMIT;';

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------------------------------------------+

| @@hostname |

+----------------------------------------------+

| ip-172-21-35-133.ap-south-1.compute.internal |

+----------------------------------------------+

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'BEGIN;SELECT @@hostname;COMMIT;';

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------------------------------------------+

| @@hostname |

+----------------------------------------------+

| ip-172-21-35-133.ap-south-1.compute.internal |

+----------------------------------------------+

[root@ip-172-21-35-79 ~]# mysql -h 127.0.0.1 --port 6033 --user mha_RW -p'Test@123' -e 'BEGIN;SELECT @@hostname;COMMIT;';

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------------------------------------------+

| @@hostname |

+----------------------------------------------+

| ip-172-21-35-133.ap-south-1.compute.internal |

+----------------------------------------------+

Can someone please help me understand why the SELECT statements in former transaction pattern are not routed correctly to Write Hostgroup.

Regards,

Ankit

René Cannaò

unread,

Jul 26, 2017, 6:10:54 AM7/26/17

to Toruk, proxysql

Ankit,

You wrote that "I am expecting that transaction_persistent=1 will take precedence over the Query rule" : that is correct!

The problem is that SET AUTOCOMMIT doesn't start a transaction.

And this is easy to verify, even bypassing proxyql:

* connect to mysqld

* run SET AUTOCOMMIT=0

* run SHOW ENGINE INNODB STATUS

* you will see that there is no active transaction

Therefore, when you run "SET autocommit = 0; SELECT|INSERT|UPDATE|DELETE; COMMIT;" the transaction doesn't start with SET, but with the first following query.

Also, please note that I strongly recommend against generic rules like the one you setup (send all SELECTs to HGx). Please refer to this:
http://www.proxysql.com/blog/configure-read-write-split

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.

Toruk

unread,

Jul 26, 2017, 7:13:26 AM7/26/17

to proxysql, ankit.t...@gmail.com

Hi Rene,

First of all Thankyou for your work on this fantastic project. You have done great service to MySQL community through ProxySQL.

You mentioned that "SET AUTOCOMMIT=0" does not start any transaction, I agree on that part but if simply issue a BEGIN then also no transaction gets started, a transaction only starts when we fire a SELECT or DML statement. With BEGIN statment ProxySQL does restrict the SELECT within a transaction to Write Hostgroup.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

Empty set (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

Empty set (0.00 sec)

mysql> SELECT * FROM test.X;

+------+

| A |

+------+

| 10 |

+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

*************************** 1. row ***************************

trx_id: 119669

trx_state: RUNNING

trx_started: 2017-07-26 15:59:31

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 0

trx_mysql_thread_id: 1723

trx_query: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 0

trx_lock_memory_bytes: 360

trx_rows_locked: 0

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

So, "SET AUTOCOMMIT = 0" and "BEGIN" have the same behaviour in terms of starting a transaction

I agree with your recommendation against generic rules but our current application supports master/slave archietecture in which READ Transactions are routed to Slave HAProxy while WRITE Transactions are routed to Master IP so the general recommendation does not apply in our case.

Is there any way in which we could do restrict the SELECTs in WRITE transaction "SET autocommit = 0; SELECT;INSERT;UPDATE;SELECT; COMMIT" to Write Hostgroup and SELECTs in READ Transactions "SET AUTOCOMMIT=1; SELECT" to Read Hostgroup ?

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

René Cannaò

unread,

Jul 26, 2017, 7:30:55 AM7/26/17

to Toruk, proxysql

Hi Ankit,

First, thank you for the feedback.

Actually, BEGIN/START TRANSACTION and "set autocommit" works in a different way.

In fact, BEGIN does start a transaction, even if not in the storage engine yet.

At the protocol level, the server informs the client (proxysql in this case) that a transaction is running using SERVER_STATUS_IN_TRANS flag:
https://dev.mysql.com/doc/dev/mysql-server/latest/mysql__com_8h.html#a1d854e841086925be1883e4d7b4e8cad

When a client issue "BEGIN", a transaction starts (SERVER_STATUS_IN_TRANS=1).

When a client issue "set autocommit=0" , a transaction is not started (SERVER_STATUS_IN_TRANS=0).

A possible feature request is to treat "set autocommit=0" as if a transaction already started, but I am not sure if this will have other negative implications.

Maybe a easier approach is to automatically convert "set autocommit=0" to "BEGIN" .

Both possible feature requests.

About your specific requirement, if your application already support read/write split, why would you need further read/write split in ProxySQL?

I assume you want to use a single access point, that is ProxySQL.

As your application is already read/write split aware, a possible option is to configure ProxySQL to listen on two ports, one for read traffic and another for write traffic.
Does this make sense?

Thanks,

René

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Toruk

unread,

Jul 26, 2017, 8:53:02 AM7/26/17

to proxysql

Yes, I want application to use ProxySQL as a single access point which can do the read/write split. The two port approach is can indeed be used with ProxySQL using different users. But if the Proxy Layer does this automatically for a application it would be really cool :)

I feel that the Query Digest routing by analysing the traffic, which you have recommended in your blog, is not a full proof solution and its benefits % might depend on workload, consider a case of a Select SQL fetching User's Bank Account Balance:

--> If the purpose is simple balance fetch for displaying user balance on welcome page then the SQL digest can be routed to Slave.

--> But if the balance fetch is within a money movement transaction then it becomes critical in which case such a SQL cannot be routed to Slaves using the Query Digest rule.

If we go by the Query Digest rule, we will decide not to route this SQL to Read Hostgroup since we do not want to face a case where we fetch the balance from a slave, with even microsecond lag, and hence we will have to forfeit the benefit of routing the Non Transaction Select to Slave even though the Non Transaction Select count might be 10 times the count of Transaction Select.

I feel that for such use cases, SELECT within a Transactions(both formats - BEGIN;COMMIT; AND SET AUTOCOMMIT=0;SELECT|DML;COMMIT;) should be routed to Write Hostgroup and Non Transactional Selects should be routed to Read Hostgroup.

Do you see any gaps in above thought process?

René Cannaò

unread,

Jul 26, 2017, 9:06:50 AM7/26/17

to Toruk, proxysql

Antik,

If your application is making money movement transactions, I hope it doesn't rely on a simple SELECT statement with no locking to read the value ...

Another solution you can implement is to specify in the query itself where in which HG you want to send the query.
ProxySQL allows modifiers embedded into the first comment:
https://github.com/sysown/proxysql/issues/166 (meh, still need to be documented after nearly 2 years)

You could run something like :

SELECT /* hostgroup=0 */ ....

(if you are testing this with mysql cli, make sure to use the -c option)

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Toruk

unread,

Jul 28, 2017, 2:43:46 AM7/28/17

to proxysql

René,

As of now the Account is locked on Redis.

I understand that this is not the best practice but in case of Optimistic Locking we can face an issue by Routing Transactional Select Balance Query to Slave that might have 1-2 seconds lag. Though with Optimistic locking the application must be able detect that it does has the latest balance record but in that case the System wide Transaction Retries will become high when the slave is lagging by some seconds.

Would you consider a feature request for --> convert "set autocommit=0" to "BEGIN"

René Cannaò

unread,

Jul 31, 2017, 2:32:49 PM7/31/17

to Toruk, proxysql

Hi Ankit,

Absolutely yes.

Please feel free to create a feature request on github.

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Reply all

Reply to author

Forward