Getting support with report builder performance issues

This guide was produced to aid our Partners and our support teams when working on report builder performance issues. It has been broken into several sections to make it easier to navigate when it is being referenced on support tickets. If this is your first time here we recommend you read the entire guide.

Understanding report builder performance issues

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, so the following is a selection 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. It is for this reason that we have created this guide, for both our Partners and our support team, to ensure that everyone is armed with the information they need to both communicate what is going on back to the customer and to identify and remedy the performance issue.

Before we begin, please understand that the answer is not always going to be a change in the product. In some situations the time it takes to generate a report is simply what is required to gather and format the information that is required. In these situations we will endeavour to identify that situation and provide you with information of where the time is spent so that you can work with the customer to alter the report or find an alternative solution to meet their needs.

Diagnosing the report

The very first step, before even contacting our support team, is to identify the report performing poorly and to begin a diagnostic process that will help with pinpointing and identifying the actual problem.

To begin with, a single poorly performing report needs to be identified. If multiple reports are affected each needs to be investigated and reported separately. While they may be related, it is just as likely they are not.
To ensure efficent support we ask that each request for support focuses on a single report.

Once a report has been identified you can start diagnosing it by following the steps below, each of which will gather information for you to provide in your support request. While you don't have to do all of them, the more you do the easier it will be for us to help you.

Writing reproduction steps

This can be done by the system administrator based upon the reports you have received from your client.

This is also very important, as it helps both yourselves and our support staff understand what is happening leading up to the poorly performing report, and it will be referred to later in this document. It may also reveal clues that help us to identify the cause of the performance issue. Reproduction steps should be the exact steps that you follow to reproduce the report. Write it as a bullet point list, and don't leave anything out.

For example:

1. I log in as an administrator.

2. I click on ‘Reports’ in the top navigation.

3. I click to view the ‘Report name’ report.

4. I can see the report and it generates in a couple of seconds, with seven pages of results.

5. I select ‘X Organisation Name’ in the ‘Organisations’ filter.

6. I have to wait five minutes for the report to display.

7. I see five results.

Anything you do in the browser should be noted down. If you have specific expectations you could share these here as well. It is also very important that you tell us how long the report took to generate. If you turn on Performance info (see the section below) before you view the report then a generation time will be printed at the bottom of the page (providing your theme supports this). Alternatively just using your stopwatch or keeping an eye on the clock is acceptable if the time taken to display the report is more than two minutes.

If the problem is not reproducible then please inform us of this. It is very likely that the issue is occurring because of something else going on in the system, and it is not the report that is to blame. The investigation will likely need to look outside of report builder for the problem.

Get the SQL for the report

This can be done by the Site Administrator by following these steps:

1. Log in as a Site Administrator.

2. Follow your reproduction steps above to view the slow report.

3. Edit the URL in the browser and add ?debug=1. If there is already a ? in the URL then change it to &debug=1.

4. Press the enter key.

5. You should see some debug output containing SQL above the report.

6. Save the page in your browser so that you can send it to us for investigation.

This SQL helps the developers understand what the database is being asked to do and may even identify potential performance bottlenecks for further investigation.

Check for and identify problem columns

This can be done by the Site Administrator, and should be done in situations where the report runs slowly every time it is viewed, even when no filters have been used. In this situation it is likely one or more columns are the cause of the slowness.

This can be a very useful process, as it will help us to identify which columns, if any, are causing the problem.
Likewise it will identify possible workarounds until the issue is resolved for your client, by identifying columns that can be removed in order to improve performance.

There are three parts to this diagnosis: the first is recording the columns and how they are set up, the second is creating a second test report with no columns, and the final step is to add the columns back one by one, viewing the report after adding each and noting the time it takes.

Step 1: Recording the columns and setup of the report.

1. Log in as a Site Administrator.

2. Browse to Quick-access menu > Reports > Report Builder > Manage reports.

3. Click on the name of the affected report.

4. Note down the Source as shown on the General tab.

5. Change to the Columns tab.

6. Note down each column, and if it is using aggregation or grouping, the value that it is set to.

7. Browse to the Content tab.

8. Note down the settings that are used here, as you will need to recreate this.

Step 2: Recreate the report without any columns

1. Browse to Quick-access menu > Reports > Report Builder > Manage reports.

2. Create a new report using the Source noted above.

3. Browse to the Columns tab and remove all columns.

4. Save the report.

