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

[Prank]

We're 7 full days down the track now and I can happily report that I've not had the same problem since switching to MySQL 8.

Thanks all 🙌 

[Marc]

8 hours ago, Prank said:

We're 7 full days down the track now and I can happily report that I've not had the same problem since switching to MySQL 8.

Thanks all 🙌 

Thank you for taking the time to come back and confirm 🙂 

[wegorz23]

same .... query that takes 20-30 seconds and get some sort of 20-25 gb of ram. 

And got another nice query mine have 20 400 chars ...

On 11/24/2024 at 6:17 PM, Idea-ahead said:

Yes, this is related to the same topic ID, and no, it is not a DDOS attack.

same on topic id 150712

WHERE forums_posts.topic_id=150712 
  AND (forums_posts.queued IN(0,2)) 

 

[wegorz23]

On 11/22/2024 at 10:05 AM, Matt said:

That is a standard query to get posts. We have Cloud clients with over over 25,000 pages and it loads just fine.

It's likely either a MariaDb issue, or an optimisation issue but the explain is clean and is what we'd expect. Please contact your host for more advice.

we have more and before update it was just fine.... CPU usage was like 6-20%. Not 99.9% 

 

[Jim M]

3 minutes ago, wegorz23 said:

same .... query that takes 20-30 seconds and get some sort of 20-25 gb of ram. 

And got another nice query mine have 20 400 chars ...

same on topic id 150712

WHERE forums_posts.topic_id=150712 
  AND (forums_posts.queued IN(0,2)) 

 

Contrary the original poster's issue, it looks like this is a search query (if my horrible eyes are not deceiving me). This may take some time due to the extensive nature of the query itself. The same question is worth asking though, are you running MariaDB or MySQL 8? If the former, it may be worth testing MySQL 8.

[wegorz23]

Just now, Jim M said:

Contrary the original poster's issue, it looks like this is a search query (if my horrible eyes are not deceiving me). This may take some time due to the extensive nature of the query itself. The same question is worth asking though, are you running MariaDB or MySQL 8? If the former, it may be worth testing MySQL 8.

but it takes only 3 seconds to process..

 

but what about getting posts on topics that takes 20-30 seconds ?? 

MariaDB 10.4.22 bionic....

 

On 11/24/2024 at 11:38 PM, Prank said:

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.

 

same... 

[Jim M]

4 minutes ago, wegorz23 said:

MariaDB 10.4.22 bionic....

As mentioned, you'll want to try MySQL 8.

[wegorz23]

11 hours ago, Jim M said:

As mentioned, you'll want to try MySQL 8.

Is there any other way to improve the performance and fix that problem ? 

The problem started after latest update soo ... maby som sort of hotfix or rollbacks ?

 

 

On 11/22/2024 at 8:56 AM, Marc said:

We have to be somewhat realistic here. If you have a page that is running 1100+ pages, that is very very much outside the normal use of the software, and you are going to require the resources and setup to run those queries. The issue there isnt with the query, its with the fact you have a 1100 page topic.

why system is not prepared for that ? many years ago u sayd that self hosted users need to improve db machines and we also done that. Have two machines where DB server runing always on 5-15% cpu usage then after update u said that the problem is maby mysql server version or something ....

[Marc]

13 minutes ago, wegorz23 said:

why system is not prepared for that ? many years ago u sayd that self hosted users need to improve db machines and we also done that. Have two machines where DB server runing always on 5-15% cpu usage then after update u said that the problem is maby mysql server version or something ....

The system is prepared for that. However that doesnt negate the fact you need to ensure your server can cope with larger datasets. As we have mentioned above, on quite a few occasions, we recommend using mySQL 8, which is what the system is optimised around.

Its worth noting, optimising for mySQL rather than MariaDB isnt really something new. I realise that MariaDB is a 'drop in' alternative that many use, and also very much appreciate that it has previously been faster than mysql at times. However currently its not. 

[bubblesnout]

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.

[Marc]

9 hours ago, bubblesnout said:

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.

We can certainly appreciate that, however please do bear in mind we support MySQL and a drop in alternative is being used there, that seemly is becoming less 'drop in', in terms of performance.