Round robin between slave servers

Yannick Jaquier

unread,

Jul 6, 2016, 11:47:59 AM7/6/16

to proxysql

Hello,

Promising product on the paper, thank you !

I have this test configuration (somehow discover that mysql_replication_hostgroups must be configured):

mysql> SELECT * FROM mysql_servers;

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

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

| 0 | 192.168.56.102 | 3316 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 10 |

| 1 | 192.168.56.103 | 3316 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 10 |

| 1 | 192.168.56.103 | 3326 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 10 |

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

3 rows in set (0.00 sec)

mysql> select * from mysql_replication_hostgroups;

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

| writer_hostgroup | reader_hostgroup |

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

| 0 | 1 |

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

1 row in set (0.00 sec)

Is it expected that ProxySQL does not round robin between my slaves if I do not end up the connection (or I'm simply missing something in the configuration). By mistake I have started with an old ProxySQL release that was handling this perfectly:

[mysql@server4 ~]$ /mysql/software/mysql01/bin/mysql --user=test --password=secure_password --host=127.0.0.1 --port=6033 --ssl-mode=disabled

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

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 80

Server version: 5.7.13 (ProxySQL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@hostname,@@port;

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

| @@hostname | @@port |

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

| server3.domain.com | 3326 |

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

1 row in set (0.01 sec)

mysql> SELECT @@hostname,@@port;

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

| @@hostname | @@port |

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

| server3.domain.com | 3326 |

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

1 row in set (0.00 sec)

.

In fact I have tested this with a small Java test application under Eclipse using MySQL Connector/J and MariaDB Connector/J...

Thanks,

Yannick.

René Cannaò

unread,

Jul 6, 2016, 12:02:07 PM7/6/16

to Yannick Jaquier, proxysql

Hi Yannick,

Thank you!

I think the old ProxySQL release you were referring to was indeed quite old, 7-8 months or so.

ProxySQL is not able to track user variable, but it is important that if the application sets an user variable in a connection (or simply use it), that user variable stays present. To ensure that, "recent" versions of ProxySQL disable connections multiplexing if an "at" symbol (@) is detected in the query digest.

Therefore, if you run "SELECT @@hostname, @@port" , ProxySQL will immediately disable connections multiplexing.

Old releases of ProxySQL do not have this protection, so multiplexing wasn't being disabled.

If instead of running "SELECT @@hostname, @@port" you run the follow query, multiplexing will still be enabled:

SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='hostname') `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='port') `port`;

Furthermore, you can debug this issue more running this query in the admin interface:
SELECT * FROM stats_mysql_connection_pool;

ConnUsed should return the number of connections currently in use (including the one assigned to a session), while ConnFree are the connections in the connection pool free to use.

Thank you,

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+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Yannick Jaquier

unread,

Jul 7, 2016, 3:01:10 AM7/7/16

to proxysql, yannick...@gmail.com

Hi René,

Thanks for super fast answer !

I have tried your trick even if I'm not 100% getting your story on multiplexing, I need to read more on this on your web site...

But I still get the same behavior. The first chosen slave is the one that is forever chosen in same session...

Am I asking to much on this round robin selection of slaves or this is expected behavior ?

The stats_mysql_connection_pool does not provide much more information as obviously each time I re-initiate a connection the slaves are all chosen randomly:

mysql> SELECT * FROM stats_mysql_connection_pool;

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

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

| 0 | 192.168.56.102 | 3316 | ONLINE | 0 | 0 | 36 | 0 | 319 | 25054 | 18100 | 635 |

| 0 | 192.168.56.103 | 3326 | OFFLINE_HARD | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 631 |

| 1 | 192.168.56.103 | 3316 | ONLINE | 1 | 0 | 39 | 0 | 180 | 12350 | 6216 | 508 |

| 1 | 192.168.56.103 | 3326 | ONLINE | 0 | 0 | 43 | 0 | 165 | 12610 | 5412 | 631 |

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

4 rows in set (0.00 sec)

Thanks,

Yannick.

René Cannaò

unread,

Jul 7, 2016, 3:09:42 AM7/7/16

to Yannick Jaquier, proxysql

Hi Yannick,

I tried to answer as soon as I can :)

stats_mysql_connection_pool actually provides some useful information:

a) ConnUsed is 1 : it means that a connection is in use by a session, and unless there is a query running at that moment it means that the connection is bind to that session

b) ConnFree is 0 while ConnOK (successful connections) is around 40. That means that connections are always terminated once not in use anymore.

Can you please have a look at "SELECT * FROM stats_mysql_query_digest" ? Probably there we can find what query is causing ProxySQL to disable multiplexing.

Thank you,

René

Yannick Jaquier

unread,

Jul 7, 2016, 3:29:24 AM7/7/16

to proxysql, yannick...@gmail.com

Hi René,

What I had (most probably a bit difficult to read):

mysql> SELECT * FROM stats_mysql_query_digest;

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

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

| 0 | information_schema | test | 0x13C1B030330ABFAF | SHOW VARIABLES WHERE Variable_name in (?, ?, ?, ?) | 34 | 1467724980 | 1467729356 | 114990 | 2053 | 3801 |

| 1 | information_schema | test | 0x4C4CA35E3C899402 | SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=?) `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=?) `port` | 43 | 1467873304 | 1467873967 | 102964 | 2118 | 4367 |

