Pro Reports Struggling

Hi guys,

The server we’re hosting the platform on is high on spec and with basically no specific limitations (60+ CPU 250G RAM). All PHP values are also much higher than the recommended ones. Here’s the server info report from the dashboard:

### WordPress Check                Required                                               Detected                                     Status   ###

FileSystem Method                  = direct                                               direct                                       Pass        
MultiSite Disabled                 =true                                                  true                                         Pass        
WordPress Memory Limit             >=64M                                                  1024M                                        Pass        
WordPress Version                  >=6.2                                                  6.5.4                                        Pass        

### PHP                            Required                                               Detected                                     Status   ###

cURL Extension Enabled             =true                                                  true                                         Pass        
cURL Timeout                       >=300 seconds                                          1000                                         Pass        
cURL Version                       >=7.29.0                                               7.81.0                                       Pass        
OpenSSL Version                    >=OpenSSL/1.1.0                                        OpenSSL/3.0.2                                Pass        
OpenSSL Working Status             Yes                                                    Yes                                          Pass        
PCRE Backtracking Limit            >=10000                                                1000000                                      Pass        
PHP Allow URL fopen                N/A                                                    YES                                                      
PHP Disabled Functions             N/A                                                    No functions disabled.                                   
PHP Exif Support                   N/A                                                    YES                                                      
PHP IPTC Support                   N/A                                                    YES                                                      
PHP Loaded Extensions              N/A                                                    Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, bcmath, brotli, calendar, cgi-fcgi, ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, gmp, hash, iconv, imagick, imap, intl, json, ldap, libxml, mailparse, mbstring, mysqli, mysqlnd, openssl, pcntl, pcre, pdo_mysql, pdo_sqlite, posix, random, readline, redis, session, soap, sockets, sodium, sqlite3, standard, tidy, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, zlib            
PHP Max Execution Time             >=30 seconds                                           3000                                         Pass        
PHP Max Input Time                 >=30 seconds                                           -1                                           Pass        
PHP Memory Limit                   >=256M                                                 4096M                                        Pass        
PHP Post Max Size                  >=2M                                                   1024M                                        Pass        
PHP Safe Mode Disabled             =true                                                  true                                         Pass        
PHP Upload Max Filesize            >=2M                                                   1024M                                        Pass        
PHP Version                        >=7.4                                                  8.2.16                                       Pass        
PHP XML Support                    N/A                                                    YES                                                      
SSL Extension Enabled              =true                                                  true                                         Pass        
SSL Warnings                       = empty                                                                                             Pass        

### MySQL                          Required                                               Detected                                     Status   ###

MySQL Client Encoding              N/A                                                    utf8                                                     
MySQL Mode                         N/A                                                    IGNORE_SPACE                                             
MySQL Version                      >=5.0                                                  8.0.35                                       Pass        

### Server Configuration           Detected Value                                      ###

Accept Content                     text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7
Accept-Charset Content             N/A                                                    
Architecture                       64		 bit                                               
Gateway Interface                  CGI/1.1                                                
HTTPS                              ON                                                     
Memory Usage                       9.97 MB                                                
Operating System                   Linux                                                  
Request Time                       1718095865                                             
Server Protocol                    HTTP/2.0                                               
Server self connect                Not expected HTTP response body:                       
Server Software                    Apache                                                 
User Agent                         Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/ Safari/537.36

### MainWP Dashboard Settings      Detected Value                                      ###

Abandoned plugins/themes tolerance 365                                                    
Basic uptime monitoring enabled    Yes                                                    
MainWP Dashboard Version           Latest: | Detected: Pass               
MainWP legacy backups enabled      No                                                     
Maximum number of pages to return                                                         
Maximum number of posts to return                                                         
Maximum simultaneous install and update requests6                                                      
Maximum simultaneous requests      4                                                      
Maximum simultaneous requests per ip2                                                      
Maximum simultaneous sync requests 13                                                     
Minimum delay between requests     200                                                    
Minimum delay between requests to the same ip1000                                                   
Number of connected sites          OVER 1k (edited)                                                  
Optimize data loading              Yes                                                    
Plugin advanced automatic updates enabledYes                                                    
Primary backup system              MainWP Legacy Backups                                  
REST API enabled                   Yes                                                    
Site health monitoring enabled     No                                                     
Theme advanced automatic updates enabledYes                                                    
Use WP Cron                        Yes                                                    
WP Core advanced automatic updates enabledYes                                                    

