Hopefully, some of this is useful. We are on a decent shared cloud plan, with shared MySQL. Performance is obviously relative, but should be instructional.
[For reference, our board currently has ~500,000 posts. The board is open for viewing, but only available to prior vetted users for posting. Hence, spam is not an issue for us.]

Hello

I've now completed a test upgrade to 20220803.1. The upgrade went fairly smoothly once I got a hang of the process and some cPanel File Manager limitations.

What limitation did you encounter?

The key cPanel limitation is that it does not allow folder uploads, only files. The workaround is to upload a ZIP of the forum install into a different directory and then copy the appropriate files.

As you mention, 20220803.1 does appear to run significantly slower for us than even 2.0.2, on every page.

Wow, slower than 2.0.2. That's disappointing.

Yes, definitely. I'll post some results / conclusions below.

Based on yours and Daniel's comments, I understand the DB changes from MyISAM to InnoDB, and the additional spam features added seem to be the primary culprits. Note that 2.4.24 provided the (significantly) best performance for us across all upgrade steps.

Maybe it's a good idea to stay with version 2.4.24 for the production/real system.

Yes, thanks. This is exactly where I am leaning at this point, particularly after digging into the SQL a bit.

I'm going to take a look at the current DB structure a little more and see if anything stands out from a SQL performance perspective. The fact that performance is slow on every page, including Users / Admin / Forum Settings, etc, is a bit surprising here.

Thank you for your effort. I hope, that, looking from different perspectives onto the issue, more eyes see more.

Glad to be of any service. It's the least I can do to appreciate all the work you and everyone have done to keep this board alive all these years.

OK, for some results / conclusions:

1) As noted previously, every page runs slowly on our forum in 20220803.1. Admin / user / help pages all take ~5 seconds to load. Home page, with 20 threads and 15 last postings takes ~ 10 seconds. Given this info, I focused on the "totals" line at the bottom of the page to start, since that displays on every forum page.

2) I tweaked 2 files (/includes/index.inc.php and includes/main.inc.php to remove any checks in the SQL WHERE clause for .spam = [0 || 1]. These fields are in the mlf2_akismet_rating and mlf2_b8_rating tables. I did leave the table JOINs in place, since that did not impact performance. After digging, I came to the conclusion these tweaks also impacted performance of the home page thread list and last postings.

3) After these quick changes:
a. Performance of the home page is significantly improved (~80% faster) with the changes to the above files.
b. Performance of the home page is significantly worse (75-125% slower) for non-logged in visitors over logged in visitors. 2-3 seconds for logged in visitors, 5-6 seconds for non logged in visitors.

4) Note #a is promising, but note #b is baffling. I have not done any digging yet on why performance might differ here, but welcome to any feedback.

5) I can duplicate the issues Daniel mentions in your performance issues link. You can see the references to "WHERE / AND (mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0)" or similar in all the queries he posts about.

6) From a SQL perspective, the biggest issue I see with these spam checks is there is basically no cardinality on these fields (0 or 1 only), plus MySQL does not have a great way of handling boolean fields. In MySQL, TinyInt(1) and Bit field types both use a full byte and do not have true boolean characteristics according to SQL standards. Since cardinality is so low, an index on these fields (even though they exist) is not of great value to the queries. This likely causes a table scan and definitely affects performance. I am not sure what the best answer moving forward is, but wanted to shed some light on the issue.

7) One note regarding the upgrade process. After upgrading, I looked at the SQL table indexes, and compared it to a clean install. After upgrade to 20220803.1, the mlf2_akismet_rating table was missing indexes on the spam and spam_check_status fields. These indexes exist on a clean install, but not an upgrade.

I will post some performance results after I run through a separate instance upgrade to 2.4.24.

Joe