[freeside] limitations

[toby cabot]

> Just want to know if there is a limit on the number of users with
> the mysql database used by freeside. Say I had several hundred
> thousand users, how would freeside and mysql cope with this load??

I've been lurking for a while and here's my opportunity.  I used to do 
capacity planning for a commercial billing company so maybe some of what 
I learned will be helpful.

At a high level you've got two different types of loads - batch and 
interactive.  Interactive are people querying the system, i.e. customer 
care representatives, maybe customers themselves using Web-based 
self-care, maybe other systems doing auth queries.  Interactive 
loads are usually specified in terms of response - i.e. how much time 
each query can take.  Most modern database systems degrade gently 
so you can plot a curve of response time versus load, pick the response 
that's acceptable, and that determines your throughput.

Batch loads are the processes that need to run in the background such as 
usage rating, invoice processing, g/l feeds, etc.  Batch processes 
usually need to run within a time window, so the batch capacity is how 
much processing you can do in that window.  Naturally the capacity of 
any given billing system (say, in terms of how many accounts it will 
support) is determined by whichever of these two loads is greater.  With 
most commercial systems the batch processing is much more intense so 
that's what people focus on when doing capacity planning.

Batch processing throughput is relatively easy to test since it's nicely 
scriptable.  Interactive might need a little more effort since the 
interfaces tend to be human interfaces so your test harnesses tend to be 
more elaborate.

The first thing that you need to do is build a business model.  The 
business model consists of the assumptions that you're making about the 
system and then other things that follow from those assumptions.  For 
example, if you're going to do invoice processing once per week for 8 
hours then you'll have to get all of your invoices done in 4 runs of 8 
hours each, or 32 hours.  If you've got 100k bills that need to get 
gernerated each month then you need to prove that the system can run at 
a sustained rate greater than 0.87 bills per second.  Actually, you'll 
probably want to run at least twice that fast in the lab since the real 
world is never as easy as the benchmark lab.

The model is also useful in doing "what-if" scenarios to determine the 
best recovery from processing errors.  "What if" my bill run is bogus 
and I have to roll it back?  "What if" my db gets corrupt and I have to 
reload it from backup and I lose a day?

To test batch loads you'll probably want to write some test tools that 
build fake data, load it into the database, and then set up repeatable 
test runs so you can do A-B-A testing to help with performance tuning.  
You can add a few accounts to the database by hand and then build a perl 
script to generate a few thousand algorithmically.  Usually you'll want 
to use the database vendor's bulk load tool (e.g. Sybase bcp, Oracle 
bulk load) to slam them into the db so you'll spend less time waiting 
for the tests to set up since you'll probably be running a lot of tests.  
You can start with small runs of a few thousand accounts and then work 
up from there.  Keep in mind that most db's do slow down as you go from 
1000's to 100000's of rows in the tables.

It's probably best to start on a smaller system and then work your way 
up to a E10000 or Superdome.  This way you'll figure out what the 
obvious bottlenecks are and you don't have to listen to the big machines 
saying "Ha! I laugh at your puny processing loads!  Bring me work that's 
worthy of my power!"  Trust me, it's no fun, especially when the EMC 
arrays chime in.  Better to work out the kinks on little boxes so you 
can hammer the big ones for all they're worth.

As a side effect of doing this testing you'll get a feel for how the 
product performs, and that will give you more confidence when you put 
the system into production.

Good luck,
Toby

PS.  I'd be *very* concerned about running a billing system on mysql.  
Transactions are a Good Thing, especially where billing is concerned.  
So is concurrency.  If you can't afford Oracle or Sybase then take a 
look at Postgresql.