[MXS-1895] Readconnroute must show the number of routed queries per service per backend Created: 2018-06-02  Updated: 2024-01-04  Resolved: 2019-12-20

Status: Closed
Project: MariaDB MaxScale
Component/s: readconnroute
Affects Version/s: None
Fix Version/s: 2.5.0

Type: New Feature Priority: Major
Reporter: Wagner Bianchi (Inactive) Assignee: Niclas Antti
Resolution: Fixed Votes: 1
Labels: None

Epic Link: Router Improvements
Sprint: MXS-SPRINT-96

 Description   

Folks,

Working with a reading pool of slaves, having three async slaves replicating from an individual MariaDB Cluster node, I am missing to have out the number of routed queries by backend out of the command show services, as you can see below:

[root@mxs01 ~]# maxadmin show service Read-Pool-Service
 
    Service:                             Read-Pool-Service
 
    Router:                              readconnroute
 
    State:                               Started
 
    Number of router sessions:       260
 
    Current no. of router sessions:    261
 
    Number of queries forwarded:       24368
 
    Started:                             Fri Jun  1 20:38:51 2018
 
    Root user access:                    Disabled
 
    Backend databases:
 
        [165.227.225.138]:3306 Protocol: MariaDBBackend    Name: async01
 
        [138.68.158.166]:3306  Protocol: MariaDBBackend    Name: async02
 
        [165.227.225.121]:3306 Protocol: MariaDBBackend    Name: async03
 
    Total connections:                   261
 
    Currently connected:                 261

I would like to see something like (the number of routed queries per service per backend):

Transactions routed:
 
 - Name: async01: 127
 
 - Name: async02: 243
 
 - Name: async03: 342

This is going to be of a great benefit for the sake of having good statistics for capacity planning and to understand better the routing decisions made by the readconnroute router for load balancing incoming traffic and when round-robing it.

Thanks folks!

 Comments   
Comment by Wagner Bianchi (Inactive) [ 2019-12-12 ]

Hello Folks,

I see this feature request now as Router Improvements and I'm happy with that. However, when I opened this JIRA, I was working yet with the old MaxAdmin we can't consider anymore as it is about to be deprecated and removed from the MaxScale world, as per the docs. With the in mind, I would like to propose the same to be implemented for the below MaxCtrl output instead:
maxctrl: show service readconn-service-master

┌─────────────────────┬─────────────────────────────────────────────────────────────────────────────────────┐
 
│ Service             │ readconn-service-master                                                             │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Router              │ readconnroute                                                                       │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ State               │ Started                                                                             │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Started At          │ Mon Oct 14 20:24:15 2019                                                            │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Current Connections │ 16                                                                                  │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Total Connections   │ 193735482                                                                           │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Servers             │ db2                                                                                 │
 
│                     │ db1                                                                                 │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Parameters          │ {                                                                                   │
 
│                     │     "router_options": "master",                                                     │
 
│                     │     "user": "maxusr",                                                               │
 
│                     │     "password": "FF2856D24FBAC82A038DC04210E5C7884C60A27218DBC17D85C08E9391E85E13", │
 
│                     │     "passwd": null,                                                                 │
 
│                     │     "enable_root_user": false,                                                      │
 
│                     │     "max_retry_interval": 3600,                                                     │
 
│                     │     "max_connections": 0,                                                           │
 
│                     │     "connection_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": 0                                                              │
 
│                     │ }                                                                                   │
 
├─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
 
│ Router Diagnostics  │ {                                                                                   │
 
│                     │     "connections": 193733865,                                                       │
 
│                     │     "current_connections": 16,                                                      │
 
│                     │     "queries": 1585574679                                                           │
 
│                     │ }                                                                                   │
 
└─────────────────────┴─────────────────────────────────────────────────────────────────────────────────────┘

Having individual numbers of routed transactions/queries per server on the Router Options will make it way more complete and able to give better information to who is analyzing traffic patterns.

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