Giter Club home page Giter Club logo

Comments (3)

JavierJF avatar JavierJF commented on June 16, 2024

Hi,

I don't see the problem with the ordering on the mysql_servers columns, that ordering has always being the same. In ProxySQL source code:

ack "mysql_servers .* status .* weight" lib/ProxySQL_Admin.cpp:

#define ADMIN_SQLITE_TABLE_MYSQL_SERVERS_V2_0_0c "CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"
#define ADMIN_SQLITE_TABLE_MYSQL_SERVERS_V2_0_11 "CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"
#define ADMIN_SQLITE_TABLE_RUNTIME_MYSQL_SERVERS "CREATE TABLE runtime_mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )"

That release can be found in our repository, https://repo.proxysql.com/ProxySQL/proxysql-2.3.x/centos/7/proxysql-2.3.2-1-centos7.x86_64.rpm, and the version also looks correct:

[root@ workdir]# proxysql --version
ProxySQL version 2.3.2-10-g8cd66cf, codename Truls

Regarding this:

We encounter a quite strange issue after running the LOAD MYSQL SERVERS TO RUNTIME
The issue is after that whe have the following warning: "Galera: too many writers in HG 101"
writer hostgroup id is 101

There is no description on which one was the issue you faced, logs, or any of the information that is required by the issue template. Please elaborate on the issue you faced and provide the required information, so far, there is no evidence of any issue.

Thanks, Javier.

from proxysql.

Neurozone avatar Neurozone commented on June 16, 2024

proxysql_too_many_writers.txt

Adding proxysql log file for you to check.
Happened on the same node, 003
Just changed the name of the severs to remove possibly sensitive informations

Look line 1: 2024-05-20 12:26:47 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command

And line 103: 2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5548:update_galera_set_writer(): [WARNING] Galera: too many writers in HG 101. Max=1, current=3

when it happened we encounter certifications issues during intensive load windows

from proxysql.

JavierJF avatar JavierJF commented on June 16, 2024

There is no issue present in these logs. The [WARNING] message is issued because the configuration is setting 3 servers in the writer hostgroup, when the maximum is set to be 1. The next monitoring action, when finding this extra servers in the hostgroup sets them as SHUNNED.

mysql_servers configuration supplied:

2024-05-20 12:26:47 [INFO] Dumping mysql_servers_incoming
+--------------+-----------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------------------------------+
| hostgroup_id | hostname                                            | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                                             |
+--------------+-----------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------------------------------+
| 101          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 103          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 103          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 102          | node001 | 3301 | 0         | 1001   | 0      | 0           | 15000           | 0                   | 1       | 0              | node001 |
| 102          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 102          | node002 | 3301 | 0         | 1000   | 0      | 0           | 15000           | 0                   | 1       | 0              | node002 |
| 101          | node003 | 3301 | 0         | 999    | 0      | 0           | 15000           | 0                   | 1       | 0              | node003 |
| 101          | node001 | 3301 | 0         | 1001   | 0      | 0           | 15000           | 0                   | 1       | 0              | node001 |
+--------------+-----------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------------------------------------+

All nodes are loaded into hostgroup 101, the writer hostgroup. The next monitoring action ProxySQL issues the WARNING and sets the nodes as SHUNNED:

2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5548:update_galera_set_writer(): [WARNING] Galera: too many writers in HG 101. Max=1, current=3
2024-05-20 12:26:48 MySQL_HostGroups_Manager.cpp:5643:update_galera_set_writer(): [WARNING] Galera: setting host node001:3301 as writer
2024-05-20 12:26:48 [INFO] Galera: Node status changed by ProxySQL, dumping all galera nodes status:
...
2024-05-20 12:26:48 [INFO] Changing status for server 101:node002:3301 (node002:3301) from 0 (0) to 1
2024-05-20 12:26:48 [INFO] Changing status for server 101:node003:3301 (node003:3301) from 0 (0) to 1

There are no miss-behaviors present in this log. Regarding this:

when it happened we encounter certifications issues during intensive load windows

Which certification issues? They haven't been added as the info supplied here, also, there is no evidence at all of correlation between those events.

What we can see here, is a warning regarding a potential undesired configuration, since the user is placing a number of servers in the writer_hostgroup that contradicts the maximum number of writers, so the next monitoring action reports about this, and corrects the server placement.

I'm closing this issue for now, as it's an amalgamation of several questions that not issues themselves. If you want to create a new ticket about his:

when it happened we encounter certifications issues during intensive load windows

Please provide all the information requested in the issue template.

Thanks, Javier.

from proxysql.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.