vb_taggregate_temp_... table grow!

[Ghostt]

we have many links like this as buttons:

forumdisplay.php?f=621&prefixid=example


but prefixes arent tags or? we dont even use tags,

i just want to know if this is normal this query what it does and why it comes so often?..



ps. this is ticket answer from server manager coz overload:

Hello, high load on your mysql server.
Slow querys like this.
Maybe you ca
SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount, IF(thread.views<=thread.replycount, thread.replycount+1, thread.views) AS views
FROM vbthread AS thread
WHERE forumid = 1101
AND sticky = 0
AND visible = 1
ORDER BY sticky DESC, thread.dateline DESC
LIMIT 177880, 20

Maybe you can try to change database engine to INNODB, but we`re not sure.
Thank you for ticket.

but its also possible the load caused by something else and this querys hang up ?

[kh99]

I'm not sure, but if you're sure that the query is the one from tags.php, then I don't see how that would happen unless someone was actually requesting tags.php. You should be able to see that in the web server logs.

Or maybe there is a similar query somewhere and it's not from tags.php? If you don't use tags.php, maybe try removing that file from your server and see what happens (save a copy of course so you can put it back).

[Ghostt]

i allready tested renaming the tags.php. yes maybe this query is also in some other file.
whats the command to check web server query log in detail?
except this one: mysqladmin -u root -p proc stat

maybe its about thread views counter?

/Edit
its also not search.php. tested and renamed this file, query still apear

answer from support:

Hello, yes i already written you about this issue.
I used MySQL Workbench tool to troobleshoot this issue, you can download it from official mysql site.
You can see there list of processes in easy readable format and with current condition.
It stays in condition "sorting result" for several douzens of seconds.
It`s unusual for this sort of querys.
It can be related with MyISAM locks, or incorrect use of indexes by MySQL engine, or lack of required indexes.
Easy way is to make backup, and try change engine for table vbthread to InnoDB.
It can possibly help, or can make things worse.
Next option is by using EXPLAIN predicate troubleshoot indexes usage, and possibly add new indexes. Possibly new index must be for rows sticky, thread.dateline.
Same as previous can possibly help, or can make things worse.
Next way is to set correct indexes with SQL-query if you found incorrect usage on previous step.
You can make it with USE INDEX option in SELECT clause.
Thank you for ticket.
Try to read this articles and links it can help you a lot.
http://dev.mysql.com/doc/refman/5.5/en/select-optimization.html
http://webandphp.com/advanced-mysql-query-tuning
http://stackoverflow.com/questions/14163674/slow-query-state-sorting-result-mysql
http://dba.stackexchange.com/questions/43952/sorting-result-state-taking-very-long-time

[Paul M]

Im not entirely sure what you are asking.

Your mysql user needs full permissions to create and delete TEMPORARY tables.

I have no idea why you are talking about tags, since they dont seem to have anything to do with the original issue.

The query (in post#6) is the cron based thread view counts update, which should be running once per hour.

[Paul M]

Originally Posted by Ghostt

The server load isnt high atm its at 7 avareage load.

On the face of it, 7 looks a high load.
How many CPU's does this server have ?

[Ghostt]

i just try to figure out how to fix this issue i have atm this query running 20 times with
600 query length

server has 8 cores running 2 websites.(wordpress and vbulletin)




.
i just installed mysql workbench and checked this long querys.

they are ALL from the same (sub )FORUmid 1101 only this forum has 750 000 threads.

-- Connection Id: 209656
-- Host: localhost
-- Command: Query
-- Time: 481
-- State: Sorting result
SELECT  thread.threadid,
			thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount, IF(thread.views<=thread.replycount, thread.replycount+1, thread.views) AS views
			
		FROM vbthread AS thread
		
		
		WHERE forumid = 1101
			AND sticky = 0
			
			 AND visible = 1 
					
			
			
		ORDER BY sticky DESC, thread.dateline DESC
		LIMIT 165260, 20

what can i do to prevent this query to process for testing? ive allready deactivated all setting if forum management . like thread views, search indexing, set forum incative, etc..
and i still see this query with forumid 1101

/edit
now i noticed that most slower querys 1-20 length atm are not "Sorting result" .
they are "sending data"

-- Connection Id: 275535
-- Host: localhost
-- Command: Query
-- Time: 8
-- State: Sending data
SELECT  thread.threadid,
			thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount, IF(thread.views<=thread.replycount, thread.replycount+1, thread.views) AS views
			
		FROM vbthread AS thread
		
		
		WHERE forumid = 1101
			AND sticky = 0
			
			 AND visible = 1 
			
			
			
			
		ORDER BY sticky DESC, lastpost DESC
		LIMIT 544080, 20

_____________________________________

i checked debug in forumdisplay with id 1101.

i think this is the query:

SELECT  thread.threadid,
	thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount, IF(thread.views<=thread.replycount, thread.replycount+1, thread.views) AS views
	
FROM vbthread AS thread


WHERE forumid = 1101
	AND sticky = 0
	
	 AND visible IN (0,1,2)
	
	
	
	
ORDER BY sticky DESC, lastpost DESC
LIMIT 0, 20

/edit
also tested without forumdisplay.php shothread.php this query still apear.



this url for example loads very long: forumdisplay.php?f=1101&order=desc&page=101


maybe server managers assumption was right that vbthreads or some else table need innondb because it cant handle that mutch posts.

--------------- Added [DATE]1403468724[/DATE] at [TIME]1403468724[/TIME] ---------------

so paul you also dont know what this query does exectly and how to fix? or at least test somehow to exclude this forum to process this query.

[kh99]

Have you tried disabling the archive? Someone was having a problem like this a few weeks ago and it turned out to be spiders seaching the archive.

[Ghostt]

yes ive allready not activated archive. yes its also possible bots are crawling this pages causing more load. but ive tested allready Disallow: /*f=1101* in robots.txt for 5 minits no effect.

i just tested this httacess deny:

<files forumdisplay.php>
order allow,deny
deny from all
</files>

this query stopped...

how to deny forumdisplay.php?f=1101

for temporary solution

[kh99]

I don't know how to do htaccess, but maybe you could go to Forum Permissions and edit the pemissions for Registered Users and make the forum unreadable.

[Ghostt]

ok its seems like vbulletin cant handle forumdisplay pages in very big forums with so many threads fast.
and search bots also overloading in forumdisplay with forumid 1101
we try innondb today