Luckymonkey

Really strange problem with vBulletin on one of the forums I look after today. MySQL ran out of available connections (even to the root user), and the site ground to a halt. Quick reboot of the server so I could get in, and SHOW PROCESSLIST showed me loads and loads and loads of threads trying to retrieve data from the datastore tables..

Something like

SELECT title,data FROM datastore WHERE title IN (’cron’,'forumcache’,..)

Initally I thought that this was a problem with the load on the server, so I increased max_connections in my.cnf, since Googling for the ‘too many connections’ problem lead me to the MySQL manual page for ‘too many connections’. So the value was increased from 100 to 800, and now although I could log into the database on the command line, our front-end servers were crawling.. (and the load was going through the roof).. so something else was up.

So, I looked into this SELECT title,data FROM datastore query which seemed to be hosing the system. The datastore appears to be an area in vBulletin 3.3 where it stores random bits of information.. like the timestamp which the cron last ran at, and (interestingly) some serialized php code under the forumcache title.

Just selecting the data on it’s own for forumcache seemed to take an age - and there was a huge amount of it, so it began to look like this was the area causing the problem. One part of the problem is this huge dataset which MySQL is spitting out, and the other problem is the load on the front end, presumably caused by unserializing all of this stuff coming out of the database.

We then decided to rebuild the information in the forumcache field, through the vB backend (reset counters -> rebuild cache?) - which took a while, but instantly solved the problem.

I see in the 3.6 version of vBulletin, they have replaced the database table with a directory and files instead - thats got to be a step in the right direction (although may end up being a little more complicated with a multi-front end setup).

It would have also been possible to rebuild the cache using :
build_datastore('forumcache', serialize($vbulletin->forumcache));