Thursday
Sep042008
Database question for upcoming project
Thursday, September 4, 2008 at 3:06AM
We will be developing an RIA that will have a lot of database access. Think something like a QuickBooks but with about 50 transactions entered per hour per user. Users will be in the system for 7 to 9 hours a day and there will be around 20,000 users, all logged in at the same time.
Reporting will be done just like a QuickBooks style app plus a lot of extra things you don't do in QuickBooks.
Our operations is familiar with W2003 Server and MS SQL Server so they are recommending we stick with that. I originally requested Linux and PostgreSQL.
How far can a single database server get me? If we have a 4 processor, 8 core, 128gb server, how far am I going to get before I need to shard or do something else? I know there are a lot of factors involved but in general for this size of a site, what should the strategy be?
I've read almost all articles on this website but most of the applications are not RIA type of apps with this type of usage or they are architectures for sites with millions of users which we also won't have.
Reporting will be done just like a QuickBooks style app plus a lot of extra things you don't do in QuickBooks.
Our operations is familiar with W2003 Server and MS SQL Server so they are recommending we stick with that. I originally requested Linux and PostgreSQL.
How far can a single database server get me? If we have a 4 processor, 8 core, 128gb server, how far am I going to get before I need to shard or do something else? I know there are a lot of factors involved but in general for this size of a site, what should the strategy be?
I've read almost all articles on this website but most of the applications are not RIA type of apps with this type of usage or they are architectures for sites with millions of users which we also won't have.
Reader Comments (2)
That's about 300 transactions a second. I would simulate the expected load on the configuration you are talking about and see what happens. It would be close to impossible to give an analytic answer.
The RAM will help with large queries, but you are probably more disk limited than CPU, so consider that in your equation too. Then you need to configure the disks correctly, keep separate physical volumes for OS, data, and logs. Use RAID10 etc. I'm sure MS has guides on the best configuration. But it's all about the disk, given you already have a lot of RAM. Consider replication slaves to remove some load from the main write database. This might help: http://www.sql-server-performance.com/sql_server_performance_audit.asp
Thanks for the link. Very good article. What first stuck out is that a 4 processor server running a heavy ERP application could handle from 100 to 150 concurrent users. The article was written just over four years ago so I'm hoping the newer servers can handle more than that! Though I know it didn't jump to 20,000 concurrent users.
I will need to get them to setup a server for me to test with. It will be hard to really test because our customers do a lot of reporting on their data and right now, I don't have any actual data to test with. All of our customers currently use a desktop client-server application. It's going to take some careful planning.