[Serusers] Why Transactional Accounting?

[Greg Fausak]

Hi Guys,

<soapbox>
OK, I have to say that I am opinionated about databases.  I've been
working with databases since before Oracle (I actually worked at
Britton Lee in Los Gatos California, they manufactured a database 
machine).
Anyway...

Postgres is a serious database engine.  When we first starting using
SER I wrote the postgres backend because it had many characteristics
that I prefer over the then available mysql engine.  I have written 
large
scale database applications, and I have had the opportunity to
port applications that were melting on mysql to postgres.

Most databases are administration heavy, postgres is no exception.
They must be indexed, profiled, analyzed and vacuumed to maintain good
performance.

For this application, we use:

syslog pushing accounting information to various capturing syslogd's.
A sweeper that reaps syslog information and populates the postgres 
database.

The customer's call information is live and viewable up to the 6 second 
mark.
Call INVITE/BYE matcher only has to deal with 'current call' records, 
that is, those
that haven't been matched.  Now, granted, we haven't had a real big 
load (yet), so
I don't know if it will scale.  But, I believe it will.

We don't use triggers either.  I think they would work fine but
they make ad-hoc database changes a bit harder.
</soapbox>

---greg



On Aug 12, 2004, at 2:52 AM, Arne Scheffer wrote:

> Mike,
>
> In a high load situation I would not use a trigger. From experience 
> you can encounter big billing delays when you start using complex 
> billing. Most DB's see the trigger as part of your insert action and 
> will not reply to the application that performed the INSERT until the 
> trigger has finished. This means your SER may have to wait for a few 
> seconds to know if the insert was OK (serious performance impact).
>
> Now may say a few seconds why ?
> just take into account you have a number port database with 2M records 
> which you need to scan to see if the number is ported. Can take a 
> second very easily :(
>
> I would advise using a JOB on your SQL server (supported by Oracle & 
> MSSQL, Postgres does not support it I think) which runs every minute. 
> You still have the load on the server but SER will not be delayed by 
> TRIGGER.
>
> Arne.
>
>
>
> -----Original Message-----
> From: serusers-bounces at iptel.org [mailto:serusers-bounces at lists.iptel.org]On
> Behalf Of Michael Przybylski
> Sent: donderdag 12 augustus 2004 8:32
> To: Michael Shuler
> Cc: serusers at lists.iptel.org
> Subject: Re: [Serusers] Why Transactional Accounting?
>
>
> SER seems to support postgresql as an accounting backend, and 
> postgresql
> support "triggers."
>
> This is a synthesis of only a cursory look at the SER source tree and
> postgresql docs on triggers, but it looks like you can do the 
> following:
>
> 1.) Set up SER to use postgresql as it's accounting backend.
> 2.) Create an extra table with the fields you would like to see in your
> "real world records."
> 3.) Create trigger to corelate INVITES and BYEs and insert a row into 
> your
> "real world records" table every time it notices that a BYE has been
> inserted into the transactional accounting table.
>
> This is still extra CPU load that will hurt your scalability but it's
> fairly elegant and an experienced PostgreSQL user/admin should be able 
> to
> crate it up pretty quickly.
>
> Finally, I'd bet that a 2-way or 4-way opteron-based database server 
> would
> hold up to a pretty substantial call volume, even while running that
> trigger.
>
> Best regards,
> Mike Przybylski
>
> On Thu, 12 Aug 2004, Michael Shuler wrote:
>
>> At first it seemed like a really good idea then the more I looked at 
>> real
>> world records I realized that to generate a bill for a customer would
>> require quite bit of work on MySQL's part to match up the INVITE's 
>> and the
>> BYE's and then calc the difference in the times.  This causes a bit 
>> of a
>> problem for me because I like my customers to be able to see their CDR
>> records live.  Which really isn't much unless you look at it from a 
>> 100,000
>> user perspective.  I would either have to beat up my SQL server every 
>> time
>> they look at their records (or my customer service employees look at 
>> them or
>> even when the biller generates their bill) or run a cron job that 
>> runs every
>> so often to convert the data to a single record with a start time and 
>> a
>> duration and store it in another table.  The cron idea isn't too 
>> efficient
>> because of wasting disk space and rewriting a lot of the same data 
>> twice.
>>
>> This then leads me to ask why the SER acc module wasn't setup to 
>> generate an
>> initial start record identical to the INVITE message that it already 
>> does
>> that would be marked as "in progress"?  Then when a BYE is received 
>> with a
>> matching Call-ID you find the original INVITE record, change its 
>> status to
>> "done" and populate a duration field.  Any ideas why this can't be 
>> done
>> without affecting SER's scalability? :)
>>
>> Thanks!
>>
>> ----------------------------------------
>>
>> Michael Shuler, C.E.O.
>> BitWise Systems, Inc.
>> 682 High Point Lane
>> East Peoria, IL 61611
>> Office: (217) 585-0357
>> Cell: (309) 657-6365
>> Fax: (309) 213-3500
>> E-Mail: mike at bwsys.net
>> Customer Service: (877) 976-0711
>>
>> _______________________________________________
>> Serusers mailing list
>> serusers at lists.iptel.org
>> http://lists.iptel.org/mailman/listinfo/serusers
>>
>
>
> _______________________________________________
> Serusers mailing list
> serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>
> _______________________________________________
> Serusers mailing list
> serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>
>
Greg Fausak
www.AddaBrand.com
(US) 469-546-1265