percona / percona/pxc_scheduler_handler

Writer node is not returning to readers hostgroup when writerIsAlsoReader is changed

Open
#62 2 comments 0 reactions 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.