[RESOLVED] Outgrowing a Dual Xeon server, what now?

cadkins23

I know this isn't a hosting forum, but I great such great advice on this site, that I was hoping some of the php mysql experts could tell me their opinions on what I should do.

Here is my problem. I have a social networking site (yeah I know) with nearly 45,000 members. I started the site back in August 2006, about 8 months ago. When there are around 100 members online which is a couple of times per day, the site will crawl, REALLY, it'll crawl and sometimes just break because of the bottlenecking, etc.

I've gone through the code and modified many queries, optimized the database tables, etc. I think the problem is just that I'm asking this single server to do too much.

Besides the social networking script, I also run vbulletin on this same server. When a user logs into the site, they are also simultaneously logged into vbulletin. I understand that some of the larger vbulletin sites require multiple servers. This is why I am thinking it's time for a new server.

I just started to take in about $150+ month in membership fees because I haven't been taking membership fees for very long. Currently I am paying Aplus.net $365/month for the Dual Xeon server. When I contacted them to ask what to do they said get another server. 🙂 I explained that I can't afford an additional $365/month, I just can't do it. They finally said that I can go to do a "premium" server instead of dual xeon and get two of them, but they will charge me $150 setup per server, etc. Just 3 months ago I paid $200 setup for the server I'm on now. 🙁

So if anyone has any really good leads on what is a good affordable/reliable hosting company (with free setup) I'd be interesting in hearing.

The second part of my question is how should I set this up.

I want my site to be extremely fast because I feel that is important. I would prefer to even get a 3rd server since I'm sure that would only help.

Here are some of my options. It looks like I can find some cheap dedicated servers for around $120/month or so. Perhaps I can get 3 of those servers, and split up the site so I have vbulletin running on it's own server, the social networking script on it's own server, and a database server. Or perhaps I can get a couple of $200 servers and not have to pay much more than I am paying now, but have two separate servers. One would be for the database, and the other would be for vbulletin and script I guess. I imagine when I say one database server, that would be for the main social networking site, and the vbulletin database would stick with the vbulletin server?

As you can see I'm quite confused.

I appologize if this is the wrong forum/site to be asking for help in.

I would really appreciate any feedback at all from you guys.

Thanks in advance,

Chris

Roger_Ramjet

Well the first thing you have to identify is where the real bottleneck is. It is as likely to be the network as the server or your scripts. People often forget that web service depends on both server response time AND network transfer speed.

Just had a look at aplus site and on their top-end $300 hosting package they list 100Mb Intel network card as if it is something to boast about: 1 network card on any server is nothing to boast about. What they don't tell you about is how much bandwidth you are actually getting on their backbone; could be throttled down to a very low level like less then 10Mbps. You will have to ask them what the real network-transfer speed is: and get them to back that up with real stats.

If you have full remote control then consider running a network monitor to gather the information for yourself. You should google for help on logging server stats to see where the bottleneck really is when performance degrades so badly.

There are tons of hosts out there, at much more competitive prices than you are paying. You will have to check them out: pay particular attention to the network-transfer speed that they allocate and ask if they allow a higher bandwidth burst for peak loads.

A dedicated server may not be your best option anyway, it could be that a virtual server on a cluster with good load balancing would be a better return for your money.

Roger_Ramjet

Oh yes, and take some time to learn up on caching, it can really help to get that right especially if you have a lot of graphics. If the pix can be cached while the dynamic text content is not then that will have a major impact on performance, especially on a social networking site where folks upload a lot of pix. Let their ISP cache servers take the load off you.

piersk

I agree with the load balancing point, but not with the network card. Here at work all our servers have 1 access point to the outside world (ok, there is another one to an internal network for backup purposes, but thats the only use that one gets), but we load balance like hell. And we can have anything up to 10,000 hits a day. Not sure what the network card is (I think its gigabit).

Is it worth splitting off your database onto a seperate server? Yes I know that means you'll have to get an extra server, but that might be worth a try.

Piranha

