[MXS-1260] MaxAdmin show service missing stats with MariaDB 5.1 MySQLBackend Created: 2017-05-07  Updated: 2018-08-29  Resolved: 2018-08-29

Status: Closed
Project: MariaDB MaxScale
Component/s: maxadmin
Affects Version/s: 2.0.5
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Andrea Barbieri Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

CentOS 7.3.1611

Issue Links:
Relates
relates to MXS-1244 MySQL monitor "detect_replication_lag... Closed

 Description   

I have setup a readconnroute service in /etc/maxscale.cnf with patched maxscale service from MXS-1244 to cope with MariaDB 5.1.62 backend Replication Cluster topology:

[c3-server1]
 
type=server
 
address=10.10.80.170
 
port=3306
 
protocol=MySQLBackend
 
myweight=100
 
 
 
[c3-server2]
 
type=server
 
address=10.10.80.169
 
port=3306
 
protocol=MySQLBackend
 
myweight=100
 
 
 
[c3-server3]
 
type=server
 
address=10.10.80.171
 
port=3306
 
protocol=MySQLBackend
 
myweight=100
 
 
 
[c3-server4]
 
type=server
 
address=10.10.80.172
 
port=3306
 
protocol=MySQLBackend
 
myweight=100
 
 
 
[c3-server5]
 
type=server
 
address=10.10.80.175
 
port=3306
 
protocol=MySQLBackend
 
myweight=100
 
 
 
[C3-RO-Top20]
 
type=filter
 
module=topfilter
 
filebase=/var/log/maxscale/C3-RO-Top20
 
count=20
 
#match=
 
#exclude=
 
#source=
 
#user=
 
 
 
[Read-Only Service C3]
 
type=service
 
router=readconnroute
 
servers=c3-server1,c3-server2,c3-server3,c3-server4,c3-server5
 
user=maxscale
 
passwd=obfuscated
 
router_options=slave
 
filters=C3-RO-Top20
 
weightby=myweight
 
 
 
[Read-Only Listener C3]
 
type=listener
 
service=Read-Only Service C3
 
protocol=MySQLClient
 
port=6008

I have captured with tcpdump the mysql traffic on the server hosting the MaxScale service before and after exercising "Read-Only Service C3" (30 minutes each capture).

before

$ tshark -nr mysql_1.pcap -q -z ip_hosts,tree
 
 
 
===================================================================
 
 IP Addresses         value	        rate	     percent
 
-------------------------------------------------------------------
 
 IP Addresses       103450       0.057559                
  10.10.81.71         63731       0.035471         100.00%
 
  10.10.80.165         6829       0.003801          10.72%
 
  10.10.80.166         4680       0.002605           7.34%
 
  10.10.80.167         4676       0.002603           7.34%
 
  10.10.80.168         4687       0.002609           7.35%
 
  10.10.80.174         4687       0.002609           7.35%
 
  10.10.80.161         6801       0.003785          10.67%
 
  10.10.80.162         4666       0.002597           7.32%
 
  10.10.80.163         4681       0.002605           7.34%
 
  10.10.80.164         4670       0.002599           7.33%
 
  10.10.80.173         4661       0.002594           7.31%
 
  10.10.80.170         3423       0.001905           5.37%
 
  10.10.80.169         2317       0.001290           3.64%
 
  10.10.80.171         2316       0.001289           3.63%
 
  10.10.80.172         2315       0.001288           3.63%
 
  10.10.80.175         2322       0.001292           3.64%
 
 
 
===================================================================

after

$ tshark -nr mysql_2.pcap -q -z ip_hosts,tree
 
 
 
===================================================================
 
 IP Addresses         value	        rate	     percent
 
-------------------------------------------------------------------
 
 IP Addresses       103450       0.057559                
  10.10.81.71        103450       0.057559         100.00%
 
  10.10.80.161         6806       0.003787           6.58%
 
  10.10.80.162         4674       0.002601           4.52%
 
  10.10.80.163         4670       0.002598           4.51%
 
  10.10.80.164         4664       0.002595           4.51%
 
  10.10.80.173         4672       0.002599           4.52%
 
  10.10.80.165         6806       0.003787           6.58%
 
  10.10.80.166         4676       0.002602           4.52%
 
  10.10.80.167         4666       0.002596           4.51%
 
  10.10.80.168         4665       0.002596           4.51%
 
  10.10.80.174         4678       0.002603           4.52%
 
  10.10.80.170         3471       0.001931           3.36%
 
  10.10.80.169        12966       0.007214          12.53%
 
  10.10.80.171        12212       0.006795          11.80%
 
  10.10.80.172        12543       0.006979          12.12%
 
  10.10.80.175        11281       0.006277          10.90%
 
 
 
===================================================================

as it can be seen the load balancing was substantially fair for the slaves of that cluster as expected by using the readconnroute service type but the stats reported by maxadmin don't reflect this (I have introduced the weightby to make the stats more explicit with regards the achieved load-balancing).

