Post table index optimization / recommendations for large vB 3.8 forum

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • webscience
    New Member
    • Apr 2009
    • 21

    Post table index optimization / recommendations for large vB 3.8 forum

    I have a post table with ~5,000,000 rows. Recently we have started experiencing very odd MySQL slowdowns and lockups, I believe due to row locking issues, possibly related to post table updates and index recalculation. It just started a week ago, all at once, very odd, perhaps related to the KPTI patch, and I'm chasing a javascript angle as well that might be causing users to submit duplicate posts.

    1. Can anyone advise what is the optimal configuration of indexes on the post table for a large VB 3.x forum?
    2. Are importthreadid and importpostid columns required or used for anything? I have an index on importpostid, possibly detritus from a previous upgrade (vB2->vB3?)?
    3. Current specification:

    Code:
    FIELD,         TYPE,                 NULL, KEY, DEFAULT, EXTRA
    postid         int(10)      unsigned NO    PRI  auto_increment
    threadid       int(10)      unsigned NO    MUL  0
    parentid       int(10)      unsigned NO         0
    username       varchar(100)          NO
    userid         int(10)      unsigned NO    MUL  0
    title          varchar(250)          NO    MUL
    dateline       int(10)      unsigned NO    MUL  0
    pagetext       mediumtext            YES
    allowsmilie    smallint(6)           NO         0
    showsignature  smallint(6)           NO         0
    ipaddress      varchar(45)           NO
    iconid         smallint(5)  unsigned NO         0
    visible        smallint(6)           NO         0
    attach         smallint(5)  unsigned NO         0
    infraction     smallint(5)  unsigned NO         0
    reportthreadid int(10)      unsigned NO         0
    importthreadid bigint(20)            NO         0
    importpostid   bigint(20)            NO    MUL  0

    Code:
    TABLE, NON UNIQUE, KEYNAME,           SEQININDEX, COLUMNNAME,  COLLATION, CARDINALITY, INDEXTYPE
    post   0           PRIMARY            1           postid       A          5306892      BTREE
    post   1           userid             1           userid       A          102055       BTREE
    post   1           threadid           1           threadid     A          312170       BTREE
    post   1           threadid           2           userid       A          1768964      BTREE
    post   1           importpost_index   1           importpostid A          1            BTREE
    post   1           dateline           1           dateline     A          5306892      BTREE
    post   1           title              1           title                   1            FULLTEXT
    post   1           title              2           pagetext                1            YES FULLTEXT
    Thank you in advance
    Last edited by webscience; Mon 15 Jan '18, 8:41pm.
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    The import* fields are from ImpEX. Probably from an unfinished import since cleaning the database after the import should remove them. We don't use them for daily operations. The software ships with the indexes that we recommend. The best solution would be to upgrade to MySQL 5.7 and use INNODB tables.
    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...