Split ibf_posts for ib3

[marklcfc]

Please help those with large Invision forums.

My site has just passed the 1 million post mark, and if to upgrade to IB3 we to make changes to the ibf_posts table I can't upgrade. I know staff have said they will try to avoid this but I'm sure changes will be made at some point.

Is it possible to split posts to more tables?

For example ability to set amount (100k posts?) the new posts would be written to the new table.

ibf_posts1
ibf_posts2
ibf_posts3
etc

[Axel Wers]

Probably not, but this possibility was suggested by me many times. I believe that in IPB3 this "issue" will be 'resolved' because big ibf_post table is really painful.

[Luke]

The biggest issue I see is that you would end up with some posts being split across different tables for the same topic. Really the problem is just having to much data for active posts. Therefore, the best solution would be to have an archive posts and topics table where old inactive topics/posts would reside. Like if you had 1 million posts, at least 75% of those should be fairly inactive (besides spiders going through them).

But either way, if you were upgrading, I'd imagine you'd still have to upgrade the archive tables just as much as the active one.

[Mat Barrie]

I think this is where the MSSQL support in IPB really shines - you could partition off the old posts (by year maybe) and reduce the amount of time the server takes to find what you want. A godsend for large forums. Perhaps suggesting table partitioning support to MySQL could help?

[Morrigan]

Wouldn't the problem actually arise with the auto_increment and duplicate post ids?

I think perhaps it would be better to have a task that counts number of closed posts and not sticked and archive them when they hit a certain number.

[Mat Barrie]

No, the auto increment field has a maximum theoretical capacity in the billions, which for obvious reasons isn't expected to occur on most forums.

[Morrigan]

Well what I mean is if you split them then wouldn't it start over on the numbers? Which would essentially cause duplicates.

[W13]

Splitting the posts table will cause tons of other problems... i.e. searching will be a pain because the search will need to happen on all 30 tables (or however many you have).

I'm at 9.7 million posts right now, and it keeps going up. The main thing that helped us was Sphinx Search. It took the high server load, and then just made it magically disappear :-)

[Mat Barrie]

Ah, I see. Yeah, that's why Partitioning helps. It basically really helps performance on MSSQL databases. Read up on it if you want.