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:
Thank you in advance
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
Comment