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

Schemarouter opens a connection to all shards

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

          markus makela markus makela added a comment -

          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.

          markus makela markus makela added a comment - 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.
          markus makela markus makela added a comment -

          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.

          markus makela markus makela added a comment - 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.
          markus makela markus makela added a comment -

          I'll change this to a New Feature since it's been the expected behavior for quite some time.

          markus makela markus makela added a comment - I'll change this to a New Feature since it's been the expected behavior for quite some time.

          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.