Cannot create rule to ignore autocommit

Question

Cannot create rule to ignore autocommit

utdrmac opened this issue 6 months ago · 7 comments

We have a JDBC/Spring application that automatically sends "SET autocommit=0" for all queries which completely disables multiplexing. This has the poor effect of creating 1:1 frontend:backend connections, which defeats the purpose of using ProxySQL to reduce backend connections.

We attempted to make rule to simply ignore this query, but it seems proxysql processes autocommit before the query reaches the rules processing engine.

proxysql> SELECT rule_id, active, flagIN, flagOUT, proxy_port, digest, match_digest, match_pattern, replace_pattern, destination_hostgroup, OK_msg, apply FROM mysql_query_rules;
+---------+--------+--------+---------+------------+--------+--------------+----------------+-----------------+-----------------------+--------+-------+
| rule_id | active | flagIN | flagOUT | proxy_port | digest | match_digest | match_pattern  | replace_pattern | destination_hostgroup | OK_msg | apply |
+---------+--------+--------+---------+------------+--------+--------------+----------------+-----------------+-----------------------+--------+-------+
| 70      | 1      | 0      | 777     | 8056       | NULL   | NULL         | NULL           | NULL            | NULL                  | NULL   | 0     |
| 87      | 1      | 777    | NULL    | NULL       | NULL   | NULL         | .*autocommit.* | NULL            | NULL                  |        | 1     |
| 88      | 1      | 777    | NULL    | NULL       | NULL   | ^SET         | NULL           | NULL            | NULL                  |        | 1     |
| 89      | 1      | 777    | NULL    | NULL       | NULL   | NULL         | NULL           | NULL            | 7                     | NULL   | 1     |
+---------+--------+--------+---------+------------+--------+--------------+----------------+-----------------+-----------------------+--------+-------+
4 rows in set (0.00 sec)

proxysql> LOAD MYSQL QUERY RULES TO RUN;
Query OK, 0 rows affected (0.00 sec)

proxysql> SELECT * FROM stats_mysql_query_rules;
+---------+-------+
| rule_id | hits  |
+---------+-------+
| 70      | 20249 |
| 87      | 0     |
| 88      | 3383  |
| 89      | 16866 |
+---------+-------+
4 rows in set (0.01 sec)

proxysql> SELECT SUM(count_star), digest_text FROM stats_mysql_query_digest WHERE digest_text LIKE 'SET autoc%' GROUP BY digest_text;
+-----------------+------------------+
| SUM(count_star) | digest_text      |
+-----------------+------------------+
| 19448           | SET autocommit=? |
+-----------------+------------------+
1 row in set (0.21 sec)

As you can see, 20,249 queries were executed and none of them matched .autocommit., yet we know at least 19,000 queries were SET autocommit=1

If we cannot create a rule to ignore/rewrite autocommit, how can we force autocommit=1 so that backend connections can be multiplexed? (Note: We know that all queries are read-only, so forcing autocommit=1 is perfectly fine for the application.)

ProxySQL 2.5.5
MySQL 8.0.35

Answer 1 · 2024-04-11T23:47:16.000Z

Hi @utdrmac .
What is your evidence that set autocommit=0 is what disables multiplexing in your specific scenario?

Answer 2 · 2024-04-12T00:35:30.000Z

You've said it at talks, and it's all over the documentation that autocommit=0 disables multiplexing.
select extended_info from stats_mysql_processlist; is just all NULLs so I really don't know why multiplexing is disabled. But given what you've said and what the docs say, that's my best guess. Is there a way to determine the truth?

Answer 3 · 2024-04-12T01:00:52.000Z

Hi @utdrmac ,

You've said it at talks

I am pretty confident I never said that autocommit=0 (on its own) disables multiplexing.
And in fact, ProxySQL has a very complex logic to handle autocommit=0 . You can read some of it here:
https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-enforce_autocommit_on_reads

and it's all over the documentation that autocommit=0 disables multiplexing

If you can please point to the documentation that says that autocommit=0 disables multiplexing, we will fix it or make it less confusing for readers.

select extended_info from stats_mysql_processlist; is just all NULLs

You probably didn't enable mysql-show_processlist_extended. Please refer to ProxySQL documentation:
https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-show_processlist_extended

But given what you've said and what the docs say, that's my best guess.

My best guess is the very first query that JDBC executes after connecting, it is a SELECT statement that retries session variables and it has a lot of @ signs.
This is well described in the documentation page about Multiplexing: https://proxysql.com/documentation/multiplexing/

Is there a way to determine the truth?

In the same documentation page about Multiplexing it is described how to determine the truth. It is also described how to enable extended_info , what "autocommit":"OFF" means (and that on its own doesn't disable multiplexing) , etc .

To summarize:

autocommit=0 on its own doesn't disable multiplexing
the answers to your questions is in the documentation about Multiplexing
if you can point where you read in the documentation that autocommit=0 disables multiplexing, we will fix it or make it less confusing for the readers.
The last point is very important for us, because we want to make the documentation both correct and clear to understand.

Thank you for being a part of our community.

Answer 4 · 2024-04-12T05:30:38.000Z

I am pretty confident I never said that autocommit=0 (on its own) disables multiplexing.

https://archive.fosdem.org/2019/schedule/event/multiplexing_proxysql/
9:22 You said "if the client execute one of those, multiplexing is automatically disabled". "others" would classify "SET autocommit=0" since it is session and a "SET" command.

You probably didn't enable mysql-show_processlist_extended. Please refer to ProxySQL documentation:

You are correct. I did not realize there was something to enable. The documentation page about Multiplexing does not give any indication you need to enable something extra in order to get this information.

if you can point where you read in the documentation that autocommit=0 disables multiplexing
https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-autocommit_false_is_transaction
"That is, autocommit=0 disables multiplexing"

My best guess is the very first query that JDBC executes after connecting, it is a SELECT statement that retries session variables and it has a lot of @ signs.

There's no indication of lots of @ signs when looking at stats_mysql_query_digest.

proxysql> SELECT SUM(count_star), digest_text FROM stats_mysql_query_digest 
WHERE digest_text NOT LIKE 'SELECT %' AND digest_text NOT LIKE 'USE %' GROUP BY digest_text;
+-----------------+-----------------------------------+
| SUM(count_star) | digest_text                       |
+-----------------+-----------------------------------+
| 27859845        | SET autocommit=?                  |
| 29405           | SET character_set_results = NULL  |
| 29405           | SET sql_mode=?                    |
| 13914544        | commit                            |
| 750             | rollback                          |
| 13940921        | set session transaction read only |
+-----------------+-----------------------------------+
6 rows in set (0.23 sec)

Since every connection is setting autocommit=0, and because we see 1:1 frontend:backend, that's why we believe autocommit=0 is disabling multiplexing.

After doing a force multiplex=2 in our last query rule, now things look much better. Now we see 3000:12. But forcing multiplex sounds dangerous long-term.

I will enable extended info and report back. Thanks!

Answer 5 · 2024-04-12T06:57:57.000Z

9:22 You said "if the client execute one of those, multiplexing is automatically disabled". "others" would classify "SET autocommit=0" since it is session and a "SET" command.

English is not my main language, but if the slide says "several session variables" , and I say "if the client execute one of those" , it doesn't mean ALL of them . Several doesn't mean ALL. So no, SET autocommit=0 doesn't classify.
So , thank you for confirming I never said that "SET autocommit=0" disables multiplexing :-)

The documentation page about Multiplexing does not give any indication you need to enable something extra in order to get this information.

A simple search on google for "proxysql extended_info" would bring you to the documentation on how to enable it.
We will improve the documentation page about Multiplexing.

There's no indication of lots of @ signs when looking at stats_mysql_query_digest.

I wrote "it is a SELECT statement that retries session variables and it has a lot of @ signs" , and you run a query like:
SELECT ... FROM stats_mysql_query_digest WHERE digest_text NOT LIKE 'SELECT %' ?

Please pay attention to the instruction you are being given.

I will close this ticket, as the solution was provided.
We will improve the documentation creating a hyperlink in the documentation page about Multiplexing.

Once again, thank you for being a part of our community.

Answer 6 · 2024-04-12T14:32:54.000Z

Several doesn't mean ALL. So no, SET autocommit=0 doesn't classify.

The slide actually says "others" and you did not exclude SET autocommit in your slide/statement so how is one supposed to know that "others" does/does not include SET autocommit?

A simple search on google for "proxysql extended_info"

Doing that, the top result is the same Multiplexing doc page I've mentioned which does not give any indication you need to enable anything. The 2nd result is 'Global Variables' doc page, which also does not mention it, and the 3rd result is a github issue which also does not mention it.

I wrote "it is a SELECT statement that retries session ...

proxysql> SELECT SUM(count_star), digest_text FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT @@%' GROUP BY digest_text LIMIT 1;
+-----------------+----------------------------------------+
| SUM(count_star) | digest_text                            |
+-----------------+----------------------------------------+
| 29405           | SELECT @@session.transaction_isolation |
+-----------------+----------------------------------------+
1 row in set (0.24 sec)

Just to confirm what you are saying, by executing SELECT @@session.transaction_isolation (or any SELECT @@) that is what disables multiplexing?

Answer 7 · 2024-04-12T15:01:54.000Z

@utdrmac , this thread is becoming ridiculous, and I will act accordingly after this reply.

The slide actually says "others" and you did not exclude SET autocommit in your slide/statement so how is one supposed to know that "others" does/does not include SET autocommit?

I suggest you to look into "set theory" : where I am coming from, it is something that students learn in primary school.

... WHERE digest_text LIKE 'SELECT @@%'

Ok, let me explain this too.
If you want to search for a SELECT that includes @ , it should be: WHERE digest_text LIKE 'SELECT%@%'