Are requests load balance for a single connection?

[Kevin Heatwole]

One of my PHP apps does not properly use transactions to group queries/updates together so each query/update is auto-committed for a given web page. The PHP app does open the connection to MySQL requesting persistent connections.

If I use ProxySQL between the PHP app and MySQL to a hostgroup of 2 equally weighted backend servers for load balancing, is there a possibility that the 10 or so queries/updates that the PHP app does on a single connection are load balanced and might end up on different MySQL backends? The backends will be using Master/Master replication, but the updates of the other backend may be delayed due to replication lag.

That is, does ProxySQL load balance connections with all queries/updates done with a single connection go to the same backend (like HAProxy does with stick tables to remember the backend server selected for the session)?

[René Cannaò]

Kevin,

On 22 September 2016 at 18:49, Kevin Heatwole <ktwa...@gmail.com> wrote:

One of my PHP apps does not properly use transactions to group queries/updates together so each query/update is auto-committed for a given web page. The PHP app does open the connection to MySQL requesting persistent connections.

If I use ProxySQL between the PHP app and MySQL to a hostgroup of 2 equally weighted backend servers for load balancing, is there a possibility that the 10 or so queries/updates that the PHP app does on a single connection are load balanced and might end up on different MySQL backends? The backends will be using Master/Master replication, but the updates of the other backend may be delayed due to replication lag.

Yes, by default, it is possible that the requests coming from a single connection are evenly distributed among all the backends of the same hostgroup.

Unrelated: master/master replication is quite scary, and I strongly recommend to use only 1 node for writes.
 

That is, does ProxySQL load balance connections with all queries/updates done with a single connection go to the same backend (like HAProxy does with stick tables to remember the backend server selected for the session)?

HAProxy is not MySQL protocol aware, so it must remember the backend server selected for the session: it doesn't know (at all!) when a request starts or when it ends, therefore cannot do multiplexing.

How to achieve this in ProxySQL?

There are few ways:

a) create a user that has fast_forward enabled in mysql_users : for this user, ProxySQL will behave similarly to HAProxy, without being aware of what is being sent over the wire

b) disable multiplexing globally: SET mysql-multiplexing='false'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Right now, this setting is global, so it will apply to every connection.

c) For only the "problematic application" (that doesn't use transactions), sends a query that implicitly disables multiplexing. For example, if you run "SELECT @a" , ProxySQL will disable multiplexing for that client and will always use the same backend connection.

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.

[Kevin Heatwole]

Unrelated: master/master replication is quite scary, and I strongly recommend to use only 1 node for writes.

I plan on using this master/master replication in active/passive mode (at least to start). The master/master replication is for failover.

I am exploring other replication topologies, but I need to understand how ProxySQL load balances to be able to scale the backends, if needed.

So, if my PHP app did use transactions properly (with BEGIN/COMMIT wrapping the queries), then does ProxySQL always route the queries in the transaction to the same server?

Yes, by default, it is possible that the requests coming from a single connection are evenly distributed among all the backends of the same hostgroup.

Would it be desirable for ProxySQL to implement the equivalent of HAProxy stick tables to "stick" all requests in a single connection to go to the same backend? If the backend fails after the first query succeeds for the backend, the rest of the queries would fail as well (from the app's point of view).

Chances are that I will start this website using HAProxy since, even though it is not SQL-aware, the proxy features I need are more mature and handle the needs of my app. But, I really want to be able to switch to ProxySQL for the connection multiplexing feature when my database traffic could overwhelm a single backend and need the connections to be load balanced.

My transaction-unaware PHP app is third-party software and while I am trying to get the vendor to upgrade the app to support a transactional view of the database, I am not hopeful.

Kevin 

Kevin,

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

[René Cannaò]

Hi Kevin,

I understand.

In that case, an easy solution is to configure ProxySQL as described in my previous reply:

a) create a user that has fast_forward enabled in mysql_users : for this user, ProxySQL will behave similarly to HAProxy, without being aware of what is being sent over the wire

b) disable multiplexing globally: SET mysql-multiplexing='false'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Thanks,

René

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

[Kevin Heatwole]

I also understand, but I don't really want to give up the connection multiplexing feature. I won't be needing this feature until the load gets to a point that a single backend can't handle it, but I'm investigating switching to ProxySQL for the long term when I will need the database service to scale.

So, I am trying to explore all the possible solutions that would address my future use case so ProxySQL can evolve in the near future to be ready to use on my "huge" and successful site. :)

[René Cannaò]

Sounds like you want persistent connection.

I will create an issue for this: persistent connection could be implemented as a temporary disabling of multiplexing.

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

[Kevin Heatwole]

Sounds like you want persistent connection.

Not sure if I agree. I still want multiplexing (many incoming connections being served by a few backend connection), but I want ProxySQL to always choose the same backend server for all requests coming into a session (from a single PHP page load that connects to the database and sends multiple queries over that connection).

Anyway, if "a temporary disabling of multiplexing" does this but still allows many incoming connections to be served by a few backend connections, then I'm good. I just don't understand the nuances of proxying well enough. I want my queries to "queue" if there aren't enough available connections to the backend server that was selected for the first query executed in the session. So, even though my sessions are "sticky" (using the HAProxy terminology), I still don't want to overload a particular backend server just because of front-end load being sent too quickly to the backend server. 

[René Cannaò]

hmm, choosing always the same backend is incompatible with ProxySQL design, as all the backends within an hostgroup are to be considered equivalents.

