Issue(s)

In version 8.0 of MySQL and version 10.2 of MariaDB, Common Table Expressions (CTE) where introduced which allows user to create named temporary results sets that only exist in the scope of a single statement. This advanced feature improves 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 within the CoreOne Suite. Starting with CoreOne Suite 8.0 we used this feature for various purposes. One of such purpose was to determinate the access rights of a user based on his organizational assignments. Security roles such as the https://itsense.atlassian.net/wiki/x/AYDNk, the https://itsense.atlassian.net/wiki/x/AYDNk as well as many features of the https://itsense.atlassian.net/wiki/x/CABGFw were built on top of that feature by creating views that leverage CTEs.

The data returned by those views depends on the data stored in the system. As the views are recursive there are multiple factors that affect the performance. Such factors are the amount of organizational unit, the structure of the hierarchy (flat list or complex tree) and others.

In production, a significant drop in the overall system performance was experienced at times. This affect could not be reproduced consistently but occurred regularly.

Cause(s)

After further investigation it was identified that the query execution of those views changed on MySQL 8.x instances at random. With 1’000 organizational record, the primary select of the view returned 1’000 rows, which is correct. But as soon as the decreased performance was identified, the primary select of the view returned several ten thousands of rows, thus leading to a hundredfold increase in execution time. The view that so far executed withing 100 ms, now has an execution time of 10 seconds.

As it turns out, MySQL's implementation of CTE seem to be subject to constant improvement and performance fixing as indicated in various release notes:

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-25.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-27.html

Solution

Migrate your database to MariaDB 10.6.x or higher. MariaDB’s implementation of CTE’s seems to be measurably faster and more importantly, consistent.