[Forum] Scheduled Task #2 bringing site down

[craigl]

It only lasts for about a minute, but Scheduled Task #2 (or something else that runs every hour at the 20min mark) is overloading my site. We run a fairly powerful server, but we also have a relatively huge database. I already removed the Orphan PM cleanup code and moved it into daily cleanup instead.

Is anyone else running into this, or does anyone else have any ideas on the subject?

[IBxAnders]

If you disable "Thread View" cron task - are you able to confirm that the issue completely goes away?

[craigl]

I just looked back at my original post and realized that I misspoke. Scheduled Task #2 should have been Hourly Cleanup #2. I have "Thread Views" running at x:56 and it is not causing issues.

[Lynne]

I don't really see anything in that script that should cause issues on a normal site. Have you tried moving it to another time to verify that is for sure the task causing the issue?

[craigl]

I will try moving it to a different time and see what happens. About half of the time it causes a bunch of MySQL (too many connections) errors, so it should be fairly easy to identify.

[craigl]

I moved it to a new time and it is doing the same thing at the new time. I will dig around in the PHP file a little more and see if I can isolate it.

[Lynne]

There are several tasks in there. You can try commenting out one at a time and see which one, when commented out, 'fixes' the issue.

[hqarrse]

I have also been having this problem, and have narrowed it down to the attachment cleaning on line 64 - 73 of cleanup2.php. &nbsp;With those lines running a cleanup task will bring my site to a halt and last up to 40 seconds, instead of 2 with them commented.<

Any help appreciated

The resulting query from the slowlog is below, notice the three mentions of "2... all the way up to...". These list all attachment ids. The query is huge and too big to fit into a post:

# Time: 110622 14:02:38
 [email protected]: my_user[my_user] @ [my_ip]
