Idea-ahead

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.

I asked the forum admin for additional information. Below is his message. --- I would like to report an issue that has arisen following a recent software update. Despite migrating our database to a machine with 12GB of RAM and 8 CPU cores (previously hosted on a machine with 6GB of RAM and 4 CPU cores), we have not observed any significant improvement in performance. The issue started after the software update, and we are not the only company experiencing this problem. Therefore, we request that this issue be treated with priority. If necessary, we ask for guidance on the escalation process within the company, particularly to the team responsible for the software, so we can resolve this issue as quickly as possible. Here are the details of the changes made: Migrated the database to a machine with 12GB of RAM and 8 CPU cores. Optimized MySQL settings, including innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method, and other memory and disk-related adjustments (NVMe disk). Optimized PHP settings, including storing sessions in memory (memcached) and using OPcache in memory. Despite these changes, the performance remains unsatisfactory, which suggests the issue may lie with the software itself. Additionally, as seen in the attached screenshot, there appears to be a repeated spawning of queries within a very short time span, which could be contributing to the performance degradation. We would appreciate a prompt response and any further instructions on how to proceed. Thank you for your attention to this matter.

Marc, the problem is that the server configuration has not been changed and there have been no problems for many months. These started literally a few hours after the forum update to 4.7.19. There are currently no processes running in the background (e.g. backup).

For example, now for the past several minutes the forum has been running very slowly. https://www.psxextreme.info/

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.

Jim, everything is OK now. About 1-2 hours after my report I saw this in the dashboard: "This account was created when you recently contacted Invision Community Support so that our team could use it. If the issue has been resolved, you should delete this account." So apparently someone from your group logged in and something was fixed because the issue has gone away.

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