[Forum] Slow query

[Rob Musquetier]

Since we are using VB 4.1.9 (after upgrading from VB 3.6.x) we are experiencing locking problems on our forums which seems to be caused by an attachment related query running for 280+ seconds each time it is called. This results in our users being hanged up for minutes several times each hour. The query consists of three sub-queries united into one.

The middle query seems to be the pitfall taking 98% of the processing time (determined by splitting up the query and running them independently). This part seems to collect attachment information from the, by the user accessible visible forums posts (via the visible threads) which are not under moderation and have a contentid = 0 and a contenttypeid = 1. The generated query seems to be very efficient put together (with a lots of OR's filtering the same sub-set of forums).

Questions:
1) which action is causing these types of queries to be generated (I can't find it in any php file because it seems to be dynamically build up)?

2) is there a solution to resolve this lengthy run-time (converting tables to InnoDB, adding indexes, applying a VB patch, others???)

Troublesome query:
(SELECT a.attachmentid, a.contenttypeid, a.displayorder, a.counter
FROM vBattachment AS a
INNER JOIN vBfiledata AS fd ON ( a.filedataid = fd.filedataid )
LEFT JOIN vBattachmenttype AS at ON ( at.extension = fd.extension )
INNER JOIN vBalbum AS album ON ( album.albumid = a.contentid )
LEFT JOIN vBprofileblockprivacy AS pbp ON ( pbp.userid = a.userid
AND pbp.blockid = 'albums' )
LEFT JOIN vBuser AS user ON ( a.userid = user.userid )
WHERE a.contenttypeid =8
AND (
(
a.contentid =0
AND a.userid =33
)
OR (
a.contentid <>0
)
)
AND a.contentid <>0
)
UNION ALL (


SELECT a.attachmentid, a.contenttypeid, a.displayorder, a.counter
FROM vBattachment AS a
INNER JOIN vBfiledata AS fd ON ( a.filedataid = fd.filedataid )
LEFT JOIN vBattachmenttype AS at ON ( at.extension = fd.extension )
LEFT JOIN vBpost AS post ON ( post.postid = a.contentid )
LEFT JOIN vBthread AS thread ON ( post.threadid = thread.threadid )
LEFT JOIN vBuser AS user ON ( a.userid = user.userid )
WHERE a.contenttypeid =1
AND (
(
a.contentid =0
AND a.userid =33
)
OR (
thread.forumid
IN ( 0, 1, 64, 65, 11, 9, 10, 14, 13, 75, 62, 63, 44, 71, 72, 73, 74 )
AND (
thread.forumid
IN ( 0, 1, 64, 65, 11, 9, 10, 14, 13, 75, 62, 63, 44, 71, 72, 73, 74 )
OR thread.postuserid =33
)
AND (
a.state <> 'moderation'
OR a.userid =33
OR thread.forumid
IN ( 0, 1, 64, 65, 11, 9, 10, 14, 13, 75, 62, 63, 44, 71, 72, 73, 74 )
)
AND (
(
post.visible =1
AND thread.visible =1
)
OR (
thread.forumid
IN ( 0, 1, 64, 65, 11, 9, 10, 14, 13, 75, 62, 63, 44, 71, 72, 73, 74 )
)
OR (
thread.forumid
IN ( 0, 1, 64, 65, 11, 9, 10, 14, 13, 75, 62, 63, 44, 71, 72, 73, 74 )
AND post.visible =2
AND thread.visible =2
)
)
)
)
AND a.contentid <>0
)
UNION ALL (


SELECT a.attachmentid, a.contenttypeid, a.displayorder, a.counter
FROM vBattachment AS a
INNER JOIN vBfiledata AS fd ON ( a.filedataid = fd.filedataid )
LEFT JOIN vBattachmenttype AS at ON ( at.extension = fd.extension )
INNER JOIN vBsocialgroupmember AS sgm ON ( sgm.userid = a.userid
AND sgm.groupid = a.contentid
AND sgm.type = 'member' )
INNER JOIN vBsocialgroup AS sg ON ( sg.groupid = a.contentid )
LEFT JOIN vBuser AS user ON ( a.userid = user.userid )
WHERE a.contenttypeid =7
AND (
(
a.contentid =0
AND a.userid =33
)
OR (
sg.options &8
)
)
AND a.contentid <>0
)
ORDER BY counter DESC
LIMIT 0 , 5

[BirdOPrey5]

1) Are your attachments stored in the database or the file system? If they are in the database I would suggest trying to move them to the file system. (Admin CP -> Attachments -> Attachment Storage Type)'

2) Attempt to optimize the table: vBattachment

[Rob Musquetier]

Hi,

Thanks for taking the time to reply!

1) Attachments are in the database but we rather keep them there to make the backups consistent.

2) All involved tables were and are already optimized.

I have converted all involved tables from IsamDB to InnoDB which at least made the system hangs (due to the table locking versus row locking) go away and therefor made my user community happy again; but these queries are still very lengthy and, in my opinion, should be revisited in a next release...

[BirdOPrey5]

It's likely that the table is just so large due to attachments being stored in the database- keeping a large amount of attachments in the database is not a sound strategy going forward, it will just cause problems. I understand it is easier to make 1 backup but eventually you will have to separate them and do a separate attachment backup. (Or dis-allow users to attach new files.)

[Rob Musquetier]

It's likely that the table is just so large due to attachments being stored in the database- keeping a large amount of attachments in the database is not a sound strategy going forward, it will just cause problems. I understand it is easier to make 1 backup but eventually you will have to separate them and do a separate attachment backup. (Or dis-allow users to attach new files.)

In my opinion that is why indexes were invented. IF queries would use the existing indexes you bypass the content of the table completely and it wouldn't matter how large the attachments would be... So this answer makes no sense.

[AbdelrahmanSalem]

i think it's not matter whether it is stored in DB nor File system , this issue happens due moderation of big forums which require the approval for all content types ! , if there is any straight forward solution for this that would be awesome , i got into this problem today , and i'm using vbulletin 4.1.3

[PyroChixRock]

I'm on 4.2.0 and having this same error, VBIV-15450. Vb staff advised me to vote on the issue, please do the same if you are having this error so we can get a fix.

In the mean time, anyone found a proper work around? Striping the permissions on this file disabled the query - /forum/packages/vbattach/attach.php - but leaves the forum nonfunctional.

My files are in the file system not the database which is the only suggestion I've found for a possible fix.

Thanks for any help.

[PyroChixRock]

Update: Changing the affected tables from MyISAM > InnoDB resolved the hanging/lag.




tables changed were as follows:
attachment
attachmentcategory
attachmentcategoryuser
attachmentpermission
attachmenttype
attachmentviews