Backend extremely slow on "large" sites due to User Status backend module

[ZoccoloCD]

Steps to reproduce the issue

Try browsing through the backend of a site that has lots of members/users that are logged in.

Expected result

Normal backend speed.

Actual result

Extremely slow backend speed.

System information (as much as possible)

Various systems with various hosts, php 5.6 to 7.1 with newest joomla versions.

Additional comments

I've done debugging on the sites and in some cases the user stats backend module takes 30.000 milliseconds to load, making the backend unworkable. Disabling the module solves this.
I couldn't find this issue in the tracker yet eventhough many of our clients were running into this issue. Luckily a workaround for now works by disabling the modules, but hope this can be fixed.

[Kubik-Rubik]

@ZoccoloCD Can you please open the database and check the sessions table? How many entries do you have there?

[ZoccoloCD]

I'm checking 1 of the sites now; 1 session in the session table currently, but the user status bar shows 2050 logged in users.

[ZoccoloCD]

@Kubik-Rubik seems like the mod_status.php was changed quite a bit earlier and takes a lot of time to query the session tables. If however you were to put an index on the guest column, the query wouldn't take that long.

So this did it for me:

ALTER TABLE #_session ADD INDEX(guest);

[nickdring]

Hi, I have had the same issue on a large site. I've been trying to figure out the reason for days when I came across this page. Disabling the user stats plugin brought the site back to normal.

[ghost]

@nickdring can you please test Pull Request #19708?

[csthomas]

IMO we have tow options:

1. Add index to guest column, as mentioned above, and slow down insert query a little.
2. Change WHERE statement from guest = 0 to userid > 0. Column userid is indexed.
   The issue, B/C break may be at 3rd party extensions that use negative user id, ex Joomdle?

[GCLW]

Are you absolutely positive your Joomla session db table is empty?
This has all the symptoms of the problem that was created by a 3.8.4 update to how sessions are (or now not) deleted from that table.
When logging into the administrator area, do you take notice it takes a much longer time now?
This sounds like your DB table has a few hundred thousand records in it (as the amount of logged in users is showing). A bloated session table most likely is the cause.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/19750.}

[ZoccoloCD]

@GCLW When Kubik-Rubik asked me there was only 1 active session, now I see hundreds of thousands in the same database. But the site is running on 3.8.4.

Do I understand correctly that this problem (sessions are not deleted) was introduced in 3.8.4 but is still existent in 3.8.5?
And is it fixed in the 3.8.6 RC/Stable?

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/19750.}

[GCLW]

Correct, a change in 3.8.4 causes the runaway session table. It was not reverted for 3.8.5, and I do not know if it will be fixed with 3.8.6. I have to run nightly cron jobs on all but one of my sites to clean the session table.

[Quy]

Yes, it will be fixed in v3.8.6 per #19687.

[ghost]

@Quy can this Issue be closed?

[infograf768]

I guess so as 3.8.6 (to be released on the 13th) solves the issue.

[nickdring]

I can put the release on a staging or dev server but I cannot deploy to production so this isn't much use to you I fear as we don't have user connected to those environments.

[joomla-cms-bot]

Set to "closed" on behalf of @Quy by The JTracker Application at issues.joomla.org/joomla-cms/19750

[Quy]

Closing as this will be fixed in v3.8.6 and there is a discussion on this in Joomla Forums.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/19750.}

[nickdring]

This hasn't been resolved with 3.8.6

[brianteeman]

@nickdring did you clean up the session table?

[nickdring]

Hi @brianteeman No I hadn't. I've done it and reenabled the plugin and for now all seems quite speedy.

[brianteeman]

:)

[csthomas]

@nickdring You can test #19708, which should speed up operations such as adding new or updating the session metadata.

It is difficult to see any improvements on small site, but it speeds up queries to the database with high traffic.