1

All, I'm a programmer by trade but for this particular project I'm finidng myself being the DBA as well. Here is the scenario I'm faced with:

Web app with anywhere from 400-1000 customers. A customer is a "physical company", each of which has n-number of uers. Each customer (company) has on average 1GB worth of data (total of about 200 million rows). Each company has probably 80% similar data in terms of the type of data stored. The other 20% is custom data that the companies can themselves define (basically custom fields).

I am trying to figure out the best way to scale this on the cheap when you conisder that the customers need pretty good reaction time. For example, customer X might want to grab all records where last name like 'smith' and phone like '555' where as customer Y might want to grab all records where account number equals '1526A'.

Bottom line, performance is key and I'm finding it hard to decide what to index and if that is even going to help me given the fact these guys can basically create their own query through the UI.

My question is, what would you do? Do you think it would be wise to break each customer out into it's own DB? Total DB size at the moment is around 400GB.

It is a complete re-write so I have the fortune of being able to start fresh if needed. Any thoughts, hints would be greatly appreciated.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Christian
  • 11
  • 1
  • 1
    "these guys can basically create their own query through the UI" -- you mean you're re-writing Access' query builder? Good luck with that... – Denis de Bernardy Jun 08 '11 at 23:31
  • Why not give each customer it's own (similarly structured) **table** (rather than database)? I've had success doing something similar with postgresql – mgalgs Jun 08 '11 at 23:41
  • You should also consider the difference senarios in `back-up` and `restore` between one big database and many smaller ones. – ypercubeᵀᴹ Jun 09 '11 at 05:51

4 Answers4

5

Bottom line, performance is key and I'm finding it hard to decide what to index and if that is even going to help me given the fact these guys can basically create their own query through the UI.

Bottom line, you're ceding your DB performance to the whims of your clients. If they're able to "create their own query", then they're able to "create their own REALLY BAD queries".

So, if you run this in a shared environment (i.e. the same hardware), then customer A's awful table scans can saturate the I/O for everyone else.

If they're on the same database server, then Customer A's scans get to flush all of your other customers data from the data cache.

Basically, the more you "share", the more one customer can impact the operations of other customers. If you give customers the capability to do expensive things, and share much of it, then everyone suffers.

So, the options are a) don't let the customers do silly things or b) keep the customers as separated as practical so that when one does do silly things, the phones don't light up from all of the other customers.

If you don't know "what to index" then you are not offering much control over what the customers can do, and thus the silly thing factor goes way up.

You would probably get quite far by offering several popular, pre-made SQL views that the customers can select from, and then they're limited to simply filtering and possibly ordering the results. Then you optimize around execution of those views.

It's likely that surprisingly few "general" views can cover a large amount of the use cases.

Generic, silly queries can be delegated to a batch process that runs overnight, during off hours, or to a separate machine that doesn't impact transactional performance, such as a nightly snapshot with "everything but todays data" on it. Let them run historic queries against that.

Will Hartung
  • 115,893
  • 19
  • 128
  • 203
0

The SO question How to design a multi tenant database has a link to a decent article on the tradeoffs along the spectrum from "shared nothing" to "shared everything". Also, SO has a tag for those kinds of questions; I added it for you.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

Creating separate databases on the same server won't help you get better performance. The performance optimisations available to you with multiple databases are just the same as you can achieve with one database.

Separate databases might make sense for administrative reasons - if different backup or availability requirements apply to different customers for example.

It's still probably sensible to build your application so that it can support multiple databases so that you have the option of scaling out over multiple DB servers.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

If you have seperate databases the 80% that is the same beciomes almost impossible to keep the same over time. YOu will end up spending far more money for maintenance.

Luckly SQL Server has some options for you. First put the customer sspeicifc information in the same database in a separate schema and the common stuff in a differnt schema(create a common schema and a schema for each client).

Next set up data partitioning by client. This can require the proper hardware to do this effectively.

Now you have one code base for common which will promugate changes to all clients at once and clients are separated for performance using the partitions.

HLGEM
  • 94,695
  • 15
  • 113
  • 186