Proxysql reconnect on failure doesnt work with mysql-java-connector; but works with mysql CLI
1,507 views
Skip to first unread message
Sanjana Pragalathan
Jan 23, 2018, 4:53:54 PM1/23/18
to proxysql
Hi,
It keeps printing the following until the loop completes.
OS: 16.04.3 LTS (Xenial Xerus)
ProxySQL version: ProxySQL version 1.4.5-19-g3b9e408, codename Truls
Looks like I'm missing something fundamental here. Appreciate if you can help.
I have configured proxysql to point to 3 mysql nodes (1 master + 2 slaves).
hostgroup-0 => master
hostgroup-1 => slave+ master (master with low weight)
I also have 6033 to point to master hostgroup and 6034 to point to slave hostgroup.
when i connect to them via proxysql, such as below,
mysql -u root -pmypass -h 127.0.0.1 -P6034
mysql> select @@hostname;
+-----------------+
| @@hostname |
+-----------------+
| ip-10-20-199-69 |
+-----------------+and when i stop mysql running on ip-10-20-199-69, it shows the following.
mysql>select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select @@hostname;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11
Current database: *** NONE ***
+------------------+
| @@hostname |
+------------------+
| ip-10-20-198-182 |
+------------------+
1 row in set (0.01 sec)
This is the behaviour we would want, i.e, when a backend MySQL connection fails, ProxySQL to automatically replace it with another connection (probably to another node) and this should not be visible to application connected to ProxySQL.
But when do try the same thing with java, it never connects to the new node.
public void test() throws Exception { Connection con = null;
try { con = DriverManager.getConnection("jdbc:mysql://proxysql:6034/employees?user=root&password=mypass"); for (int i = 0; i < 100; i++) { try { Statement stmt = con.createStatement(); ResultSet result = stmt.executeQuery("SELECT @@hostname"); if (result.next()) { System.out.println(result.getString(1)); } } catch (Exception ex) {
log.log(Level.SEVERE, ex.getMessage()); } System.out.println("sleeping"); Thread.sleep(1000); } } catch (Exception ex) { log.log(Level.SEVERE, "outer", ex); if (con != null) { con.close(); } } log.info("completed"); }SEVERE: Communications link failureThe last packet successfully received from the server was 1,074 milliseconds ago. The last packet sent successfully to the server was 73 milliseconds ago.
OS: 16.04.3 LTS (Xenial Xerus)
ProxySQL version: ProxySQL version 1.4.5-19-g3b9e408, codename Truls
Java version: 1.8.0_131
MySQL Connector version: mysql-connector-java-6.0.6.jar (tried with 5.1.8 also)
MySQL Server version: mysql-server (5.7.21-0ubuntu0.16.04.1)
MySQL Client version: mysql-client-5.7(5.7.21-0ubuntu0.16.04.1)
Global variables:
mysql> select * from global_variables;
+-----------------------------------------------------+---------------------------+
| variable_name | variable_value |
+-----------------------------------------------------+---------------------------+
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-refresh_interval | 2000 |
| admin-read_only | false |
| admin-hash_passwords | true |
| admin-version | 1.4.5-19-g3b9e408 |
| admin-cluster_username | |
| admin-cluster_password | |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| admin-admin_credentials | admin:admin |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-query_retries_on_failure | 100 |
| mysql-connect_retries_delay | 1 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-default_charset | utf8 |
| mysql-free_connections_pct | 10 |
| mysql-session_idle_ms | 1000 |
| mysql-client_found_rows | true |
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_replication_lag_interval | 1500 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | true |
| mysql-forward_autocommit | false |
| mysql-enforce_autocommit_on_reads | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-autocommit_false_is_transaction | false |
| mysql-verbose_query_error | false |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-wait_timeout | 28800000 |
| mysql-throttle_max_bytes_per_second_to_client | 2147483647 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_stmts_cache | 10000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-default_max_latency_ms | 1000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-server_capabilities | 45578 |
| mysql-session_idle_show_processlist | true |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | (null) |
| mysql-ssl_p2s_cert | (null) |
| mysql-ssl_p2s_key | (null) |
| mysql-ssl_p2s_cipher | (null) |
| mysql-init_connect | (null) |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-threads | 4 |
| mysql-max_connections | 2048 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-have_compress | true |
| mysql-poll_timeout | 2000 |
| mysql-interfaces | 0.0.0.0:6033;0.0.0.0:6034 |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.5.30 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_username | root |
| mysql-monitor_password | okta |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-commands_stats | true |
| mysql-sessions_sort | true |
| mysql-connect_retries_on_failure | 10 |
+-----------------------------------------------------+---------------------------+
124 rows in set (0.00 sec)
mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 10.20.198.182 | 3306 | ONLINE | 1 | 0 | 1000 | 5 | 0 | 0 | |
| 1 | 10.20.197.114 | 3306 | ONLINE | 1000 | 0 | 1000 | 5 | 0 | 0 | |
| 1 | 10.20.199.69 | 3306 | ONLINE | 1000 | 0 | 1000 | 5 | 0 | 0 | |
| 1 | 10.20.198.182 | 3306 | ONLINE | 1 | 0 | 1000 | 5 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)Thanks,
Pragalathan M
Nikolaos Vyzas
Jan 24, 2018, 8:00:27 AM1/24/18
to proxysql
If I'm understanding correctly, the initial connection succeeds however the retries fail - this could be happening because you are initialising the connection outside of your loop and it is lost when you execute "Statement stmt = con.createStatement();".
con = DriverManager.getConnection("jdbc:mysql://proxysql:6034/employees?user=root&password=mypass");
Try putting this line within your loop just before "con.createStatment()".
Nikolaos Vyzas
Jan 24, 2018, 8:50:26 AM1/24/18
to proxysql
Also to further elaborate - when you are running the command "select @@hostname" ProxySQL disables multiplexing.
If you try with "select 1" you'll notice that the backend connection is retried on another server.
Please have a look at https://github.com/sysown/proxysql/wiki/Multiplexing for further information regarding scenarios where multiplexing will be disabled.
On Tuesday, January 23, 2018 at 11:53:54 PM UTC+2, Sanjana Pragalathan wrote:
Nikolaos Vyzas
Jan 24, 2018, 8:57:54 AM1/24/18
to proxysql
Finally, for the sake of completeness :) please be aware that in the example of the MySQL CLI test the output indicates that the client is reconnecting since ProxySQL killed the frontend connection:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
No connection. Trying to reconnect...
This is the equivalent of the "con.DriverManager.getConnection..." call.
On Tuesday, January 23, 2018 at 11:53:54 PM UTC+2, Sanjana Pragalathan wrote:
Sanjana Pragalathan
Jan 24, 2018, 12:40:29 PM1/24/18
to proxysql
Hi Nikolaos. Thanks for your time & responding to my queries. I replaced "select @@hostname" with "select 1". the result is same. I didnt move the getConnection inside the loop though.
Appreciate your help.
If i establish the connection inside the loop, any query works.
He is my requirement:
I have a tomcat+spring managed mysql connection pool. I want to replace the direct connections with proxysql->mysql.
When any connection between proxysql & mysql is broken (due to reasons such as mysql node is restarted/stopped) the corresponding app->proxysql should be wired with another valid mysql connection without letting app know that the mysql connection is broken.
But it looks like this cant be achieved. perhaps my misunderstanding. I read the following, from http://proxysql.blogspot.ca/2015/09/proxysql-tutorial-setup-in-mysql.html.
When a connection to a backend fails either during the connect phase or during the execution of a query, ProxySQL reacts to such event trying to establish a new connection (possibly on a different node) and eventually retry the query
René Cannaò
Jan 24, 2018, 1:01:11 PM1/24/18
to proxysql
This is likely jdbc driver sending some query that disables multiplexing.
Can you copy the output of "SELECT digest_text FROM stats_mysql_query_digest" after your test?
--
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.
Pragalathan
Jan 24, 2018, 1:49:13 PM1/24/18
to proxysql
Hi René.Please find the output. (i couldnt clear the table before running the test. not sure if that is possible/required)
mysql> SELECT digest_text FROM stats_mysql_query_digest;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| digest_text
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select ?
|
| SET character_set_results = NULL
|
| 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 A
S 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 l
ower_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_cac
he_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 |
| select ?
|
| SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment
|
| show tables
|
| select count(*) from employees
|
| 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 A
S 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 l
ower_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_cac
he_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 |
| SET NAMES utf8mb4
|
| SELECT @@session.auto_increment_increment
|
| select @@hostname
|
| SHOW COLLATION
|
| select @hostname
|
| set autocommit=?, sql_mode = concat(@@sql_mode,?)
|
| SET autocommit=?
|
| SET character_set_results = NULL
|
| SELECT @@hostname
|
| bles
|
| SHOW VARIABLES WHERE Variable_name =? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_nam
e = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Variable_name = ? OR Vari
able_name = ? OR Variable_name = ? OR Variable_name = ?
|
| select @@version_comment limit ?
|
| select @@hostname
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.00 sec)
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
René Cannaò
Jan 24, 2018, 1:59:01 PM1/24/18
to proxysql
All the queries where there is a @ cause ProxySQL to disable multiplexing and therefore also retry mechanism.
I would suggest to take the digest from all the queries with a @ , and create a rule for them to not disable multiplexing.For reference: https://github.com/sysown/proxysql/wiki/Multiplexing#ad-hoc-enabledisable-of-multiplexing
Something like:
INSERT INTO mysql_query_rules (active, digest, multiplex) SELECT 1,digest,2 FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%';
INSERT INTO mysql_query_rules (active, digest, multiplex) SELECT 1,digest,2 FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%';
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
Pragalathan
Jan 24, 2018, 4:07:08 PM1/24/18
to proxysql
- I retried the proxysql to ensure that there no old connection to backend mysql.
- Started running 'select 1' that created a connection to hostgroup1 as shown below
mysql> select * from stats_mysql_connection_pool;
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0 | 10.20.198.182 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 432 |
| 1 | 10.20.197.114 | 3306 | ONLINE | 1 | 0 | 1 | 0 | 14 | 1001 | 315 | 401 |
| 1 | 10.20.199.69 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 454 |
| 1 | 10.20.198.182 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 432 |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- Stopped the mysql
This is to ensure that no connection is set to multiplexing=disabled as no query were run with @.
The result is same. after that node is stopped query was not retried.
I also tried moving the following statement inside the loop. Still the result is same.
Statement stmt = con.createStatement();
Here is the content of digest table.
mysql> select digest_text from stats_mysql_query_digest;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| digest_text
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SET autocommit=?
|
| select ?
|
| SET character_set_results = NULL
|
| 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 A
S 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 l
ower_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_cac
he_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 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
I added the rule definition as you suggested.
...
Pragalathan
Jan 24, 2018, 4:12:06 PM1/24/18
to proxysql
Sorry. could add this in the last reply. Here is the rules table.
Even after this, query is failing. Can you please check and suggest.
mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6033
digest: NULL
match_digest: NULL
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 0
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
log: NULL
apply: 1
comment: NULL
*************************** 2. row ***************************
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6034
digest: NULL
match_digest: NULL
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 1
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: 2
log: NULL
apply: 1
comment: NULL
*************************** 3. row ***************************
rule_id: 3
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: 0x2B838C3B5DE79958
match_digest: NULL
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: NULL
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: 2
log: NULL
apply: 1
comment: NULL
3 rows in set (0.00 sec)
mysql>
Even after this, query is failing. Can you please check and suggest.
René Cannaò
Jan 24, 2018, 4:21:54 PM1/24/18
to proxysql
You missed this part:
set the rules in the right order (perhaps you may want to set these rules before any routing rules)
--
You received this message because you are subscribed to the Google Groups "proxysql" group.
Pragalathan
Jan 24, 2018, 7:32:03 PM1/24/18
to proxysql
Thanks for your patience.
the updated table looks like below.
mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
rule_id: 3*************************** 2. row ***************************
rule_id: 4*************************** 3. row ***************************
rule_id: 53 rows in set (0.01 sec)
The result is same. Is this order correct?
On a side note, Is there a link where i can read more about the columns and their possible values to understand them better?
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
Pragalathan
Jan 25, 2018, 2:21:17 PM1/25/18
to proxysql
Here is the final configuration. I retained only one hostgroup.
What works?
When I run the query "show tables;" multiple times using mysql CLI, it seems the single connection to proxysql is more or less evenly distributed among mysql servers (multiplexing works! yes.) I broght down 10.20.197.114 for sometime; hence the difference in stats_mysql_connection_pool#Queries.mysql> select * from stats_mysql_connection_pool;
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0 | 10.20.197.114 | 3306 | ONLINE | 0 | 1 | 2 | 16 | 10 | 116 | 1063 | 410 |
| 0 | 10.20.199.69 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 16 | 182 | 1976 | 472 |
| 0 | 10.20.198.182 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 436 |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)
mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | employees | root | 0x02033E45904D3DF0 | show databases | 2 | 1516906709 | 1516906795 | 2852 | 1021 | 1831 |
| 0 | employees | root | 0x99531AEFF718C501 | show tables | 22 | 1516906709 | 1516907265 | 20512 | 566 | 1986 |
| 0 | information_schema | root | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1516906708 | 1516906708 | 1517 | 1517 | 1517 |
| 0 | information_schema | root | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1516906682 | 1516906682 | 0 | 0 | 0 |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
4 rows in set (0.00 sec)
mysql> select * from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 10.20.197.114 | 3306 | ONLINE | 1000 | 0 | 1000 | 5 | 0 | 0 | |
| 0 | 10.20.198.182 | 3306 | ONLINE | 1 | 0 | 1000 | 5 | 0 | 0 | |
| 0 | 10.20.199.69 | 3306 | ONLINE | 1000 | 0 | 1000 | 5 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: 6034
digest: NULL
match_digest: NULL
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 0
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: 2
log: NULL
apply: 1
comment: NULL
*************************** 2. row ***************************
rule_id: 3
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: 0x2B838C3B5DE79958
match_digest: NULL
match_pattern: NULL
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 1
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: 2
log: NULL
apply: 1
comment: NULL
2 rows in set (0.00 sec)
mysql>
What doesnt work?
I ran the same query using java mysql connector 6.0.6. All the queries went to one host (10.20.199.69)
mysql> select * from stats_mysql_connection_pool;
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0 | 10.20.197.114 | 3306 | ONLINE | 0 | 1 | 2 | 16 | 12 | 1000 | 1436 | 421 |
| 0 | 10.20.199.69 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 67 | 764 | 8476 | 440 |
| 0 | 10.20.198.182 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 352 |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)
mysql> select digest, digest_text from stats_mysql_query_digest\G
*************************** 1. row ***************************
digest: 0xC51BDBBD6C9BB1D7
digest_text: SET autocommit=?
*************************** 2. row ***************************
digest: 0x32DD8987F5DA033D
digest_text: SET character_set_results = NULL
*************************** 3. row ***************************
digest: 0x2B838C3B5DE79958
digest_text: SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@chara
cter_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, @@lo
wer_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_timeo
ut AS wait_timeout
*************************** 4. row ***************************
digest: 0x620B328FE9D6D71A
digest_text: SELECT DATABASE()
*************************** 5. row ***************************
digest: 0x99531AEFF718C501
digest_text: show tables
*************************** 6. row ***************************
digest: 0x226CD90D52A2BA0B
digest_text: select @@version_comment limit ?
*************************** 7. row ***************************
digest: 0x02033E45904D3DF0
digest_text: show databases
7 rows in set (0.01 sec)
Pragalathan
Jan 25, 2018, 3:50:04 PM1/25/18
to proxysql
René, is there any known/specific setting that i need to have, to use proxysql multiplexing with java client?
Reply all
Reply to author
Forward
0 new messages