Running a Asp.net website with MS SQL server - When should i worry about scalability?

Question

Hello All,

I run a medium sized website on an ASP.net platform and using MS SQL server to store the data.

My current site stats are:

~ 6000 Page Views a day ~ 10 tables in the SQL server with around 1000 rows per table ~ 4 queries per page served The hosting machine has 1GB RAM

I expect by the end of 2009 to hit around:

~ 20,000 page views ~ 10 tables and around 4000 rows per table ~ 5 queries per page served

My question is should I plan for scalability right now itself? Will the machine hold up till the end of the year with the expects stats.

I know my description is very top level and does not provide insight into the kind of queries etc. But just wanted to know what your gut instinct tells you?

Thanks!

Answer 1

Have you tried setting up a quick performance test using sample data? 20,000 page views is less than one/sec (assuming even distribution over 8 hours), which is pretty minimal given your small tables. Assuming you're not sending a ton of data with each page view (i.e. a data table with all 1000 rows from one of your tables), you are likely OK.

You may need to increase RAM, but other than running a performance test I wouldn't worry too much about performance right now.

Answer 2

Essentially, by the end of 2009, you expect to do 100,000 SQL queries per day. This is about 1.157 queries per second.

I am making the assumption that your configuration is "normal" (i.e. you're not doing something funky and these are pretty straightforward SELECT, UPDATE, INSERT, etc), and that your server is running RAID disks.

At 4,000 rows per table this is nothing to SQL server. You should be just fine. If you wanted to be proactive about it, put another stick of RAM in the server and bring it up to at least 2GB, that way IIS and SQL have plenty of memory (SQL will certainly take advantage of it).

Answer 3

You should always plan for scalability. When to put resources into doing the actual scaling is usually the tough guess.

Will the machine hold up until the end of the year

Way too little information to answer this. If a page request takes 30 CPU seconds to process due to massive interaction with a legacy enterprise application through the four queries per page - then there's no way. If it's taking miniscule fractions of a second to serve some static content stored in the cache and your queries are only executed every half hour to refresh the content - then you're good until 2020 at the rate of traffic growth you describe.

My guess is that you're somewhere closer to the latter scenario. 20,000 page hits a day is not really a ton of traffic, but you'll need to benchmark your page and server performance at some point so that you can make the calculations you need.

Things to look at for scaling your site when it is time:

• Output Caching
• Optimizing Viewstate
• Using Ajax where appropriate
• Session optimization
• Request, script, css and html minification

Two years ago I saw a relatively new (for two years ago) laptop running IIS and serving up 1100 to 1200 simple dynamic page requests per second. It had been set up by a consulting firm whose business was optimizing ASP.Net websites, but it goes to show you how much you can do.

Answer 4

I don't think the load you are describing would be too much of a problem for most machines. Of course it doesn't just depend on the few metrics you outlined but also on query complexity, page size, and a heap of other things.

If you worry about scalability do some load testing and see how your site handles, say 10000 page views per hour (about 3 views per second). It's mostly always good to plan ahead as long as you plan for probable scenarios.

Answer 5

Guts say: Given 10 tables with 4,000 rows each and assuming about 2KB of data per row is only 80MB for the entire database. Easily cached within memory available. Assuming everything else about the application is equally simple, you should be able to easily serve hundreds of pages per second.

Engineers say: If you want to know, stress test your application.

Answer 6

The hosting machine? Does this mean that you have IIS and SQL installed on the same box or IIS on your host machine with a dedicated SQL Server provided by your hosting company? Either way I would suggest starting to take a look at how you might implement a caching layer to minimize the hits (where possible) to the database. Once this is PLANNED (not necessarily implemented) I would then start to look at how you might build a caching layer around your output (things built in ASP.NET). If you see a clear an easy path to building caching layers...then this is a quick and easy way to start to minimize request to the database and work on your web server. I suggest that this cache layer be flexible...read not use anything provided by .NET! Currently I still suggest using MemCached Win32. You can install it on your one hosted local box easily and configure your cache layer to use local resources (add memory...1gb is not enough). Then if you find that you really need to squeeze every little bit of performance out of your system...splurge for a second box. Split your cache between your current box...and the new box (allowing you to keep more in cache). This will give you some room (and time) to grow. Offloading to more cache should help address any future spikes...and with the second box you can now also focus on making your site work in farmed environment. If you are using local session..push that into your cache layer so that a request from one box or another won't matter (standard session is local to the box that it is managed on).

This is a huge subject...so without real details this is all speculation of course! You might be just right for adding better and more hardware to the existing installation.