percona / percona/pxc_scheduler_handler
Writer node is not returning to readers hostgroup when writerIsAlsoReader is changed
Nobody has claimed this yet.
- Dominant language
- Go
- Stars
- 15
- Forks
- 11
- PR merge metrics
- No merged PRs in 30d
Description
I have 2 nodes, mostly pxc_scheduler_handler default configuration and on proxysql the default split r/w rules:
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE',100,1),(2,1,'^SELECT',101,1);
I tested:
root@proxysql1:~# for i in $(seq 1 100); do echo "select @@hostname for update" | mysql -P 6033 -h 127.0.0.1 -u root -ppxc34327 -NA 2>/dev/null; done | sort | uniq -c
100 node1
root@proxysql1:~# for i in $(seq 1 100); do echo "select @@hostname" | mysql -P 6033 -h 127.0.0.1 -u root -ppxc34327 -NA 2>/dev/null; done | sort | uniq -c
100 node1
I decided to change:
writerIsAlsoReader = 1
to:
writerIsAlsoReader = 0
Nice! it is working:
root@proxysql1:~# for i in $(seq 1 100); do echo "select @@hostname for update" | mysql -P 6033 -h 127.0.0.1 -u root -ppxc34327 -NA 2>/dev/null; done | sort | uniq -c
100 node1
root@proxysql1:~# for i in $(seq 1 100); do echo "select @@hostname" | mysql -P 6033 -h 127.0.0.1 -u root -ppxc34327 -NA 2>/dev/null; done | sort | uniq -c
100 node2
But when I reverted (writerIsAlsoReader back to 1), the servers remains as:
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------------------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------------------------+
| 100 | 10.128.15.250 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | 100_W_101_R_retry_up=0;100_W_101_R_retry_down=0; |
| 101 | 10.128.15.253 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 100 | 10.128.15.253 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------------------------------+```
The pxc_scheduler_handler conf: [pxc_conf.txt](https://github.com/percona/pxc_scheduler_handler/files/9470182/pxc_conf.txt)
I just want to be sure that it is an expected behavior.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the attached pxc_conf.txt and reproduce the writerIsAlsoReader transition using the shown ProxySQL query rules and runtime_mysql_servers output. Trace how the scheduler handles changing the setting in each direction. Done means establishing whether the retained OFFLINE_SOFT hostgroup entry is expected and documenting or fixing the transition behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100