# Time: 110622 14:02:38
# [email protected]: arrse_2010_user[arrse_2010_user] @  [192.168.1.1]
# Query_time: 30.634069  Lock_time: 0.105759 Rows_sent: 25  Rows_examined: 728244
use arrse_2010;
SET timestamp=1308747758;
(
			SELECT
				a.attachmentid, a.contenttypeid, a.displayorder
			FROM vb_attachment AS a
			INNER JOIN vb_filedata AS fd ON (a.filedataid = fd.filedataid)
			LEFT JOIN vb_attachmenttype AS at ON (at.extension = fd.extension)
			LEFT JOIN vb_post AS post ON (post.postid = a.contentid)
LEFT JOIN vb_thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN vb_user AS user ON (a.userid = user.userid)
			WHERE
				a.contenttypeid = 1 AND 
			(
				(
					a.contentid = 0
						AND
					a.userid = 1
				)
					OR
				(
					thread.forumid IN (0, 133, 50, 12, 22, 143, 144, 189, 117, 97, 48, 71, 5, 21, 127, 83, 59, 10, 27, 63, 64, 70, 122, 123, 124, 126, 125, 6, 190, 165, 29, 168, 169, 60, 116, 56, 73, 91, 161, 177, 72, 86, 131, 167, 47, 68, 88, 93, 178, 134, 181, 182, 24, 183, 184, 25, 186, 187, 162, 185, 188, 132, 30, 95, 32, 33, 34, 31, 66, 79, 128, 94, 112, 78, 96, 9, 141, 142, 160, 35, 53, 36, 74, 37, 38, 39, 40, 41, 42, 43, 44, 45, 136, 51, 76, 77, 85, 17, 46, 110, 107, 108, 111, 109, 106, 170, 174, 172, 173, 175, 171, 135, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 130, 58, 87, 163, 55, 67, 28, 18, 26, 158, 115, 114, 14, 179, 52, 54, 101, 118, 84, 90, 119, 137, 191, 139, 62, 140, 138, 19, 49, 57, 103, 15, 23, 180, 92, 104, 13, 82, 81, 75, 65, 61, 80, 89, 98, 99, 100, 113, 3) AND (
				thread.forumid IN (0, 133, 50, 12, 22, 143, 144, 189, 117, 97, 48, 71, 5, 21, 127, 83, 59, 10, 27, 63, 64, 70, 122, 123, 124, 126, 125, 6, 190, 165, 29, 168, 169, 60, 116, 56, 73, 91, 161, 177, 72, 86, 131, 167, 47, 68, 88, 93, 178, 134, 181, 182, 24, 183, 184, 25, 186, 187, 162, 185, 188, 132, 30, 95, 32, 33, 34, 31, 66, 79, 128, 94, 112, 78, 96, 9, 141, 142, 160, 35, 53, 36, 74, 37, 38, 39, 40, 41, 42, 43, 44, 45, 136, 51, 76, 77, 85, 17, 46, 110, 107, 108, 111, 109, 106, 170, 174, 172, 173, 175, 171, 135, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 130, 58, 87, 163, 55, 67, 28, 18, 26, 158, 115, 114, 14, 179, 52, 54, 101, 118, 84, 90, 119, 137, 191, 139, 62, 140, 138, 19, 49, 57, 103, 15, 23, 180, 92, 104, 13, 82, 81, 75, 65, 61, 80, 89, 98, 99, 100, 113, 3)
					OR
				thread.postuserid = 1
			) AND (
				a.state <> 'moderation'
					OR
				a.userid = 1
					OR
				thread.forumid IN (0, 133, 50, 12, 22, 143, 144, 189, 117, 97, 48, 71, 5, 21, 127, 83, 59, 10, 27, 63, 64, 70, 122, 123, 124, 126, 125, 6, 190, 165, 29, 168, 169, 60, 116, 56, 73, 91, 161, 177, 72, 86, 131, 167, 47, 68, 88, 93, 178, 134, 181, 182, 24, 183, 184, 25, 186, 187, 162, 185, 188, 132, 30, 95, 32, 33, 34, 31, 66, 79, 128, 94, 112, 78, 96, 9, 141, 142, 160, 35, 53, 36, 74, 37, 38, 39, 40, 41, 42, 43, 44, 45, 136, 51, 76, 77, 85, 17, 46, 110, 107, 108, 111, 109, 106, 170, 174, 172, 173, 175, 171, 135, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 130, 58, 87, 163, 55, 67, 28, 18, 26, 158, 115, 114, 14, 179, 52, 54, 101, 118, 84, 90, 119, 137, 191, 139, 62, 140, 138, 19, 49, 57, 103, 15, 23, 180, 92, 104, 13, 82, 81, 75, 65, 61, 80, 89, 98, 99, 100, 113, 3)
			) AND (
				(
					post.visible = 1
						AND
					thread.visible = 1
				)
					OR
				(
					thread.forumid IN (0, 133, 50, 12, 22, 143, 144, 189, 117, 97, 48, 71, 5, 21, 127, 83, 59, 10, 27, 63, 64, 70, 122, 123, 124, 126, 125, 6, 190, 165, 29, 168, 169, 60, 116, 56, 73, 91, 161, 177, 72, 86, 131, 167, 47, 68, 88, 93, 178, 134, 181, 182, 24, 183, 184, 25, 186, 187, 162, 185, 188, 132, 30, 95, 32, 33, 34, 31, 66, 79, 128, 94, 112, 78, 96, 9, 141, 142, 160, 35, 53, 36, 74, 37, 38, 39, 40, 41, 42, 43, 44, 45, 136, 51, 76, 77, 85, 17, 46, 110, 107, 108, 111, 109, 106, 170, 174, 172, 173, 175, 171, 135, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 130, 58, 87, 163, 55, 67, 28, 18, 26, 158, 115, 114, 14, 179, 52, 54, 101, 118, 84, 90, 119, 137, 191, 139, 62, 140, 138, 19, 49, 57, 103, 15, 23, 180, 92, 104, 13, 82, 81, 75, 65, 61, 80, 89, 98, 99, 100, 113, 3)
				)
					OR
				(
					thread.forumid IN (0, 133, 50, 12, 22, 143, 144, 189, 117, 97, 48, 71, 5, 21, 127, 83, 59, 10, 27, 63, 64, 70, 122, 123, 124, 126, 125, 6, 190, 165, 29, 168, 169, 60, 116, 56, 73, 91, 161, 177, 72, 86, 131, 167, 47, 68, 88, 93, 178, 134, 181, 182, 24, 183, 184, 25, 186, 187, 162, 185, 188, 132, 30, 95, 32, 33, 34, 31, 66, 79, 128, 94, 112, 78, 96, 9, 141, 142, 160, 35, 53, 36, 74, 37, 38, 39, 40, 41, 42, 43, 44, 45, 136, 51, 76, 77, 85, 17, 46, 110, 107, 108, 111, 109, 106, 170, 174, 172, 173, 175, 171, 135, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 130, 58, 87, 163, 55, 67, 28, 18, 26, 158, 115, 114, 14, 179, 52, 54, 101, 118, 84, 90, 119, 137, 191, 139, 62, 140, 138, 19, 49, 57, 103, 15, 23, 180, 92, 104, 13, 82, 81, 75, 65, 61, 80, 89, 98, 99, 100, 113, 3)
						AND
					post.visible = 2
						AND
					thread.visible = 2
				)
			)
				)
			)
			 AND a.attachmentid IN (2... all the way up to.. 32993)
		) UNION ALL (
			SELECT
				a.attachmentid, a.contenttypeid, a.displayorder
			FROM vb_attachment AS a
			INNER JOIN vb_filedata AS fd ON (a.filedataid = fd.filedataid)
			LEFT JOIN vb_attachmenttype AS at ON (at.extension = fd.extension)
			INNER JOIN vb_socialgroupmember AS sgm ON (sgm.userid = a.userid AND sgm.groupid = a.contentid AND sgm.type = 'member')
INNER JOIN vb_socialgroup AS sg ON (sg.groupid = a.contentid)
LEFT JOIN vb_user AS user ON (a.userid = user.userid)
			WHERE
				a.contenttypeid = 7 AND 
			(
				(
					a.contentid = 0
						AND
					a.userid = 1
				)
					OR
				(
					sg.options & 8 AND (
				a.state <> 'moderation'
					OR
				a.userid = 1
			)
				)
			)
			 AND a.attachmentid IN (2...... all the way up to.... 32993)
		) UNION ALL (
			SELECT
				a.attachmentid, a.contenttypeid, a.displayorder
			FROM vb_attachment AS a
			INNER JOIN vb_filedata AS fd ON (a.filedataid = fd.filedataid)
			LEFT JOIN vb_attachmenttype AS at ON (at.extension = fd.extension)
			LEFT JOIN vb_cms_node AS node ON (a.contentid = node.nodeid)
LEFT JOIN vb_user AS user ON (user.userid = node.userid)
			WHERE
				a.contenttypeid = 18 AND 
			(
				(
					a.contentid = 0
						AND
					a.userid = 1
				)
					OR
				(
					a.contentid <> 0
				)
			)
			 AND a.attachmentid IN (2...... all the way up to.... 32993)
		)
ORDER BY displayorder DESC
LIMIT 0, 25;

[hqarrse]

Are no other big sites getting this problem? If your site is large, say > 30,000 attachments, could you give me some feedback please? I'd like to know if this is a VB issue and I should submit a bug report or if it's something peculiar to my site. Thanks

[hqarrse]

Perhaps the VB staff could comment on whether 45,000 attachments is unusually large and therefore whether issues like this are possible?

[hqarrse]

VB staff? Large forum owners? pleaaasee?

[Wayne Luke]

The attachment statistics from this site are:

Unique / Total Attachments	41,255 / 38,835
Attachment Filesize Sum	1.86 GB
Disk Space Used	1.72 GB
Storage Type	Attachments are currently being stored in the filesystem at
Average Attachment Filesize	47.2 KB
Total Downloads	5,532,256

We don't have this problem here. However it appears that query could use some indexes or reworking to reduce load. I suggest entering it as an issue in JIRA to have the developers look at it. Include as much information as possible including the slowlog entries and server load information.

Also make sure you are storing your attachments in the file system and not the database.

[hqarrse]

Thanks for the reply Wayne.