Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-4977

Schemarouter opens a connection to all shards

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 23.08.4
    • None
    • maxctrl
    • 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

          People

            markus makela markus makela
            oli Oli Sennhauser
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.