A good alternative with postgres for external actions that might incur large delays is to use a trigger that simply touches a file in the file system. A very low-overhead program can watch for a change in the file timestamp and perform an action. This watcher program can check the file on a few seconds delay giving much better responsiveness than is typical of cron or database job scheduling, and the workload on the database only runs as needed, not every minute.
This also has the advantage (or disadvantage) of transferring the action to a different security and execution realm. Ie, the operation now runs as the uid of the watcher program and with the watcher program's permissions, scheduler-priority, and enviroment, rather than as the postgres user.
However, as Greg mentioned, we have implemented the code to match INVITE to BYE. This has not had substantantial stress testing but certainly operates with no measurable load at >20 calls per minute.
Matching performance is not an issue even with six-figure call histories. Good indexing completely resolves this for probably any size, but almost certainly for eight-figure sizes. That's because you are only ever concerned with the currently active set of calls (those with no hangup time recorded yet).
The two biggest problems we have encountered are:
BYEs are not reliable. They are initiated solely by the UA, so even with a completely reliable network, BYEs will go missing if the user powers down or disconnects their system before hanging up.
call_ids are not reliable. They are also created by the UA, and UAs have bugs. Even major vendors have released firmware that does not produce unique call_ids.
For the most part, these problems can be banged on until they go away.
For the former you could do probing from the proxy to confirm calls are still in progress. This is still a problem. What if the UA simply ignores the probe? Then the proxy fakes a BYE to close the call but the call (the RTP path) is still operating and the caller is now talking for free. Instead, we use multiple accounting sources for calls we are actually billing for and for SIP<->SIP we just ignore the problem because we're not billing that path.
The latter can be resolved using internally generated IDs among a realm of cooperating proxies, but this is an impossible dream in any mixed vendor world, and implies all SIP routing must be done through the realm, which may also be undesirable or unattainable.
Currently we use call_id/fromtag/totag as a key which seems reliable for most UA vendors and is consistent with the RFC. We also detect attempts to reuse keys. Currently these are just logged but they will probably eventually trigger call rejection and might even get to UA mfg + firmware version blocking if we start to encounter widespread flaws. The objective of the latter is to get the manufacturer to take notice.
Andrew Fullford -- Email: andy@addabrand.com Web: www.addabrand.com
Cc: mikep@pch.net mikep@pch.net, Michael Shuler mike@bwsys.net, 'serusers@lists.iptel.org' serusers@lists.iptel.org, 'Addabrand Support' support@addabrand.com From: Greg Fausak greg@addabrand.com Subject: Re: [Serusers] Why Transactional Accounting? Date: Thu, 12 Aug 2004 09:27:41 -0500 To: "Arne Scheffer" arne.scheffer@ritstele.com
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
On 12-08 13:17, Andrew Fullford wrote:
BYEs are not reliable. They are initiated solely by the UA, so even with a completely reliable network, BYEs will go missing if the user powers down or disconnects their system before hanging up.
There is a partial solution if the other end-point is still alive. It will send a BYE which will be replied with 408 Request Timeout by SER. You can account such failed transactions too.
If both end-points fail then there is no chance to get a BYE, of course :-). But this case is in my opinion negligible.
call_ids are not reliable. They are also created by the UA, and UAs have bugs. Even major vendors have released firmware that does not produce unique call_ids.
Yes, that's true. Here you can use the triplet <call-id, fromtag, totag>.
For the most part, these problems can be banged on until they go away.
For the former you could do probing from the proxy to confirm calls are still in progress. This is still a problem. What if the UA simply ignores the probe? Then the proxy fakes a BYE to close the call but the call (the RTP path) is still operating and the caller is now talking for free. Instead, we use multiple accounting sources for calls we are actually billing for and for SIP<->SIP we just ignore the problem because we're not billing that path.
I suppose that you have a PSTN gateway in your hands. If you are lucky then it will support session timer. Session timer gives you the possibility to send keepalive SIP messages periodically (re-INVITEs), if the gateway does not receive a proper reply then it will declare the other end-point dead and terminate the call. Session timer support in one end-device is enough to make it work. In addition to that it is possible to detect a failure when the gateway does not receive RTP traffic anymore (cisco can do it).
It is best to detect failures on the PSTN gateway if possible, because that's the device that is burning up money.
Jan.
Jan Janak wrote:
On 12-08 13:17, Andrew Fullford wrote:
BYEs are not reliable. They are initiated solely by the UA, so even with a completely reliable network, BYEs will go missing if the user powers down or disconnects their system before hanging up.
There is a partial solution if the other end-point is still alive. It will send a BYE which will be replied with 408 Request Timeout by SER. You can account such failed transactions too.
for the sake of completeness: modparam("acc", "failed_transactions", 1)
regards, klaus
-
akfullfoï¼ august.com -
Jan Janak -
Klaus Darilion