I think it is strange that the server have problems when only 100 persons is online at the same time. Let's say that each page is loaded in 1 second when everything works ok, and viewed for 10 seconds before a new page is loaded. The result would then be that only an average of 10 page loads is done simultanously in the peak, and that should normally not be a big problem. It seems to me that it is either to big pages or sloppy coding that causes the biggest problem and cause the page loads to be slow.

You should check your code to see that you don't have any database queries that relies on results on other database queries. For example:

$sql = "SELECT id, username FROM user";
Run the query
For each user
{
    $newsql = "SELECT id, link, text FROM pictures WHERE userid = $row[id]"
}

The example above may look like doing 2 queries. But with 45,000 members it is in fact doing 45,001 queries, the first one one time and the second one once for each member. By using joins this problems can easily be avoided.

Other things you can change in the code is to not allow everything to show in one page. As an example it is much better for the server to list 50 or 100 persons at a time instead of 45,000. Another problem may be when you just want to count something. Many persons use something like "SELECT * FROM table" and then mysql_num_rows to count, with the result that the database have to do much more work than is needed. Use LIMIT and COUNT as much as possible in the queries to avoid these kind of problems.

piersk

I never use

select * from table_name

as you're instantly slowing down the server. Even if it's a pain in the backside to write out all the column names, you're stopping the server having to go to the system schema and looking up all the column names.

Roger_Ramjet

Piersk, my point was that I see nothing special about a 100Mb network card - certainly not to mention it as a feature on your most expensive package. After all who does not use 100Mb cards these days? Now 2 or more cards connected to separate trunks would be a feature.

As you say Piranha, 100 users should be nothing for that box, even with poor scripting. So it is my guess that the bottleneck is elsewhere.

In my experience that elsewhere would be the network. Speeding up the server is going to make no difference if your server is in contention with 100 others in the same collision domain of a 100Mb Ethernet segment. Also in my experience, hosts use intelligent switches to ration bandwidth amongst all the co-located boxes rather than add more bandwidth as they add more customers. Would not make any difference if you had a 1Gb card if the network is rationing you to less than 10Mb effective throughput: and that is very likely to be the case.

bubblenut

I doubt it's the network connection if you're dealing with such a small number of concurrent users, however I would certainly check it out. Another area I would look into is profiling. From here on in I assume that your server is a Linux box and that you have shell access to it. The first step would be to see exactly where you're bound (I/O, CPU, memory, etc). A good tool for looking at this at a system wide level is vmstat which will be found already on all Linux installations.

I don't want to go into too much detail as it could easily turn into a multipage article but once you know where you're bound then you will want to profile Apache (or whatever your webserver is) with valgrind and cachegrind (kcachgrind or wincachegrind), here's a talk which touches on it, to find out exactly where your bottleneck is. An alternative to this is to assume that the issue is directly applicable to PHP (which is a safe bet) and profile PHP directly with Xdebug. Although this is probably a little easier to understand it does have the drawback that you may miss bottlenecks which aren't directly applicable to PHP. Once you have run your profiling and determined your bottlenecks pick the biggest, easiest one to refactor and refactor it. THEN PROFILE AGAIN! One of the biggest mistakes in optimization is to do the initial profile and then fix all the bottlenecks you see, this is very bad as fixing one bottleneck will change the playing field completely.
You may think you've already optimized but seriously, until you've profiled you won't know. I have had code which I've been able to speed up by over 1000% in an afternoon before.

Tools to play with:
Linux Shell:vmstat, valgrind, top
PHP Extensions: xdebug
Applications: kcachegrind (for linux), wincachegrind (for windows)

Roger_Ramjet

All you folk who run your own servers have forgotten what co-located servers are like. It very much can be the network when you do not control that network.

An example from Fasthosts

With unlimited bandwidth rates and our high speed connection, no matter what level of demand is placed on your dedicated server, you'll never receive an additional bandwidth bill.
* You only pay the standard monthly rate for your chosen dedicated server.
* Your dedicated server will experience lightning fast connection speeds at no extra cost.
[COLOR="Red"]* Up to 10 Mbps virtual data pipe – just like having your own leased line.[/COLOR]
* Uncapped download & upload levels - no limits on the data that can be exchanged.