5. View the report and record the time it takes to generate the report with no columns.

Step 3: Add the columns back in one at a time

1. Browse to the Columns tab of your test report.

2. Add a new column, of the same type as the first column in the original report.

3. Set the aggregation or grouping (if that column had aggregation or grouping).

4. Save the report.

5. View the report and record the time it takes to view the report.

6. Now add the next column from the original report and repeat steps 3-5. Record the time to generate the report.

At the end of this you should end up with a set of results something like:

In the above example, adding the first organisation field (Organisation idnumbers) leads to unsatisfactory performance, and then adding the first position field further reduces performance.

Count the records in the database for the tables used by the report

This requires a database administrator with some knowledge of SQL. Looking at the SQL from the Get the SQL for the report step above, and identify the tables that are used by the report. Each table will be wrapped in curly braces, and will not have your database prefix on it, e.g. {user} references the mdl_user table, {course_completions} references the mdl_course_completions table. For each of these tables, run a simple COUNT(*) command to count the total number of rows in the table.

Record the results of each query and provide them to the support team when you file the ticket. This will give us a good gauge on the size of the affected site, and help us accurately reviewing the performance of the query.

Requesting support at totara.support

Once you've done the initial diagnosis above you should have a fair bit of information you can share with us that will help us in supporting you. As well as the diagnosis results, there is some information that will be essential in helping us to understand the issue and to reproduce and fix the problem.

Remember, we want a single request per poorly performing report.

Your server environment

The following can be copied and filled in as part your request at totara.support:

• Exact version of Totara (e.g. 2.9.20, 9.8): 

• Operating system (Linux, OSX, Windows):

• Operating system version:

• Database (PostgreSQL, MySQL, MSSQL):

• Database version:

• Totara database driver if MSSQL: mssql or sqlsrv (see $CFG->dbtype in your config.php)

• Web server (Apache, IIS):

• Web server version:

• Web server - CPU cores: 

• Web server - RAM: 

• Web server hosting classification (dedicated server, cloud environment, shared hosting):

• Are you using a proxy solution (if yes, which):

• Are you using a load balancer (if yes, which):

• Are you using any caching solutions (if yes, which): 

• Are other reports affected of just this one (yes/no):

How the report has been constructed

In order to understand the report we need to know how it has been constructed. The easiest way to share this information is to browse to the editing screen for the report and take a screenshot of each tab. You can do this by following these steps:

1. Log in as a Site Administrator.

2. Go to Quick-access menu > Reports > Report builder > Manage reports.

3. Click on the name of the poorly performing report.

4. Save a full-page screenshot of the General tab.

5. Go to the Columns tab and save a screenshot.

6. Go to the Graph tab and save a full-page screenshot.

7. Go to the Filters tab and save a full-page screenshot.

8. Go to the Content tab and save a full-page screenshot.

9. Go to the Performance tab and save a full-page screenshot.

10. Attach all six screenshots to your support request.

Known technical issues

The following are known technical issues and impactors.

MySQL 5.5

Report builder uses correlated subqueries and temp table aggregation in many of its sources, columns and filters. These are known to perform poorly on large datasets. The only solution to this issue is to upgrade MySQL.

We strongly recommend upgrading MySQL to the latest support version, however 5.6 is satisfactory in getting past these particular problems. For reference, the following is the result of in-product testing of MySQL 5.5 against MySQL 5.7 on a basic user report against a system running 10,000 users with columns added one by one.

Use of MySQL 5.5 is supported in Totara, however we strongly recommend upgrading to the latest stable version of MySQL for both performance and security.

References:

How to take a full-page screenshot in your browser.

Full-page screenshots are screenshots of the whole page including the area that is not visible without scrolling, not just what is visible in the browser. This allows us to see everything that is on the page, and is the only way to really provide the complete view we need.

Unfortunately most browsers cannot take a full-page screenshot themselves, and you will need to install an extension. If you do not want to install an extension or cannot install an extension then instead of taking a screenshot you can save the page as a complete web page and provide that to us instead.

Turning on Performance info

Turning on performance information adds details such as page generation time to the footer of every page, providing your theme supports it. Please be aware that, when enabled, all users are able to see this information on every page. As such we do not recommend it for production use.

To turn on performance information, follow these steps:

1. Log in as a Site Administrator.

2. Go to Quick-access menu > Development > Debugging.

3. Enable Performance info.