What is vb_searchlog table and why is mine so large?

[jkotlowski]

The data length on my vb_searchlog table is 8.75 GB

Is this normal? It's really strange, because a couple nights ago it was half this size. My host recently moved my SQL server and immediately afterwards, my site has become extremely slow. Would this have something to do with it? And is there some way I can fix it?

[Trevor Hannant]

What do you have the following set to:

AdminCP > Settings > Options > Server Settings and Optimization Options > Search Result Cache Lifetime

[jkotlowski]

What do you have the following set to:

AdminCP > Settings > Options > Server Settings and Optimization Options > Search Result Cache Lifetime

It's set to "1".

-edit-

I just set that to 3 and set the "cached posts lifespan" to 4, which seems to have improved the site's loading time quite a bit. The table is still pretty huge, though. 7.61 gigs. It's still about 3 gigs larger than it was before my host moved my server, which I really can't understand. Could something have been duplicated when they moved it?

And does changing that setting affect JSON searches? I currently have a search module showing latest topic results on my homepage.

-edit 2-

The loading speed doesn't seem to have been improved as much as I thought. Still takes between 5 and 10 seconds to load the home page.

[Mark.B]

You can in theory truncate that table (delete the contents) and then rebuild your search index. Note that on a large site rebuilding the search index is a lengthy and resource intensive process, and you may still end up with a similarly sized table.

Note: If you do go down this route, take a backup of the table first.

[jkotlowski]

You can in theory truncate that table (delete the contents) and then rebuild your search index. Note that on a large site rebuilding the search index is a lengthy and resource intensive process, and you may still end up with a similarly sized table.

Note: If you do go down this route, take a backup of the table first.

Would that be the same result as deleting the search index from inside vBulletin then rebuilding it? I did that yesterday and, unfortunately, it didn't seem to help. My site has amassed around 300,000 posts, so rebuilding the index is a very lengthy process anyway. So if it's essentially the same doing it through my cPanel, I'd rather not waste time on it again.

[Mark.B]

Would that be the same result as deleting the search index from inside vBulletin then rebuilding it? I did that yesterday and, unfortunately, it didn't seem to help. My site has amassed around 300,000 posts, so rebuilding the index is a very lengthy process anyway. So if it's essentially the same doing it through my cPanel, I'd rather not waste time on it again.

If you have 300,000 posts then your searchlog table shouldn't be anything like that size.

I would try manually truncating the table, then rebuilding the search index. At least when you manually truncate you can verify it's empty and the size is zero before you run the rebuild.

[Paul M]

The searchlog table is (as its name suggests) just a log (in fact, cache) of previous searches.
You can truncate it if you want to, there is no requirement to rebuild any search indexes if you do.

I dont believe "Search Result Cache Lifetime" has any effect on it at all, thats seems to be for some search stuff cached in the main vB5 cache.
I could not, in fact, find anything that clears that table, Ive asked the current vB5 team about that, not had chance to see any reply from them yet.

[jkotlowski]

I'll make a backup of the database first then I'll try to truncate the table to see if it helps.

[jkotlowski]

After clearing the table, the site seems to be loading slightly better. The table size is currently at 144 megabytes. I'm going to keep an eye on it to make sure that it doesn't grow so incredibly large again. For 330,000 posts, what do you guys say should be an average size for the searchlog?

[Paul M]

The searchlog records are cleared by one of the cleanup crons, which deletes records over a day old from it, so it sounds to me like your crons are not working.

[jkotlowski]

The searchlog records are cleared by one of the cleanup crons, which deletes records over a day old from it, so it sounds to me like your crons are not working.

How would I be able to go about checking that?

[Trevor Hannant]

Go to:

AdminCP > Scheduled Tasks > Scheduled Task Log

...and make sure there's entries for the Hourly Cleanup, Hourly Cleanup#2 and Daily Cleanup crons.

[jkotlowski]

Go to:

AdminCP > Scheduled Tasks > Scheduled Task Log

...and make sure there's entries for the Hourly Cleanup, Hourly Cleanup#2 and Daily Cleanup crons.

Yes, all three of them are there and all three are enabled.

[jkotlowski]

Go to:

AdminCP > Scheduled Tasks > Scheduled Task Log

...and make sure there's entries for the Hourly Cleanup, Hourly Cleanup#2 and Daily Cleanup crons.

Hi, my apologies for bumping this topic, but I'm still having this issue. I do not believe that my hourly cleanups are working properly as the VB Serchlog is still growing in size. I truncated it manually again and it's back up to over 700 megabytes despite the hourly cleanups running just a few minutes ago.

I believe this is creating a disastrous problem with my hosting service, as they've told me as of a month ago that my databases have grown too large to use the hosting package I've been using and forced me into a higher tier package that costs four times as much. Without ads on the site, I obviously cannot sustain such a cost for hosting so I desperately need to fix this as soon as possible.

-edit-

My database is over 4.7 gigabytes in size. And they tell me that it would have to be under 2 gigabytes to move back to the hosting plan I was on.

What is the typical database size for a website my size?

http://t5forums.com/

[Wayne Luke]

What is the typical database size for a website my size?

Really difficult to tell just by looking at the site. There are a lot of variables that are hard to quantify from an end-user prospective. The size doesn't seem unreasonable.

Where are you storing your attachments? You can see this in the AdminCP by going to Attachments -> Attachment Storage Type. If you're storing them in the database, you should move them to the file system and run the tool to clean up the database usage.

Same for avatars. Where are you storing them? You can see this in the AdminCP under Avatars -> User Picture Storage Type. If they are in the database, they should also be moved to the file system.

These might move you under the limit if you have a lot of attachments. However, you'll probably bypass the limit again as your site continues to grow. If you site is only using vBulletin, you might look into something like vBulletin Cloud. vB Cloud offers 100 gigabytes of storage for its base price of $19.95 a month. We'll even move your attachments and avatars to the file system when you migrate.