Yes, UP TO 10Mbps. WOW now there's an offer you can't refuse - and it is their top line special no less :rolleyes:

In some places you can be lucky to get 3-4Mbps real throughput from them. Does not matter for your own personal little dev server or blog, but just wait till you get popular. Thing you all forget is that the big cost is the leased line connections to the internet backbone that costs most for an ISP, all the other stuff is dirt cheap these days.

cadkins23

Thanks for all the help guys. I think the problem may not be the network connection, although I'm not 100% sure. They did just upgrade me to 100Mbps from 10Mbps for free of charge after I emailed support and they reviewed my usage.

Roger mentioned a virtual server with a good cluster. I read a site lastnight (hosting site) that said they were better than dedicated and mentioned about server clusting across their entire network or something. I opened a live chat at 1am and the guy offered me a package for liek $60 and I said that I pay $360 now and would that be enough hosting, he said they didn't have dedicated yet, so I closed the chat. I guess he could've explained a little to me.

Also I will try to use some of these tools, I was previously using: mysqladmin -u youradul_dbadmin -p processlist which shows mysql bottlenecks. Ok I am running late for work thanks guys I'll review this again when I get home.

Chris

Roger_Ramjet

cadkins23 wrote:
Thanks for all the help guys. I think the problem may not be the network connection, although I'm not 100% sure. They did just upgrade me to 100Mbps from 10Mbps for free of charge after I emailed support and they reviewed my usage.

Look, it does not matter what card they gave you, the card can only serve data as fast as the network will accept it. What matters is the throughput of the slowest network segment your connections have to pass through. That is why they are called BOTTLENECKS. Think of it like the rush hour when all that motorway traffic hits the narrow city streets, the traffic just backs up along the motorway and everything comes to a grinding halt.

Monitor your server and 10 to 1 you will find it is spending most of its time waiting for the network, the IP stacks will be full and loads of packets will be being dropped or resent because they got no ACK - if you don't know what SYN n ACK is then go learn about IP and Ethernet.

Of course if you know better then fine, spend your time optimising the server so that it can wait even more time for the network.

Piranha

cadkins23 wrote:
Thanks for all the help guys. I think the problem may not be the network connection, although I'm not 100% sure.

Then test it so you are 100% sure.

dalecosp

Have you looked at Pair Networks (www.pair.com) or New York Internet (www.nyi.net) ? Both of these typically have some of the best connectivity in the Netcraft surveys....

crux_op

I ran into this same problem with a site I support. Just like yours its a social scene which means user-uploaded content + every page is dynamic.

After every DB query seemed optimized, and I could go no further with optimizing the PHP itself (short of redesigning the entire backend), I decided to profile the system by breaking it down into modules:

1) Database
2) Dynamic Page Creation
3) Static Content - Images, CSS, JS files etc.

I found that the DB was not my bottleneck. True, when there were between 200-300 concurrent users, each user caused at least one DB query and usually 30 queries on every page but MySQL can easily handle that number if it isn't sharing the CPU.

The real issue was the cost in generating the page after the database was touched.

Most of what I considered dynamic pages, generated in PHP, were really equivilent to static content. Here are some examples:

1) After a forum post is more than 3 weeks old, it hardly ever received a new post even though users would visit the page.

2) The frontpage was dynamically generated, but only changed once a day by staff.

3) The image gallery (200,000+ images) would only be updated periodically, and some sections hadn't recieved new content in years.

My solution was to cache all of those files into static HTML. Then instead of rebuilding the page on every hit, PHP would just grab the static file from cache and display it. My load went from 8 or 9, on a Dual Opeteron at peak hours, to between 2-3.

I also enabled the MySQL query cache. I didn't see good results from that initially. The problem was that when a forum post was viewed, it add update the POSTS table in the database to show +1 view. That pulled the table from the query cache.

To solve that I separated the frequently written columns in the DB from those infrequently written, then did a JOIN when I needed to use the values from the separated tables.

Lastly, I enabled a cache for the PHP byte code. eAccellerator is what I used first, but it broke once I began coding in PHP5. Now I use APC.

Summary:

