getOnlineUsers causing MySQL problems

[jay5r]

For the third time in as many weeks I've discovered that IP.Board is causing problems with MySQL. When I go in and look client connections to MySQL I see a backlog of requests that look like this one:

-- Connection Id: 208197222
-- User: username
-- Host: localhost
-- DB: databasename
-- Command: Execute
-- Time: 3206
-- State: Sending data
/*IPS\Session\Store\_Database::getOnlineUsers:79*/ SELECT COUNT(*) FROM `core_sessions` WHERE ( core_sessions.id IN(SELECT MAX(id) FROM `core_sessions` AS `s` WHERE s.running_time>1572865467 AND s.login_type!=3 AND s.login_type!=1 AND s.member_id IS NOT NULL GROUP BY `member_id`) OR core_sessions.id IN(SELECT MAX(id) FROM `core_sessions` AS `s` WHERE s.running_time>1572865467 AND s.login_type!=3 AND s.login_type!=1 AND s.member_id IS NULL GROUP BY `ip_address`) ) AND core_sessions.member_id IS NOT NULL /* statement may be truncated */

I've been able to fix the problem by killing those queries and then truncating the `core_sessions` table (which seems to cause no problems).

How do I stop this problem? It slows down everything on my server (not just the IP.Board site).

[Matt]

If you have Redis available to you, you can use that to alleviate issues with your database.

Otherwise you'd need to look at your MySQL config to determine where the bottleneck is.

[jay5r]

2 hours ago, Matt said:

If you have Redis available to you, you can use that to alleviate issues with your database.

Otherwise you'd need to look at your MySQL config to determine where the bottleneck is.

I'm using InnoDB, with 3 GB of RAM in a pool with 4 instances. Typically buffer usage is around 98%, and key efficiency 100%.

I didn't look too closely when I truncated the table this morning, but I know the first time I did it there were only about 500 records in `core_sessions`. Right now there's about 1250 records in the table and everything is operating smoothly. I simply don't understand how sometimes, all of a sudden, there are problems.

Edited November 4, 2019 by jay5r

[Yalu]

Same problem, after running without a hitch for several years. I have memcached enabled now but don't think it's used for session storage, so it didn't seem to help. 

1883 rows in core_sessions

I will try truncating soon but I don't think it's a great or long-term solution.

[bfarber]

We have an open report internally to review this feature for an upcoming maintenance release.

[Yalu]

After the last update (which added caching to the who is online widget if I'm not mistaken) I tried to re-enable the widget and ran into the same problems after 2 days. Had to disable it again and truncate core_sessions or mysql would be stuck at 100% CPU.

Memcached is set up and enabled for caching on the system settings.

Edited February 21, 2020 by Yalu

[iacas]

Has this been resolved? I just submitted a support ticket with a getOnlineUsers issue.