Master accept reads but slave doesn't get any in ReadWrite split mode

[joh...@movidone.com]

Hello,

I have following configuration for read-write splitting the load between maria1 and maria3; maria2 is used for backup purpose only so it has the last priority et weight=0.

I'm using Maxscale 2.1.4 and I don't know why but the SELECT statements go all into maria1, and none into maria3.

What should I check to troubleshoot this and to get lesser load on maria1?

Any help appreciated!

[RW]
type=service
router=readwritesplit
servers=maria1,maria2,maria3
max_slave_connections=1
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS,master_accept_reads=true
user=mastersplit
localhost_match_wildcard_host=1
weightby=myweight

[maria1]
type=server
address=10.181.111.74
protocol=MySQLBackend
priority=1
myweight=1

[maria2]
type=server
address=10.181.111.32
protocol=MySQLBackend
priority=3
myweight=0

[maria3]
type=server
address=10.181.111.14
protocol=MySQLBackend
priority=2
myweight=1

[Markus Mäkelä]

Hi,

To test that MaxScale works as you expect, try using the mysql command line client to connect to MaxScale. Executing SELECT @@hostname will allow you to see where the selects are going.

Reasons why SELECT statements can be sent to the master are:

• SELECT inside a transaction
• autocommit is disabled
• SELECT touches a master-only variable or function e.f. LAST_INSERT_ID()
• You have executed multi-statement commands with the option strict_multi_stmt=true (default)

For more details, refer to the ReadWriteSplit documentation and the Limitations document.

If you still have problems, let me know.

Markus

-- 
Markus Mäkelä, Software Engineer
MariaDB Corporation
t: +358 40 7740484 | Skype: markus.j.makela 

[joh...@movidone.com]

Thanks for your answer Markus.

The command SELECT @@hostname is always returning the first node's name even w/o the option master_accept_reads.

No limitation seems to interfere in this use case from what I've read on the provided links.

I attached a screen cap of the observed load of queries on all 3 servers grouped by query type.

Node 1 is getting all the load from maxscale and node 3 isn't even getting a part of the SELECT load.

The strange this is that maxscale's RW Split service show that 49% goes to a slave :
Number of queries forwarded to master: 17530969 (50.20%)
Number of queries forwarded to slave: 17392880 (49.80%)
Number of queries forwarded to all: 5062417 (14.50%)

But as master_accept_reads=1, it seems that master=master and slave=master too,
because there isn't event 10% load going to node 3.

Expected behavior would be :75% to master (50% as master + 25% because master_accept_reads=1) and 25% to slave node3.

[Markus Mäkelä]

Hi,

How many connections do you have open through MaxScale on average? It's possible that with only one or two average connections you'd see output like that.

The slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS option combined with max_slave_connections=1 will pick two servers for each new session; a master and a slave. The root level master is always chosen as the master server and it does not count towards the slave server count. The server with the smallest amount of connections will be chosen as the slave. This means that each client connection picks the master node and one of the slave nodes. With this setup, connections are balanced equally but it alone does not guarantee that the number of queries to each server is balanced equally.

I'd suggest trying out the slave_selection_criteria=LEAST_CURRENT_OPERATIONS mode combined with max_slave_connections=100%.  This is what we call the dynamic load balancing mode where the amount of active operations on each server is used to find the least busy server where the query is routed. With this, you should see equal load on all slave servers.

To figure out how the master_accept_reads parameter fits into this all, we will have to first know what the output of the service diagnostics mean.

Number of queries forwarded to master:  17530969 (50.20%)
Number of queries forwarded to slave:   17392880 (49.80%)
Number of queries forwarded to all:     5062417 (14.50%)

The first number is how many queries classify as queries that must be routed to a master (e.g. INSERT, DELETE). The second number is how many queries classify as read-only queries that do not modify either the database or the connection state. These are mainly SELECT statements that only read data. The last number is how many queries must be routed to all active connections because they somehow change the state of the connection (e.g. SET commands, changing default database).

