Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25333

Debug server is extremely slow in some JSON functions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.5
    • Debug, JSON

    Description

      JSON_MERGE_... and probably some other JSON functions take a lot of time on a debug server, while on a release server of the same version they run almost instantly. In the example below, the difference is 30 sec vs 0.5 sec.

      I fully realize that debug server is bound to be slower, but I think it's worth checking, when/if priorities permit, whether it should be that much slower.

      MySQL 8.0 doesn't have the same effect, there the difference is reasonable, something like 2 sec vs 0.5 sec.

      To make things worse, as reported in MDEV-24909, JSON functions don't respect KILL QUERY or max_statement_time, so a query (which can be much longer than in the example here, depending on the data and the query itself) will run till the end and prevent the server shutdown.

      The attached file helps to reproduce the problem. Download, unpack and modify LOAD_FILE below to point at its location. The file is 20M unpacked, a real-life JSON document from an official public resource.

      Important: To run the example, the server needs to be run with

      --max-allowed-packet=128M --secure-file-priv=''

      , and the command-line client should be started with

      --max-allowed-packet=128M

      . It can also be run via MTR, in which case it's

      --mysqld=--max-allowed-packet=128M --mysqld=--secure-file-priv=''

      Queries

      set @a= convert(load_file('/dev/shm/01.json') using utf8mb4);
      select length(json_merge_preserve(@a,@a));
      

      10.5 7f75acc0 non-debug build

      MariaDB [test]> select length(json_merge_preserve(@a,@a));
      +------------------------------------+
      | length(json_merge_preserve(@a,@a)) |
      +------------------------------------+
      |                           43788415 |
      +------------------------------------+
      1 row in set (0.481 sec)
      

      10.5 7f75acc0 debug build

      MariaDB [(none)]> select length(json_merge_preserve(@a,@a));
      +------------------------------------+
      | length(json_merge_preserve(@a,@a)) |
      +------------------------------------+
      |                           43788415 |
      +------------------------------------+
      1 row in set (29.951 sec)
      

      MySQL 8.0.23 debug

      MySQL [(none)]> select length(json_merge_preserve(@a,@a));
      +------------------------------------+
      | length(json_merge_preserve(@a,@a)) |
      +------------------------------------+
      |                           43788415 |
      +------------------------------------+
      1 row in set (1.721 sec)
      

      CONVERT is not important for the scenario, it's there just to make the statement universal, usable for MySQL too.
      LENGTH is also unimportant, it's there to produce a reasonable output and to ensure that the result is the same for all runs.
      Previous assignment to a variable is also unimportant, it's there to rule out the influence of LOAD_FILE function on the duration of the query.

      Attachments

        1. 01.json.gz
          7.21 MB
          Elena Stepanova

        Issue Links

          Activity

            People

              rucha174 Rucha Deodhar
              elenst Elena Stepanova
              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.