jay5r Posted November 4, 2019 Share Posted November 4, 2019 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). Link to comment Share on other sites More sharing options...
Management Matt Posted November 4, 2019 Management Share Posted November 4, 2019 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. Link to comment Share on other sites More sharing options...
jay5r Posted November 4, 2019 Author Share Posted November 4, 2019 (edited) 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 Link to comment Share on other sites More sharing options...
Yalu Posted December 26, 2019 Share Posted December 26, 2019 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. Link to comment Share on other sites More sharing options...
bfarber Posted January 2, 2020 Share Posted January 2, 2020 We have an open report internally to review this feature for an upcoming maintenance release. Link to comment Share on other sites More sharing options...
Yalu Posted February 21, 2020 Share Posted February 21, 2020 (edited) 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 Link to comment Share on other sites More sharing options...
iacas Posted January 14, 2021 Share Posted January 14, 2021 Has this been resolved? I just submitted a support ticket with a getOnlineUsers issue. Link to comment Share on other sites More sharing options...
Recommended Posts