Query locking tables and stacking queries - need help understanding

[starl]

Our forum has been lagging a lot at night, even crashing. We've done a lot of hardware changes to manage it, but it's not enough. After this morning's lag, server support told me:

queries had locked tables in the DB, causing the queries after them to stack up until the limit was hit. The query causing the lock at the time of the logging snapshot was showing as follows :

----------------------------------------------------------------

3669084 Query SELECT threadid, title, prefixid, lastpost, replycount
FROM vb_thread AS thread
WHERE forumid = 10
AND visible = 1
AND open <> 10

ORDER BY dateline DESC
LIMIT 182750,250
________________
I get the gist of it: this one query was locking a table that other queries needed, so those queries queued up until the table was unlocked. What I don't get - What is this query? Is this one of the scheduled tasks?

Our forum database is very large. Server support suggests archiving posts, but we dont' want to make posts unsearchabel/unfindable...

note: while our forum has been around a long time, I never was involved in the guts at this level. I am trying to learn and come up to speed as quickly as possible.

thanks

[kh99]

I did a search of the code for that query, and it looks like it's from archive/index.php. It also looks like you must have hundreds of thousands of threads in one forum, which is what I believe is causing the problem. The query is doing an "order by dateline" and there's an index on dateline for the thread table, which would be good, except that there's no index by the open or visible columns which I think means that all rows have to be read in order to find the right 250 rows to return. In short, I think it's just not optimized for having that many threads in a forum.

But if your forum is like ours (not vbulletin.org, but the one I help run), then you probably have spiders constantly crawling the entire site. So maybe a solution would be to try the "Ban Spiders by User Agent" mod to try to eliminate the unwanted spiders.

[starl]

Thanks Kevin. Yes, we definitely have a forum with that many threads..

I ask you to bear with me while I'm learning this stuff.. so I may ask bad questions..

archive/index.php.. sounds like somethat's archiving? If that's the case, why would it archive old threads and not just the newer? Or is it, but it's sorting by date first?

I don't understand " there's no index by the open or visible columns"

Yes, we have lots of spiders.. Baidu has been a royal pita. I've updated the robots.txt, but out of 200 spiders - they're 90%. Course, I know that the users online isn't up to the second, but still?

Thanks for referring a product, but we avoid 3rd party additions when possible. Is there another fix to this?

Any idea what makes the query run? With your reference to spiders, it sounds like they may be responsible?

thanks again!

[kh99]

Originally Posted by starl

archive/index.php.. sounds like somethat's archiving? If that's the case, why would it archive old threads and not just the newer? Or is it, but it's sorting by date first?

I have to admit that I don't understand the archive. I have a test site that I simply installed in the default way, and it has an "archive" link at the bottom, and when I click it it shows the forums but in a simplified format. I don't know if that means it's just another way to display the forum, or if it's automatically being "archived". I'm sure someone else will know.

I don't understand " there's no index by the open or visible columns"

I should point out that I'm not an sql expert or anything, but when you have a large database, the speed of a given query depends a lot on what the query is doing and what indexes you have defined for a table (in the case of vbulletin, they've been defined ahead of time by the programmers, so I don't really mean "you"). All that crap i wrote was just saying that I think that particular query is slow because there's no index it can use to avoid reading a lot of rows, and you have a lot of threads.

Any idea what makes the query run?

I believe it just runs whenever anyone goes to the archive folder (if you can't find a link at the bottom of your home page, try just going to archive/index.php). In particular it looks like that query is from someone requesting page 731 or so from the list of threads in a forum (with 250 threads per page).

With your reference to spiders, it sounds like they may be responsible?

I just know that we constantly have spiders crawling the archive on our site, and I don't think humans are usually interested in the archive. I don't know of any other way to stop them, but I'm not an expert on that either.


Re-reading the above, I realize that I'm not an expert in much at all, so I'm not sure I've done anything other than producing more questions. But other people here will know more, and you could also ask on vbulletin.com, if you haven't already.

[starl]

Thanks for the feedback, Kevin. Any information is useful.

[cellarius]

The archive does not archive anything, it just was supposed to be a lightweight version of the forum for serch spiders. That was useful a long time ago, nowadays its just not needed anymore. Don't bother with that query, just turn it off in AdminCP.

[tbworld]

Originally Posted by cellarius

The archive does not archive anything, it just was supposed to be a lightweight version of the forum for serch spiders. That was useful a long time ago, nowadays its just not needed anymore. Don't bother with that query, just turn it off in AdminCP.

Thanks Cellarius, I looked at vb's archive feature when I first installed vb, and said 'cool' vbulletin archives old posts. After looking over the code I realized I had no use for it, but I never understood what it was really for or why it was implemented in the first place. I suppose I should have asked.

If you do not mind me asking, how did it help the search engines?

[cellarius]

By presenting the content in a very accessible way, with much less "clutter" (table-layout, menus and stuff) than the real thing. No need for that anymore in 2014.

[starl]

I really appreciate all the feedback here - I have a LOT to learn.

Interestingly - couple nights ago, we had no problems. Last night, we had lockups again.... same query, so we're looking at shutting it down.

Owner has a question though - and I'm hoping someone here can answer it - When I turn off the query (still gotta figure out how, hoping it's an easy control in the Admin Panel) - what happens to the pages that have already been generated? Will they remain? And will the "archive" link (at the bottom of our forum) remain or is it all tied together?

thanks again everyone for their patience.

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

Well.. I answered my own question. I turned off the option (Admin Panel ->options->Search Engine Friendly archive). The link went away, the address doesn't work and old links don't work.
Course, I don't know if the old table is still there and if I turn it back on if everything will be back. Or if it will restart the query .. which I do NOT want to do.

[Paul M]

You could add an index that will help that query ;

ALTER TABLE vb_thread
ADD INDEX forumid_dateline (forumid, dateline);

It may take a while to build the index, but once it has, that query will be happier.