Looking at that output, you seem to have a roughly 50/50 split of reads and writes which means that you should see at least 50% of the load on the master, as you yourself stated, and the remaining 50% of the reads should be split across all servers that qualify as a slave. The master_accept_reads parameter controls whether the master is included in the list of servers that can be a target of a read-only query. With a three node setup, this causes all thee servers to be considered as slaves.

I'd recommend tracking the output of maxadmin show servers to see how many active operations each server has. This should give a good picture of how many operations MaxScale thinks are running on each server. It's not perfect for this particular purpose but it should be an indicator of whether load balancing works.

I think we could improve the diagnostic output of the readwritesplit router to show per server statistics of the load. Showing this for each server alone would also be a good way to display it.

Markus

[Jo]

Hi Markus,

thanks for the in detail explanation which helped me solving the problem.

Indeed, after enabling dynamic loadbalancing with max_slave_connections=100% and router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS,master_accept_reads=true

all queries get equally balanced between maria1 and maria2 and it's also confirmed by the CPU graph which shows an equivalent CPU load on maria3.





Best regards,
Jo

[Markus Mäkelä]

Hi,

That's great to hear! Let us know if you run into any more strange behavior.

Markus

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Pk Singh]

Hi Markus,

So I have a similar kind of maxscale setup to acheivea read-write split operation between a Mysql 5.6 master-slave setup.

The routing though not working as expected and queries are ending up on master itself.

I have enabled log_info to get more insights and ran SELECT @@hostname command in a loop.

All queries resulted in hostname of master.

Excerpts from maxscale logs regarding the same.

2018-07-18 08:31:21   info   : (5433) [readwritesplit] > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: SELECT @@hostname
2018-07-18 08:31:21   info   : (5433) [readwritesplit] Server [xxx.xxx.xxx.xxx]:3306 is too much behind the master, -1 s. and can't be chosen.
2018-07-18 08:31:21   info   : (5433) [readwritesplit] Server [xxx.xxx.xxx.xxx]:3306 is too much behind the master, -1 s. and can't be chosen.
2018-07-18 08:31:21   info   : (5433) [readwritesplit] Route query to master    [xxx.xxx.xxx.xxx]:3306 <

Below is my Maxscale config

[maxscale]
threads=1

[master]
type=server
address=xxx.xxx.xxx.xxx
port=3306
protocol=MySQLBackend

[slave1]
type=server
address=xxx.xxx.xxx.xxx
port=3306
protocol=MySQLBackend

[slave2]
type=server
address=xxx.xxx.xxx.xxx
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=master,slave1,slave2
user=maxscale
passwd=maxscalepasswd
monitor_interval=10000
detect_replication_lag=true
detect_stale_master=true

[Read-Write Service]
type=service
router=readwritesplit
servers=master, slave1, slave2
user=maxscale
passwd=maxscalepasswd
max_slave_replication_lag=120
max_slave_connections=100%
slave_selection_criteria=LEAST_CURRENT_OPERATIONS
strict_multi_stmt=false

[MaxAdmin Service]
type=service
router=cli

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=3306

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

[CLI]
type=service
router=cli

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603

It would be great if you can point me out what I am doing wrong here.

Thanks in advance.

[Markus Mäkelä]

Hi,

It seems the replication lag between the master and the slaves could not be measured. This probably means that the absolute lag measurement done in the monitor is not working.

Are there any errors in the MaxScale log? If there are, they should point to what is wrong with the replication lag measurement.

Markus

--

You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
Markus Mäkelä, Senior Software Engineer
MariaDB Corporation
t: +358 40 7740484 | IRC: markusjm@freenode

[Pk Singh]

Hi Markus,

The issue was due to the maxscale_schema DB being not sycning between the master and slave.

Working on fixing the replication and update you with the findings.