If you are experiencing issues with Moodle reports timing out, here are 10 tips to help you identify and resolve the problem. Some of these will also help if your site is timing out during other intensive Moodle operations such as backing up, restoring and deleting large courses.
- Check PHP’s maximum execution time. Logged in as a Moodle Administrator, navigate to Site Administration > Server > PHP Info. Then search for the max_execution_time line and look at the value in the first column. This value displayed in Moodle should be at least 300 (5 minutes). I have put it to 600 (10 minutes) or higher on some larger systems to handle larger reports and the deletion of larger courses. IMPORTANT: Do NOT do this from a shell, terminal or command line. Many environments have separate PHP settings for the web and for the command line.
- Verify the connection timeout of any firewall, proxy or other type of server that may be in front of your site. Regardless of PHP’s timeout setting, your browser and your web server will only stay connected for as long as the part with the lowest timeout.
- Measure the timeout duration. Knowing how long it takes before the timeout occurs may give you a hint as to what part of your system is timing out. Here are a few examples of default timeouts:
- Apache: 60 seconds
- Cloudflare: 100 seconds
- CloudFront (AWS): 30 seconds
- IIS: 120 seconds
- NGINX: 60 seconds
- PHP: 30 seconds
- Time the generation of your report. Run your report’s SQL query from a terminal/shell/command line. How long does it take to complete? This will give you an idea of the minimum amount of time it is taking to complete. How big is the report? Redirect the output of your command line to a file. The HTML version of a report sent to your browser will take longer and be even bigger to generate. If it takes 15 minutes to run the report from the command line, a PHP execution timeout of 5 or 10 minutes won’t help.
- Timeout while exporting as a file. Are you trying to produce an HTML report or exporting/downloading it as an Excel, PDF, or CSV file? I have seen issues with exporting to Excel, for example, because a PHP extension was missing. The worse part is that it will just timeout instead of showing up as an error.
- Change the tool used to generate reports. What are you using to generate the report? Configurable Reports, ad-Hoc Database Queries or the new Report Builder included with recent versions of Moodle? If you aren’t getting the report that you want out of one of them, try one of the others. Ad-Hoc Database Queries, for example, is lighter and has fewer features than the other two report builders but it is lean and may produce the results you are looking for.
If you are trying to generate large/complex reports that bring your Moodle site to a crawl or cause your site to timeout here are a few suggestions:
- Consider setting up a replica database server and pointing Configurable Reports to this alternate database server.
- Generate your reports in the middle of the night when your site is less busy. This is one of the nice features only found in Ad-Hoc Database Queries. It will even automatically deliver reports by email.
- The Moodle Report Builder’s advantage is the drag-and-drop report builder. But it is very young and does not yet include some of the more advanced features found in the other report plugins to help handle large reports.
- If that isn’t good enough, consider using a 3rd party reporting tool instead of Moodle for your reporting needs.
- Optimize your SQL query. If you have JOINs, LOOPs or TRIGGERs in the SQL query of your report, consider optimizing the query to make it more efficient and/or creating some additional table indexes to improve query performance.
- Can’t run a Moodle Log report? If you are trying to generate a Moodle Log report and it is timing out, your database may require some maintenance. For more information, see Excessive Moodle Logs Part 1 and Excessive Moodle Logs Part 2.
- Monitor the system resources. If the database or web server is running out of CPU, you may need to add more CPUs to your system, to handle the load of processing your report. If your server starts paging memory to a swap file, allocating more memory to your server will significantly speed up report generation. If the configuration of your infrastructure is pretty stable, consider optimizing the configuration of your database server for optimal performance. For more information on this, search for Performance Tuning and Optimization and the name of your database server software.
Are you running the cron/task scheduler from a URL request? Keep in mind that this can be limited by connection and execution timeouts, potentially affecting scheduled maintenance tasks and report generation.
Hope these tips help you optimize your Moodle report and let you get some real rest away from the office.
Happy reporting and see you next month!
- Moodle LMS Email Deliverability in 2024: Best Practices, Authentication Standards and Troubleshooting – 23rd February 2024
- Terminology for Those New to Moodle LMS – 20th January 2024
- Enhancing E-Learning in Moodle LMS with an AI Chat Block and FilterCodes – 20th November 2023