How to optimize MySQL databse for serving 1000s of requests at a time?

For a site like: linksnappy.com

Is it possible to configure to separate MySQL servers into one load balancing server? So if one of them is overloaded switch to the next one.

Same question for the http requests handling server.

Another question : What kind of server do I need to serve 1000s of requests at a time? (http server) You can see the kind of site I’m talking about is a download site. The server just dies when we have too many download requests. We currently have intel XEON quad core 2.4ghz with 4GB of ram.


Let*s start from the end:

we currently have intel xeon quad core 2.4ghz with 4gb of ram.

This possibly is the culprit here. 4gb of RAM is a joke for a quad core, especially if handling thousands of parallel requests. 4096mb RAM by 2000 requests (lower definition of thousands) leaves 2mb RAM per request (ingnoring all else).

You definitely dont havrve a MySQL problem ONLY. The main problem is that you need a complete infrastructure that allows handling many thousand doswload requests per second (or need someone clarifying your post), which means a lot of network bandwith, a lot of IO bandwidth, and a server with a lot more ram than - sorry - a cheap outdated low end workstation (quite like that - my developer workstation had 4gb about 3 years ago, recently moving to 8gb). You also will need an IO subsystem to handle the load, which possibly means many many many discs in a nice hardware RAID controller (forget software here - you want something with more features than your software raid).

  • HTTP Load balancing: easily dable, either in software or in Hardware (F5 has some hardware for that). Depending on your programming (sessions) you may need sticky sessions so the same client ends up on the same box again.

  • First step would be separating out the download files from the actual website (separate sdomain / subdomain) and in the next process mving them to separate servers. The MySQL Database (well, the database - this is in no means specific to MySQL) can go to a separate server, too.

  • 2000 requests per second is not something that needs multiple servers unless the requests are complex, which they most likely are not. The main limitation on that performanc eis most likely the IO side. In your case I bet you couple the 4gb RAM with a low end disc subsystem. To give you an idea of how a db server mayl ook like - my own main databas (storing financial data) has about 10 high speed discs (Velociraptors, 10k RPM) to make sure the database (in my case a SQL Server) does not turn into the bottleneck. I still have performance issues on the IO side... so I will move some stuff to SSD now likely. Technically, unless doing only rporting from in memory (which is very unlikely with your 4gb ram shared for eveything setup) the IO subsystem is the main limitation for databases.

At the end, a lotdepends on your programming. You will not ind a decent answer hwere - this requires a lot of planning, looking at your code.

  • ok I understood most of the stuff.. but what is : IO subsystem ??? And by upgrading the hardware to RAID, do you mean I gotta upgrade the harddrives to RAID system? the server doesnt really need any harddisk space.. It serves the files from a different server (file server, which has too much capacity , its configured in RAID i think) Yes I was planning to use Load balancing, but then ill have to move over to lighttpd (for better balancing than apache2) and about the sticky session, thats my main concern, the script depends on the session. – Bilal Sep 3 '10 at 2:36
  • and yes, the mysql server is on different server as well. – Bilal Sep 3 '10 at 2:36
  • Yes Ill upgrade my ram to 8GB. but which server should I upgrade ? the main site one? mysql one? or the fileserver? and, which server would require loadbalancing? the dload server, main or mysql? Oh yea, all of the servers are at 100mbit connection. except for the download servers(which actually provide the real file), they are probably at gbits+ (rapidshare.com) the structure of the site is like this : Server 1: main site Server 2: mysql Server 3: File server when a user requests a download link, his request is forwarded from Server 3 to rapidshare.com – Bilal Sep 3 '10 at 2:39
  • Then Server 3 provides the user his download file as a response from rapidshre.com Like this : User -> mainserver <--> mysql server <--> Fileserver -> Rapidshare.com -> Fileserver -> User so.. ill probably need to upgrade the network connection to 1gbit on all of the servers right? Ram will be upgraded to 8gb on all of them. And what about load balancing ? and multiple servers ? Thanks for your response :) I highly appreciate that. – Bilal Sep 3 '10 at 2:42
  • No, better upgrade your backbone to 10gbit. Even 1gbit is just 100mbyte / second and you run overhead here. Infiniband was created EXACTLY for this scenario. You should invest in some software to analyse your server loads - seriously. You dont even know which serve rhas which bottleneck. – TomTom Sep 3 '10 at 3:10

The requests per second is magical number and makes no sense. I can serve 10,000 requests per second with web server running hello world and 0.0001 requests per second with some bloatware behind the same web server.

What kind of server do you need? You need a person that knows what they are doing not a new server. But to answer your question 'a big kind'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy