Database caching?

[fury]

I was just thinking (imagine that!) about something that might turn out to be a good feature, and that is database caching. I don't know what method would be used to cache the database, but it could significantly reduce MySQL queries for some things that don't change a whole lot, like username, registered date, email address, birthdate, signature, etc etc... Unless I'm mistaken, things like that are queried from the database every time a thread is loaded.

I'm probably nuts though, but this could reduce the amount of MySQL queries that occur when running the board, which for some hosts may be limited or closely monitored and frowned upon.

Whaddya think, vBulletin developers?

[The Prohacker]

Where would the cached info be stored? If back in the database it defeats the purpose and if in a flat file you will suffer the same problems as UBB, right now vB is lean and mean......

[SirFaraz Khan]

Good point PH~

[eva2000]

well you could cache threads by changing thread_cache_size in my.cnf

[Wayne Luke]

Actually some information is cached already as long as you have the proper version of MySQL.

We currently keep the session table as a HEAP table. This is a table that exists in memory only. As such it is very fast on accesses. Faster than even out of the database. THis is the table that is accessed everytime on page view.

Now it is theoretical that other tables could be kept as HEAP tables but this wouldn't work for large tables like post or thread and the HEAP or cache tables would have to contain duplicate information because it has to be stored on the disk at some point and it won't if it is all in a HEAP table.

We are constantly looking at ways to improve the speed of the vBulletin system so you can rest assured that we will review all available technologies.

[fury]

Okely dokely. It was just a suggestion that I thought I might share in case nobody else thought of it :)

You guys r0x0r

[MattR]

MySQL (hopefully), like any (R)DBMS, will cache frequently used parts of tables and indexes in memory and avoid disk reads. It's caching is usually far superior to anything 'home cooked' since it generally knows best. :D Of course, if you know something it doesn't know (like the session table) you can explicitly force it to be stored in RAM.

[Wayne Luke]

Exactly Matt. We do try to work in the system as much as possible because if we override the wrong routine, it will actually hurt the overall performance of the system.

MySQL also realizes the need for performance increases so is released new features that will help in the future.

I know you use Sybase so have done the same thing with your forums and spent untold hours tweaking the performance. With many of the same concerns.

[N.B.]

Yes, caching info is really a good idea !
Here there is another:
Cache the PHP skripts !
Dunno ask me how, bcz I am not the developer, but Zend (company behind PHP) has something for that issue:
http://www.zend.com/zend/products.php#cache
I do use VBB and PHPNuke for my webprojects, and installing Zend Cache dropped Server load from .9 to .09 :)
Server is now really speedy ...

So maybe that is another option, if your server is already overloaded !

[Wayne Luke]

Originally posted by N.B.
Yes, caching info is really a good idea !
Here there is another:
Cache the PHP skripts !
Dunno ask me how, bcz I am not the developer, but Zend (company behind PHP) has something for that issue:
http://www.zend.com/zend/products.php#cache
I do use VBB and PHPNuke for my webprojects, and installing Zend Cache dropped Server load from .9 to .09 :)
Server is now really speedy ...

So maybe that is another option, if your server is already overloaded !

We are actually talking about techniques that can be used in the vBulletin software to speed data access. We have no control over what people install on their servers so we don't discuss those issues.

[N.B.]

yep, sorry ..
A little offtopic, but I thought it would be helpfull for some people !
Especially bcz you may try Zend Cache for a month, just for free ...
And it did really speed up the server hosting the board, so give it a try ....

[rylin]

ramdisks are your friend :P

[N.B.]

That would be a nice solution as well !
Especially now ! Here the RAM price is down under EVERY level ..
so filling up the server with 1.5 gig RAM wouldn´t be aproblem ..
and a 700 meg RAM disc for the mysql stuff, nice idea ..

Although, I never worked with RAM discs under Linux ! Do not even know, if that is possible ! ;)

[N.B.]

yes, and a Problem what I didn´t think about:
If the server crash, or shutsdown ...
The RAM is flushed, and so all the data would be lost ...
But the speed would be unbeatable :D

[rylin]

the data wouldn't be lost..
you just cache it to the ramdisk
and if it's not available on the ramdisk, you cache it to it and read it from there ;)

eg something like

if((!file_exists("/ramdisk/userid_signature")||(filectime("/ramdisk/userid_signature")+3600)<time())) {
perform nice query for putting the sig in /ramdisk/userid_signature if the file doesn't exist or is older than one hour
}

[MattR]

Why not get a solid-state disk? :D