renecannao
commented
9 months ago @nicholascp : I don't know anything about your configuration, but it is likely you have some incorrect configuration. Let me show an example of how to properly report an issue. In this example I illustrate how to configure ProxySQL (all the relevant tables are deleted, new configuration is created, and then loaded to runtime), the commands executed, and finally the result I get. I know nothing about your configuration, and this is absolutely required when creating an issue. In a proper bug report I must also include the error log: maybe proxysql is not able to connect to vm3 , and in the output you wrote so far there is nothing that could exclude this possibility.
For simplicity, in this test I used sqlite3 server as ProxySQL's backend: we need to test ProxySQL's load balancing algorithm, and therefore it doesn't matter what the backend is.
Configuring ProxySQL:
Admin> DELETE FROM mysql_servers;
Query OK, 5 rows affected (0.00 sec)
Admin> DELETE FROM mysql_query_rules;
Query OK, 0 rows affected (0.00 sec)
mysql> SET sqliteserver-mysql_ifaces='0.0.0.0:6030';
Query OK, 1 row affected (0.00 sec)
Admin> LOAD SQLITESERVER VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> INSERT INTO mysql_servers (hostname, port) VALUES ('127.0.0.1',6030), ('127.0.0.2',6030), ('127.0.0.3',6030);
Query OK, 3 rows affected (0.00 sec)
Admin> DELETE FROM mysql_users;
Query OK, 2 rows affected (0.01 sec)
Admin> INSERT INTO mysql_users (username,password) VALUES ('sbtest','sbtest');
Query OK, 1 row affected (0.00 sec)
Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.02 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT hostgroup_id, hostname, port, status, weight FROM runtime_mysql_servers;
+--------------+-----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+-----------+------+--------+--------+
| 0 | 127.0.0.1 | 6030 | ONLINE | 1 |
| 0 | 127.0.0.2 | 6030 | ONLINE | 1 |
| 0 | 127.0.0.3 | 6030 | ONLINE | 1 |
+--------------+-----------+------+--------+--------+
3 rows in set (0.03 sec)
Admin> SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, Queries FROM stats_mysql_connection_pool;
+-----------+-----------+----------+--------+----------+----------+--------+---------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | Queries |
+-----------+-----------+----------+--------+----------+----------+--------+---------+
| 0 | 127.0.0.3 | 6030 | ONLINE | 0 | 0 | 0 | 0 |
| 0 | 127.0.0.1 | 6030 | ONLINE | 0 | 0 | 0 | 0 |
| 0 | 127.0.0.2 | 6030 | ONLINE | 0 | 0 | 0 | 0 |
+-----------+-----------+----------+--------+----------+----------+--------+---------+
3 rows in set (0.02 sec)Run mysqlslap:
rene@vm22:~/proxysql$ for i in `seq 1 100` ; do echo "SELECT 1;" ; done > /tmp/select1.sql
rene@vm22:~/proxysql$ mysqlslap -h 127.0.0.1 -P 6033 -u sbtest -psbtest -c 32 -i 10 -q /tmp/select1.sql
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.228 seconds
Minimum number of seconds to run all queries: 0.204 seconds
Maximum number of seconds to run all queries: 0.285 seconds
Number of clients running queries: 32
Average number of queries per client: 100Back to Admin, verify where the queries where executed:
Admin> SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, Queries FROM stats_mysql_connection_pool;
+-----------+-----------+----------+--------+----------+----------+--------+---------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | Queries |
+-----------+-----------+----------+--------+----------+----------+--------+---------+
| 0 | 127.0.0.3 | 6030 | ONLINE | 0 | 15 | 15 | 10594 |
| 0 | 127.0.0.1 | 6030 | ONLINE | 0 | 14 | 14 | 10738 |
| 0 | 127.0.0.2 | 6030 | ONLINE | 0 | 14 | 14 | 10668 |
+-----------+-----------+----------+--------+----------+----------+--------+---------+
3 rows in set (0.02 sec)
Admin> SELECT SUM(Queries) FROM stats_mysql_connection_pool;
+--------------+
| SUM(Queries) |
+--------------+
| 32000 |
+--------------+
1 row in set (0.02 sec)The above show that the load balancing algorithm works as expected. There is no bug. If you need support, please reach us here: https://proxysql.com/services/support/ Thanks
Hi @nicholascp . You should be happy from the result you get: ProxySQL is using already established connections to execute your trivial query
select @hostname, saving unnecessary time to create a new connection. New connections are clearly not needed for your trivial workload (single connection, 1 query per second).