vb_taggregate_temp_... table grow!

[Ghostt]

i checked my mysql db, its still has over 70 temp tables like this:

vb_taggregate_temp_1400969880

this temp tables are not deleted automatically. sever manager says its because of cron job, but which one?

[Zachery]

You can just delete them, make sure your MySQL user has full permissions to the database.

[Ghostt]

thisproblem is allready long time. i dont want delete them everytime manuelly.
i need a solution.

[Lynne]

The solution is to make sure your MySQL user has been granted ALL permissions to the database. If they don't have all permissions, like delete, then you will see the problem you are seeing.

[Ghostt]

thanks ive told this to the server manager.
also found here similar idea: http://www.cutehits.com/2011/12/why-vbulletin-is-automatically-creating-tables-like-aaggregate_temp_-or-aaggregate_temp_/

i ll report if its fixed.


---
what does this query mean, its all the time running.:
SELECT thread.threadid, thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount

[Ghostt]

They dont fixed it... and i got 50 more of this tables:
vb_taggregate_temp_1401475080
what does this table create?

we dont even use TAGs for posts.


/edit maybe the solution. lets see after threadviews table repair if its ok.
http://www.vbulletin.com/forum/forum/vbulletin-legacy-versions-products/legacy-vbulletin-versions/vbulletin-3-7-questions-problems-and-troubleshooting/293862-taggregate-temp-tables-being-created


--------------- Added 1401738524 at 1401738524 ---------------

ive set the cron for update THREAD HITS to every 10 min maybe this causing it.


it could be caused due overload timeout sometimes... ive tested run cron manuelly and sometimes this error:

Database error in vBulletin 3.8.4:

Invalid SQL:
UPDATE vbthread AS thread,vbtaggregate_temp_1401739680 AS aggregate
SET thread.views = thread.views + aggregate.views
WHERE thread.threadid = aggregate.threadid;

MySQL Error : Lock wait timeout exceeded; try restarting transaction
Error Number : 1205
Script : /admincp/cronadmin.php?do=runcron&cronid=2
Referrer : /admincp/cronadmin.php?do=modify
Classname : vB_Database
MySQL Version : 5.5.34

[Ghostt]

this "sorting result" query.
why no one can explain why this query often slow and apear all the time?

| Query | 1 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |

-i found this line in /tags.php

-ive set to update thread views every 20 min with cron.
sometimes this cron get timeout error tested to run manuelly .

also i used googled very long and dont find any result.

i think everytime this task crashes it created a temp table...
and this all also the query are connected.

[kh99]

How busy is your forum and how many posts does it have? I'm not an expert on these things but I'm curious because I'm wondering if there is some problem or if it could just be that the database is too busy.

[Ghostt]

The server load isnt high atm its at 7 avareage load.
and it loocks like this

| 40481 | db_example | localhost | db_example | Query | 8 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |
| 40489 | db_example | localhost | db_example | Query | 8 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |
| 40537 | db_example | localhost | db_example | Query | 5 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |
| 40555 | db_example | localhost | db_example | Query | 4 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |
| 40610 | db_example | localhost | db_example | Query | 0 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |
| 40621 | root | localhost | | Query | 0 | | show processlist

if server is overloaded this query goes up to 200

[kh99]

this "sorting result" query.
why no one can explain why this query often slow and apear all the time?

| Query | 1 | Sorting result | SELECT thread.threadid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.replycount |

-i found this line in /tags.php

That looks like it might be a search for threads with a given tag. I don't know why that would happen often. I suppose you could check your web server logs and see if someone is actually doing a search, or if it's a spider or something.

[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]

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 1403468724 at 1403468724 ---------------

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

[Simon Lloyd]

a lot of bots dont recognise robots.txt but follow some of the advice here http://antezeta.com/news/avoid-search-engine-indexing. For .htaccess use thisRewriteEngine On

RewriteCond %{HTTP_USER_AGENT} (googlebot|bingbot|Baiduspider|yandex|anywordyouli ke|like|bing) [NC]
RewriteRule .* - [R=403,L]
just keep adding names to the string or use my mod for banning bots.

Your .htaccess file must reside in the forum root for this (you might have to set your control panel to view hidden files if you dont see it)

[Ghostt]

1. i dont want block all useragent especially not google....

i need to block them from only 1101 forumdisplay what ive done with this robot.txt line (hope this is correct):
Disallow: /*forumdisplay.php?f=1101&order=desc&page=*

but this is the emergency solution because its not a real fix of the bad performance of very big forumdisplay pages.
and how i see no one can realy help here .

and i allready use your ban spider addon. thanks for it, but we have sometimes problem with it.
if server crashes the cloudflare.com 502 error page is shown, after user refresh page. user get blocked/redirected from this addon.
you know why?

[Simon Lloyd]

It will be to do with cloudflare caching i would imagine as my mod doesn't store any user/visitor details. As for your search engines try this, put this in your header template (or even forumdisplay but i think it must always use the header template)<if condition="in_array($forumid, array(X,Y,Z)) AND "$show['search_engine']">
<meta HTTP-EQUIV="REFRESH" content="0; url=http://www.mysite.com">
</if>Change x,y,z to be whichever forumid's you want to protect, change mysite.com to any url you want to redirect the spiders to and you should be golden :)

[Ghostt]

just tested htaccess code on the wordpress site on same server. connections/sec dropped from 200 to 10
with blocking this bastards: Baiduspider|yandex|anywordyoulike|like

so my robot.txt code not allways work you say?
seems to. but i will test your code tomorrow and check querys. thanks,

[Simon Lloyd]

I put these in anywordyoulike|like to show you can include anything in the string, they're not actual bots ;) Robots.txt is only obeyed by good legitimate (but maybe unwanted) bots, for the likes of Baidu...etc you can actually get in contact with them and ask them to stop indexing your site.

[Ghostt]

so baidu ignore robots.txt?

[Simon Lloyd]

It seems that way as do many others like AhrefsBot, sosospider, Aboundex and even Bing to name but a few!

--------------- Added 1403918146 at 1403918146 ---------------

For a more complete .htaccess block look here http://wpsecure.net/bad-bot-list/

[Ghostt]

i test it . SetEnvIfNoCase or rewrite Rules better?
both codes from the link dont work i think coz i see in cloudflare baidu still crawling.... first rewrite gives an error.
can i use this bots listed there for your write here?
or any better list. in your plugin ive a very big list but i think it will not work with that list because there are not complete spider names

RewriteCond %{HTTP_USER_AGENT} (googlebot|bingbot|Baiduspider|yandex|bing) [NC]
RewriteRule .* - [R=403,L]