-1

I have a web application. bought a pretty strong server for it. Server specification is below: CPU: DUAL E5-2620 RAM: 32 GB DDR3 HDD: 2 x 300GB SAS 10K Network: 100mbps/10TB internet.

WebServer Specification: Apache/nginx/PHP Database: MySQL

Now my users are getting high day by day. My server is not tolerate such this pressure. and waiting time is getting high. So I thought about separating MySQL and Web server on two identical server to balance cpu load. Now I found out with separating mysql and php, I will have another below problems!

  • Waiting on network transferring: I have such this MySQL query: "SELECT * FROM table" If the table has more than 500MB data. With my connection speed will take several seconds to get such table over network. and it's only for one user with one connection but it multiplies by 10 connections from one user and more than 100 online users.

Above example is just an example but real data shows below info for 100 online users at the same time:

Traffic + ø + per hour

Received + 14.7 GiB + 201.4 MiB

Sent + 429.4 GiB + 5.7 GiB

Total + 444.2 GiB + 5.9 GiB

So it seems separating is something impossible for my web application. Now I am stuck what to do to balance load onto these two servers without having such this big issue. What can be alternative way to balance my cpu load without have latency problem (at least with less problem)?

*****notice: query is an example. my queries are enough optimized but my application is so much more complicated hence have very much interacts with database. But mysql info shows still 6GiB data transferred.

Improve this question
5
  • Start by fixing those absurd queries.
    – Michael Hampton
    Jan 13, 2014 at 23:07
  • My queries is are optimized enough. it was an example.
    – Pejman G
    Jan 13, 2014 at 23:10
  • 3
    Bad examples don't make for good questions.
    – Michael Hampton
    Jan 13, 2014 at 23:11
  • Is that traffic example showing us feom the webserver? The sql? Or both still on one box? Have you actually seperated them or are you just planning to at this point?
    – Grant
    Jan 13, 2014 at 23:12
  • This is only MySQL traffic usage.
    – Pejman G
    Jan 13, 2014 at 23:21

1 Answer 1

Reset to default
1

If you optimized the webapp to not use rediculously inefficient SQL queries you could probably put them both back on the same box with much lower load. There is no reason every user should be reading the entire table each time. Proper queries and indexes will probably provide an enormous boost here - far more than just throwing more hardware at it.

Failing that they make ethernet adapters up to at least 10Gb/s now. Even faster for some of the more exotic types. A faster link between the servers will help. But not as much as proper SQL queries.

Improve this answer
6
  • As I said this is just an example.My Queries are optimized enough,Not 100 percent but would not better than that possible at the right now. However you can see still my website have 5.9 GB transfer data from mysql. this is my main worry to think about alternative way.
    – Pejman G
    Jan 13, 2014 at 23:07
  • @pejmang Is there a good reason for that 6gb/hour? Are video files being stored in the database or something? Start by explaining why such a large amount of data between the web and sql servers is reasonable for your use case and we can go from there.
    – Grant
    Jan 13, 2014 at 23:10
  • My application is very complicated and 6gb/hour is normal thing. it stores users data 90 tables every milliseconds will be updated. you may think about facebook by each click facebook needs to get some parameters for users "people may you know" , "comments" and ... many of this data should exchange with webserver and database to be analyzed.
    – Pejman G
    Jan 13, 2014 at 23:19
  • 1
    @penjmang Ok then. The second part of the answer stands - upgrade to gigabit, 10gb, or higher between the two servers. You may even be able to use a direct cable between the servers to avoid buying a 10gb switch for now.
    – Grant
    Jan 13, 2014 at 23:22
  • 1
    Then you start scaling out to multiple webservers accessing multiple sql servers with replication between them
    – Grant
    Jan 13, 2014 at 23:30

Not the answer you're looking for? Browse other questions tagged .