Loading the vBulletin tables into MySQL MEMORY tables?

[Adult SEO]

Does anyone have experience with loading the vBulletin tables into memory tables?

It seems this may speed up the forum 3000x in practice according to MySQL performance blog.

http://www.mysqlperformanceblog.com/...vs+bdb+vs+heap

The only thing you would need is a backup system (memory is erased on server restart) and enough ram for the tables.

I am able to configure a varchar field up to 21k chars for MEMORY tables in PHPMyAdmin, which should be enough for most forums I think.

[Lynne]

Only put stuff into memory if you are prepared to lose it. vb puts the session tables into memory but that's it.

[Adult SEO]

Originally Posted by Lynne

Only put stuff into memory if you are prepared to lose it. vb puts the session tables into memory but that's it.

Well, looking at the potential benefit: a forum that runs 3000x faster in practice, a comprehensive failsafe for it may be worth the effort?

I think this problem can be overcome.

And think about the benefits. Everything can just work as it does now, it will just work 3000x faster and requires some extra code for the failsafe. And most servers remain up for months easily, so it's not that the tables would need to be repopulated every day or so.

And ram isn't very expensive anymore, you could easily install 32 gig extra ram for your tables for just about $1000 dollar (high quality ram).

The benefit: a forum that can run as normally (also with the regular MySQL slave server configuration) but 3000x as fast as when using InnoDB or MyISAM tables.

--------------- Added 26 Mar 2009 at 21:50 ---------------

Intel is getting a memory like disk on the market:

Intel? X25-E Extreme SATA Solid-State Drive

Sustained sequential read: up to 250 MB/s
Sustained sequential write: up to 170 MB/s

http://www.intel.com/design/flash/na...reme/index.htm

Compared to a 15k SCSI disk with a read speed of about 80MB/s it is very good!

However, it is still nothing compared to DDR3-1600 ram speed: 12800 MB/s (about 250 times as fast as a regular SCSI disk)

[Dismounted]

Originally Posted by Adult SEO

a comprehensive failsafe for it may be worth the effort?

You're going to have to move data to and from that failsafe - which will be slower than the RAM, meaning you would still lose data.

[Adult SEO]

Originally Posted by Dismounted

You're going to have to move data to and from that failsafe - which will be slower than the RAM, meaning you would still lose data.

Yes, but does it need often, or could it be once per year if the server remains running?

If so, the user could be provided with a temporary "Under maintainance" message while the tables are repopulated.

[Lynne]

Originally Posted by Adult SEO

Yes, but does it need often, or could it be once per year if the server remains running?

Do you never have your servers go down unexpectedly? I know I do for reasons that have nothing to do with my site or anything I've done. I've even had one go down because some guy in the server building did an oops! and disconnected us. I would rather have my servers optimized to run well and have the data stored on the drive than count on the servers never having any sort of problem.

[Adult SEO]

Originally Posted by Lynne

Do you never have your servers go down unexpectedly? I know I do for reasons that have nothing to do with my site or anything I've done. I've even had one go down because some guy in the server building did an oops! and disconnected us. I would rather have my servers optimized to run well and have the data stored on the drive than count on the servers never having any sort of problem.

Well, that is what the failsafe would be for so you benefit from the same security for data as with disk storage and also the speed benefit of memory storage.

And 3000x more speed for a big forum can make a huge difference, let alone in savings on hardware when you would use 64 gig ram (2000 USD) instead of a few extra servers (20k USD or more).

How much time would it cost to repopulate the memory tables from disk? Even if it costs 10 minutes for very big forums, why not having the server show a temporary maintainance message during that time. (64 gig data from a disk with 100MB/s speed would take about 10 minutes to do it)

The speed benefit would be so huge during the time the server remains online, I doubt any super expensive hardware setup will be able to beat it easily.

[Adult SEO]

Also, IIRC, tables stored HEAP/MEMORY cannot have indexes - meaning you may even slow your forum down by doing it.

It seems to me that MEMORY tables can have indexes like InnoDB.

[acroplex]

Are there servers that use solid state drives? That might be your answer.

[Adult SEO]

Originally Posted by acroplex

Are there servers that use solid state drives? That might be your answer.

Well, it will be a great improvement yes, however, MEMORY is still just about 12 times as fast.

12 x the fastest disk, for just 2k investment.

What would you need hardware wise to get this capacity with just those disks? raid 10 with 240 disks?