Handle SQL_CALC_FOUND_ROWS

[renecannao]

SQL_CALC_FOUND_ROWS and FOUND_ROWS() should be tracked to identify when multiplexing needs to be disabled and re-enabled

[geotro]

A vote up from me to handle this.

Any chance you could tell us when this would be done?

Thank you

[brivazac]

Hello,
This issue is still in the roadmap ?
Thank you.

[renecannao]

Hi.
Yes, still in the roadmap, not implemented yet.

[leeparayno]

What is the context of this issue regarding SQL_CALC_FOUND_ROWS and FOUND_ROWS()?

[renecannao]

Multiplexing is not disabled when SQL_CALC_FOUND_ROWS is used, therefore it is possible that FOUND_ROWS() is executed on a different connection.

[leeparayno]

So, the eventual intention is to have "sticky" mapping and routing in SQL_CALC_FOUND_ROWS situations?

[renecannao]

Correct, that is the intention.

The roadmap for 1.4 is to have routing even more configurable. For example, it would be possible to define when multiplexing and stickiness are disabled, and when they will be re-enabled.
This is very useful to define routing based on the specific application.
For example, if you have an application that does something like:
a) START TRANSACTION
b) SELECT
c) INSERT
d) SELECT
e) UPDATE
f) SELECT
g) SELECT
h) COMMIT
If you want that all goes to master but the last 2 SELECT (f and g, they can read from slave) you could define rules that enable stickiness on START TRANSACTION and disable it on the specificUPDATE in e.

[renecannao]

Fixed in 1.3.5 (to be released this week) and 1.4.0 .
Closing

[oytuntez]

@renecannao, any news about 1.3.5? We need this feature to be able to use ProxySQL in production. We are using ProxySQL primarily behind a closed source application and did not realize they use quite a lot SQL_CALC_FOUND_ROWS and FOUND_ROWS() in their application.

We are rolling ProxySQL usage back at this time until this issue has been fixed. :(

[renecannao]

There are already too many bug fixes in 1.3.5 . Therefore will be released this week

[oytuntez]

Thank you, Rene!

I think we will try disabling multiplexing for now, benchmark a bit, and see if it is usable at least until 1.3.5.

[alejg]

Tested against 1.3.5, works only if mysql-query_digests = true
I've isolated a test case, please find attached a bash script.
i732.txt

mysql> update mysql_servers set max_connections = 1;
mysql> SET mysql-query_digests='false';
mysql> LOAD MYSQL .....

for i in {1..5}; do ./i732.sh&; done;

The only disadvantage is that it increases CPU usage.

Thanks!

[renecannao]

@alejg, that is expected, as the digest is needed before searching for SQL_CALC_FOUND_ROWS .

CPU utilization is expected to increase, but I don't expect more than 1% increase.
Do you see bigger different?

[alejg]

I'll measure tomorrow within the high load hours. I think that this could be documented, would have saved a lot of time.

Thanks

[renecannao]

Very valid point!
Documentation updated