Need advice for server upgrade. Please?

[skuba]

Hi,
Our traffic is going to increase a lot once we start our next online marketing campaign. We want to get prepared to avoid getting the server down and assuring a good server performance.

Our current specs are:
Intel Pentium 4 Processor, 2.6GHz Hyperthreaded (Basically acts as a dual processor even though there is only one)
2GB of RAM
Windows 2000 SP 4
Dual 30 GB Hard Drives
10/100 Gbps Network Card
Currently also running SQL 2000 SP3

On a daily basis we get an average of 10k unique users and 150k page views. The number of simultaneous users range in average between 300-600.

This number will grow.

Our main problem is when we send promotional emails to our subscribers.

Currently we have 100k opted in subscribers, but when I send the emails I have to send 25k at a time, so we get around 2000 simultaneous users. The site doesn't go down but it gets pretty slow.

What specs would you guys suggest so that the server can take at least double what he takes now. Like around 4k simultaneous users?

Thanks for helping.

[txbakers]

You already have a kick-*** server in place.

You need to move your mailing to a separate server now. With that much traffic, you want to keep your services separate.

[skuba]

The mailing is not in the same server. We use a third party service. Sending the mail is not the problem. The problem is the response to the email. So the problem is in our server.
It may be kick *** server, but it's still not good enough.
Thanks

[edited by: DaveAtIFG at 5:33 pm (utc) on Mar. 23, 2004]

[txbakers]

If your server can't handle 2000 concurent users then something must not be configured correctly.

Your specs should easily handle the traffic.

[skuba]

It handles 2000, but gets slow.
Where do you think could be the problem?
Thanks

[richlowe]

Question:

1) are you running SQL on the same server?
2) what is the speed of the disk controller?
3) is it all on one disk or are you using RAID 5 or 10?
4) I assume you are using IE5.
5) Are you using ASP, Perl, or other scripting languages?
6) Is your server doing anything except serving web pages?

Richard

[richlowe]

I would typically:

1) Split it into two servers, one for web and one for SQL.

2) Install a gigabit network card in each server which is only used for communication between the web server and the sql server (dual homed systems).

3) Install a 360mb disk controller.

4) Install a 10-disk raid-10 array of 15K SCSI disk drives (raid 10 is a mirrored stripe-set).

5) Get a third-party product for compression so the HTTP requests are smaller.

6) Ensure that I've gone through the NSA security manual and am compliant with their recommendations.

7) Don't use perl or other non-windows based scripting solutions

Of course, this assumes a relatively large budget...

Richard

[richlowe]

(Basically acts as a dual processor even though there is only one)

Sorry, not true.

If you want two processors you have to get two processors.

Richard

[richlowe]

If your server can't handle 2000 concurent users then something must not be configured correctly.
Your specs should easily handle the traffic.

Not with SQL installed on the same server. That's the bottleneck.

Richard

[skuba]

We're working on getting a clustered solutions with like 4 web servers and 1 for SQL. It will cost a lot but will be able to handle 4x times traffic.

I might be getting a quote and specs today. Woul dyou guys like to see it and give your opinion?

Thanks

[bakedjake]

with like 4 web servers and 1 for SQL

Have you identified your bottleneck?

Often HTTP processing isn't the problem, and if it is, it's a code (not a server) issue.

Before you spend that sort of $$$, make sure you profile your code and SQL queries.

[skuba]

Well, I believe the main bottleneck is the fact of having SQL and HTTP in the same server. Isn't it?

We are looking into getting 4x the traffic we have now. We could have 8k users simultaneously. I think we need a big long term upgrade.?

[bakedjake]

Isn't it?

Is it? It worries me that you don't know! If it turns out to be a code problem, you may be wasting your money.

Profile, don't speculate.

[skuba]

I don't know because I am not the server guy. As I said before, the server is managed by the development company with the people where the server is colocated.
They are working on getting a quote of the clustered solution. They know about servers, I don't. I know about HTML, email marketing, SEO, etc...

I can't get that many details from the devopers. I wish I knew more to tell you.

[percentages]

I have servers (old ones) running at half your speed and they can still cope with your current traffic levels, SQL included, no noticeable slow down, even on high speed connections.

I think your problem is most likely with the network you are on.

My advice is look at superior networks and then load balancing. 8K simultaneous users is a decent load if all are hitting one DB. You will need a good network and good config to cope with that......but I guess money is no object?

[bakedjake]

I'm not discounting the fact that it could be SQL on the same machine - please don't get me wrong.

But, as tx mentioned, you have a pretty decent server in place. That box should easily handle 4m/month page views with little problem.

And, unless you're doing crazy, crazy SQL stuff, it should be okay. You shouldn't be seeing drag.

My recommendation: Pick up a copy of Smartline's Remote Task Manager, get the development company to install it, and see if you can't identify the bottleneck. If if really is SQL Server, it will be obvious in 10 seconds of research with RTM.

From there, it gets a bit tricky. If SQL Server is hogging CPU time, it could be the amount of data or it could be the complexity of queries. Often, queries are not nearly as optimized as they should be. I've seen performance increases on the scale of 10x-15x because of straight SQL query optimization.

If it really is the amount of data, or you really do need those complex queries, then moving SQL to its own machine will help. But I'd investigate first.

I think your problem is most likely with the network you are on.

4m/month pageviews = About 256k sustained network bandwidth. Unless the network really sucks, I don't think that's the problem.

It smells like a SQL query problem to me.

[skuba]

Hey everybody,
thanks for all the input.
I considered some of the ideas you guys gave and I also answer your questions below.
See if what you think of that. Thanks.

1)Yes, SQL is running on the same server. We did that as an emergency solution when we were having trouble with the server in late January. We've been of the opinion that any upgrade would move that off the Web server to it's own box.

2)& 3)We're not using raid, just two separate hard drives. Each drive is 72000RPM. Going to raid or just faster drives would help, but I don't think it would be enough for my long term goals.

4)I think he means IIS5, which we are on. Browser versions should be a non-issue here.

5)The whole site is in ASP, which is a very database driven scripting language. It is possible to gain some performance with making sure database connections are clean and streamlined. That is what we did yesterday.

6)Yes, the server acts as an FTP server and a LiveStats server. These functions are necessary to send orders to your HP, to upload code to the Web server, and to give a way to measure traffic. These features really need to stay, but in and of themselves do not consititute a large performance loss.

My server guy is all for breaking the SQL server onto it's own machine.

Gigabit network cards are a good option as well, though we'd have to make sure our host can support this. I'm also alright with a raid configuration, though I don't think we'll gain as much here as we will by separating the two servers. We also don't gain much in the way of fault tolerance as the code and the database are backed up off-machine in two locations already.

If you're interested in what programs are taking up cpu time I can give you an idea. From watching the server, SQL takes anywhere from 10% to 75% of the CPU at any given time. This probably reflects the database intensive code that the site is written in. Like I said above, we've gone through and cleaned up a lot of the SQL queries, but as far as overall performance I think moving the SQL server to a seperate machine is the way to go.

All of this still doesn't address the goal of 4 times the traffic volume I would like to see. To get to that level I think we're going to need a clustered solution, but again we have some people putting together a proposal for us that will have several options.

[bakedjake]

... clustered solutions with like 4 web servers and 1 for SQL

I think we're going to need a clustered solution

How did you come to that decision?

You just wrote that SQL can run up to 75% of your CPU... so you're going to add more WEB servers?

That's not the answer, kemosabe. Keep workin' on those SQL queries. Are you caching data from commonly used queries?

[skuba]

How did you come to that decision?

So, do you think we can have 8,000 simultaneous users running only 1 http and 1 sql server?

I guess the clustered solutions is also good for the case of 1 server goes down, we still have others. Not?

Thanks

[bakedjake]

I didn't say that, but, you've admitted that your bottleneck is SQL. 8000 simultaneous isn't insurmountable with one machine, man. There are tons of single machines doing this right now.

It may not be possible to have a single machine running your app - that's true. But before you get ICD (immediate cluster disease), make sure you know what to cluster!

(One of my production Win2K servers has 6300 simultaneous right now. 4 GB RAM, 2x PIII Xeon, and it runs SQL Server)

[onlineleben]

I am not the expert on machines, but read a lot.
One thing that I came across was putting the logfiles on a separate disc, so that when you have lots of traffic accessing the site the read processes (for code and db queries) are not affected by writing the logfile entries. Don't know if this works in your IIS system.
And as already said, optimize code

Good luck

[skuba]

Bakedjake, interesting. The only caveat is that we may be comparing apples and oranges here. What does your Web server do? Is it an ecommerce site? Does it take 5,000 orders a month? Is everything on the site dynamic ASP?

I'm not saying you're wrong, just to take things in perspective.

Thanks

[skuba]

What do you guys think of getting the following clustered solution.

- Load Balancer
- Cisco firewall
- 3 web servers Pentium 4 2.4 ghz with 1gig memory
- 2 SQL servers with Pention 4 duel Xeon with 2GB ram

The hosting would also include:
1.Off site backups.
2.Redundant connections to the Internet backbone through six different providers. Guaranteed 100% network uptime, no packet loss, and the lowest latency in the industry
3.Fully monitored site. Monitoring includes not only the web site, but the database and the order transfer process to the HP. There is someone on site 24 hours a day for maximum response to hardware issues.
4.Full diesel powered backup power generators.
5.Full web site and order transfer support for bugs and downtime.
6.Full management of the servers for performance, operating system patches, firewall protection, etc.
7.Includes 300 gigabytes of outgoing traffic per month.
8.Full traffic reports available. You will be able to see how your web site is doing in real time.

They want $3800 for setup and $6300 monthly.
They estimate we can handle 15000 sessions with this solution.

Are they asking too much? Can we handle 1500 sessions with a smaller and cheaper solution?

Thanks a lot for your time.

[digitalv]

My suggestion to the original poster is to evaluate not only how you're executing your SQL queries, but how everything is laid out in the database. Do this BEFORE you buy any hardware - you may find out you don't need it.

