Versions Compared

Version Old Version 1 New Version 2
Changes made by

Silvan Grüter

Silvan Grüter

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

In version 8.0 of MySQL and version 10.2 of MariaDB, Common Table Expressions (CTECTEs) where introduced which allows user were introduced, allowing users to create named temporary results result sets that only exist in within the scope of a single statement. This advanced feature improves enhances query readability of queries and allows users to use it in a recursive manor. This features proved to be ideal to build a recursive closure tree, representing the organizational hierarchy that is often used and enables the use of CTEs in a recursive manner, which has proven ideal for building recursive closure trees—a common need for representing organizational hierarchies within theCoreOne Suite. Starting with CoreOne Suite 8.0, we used this feature leveraged CTEs for various purposes. One of such purpose was to determinate the access rights of a user based on his , including determining a user's access rights based on their organizational assignments. Security roles, such as the https://itsense.atlassian.net/wiki/x/AYDNk, the https://itsense.atlassian.net/wiki/x/AYDNkas well as many features of the https://itsense.atlassian.net/wiki/x/CABGFw , were built on top of that feature by creating using views that leverage utilize CTEs.

The data returned by those these views depends on the data stored in the system. As Since the views are recursive there are multiple , several factors that affect the performance. Such factors are the amount performance, such as the number of organizational unitunits, the structure of the hierarchy (whether it is a flat list or a complex tree) and , among others.

In the production environment, a significant drop in the overall system performance was experienced at timesobserved intermittently. This affect performance degradation could not be consistently reproduced consistently but occurred regularlyfrequently enough to impact operations.

Cause(s)

After further investigation it was identified Further investigation revealed that the query execution of those plan for these views changed randomly on MySQL 8.x instances at random. With 1’000 organizational record. For example, with 1,000 organizational records, the primary select of SELECT statement in the view initially returned 1’000 1,000 rows, which is was correct. But as soon as the decreased performance was identified, the primary select of the view returned several ten However, during periods of degraded performance, the same query returned tens of thousands of rows, thus leading to a hundredfold significant increase in execution time. The view that so far executed withing 100 ms, now has an execution time of time—from approximately 100 ms to 10 seconds.

As it turns out, MySQL's implementation of CTE seem to be CTEs is subject to constant improvement ongoing improvements and performance fixing fixes, as indicated highlighted in various multiple release notes:

These release notes indicate continuous improvements and adjustments, which may have caused variations in query performance.

Solution

Migrate your the database to MariaDB 10.6.x or higher. MariaDB’s MariaDB's implementation of CTE’s seems CTEs appears to be both measurably faster and, more importantly, consistent in its performance. This stability makes MariaDB a more suitable choice for applications requiring predictable query execution times, especially for recursive queries such as organizational hierarchy lookups.