Description:
JSON_SCHEMA_VALID() exhibits a severe performance regression in MySQL Community Server 9.7.2 compared with 8.0.45 when a small, acyclic JSON Schema contains shared local-reference subgraphs.
The validation root is simply {"type":"integer"}, and the document is the JSON scalar 1. The root does not reference the definitions. Inside definitions, each level contains an allOf with two references to the same next-level definition. The terminal definition is also {"type":"integer"}.
These schemas contain no reference cycles. L12, L16 and L20 below refer to the number of reference-chain levels, not the textual JSON nesting depth. Their maximum container/reference-edge heights are 38, 50 and 62, respectively, below the existing expansion-depth limit of 100.
Official, unmodified server measurements:
Case Schema bytes 8.0.45 ms 9.7.2 ms New/old
Shared references, L12 973 0.86645 3.94130 4.59
Shared references, L16 1277 0.84985 50.60625 59.53
Shared references, L20 1581 0.91895 865.38960 941.72
Chain+terminal control, L16 1366 0.88595 0.94980 1.07
Chain+terminal control, L20 1690 0.87980 0.97680 1.09
Each case was measured in three rounds, with three warmup executions and twenty measured executions per server per round. Server execution order alternated. Latencies above are medians of the three per-round medians; ratios are medians of the paired per-round ratios. They are end-to-end query latencies over local TCP, not isolated server CPU times. L20 was a separate follow-up run using the same measurement protocol.
For shared L20, the three 9.7.2 round medians were 865.38960, 872.89315 and 860.42960 ms. The corresponding 8.0.45 medians were 0.91895, 0.93095 and 0.90240 ms. Thus, the large difference was present in every round.
The chain control has the same reference-chain length, but its second allOf branch references a terminal integer schema instead of repeating the next-level subtree. Both forms accept the same integer document. The control is slightly larger in bytes but does not show the severe slowdown.
Source inspection of MySQL 9.7.2, commit 008e09c2834b98143a8c067d4d225c90953050cf, identifies a relevant mechanism in sql-common/json_schema.cc:
json_schema_has_unsafe_expansion() tracks nodes on the current recursion path, removes them on return, and does not memoize completed subgraphs. It traverses both container edges and local-reference edges, starting from the complete schema document. Consequently, shared acyclic subgraphs are expanded again when reached through another path. For this fixture, the branching structure creates exponentially many traversal paths despite linear growth in schema size. This is derived from the retained source and control flow; exact runtime visit counts were not instrumented.
A separate diagnostic experiment provides additional causal evidence. On a dedicated 9.7.2 instance, bypassing only the dynamic safety-preflight call changed a table-based L16 query from 51.24805 ms to 0.86355 ms; restoring the official executable restored the latency to 50.79400 ms. The literal-schema control remained slow because its separate initializer preflight was not changed. This diagnostic was restricted to independently checked acyclic inputs. It removes safety protection and is NOT a proposed production fix. All primary version-comparison measurements above used unmodified official executables.
Both official containers used the same CPU and memory limits and the following relevant server settings:
innodb_buffer_pool_size = 536870912
innodb_adaptive_hash_index = OFF
tmp_table_size = 67108864
max_heap_table_size = 67108864
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
performance_schema = ON
The reproduction is a scalar SELECT without table access, so it does not require a large dataset or JSON indexes.
The 9.7.2 image uses Oracle Linux 9.8; the 8.0.45 image uses Oracle Linux 9.7. Both run on the same Docker Desktop/WSL2 host. This distribution difference was not eliminated, although the reversible same-version diagnostic independently localizes the dominant additional cost to the safety preflight.
The affected server version actually benchmarked was 9.7.2; 8.0.45 was the baseline. No intermediate release or current development server was benchmarked. No claim is made that this report is the first public report of this issue.
How to repeat:
1. Use unmodified MySQL Community Server 9.7.2 and, for comparison, 8.0.45.
The original tests used the official mysql:9.7.2 and mysql:8.0.45 Docker images on the same host. Each container had a 4-CPU quota, cpuset 0-3, and 3 GiB RAM without swap.
No plugin, application schema, large dataset or JSON index is required.
2. Open a persistent connection to each server and use:
SET NAMES utf8mb4;
SET SESSION time_zone = '+00:00';
SET SESSION max_execution_time = 10000;
3. Execute Query A and Query B below as separate text statements. Do not use BENCHMARK(), a stored loop or a prepared statement for the primary reproduction, because that would change statement preparation and schema-initialization behavior.
Query A — shared-reference case, 1,581-byte schema:
SELECT JSON_SCHEMA_VALID('{"type":"integer","definitions":{"d0":{"allOf":[{"$ref":"#/definitions/d1"},{"$ref":"#/definitions/d1"}]},"d1":{"allOf":[{"$ref":"#/definitions/d2"},{"$ref":"#/definitions/d2"}]},"d2":{"allOf":[{"$ref":"#/definitions/d3"},{"$ref":"#/definitions/d3"}]},"d3":{"allOf":[{"$ref":"#/definitions/d4"},{"$ref":"#/definitions/d4"}]},"d4":{"allOf":[{"$ref":"#/definitions/d5"},{"$ref":"#/definitions/d5"}]},"d5":{"allOf":[{"$ref":"#/definitions/d6"},{"$ref":"#/definitions/d6"}]},"d6":{"allOf":[{"$ref":"#/definitions/d7"},{"$ref":"#/definitions/d7"}]},"d7":{"allOf":[{"$ref":"#/definitions/d8"},{"$ref":"#/definitions/d8"}]},"d8":{"allOf":[{"$ref":"#/definitions/d9"},{"$ref":"#/definitions/d9"}]},"d9":{"allOf":[{"$ref":"#/definitions/d10"},{"$ref":"#/definitions/d10"}]},"d10":{"allOf":[{"$ref":"#/definitions/d11"},{"$ref":"#/definitions/d11"}]},"d11":{"allOf":[{"$ref":"#/definitions/d12"},{"$ref":"#/definitions/d12"}]},"d12":{"allOf":[{"$ref":"#/definitions/d13"},{"$ref":"#/definitions/d13"}]},"d13":{"allOf":[{"$ref":"#/definitions/d14"},{"$ref":"#/definitions/d14"}]},"d14":{"allOf":[{"$ref":"#/definitions/d15"},{"$ref":"#/definitions/d15"}]},"d15":{"allOf":[{"$ref":"#/definitions/d16"},{"$ref":"#/definitions/d16"}]},"d16":{"allOf":[{"$ref":"#/definitions/d17"},{"$ref":"#/definitions/d17"}]},"d17":{"allOf":[{"$ref":"#/definitions/d18"},{"$ref":"#/definitions/d18"}]},"d18":{"allOf":[{"$ref":"#/definitions/d19"},{"$ref":"#/definitions/d19"}]},"d19":{"allOf":[{"$ref":"#/definitions/d20"},{"$ref":"#/definitions/d20"}]},"d20":{"type":"integer"},"terminal":{"type":"integer"}}}','1');
Query B — chain+terminal control, 1,690-byte schema:
SELECT JSON_SCHEMA_VALID('{"type":"integer","definitions":{"d0":{"allOf":[{"$ref":"#/definitions/d1"},{"$ref":"#/definitions/terminal"}]},"d1":{"allOf":[{"$ref":"#/definitions/d2"},{"$ref":"#/definitions/terminal"}]},"d2":{"allOf":[{"$ref":"#/definitions/d3"},{"$ref":"#/definitions/terminal"}]},"d3":{"allOf":[{"$ref":"#/definitions/d4"},{"$ref":"#/definitions/terminal"}]},"d4":{"allOf":[{"$ref":"#/definitions/d5"},{"$ref":"#/definitions/terminal"}]},"d5":{"allOf":[{"$ref":"#/definitions/d6"},{"$ref":"#/definitions/terminal"}]},"d6":{"allOf":[{"$ref":"#/definitions/d7"},{"$ref":"#/definitions/terminal"}]},"d7":{"allOf":[{"$ref":"#/definitions/d8"},{"$ref":"#/definitions/terminal"}]},"d8":{"allOf":[{"$ref":"#/definitions/d9"},{"$ref":"#/definitions/terminal"}]},"d9":{"allOf":[{"$ref":"#/definitions/d10"},{"$ref":"#/definitions/terminal"}]},"d10":{"allOf":[{"$ref":"#/definitions/d11"},{"$ref":"#/definitions/terminal"}]},"d11":{"allOf":[{"$ref":"#/definitions/d12"},{"$ref":"#/definitions/terminal"}]},"d12":{"allOf":[{"$ref":"#/definitions/d13"},{"$ref":"#/definitions/terminal"}]},"d13":{"allOf":[{"$ref":"#/definitions/d14"},{"$ref":"#/definitions/terminal"}]},"d14":{"allOf":[{"$ref":"#/definitions/d15"},{"$ref":"#/definitions/terminal"}]},"d15":{"allOf":[{"$ref":"#/definitions/d16"},{"$ref":"#/definitions/terminal"}]},"d16":{"allOf":[{"$ref":"#/definitions/d17"},{"$ref":"#/definitions/terminal"}]},"d17":{"allOf":[{"$ref":"#/definitions/d18"},{"$ref":"#/definitions/terminal"}]},"d18":{"allOf":[{"$ref":"#/definitions/d19"},{"$ref":"#/definitions/terminal"}]},"d19":{"allOf":[{"$ref":"#/definitions/d20"},{"$ref":"#/definitions/terminal"}]},"d20":{"type":"integer"},"terminal":{"type":"integer"}}}','1');
4. Verify that both queries return one row containing 1. Replacing the second argument with the JSON string '"wrong"' returns 0.
5. For each query and server, execute three warmups followed by twenty measured executions. Repeat for three rounds. Alternate which server executes first between successive samples, and avoid running the two servers' measured queries concurrently.
Use elapsed time covering statement execution and complete result fetching. Keep each sample separately and calculate the median for each round.
The original harness used Python 3.11.9 and PyMySQL over local TCP, with autocommit enabled and utf8mb4 connections. The exact historical PyMySQL package version was not recorded and is unavailable. The statements can also be reproduced using another text-protocol client.
6. Observed results:
Query A, shared references:
8.0.45 round medians: 0.91895, 0.93095, 0.90240 ms.
9.7.2 round medians: 865.38960, 872.89315, 860.42960 ms.
Query B, chain+terminal control:
8.0.45 round medians: 0.90300, 0.87980, 0.85535 ms.
9.7.2 round medians: 0.98340, 0.97680, 0.90790 ms.
Absolute times will depend on the machine. The important observation is that the shared-reference case becomes dramatically slower in 9.7.2, while the slightly larger chain control remains near the baseline.
7. Expected result:
Both queries should retain their current correct validation result and depth/cycle protection. Checking a small acyclic schema should avoid exponential re-expansion of already processed shared subgraphs. No specific sub-millisecond execution-time guarantee is expected.
Suggested fix:
Consider memoizing completed subgraphs during the schema-expansion safety check, while preserving cycle detection and the existing depth limit.
For example, retain active-path cycle state and cache the maximum remaining container/reference-edge height of completed nodes. Reusing a cached node must still check the current traversal depth plus the cached remaining height against the limit.
A simple global visited/safe flag is insufficient because the same subtree can be reached at different depths. Disabling or bypassing the safety check is not an acceptable fix.
This is a source-based optimization suggestion, not an implemented or production-validated patch.
Description: JSON_SCHEMA_VALID() exhibits a severe performance regression in MySQL Community Server 9.7.2 compared with 8.0.45 when a small, acyclic JSON Schema contains shared local-reference subgraphs. The validation root is simply {"type":"integer"}, and the document is the JSON scalar 1. The root does not reference the definitions. Inside definitions, each level contains an allOf with two references to the same next-level definition. The terminal definition is also {"type":"integer"}. These schemas contain no reference cycles. L12, L16 and L20 below refer to the number of reference-chain levels, not the textual JSON nesting depth. Their maximum container/reference-edge heights are 38, 50 and 62, respectively, below the existing expansion-depth limit of 100. Official, unmodified server measurements: Case Schema bytes 8.0.45 ms 9.7.2 ms New/old Shared references, L12 973 0.86645 3.94130 4.59 Shared references, L16 1277 0.84985 50.60625 59.53 Shared references, L20 1581 0.91895 865.38960 941.72 Chain+terminal control, L16 1366 0.88595 0.94980 1.07 Chain+terminal control, L20 1690 0.87980 0.97680 1.09 Each case was measured in three rounds, with three warmup executions and twenty measured executions per server per round. Server execution order alternated. Latencies above are medians of the three per-round medians; ratios are medians of the paired per-round ratios. They are end-to-end query latencies over local TCP, not isolated server CPU times. L20 was a separate follow-up run using the same measurement protocol. For shared L20, the three 9.7.2 round medians were 865.38960, 872.89315 and 860.42960 ms. The corresponding 8.0.45 medians were 0.91895, 0.93095 and 0.90240 ms. Thus, the large difference was present in every round. The chain control has the same reference-chain length, but its second allOf branch references a terminal integer schema instead of repeating the next-level subtree. Both forms accept the same integer document. The control is slightly larger in bytes but does not show the severe slowdown. Source inspection of MySQL 9.7.2, commit 008e09c2834b98143a8c067d4d225c90953050cf, identifies a relevant mechanism in sql-common/json_schema.cc: json_schema_has_unsafe_expansion() tracks nodes on the current recursion path, removes them on return, and does not memoize completed subgraphs. It traverses both container edges and local-reference edges, starting from the complete schema document. Consequently, shared acyclic subgraphs are expanded again when reached through another path. For this fixture, the branching structure creates exponentially many traversal paths despite linear growth in schema size. This is derived from the retained source and control flow; exact runtime visit counts were not instrumented. A separate diagnostic experiment provides additional causal evidence. On a dedicated 9.7.2 instance, bypassing only the dynamic safety-preflight call changed a table-based L16 query from 51.24805 ms to 0.86355 ms; restoring the official executable restored the latency to 50.79400 ms. The literal-schema control remained slow because its separate initializer preflight was not changed. This diagnostic was restricted to independently checked acyclic inputs. It removes safety protection and is NOT a proposed production fix. All primary version-comparison measurements above used unmodified official executables. Both official containers used the same CPU and memory limits and the following relevant server settings: innodb_buffer_pool_size = 536870912 innodb_adaptive_hash_index = OFF tmp_table_size = 67108864 max_heap_table_size = 67108864 character_set_server = utf8mb4 collation_server = utf8mb4_0900_ai_ci performance_schema = ON The reproduction is a scalar SELECT without table access, so it does not require a large dataset or JSON indexes. The 9.7.2 image uses Oracle Linux 9.8; the 8.0.45 image uses Oracle Linux 9.7. Both run on the same Docker Desktop/WSL2 host. This distribution difference was not eliminated, although the reversible same-version diagnostic independently localizes the dominant additional cost to the safety preflight. The affected server version actually benchmarked was 9.7.2; 8.0.45 was the baseline. No intermediate release or current development server was benchmarked. No claim is made that this report is the first public report of this issue. How to repeat: 1. Use unmodified MySQL Community Server 9.7.2 and, for comparison, 8.0.45. The original tests used the official mysql:9.7.2 and mysql:8.0.45 Docker images on the same host. Each container had a 4-CPU quota, cpuset 0-3, and 3 GiB RAM without swap. No plugin, application schema, large dataset or JSON index is required. 2. Open a persistent connection to each server and use: SET NAMES utf8mb4; SET SESSION time_zone = '+00:00'; SET SESSION max_execution_time = 10000; 3. Execute Query A and Query B below as separate text statements. Do not use BENCHMARK(), a stored loop or a prepared statement for the primary reproduction, because that would change statement preparation and schema-initialization behavior. Query A — shared-reference case, 1,581-byte schema: SELECT JSON_SCHEMA_VALID('{"type":"integer","definitions":{"d0":{"allOf":[{"$ref":"#/definitions/d1"},{"$ref":"#/definitions/d1"}]},"d1":{"allOf":[{"$ref":"#/definitions/d2"},{"$ref":"#/definitions/d2"}]},"d2":{"allOf":[{"$ref":"#/definitions/d3"},{"$ref":"#/definitions/d3"}]},"d3":{"allOf":[{"$ref":"#/definitions/d4"},{"$ref":"#/definitions/d4"}]},"d4":{"allOf":[{"$ref":"#/definitions/d5"},{"$ref":"#/definitions/d5"}]},"d5":{"allOf":[{"$ref":"#/definitions/d6"},{"$ref":"#/definitions/d6"}]},"d6":{"allOf":[{"$ref":"#/definitions/d7"},{"$ref":"#/definitions/d7"}]},"d7":{"allOf":[{"$ref":"#/definitions/d8"},{"$ref":"#/definitions/d8"}]},"d8":{"allOf":[{"$ref":"#/definitions/d9"},{"$ref":"#/definitions/d9"}]},"d9":{"allOf":[{"$ref":"#/definitions/d10"},{"$ref":"#/definitions/d10"}]},"d10":{"allOf":[{"$ref":"#/definitions/d11"},{"$ref":"#/definitions/d11"}]},"d11":{"allOf":[{"$ref":"#/definitions/d12"},{"$ref":"#/definitions/d12"}]},"d12":{"allOf":[{"$ref":"#/definitions/d13"},{"$ref":"#/definitions/d13"}]},"d13":{"allOf":[{"$ref":"#/definitions/d14"},{"$ref":"#/definitions/d14"}]},"d14":{"allOf":[{"$ref":"#/definitions/d15"},{"$ref":"#/definitions/d15"}]},"d15":{"allOf":[{"$ref":"#/definitions/d16"},{"$ref":"#/definitions/d16"}]},"d16":{"allOf":[{"$ref":"#/definitions/d17"},{"$ref":"#/definitions/d17"}]},"d17":{"allOf":[{"$ref":"#/definitions/d18"},{"$ref":"#/definitions/d18"}]},"d18":{"allOf":[{"$ref":"#/definitions/d19"},{"$ref":"#/definitions/d19"}]},"d19":{"allOf":[{"$ref":"#/definitions/d20"},{"$ref":"#/definitions/d20"}]},"d20":{"type":"integer"},"terminal":{"type":"integer"}}}','1'); Query B — chain+terminal control, 1,690-byte schema: SELECT JSON_SCHEMA_VALID('{"type":"integer","definitions":{"d0":{"allOf":[{"$ref":"#/definitions/d1"},{"$ref":"#/definitions/terminal"}]},"d1":{"allOf":[{"$ref":"#/definitions/d2"},{"$ref":"#/definitions/terminal"}]},"d2":{"allOf":[{"$ref":"#/definitions/d3"},{"$ref":"#/definitions/terminal"}]},"d3":{"allOf":[{"$ref":"#/definitions/d4"},{"$ref":"#/definitions/terminal"}]},"d4":{"allOf":[{"$ref":"#/definitions/d5"},{"$ref":"#/definitions/terminal"}]},"d5":{"allOf":[{"$ref":"#/definitions/d6"},{"$ref":"#/definitions/terminal"}]},"d6":{"allOf":[{"$ref":"#/definitions/d7"},{"$ref":"#/definitions/terminal"}]},"d7":{"allOf":[{"$ref":"#/definitions/d8"},{"$ref":"#/definitions/terminal"}]},"d8":{"allOf":[{"$ref":"#/definitions/d9"},{"$ref":"#/definitions/terminal"}]},"d9":{"allOf":[{"$ref":"#/definitions/d10"},{"$ref":"#/definitions/terminal"}]},"d10":{"allOf":[{"$ref":"#/definitions/d11"},{"$ref":"#/definitions/terminal"}]},"d11":{"allOf":[{"$ref":"#/definitions/d12"},{"$ref":"#/definitions/terminal"}]},"d12":{"allOf":[{"$ref":"#/definitions/d13"},{"$ref":"#/definitions/terminal"}]},"d13":{"allOf":[{"$ref":"#/definitions/d14"},{"$ref":"#/definitions/terminal"}]},"d14":{"allOf":[{"$ref":"#/definitions/d15"},{"$ref":"#/definitions/terminal"}]},"d15":{"allOf":[{"$ref":"#/definitions/d16"},{"$ref":"#/definitions/terminal"}]},"d16":{"allOf":[{"$ref":"#/definitions/d17"},{"$ref":"#/definitions/terminal"}]},"d17":{"allOf":[{"$ref":"#/definitions/d18"},{"$ref":"#/definitions/terminal"}]},"d18":{"allOf":[{"$ref":"#/definitions/d19"},{"$ref":"#/definitions/terminal"}]},"d19":{"allOf":[{"$ref":"#/definitions/d20"},{"$ref":"#/definitions/terminal"}]},"d20":{"type":"integer"},"terminal":{"type":"integer"}}}','1'); 4. Verify that both queries return one row containing 1. Replacing the second argument with the JSON string '"wrong"' returns 0. 5. For each query and server, execute three warmups followed by twenty measured executions. Repeat for three rounds. Alternate which server executes first between successive samples, and avoid running the two servers' measured queries concurrently. Use elapsed time covering statement execution and complete result fetching. Keep each sample separately and calculate the median for each round. The original harness used Python 3.11.9 and PyMySQL over local TCP, with autocommit enabled and utf8mb4 connections. The exact historical PyMySQL package version was not recorded and is unavailable. The statements can also be reproduced using another text-protocol client. 6. Observed results: Query A, shared references: 8.0.45 round medians: 0.91895, 0.93095, 0.90240 ms. 9.7.2 round medians: 865.38960, 872.89315, 860.42960 ms. Query B, chain+terminal control: 8.0.45 round medians: 0.90300, 0.87980, 0.85535 ms. 9.7.2 round medians: 0.98340, 0.97680, 0.90790 ms. Absolute times will depend on the machine. The important observation is that the shared-reference case becomes dramatically slower in 9.7.2, while the slightly larger chain control remains near the baseline. 7. Expected result: Both queries should retain their current correct validation result and depth/cycle protection. Checking a small acyclic schema should avoid exponential re-expansion of already processed shared subgraphs. No specific sub-millisecond execution-time guarantee is expected. Suggested fix: Consider memoizing completed subgraphs during the schema-expansion safety check, while preserving cycle detection and the existing depth limit. For example, retain active-path cycle state and cache the maximum remaining container/reference-edge height of completed nodes. Reusing a cached node must still check the current traversal depth plus the cached remaining height against the limit. A simple global visited/safe flag is insufficient because the same subtree can be reached at different depths. Disabling or bypassing the safety check is not an acceptable fix. This is a source-based optimization suggestion, not an implemented or production-validated patch.