Hi Harry

The benefits of report caching are actually quite limited and this feature only works with simpler reports. When a report uses columns, aggregations, restrictions or other features that cannot be resolved entirely on the database or that require excessive data to resolve report caching will not take effect for the report. This is typically caught during configuration and the report caching option will be disabled in the interface, preventing it from being selected. However on occasion it cannot be known in advance, such as when global report restrictions may be in effect.

When it is operational a cron routine will create a temp cache table in the database for the report, populate it, and then periodically update it. If cron has not yet run for the report the cache table will not exist and report caching will not be usable for the report.

The benefit it brings is a variable and it's worth noting that from about Totara 9 onwards rather than rely upon report caching we have preferred to optimise our report sources enabling them to perform without the use of the report caching system.

The reality is that in many cases we can get a report source query performing just as fast as the report cache would enable it to perform, removing the benefit. This enables us to build faster, more complex report sources.

For report sources that stretch across many areas of the system report caching may still be of benefit.

The following additional information may also be useful to you:

MySQL query caching

MySQL deprecated the query cache in MySQL 5.7.20, and removed it entirely in MySQL 8.0.
This has had a mixed reception from the MySQL community. On one hand MySQL 8 includes significant performance improvements. On the other hand complex queries returning data that does not often change benefited hugely from query caching.
Ultimately the actions that MySQL took are outside of our control. Having focused on the performance of our report sources without relying upon Totara report builder report caching however has put us in a position in many cases whereby the impact of losing MySQL query caching is minimised.

For those who do find that MySQL query caching was really brining them benefit there are alternative solutions for MySQL 8 out there, created and supported by the wider MySQL community.

Report builder performance

Report Builder is one of the most powerful features that Totara has to offer, and one of the costs of this power is performance.
The cost of generating reports comes down to how the report has been constructed.
There are many factors that affect Report Builder performance, the following is a list of just some of the more significant aspects that influence performance:

• The columns that have been added.
• The aggregation that is being used.
• The filters that have been applied.
• The content restrictions that are in effect.
• The global restrictions that are in effect.
• The number of records in the database.
• The environment the site is being run on.

Combined, these can make it incredibly hard to pinpoint performance issues within Reports.

Secondary reporting connection

One of the more advanced performance features we offer for report builder is the ability to configure a second database connection, for use by report builder only. It enables a site to point report builder at a read only database, typically a read optimised. This enables the read only database to be optimised for reporting purposes and benefits all users of the site by offloading reporting queries (typically heavy) from the primary database.
More information can be found in the config-dist.php file accompanying the code.

Performance testing

Each individual change gets performance tested if the development team has concerns or doubts about the ability of the change to perform. This happens at the time of development.

In the lead up to a major release performance testing occurs in all supported databases. However because of the size of the product we focus on areas that have changed, and target areas such as individual web api methods, and interfaces.
This testing is not focused on producing comparison results, but on confirming that changes will perform on all supported databases.

We are in the process of setting up scenario based performance testing to accompany our everyday development processes (currently required manually of the developers, but handled inconsistently) This is two fold, it will help us detect performance regressions in advance of the release, and it will give us a set of results that we can publish to help answer questions like that which you are asking on how Totara performs on different databases.

We also conduct issue targeted performance testing when performance problems are reported. If you do have concerns about perfomance of a particular source please let us know.