First, look go to Task Manager and see what's taking up the CPU. You'll probably find it's SQL, not IIS. Assuming that's the case, evaluate how you're storing/retrieving the data.

First, check the simple stuff - your code. I assume you're using ADO? Make sure you're properly closing your connections. When you're doing SELECT statements, don't do "SELECT *" - always specify the field names you want to retrieve, and use the NUMBER when retrieving the recordset, not the name:

ex: rs(0) instead of rs("field1"), rs(1) instead of rs("field2"), etc.

Second, if you need to use a field from a recordset more than once on a page, store it as a STRING by doing stringName = rs(0). Don't keep using rs(0) because every time you do that you are querying the database again. If you use rs(0) 5 times on a page, you're querying the database 5 times whereas if you save it as a string, you're only doing it once. When you're done with the strings, set them to nothing.

Third, if you only need to get ONE value from the database - something like a count or a "SELECT TOP 1" type of thing, then simplify the query. Use this:

stringName = con.execute("select top 1 whatever from wherever")(0)

Don't go through the whole ADO set rs = crap just to get one result, that will kill the server.

Use numbers whenever possible to avoid doing text queries. Use char and varchar when you're working with a small amount of text, since it will query faster than text or ntext fields.

One example I like to use is what many e-commerce website owners do, they record the TEXT status of a transaction in the database. So if they've made 1,000 sales then there are 1,000 records in the database for the sale and one of them is a text (or text type) field that says "approved" or "declined". Stuff like that is a waste of space and will slow down queries. The proper way to do it is use a numbering system - make a table of all of the text messages that could be there and assign a number to it. Then just write the NUMBER to your database. Numbers are selected, inserted, and updated faster than text, and take up less space in the DB. The physical size of the database will also affect performance because your system only has so much RAM - you want to have more RAM in the system than the size of your database if possible, that keeps the entire DB in memory and makes it run a heck of a lot faster.

High performance databases come by designing them properly, not by having a computer more powerful than God. I have a SQL 2000 Enterprise server running that receives over 300,000 complicated INSERTS every day (a stored procedure that inserts data into multiple tables only after verifying it against MILLIONS of rows of existing data) - it's kind of a complicated procedure that when I first wrote it, I was using up 80% - 100% CPU resources on that server and it was slow as crap. After optimizing the table structure and the query itself, I'm running at an average of 10% with occasional spikes into the 30% range. Same hardware, dual 800 Mhz. It's all in the design.

Check out www.sql-server-performance.com for more tips in this area.

I strongly suggest doing this first - you will ultimately want to go to another server, especially for the type of traffic you're talking about. But moving a poorly designed database to a faster server is still going to get slower and slower as time goes on. I don't think hardware is your problem.

[onlineleben]

Just popped this to the top because I am curious about the outcome of this adventure.
Skuba, did you go the expensive way or did you use the optimization tips offered in this thread (and did they help in getting a better performance).

[Macro]

I've only just seen this thread.

Skuba, I'm no expert on SQL, load balancing or IIS. I do know my hardware though. So do get back with some more info on what you're being quoted for and I'll have a look at it.

30 GB hard disks are unlikely to be 7200 rpm (72000 doesn't exist) but there were a few IDEs disks from IBM (and I think Seagate) that were 30 GB in size with 7200 rpm spindle speed. I'd recommend going for an 80 GB. You don't need the size? Maybe not, but some 80 GB disks come with 8 MB of cache and that does speed things up. A RAID 0 configuration will get you another 5-10% on hard disk performance.

With the Xeon CPUs check that they aren't 400 MHz FSB (there are still some of those floating about). The price difference between a 2.4 and a 2.8 is only about £40 so if you find your CPU is being tied up a lot then that upgrade makes sense. What speed is your RAM? Servers use the more reliable ECC RAM which is slower than the standard RAM anyway but do go for the fastest ECC RAM your motherboard support. What mobo is it anyway?

[plumsauce]

Our main problem is when we send promotional emails to our subscribers.

Currently we have 100k opted in subscribers, but when I send the emails I have to send 25k at a time, so we get around 2000 simultaneous users. The site doesn't go down but it gets pretty slow.

I think the problem lies in the above paragraph
from message 1.

How come the user sessions spike when sending
the emails? This is a server side process
that should spawn at most one additional
session *if* it is triggered from a web page,
and *none* if it is a scheduled job.

Doing the math using 8 hours as representative
of a full day, yields 5.3 page views/second.
Or, 318/minute. Any kind of decent server
will do 1000+ TPMC on SQL server. The benchmarks
are typically in the 35K+ TPMC range.

The server should be loafing.

BTW, you can do 400Mbps sustained out
of a single quad xeon with a single
gigabit nic. And I think it could be
done with a dual. CPU utilisation
is about 35% with all the nic
acceleration options turned off.

+++

[skuba]

It's not sending the emails that spike traffic my friend. It's the response to the emails. We have a high response of average 35%. Our Database has 100k subscribers. So.....