Queries clogging MySQL [Archive]

View Full Version : Queries clogging MySQL

FockerFGAA

12-01-2008, 07:33 PM

FockerFGAA

12-03-2008, 03:29 AM

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.

Lynne

12-03-2008, 03:56 AM

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.

FockerFGAA

12-03-2008, 04:07 AM

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

"GET /forum/transparent HTTP/1.1" 404 649 "http://www.fvsports.com/forum/usercp.php" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3"
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 1228284748 at 1228284748 ---------------

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)

Lynne

12-03-2008, 04:16 AM

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

FockerFGAA

12-03-2008, 04:25 AM

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.

Lynne

12-03-2008, 04:27 AM

FockerFGAA

12-03-2008, 04:38 AM

Lynne

12-03-2008, 04:47 AM

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.

FockerFGAA

12-03-2008, 04:55 AM

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

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

Marco van Herwaarden

12-03-2008, 08:21 AM

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.

FockerFGAA

12-03-2008, 01:45 PM

Lynne

12-03-2008, 02:20 PM

"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.
Learn something new every day! :up:

FockerFGAA

12-05-2008, 03:17 AM

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.

The Ayatollah

02-18-2011, 09:14 PM

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?

Database error in vBulletin 3.6.8:

Invalid SQL:

SELECT * FROM rsslog
WHERE uniquehash IN ('d18878d8260a370dc7f407ffadf1fac8', 'fd4d852b10413d423d7705d2829d941b', 'ac3dab377521435b4ef93b99709c6ce8', '17820c2d8b658af74fb2529b7b910edf', '18ce1b2f2dc91c882a4d71c62db04e94', 'a05e8f98d0c563ca898136c5301f4bc3', 'a05afb7ee5a567edaa0eac32acb62a01', 'e14ada3f21a2e947dcbfd873c0112c24', '6a15b6b19b8074dbbdd308cf74185715', '55e559197f4d0ad938f5f2e7bdf0464a', 'd8a66704930192ee14b7b1b22ed354cc', 'df5bfcac63bf02b22e961b2fdadc67e3', 'c07d8d1add26926e1fada3d590f8344d', '6cfdae6357c116e9ee8aacb04970ed31', 'caffa099b45fbe4e05e036a64f6bfb32', 'df1e74f1dd4fd873560a1a2b7c98c0a3', '9230086cb8ee2aa6628c00e79ce84506', 'a8bf3cc8767f4360f3b1f07557ac8b73', '76921c7b88d06a1c6faeaa658a306b93', 'c077e4a9d0f3e4870faf743ca1d0ca57', '086c3b317ed243167646f8381967ccce', '97934f4dd3586bf9f4ada29610bae354', '4845c6b6c189dc0594ccc45d649f4d5b', 'd689651f72f3a3ee0d6fa83aa9a44b4b', 'fd9eb040116a1b1569d2db8ee1cf2bc1', 'ab3ae9c56ebf2dcf0c7b44d648f7b984', 'fb6a058d89a16e42bfe20677f1afa505', 'd9ca98535bd9dcdc9347bb18f4845ed3', '2f96199c4c8743866ca194e4f264ba67', '275973dc3d8bdf41ccbbace7793000d6', '4de923ae489f7bac0900854c14ce23d1', '54903cb8cc41089d303859f9ba837af6', '0c2507a4c9581d418ed777998c691f96', '727d5476a4c40531105d74d6f0f56045', 'f377d7cab298501e676d0559c153fa94', 'd195464dd1ecedc884229dae71d7adf7', '347b21f861320f07ffa24ad0f95cf29f', '7185bd21a8a79787a0748acc0bc1084f', 'be114a8b326091a1574d2266dd2a5a64', '3c46ef5c0e3286cb1f7853b77c61d811', '5c29c2e808be3f3b878742462c2dde46', '77b79dd556c80ecef7f6a859b5e8b736', '4398088d015ce5ed194ac5f3fa609bcc', '770bce9eecb13e5849aaf555c0c2dfe1', 'd8ed1bff65c9f617b7c4b2dc520686fa', '37efa5f1eaeea8a1a6e3aa7c1e3be85b', 'ba92711b0c6077d8af9cc4fa1d9a31e9', '7675d81f6f9a6634d0d4da3a5d160f84', 'c5d2f7fbc0f58b400e33d91a07d09388', '092d3dc84c983ba48f3ea981bfb80381', '07e445bfb2e098bc21d79894794bae87', '1c0cd25e38c8095480668f982def9dd6', '1af61ab00d0f8371102e7d3b79fc0a1c', 'b8df530fbfed7762b78bbd8cfd950aac', 'ec11924fcb285b216aecba21b7651ebc', '432019b671deaff0d89cf16aa4275ae3', '112b793cc27a823e9f290c5bdaa964c5', '78991541f3dfaf4aa624cebf6f664fb0', '5ed9e656bbdb8b29b450df8eb921e187', '39fe513e472efc8e1fe340ddb1f54635', 'b2a19fc75b3f58a2b8d76935b0d236bb', 'bc5c930680d863731cf57e3cbfd2d7aa', '557590f9f8c7e3c4d15519b91e718194', '549f4d02b0c05fd054c6c8179b7e4bdb', '28dd6a71168b713ade3737866d38a541', '09fa91b02e64a954de092b911b597108', 'b8d27f5c1bdc4ecdca7f99b43a9b5543', 'f8a4988585f86a4364fd7598bbbf406d', 'c3f0002494f2d528f50265c004f44572', '540a08a908a78f710528875090341fd2', 'dfa8df6d7f611806946d1af360cb34ec', '4e8fe940909e05a28c2b0e496dd5e0ed', 'cf1ddc431cce567a2fe5ae6c1c63756a', 'f4eb7520059d7e685dd57659df309292', '12ae314970ef346e6eb8dc790988d72f', 'bd8daade3cec8022e31ddd76e766c66b', '730d895e025f12461a6ee611ac4e76f6', '60dd14766dd5914fa73e01892f7a11e1', '9bcf06e40c2d01f905cd8225d206939c', '06128bec6f4ef5de3bf183eca69f8b2a', 'e7c7555abd1aef273210fdd23b09c9a7', 'ef0ce3aac198986c74b057640c35db64', 'b96ffc1302c9b6f8b4bb1be0c29d05b6', 'b535be212928b0d0d9284e9e1afe8f44', 'b6dbab0c6312d76575d8a6d6cd80d3b8', '7ab317b03e3c8a26663d1fad16897c38');

MySQL Error : MySQL server has gone away
Error Number : 2006
Date : Friday, February 18th 2011 @ 02:52:58 PM
Script : http://www.fightbackinsac.com/forums/cron.php?&rand=987610
Referrer : http://www.fightbackinsac.com/forums/search.php?searchid=10876
IP Address : 75.45.11.56
Username : Unregistered
Classname : vB_Database

The Ayatollah

02-20-2011, 03:39 PM

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

Hornstar

02-22-2011, 07:58 PM

You need to increase your mysql wait time.

Videx

02-26-2011, 01:25 AM

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.