Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
23.08.4
-
None
-
Linux n.a.
Description
The cli maxctrl command list servers does not show the Connections correctly AND State Master is very strange in SchemaRouter sharding system where IMHO all shards should be treated equally. And maxscale keeps too many connections open.
In the following example we have application 6 Connections open:
2 to shard 1
3 to shard 2
1 to shard 3
root@ubu2204-max:/tmp# maxctrl list services
|
┌─────────────────┬──────────────┬─────────────┬───────────────────┬────────────────────────┐
|
│ Service │ Router │ Connections │ Total Connections │ Targets │
|
├─────────────────┼──────────────┼─────────────┼───────────────────┼────────────────────────┤
|
│ Sharded-Service │ schemarouter │ 6 │ 6 │ shard1, shard2, shard3 │
|
└─────────────────┴──────────────┴─────────────┴───────────────────┴────────────────────────┘
|
root@ubu2204-max:/tmp# maxctrl list servers
|
┌────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────┬─────────────────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
|
├────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────┼─────────────────┤
|
│ shard1 │ 10.139.158.1 │ 3363 │ 6 │ Master, Running │ 0-3363-42 │ MariaDB-Monitor │
|
├────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────┼─────────────────┤
|
│ shard2 │ 10.139.158.1 │ 3364 │ 6 │ Running │ 0-3364-47 │ MariaDB-Monitor │
|
├────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────┼─────────────────┤
|
│ shard3 │ 10.139.158.1 │ 3365 │ 6 │ Running │ 0-3365-37 │ MariaDB-Monitor │
|
└────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────┴─────────────────┘
|
--> Master looks strange
And we have in total 18 connections open (SHOW PROCESSLIST):
show processlist;
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| 33 | mysql | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
|
| 35 | maxscale | 10.139.158.211:41996 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 37 | app | 10.139.158.211:49234 | customer_0010 | Sleep | 316 | | NULL | 0.000 |
|
| 38 | app | 10.139.158.211:38886 | customer_0011 | Sleep | 296 | | NULL | 0.000 |
|
| 39 | app | 10.139.158.211:58320 | NULL | Sleep | 287 | | NULL | 0.000 |
|
| 40 | app | 10.139.158.211:40536 | NULL | Sleep | 282 | | NULL | 0.000 |
|
| 41 | app | 10.139.158.211:40538 | NULL | Sleep | 278 | | NULL | 0.000 |
|
| 43 | app | 10.139.158.211:50232 | NULL | Sleep | 269 | | NULL | 0.000 |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
|
|
show processlist;
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| 32 | mysql | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
|
| 34 | maxscale | 10.139.158.211:51294 | NULL | Sleep | 1 | | NULL | 0.000 |
|
| 36 | app | 10.139.158.211:60042 | NULL | Sleep | 321 | | NULL | 0.000 |
|
| 37 | app | 10.139.158.211:37718 | NULL | Sleep | 301 | | NULL | 0.000 |
|
| 38 | app | 10.139.158.211:51646 | customer_0020 | Sleep | 292 | | NULL | 0.000 |
|
| 39 | app | 10.139.158.211:55992 | customer_0021 | Sleep | 287 | | NULL | 0.000 |
|
| 40 | app | 10.139.158.211:56006 | customer_0022 | Sleep | 282 | | NULL | 0.000 |
|
| 42 | app | 10.139.158.211:34016 | NULL | Sleep | 273 | | NULL | 0.000 |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
show processlist;
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
| 32 | mysql | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
|
| 34 | maxscale | 10.139.158.211:56696 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 36 | app | 10.139.158.211:60772 | NULL | Sleep | 325 | | NULL | 0.000 |
|
| 37 | app | 10.139.158.211:39592 | NULL | Sleep | 305 | | NULL | 0.000 |
|
| 38 | app | 10.139.158.211:59874 | NULL | Sleep | 296 | | NULL | 0.000 |
|
| 39 | app | 10.139.158.211:39492 | NULL | Sleep | 291 | | NULL | 0.000 |
|
| 40 | app | 10.139.158.211:39500 | NULL | Sleep | 287 | | NULL | 0.000 |
|
| 42 | app | 10.139.158.211:45858 | customer_0030 | Sleep | 278 | | NULL | 0.000 |
|
+----+----------+----------------------+---------------+---------+------+----------+------------------+----------+
|
Much too many connections open. This will not scale well on a system with many tenants!!!
What IMHO is wrong:
1.) State: Master
2.) list servers: Connections should be 2,3,1 and not 3 x 6
3.) Too many connections open (see show processlist)
Attachments
Activity
The Master state is expected if you're using mariadbmon to monitor it. It is designed to work with a single async replication cluster and it selects one node as the cluster's Master. For the schemarouter, there's no real effect for this since it only looks at the Running state.
I think that this might be somewhat expected behavior as the schemarouter will open connections to all servers that it can. This has to be done whenever the shard location cache is cold as the location of the tables and databases needs to be figured out. However, once the result is in the cache, the creation of the connections could be deferred until it is known that they're needed, similar to how lazy_connect in readwritesplit works.
I'll change this to a New Feature since it's been the expected behavior for quite some time.