Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL)
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
Issue Links
- relates to
-
MDEV-24909 JSON functions don't respect KILL QUERY / max_statement_time limit
- Closed