[Serusers] Why Transactional Accounting?

[Ranga]

Hi,

This is what I am thinking.

I will have two tables in database. One holds
inprogress calls and the other holds finished calls.

I would write a module that inserts a new record when
an INVITE is sent out. Update the same
record(timestamps) when 200 OK, ACK are received. When
BYE or any 4xx is received, update the new timestamp (
another field) and move this record to the new table. 
which will be one INSERT and one delete. Always when I
update, I update only the inprogress calls table.

Then what would be the impact on the performance?
At any point of time, I dont see more than 500 calls
in progress in our ser. Currently we see about 40-50
simultaneous calls happening. I execute an update on
40-50 records or at the max 500 records. I execute an
INSERT into a table that has lot of records.


This I think is could serve our purpose. Any
suggestions on this?

Greg, which database do you suggest would be best to
use in this case? mysql or postgre?

regards
Rao


--- Greg Fausak <greg at addabrand.com> wrote:

> 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 lists.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!
> 
=== message truncated ===



		
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail