Maxscale 2.4.9 query routing(readwritesplit) fail

469 views
Skip to first unread message

1000

unread,
Apr 6, 2021, 7:29:01 PM4/6/21
to MaxScale
Hi,
I am currently using maxscale 2.4.9.

The server configuration is composed of 4 units in total, 1 master, 2 slaves, and 1 Maxscale server.

When setting readwritesplit, there is a peculiar phenomenon that read queries are routed only to a specific server.



The maxscale config looks like this:
[maxscale]
threads=auto

[server1]
type=server
address=192.168.56.11
port=3011
protocol=MariaDBBackend

[server2]
type=server
address=192.168.56.21
port=3121
protocol=MariaDBBackend

[server3]
type=server
address=192.168.56.28
port=3128
protocol=MariaDBBackend

[MaxAdmin-Service]
type=service
router=cli

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


[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1, server2, server3
user=admin
password=####
monitor_interval=1000
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=on

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1, server2, server3
user=admin
password=####
master_accept_reads=true
slave_selection_criteria=ADAPTIVE_ROUTING
use_sql_variables_in=master

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBclient
port=3248



If server1 server is Master, Read/Write query is routed only to server1.
If server2 server is Slave, there is a problem that Read Query is routed only to server1.
Please tell me how to fix it.


Markus Mäkelä

unread,
Apr 7, 2021, 1:21:36 AM4/7/21
to maxs...@googlegroups.com

Hi,

Are the reads done with autocommit=1 and outside of transactions? All reads inside transactions will be routed to the current master. You can do a sanity check with the command line client and execute SELECT @@hostname, @@server_id to see where the query is routed. You can also enable log_info in MaxScale to see detailed logging about routing decisions.

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.
To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/ae813888-e591-4d44-94db-e61afeadd5c1n%40googlegroups.com.
-- 
Markus Mäkelä, Senior Software Engineer
MariaDB Corporation
Message has been deleted

1000

unread,
Apr 7, 2021, 1:56:02 AM4/7/21
to MaxScale

The server to be routed read query is the slave server, and if it is executed by SELECT @@hostname, @@server_id query from outside, it is routed only to server1. And there is nothing unusual in maxscale.log. How to fix it?

2021년 4월 7일 수요일 오후 2시 21분 36초 UTC+9에 Markus Mäkelä님이 작성:

Markus Mäkelä

unread,
Apr 7, 2021, 2:05:03 AM4/7/21
to maxs...@googlegroups.com

Hi,

I would recommend adding log_info=true under the [maxscale] section of your configuration and capturing the log output from a single session. It's hard to tell why the routing is behaving like it currently does without any of the log output.

You can also test if removing any of the extra parameters you defined helps with the issue:

master_accept_reads=true
slave_selection_criteria=ADAPTIVE_ROUTING
use_sql_variables_in=master

Markus

Message has been deleted

1000

unread,
Apr 7, 2021, 3:01:55 AM4/7/21
to MaxScale
Log check result without changing options

log_info = true
maxscale.log :
04-07 15:52:14   info   : (666889) [readwritesplit] Servers and router connection counts:
2021-04-07 15:52:14   info   : (666889) [readwritesplit] adaptive avg. select time: 175.789us from [192.168.56.11]:3011 Slave, Running
2021-04-07 15:52:14   info   : (666889) [readwritesplit] adaptive avg. select time: 955.746us from [192.168.56.21]:3121 Master, Running
2021-04-07 15:52:14   info   : (666889) [readwritesplit] adaptive avg. select time: 1.20067ms from [192.168.56.28]:3128 Slave, Running
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Connected to 'server2'
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Selected Master: server2
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Connected to 'server1'
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Selected Slave: server1
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Connected to 'server3'
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Selected Slave: server3
2021-04-07 15:52:14   info   : (666889) Started Read-Write-Service client session [666889] for 'dbadmin' from ::ffff:192.168.56.28
2021-04-07 15:52:14   info   : (666889) Connected to 'server1' with thread id 913546
2021-04-07 15:52:14   info   : (666889) Connected to 'server2' with thread id 2085845
2021-04-07 15:52:14   info   : (666889) Connected to 'server3' with thread id 816666
2021-04-07 15:52:14   info   : (666889) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 23, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@server_id 
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Route query to slave: server1 [192.168.56.11]:3011 <
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Reply complete, last reply from server1
2021-04-07 15:52:14   info   : (666889) > Autocommit: [enabled], trx is [not open], cmd: (0x01) COM_QUIT, plen: 5, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Session write, routing to all servers.
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Route query to slave: server1 [192.168.56.11]:3011
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Route query to master: server2 [192.168.56.21]:3121
2021-04-07 15:52:14   info   : (666889) [readwritesplit] Route query to slave: server3 [192.168.56.28]:3128
2021-04-07 15:52:14   info   : Stopped Read-Write-Service client session [666889]


2021년 4월 7일 수요일 오후 3시 5분 3초 UTC+9에 Markus Mäkelä님이 작성:

Markus Mäkelä

unread,
Apr 7, 2021, 3:08:01 AM4/7/21
to maxs...@googlegroups.com

Hi,

MaxScale seems to be working correctly. The server2 is the current master: Selected Master: server2

You can check which server is the master with: maxctrl list servers

Markus

1000

unread,
Apr 11, 2021, 8:55:19 PM4/11/21
to MaxScale

There are queries A, B, C, but query A goes only to server2, and query B is routed only to A. Is there a reason that queries are only routed to a specific server?


I am using slave_selection_criteria = LEAST_CURRENT_OPERATIONS.
2021년 4월 7일 수요일 오후 4시 8분 1초 UTC+9에 Markus Mäkelä님이 작성:

Markus Mäkelä

unread,
Apr 12, 2021, 12:23:01 AM4/12/21
to maxs...@googlegroups.com

Hi,

That might be due to your use of slave_selection_criteria=ADAPTIVE_ROUTING, it's designed to direct traffic to a single node until it starts to slow down and it's not completely deterministic as it picks a server based on the likelihood of it being the fastest to respond to it. It's the inverse of the default slave_selection_criteria=LEAST_CURRENT_OPERATIONS which tries to balance the load as equally as possible based on the number of queries being executed. Under high loads both load balancing methods end up with very similar results.

If you want to test how the queries and connections are load balanced, I'd recommend testing with multiple client connections in order to adequately saturate the servers with queries. This will force the load balancing to actually do some work instead of always picking the server with the least ongoing queries.

Markus

On 4/12/21 3:55 AM, 1000 wrote:

There are queries A, B, C, but query A goes only to server2, and query B is routed only to A. Is there a reason that queries are only routed to a specific server?

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.
Message has been deleted

1000

unread,
Apr 22, 2021, 8:33:42 PM4/22/21
to MaxScale
It is not routed to server 3 at all. Please tell me how to fix it

$ maxctrl show service Read-Write-Service
┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ Service             │ Read-Write-Service                                          │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router              │ readwritesplit                                              │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ State               │ Started                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Started At          │ Thu Apr  8 19:39:08 2021                                    │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Current Connections │ 437                                                         │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Total Connections   │ 962407                                                      │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Servers             │ server1                                                     │
│                     │ server2                                                     │
│                     │ server3                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Filters             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Parameters          │ {                                                           │
│                     │     "router_options": null,                                 │
│                     │     "user": "dbadmin",                                      │
│                     │     "password": "*****",                                    │
│                     │     "enable_root_user": false,                              │
│                     │     "max_retry_interval": 3600,                             │
│                     │     "max_connections": 0,                                   │
│                     │     "connection_timeout": 0,                                │
│                     │     "net_write_timeout": 0,                                 │
│                     │     "auth_all_servers": false,                              │
│                     │     "strip_db_esc": true,                                   │
│                     │     "localhost_match_wildcard_host": true,                  │
│                     │     "version_string": null,                                 │
│                     │     "weightby": null,                                       │
│                     │     "log_auth_warnings": true,                              │
│                     │     "retry_on_failure": true,                               │
│                     │     "session_track_trx_state": false,                       │
│                     │     "retain_last_statements": -1,                           │
│                     │     "session_trace": false,                                 │
│                     │     "cluster": null,                                        │
│                     │     "use_sql_variables_in": "master",                       │
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
│                     │     "master_failure_mode": "fail_instantly",                │
│                     │     "max_slave_replication_lag": 120,                       │
│                     │     "max_slave_connections": "100%",                        │
│                     │     "retry_failed_reads": true,                             │
│                     │     "prune_sescmd_history": false,                          │
│                     │     "disable_sescmd_history": false,                        │
│                     │     "max_sescmd_history": 50,                               │
│                     │     "strict_multi_stmt": false,                             │
│                     │     "strict_sp_calls": false,                               │
│                     │     "master_accept_reads": true,                            │
│                     │     "connection_keepalive": 300,                            │
│                     │     "causal_reads": false,                                  │
│                     │     "causal_reads_timeout": 10,                             │
│                     │     "master_reconnection": false,                           │
│                     │     "delayed_retry": false,                                 │
│                     │     "delayed_retry_timeout": 10,                            │
│                     │     "transaction_replay": false,                            │
│                     │     "transaction_replay_max_size": "1Mi",                   │
│                     │     "transaction_replay_attempts": 5,                       │
│                     │     "transaction_replay_retry_on_deadlock": false,          │
│                     │     "optimistic_trx": false,                                │
│                     │     "lazy_connect": false                                   │
│                     │ }                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router Diagnostics  │ {                                                           │
│                     │     "connections": 962242,                                  │
│                     │     "current_connections": 437,                             │
│                     │     "queries": 39215621,                                    │
│                     │     "route_master": 11968752,                               │
│                     │     "route_slave": 27246869,                                │
│                     │     "route_all": 8904151,                                   │
│                     │     "rw_transactions": 0,                                   │
│                     │     "ro_transactions": 1595247,                             │
│                     │     "replayed_transactions": 0,                             │
│                     │     "server_query_statistics": [                            │
│                     │         {                                                   │
│                     │             "id": "server1",                                │
│                     │             "total": 35844095,                              │
│                     │             "read": 35844095,                               │
│                     │             "write": 0,                                     │
│                     │             "avg_sess_duration": "6.87643min",              │
│                     │             "avg_sess_active_pct": 0.011127564188843439,    │
│                     │             "avg_selects_per_session": 28                   │
│                     │         },                                                  │
│                     │         {                                                   │
│                     │             "id": "server2",                                │
│                     │             "total": 21037320,                              │
│                     │             "read": 9068568,                                │
│                     │             "write": 11968752,                              │
│                     │             "avg_sess_duration": "6.87643min",              │
│                     │             "avg_sess_active_pct": 0.0000919819589078882,   │
│                     │             "avg_selects_per_session": 18                   │
│                     │         },                                                  │
│                     │         {                                                   │
│                     │             "id": "server3",                                │
│                     │             "total": 9046659,                               │
│                     │             "read": 9046659,                                │
│                     │             "write": 0,                                     │
│                     │             "avg_sess_duration": "6.87643min",              │
│                     │             "avg_sess_active_pct": 0.0002396228377142701,   │
│                     │             "avg_selects_per_session": 7                    │
│                     │         }                                                   │
│                     │     ]                                                       │
│                     │ }                                                           │
└─────────────────────┴─────────────────────────────────────────────────────────────┘


2021년 4월 12일 월요일 오후 1시 23분 1초 UTC+9에 Markus Mäkelä님이 작성:

Markus Mäkelä

unread,
Apr 23, 2021, 12:45:50 AM4/23/21
to maxs...@googlegroups.com

Hi,

Without knowing more about your installation and the type of traffic going through MaxScale, it's hard to say why server3 isn't being used. You might want to check out what maxctrl list servers says about it: the connections and active query counts should be distributed equally for all three servers.

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.
Message has been deleted

1000

unread,
Apr 25, 2021, 7:28:04 PM4/25/21
to MaxScale
Hi,

The number of connections is also shown. 

maxctrl list server : 
$ maxctrl list servers
┌───────────────────────┬───────────────┬──────┬─────────────┬─────────────────┬─────────────────┐
│ Server                │ Address       │ Port │ Connections │ State           │ GTID            │
├───────────────────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
│ server1               │ 192.168.56.11 │ 3011 │ 345         │ Slave, Running  │ 0-10121-9760441 │
├───────────────────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
│ server2               │ 192.168.56.22 │ 3121 │ 345         │ Master, Running │ 0-10121-9760441 │
├───────────────────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
│ server3               │ 192.168.56.33 │ 3128 │ 345         │ Slave, Running  │ 0-10121-9760441 │
└───────────────────────┴───────────────┴──────┴─────────────┴─────────────────┴─────────────────┘


2021년 4월 23일 금요일 오후 1시 45분 50초 UTC+9에 Markus Mäkelä님이 작성:

Markus Mäkelä

unread,
Apr 26, 2021, 1:46:38 AM4/26/21
to maxs...@googlegroups.com

Hi,

My apologies, I was confusing the output with the old MaxAdmin command which did show it. You should see it in the show servers output which also has statistics about the server usage.

Markus

Reply all
Reply to author
Forward
0 new messages