Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-684

Performance of con.getMetaData().getProcedureColumns(null, null, name, "%") is much lower than mysql jdbc

Details

    Description

      Recently, i use mariadb jdbc replace of mysql jdbc in our project. After replcing i found there is heavyer time costs of page reload in one of my forward app. To figure out why i has a below comparsion, i found it's the deal of the methond connection.getMetaData().getProcedureColumns().
      in my case mariadb jdbc cost more 8 -10 times than MySQL jdbc.
      Then i trace the source of mariadb jdbc i found that Mariadb JDBC actually queries the system table INFORMATION_SCHEMA.PARAMETERS to obtain a ResultSet describing stored procedures, which means that the performance of this method will gradually decline as the data volume of the system table increases.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-20609 Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            diego dupin Diego Dupin added a comment -

            Right, current implementation is fast only when not having lots of procedures/functions, and performance degrade fast when there is !

            Implementation must rely on mysql.proc :
            I_S.PARAMETERS view is a memory table construct from mysql.proc.
            Difference is mysql.proc has index, permitting fastest access.

            Downside is that I_S tables are always accessible without any rights, mysql.proc needs some rights. MySQL default implementation rely on accessing mysql.proc (or if not set option "noAccessToProcedureBodies")

            diego dupin Diego Dupin added a comment - Right, current implementation is fast only when not having lots of procedures/functions, and performance degrade fast when there is ! Implementation must rely on mysql.proc : I_S.PARAMETERS view is a memory table construct from mysql.proc. Difference is mysql.proc has index, permitting fastest access. Downside is that I_S tables are always accessible without any rights, mysql.proc needs some rights. MySQL default implementation rely on accessing mysql.proc (or if not set option "noAccessToProcedureBodies")
            diego dupin Diego Dupin added a comment -

            This will be corrected by MDEV-20609, this being a better solution than relying on mysql schema that would need rights.

            diego dupin Diego Dupin added a comment - This will be corrected by MDEV-20609 , this being a better solution than relying on mysql schema that would need rights.
            diego dupin Diego Dupin added a comment -

            closing since a server issue, that is now corrected with MDEV-20609

            diego dupin Diego Dupin added a comment - closing since a server issue, that is now corrected with MDEV-20609

            People

              diego dupin Diego Dupin
              NeveryLee Xiao Peipei
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.