Max traffic on a web server

[bobb]

OK, Windoze 2003 server. Plenty of oomph and using MySQL.

Just recently we've started having people moan about stuff. Stuff I need to fix. One site is a recruitment site where people can upload CVs and apply for jobs etc. Another is a commerce site which gets fairly heavy traffic and lots of orders. One of my colleagues has also written a Windows app which taps into the database on the web server. A few other things going on too. The load on the database is massive.

People are on and off emailing me with problems they're having using the various sites. Obviously I catch all errors and give nice friendly message, but it's starting to get a bit much and I obviously need to fix it.

I never used to have this problem and I can only assume it's down to the massive amount of traffic.

Please don't say "Get a Linux server" because I can't - I'm bound by my clients. The commerce site is an old skool asp app and to be honest has always been pretty robust with very few problems. All other sites are .NET and the Windows app is C#.

All I can think of is that the database is being absolutely hammered and just can't "keep up" to use technical terms 

This is starting to cause me sleepness nights and I really don't know what is causing the problems.

I'm a code monkey and have never really had much interest in servers. But it's clear that it's about time I did!

The question I have is that I really don't know how much abuse a Windows web server can take? Or more importantly a MySQl database server. Am I asking too much of it? Do I need to get another server to spread the load?

I am woefully unprofessional 

Cheers....

[Greenbank]

Database optimisation and efficiency is a massive subject.

For the simple/shallow approach just google "mysql optimization" (yes, with the US spelling) and read up on some of the bits.

For a more in-depth look, the areas you need to consider are:

1) First, find out where the bottleneck(s) is/are. Is the machine CPU-bound, memory bound or I/O bound?
2) Inefficient/Unoptimised Queries - Log them out and either fix them or add appropriate indexes. Learn to use EXPLAIN and ANALYZE.
3) Mysql setup - Threads (and cpus), cache sizes, separate databases?
4) Hardware setup - How many HDDs are there in the server? Are the OS and the data sitting on the same single HDD? Possible RAID striping of data drives? Would moving the DB to a separate machine help? Would splitting the sites onto separate machines help?

[Valiant]

The other option which is more costly is more servers and load balance.

But first, where is your server hosted and what is the connections into it?

[TimO]

The other option which is more costly is more servers and load balance. ...

But before that, it would be very sensible to consider that at least some of Greenbank's questions need to be answered.

ie if the bottleneck is in the MySQL server, then you would need multiple database servers (and some mechanism to distribute the load across them, as well as how to update them if necessary), if the bottleneck was in the web server, then the opposite would be needed (although this is a less likely outcome).

From previous discussions I've read about this sort of thing, often the queries against the SQL server can be putting a large, and unnecessary load on the server, so carefully looking at those, and optimising them can solve a lot of problems.

[Valiant]

Agreed, the last time I was having major errors it was down to a recursive error in the sql, the data was being gather again and again and again until the server panicked.

[bobb]

Cheers for the advice - I'll look at some of GBs wonderfully geeky questions first thing tomorrow....

[Greenbank]

Simply put, if you've no idea how to monitor how much of a performance hit your stuff makes on the server then, more than likely, it's making a reasonable performance hit because it's not very efficient.

You should be able to pull back at least 50% if it's a poorly looked after un-indexed database running with the default settings.

Embrace the geeky shit. It pays well.

[bobb]

OK, nice one GB - I've looked some of that stuff up and have had a few ideas on how I can implement certain things to get a better angle on this.

I guess the problem I have is common - I work for a small company and therefore have to be a jack of all trades and master of none. If I had my way I'd sit around coding away, occasionally looking up to gawp at the remarkable tits of the girl in accounts and not have to bother myself with anything I don't know much about...... but there you go.  Cheers.....

[Greenbank]

I guess the problem I have is common - I work for a small company and therefore have to be a jack of all trades and master of none.

BTDTGTTS. To be fair though, I kind of prefer the seat of your pants type stuff to big corporate bloat/bureaucracy.

Good luck and enjoy...

[chris]

I have often found that one or two poorly coded queries can cause severe problems for other database users. Luckily the way my code is structured I was able to easily build in a timer for all queries, and I modified my database access components so that they logged any queries that were taking more than a couple of seconds. Looking at the log files I was able to find the culprits and take corrective action. I then tightened the parameter to about half a second and found more, smaller problems that I was able to fix.

[rae]

1) First, find out where the bottleneck(s) is/are. Is the machine CPU-bound, memory bound or I/O bound? 

I'd add one small caveat to this sage advice.   It is quite possible to have a poorly performing server that is not CPU, memory or IO bound at all.   Indeed looking at these reveals a placid server that is having no trouble keeping up with demand at all....because you're not actually asking it to do anything. 

