"Who's Online" suddenly started creating HUGE DB Load

[RyanD789]

Today, around noon, the who's online feature began causing massive db load (please read this post carefully before simply suggesting to leave it off, or turn it off for better performance).

The DB server is MySQL 5, and is running on Gentoo 2006.1 with a 2.6.19.1 kernel, 16 opterons(yes, 16 cores), 32gb of RAM, and a fiberchannel attached disk array (so I can say for sure, it isn't a problem with "skimpy hardware").

The vBulletin is 3.6.3 with security patches up to 3.6.5.

Whenever the "Display Logged in Users?" option is turned on, the load on the database server (this behavior started only today) skyrockets from a comfortable 3-4 (it's normal load average) to upwards of 40-60, and mysql begins using 1500% cpu.

The queries which seem to be taking the longest:

EXPLAIN SELECT user.username, (user.options & 512) AS invisible, user.usergroupid, session.userid, session.inforum, session.lastactivity, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid FROM session AS session LEFT JOIN user AS user ON(user.userid = session.userid) WHERE session.lastactivity > 1175803046:

*** row 1 ***
          table:  session
           type:  ALL
  possible_keys:  lastactivity
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  4220
          Extra:  Using where
*** row 2 ***
          table:  user
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mdbb.session.userid
           rows:  1
          Extra:  NULL

Describes on the session and user tables:

mysql> describe session;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| sessionhash   | varchar(32)          | NO   | PRI |         |       |
| userid        | int(10) unsigned     | NO   | MUL | 0       |       |
| host          | varchar(15)          | NO   | MUL |         |       |
| useragent     | varchar(100)         | NO   |     |         |       |
| lastactivity  | int(10) unsigned     | NO   | MUL | 0       |       |
| location      | varchar(255)         | NO   |     |         |       |
| styleid       | smallint(5) unsigned | NO   |     | 0       |       |
| althash       | varchar(32)          | NO   |     |         |       |
| badlocation   | smallint(5) unsigned | NO   |     | 0       |       |
| inforum       | smallint(5) unsigned | NO   |     | 0       |       |
| inthread      | int(10) unsigned     | NO   |     | 0       |       |
| incalendar    | int(10) unsigned     | NO   |     | 0       |       |
| loggedin      | smallint(5) unsigned | NO   |     | 0       |       |
| idhash        | varchar(32)          | NO   | MUL |         |       |
| bypass        | smallint(6)          | NO   |     | 0       |       |
| languageid    | smallint(5) unsigned | NO   |     | 0       |       |
| profileupdate | smallint(5) unsigned | NO   |     | 0       |       |
+---------------+----------------------+------+-----+---------+-------+
17 rows in set (0.12 sec)

mysql> describe user;
+--------------------+----------------------+------+-----+------------+----------------+
| Field              | Type                 | Null | Key | Default    | Extra          |
+--------------------+----------------------+------+-----+------------+----------------+
| userid             | int(10) unsigned     | NO   | PRI | NULL       | auto_increment |
| usergroupid        | smallint(5) unsigned | NO   | MUL | 0          |                |
| username           | varchar(100)         | NO   | MUL |            |                |
| password           | varchar(32)          | NO   |     |            |                |
| email              | varchar(100)         | NO   |     |            |                |
| styleid            | smallint(5) unsigned | NO   |     | 0          |                |
| parentemail        | varchar(50)          | NO   |     |            |                |
| homepage           | varchar(100)         | NO   |     |            |                |
| icq                | varchar(20)          | NO   |     |            |                |
| aim                | varchar(20)          | NO   |     |            |                |
| yahoo              | varchar(32)          | NO   |     |            |                |
| usertitle          | varchar(250)         | NO   |     |            |                |
| customtitle        | smallint(6)          | NO   |     | 0          |                |
| joindate           | int(10) unsigned     | NO   |     | 0          |                |
| daysprune          | smallint(6)          | NO   |     | 0          |                |
| lastvisit          | int(10) unsigned     | NO   |     | 0          |                |
| lastactivity       | int(10) unsigned     | NO   |     | 0          |                |
| lastpost           | int(10) unsigned     | NO   |     | 0          |                |
| posts              | int(10) unsigned     | NO   |     | 0          |                |
| timezoneoffset     | varchar(4)           | NO   |     |            |                |
| pmpopup            | smallint(6)          | NO   |     | 0          |                |
| avatarid           | smallint(6)          | NO   |     | 0          |                |
| options            | int(10) unsigned     | NO   | MUL | 0          |                |
| birthday           | varchar(10)          | NO   | MUL | 0000-00-00 |                |
| maxposts           | smallint(6)          | NO   |     | -1         |                |
| startofweek        | smallint(6)          | NO   |     | 1          |                |
| ipaddress          | varchar(15)          | NO   | MUL |            |                |
| referrerid         | int(10) unsigned     | NO   |     | 0          |                |
| pmtotal            | smallint(5) unsigned | NO   |     | 0          |                |
| pmunread           | smallint(5) unsigned | NO   |     | 0          |                |
| passworddate       | date                 | NO   | MUL | 0000-00-00 |                |
| salt               | char(3)              | NO   |     |            |                |
| showvbcode         | smallint(5) unsigned | NO   |     | 0          |                |
| msn                | varchar(100)         | NO   |     |            |                |
| avatarrevision     | int(10) unsigned     | NO   |     | 0          |                |
| membergroupids     | varchar(250)         | NO   |     |            |                |
| displaygroupid     | smallint(5) unsigned | NO   | MUL | 0          |                |
| reputation         | int(11)              | NO   |     | 10         |                |
| reputationlevelid  | int(10) unsigned     | NO   | MUL | 1          |                |
| languageid         | smallint(5) unsigned | NO   | MUL | 0          |                |
| threadedmode       | smallint(5) unsigned | NO   |     | 0          |                |
| emailstamp         | int(10) unsigned     | NO   |     | 0          |                |
| autosubscribe      | smallint(6)          | NO   |     | -1         |                |
| birthday_search    | date                 | NO   | MUL | 0000-00-00 |                |
| profilepicrevision | int(10) unsigned     | NO   |     | 0          |                |
| showbirthday       | smallint(5) unsigned | NO   |     | 2          |                |
| skype              | varchar(32)          | NO   |     |            |                |
| adminoptions       | int(10) unsigned     | NO   |     | 0          |                |
| lastpostid         | int(10) unsigned     | NO   |     | 0          |                |
| sigpicrevision     | int(10) unsigned     | NO   |     | 0          |                |
| ipoints            | int(10) unsigned     | NO   |     | 0          |                |
| infractions        | int(10) unsigned     | NO   |     | 0          |                |
| warnings           | int(10) unsigned     | NO   |     | 0          |                |
| infractiongroupids | varchar(255)         | NO   |     |            |                |
| infractiongroupid  | smallint(5) unsigned | NO   | MUL | 0          |                |
+--------------------+----------------------+------+-----+------------+----------------+
55 rows in set (0.00 sec)

We have had this feature on for basically since the vbulletin was installed, and only today ran into issues. (No changes were preceeding, such as to indexes, php, mysql, variables, software, etc)


ANY help is appreciated, and this is a must-have feature, since we use this data for many reasons.

Thanks.

[peterska2]

Are you resolving IP addresses in WOL? If you are, turn this off. You can still see the host name by clicking the IP address if this is required at any point.

If not, change the refresh rate to have longer between refreshes.

Finally, turn WOL off for non-staff (ie not admins or moderators) via the usergroup permissions.

[RyanD789]

Thanks you for the suggestions, however, those settings only seemed to make a difference to the "Who's Online" feature that we actually have turned off, rather than the "display logged in users" which shows who is logged in, and how many users are currently on the forum.

Anything else that I might try?

Thanks.

[peterska2]

How many users do you have online at any one time? Are you showing the information alphbetically or randomly? To guests or just to members?

[RyanD789]

How many users do you have online at any one time? Are you showing the information alphbetically or randomly? To guests or just to members?

Right now, we have an average of between 2500-3500 users on concurrently. (having less today that yesterday actually, yet it became a problem today).

If I show the information to just members (randomly) the load is fine, but there are only about 1/3 of the actual users logged in(and this isn't an acceptable option, we need it to be viewable as guest).

If I show the information to everyone (randomly), load instantly skyrockets, and the mysql process uses 1500% cpu.

It was set up (yesterday, when it was working correctly) as Show to All, Randomly sorted.

Thanks.

[peterska2]

With that number of users logged in at once you shouldn't have it displayed. The display of the online users uses a lot of resources, so while this is fine for a couple of hundred users, once you start getting to 1000+ concurrent users then you should disable this display. If you need to know who is online, then you can view the information in the WOL page which is accessible via a usergroup permission.

[RyanD789]

As I said, it was working fine yesterday, and then suddenly not today. I had even fewer users on today than yesterday, and yet it's been a problem all day today.

Turning it off just isn't an option, we'd also like our users to see how many users are online, it encourages users to join/post.

Any other options?

Thanks.