UBB.Developers Forums Community Development Programming MySQL and Server Configuration Multiple SQL servers

We are running large forum with 20.000 users and in the peek we have big slow down We are running forum on two servers: 2100XP+ for web (fast enought) and 1800XP+ for SQL. Both servers have at least 1G RAM, powered by Debian Linux and running latest Ubbthreads + PhotoPost + TaskList + Calendar.

I think that servers are optimized very well - right kernel, right sql performance tunning, etc ...

BUT, that is not enought. Our web site is growing and we won't hold any longer.

I seek solution, how to run forum on multiple SQL servers, for each board / section own SQL server or migration to multiserver SQL (Oracle,MS Sql, ...)?.

Dr. Watson

I recommend to use a mysql replication to achieve some kind of load balancing rather than modifying all the ubb.threads scripts to use different servers for different forums/sections etc.

I have done this on my forum some while ago and it worked very well.

Yes replication is the answer. It is quite easy to set up and you won't have to go through the headache of altering scripts like Astaran said.

>I recommend to use a mysql replication

How can this help?

>to achieve some kind of load balancing

I am looking for pure SQL power, so real load balancing or solution mentioned in my first post.

Tnx

>It is quite easy to set up.

You mean this?

http://www.mysql.com/doc/en/Replication.html

Yes, use the master to perform all changes to the database (Inserts, Updates, Deletes,..) and setup as many slaves as you wish to perform the select statements.

What would you have to change in the php files to have threads pull the selects from the local machine and the writes/edits to the master machine?

While you're busy working on the board please put the copyright back on.

I mean how many users are logged in at any given time?

I just want to see how busy your board is. I am having problems with 50 visitors and 200 000 posts. But all is on one server only!

[]mario2 said:
I mean how many users are logged in at any given time?

I just want to see how busy your board is. I am having problems with 50 visitors and 200 000 posts. But all is on one server only!
[/]

I don't see why this would be a problem, unless your server is fairly slow, or housed with many other websites. My forum has almost 280,000 posts, and at almost all times has 40-90 users online, and I don't use a dedicated database server

@ehill modfifing the mysql class would be the easiest solution. No need to alter all the other scripts then. I've done this for a 5.5x version, but it should also work on the current version.

The ability to handle many users mainly depends on your hardware and server configuration. I run a board with about 10000 users, 1 million posts and up to 250 concurrent users on a single machine.

I don't see why this would be a problem, unless your server is fairly slow, or housed with many other websites. My forum has almost 280,000 posts, and at almost all times has 40-90 users online, and I don't use a dedicated database server

>> pls specify the hardware and software you have.

In my case, I have a dual PII 600 with 256 Meg memory. I just upgraded to mysql 4 which gave a maior improvement even before I am attempting to optimize my.cnf

256mb of memory? ermm.. you really really should upgrade that. The CPU isn't the most powerful either.. but having a dual machine helps. but RAM would help significantly.

That is a pretty slow server as far as for hosting a website, especially a dynamic one such as yours. I was running a 1ghz single CPU with 512mb of ram for my site, and it eventually outgrew that, so you have for the most part really outgrown your server. If you can, try to get at least 512mb of ram, but really 1gb+ is better. But you should seriously consider upgrading.. as a PII is not a server CPU, and considering how old it is, youj are probably running ATA33 or ATA66 hard disks, which is going to be another bottleneck on a busy site.

so my slow one should do for 50!?

I upgraded mysql, now I moved the 150 000 posts mainly to a rarely used archive forum (just a plain forum), using a hack of the bulk move plugin routine.

Things are a lot quieter now, seems I am saving 2000 dollars of hardware cost now.

I still hope some restriction on searches will be implemented, so malicious people cannot overload the server so easily!!

In the peek time we have up to 250 users online, about one milijon posts, 150.000 PM's ...

We decided to upgrade our hardware (SQL server only) to IBM dual Xeon 2.4G with 2G DDR and also migrade database engine to MySQL 4.x and we achived great performance progress.

Dr. Watson

[]navaho said:
While you're busy working on the board please put the copyright back on. [/]

We'l put it on shortly - during next upgrade.

Alright, lets put it this way. You're violating the license agreement. Please replace the copywrite A.S.A.P.

navaho

aka -


David Dreezer
V.P. Infopop Corporation.

done

Thank you.

[]mario2 said:I still hope some restriction on searches will be implemented, so malicious people cannot overload the server so easily!![/]

The easiest way to diminish search bogdown is to limit the number of rows queried. For example, the first search only looks at the most recent n% of the post table. When they get to the end of the results give them a button to continue searching the rest of the db.

The best solution would be to integrate a real search engine instead of slogging thru the entire database, but it would probably more than double the size of the db, so that might not be an option.