By way of example, let's say that you have only a single process in your database - i.e. your database can only do one thing at at time.   Jobs are picked up in the order in which they come in.   When the database is running flat out, doing its one job, it is not overly pasting the CPU or IO - but all the other jobs are queued up, waiting for a chance - and all of the other users are pissed off.

It may be the database, it may be your app server, it may be anything - but if you don't see anything obvious like a pegged disk or CPU, then search for the bottleneck.   If you have a spare month or two, google "parallelism".....

<war story>

I once had to look at an Oracle problem on a Sun E10K, when an E10K was the mutts nuts.  64 processors, 128 GB memory, big EMC array sitting next to it.   The application was running like a dog, they had just moved off the Dev kit, and performance had not improved one iota.   Superficially, the machine was not busy in the slightest - 1.5% busy at peak.  1.5% - or roughly 1/64 busy, I didn't even need to look at it, but I did anyway.   One CPU was absolutely pegged, the others were dead idle.   They'd written a single threaded application....

</war story>

[nuttycyclist]

Last time I had performance issues on a sql database, performance monitoring showed it was the Hard Drive speed that was causing the issue.  The query running was having to wait for the HDD to catch up (mind you, this was a 130gb database with data all over the place and the main table wasn't indexed).

Indexes in the database.  Are they there and correct?

It's a windows server.  Is it running anti virus, and if yes is the anti-virus configured correctly to not scan "safe" things on access - e.g. the database file, the mysql executables, and any other thing that gets multiple hits per second as the database works.

[vorsprung]

OK, Windoze 2003 server. Plenty of oomph and using MySQL.

you say "plenty" but that is all relative

Just recently we've started having people moan about stuff. Stuff I need to fix. One site is a recruitment site where people can upload CVs and apply for jobs etc. Another is a commerce site which gets fairly heavy traffic and lots of orders. One of my colleagues has also written a Windows app which taps into the database on the web server. A few other things going on too. The load on the database is massive.

People are on and off emailing me with problems they're having using the various sites. Obviously I catch all errors and give nice friendly message, but it's starting to get a bit much and I obviously need to fix it.

I never used to have this problem and I can only assume it's down to the massive amount of traffic.

Unless you have figures of numbers of complaints vs. volume of traffic to the site, then you cannot assume this.  The problems maybe due to changes you have made

The question I have is that I really don't know how much abuse a Windows web server can take? Or more importantly a MySQl database server. Am I asking too much of it? Do I need to get another server to spread the load?

I don't understand why, if you are a PoB (Prisoner of Bill- a tied in user of MS products) you are using MySQL

I am woefully unprofessional 

Nah, your bosses have given you too wide a remit and no training.  Don't beat yourself up.

Here are some things you can try to speed things up

1) look up mysql logging documentation and how the logging works
2) use the logging to understand the read vs write use of your database. This can help with tuning and design.  For example, if there are lots of writes then adding indexes will slow the database.  If there are lots of reads then adding indexes may speed it up
3) now you this insight, read up on tuning parameters ie how much memory is reserved for index caches
4) use the logging feature of "slow query logging" to see if there are any absolute stinkers of queries you can get rid of or spend time simplifying
5) get quotes from your ISP on a) a front end caching accellorator b) adding extra RAM to the server.  Both of these should help performance without any changes having to be made to the software on the server.  Or drastic changes like a move from one host to another.
6) look at the over all memory usage on the server.  This page SolutionBase: Improving Windows Server 2003 memory usage seems to explain some of the key concepts, like page faults and how to monitor memory usage

[ScumOfTheRoad]

Re your comment on "get Linux" it has little to do with Linux or not.
There was a good article on Slashdot (maybe Zdnet) yesterday on Lufthansa taking over Swiss airlines, and moving the other way, ie a Linux ecommerce setup to Windows, which is often used as a case study for a Windows/SQL server performance and TCO benefit.
What the article pointed out was that the hardware was changed - and upgraded by a great deal -
in the course of that upgrade. That accounted for a great deal of the performance benefit.

So I'd ask first off - take a step back and look at your hardware.
Where are the bottlenecks?
If you have a slow database - do you need a separate database machine with lashings of RAM?
If you are disk limited, maybe putting in a fast RAID with striped disk access. Or even a solid state disk these days - 1tbyte solid state disks are available.


ps. I'm a Linux sysadmin of long standing, but I'm still against the kneejerk "replace with with Linux and it will go faster"

[pcolbeck]

Yup on the same hardware platform Linux will probably be faster but and it's a big but you have to take into account the cost of re training your admins and porting your software. Can often be cheaper just to buy faster hardware. Just doing a "ctr alt del" and running performance monitor will show you if you are CPU bound.

[Valiant]

Get an Apple Server?