Spliting forums into multiple servers

[Poh]

Hi,
Is there any way of spliting the forums into different section and each section running on different servers? This is because, we ran into load problem with all the forums running off one dual xeon system. If not, will there be such an option for version 2.1? Thanks.

[Chris Schreiber]

Well you could move the database to one server and then have apache/php on another (or multiple) server to help balance the load.

[GMTalk]

how difficult would that be to set up? I am thinking of doing this for my server. I would like to get a secondary server to run just mySQL. I posted a message about this in the mysql area not realizing that this thread was here.


Sorry guys about the double post.

John

[Chris Schreiber]

It's actually very simple... just install MySQL on the new server and move your database there.... then grant permissions for remote connections... edit your admin/config.php file and change the server from "localhost" to "your.server.name" and you're all set.

[GMTalk]

sorry for the questions, but I have to ask. I can get another server with a 500mhz proc and 256mb of ram. Would htat be enough to handle the mySQL server with that being the only thing running on it for 4 VB sites or should I go with more ram?

Thanks for your info Chris

[Chris Schreiber]

It depends on the number of concurrent users (connections needed).

[GMTalk]

well I average about 450 online right now during peak times for all sites.

[Chris Schreiber]

That being the case I would opt for 512MB of RAM, that should perform well.

[GMTalk]

thank you Chris.

[Vinnie]

I heard that MySQL does not scale well once you go past 600 or 700 concurrent connections.

The primary reason, is that the lack of row level locking causes simultaneous reads and inserts in the "posting" table to be very slow.

This can be solved using a transactional DB with "versioning" capabilities (InnoDB being one example) however it is currently in beta and has not been approved for distribution as a shipping quality MySQL binary.

[Chris Schreiber]

That's true Vinnie, once you go above 700 concurrent users you need row level locking (either Innodb/Gemini with MySQL or an enterprise level database).

[Mike Sullivan]

Don't forget about BDB tables (page level locking). However, the lack of a summary full table count (used in SELECT COUNT(*) FROM table) annoys me.

FWIW, InnoDB has an ~8k limit so it might not be a good idea on the post table. AFAIK, BDB has no such limit. I'll go see if I can hit the Gemini limit later tonight (although I don't think there is one anymore; it's only limited by the field you use)

[Chris Schreiber]

Gemini may have a limit of 32k per field, but I am not 100% sure...

[Mike Sullivan]

Ahh yeah, that's right -- Kinda funny that they have that in the "features" section, but nothing about it in the limitations section. :)

[fastforward]

Originally posted by Ed Sullivan
FWIW, InnoDB has an ~8k limit so it might not be a good idea on the post table.
Ahh.. so that's why I couldn't convert the post table. :rolleyes: I was playing around with that a few weeks ago. I spent hours trying to figure out what went wrong. Maybe I should have read the docs :)

[Chris Schreiber]

Originally posted by Ed Sullivan
Ahh yeah, that's right -- Kinda funny that they have that in the "features" section, but nothing about it in the limitations section. :)

LOL Yes, it's a "feature".... well actually Progress has the same limitation on most field sizes, and I know they took some code from their enterprise database for the Gemini logic.

[Chris Schreiber]

Originally posted by fastforward
Ahh.. so that's why I couldn't convert the post table. :rolleyes: I was playing around with that a few weeks ago. I spent hours trying to figure out what went wrong. Maybe I should have read the docs :)

Don't feel bad, I tried to convert to Innobase as well and couldn't convert for the same reason.

[Mike Sullivan]

Try InnoDB on the thread table (good for when the view count is updated).

searchindex might be a good idea (lots of inserts). word wouldn't hurt either.

You could play around with session -- Jonathon (roy7) said HEAP and InnoDB basically handled the same for him with it. Although I think HEAP would probably be better, I have no test results to back that up :)

user could be a good idea (lastactivity update on all pages).

Other than post, I think that's it basically...

</random_thoughts>

[MattR]

I would be interested in benchmarks comparing the de-normalized vs. normalized tables...

Namely inserting a whole lot of posts from the web end with the update to the thread / forum tables (as is) vs. many many posts w/o updating thread / forum (maybe user) tables.

Then try querying 100,000 times with the de-normalized queries (again as-is) then trying a respective join or count or whatnot and see how noticeable a performance hit it is.

If there are people who are experiencing table-lock problems, removing the de-normalization (which if you have a lot of concurrent inserts can really kill the thread / forum tables) could isolate the locking problems to the post table only, which then could be solved by other methods.