vbulletin_datastore - serialized tables

[marisconsul]

Hi,

I am new to vBulletin so I am that familiar yet with the backend.
I am planning to have more than 1000 usergroups AND more than 1000 forums. That shouldn't be a problem so far. But I checked some of the tables vBulletin is using. One is vbulletin_datastore.
It seems that in this table two other tables are stored in a serialized way - vbulletin_usergroup and vbulletin_forum. In that case it is not possible to have e.g. 1000 usergoups or forums because the size of the row would be to great for MyISAM.

Am I correct? If so, how can I prevent this?

Thank you.

René

[Marco van Herwaarden]

MyISAM can handle very large columns.

But creating 1000 usergroups and 1000 forums will very likely give you memory problems and i strongly advise against this.

[marisconsul]

1.
Thank you for your fast reply. I am talking about MySQL and here MyISAM restricts the maximum row size to 65535 Bytes per row. 1000 entries are not much for a database. Hence is this an issue in vBulletin? If so, what would rise problems here?

2.
Why are the forum and usergroup table stored serialized in the table datastore? Is there a specific reason and can this be changed easily.

Thank you.

[Paul M]

The maximum row size does not really come into it - the datastore "data" column is defined as MEDIUMTEXT (a form of BLOB) and can be extremely large (about 16 million chars I believe). It only takes up between 9 and 12 bytes in a MyISAM row.

Its done in vb to cache the data, and the cache is used in many places, it cannot be changed easily.

[Marco van Herwaarden]

1. In addition to the MEDIUMTEXT datatype, there is also a LONGTEXT which can hold up to 4Gb. ;)

2. Usergroup, forum & permission data is serialised into the datastore where all the data can be read into memory from a single read. This is a "cache" to speed up the forums.

The problem is mostly the permission, read into memory for each pageload:
- Data for all forums (name, description, settings, etc..)
- Data for all usergroups
- Permissions for each usergroup / forum combination (this can get huge if you have 1000 forums with 1000 usergroups)

Your server will probably quickly run out of memory.

[orban]

If every user is just member of a handful of those usergroups just create a new field in the user table, and store directly the ids of the forums he has access to in that field. Then make some plugins to manually check for permission in those forums (showthread, reply, newthread...etc).

[marisconsul]

Is it possible to prevent vBulletin from storing the forum and usergroup information into the datastore table and read it dynamically from the database?
I really need to bypass this behaviour. Can you please tell me the where I can find the places in vBulletin to change this.


P.s.:
I was talking about MyISAM tables in MySQL. Except TEXT and BLOB (5-9 bytes only go toward the total row size because TEXT and BLOB types don't get stored in the row itself, just a pointer to them.) fields the maximum row size for MyISAM engine is 65535 bytes per row. See the following page:

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html


Thank you.

[Marco van Herwaarden]

Is it possible to prevent vBulletin from storing the forum and usergroup information into the datastore table and read it dynamically from the database?Short answer: No!

The only option for that wuld be to run vBulletin 2.x, something you don't want to get started with.