Queries clogging MySQL
 

Here are the queries that they said were running when our MySQL load goes to 100%. I can't really tell what is causing the issue because I don't have a clue what all that means, but it is something that isn't good im sure. Any help would be appreciated.

this shit is driving me insane. i just want it to run smoothly yet i have queries causing a lock up and i cant tell what. im really getting frustrated by all this.

Do a search in your files for parts of those queries. I assume you have a copy of the full query, so it will be easier for you to search for them and locate the correct ones. But, usually it isn't those queries that are the problem - it's the query performed right before them that locked the tables that is the problem. Check the times on these and see if they show up at the same time - perhaps a cron job/scheduled task is being run that is causing the problem.

im not sure which files you mean i should do searches in? and is there a good program to do a search in multiple files? i checked my raw access logs and basically i get these

constantly. i get error logs filled with forum/transparent file does not exist. it isnt in any my templates so now im going to check files but there has to be a way to check them faster than one at a time.

and as for the queries, i only have the information they give me. i dont really have access to a lot of stuff (im a semi dedicated). that was just what the techs send me whenever i messaged support.

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

sigh. think this is something i could hire someone through the paid services to do? this is just so frustrating. we dont have a high traffic board, but ive invested a lot of time trying to make it run properly. i know im limited at what i can do with a semi dedicated, but there has to be something. maybe a bad table in the database (i used repair and analyze and got nothing)? maybe an old mod gone bad (but ive tried to make sure i remove all lines of code that i changed when i uninstall a mod)

I use BBEdit to search all files in a folder. That is for a Mac. I've sure there are similar applications for the PC.

And what I was talking about was finding the query so you can figure out what is happening when it's called. You would search for part of the query like "IF(visible = 2, 1, 0) AS isdeleted" (I think that one is from functions.php getting threadinfo). But, as I said, I don't think that will help because those are the queries having problems *because* the table is locked. You need to find out *why* the table is locked and whatever is doing that is doing it right before those queries. So, you should see if you can get the times of the problem queries and see if it is always happening at the same time and then figure out what is going on on your forum at those times.

As for /transparent, I would guess it is a modification causing that problem because it isn't a default vbulletin thing. So, you need to look through your plugins for that one (although I think I've seen it mentioned before, so perhaps a search here and reading a few threads will tell you what modification it is).

Well I went through the scheduled tasks in my admin panel and the only task that was set for more than twice an hour was an RSS deal. I disabled that since I don't have any RSS stuff currently anyways, but it is occurring right now about once every 5 minutes or so. It will do this for around an hour usually (sometimes longer) and then it will run smoothly for a few hours. Sometimes it won't do it for days and a few times even weeks and then sometimes its every other hour.

How much access to the server do you have? If you are able to change your php.ini and my.cnf, then I would suggest posting on vb.com in the Server Configuration forum to see if they can help fine tune your server.

no access to those. i mean would i be better served moving to a vps? i just dont see the reason to move to a full dedicated though i would have full access. i just dont have the users to justify a full dedicated server

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

this is what they just posted to me again

None of those tell me a thing. Like I said, those are problem queries because the table is locked and thus the queries can't be performed. The real problem is whatever is locking the tables. You need to start noting down the times and what is going on on your forum when this happens and then see if you can figure it out. Basically a near impossible task (believe me, I say that from experience!).

What you might want to do is turn off all your modifications. Leave them off for a couple of days and see if that fixes the problem. Then turn on a modification a day and keep seeing if the problem returns. Maybe that way you can figure out what modification is causing the problem and then disable it for good.

Ya I have been trying that. I will see what I can find out doing it. I told them that those queries weren't showing anything and if they had more info for me. I will see what I get.

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

and basically ive ruled out that the transparent deal is in any file or template (i found editplus that searches all files in a folder and subfolders) that has to deal with my forum. now i have to check to see about folders in my public_html i suppose

This is caused by a modification.

The main culprit is:

| 180105 | fvsports_Focker | localhost | fvsports_forum | Query | 24 | Copying to tmp table | SELECT item.item_id, item.item_title, item.threadid, grp.group_title, item.item_status, item.item_cr |

This does not look like a default vB query.

"Copying to tmp table" This indicates that MySQL need to copy all data to a temporary table in order to do a sort, filter or merge. This can be caused by missing indexes on the table. Please contact the author of this modification for a solution.

thanks. i did a search and that comes up as my vbookie mod. ill have to look into remedying that and seeing if that helps

Learn something new every day! :up:

Thanks for the help you two. I believe that may have been the issue since it hasn't acted up since I disabled that mod. I will keep an eye on it for the next few days to make sure, but I really appreciate the help.

I'm having similar problem. My webhost said that the query has become too complex, and that it was timing out before it finished. I tried rescheduling some of the RSS feeds, but that proved fruitless, as it got another error message today. Any suggestions on this one?

I've noticed another accompanying problem:
The RSS feeds won't write to the server until, or unless I sign on as admin. Like it needs that as authorization

You need to increase your mysql wait time.

Oh for gods sake open your own thread. I just wasted 5 minutes reading this one only to realize it's from 2008! And your error isn't even the same as his. Assuming you've uninstalled (or at least disabled) all your mods, and the problem still occurs, then take it to vb.com.