| 0 | information_schema | test | 0x29648F83A6DFB03B | SELECT concat(@@hostname,?,@@port) as variable_value for update | 246 | 1467724980 | 1467729356 | 182516 | 290 | 1046 |

| 0 | information_schema | test | 0x8B3EDFFC1D92525A | set session autocommit=? | 34 | 1467724980 | 1467729356 | 21437 | 330 | 726 |

| 0 | information_schema | test | 0x3ABB94868E2C708C | SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@language AS language, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout | 1 | 1467725287 | 1467725287 | 2211 | 2211 | 2211 |

| 0 | information_schema | test | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 46 | 1467725241 | 1467873337 | 0 | 0 | 0 |

| 0 | information_schema | test | 0x2B838C3B5DE79958 | SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout | 2 | 1467725604 | 1467725686 | 3180 | 958 | 2222 |

| 1 | information_schema | test | 0x2C69F23F00BF749B | SELECT concat(@@hostname,?,@@port) as variable_value | 246 | 1467724980 | 1467729356 | 439477 | 326 | 204978 |

| 1 | information_schema | test | 0xA56346186F5C59DF | SELECT @@hostname,@@port | 60 | 1467725241 | 1467818808 | 122762 | 662 | 5036 |

| 0 | information_schema | test | 0xC51BDBBD6C9BB1D7 | SET autocommit=? | 2 | 1467725604 | 1467725686 | 0 | 0 | 0 |

| 0 | information_schema | test | 0x32DD8987F5DA033D | SET character_set_results = NULL | 2 | 1467725604 | 1467725686 | 1202 | 527 | 675 |

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

11 rows in set (0.00 sec)

Then I restarted ProxySQL but still same behavior with obviously now only on row in stats_mysql_query_digest:

mysql> SELECT * FROM stats_mysql_query_digest;

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

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

| 1 | information_schema | test | 0x4C4CA35E3C899402 | SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=?) `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=?) `port` | 6 | 1467876389 | 1467876394 | 15735 | 2193 | 4341 |

| 0 | information_schema | test | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1467876388 | 1467876388 | 0 | 0 | 0 |

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

2 rows in set (0.00 sec)

Thanks,

Yannick.

René Cannaò

unread,

Jul 7, 2016, 4:26:09 AM7/7/16

to Yannick Jaquier, proxysql

Hi Yannick,

I am not able to reproduce it, for me works correctly:

mysql> SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='hostname') `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='port') `port`;
+-----------+-------+
| hostname | port |
+-----------+-------+
| ubuntu-14 | 21893 |
+-----------+-------+

1 row in set (0.00 sec)

mysql> SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='hostname') `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='port') `port`;
+-----------+-------+
| hostname | port |
+-----------+-------+
| ubuntu-14 | 21892 |
+-----------+-------+

1 row in set (0.00 sec)

mysql> SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='hostname') `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='port') `port`;
+-----------+-------+
| hostname | port |
+-----------+-------+
| ubuntu-14 | 21891 |
+-----------+-------+
1 row in set (0.06 sec)

Maybe multiplexing is disabled globally?

Can I have a look at "SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%';" ?

Thanks

Yannick Jaquier

unread,

Jul 7, 2016, 5:27:53 AM7/7/16

to proxysql, yannick...@gmail.com

Hi René,

Okay got it ! mysql-multiplexing was set to false !!

Below sequence solved the issue:

set mysql-multiplexing='true';

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

Why did you set mysql-multiplexing to false by default ??!! :-)

Thanks,

Yannick.

René Cannaò

unread,

Jul 7, 2016, 5:33:59 AM7/7/16

to Yannick Jaquier, proxysql

If it is set false by default it is surely a bug!
I will double check in few hours.

Thank you

Yannick Jaquier

unread,

Jul 7, 2016, 5:38:53 AM7/7/16

to proxysql, yannick...@gmail.com

René,

Forget it, I'm stupid !

I have search in the long history of my putty session and the mistake is only mine. I have set mysql-multiplexing to false for I don't know why.

Default value is true: I confirm !

Thanks again and sorry for time wasted,

Yannick.

René Cannaò

unread,

Jul 7, 2016, 5:40:34 AM7/7/16

to Yannick Jaquier, proxysql

Hi Yannick,

Bo problem at all!
Thank you for verifying that default was true and that you erroneously set it to false.

Thanks

Reply all

Reply to author

Forward