Problem with SQL query that takes a long time to execute

[Idea-ahead]

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

 

[Jim M]

Do you have an example URL with the time it is taking to execute? Seems we're missing that information in the slow query log. I just clicked a topic on your community which has over 1000 pages, and it didn't take a long time to execute than I would expect. 

[Idea-ahead]

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.

[Marc]

12 minutes ago, Idea-ahead said:

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.

What actually happened was that my colleague logged in to see if he could see what you were referring to. When he couldnt, he asked you the question above.

[Idea-ahead]

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.

[Marc]

All I can suggest is letting us know if y ou see the issue happening again.

[Idea-ahead]

23 hours ago, Marc said:

All I can suggest is letting us know if y ou see the issue happening again.

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

[Marc]

If this is going up and down in speed, that can only really be server related. The software either works correctly, or doesnt. There isnt anything in terms of the software that would make it work slower one minutes than the next. It may be worth monitoring when the times are you are seeing this, as it may be something is running at the same point, such as backups

[Idea-ahead]

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

[Marc]

2 minutes ago, Idea-ahead said:

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

I understand what you are saying there, but there will indeed have been changes in 4.7.19. As much as it could be an issue in the software, it could be something changed that your server isn't coping with for whatever reason. If you had updated and you have a constantly slow loading. The reality is, its quite a simple query really. Its just pulling posts. 

[Prank]

I am also getting issues with these queries.

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=379859 AND (forums_posts.queued IN(0,2))
 ORDER BY post_date asc LIMIT 0,20

I just searched here to see if others are having it.

I get thousands of them running over and over. It eventually will bring the site down at random times through the day.

MariaDB [(none)]> show processlist;
+--------+--------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User   | Host      | db         | Command | Time | State        | Info                                                                                                 | Progress |
+--------+--------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| 639673 | skycom | localhost | NULL       | Query   |    0 | Init         | show processlist                                                                                     |    0.000 |
| 639899 | skycom | localhost |[obfiscated] | Sleep   |    0 |              | NULL                                                                                                 |    0.000 |
| 639903 | skycom | localhost | [obfiscated] | Execute |  111 | Sending data | /*[obfiscated]::IPS\Content\_Item::_comments:31*/ SELECT forums_posts.*, author.*, author_pfie |    0.000 |
| 639911 | skycom | localhost | [obfiscated] | Execute |   65 | Sending data | /*[obfiscated]::IPS\Content\_Item::_comments:31*/ SELECT forums_posts.*, author.*, author_pfie |    0.000 |
| 639915 | skycom | localhost | [obfiscated] | Execute |   65 | Sending data | /*[obfiscated]::IPS\Content\_Item::_comments:31*/ SELECT forums_posts.*, author.*, author_pfie |    0.000 |
| 639917 | skycom | localhost | [obfiscated] | Execute |   61 | Sending data | /*[obfiscated]::IPS\Content\_Item::_comments:31*/ SELECT forums_posts.*, author.*, author_pfie |    0.000 |
| 639918 | skycom | localhost | [obfiscated] | Execute |   61 | Sending data | /*[obfiscated]::IPS\Content\_Item::_comments:31*/ SELECT forums_posts.*, author.*, author_pfie |    0.000 |
<snip>

The site will stay down for 30 - 40 mins unless I restart php and mysql. 

It's down right now as I speak with load at 40. I see anywhere between 40 and 100 of those queries running.

The topic from the query above is closed, since 2015 and has 4200 pages. 

Edited November 22 by Prank

[Prank]

I'm down again with a different topic ID.

Another big topic with 1100+ pages.

I'll log a support ticket.

Oh, looks like creating tickets through the admincp is no longer a thing? 

Either way, I restarted mysql, again, to bring things back up. 

[Prank]

The explain;

> EXPLAIN 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=310874 AND (forums_posts.queued IN(0,2)) ORDER BY post_date asc LIMIT 0,20;
+------+-------------+----------------+--------+----------------------------+-----------+---------+-----------------------------------+------+-------------+
| id   | select_type | table          | type   | possible_keys              | key       | key_len | ref                               | rows | Extra       |
+------+-------------+----------------+--------+----------------------------+-----------+---------+-----------------------------------+------+-------------+
|    1 | SIMPLE      | forums_posts   | index  | queued,topic_id,first_post | post_date | 5       | NULL                              | 3048 | Using where |
|    1 | SIMPLE      | author         | eq_ref | PRIMARY,mgroup             | PRIMARY   | 3       | skycom_sau.forums_posts.author_id |    1 |             |
|    1 | SIMPLE      | author_pfields | eq_ref | PRIMARY                    | PRIMARY   | 3       | skycom_sau.author.member_id       |    1 | Using where |
+------+-------------+----------------+--------+----------------------------+-----------+---------+-----------------------------------+------+-------------+
3 rows in set (0.001 sec)

 

