[MXS-588] weightby and readwritesplit not working as expected Created: 2016-02-24  Updated: 2016-02-25  Resolved: 2016-02-25

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 1.3.0
Fix Version/s: 1.3.0

Type: Bug Priority: Minor
Reporter: Frederic -lefred- Descamps Assignee: markus makela
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 6.7
maxscale-1.3.0-1.x86_64


 Description   

one master: percona1
two slaves: percona2 and percona3

I've configured weithby to load balance the requests 90% and 10%, but they are always spread in 50% each:

[Splitter Service]
 
type=service
 
router=readwritesplit
 
servers=percona1, percona2, percona3
 
max_slave_replication_lag=30
 
weightby=myweight
 
user=maxscale
 
passwd=percona
 
 
 
[percona2]
 
type=server
 
address=192.168.90.3
 
port=3306
 
protocol=MySQLBackend
 
myweight=1
 
 
 
[percona3]
 
type=server
 
address=192.168.90.4
 
port=3306
 
protocol=MySQLBackend
 
myweight=9
 
 
 
# maxadmin -pmariadb show service "Splitter Service"
 
Service 0x202b460
 
	Service:                             Splitter Service
 
	Router:                              readwritesplit (0x7f7b78323540)
 
	State:                                       Started
 
	Number of router sessions:           	200
 
	Current no. of router sessions:      	100
 
	Number of queries forwarded:          	600
 
	Number of queries forwarded to master:	0
 
	Number of queries forwarded to slave: 	600
 
	Number of queries forwarded to all:   	0
 
	Master/Slave percentage:		0.00%
 
	Connection distribution based on myweight server parameter.
 
		Server               Target %    Connections  Operations
 
		                               Global  Router
 
		percona3             90.0%     100     100     50
 
		percona2             10.0%     100     100     50
 
		percona1             100.0%     100     100     0
 
	Started:                             Wed Feb 24 13:55:48 2016
 
	Root user access:                    Disabled
 
	Backend databases
 
		192.168.90.4:3306  Protocol: MySQLBackend
 
		192.168.90.3:3306  Protocol: MySQLBackend
 
		192.168.90.2:3306  Protocol: MySQLBackend
 
	Routing weight parameter:            myweight
 
	Users data:                          0x20297c0
 
	Total connections:                   202
 
	Currently connected:                 102
 
	SSL:  Disabled

I expect to have 90% and 10% of operations like showed in the target, or do I miss something ?

Thank you,

 Comments   
Comment by markus makela [ 2016-02-25 ]

Depending on the command it could be executed on all backend servers if the query changes the session state. This means that USE ..., SET autocommit=1 etc. are executed on all backend servers which could explain this behavior.

Please try to do plain select queries like SELECT 1 to see if that causes the distribution to shift

Comment by Frederic -lefred- Descamps [ 2016-02-25 ]

Hi Markus,

In fact you replied me yesterday on IRC, setting

{{router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
max_slave_connections=1}}

resolved it.

Maybe the documentation should be more explicit on how weightby is working with the default slave_selection_criteria

Comment by markus makela [ 2016-02-25 ]

Yes, it should be made absolutely clear that the current mechanism uses statement instead of connections for the load balancing. I'll close this as not a bug.

Generated at Thu Feb 08 04:00:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.