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

Nontrivial number of privilege entries causes long running queries / overload

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 2.3.16
    • Core
    • None

    Description

      The recursive privilege selection query issued by MaxScale does not scale well when having a non-trivial (but still realistic) number of users and roles.

      WITH RECURSIVE t AS
      (
        SELECT u1.user, u1.host, d1.db, u1.select_priv, IF(u1.password <> '', u1.password, u1.authentication_string) AS password, u1.is_role, u1.default_role
          FROM mysql.user AS u1
          LEFT JOIN mysql.db AS d1
            ON (u1.user = d1.user AND u1.host = d1.host)
         WHERE u1.plugin IN ('', 'mysql_native_password')
      UNION
        SELECT u.user, u.host, t.db, u.select_priv, IF(u.password <> '', u.password, u.authentication_string), u.is_role, u.default_role
          FROM mysql.user AS u
          LEFT JOIN mysql.tables_priv AS t
            ON (u.user = t.user AND u.host = t.host)
         WHERE u.plugin IN ('', 'mysql_native_password')
      )
      , users AS
      (
        SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
          FROM t
         WHERE t.is_role <> 'Y'
      UNION
        SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
          FROM t
          JOIN users AS u ON (t.user = u.role)
          LEFT JOIN mysql.roles_mapping AS r ON (t.user = r.user)
      )
      SELECT DISTINCT t.user, t.host, t.db, t.select_priv, t.password
        FROM users AS t
       WHERE t.user <> 'root'
      ;
      

      E.g. wiht a privileges database with the following row counts:

        mysql.user          - 410
        mysql.roles_mapping - 177
        mysql.db            - 442
        mysql.tables_priv   -  24
      

      the query returns 1488 rows, and runs for about two seconds on a MariaDB 10.3 instance on an otherwise idle host, being fully CPU bound.

      +------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
      | id   | select_type     | table      | type  | possible_keys | key     | key_len | ref          | rows | Extra                                                        |
      +------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
      |    1 | PRIMARY         | <derived4> | ALL   | NULL          | NULL    | NULL    | NULL         | 2050 | Using where; Using temporary                                 |
      |    4 | DERIVED         | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL         | 2050 | Using where                                                  |
      |    2 | DERIVED         | u          | ALL   | NULL          | NULL    | NULL    | NULL         |  410 | Using where                                                  |
      |    2 | DERIVED         | d          | ref   | PRIMARY,User  | PRIMARY | 180     | mysql.u.Host |    4 | Using where; Using index                                     |
      |    3 | UNION           | u          | ALL   | NULL          | NULL    | NULL    | NULL         |  410 | Using where                                                  |
      |    3 | UNION           | t          | ref   | PRIMARY       | PRIMARY | 180     | mysql.u.Host |    1 | Using where; Using index                                     |
      | NULL | UNION RESULT    | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL         | NULL |                                                              |
      |    5 | RECURSIVE UNION | <derived6> | ALL   | NULL          | NULL    | NULL    | NULL         | 2050 |                                                              |
      |    5 | RECURSIVE UNION | <derived4> | ref   | key0          | key0    | 241     | t.user       |   10 |                                                              |
      |    5 | RECURSIVE UNION | r          | index | NULL          | Host    | 660     | NULL         |  177 | Using where; Using index; Using join buffer (flat, BNL join) |
      |    6 | DERIVED         | u          | ALL   | NULL          | NULL    | NULL    | NULL         |  410 | Using where                                                  |
      |    6 | DERIVED         | d          | ref   | PRIMARY,User  | PRIMARY | 180     | mysql.u.Host |    4 | Using where; Using index                                     |
      |    7 | UNION           | u          | ALL   | NULL          | NULL    | NULL    | NULL         |  410 | Using where                                                  |
      |    7 | UNION           | t          | ref   | PRIMARY       | PRIMARY | 180     | mysql.u.Host |    1 | Using where; Using index                                     |
      | NULL | UNION RESULT    | <union6,7> | ALL   | NULL          | NULL    | NULL    | NULL         | NULL |                                                              |
      | NULL | UNION RESULT    | <union4,5> | ALL   | NULL          | NULL    | NULL    | NULL         | NULL |                                                              |
      +------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
      

      As the query is running as part of the user data reload, it can be triggered quite a lot if unknown / invalid users try to connect often.

      In the reported case leading to this bug report there were quite a lot of this query seen running in parallel, some already having been active for 30 seconds as all of them were competing for CPU time, so causing a massive overload situation ...

      Attachments

        1. MXS-2759-data.sql
          149 kB
          Hartmut Holzgraefe
        2. MXS-2759-query.sql
          1 kB
          Hartmut Holzgraefe

        Issue Links

          Activity

            People

              markus makela markus makela
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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