UBB.threads PHP Forum Software Community Forums General Suggestions and Feedback Memory (heap) tables

There are 2 tables that we use in UBB.threads that are pretty much just temporary tables, but they get accessed very frequently. These are ubbt_ONLINE and ubbt_TOPIC_VIEWS. I've chagned both of these to a memory (heap) type table here. This basically makes it so the table resides in memory and isn't written to disk, which should provide a bit of a performance increase. These 2 tables are also prone to corruption since they are accessed and updated so frequently since every update requires writing to disk. Changing these tables so they just store their data in memory should help in that area as well.

We may end up making this change a part of the original setup. It's an easy change, one that you can try yourself if interested by running the following SQL commands

alter table ubbt_ONLINE type=heap

alter table ubbt_TOPIC_VIEWS type=heap

Is there any minimum MySQL version for this - or server memory required. Is there a point where writing to the disk is better on bigger busier forums, or doesn't it matter?

Thanks.

No minimum version, heap tables have been around since vesion 3 of MySQL. There are limitations to the tables in that they cannot hold text or blob fields. They also lose all of their data when mysql is shutdown, etc, so they are really only good on temporary tables.

Memory useage. Currently 200 rows in the online table is using about 2 megs of memory. On a busy site where you have 500 or 600 people online you'd probably be using about 5-6 megs of memory. The TOPIC_VIEWS table is very small since it just stores a topic id, so that one won't use much at all.

The busier your forum is the more improvement you'll probably see by making this change. For example the online table is written to every time someone access a page. Under the normal table type it has to write that to disk, under a memory type table it just changes in memory, that's it. So, smaller forums probably won't notice much of a difference but larger forums will.

Thanks - what happens to the topics views data in the even of MySQL restarting? Who's online will reset and that is fine - but isn't the topic views required?

topic views is just a temp table as well. We no longer update topic views live since this put locks on the posts table every time it was updated. So what we do is store these in a the topic_views table. Every time a new post is made we grab everything from this table and then update the view counter in the TOPICS table at the same time we're updating other fields, and clear everything out from the topic_views table.

Hopefully that's as clear as mud But in essence, the topic_views table is strictly a temp table as well, so losing data in that table would only mean you miss a few views at the most.

A good idea!

But - If this is introduced there also should be a script that can be called to safely shut down your MySQL server. My db automatically restarts once a week, so I like to call such a script before, to flush the contents of the heap tables to the disk tables.
At least we should take care of the topic views and write them to disk, because there can be thousands of views before a new post is added to a topic.
Btw, the view count of all the older topics, where no new posts will be added anymore, will stay unchanged forever. One could use such a shutdown-script to write the views to disk manually or on a regular interval.

I need to clarify a bit. It doesn't have to be a new post to that individual topic. Any new post to any topic will update all of the view counters for every topic. We just update them all at once.

Ah, I see. So you would just loose a few counts, that's fine for me.

This will also solve the reocurrence of the ubbt_TOPICS_VIEWS problem that I had?

My opinion; a temporary table is a temporary table ...

The second one should be TOPIC

...at least I hope so, cuz that's what I changed it to after getting a "table not found" error.

Yes, I also agree that the command should be alter table ubbt_TOPIC_VIEWS type=heap