problems with server load after upgrade to 4.0

[Vroom]

After upgrading to 4.0 having some server load problems. My server is running fine most of the time. Then this query came up today and the server was close to death: 

| 1063478 | localhost | DATABASE | Execute | 7344 | Sending data        | /*IPS\Patterns\_ActiveRecordIterator::count:246*/ SELECT SQL_CALC_FOUND_ROWS forums_posts.*, forums_topics.* FROM `forums_posts` LEFT JOIN `forums_topics` ON forums_posts.topic_id=forums_topics.tid LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_topics.forum_id LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id WHERE ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND ( forums_forums.password IS NULL OR ( ( FIND_IN_SET(2,forums_forums.password_override) ) ) ) AND ( forums_forums.can_view_others=1 OR forums_topics.starter_id IS NULL ) AND forums_forums.min_posts_view<=0 AND queued=0 AND forums_topics.approved=1 AND (( ( FIND_IN_SET(2,perm_2) ) ) OR perm_2='*' ) ORDER BY post_date desc LIMIT 0,25  

It ran for 6 hours, and spawned 5 or 6 identical queries, which further killed the server. Finally I had to kill them off because server load was at 10 and I/O was at 100% util.

Then everything back to normal and everything working fine again. After that this query didn't come back and server load is back to 1.

Anyone can tell what this query is related to? Is it part of the upgrade background processes? Will it go away or keep coming back forever to haunt me?

Forum has 15 million posts and is on a dedicated server.

 

[AutoItScript]

It's looks like Activity Stream->Posts

I've got it disabled on my forum for that reason. Definitely disable it for guests at least (System->Applications->System->Activity Stream).

If all your tables are Innodb it should complete and other users should be OK (it will be slow though, mine is 30 seconds on 1.5million posts).

If all your tables are MyISAM it will completely lock some tables and other users will hang.

[Vroom]

Thanks for the reply. I will disable it and see.

I plan to upgrade the server to SSD drives and 32GB ram. But I never needed to do that in the past with any other forum I ran even with much much more active forums.

[ctrlbrk]

Thanks for the reply. I will disable it and see.

I plan to upgrade the server to SSD drives and 32GB ram. But I never needed to do that in the past with any other forum I ran even with much much more active forums.

This will help, but are your tables InnoDB or MyISAM?

[Vroom]

The largest tables are InnoDB. I havent had time to convert all the small tables.

[genrobgen]

I've had this exact same problem for two months ever since upgrading.

I submitted a support ticket, but it never went anywhere.

I've since upgraded MySQL 5.5 to 5.6, changed my tables to InnoDB, switched on memcache, tweaked the my.cnf settings and the IPS settings.

All to no avail.

In the end I wrote a script on a cron to automatically kill these queries after 30 seconds:

https://github.com/kill-slow-queries-ips-4-0-4.php

 

[Vroom]

Thanks. I will have a look at your script and may have to use it.

But it raises the question, if this is happening to multiple people then isn't something wrong with the software? I have ran more than a dozen different forum softwares over the last 15 years. I never had this problem where a single query would kill a server. I know I can add more ram, change to SSD, etc., but should we really have to do all that for something so simple? 

[AutoItScript]

I wouldn't use the script personally - the root problem needs fixing and not hiding. That sql query you posted is definitely Activity Stream and that has been reported and bugged (but ignored to be honest, but Activity Stream is being rewritten so...). The Topic Feed widget also has a similar query that causes issues for many (also bugged and marked as fixed for 4.0.10 but we'll see).  if you see a long query that has a "limit 5" at the end of it it's a Topics Feed widget, if you see "limit 0,25" it's likely Activity Stream/New Content.

People with small boards won't see it at all. You need to go all Innodb to "mask" the issue at least so that other users don't get locked when it runs. Even though you've converted a couple of tables to Innodb you might be getting locks on some of the tables in that query which are:

• forums_posts
• forums_topics
• core_permission_index
• forums_forums

Even though you've converted the forums_posts table (I'm guessing). If the forums_topics table is locked then NOONE can view your board at the same time.

If you want to verify which it is then I did this:

1. Disable Topic Feed widgets, set Activity Stream so that only Administrators can access
2. Open 3 browser tabs, two on the forum, one in phpmyadmin
3. In phpmyadmin goto Server -> Status -> Processes. Enable 2 second autorefresh
4. On forum tab 1 do Activity Stream -> Posts - this should take a while
5. On forum tab 2 try and browse to a topic
6. In phpmyadmin you should see the long query running - check it's the same one you posted above and monitor it

If it's what I think it is your forum tab 2 session should hang and you will see a sessions queueing up "waiting for table lock"

 

[The.Reaper]

Also suffering from slow loads, let hope it's fixed in the next update!

http://ancient-fury.com/Forum/

[genrobgen]

That sql query you posted is definitely Activity Stream

More specifically, it's the Activity Stream when filtered to show topics or posts only, particularly posts.

As a temporary work-around on my forum I've hidden that filter panel.

The activity stream still shows, but users can't find any link to filter. The default activity at URL "/activity/" loads reasonably fast.

On this here forum the "Activity posts" is also quite slow, takes about 5 seconds.

https://community.invisionpower.com/activity/?type=forums_topic_post&change_section=1

But as others have mentioned, it sounds like this is being worked on and will be fixed or changed in a forthcoming version.

[Vroom]

I disabled activity stream as recommended above and haven't had any problems since.

[Lab Rats Rule]

Well, I have two nearly 1000 member forums, one with 3.34 and the other with 4.0, and my speed is wonderful. .  In fact flash lightning...   BUT, I changed my mem limits for PHP to -1.  I have 4 GB memory.

So there you have it, you needs lots of dedicated memory.  I like the activity stream, and I want another on my forum page in a side block if possible,.

[DD&DD]

I've had this exact same problem  months ever since upgrading.
AutoItScript thank you very much to explain.

[h-y-b-r-i-d]

Was this ever addressed by ips?

[Ohio Riders]

On November 26, 2015 at 5:38 PM, mrbanjo said:

Was this ever addressed by ips?

Nope. Still slow.