[Marc]

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.

[Prank]

1 minute ago, 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.

Seriously? Thats your response?

As with @Idea-ahead the only change on my server is that I'm now running 4.7.19. Invision on my server has not had *any* problems with these topics for many years.

Again, let me reiterate - the ONLY change is that I updated Invision. Nothing else runs on this server, it is entirely dedicated to Invision. 

[Marc]

11 minutes ago, Prank said:

Seriously? Thats your response?

As with @Idea-ahead the only change on my server is that I'm now running 4.7.19. Invision on my server has not had *any* problems with these topics for many years.

Again, let me reiterate - the ONLY change is that I updated Invision. Nothing else runs on this server, it is entirely dedicated to Invision. 

Fair comment, and asked if someone can take a look to see what has changed there. 

[Prank]

1 minute ago, Marc said:

Fair comment, and asked if someone can take a look to see what has changed there. 

Thanks, very much appreciated.

[Marc]

On 11/16/2024 at 11:05 PM, Idea-ahead said:

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

 

Please could you confirm if this is actually taking a long time, or causing you issues because its being run repeatidly? I ask as the posted above mentioned the latter, so trying to determine if these are actually the same issue

[Matt]

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.

[Prank]

1 minute ago, 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.

Hi Matt, I don't think this is a good enough response when it was fine with the previous version. 

If you do refuse to accept that something has changed maybe you can advise if its possible to roll back to the previous version?

Also, note that the explain is clean, but its running *so* many queries that it bogs down. Individually the queries are fine, but not at the numbers they come in at.

[Matt]

Which version were you on previously?

We changed how offsets are handled in large data sets when getting comments in December 2023.

We removed an old optimisation in Content/Item.php in April 2024 that was causing a lot of issues with newer versions of MySQL.

[Prank]

I always keep it updated, within days of an update being released so it would have been the previous point release.

As I said above, this is the only change I've made. The server is hefty, Maria is very well configured and invision has never had a problem with these topics until the update. I run nothing else on the server.

I'd just appreciate not being fobbed off here.

To be clear - I don't know if these queries come from viewing the topic or something else. Maybe you guys could shed soe light on this? I'm happy to dig into code, I just haven't had a chance.

[Matt]

It's not about fobbing you off, it's just nothing significant changed between .18 and .19, so there's little we can offer in terms of advice.

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.

Looking at what you've shared, the query to fetch the comments is fine. It takes virtually no time, and the explain is clean in that there are no temporary tables or file sorts.

The process list shows MySQL waiting 111 seconds for 'sending data' which is a bit misleading as it's still likely scanning the disk for data due to the buffer pool either not being primed or being full but it looks more like slow disk access or network latency than a slow query. But it's something you'd need to discuss with your host.

I understand the frustration of "but nothing has changed" and can only tell you some of us have been woken in the night to deal with something even though nothing has changed.

The alternative is for one of us to debug your MySQL and web servers to identify the problem but that is beyond technical support for our product.

[Prank]

Hi Matt, thanks for the reply. I appreciate the info you've added.

I think Marc telling me straight up that I was being unrealistic and that my 1100 page topic was "very very much outside the normal use of the software" was a clear fob attempt and was totally incorrect, as you stated you have clients with 25k pages. It wasn't helpful at all. 

On the flip side I think that you adding this extra info has been very helpful. If you say that there was no change between the two releases that could cause this problem then I'll have to accept that and look into other options. Thanks again. 

[Marc]

25 minutes ago, Prank said:

I think Marc telling me straight up that I was being unrealistic and that my 1100 page topic was "very very much outside the normal use of the software" was a clear fob attempt and was totally incorrect, as you stated you have clients with 25k pages. It wasn't helpful at all. 

Again this was also not about fobbing you off. It's important things are taken within the context that was said. It may well be myself who hasn't explained myself very well there, and can only apologise for that. While there are indeed clients with 25k pages, that doesn't make that normal use. It means simply that they exist, and these people aren't having issues. What i actually said there was 

Quote

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

So by this, I mean you need to ensure if you have those kinds of topics and items which are going to take longer to run than say a topic with 10 pages, you need to ensure you have the resources and setup to run those things effectively. In this case, as pointed out by Matt above, running MySQL 8 will likely help, and indeed further to that, getting someone to debug the MySQL instance and web servers would be what you would need.