Yet, the current design allows to isolate (make it sticky) a single connection, so temporary disabling multiplexing on it should suffice.

Queues is still implemented setting max_connections for a backend.

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

[Kevin Heatwole]

On Friday, September 23, 2016 at 3:37:08 PM UTC-4, René Cannaò wrote:

hmm, choosing always the same backend is incompatible with ProxySQL design, as all the backends within an hostgroup are to be considered equivalents.

Yet, the current design allows to isolate (make it sticky) a single connection, so temporary disabling multiplexing on it should suffice.

Queues is still implemented setting max_connections for a backend.

Sounds like your design should work for me as long as queues are supported for a "sticky" connection...

[shahril kamaruzzaman]

Hi Rene,

I also looking for a sticky session features that maybe have introduce under ProxySQL then found this old thread.

I've followed you suggestion steps like above but seems it didn't work :-

[root@6db9cf678ff5 ~]# mysql -u shahril -pDBaaS1234% -h 172.17.0.2 -e "select @@hostname; "

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

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

| @@hostname   |

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

| 6db9cf678ff5 |

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

[root@6db9cf678ff5 ~]# mysql -u shahril -pDBaaS1234% -h 172.17.0.2 -e "select @@hostname; "

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

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

| @@hostname   |

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

| b7775bce7cea |

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

[root@6db9cf678ff5 ~]# mysql -u shahril -pDBaaS1234% -h 172.17.0.2 -e "select @@hostname; "

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

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

| @@hostname   |

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

| 6db9cf678ff5 |

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

[root@6db9cf678ff5 ~]# mysql -u shahril -pDBaaS1234% -h 172.17.0.2 -e "select @@hostname; "

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

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

| @@hostname   |

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

| 6db9cf678ff5 |

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

It's still keep on load balance between MySQL nodes eventhough I've enable the fast_forward as you've suggest. Below are my proxysql configuration :-

  

MySQL [(none)]> select username, fast_forward, transaction_persistent, max_connections from mysql_users where username = 'shahril';

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

| username | fast_forward | transaction_persistent | max_connections |

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

| shahril  | 1            | 1                      | 2000            |

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

1 rows in set (0.00 sec)

MySQL [(none)]> select * from global_variables where variable_name like '%multipl%';

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

| variable_name                       | variable_value |

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

| mysql-connection_delay_multiplex_ms | 0              |

| mysql-multiplexing                  | false          |

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

2 rows in set (0.00 sec)

Kindly appreciate if you could let me know what that i'm missing.

Thanks

[René Cannaò]

Hi Shahril,

Every time you are creating a new connection to ProxySQL is, well, a new connection.
Therefore you cannot have sticky connection on the backend for two distinct connections from the frontend.

In other words, you are doing this:

FrontendConn1 -> proxysql -> BackendConn1
FrontendConn2 -> proxysql -> BackendConn2

You can say "it doesn't work" if you send two queries from the same client connection, but you are using multiple client connections: they are independent from each other.

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

[shahril kamaruzzaman]

Wow, first of all thanks for your quick reply, really appreciate it.

The test above made by same client box access to the proxysql box, only did with different session. Is it means that this not a proper test to ensure sticky session workable or not?

Do you care to share your example how did you make the sticky session works using proxysql.

Again really appreciate your effort on this.

[shahril kamaruzzaman]

Hi Rene,

Care to share on this?

Thanks

[René Cannaò]

Hi Shahril,

For me it is very difficult to understand what are you asking and/or trying to achieve, it you expect that two distinct connections from the same box ends on the same backend.

What is your definition of "sticky sessions" ?

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

[shahril kamaruzzaman]

Hi Rene,

Currently I have 10 webserver possibly will connect to multi-master MySQL via proxysql as middle server. By sticky session I mean I need to direct each connection made by same webserver to same backend MySQL node. Example like ProxySQL can identify the webserver IP address automatically then route to certain MySQL node base on historical transaction. By this I no need to worry if the are several different R/W from same webserver it won't keep jumping between MySQL nodes at backend.

[René Cannaò]

Hi Shahril,

My interpretation of the above is that you don't need sticky connections, but "source IP hash" load balancing algorithm.

ProxySQL doesn't support it (yet?), but can do routing based on client address (IP) using rules in mysql_query_rules. You will need to hardcode each web server IP to a hostgroup with just one DB server.

I am not sure what is your need for this, but I would like to hear more about it.

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+2

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

[shahril kamaruzzaman]

Hi Rene,

Yeah, seems to think like that before also but as it will be more like hardcoded thing therefore I check with you if there's other alternative on this.

Yet just to ensure if need to be hardcode, the source IP of webserver should be updated under column CLIENT_ADDR inside table MYSQL_QUERY_RULES right ?

But im thinking if doing like this how to tackle if the backend db under listed hostgroup_id for that hardcode IP was down or not available? Will it reroute to other active backend db automatically ? 

On Sunday, January 14, 2018 at 1:37:47 AM UTC+8, René Cannaò wrote:

Hi Shahril,

My interpretation of the above is that you don't need sticky connections, but "source IP hash" load balancing algorithm.

ProxySQL doesn't support it (yet?), but can do routing based on client address (IP) using rules in mysql_query_rules. You will need to hardcode each web server IP to a hostgroup with just one DB server.

I am not sure what is your need for this, but I would like to hear more about it.

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+2

[shahril kamaruzzaman]

Hi Rene,

Apologize to disturb again, any ProxySQL case studies comes in mind on this?