Maxcale readconnroute‘s router_options not take effect

[Lei Zhang]

Hello，everyone

 My current deployment like bellow：

1）A mgr cluster that consist of  three mysql instences 【Oracle MySQL】

2）A maxScale instence used to router readonly traffic to all replicas 

When i test traffic router，all  read traffics are  router to the first instence，only the first node go down the traffic will router to the second instence。I need help to fix this issue。My MaxScale config like below：

# MaxScale documentation:

# https://mariadb.com/kb/en/mariadb-maxscale-24/

# Global parameters

#

# Complete list of configuration options:

# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-maxscale-configuration-guide/

[maxscale]

threads=auto

local_address=192.168.100.187

# Server definitions

#

# Set the address of the server to the network

# address of a MariaDB server.

#

[server_48]

type=server

address=192.168.100.48

port=3306

protocol=MariaDBBackend

serv_weight=3

[server_183]

type=server

address=192.168.100.183

port=3306

protocol=MariaDBBackend

serv_weight=3

[server_186]

type=server

address=192.168.100.186

port=3306

protocol=MariaDBBackend

serv_weight=3

# Monitor for the servers

#

# This will keep MaxScale aware of the state of the servers.

# MariaDB Monitor documentation:

# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/

[MariaDB-Monitor]

type=monitor

module=mariadbmon

servers=server_48,server_183,server_186

user=maxscale

password=5j%w%j5DWQ3T

monitor_interval=2000

# Service definitions

#

# Service Definition for a read-only service and

# a read/write splitting service.

#

# ReadConnRoute documentation:

# https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/

[Read-Only-Service]

type=service

router=readconnroute

servers=server_183,server_186,server_48

user=maxscale

password=5j%w%j5DWQ3T

router_options=running

# ReadWriteSplit documentation:

# https://mariadb.com/kb/en/mariadb-maxscale-24-readwritesplit/

[Read-Write-Service]

type=service

router=readconnroute

servers=server_48,server_183,server_186

user=maxscale

router_options=master

password=5j%w%j5DWQ3T

router_options=master

# Listener definitions for the services

#

# These listeners represent the ports the

# services will listen on.

#

[Read-Only-Listener]

type=listener

service=Read-Only-Service

protocol=MariaDBClient

port=4008

[Read-Write-Listener]

type=listener

service=Read-Write-Service

protocol=MariaDBClient

port=4006

[MaxAdmin]

type=service

router=cli

[MaxAdmin-Unix-Listener]

type=listener

service=MaxAdmin

protocol=maxscaled

socket=default

[MaxAdmin-Inet-Listener]

type=listener

service=MaxAdmin

protocol=maxscaled

address=localhost

port=6603

[Markus Mäkelä]

Hi,

How many connections did you run in parallel? The routing will only change servers when there's more than one concurrent connection.

In addition, once a connection to readconnroute is opened, it will always use the same server for all queries. If you want query level load balancing, you'll need to use readwritesplit.

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/977e0701-ee06-401d-b65b-f1d0385c8c7cn%40googlegroups.com.

-- 
Markus Mäkelä, Senior Software Engineer
MariaDB Corporation

[Lei Zhang]

Hi，

I use this command to test maxscale，and I just need connection_based loading balance：

  while [ 1 ]; do mysql -h 192.168.100.187 -P 4008 -u maxscale -p`cat /etc/.qiankun` -e "select @@hostname;"; sleep 2; done

[Wagner Bianchi]

This command is hitting up only one of the defined listeners you defined with your configurations:

[Read-Only-Listener]

type=listener

service=Read-Only-Service

protocol=MariaDBClient

port=4008

As Markus said, if you would like to send your database connections to one port and have it hitting master and slave automatically, you need the ReadWriteSplit Router. Otherwise, it would be best if you had your applications sending writes to your defined Read-Write-Listener at 4006 and reads to your Read-Only-Listener at 4008. It is better to have the router_options for your Read-Only-Service set as a slave (https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/#router-options) - did you tested it?

Maybe Markus has additional comments also.

-- Wagner Bianchi Oracle ACE Director Splunk Certified Architect AWS Certified Solutions Architect Oracle Certified Expert (OCE/MySQL) Official Certified MariaDB Professional, 10.3 +55.31.8654.9510 m...@wagnerbianchi.com www.wagnerbianchi.com wbianchijr

To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/4ace333a-53cb-42c2-b06a-a5c5b368e425n%40googlegroups.com.

[Markus Mäkelä]

Hi,

Try something like this:

while true; do for i in $(seq 0 10); do mysql -h 192.168.100.187 -P 4008 -u maxscale -p`cat /etc/.qiankun` -e "select @@hostname;" & done; wait; sleep 2;done

This will create 10 parallel connections and they should get sent to different servers. If you want to emulate slower queries, you can use "select @@hostname, sleep(1)" for the query.

Markus

To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/4ace333a-53cb-42c2-b06a-a5c5b368e425n%40googlegroups.com.