Report builder performance and read-only connection troubleshooting

Owned by Liam Tanner
Feb 06, 2024
2 min read

Managing reports on large sites may result in performance issues. Site Administrators may need to use advanced techniques and different configurations to deliver better performance.

This article covers the following:

  • General report performance

  • Setting up a read-only connection to work

Report performance

In order to find the best solution for performance issues you need to first identify and understand the problem. In the first instance, see Getting support with report builder performance issues. This article covers a wide range of scenarios, helps identify common problems, and provides general guidance on how to manage report builder reports.

Read-only database connection

It is now possible to configure a second database connection for use by report builder. The purpose of this secondary connection is so that you can direct the main report builder queries at a read-only database clone, as this can be used to improve performance.

These settings should be considered highly advanced. It has to be configured by the sysadmin in config.php and then has to be manually enabled in individual reports by a Totara Site Administrator via the report builder (using the database clone setting).

It is suggested that sysadmins create a separate database instance with an exact clone of the production database that is kept in sync automatically.

Support cannot be provided on configuring a read-only clone, so you will need in-house expertise to achieve this.

The only technical requirement for the databases is that the main database must be ACID compliant.

Report builder will use the read-only connection for the main report query if:

  • The $CFG settings for the clone/read-only database are configured and working (see config-dist.php for configuration information*)

  • The Use cloned database setting is enabled in the specific report

Only the main report query uses the slave, any database queries in the display functions or other queries that are used on the page continue to use the master.

*Those wishing to use the second database connection can find instructions for it within config-dist.php. You can use a read-only database clone. These settings allow the admin to configure a second database:

// connection that will be used for selected Report Builder reports to improve performance // and lower the main database load. // // $CFG->clone_dbname = 'xxx'; // $CFG->clone_dbhost = $CFG->dbhost; // optional // $CFG->clone_dbuser = $CFG->dbuser; // optional // $CFG->clone_dbpass = $CFG->dbpass; // optional // $CFG->clone_dboptions = $CFG->dboptions; //optional

The master database is locked when querying the read-only database

We don't lock the master when querying the read-only database. If the master is being locked when the read-only database is running a query, it indicates it isn't configured correctly. This type of issue would need to be debugged by the sysadmin as support cannot usually be provided by the Totara Helpdesk.