Vbulletin Tag Locking database

Hi Vbseo,

Please help. I recently moved to a more powerful host and started having server load problem. I contacted my host and they said that it had something to do with the the tags... They gave me this...

Code:

 194303 | eximstats | localhost | eximstats | Sleep | 30 | | |
| 195021 | xxxxx| localhost | xxxxx | Sleep | 0 | | |
| 195024 | xxxxx | localhost | xxxxx | Sleep | 3 | | |
| 195025 | xxxxx | localhost | xxxxx | Query | 3 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195026 | xxxxx | localhost | xxxxx | Query | 2 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195027 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195028 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195029 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195030 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195031 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195033 | xxxxx | localhost | xxxxx | Query | 1 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195034 | xxxxx | localhost | xxxxx | Query | 0 | Copying to tmp table | SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
FROM tagthread AS tagthread
I |
| 195035 | root | localhost | | Query | 0 | | show processlist |ist |

I never had this problem only until i moved to the new host. Does anyone know the fix for this? Please let me know. Thanks.

My host just tried fixing it, but they said this is query is causing the problems...

Code:

SELECT tagthread.tagid, tag.tagtext, COUNT(*) AS searchcount
                                       FROM tagthread AS tagthread
                                       INNER JOIN tag AS tag ON (tagthread.tagid = tag.tagid)
                                       INNER JOIN thread AS thread ON (tagthread.threadid = thread.threadid)
                                       WHERE thread.open <> 10
                                               AND thread.visible = 1
                                       AND thread.forumid IN (1,2,46,39,38,67,23,58,50,36,32,49,28,5,70,7,71,6,73,8,72,29,9,74,12,75,10,76,11,77,30,14,78,16,79,13,80,15,81,31,18,82,20,83,17,84,19,85,51,60,90,91,92,59,87,88,89,61,86,93,94,95,98,99,100,101,97,37,66,44,45,96,63,33,48,25,43,47,102,54,21,22,35,34,40,41,42,27,24,53,56,62,68)
                                       AND tagthread.dateline > 1250186727
                                       GROUP BY tagthread.tagid, tag.tagtext
                                       ORDER BY searchcount DESC
                                       LIMIT 70 |

Is this when your tag cloud is loaded? What is your "Tag Cloud: Cache Time" setting?

replied via pm

Originally Posted by woostar

Is this when your tag cloud is loaded? What is your "Tag Cloud: Cache Time" setting?

The tag cloud cache time is 30min. Should I increase it?

Originally Posted by Brian Cummiskey

replied via pm

Thank you Brian. I took Brian's advice and deleted most of my tags and disabled the auto tagging generator. It's now running fine.

I have an auto tagging generator running on a very busy big-board without any problems (and have never deleted any tags). I found a problem when the forum built the tag cloud. So I now set it to only build once a day:
Tag Cloud: Cache Time: 1440
Tag Cloud: Usage History (Days): 30