Problem with SQL query that takes a long time to execute

[TSP]

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. 

[Prank]

15 hours ago, TSP said:

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. 

I'd appreciate you posting what you do here as we were on Maria 10.3 and now 10.5. I'm weighing up options.

[Idea-ahead]

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.

[Jim M]

3 hours ago, Idea-ahead said:

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 😉 .

3 hours ago, Idea-ahead said:

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?

[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.

[Marc]

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

On 11/22/2024 at 9:19 AM, Matt said:

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.

[Richard Arch]

Out of curiosity I ran the query form above after adjusting the table prefix, it fails to run in both MySQL and MariaDB with this same error.

 

Quote

 

MySQL said: http://localhost/phpmyadmin/themes/dot.gif

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', author., author_pfields.*
FROM forums_posts AS forums_posts
LEFT JOIN core_' at line 1

 

 

 

Is not having the * valid in the Select statement? 

Should it be "SELECT forums_posts.*, author.*, author_pfields.*" instead?

Once adjusted it runs fine.

[Marc]

If you mean from the screenshot there, its showing only part of the sql statement

[Prank]

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.

 

[TSP]

@Idea-ahead I sent you a message on my modifications done to the Item.php file for 4.7.17. I expect one of those two methods is the culprit. Please note as I said that my customizations was made for 4.7.17, and I haven't reviewed the changes made to that file in newer versions 4.7.18 and 4.7.19 yet, so it may not be directly compatible. 

[Richard Arch]

23 hours ago, Idea-ahead said:

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;

18 hours ago, Marc said:

If you mean from the screenshot there, its showing only part of the sql statement

No, I meant the query within the post.  It seems to be missing something after the dots.

 

[Marc]

Yes, its missing a * by the look of it. very likely just a deletion when it was posted here