Cache overloaded, host banned DB

[astonishedman]

We've been running vBulletin for a few years and recently it stopped working. I investigated and found that the database had been deleted. We contact the host company and they said it had gotten too large, using more than allotted resources so they deleted the database. They did export it to a file, though, so at least it isn't lost. I downloaded the 2GB file and found that the "cache" table is about 80% of the document. So a few questions:

1. Can I delete the INSERT for the cache table and re-install the database without it crashing the forum in any way?

2. Why is the cache table needed and can I opt not to have it fill up like this?

[Wayne Luke]

Your site will not work properly without the caching. You can clear the Cache in the AdminCP under Maintenance. You can delete the content of the cache and cachevent tables within the database but they will be rebuilt.

On a properly configured server, the cache is self-limiting and old entries will be deleted. A 2 Gigabyte database is really not that large. You need to find a new hosting provider that allows you to have a proper database if you expect your site to be able to grow. There is also the alternative of vBulletin Cloud where we won't put petty restrictions on your forum. You receive 100 GB of storage for your forums with Cloud.

[astonishedman]

Thank you for the response. I have posed the cloud option to the owner to consider. If we try to reinstall on current host the file limit for upload to the database is 1GB, so I will have to manually alter the file to reduce the size in half. So, even though it will be rebuilt, can I delete the cache table insert command and allow the cache table to be created empty just to try to get it up and running and then I can get into admin? Or will this cause the software to not work?

[Wayne Luke]

Personally, I would restore the database locally and delete the contents of the cache and cacheevents tables. Then create a new backup.

You can carefully delete the insert queries for the cache and cacheevent tables but if you delete too much it can make the entire file worthless.

[astonishedman]

Okay, I imported the file with the cache and cacheevent tables emptied and truncated. When I go to my forum it is just a blank page.

http://www.uberdrivers.com/forum/

Not sure how to restore. Should I try to do a reinstall of the software? The latest I have is 5.4.0.

[Wayne Luke]

Running the install again will simply delete the database. This is why I recommend using queries on a functioning database to empty the two tables. I have no clue as to what the problem is here as the cache and cacheevent tables are transient data and will simply be rebuilt. I can only guess that the import failed somewhere along the way.

[Ryu_gr]

Hello,

I have the same issue and found this topic.

My database reaches limit (3gb) with cache/cacheevent tables being responsible for that and i'm forced to empty them via phpmyadmin because i lose access to admincp due to db overload.

On a properly configured server, the cache is self-limiting and old entries will be deleted.

Shouldn't this be forum's responsibility? i.e. set cache table limit in admincp settings (e.g. max 500mb). Please consider it as a feature request if this is possible technically.

Our forum has 1,5 GB database size with empty cache tables and to avoid the 3gb limit issue overload we must manually empty them from time to time.

[Wayne Luke]

Hello,
Shouldn't this be forum's responsibility? i.e. set cache table limit in admincp settings (e.g. max 500mb). Please consider it as a feature request if this is possible technically.

There is a issue open to create a hard limit but that would also create performance issues with sites as not everyone is using SSD storage, PHP Opcache, and Memcache yet.

As I said above, vBulletin should be deleting records out of your cache on a regular basis to limit its size. With a 1.5 GB size database, it is quite likely that you'll hit the size restrictions implemented by your host even with this functionality. This will depend on how many channels you have, how many usergroups you have, and how you use those usergroups. If you have a lot of restricted channels and a lot of secondary usergroups, your cache could easily be larger than the data in your forum just creating all the custom views for each user.

However, your site doesn't seem that large to have a 1.5 GB database. Are you storing attachments in the database? I suggest moving them to the file system. You can do this in the AdminCP under Attachments → Attachment Storage Type. Moving attachments and avatars to the file system will help reduce the size of your database.

I also believe there is a Schedule Task script that you can download at vBulletin.org that will allow you to delete the cache periodically.

[Trevor Hannant]

This is the modification that Wayne references above:

https://www.vbulletin.org/forum/showthread.php?t=328709

You will need to be registered on vBulletin.org, using the same email as is on your vBulletin Customer Account (if you use a different email address, make sure you add this in the Priority Forum Support form within the Members Area).

[Ryu_gr]

Thank you for the suggestion, this modification looks great, however i found another solution that fitted in my case and in case it helps other users or development of vbulletin here it is:

I noticed the vbulletin database cache increases on inactivity combined with bot visits and i had many web crawler bots visiting my site, about 50-60 were online every time.

When new posts where made regularly the forum didn't have problem, i assume the cache clears itself on new posts? (makes sense because users must see the new content).

So inactivity and constant web crawler/search engine bot visits = database increase (in my case from 1,5 to 3gb).

I stopped the bots (found them on who is online with admin login) by editing robots.txt:

User-agent: SemrushBot
User-agent: PetalBot
User-agent: barkrowler
Disallow: /

I don't have anything against them but i needed to do that in my case.

[Wayne Luke]

Bots are just guest users. At least as far as vBulletin is concerned. There is no differentiation between the two.

You can turn on additional caching for Guest Users under Settings → Options → Server Settings and Optimization Options. Set it to 30 minutes or more to reduce the amount of cache writes that are needed. Could even go longer with something like 720 minutes for twelve hours.