Had to Disable Leaderboard, MySQL Pegged?

[iacas]

I'm not sure if it's the Leaderboard that's causing the problem(s) I've been having since updating to 4.1.17, but several times today I'd find a mysql process pegged at 100% on my server, preventing anyone from viewing anything on the site until I killed the process (which, to make things simpler, I'd often just kill/restart mysqld and httpd).

I don't know if it's someone checking the "all-time" stats on the Leaderboard or something else entirely, but I've disabled the Leaderboard for now and will see if the problem disappears.

I hope to heck whatever is causing this quickly becomes a known issue with a fix issued, because right now it's really bad - I can't be around to restart mysqld/httpd several times per day within a minute or two of them getting smeared.

I see a few things that I'll pay attention to in my httpd/error_log, but I also see some Leaderboard-specific entries in my mysql/slow-log, like:

tail -f slow-log 
SET timestamp=1480989865;
/*IPS\core\modules\front\discover\_popular::leaderboard:66*/ SELECT lookup_type_id_hash, SUM(rep_rating) as total_rep FROM `core_reputation_index` STRAIGHT_JOIN `core_search_index` ON core_search_index.index_object_id=core_reputation_index.type_id AND core_search_index.index_class=core_reputation_index.rep_class AND ( index_permissions = '*' OR ( FIND_IN_SET(8,index_permissions) ) AND index_hidden=0 ) WHERE rep_date BETWEEN 1478390400 AND 1480989775  AND rep_class='IPS\\forums\\Topic\\Post' GROUP BY `lookup_type_id_hash` ORDER BY total_rep DESC LIMIT 0,50;
# Time: 161205 22:14:27
# User@Host: ipb[ipb] @ localhost []  Id: 43321
# Query_time: 4161.353940  Lock_time: 0.000032 Rows_sent: 0  Rows_examined: 42637
SET timestamp=1480994067;
/*IPS\core\modules\front\discover\_popular::leaderboard:66*/ SELECT lookup_type_id_hash, SUM(rep_rating) as total_rep FROM `core_reputation_index` STRAIGHT_JOIN `core_search_index` ON core_search_index.index_object_id=core_reputation_index.type_id AND core_search_index.index_class=core_reputation_index.rep_class AND ( index_permissions = '*' OR ( FIND_IN_SET(8,index_permissions) ) AND index_hidden=0 ) WHERE rep_date BETWEEN 1296574703 AND 1480989906  AND rep_class='IPS\\forums\\Topic\\Post' GROUP BY `lookup_type_id_hash` ORDER BY total_rep DESC LIMIT 0,50;
/usr/libexec/mysqld, Version: 5.6.30-log (Distributed by The IUS Community Project). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock

So…

 

Does anyone else have similar experiences/issues? My forum is rather old, but not super huge.

[iacas]

The latter query has Unix timestamps of February 1, 2011 and December 6, 2016, so it kind of makes sense to me and backs up what I seem to have noticed that clicking "All Time" can cause major blocking and trouble.

[Lindy]

Interesting - thanks for bringing this up. We can't immediately reproduce this, so would you mind submitting a support request and mentioning this topic so we can get you straight to engineering to further debug? If you can do this now, I'll get it expedited personally so it's addressed in the morning. 

Thanks so much.

[iacas]

I'll do so right now. I've done further testing and it's become incredibly clear that the leaderboard is the issue.

The leaderboard query (I did just "this year" for the first test, which failed…) is running forever and preventing other queries that need to lock tables to operate from getting their lock (and hanging everything up).

(Filed @Lindy: #969512).

[Lindy]

Thanks Erik! I've flagged it up to development. 

[Apfelstrudel]

Please keep us up to date on this issue.

[RevengeFNF]

I think it also depends on the server and your Mysql configuration. Using Innodb with the full DB fit in the Innodb Buffer Pool, choosing All Time(4 years) it takes about 3 seconds to fully load in my site.

[Apfelstrudel]

Would be great to have a switch for disabling those massive timespan queries.

[Square Wheels]

Thanks for posting this, I have not updated my sites yet.  Is the Leaderboard an option to turn on / off until this is resolved?

[RevengeFNF]

Just now, Square Wheels said:

Thanks for posting this, I have not updated my sites yet.  Is the Leaderboard an option to turn on / off until this is resolved?

Yes, you can turn it off, but if you have your server well configured, you don't need to worry with this.

[The Old Man]

The Leaderboard would be cached though wouldn't it? Say run once every 4hrs or something?

Presumably  sone protection so that bots wouldn't be able to trigger a large All Time query loads of times a minute?

[RevengeFNF]

44 minutes ago, The Old Man said:

The Leaderboard would be cached though wouldn't it? Say run once every 4hrs or something?

Presumably  sone protection so that bots wouldn't be able to trigger a large All Time query loads of times a minute?

Ya, a layer 7 attack to that page can be very devastating. 

[Charles]

Please do not spread misinformation and confuse people

A small number of clients are having issues with the new Leaderboard feature and we are working with them in tickets to sort it. If you are having issues yourself please submit a ticket.

[iacas]

2 hours ago, Apfelstrudel said:

Would be great to have a switch for disabling those massive timespan queries.

I agree that it would be nice. Just limit it to the past day, week, and month if I choose.

2 hours ago, RevengeFNF said:

Yes, you can turn it off, but if you have your server well configured, you don't need to worry with this.

I don't think it has much to do with how "well configured" the server is. You don't know how my site or server are configured, so please don't assume…

[RevengeFNF]

17 minutes ago, iacas said:

I agree that it would be nice. Just limit it to the past day, week, and month if I choose.

I don't think it has much to do with how "well configured" the server is. You don't know how my site or server are configured, so please don't assume…

Im not assuming anything. I just said that a server with the tables using Innodb Storage Engine with enough buffer pool, i don't believe it will be an issue. I don't have small site and it only takes 3 seconds to load it.

PS: Im not even using a dedicated server, but a vps.

[iacas]

The bug seems to have been fixed - without any changes to my server setup - and now my site responds very very quickly. Knock on wood and all that, but queries that took over ten minutes now take about a second or two.