$ maxadmin show service "Read-Only Service C3"
 
Service 0xf59120
 
	Service:                             Read-Only Service C3
 
	Router:                              readconnroute (0x7f46a764e140)
 
	State:                               Started
 
	Number of router sessions:   	723
 
	Current no. of router sessions:	0
 
	Number of queries forwarded:   	13212
 
	Connection distribution based on myweight server parameter.
 
		Server               Target % Connections
 
		c3-server1           20.0%     0
 
		c3-server2           20.0%     0
 
		c3-server3           20.0%     0
 
		c3-server4           20.0%     0
 
		c3-server5           20.0%     0
 
	Started:                             Thu Apr 27 08:39:21 2017
 
	Root user access:                    Disabled
 
	Filter chain:                C3-RO-Top20  
	Backend databases:
 
		10.10.80.170:3306  Protocol: MySQLBackend
 
		10.10.80.169:3306  Protocol: MySQLBackend
 
		10.10.80.171:3306  Protocol: MySQLBackend
 
		10.10.80.172:3306  Protocol: MySQLBackend
 
		10.10.80.175:3306  Protocol: MySQLBackend
 
	Routing weight parameter:            myweight
 
	Users data:                          0xf9f890
 
	Total connections:                   724
 
	Currently connected:                 1



 Comments   
Comment by Andrea Barbieri [ 2017-05-12 ]

no further testing took place but the relevant individual server stats do still provide evidence of load-balancing fairness for client queries:

Server 0xf5c0a0 (c3-server1)
 
	Server:                              10.10.80.170
 
	Status:                              Master, Running
 
	Protocol:                            MySQLBackend
 
	Port:                                3306
 
	Server Version:                      5.1.62-MariaDB-mariadb115-log
 
	Node Id:                             168448170
 
	Master Id:                           -1
 
	Slave Ids:                           168448169,  168448171 , 168448172 , 168448175 
	Repl Depth:                          0
 
	Last Repl Heartbeat:                 Fri May 12 05:07:34 2017
 
	Server Parameters:
 
	                                       myweight	100
 
	Number of connections:               7
 
	Current no. of conns:                0
 
	Current no. of operations:           0
 
Server 0xf5bb00 (c3-server2)
 
	Server:                              10.10.80.169
 
	Status:                              Slave, Running
 
	Protocol:                            MySQLBackend
 
	Port:                                3306
 
	Server Version:                      5.1.62-MariaDB-mariadb115-log
 
	Node Id:                             168448169
 
	Master Id:                           168448170
 
	Slave Ids:                           
	Repl Depth:                          1
 
	Slave delay:                         0
 
	Last Repl Heartbeat:                 Fri May 12 05:07:24 2017
 
	Server Parameters:
 
	                                       myweight	100
 
	Number of connections:               213
 
	Current no. of conns:                0
 
	Current no. of operations:           0
 
Server 0xf5b560 (c3-server3)
 
	Server:                              10.10.80.171
 
	Status:                              Slave, Running
 
	Protocol:                            MySQLBackend
 
	Port:                                3306
 
	Server Version:                      5.1.62-MariaDB-mariadb115-log
 
	Node Id:                             168448171
 
	Master Id:                           168448170
 
	Slave Ids:                           
	Repl Depth:                          1
 
	Slave delay:                         0
 
	Last Repl Heartbeat:                 Fri May 12 05:07:24 2017
 
	Server Parameters:
 
	                                       myweight	100
 
	Number of connections:               213
 
	Current no. of conns:                0
 
	Current no. of operations:           0
 
Server 0xf5afc0 (c3-server4)
 
	Server:                              10.10.80.172
 
	Status:                              Slave, Running
 
	Protocol:                            MySQLBackend
 
	Port:                                3306
 
	Server Version:                      5.1.62-MariaDB-mariadb115-log
 
	Node Id:                             168448172
 
	Master Id:                           168448170
 
	Slave Ids:                           
	Repl Depth:                          1
 
	Slave delay:                         0
 
	Last Repl Heartbeat:                 Fri May 12 05:07:34 2017
 
	Server Parameters:
 
	                                       myweight	100
 
	Number of connections:               212
 
	Current no. of conns:                0
 
	Current no. of operations:           0
 
Server 0xf5aaa0 (c3-server5)
 
	Server:                              10.10.80.175
 
	Status:                              Slave, Running
 
	Protocol:                            MySQLBackend
 
	Port:                                3306
 
	Server Version:                      5.1.62-MariaDB-mariadb115-log
 
	Node Id:                             168448175
 
	Master Id:                           168448170
 
	Slave Ids:                           
	Repl Depth:                          1
 
	Slave delay:                         0
 
	Last Repl Heartbeat:                 Fri May 12 05:07:34 2017
 
	Server Parameters:
 
	                                       myweight	100
 
	Number of connections:               212
 
	Current no. of conns:                0
 
	Current no. of operations:           0

Comment by markus makela [ 2018-08-29 ]

Support for 5.1 has been removed.

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