### Extensions                     Version                                                License                                      Status   ###

Advanced Uptime Monitor Extension  5.3.1                                                  Active                                       Pass        
Dashboard Lock                     5.0.1                                                  Active                                       Pass        
MainWP Custom Dashboard Extension  5.0.1                                                  Active                                       Pass        
MainWP Database Updater Extension  5.0.1                                                  Active                                       Pass        
MainWP Domain Monitor Extension    5.0.2                                                  Active                                       Pass        
MainWP Pro Reports Extension       5.0.2                                                  Active                                       Pass        
MainWP Vulnerability Checker Extension5.0.1                                                  Active                                       Pass        
MainWP White Label Extension       5.0                                                    Active                                       Pass        

### Plugin                         Version                                                Status                                    ###

Activity Log for MainWP            2.1.3                                                  Inactive                                     
Admin CSS MU                       2.10                                                   Active                                       
MainWP Dashboard                                                         Active                                       
Redis Object Cache                 2.5.2                                                  Active                                       
Wordfence Security                 7.11.6                                                 Active                                       
WP Activity Log                    5.0.0                                                  Inactive                                     
WP Mail SMTP                       4.0.1                                                  Active                                       

We’re managing quite a lot of sites and have been having performance issues with the “Pro Reports” extension and generating individual reports ever since.
The initial report sites list page loads fine and with no delays (page=Extensions-Mainwp-Pro-Reports-Extension), however everything else like “Create report” or “Edit” site, takes forever and in most cases doesn’t even complete (it just spins). Stracing the process in most cases loops through this:

sendto(13, "\246\10\0\0\3SELECT wp.*,wp_sync.*,wp_optionview.*\n                    FROM wp_mainwp_wp wp\n                    JOIN wp_mainwp_wp_sync wp_sync ON = wp_sync.wpid\n                    JOIN (SELECT AS wpid ,(SELECT recent_comments.value FROM wp_mainwp_wp_options recent_comments WHERE  recent_comments.wpid = AND = \"recent_comments\" LIMIT 1) AS recent_comments,\n\t\t\t\t\t(SELECT recent_posts.value FROM wp_mainwp_wp_options recent_posts WHERE  recent_posts.wpid = AND = \"recent_posts\" LIMIT 1) AS recent_posts,\n\t\t\t\t\t(SELECT recent_pages.value FROM wp_mainwp_wp_options recent_pages WHERE  recent_pages.wpid = AND = \"recent_pages\" LIMIT 1) AS recent_pages,\n\t\t\t\t\t(SELECT phpversion.value FROM wp_mainwp_wp_options phpversion WHERE  phpversion.wpid = AND = \"phpversion\" LIMIT 1) AS phpversion,\n\t\t\t\t\t(SELECT added_timestamp.value FROM wp_mainwp_wp_options added_timestamp WHERE  added_timestamp.w"..., 2218, MSG_DONTWAIT, NULL, 0) = 2218
poll([{fd=13, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000

For the sake of troubleshooting, we tried enabling this on the child site’s report plugin settings:
" Comment Flood Tracking" , but no luck.

We’ve also tried adding Redis but it didn’t make a difference either (there’s limited info online if it would actually help or might cause issues with the overall performance and functionalities)

There are no browser console errors or processes running in the Network tab that eventually timeout (which is a bit odd).

My questions are:

Is there a setting somewhere in the reports plugin that could help with optimizing the SQL queries and perhaps exclude certain data?

How many sites can it handle realistically? It does say “Unlimited” but there is a point where it would eventually start to struggle?

Are there API end-points that can be used for generating monthly reports? This will be very, very handy since the functionality can be still used even if the UI struggles.

Any insight would be appreciated!

Hey @Fixed

Welcome to the MainWP community!

There aren’t Pro Reports specific settings, but there is Optimize data loading, which is a Dashboard-wide setting that, according to your System Report, you already have enabled.

Can you temporarily deactivate it and see if you get the same behavior on the Create and Edit report pages?

Generally we don’t advise using any caching/performance plugins on the Dashboard site. Since most of the content is dynamic, the performance improvements are going to be neglible, but they can sometimes cause issues.

We’ve had a number of users with over a thousands child sites and we aren’t aware of a actual limit at which the functionality breaks. It has always been a matter of adequate server resources.

Our API has Pro Reports calls but not for generating reports. They are mostly for fetching individual pieces of data that would otherwise be included in the report.