vb.org Archive - vb_taggregate_temp

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - vb_taggregate_temp_... table grow! (https://vborg.vbsupport.ru/showthread.php?t=311785)

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:

Code:

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 ?

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).

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:

Quote:

Code:

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

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.

Quote:

Originally Posted by Ghostt (Post 2503076)

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 ?

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.

Code:



-- 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"

Code:

-- 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:

Code:

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.

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.

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

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.

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

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (5)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01586 seconds Memory Usage 1,754KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (5)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: