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@iptel.org [mailto:serusers-bounces@lists.iptel.org]On Behalf Of Michael Przybylski Sent: donderdag 12 augustus 2004 8:32 To: Michael Shuler Cc: serusers@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@bwsys.net Customer Service: (877) 976-0711
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
_______________________________________________ Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
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@iptel.org [mailto:serusers-bounces@lists.iptel.org]On Behalf Of Michael Przybylski Sent: donderdag 12 augustus 2004 8:32 To: Michael Shuler Cc: serusers@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@bwsys.net Customer Service: (877) 976-0711
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
Greg Fausak www.AddaBrand.com (US) 469-546-1265
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@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@lists.iptel.org
[mailto:serusers-bounces@lists.iptel.org]On
Behalf Of Michael Przybylski Sent: donderdag 12 augustus 2004 8:32 To: Michael Shuler Cc: serusers@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
-
Arne Scheffer -
Greg Fausak -
Ranga