Cache your dynamic pages. Invalidate the cache when the pages change.
Cache your SQL queries. Separate frequently written data into its own table to prevent cache thrashing.
Cache your PHP byte code. There's no reason to re-interpret it unless its modified.

cadkins23

Thanks everyone for your posts. I wasn't trying to imply that I know better, or else I wouldn't be here. I was only gathering this because I was noticing this lagging/bottlenecking problem when my previous coder made a lot of changes to the code on my site. I thought the traffic was increasing was the problem, however later I fixed some of his very buggy code and added some indices to the tables and found we were finally able to get back up to 100 people again before really noticing the problem. I still notice a lag, but it is when fewer people are online.

After work I have been trying to use vmstat. I receive the following message:
(As you can see I ran this repeatedly to get a range, however only 80 people were online at first, then dropped down to 65 towards the end)

# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 3  0     72 173912 103432 509128    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
16  1     72 124092 103512 552988    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 5  0     72 100296 103712 510928    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 3  0     72  94468 103712 511448    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
18  0     72  76364 103712 511448    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
12  1     72  74704 103724 511956    0    0   104    90   79    58 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
10  0     72  75540 102868 520092    0    0   104    90   79    59 54 12 30  4
# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 9  5     72  58016 102716 585504    0    0   104    91   80    59 54 12 30  4
# valgrind
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 6  0     72 127948 103984 529896    0    0   104    91   80    60 54 12 30  4
[root@66-226-77-30 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
22  3     72  88168 103912 513848    0    0   104    92   80    61 54 12 30  4
[root@66-226-77-30 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 7  0     72 151408 100320 539020    0    0   103    94   81    64 54 12 30  4

I think I will do some research to find out what this means here, but I'd still like to do some research to find out about what Roger was saying. I have googled and done tons of research, but my site has been slow for months, during peak hours, or should I rephrase and say while many users are online.

When I use mysqladmin -u db_uname -p processlist I get a nice breakdown of any delayed processes, etc. Prior to creating many desperately needed indices, this list would sometimes be longer, now it is usually relatively empty. Sometimes with a lot of users a bottleneck appears and you'll see processes stalled for minutes at a time. Normally the processes all just go through quickly, except when around 100+ people are online, then there is an increased chance of crashing. Yesterday I saw 117 people online, and then it crashed. 🙂

cadkins23

Here's top, several times to show a range: This was done when 80 people were online. The problem pokes its head at around 100 people although the site is still slower than it once was at 80 people online.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                       

11098 mysql     15   0  193m  83m 4320 R 99.9  4.1 498:47.19 mysqld                                                                                                                                                                        

 8783 apache    15   0 34108  14m 4492 S 15.9  0.7   0:11.49 httpd                                                                                                                                                                         

28035 apache    16   0 33892  14m 4612 R 12.0  0.7   1:01.15 httpd                                                                                                                                                                         

 9451 apache    15   0 33264  14m 4472 S 10.8  0.7   0:07.85 httpd                                                                                                                                                                         

 9480 apache    15   0 30732  11m 4312 S  9.6  0.6   0:07.62 httpd                                                                                                                                                                         

10314 apache    15   0 31640  12m 4344 R  8.7  0.6   0:03.72 httpd                                                                                                                                                                         

 9462 apache    15   0 33160  13m 4364 S  8.4  0.7   0:07.23 httpd                                                                                                                                                                         

 6565 apache    15   0 33160  14m 4532 S  8.1  0.7   0:18.91 httpd                                                                                                                                                                         

 8784 apache    15   0 31640  12m 4376 S  8.1  0.6   0:12.42 httpd                                                                                                                                                                         

27569 apache    15   0 34236  15m 4556 S  7.5  0.7   1:14.21 httpd                                                                                                                                                                         

 8782 apache    15   0 31976  12m 4408 S  7.5  0.6   0:09.88 httpd                                                                                                                                                                         

 9478 apache    16   0 30648  11m 4288 S  7.2  0.6   0:07.71 httpd                                                                                                                                                                         

10095 apache    15   0 33148  13m 4348 R  6.9  0.7   0:04.94 httpd                                                                                                                                                                         

 6195 apache    15   0 32436  13m 4360 R  6.3  0.6   0:21.50 httpd                                                                                                                                                                         

10254 apache    15   0 31768  12m 4424 S  6.3  0.6   0:04.85 httpd                                                                                                                                                                         

32552 apache    15   0 33152  14m 4544 S  5.7  0.7   0:47.49 httpd                                                                                                                                                                         

 8781 apache    16   0 33152  13m 4392 S  5.7  0.7   0:11.09 httpd                                                                                                                                                                         

 7779 apache    15   0 31980  12m 4392 S  4.5  0.6   0:14.92 httpd                                                                                                                                                                         

30100 apache    15   0 34020  15m 5020 R  4.2  0.8   0:57.10 httpd                                                                                                                                                                         

 7030 apache    15   0 33256  14m 4432 S  3.9  0.7   0:17.39 httpd                                                                                                                                                                         

 9479 apache    15   0 31648  12m 4444 S  3.9  0.6   0:07.46 httpd                                                                                                                                                                         

 7201 apache    15   0 32148  12m 4392 S  2.7  0.6   0:18.83 httpd                                                                                                                                                                         

25376 apache    15   0 33272  14m 4984 S  2.4  0.7   1:18.59 httpd                                                                                                                                                                         

28627 apache    15   0 33160  14m 4596 S  2.4  0.7   1:02.39 httpd                                                                                                                                                                         

 3332 apache    16   0 32556  13m 4520 R  2.1  0.7   0:37.93 httpd                                                                                                                                                                         

 9463 apache    15   0 32360  13m 4360 S  2.1  0.6   0:08.84 httpd                                                                                                                                                                         

 7181 apache    15   0 32044  12m 4528 S  1.5  0.6   0:17.60 httpd                                                                                                                                                                         

10113 apache    15   0 30672  11m 4444 S  1.5  0.6   0:05.28 httpd                                                                                                                                                                         

 6568 apache    15   0 33160  13m 4420 S  0.9  0.7   0:19.06 httpd                                                                                                                                                                         

10414 apache    15   0 30736  11m 4264 S  0.3  0.6   0:03.76 httpd

If anyone has any more tips on what I can test or help me analyze things, please let me know. I do read and search for information, however I'm not having an easy time getting this problem figured out so I do appreciate everyone's help and I am willing to try various avenues.

cadkins23

crux_op wrote:
Summary:

Cache your dynamic pages. Invalidate the cache when the pages change.

Cache your SQL queries. Separate frequently written data into its own table to prevent cache thrashing.

Cache your PHP byte code. There's no reason to re-interpret it unless its modified.

Currently when someone uses BROWSE, they will have 30 images load dynamically, so this is something I've been thinking about for some time. Perhaps this is the main problem. 🙂 I'll have to research more about caching SQL queries, thanks. Also I'll have to learn more about caching my PHP byte code. 🙂

Thanks, this sounds like it could be of some use!!

Chris

bubblenut

Roger, I think you're being dangerously myopic. I'm perfectly willing to accept that the issue could be the network, but the point is knowing rather than guessing.This is where profiling comes in, it's a vital first step no matter how sure you are of the cause. Remember Donald Knuth's famous quote "premature optimization is the root of all evil."

cadkins23, a number of things, vmstat can be passed an integer argument which will be the seconds gap between each call to make life a little easier. Looking at the results your application seems to be largely user space CPU bound. Looking at the output of top if the most revealing of all, mysql is topping out with 99.99 CPU, so it's basically hogging one of your cores completely. There are a number of things you can investigate to improve preformance. Check that you have the query cache turned on with an appropriate amount of memory, if you still have problems look at turning on the slow query log to track the slowest of queries. The MySQL manual will have information on both of these features.

Piranha

And of course be sure that you have indexed the apropriate columns.

Weedpacket

cadkins23 wrote:
This was done when 80 people were online. The problem pokes its head at around 100 people although the site is still slower than it once was at 80 people online.

Are you saying that it has slowed down over time? That it used to be faster when 80 people were on line than it is now?