After the last forum update (4.7.19) on my server, there was a problem with a SQL query that takes a very long time to execute. It is most visible in large topics with more than 500 replies. Increasing the amount of RAM or CPU does not help much.

/psxekstrema::psxmoder::IPS\Content_Item::_comments:2166/ SELECT forums_posts., author., author_pfields.* FROM ibf_forums_posts AS forums_posts  LEFT JOIN ibf_core_members AS author ON author.member_id = forums_posts.author_id  LEFT JOIN ibf_core_pfields_content AS author_pfields ON author_pfields.member_id=author.member_id WHERE forums_posts.topic_id=112736 AND (forums_posts.queued IN(0,2)) ORDER BY post_date asc LIMIT 0,25

 

Oh, that's interesting, because I thought you might have fixed something. After logging into our panel, the problem disappeared. We didn't try to do anything.

We noticed similar issues going from 4.7.13 to 4.7.16. I was in contact with @Matt back then. 

In our case we ended up reverting to an the code in getItemsWithPermission from an earlier version where we hadn't had that issue with getItemsWithPermission method.

We also got another issue upgrading to 4.7.17. In that case we ended up reverting some code in _comments()

Using Mariadb 10.5. But we'll upgrade to either a newer version of MariaDB or Mysql 8.0 or Mysql 8.4 soon-ish, so we hopefully can remove these customizations. 

 

particularly to the team responsible for the software

It would be worth mentioning that Matt has replied in this topic, who is our Director of Software Development. Everyone here is responsible for the software who has been commenting. This topic is an official means of support 😉 .

 

This is a bit hard to read. Are you able to enlarge it? Are these all the same topics?

It may be worth exploring who is making these requests. Is it a DDoS attack?

Yes, this is related to the same topic ID, and no, it is not a DDOS attack. We have verified this on the edge server, and there were no such activities at the time in question. Additionally, our provider did not notify us of any incidents.

From our analysis of the SHOW ENGINE INNODB STATUS output, it appears that the issue stems from resource contention, specifically competition for table access. This contention seems to cause the system to stall.

I am attaching a small excerpt from the output for reference:

MariaDB [psxekstrema]> SHOW ENGINE INNODB STATUS\G;
** 1. row **
  Type: InnoDB
  Name:
Status:
=====================================
2024-11-24 10:07:12 0x7fbc281fe700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
...
--Thread 140441241384704 has waited at btr0sea.cc line 956 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x55ea60016340 created in file btr0sea.cc line 240
...

Interestingly, this issue occurs sporadically without a clear pattern in timing or other consistent factors, but it does happen several times a day.

One of the queries being executed at the time is as follows:

SELECT forums_posts., author., author_pfields.* 
FROM ibf_forums_posts AS forums_posts
LEFT JOIN ibf_core_members AS author ON author.member_id = forums_posts.author_id
LEFT JOIN ibf_core_pfields_content AS author_pfields ON author_pfields.member_id = author.member_id 
WHERE forums_posts.topic_id = 111953 
AND (forums_posts.queued IN(0,2)) 
ORDER BY post_date ASC 
LIMIT 0,25;

Interestingly, when the system stabilizes, this same query executes relatively quickly, taking about 2 seconds.

Our question is whether there could be an issue with the query structure that affects index usage, particularly with this fragment:
forums_posts.queued IN(0,2).
Could this condition be causing the optimizer to avoid using indexes effectively?

We would appreciate your insights into whether there are structural improvements we could make to mitigate this issue.

Looking forward to your guidance.

For those who are still having issues here, its worth reading this part of Matts comments above. 

 

Personally, I've found MariaDb less efficient than vanilla MySQL 8.x which we generally optimise for as it's the most used MySQL variant.

Just throwing some potentially helpful info in here from my investigations and the posts from the Invision guys.

The process list posted by @Idea-ahead looks identical to mine. They all relate to a single topic.

The query individually is perfectly fine, runs fast. I think its just a snowball of queries (Similar to the affect of a DDoS but see below).

But, its definitely not a DDoS attack. I don't see an increase in PHP processes and I'm behind Cloudflare with fairly strong security like browser check etc.

After the Invision responses and some of my own research I decided to follow the recommendations and switch to MySQL 8 from Maria 10.5 yesterday. I'm at 20h uptime now which is promising but early days. I'll report back after another day or two to see how it's looking.

 

Just wanted to put my hand up as another one having this same problem.

Also running mariadb which has been working perfectly for years, but since 4.7.19 we are regularly seeing mariadb get absolutely smashed which grinds the server to a halt and makes IPS completely die.

I don't see much point posting logs when they are effectively identical to those posted above. Just want to help understand the scale of this problem by adding myself to the list.

I don't particularly want to build out a new server with MySQL 8 and do a migration but it's sounding like that's going to be the only option here. Surely it can be appreciated why this is so frustrating when this configuration has worked perfectly fine for years and now after an update it's basically rendering the software completely unusable.