How to optimize user table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • yi.zhou@xplusz.com
    Member
    • Feb 2017
    • 31
    • 5.2.x

    How to optimize user table

    Hi, my forum is reaching almost 5m register user which make the user table about 7.5GB. It also cause timeout when search user in admincp, usergroup management page, moderator permission, and it all seems related to user table. What should I do here to improve this.

    Here is some slowquery log


    # Query_time: 199.650164 Lock_time: 0.000047 Rows_sent: 69 Rows_examined: 157
    SET timestamp=1523406231;
    SELECT user.*, usergroup.usergroupid
    FROM usergroup AS usergroup
    INNER JOIN user AS user ON(user.usergroupid = usergroup.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
    WHERE (usergroup.adminpermissions & 1)
    GROUP BY user.userid
    ORDER BY user.username
    /**getSuperGroups**/;


    # Query_time: 130.292878 Lock_time: 0.000030 Rows_sent: 3989532 Rows_examined: 5073500
    SET timestamp=1523406088;
    SELECT `usergroupid`,`membergroupids` FROM user
    WHERE `membergroupids` <> '';

  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    You will have to create an issue report here: http://tracker.vbulletin.com/secure/...91&issuetype=1

    The developers will have to work on the query to make it more efficient. Until then, you would have to increase the timeout or delete inactive users from the site.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • yi.zhou@xplusz.com
      Member
      • Feb 2017
      • 31
      • 5.2.x

      #3
      Will do, thanks

      Comment

      • Wayne Luke
        vBulletin Technical Support Lead
        • Aug 2000
        • 73981

        #4
        What is the output of this query:

        Code:
        SHOW CREATE TABLE user;
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment

        • yi.zhou@xplusz.com
          Member
          • Feb 2017
          • 31
          • 5.2.x

          #5
          Code:
          CREATE TABLE `user` ( `userid` int(10) unsigned NOT NULL AUTO_INCREMENT, `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0', `membergroupids` char(250) NOT NULL DEFAULT '', `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0', `username` varchar(100) NOT NULL DEFAULT '', `token` varchar(255) NOT NULL DEFAULT '', `scheme` varchar(100) NOT NULL DEFAULT '', `secret` varchar(100) NOT NULL DEFAULT '', `passworddate` date NOT NULL DEFAULT '9999-01-01', `email` char(100) NOT NULL DEFAULT '', `styleid` smallint(5) unsigned NOT NULL DEFAULT '0', `parentemail` char(50) NOT NULL DEFAULT '', `homepage` char(100) NOT NULL DEFAULT '', `icq` char(20) NOT NULL DEFAULT '', `aim` char(20) NOT NULL DEFAULT '', `yahoo` char(32) NOT NULL DEFAULT '', `msn` char(100) NOT NULL DEFAULT '', `skype` char(32) NOT NULL DEFAULT '', `google` char(32) NOT NULL DEFAULT '', `status` varchar(1000) NOT NULL DEFAULT '', `showvbcode` smallint(5) unsigned NOT NULL DEFAULT '0', `showbirthday` smallint(5) unsigned NOT NULL DEFAULT '2', `usertitle` char(250) NOT NULL DEFAULT '', `customtitle` smallint(6) NOT NULL DEFAULT '0', `joindate` int(10) unsigned NOT NULL DEFAULT '0', `daysprune` smallint(6) NOT NULL DEFAULT '0', `lastvisit` int(10) unsigned NOT NULL DEFAULT '0', `lastactivity` int(10) unsigned NOT NULL DEFAULT '0', `lastpost` int(10) unsigned NOT NULL DEFAULT '0', `lastpostid` int(10) unsigned NOT NULL DEFAULT '0', `posts` int(10) unsigned NOT NULL DEFAULT '0', `reputation` int(11) NOT NULL DEFAULT '10', `reputationlevelid` int(10) unsigned NOT NULL DEFAULT '1', `timezoneoffset` char(4) NOT NULL DEFAULT '', `pmpopup` smallint(6) NOT NULL DEFAULT '0', `avatarid` smallint(6) NOT NULL DEFAULT '0', `avatarrevision` int(10) unsigned NOT NULL DEFAULT '0', `profilepicrevision` int(10) unsigned NOT NULL DEFAULT '0', `sigpicrevision` int(10) unsigned NOT NULL DEFAULT '0', `options` int(10) unsigned NOT NULL DEFAULT '167788559', `privacy_options` mediumtext, `notification_options` int(10) unsigned NOT NULL DEFAULT '1073741818', `birthday` char(10) NOT NULL DEFAULT '', `birthday_search` date NOT NULL DEFAULT '9999-01-01', `maxposts` smallint(6) NOT NULL DEFAULT '-1', `startofweek` smallint(6) NOT NULL DEFAULT '1', `ipaddress` varchar(45) NOT NULL DEFAULT '', `referrerid` int(10) unsigned NOT NULL DEFAULT '0', `languageid` smallint(5) unsigned NOT NULL DEFAULT '0', `emailstamp` int(10) unsigned NOT NULL DEFAULT '0', `threadedmode` smallint(5) unsigned NOT NULL DEFAULT '0', `autosubscribe` smallint(5) unsigned NOT NULL DEFAULT '0', `emailnotification` smallint(5) unsigned NOT NULL DEFAULT '0', `pmtotal` smallint(5) unsigned NOT NULL DEFAULT '0', `pmunread` smallint(5) unsigned NOT NULL DEFAULT '0', `ipoints` int(10) unsigned NOT NULL DEFAULT '0', `infractions` int(10) unsigned NOT NULL DEFAULT '0', `warnings` int(10) unsigned NOT NULL DEFAULT '0', `infractiongroupids` varchar(255) NOT NULL DEFAULT '', `infractiongroupid` smallint(5) unsigned NOT NULL DEFAULT '0', `adminoptions` int(10) unsigned NOT NULL DEFAULT '0', `profilevisits` int(10) unsigned NOT NULL DEFAULT '0', `friendcount` int(10) unsigned NOT NULL DEFAULT '0', `friendreqcount` int(10) unsigned NOT NULL DEFAULT '0', `vmunreadcount` int(10) unsigned NOT NULL DEFAULT '0', `vmmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0', `socgroupinvitecount` int(10) unsigned NOT NULL DEFAULT '0', `socgroupreqcount` int(10) unsigned NOT NULL DEFAULT '0', `pcunreadcount` int(10) unsigned NOT NULL DEFAULT '0', `pcmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0', `gmmoderatedcount` int(10) unsigned NOT NULL DEFAULT '0', `assetposthash` varchar(32) NOT NULL DEFAULT '', `fbuserid` varchar(255) NOT NULL DEFAULT '', `fbjoindate` int(10) unsigned NOT NULL DEFAULT '0', `fbname` varchar(255) NOT NULL DEFAULT '', `logintype` enum('vb','fb') NOT NULL DEFAULT 'vb', `fbaccesstoken` varchar(255) NOT NULL DEFAULT '', `account_id` varchar(100) DEFAULT NULL, PRIMARY KEY (`userid`), KEY `usergroupid` (`usergroupid`), KEY `username` (`username`), KEY `email` (`email`), KEY `birthday` (`birthday`,`showbirthday`), KEY `birthday_search` (`birthday_search`), KEY `referrerid` (`referrerid`), KEY `fbuserid` (`fbuserid`(191)), KEY `joindate` (`joindate`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
          I only added one new field account_id and it store around 20 char for each user.

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73981

            #6
            Thanks. Another question from our lead developer...

            Do you use the Secondary Usergroup system a lot?

            The way this is stored currently can cause a lot of performance issues. We basically store it as a list of comma separated values. If you do use secondary groups, we will most likely need a copy of your database setup for testing and performance purposes. The developers can create a database with a lot of users but if we don't have all the nuances, it will be difficult to recreate the issue.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API

            Comment

            widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
            Working...