[Asterisk-Dev] Current database abstraction effort ?

[david]

I can add a few observations about CDR and ODBC from past experience -- I've
only been using Asterisk since late October so I'm new to this platform but
have been looking at CDR from other PBXs for a very long time.

>>My point was, and maybe you are agreeing, is that ODBC adds
>>very little to the cost of a CDR write.  Any bottle
>>neck will be with the DBMS itself not with ODBC.

ODBC will slow down db access in varying degrees, depending on a number of
issues including db schema, the ODBC driver efficiency, the system's
implementation of ODBC.  But from a code management standpoint (if you are
going to write to a database at all!), this is probably all worth the loss
of some performance, which should not be significant if you read further.

>>That rate of a susstained 100 records/sec is _very_ high.
>>I doubt any Asterisk system could come close to that.

A medium-sized central office class 4 switch process traffic at peak rates
of 50,000 calls per hour or 833 calls per minute.  Very large PBXs of
10,000+ phones average well under 100 calls per minute.  A rule of thumb I
have used for many years in estimating PBX traffic is this:  Number of
Extensions * 5 calls per day * 5 minutes per call.  This gets skewed some at
the very small and the very large systems, but holds surprisingly close to
realistic in most cases.  This includes ALL phones such as kitchen and
conference rooms. It is sometimes confused with people count, which is less
accurate.

PBXs have been delivering CDR via RS-232 at rates of 1200 baud (bps)
forever.  With an average CDR record length of 100 bytes, this translates to
a maximum of 12 calls per second or 720 calls per minute, or 43200 calls per
hour.  We're back at the peak load for a CO Class Switch!  In many years of
logging and processing many varieties of CDR, there are very, very few
customer PBXs that sustain traffic at that rate. Usually these end up being
CO-class switches such as Nortel DMS-100 or 250's.   I don't think Asterisk
has to worry about that kind of load.

My question is, why do you need to write the CDR to a database in the first
place?  The applications that read this type of data are used to accessing
it as clear text (ASCII).  This has been picked up in the past from RS-232
streams but in more recent years "evolved" to file or IP streams.  Here are
approaches that are used today by various IP PBX manufacturers:

Cisco CallManager: MS SQL7 access via ODBC -- requires fairly complex
application logic to access these CDR records for use in a 3rd party
application.  CallManager currently restricts access to the CDR tables to
10ms per query (about 1000 records max) to avoid a timeout error.

Shoreline: File-based -- flat ASCII file written daily.

Avaya: Proprietary application of RSP protocol available using SDK for
partners

Nortel BCM: File-based -- flat ASCII

Mitel 3100/3300 controller -- IP messaging (no protocol, no buffering)

Other options: Telnet, FTP

I wonder why it is important to write the Asterisk CDR to a database.
Writing a flat, fixed column ASCII file makes it much more accessible to the
variety of applications out there used for traffic engineering and billing.
One effective way to do this is to manage a threshold (timer or call count)
at which point you write a new file using a unique file name scheme
(sequence number, date/time, etc.).  While the new file is open, it is
write-locked, preventing any other app from reading it. All previous CDR
files are accessible.  If you use a timer, these files could be available
every hour, or every day, or whatever sequence the user sets it up for.
Sequential file names like cdrXXXX.txt could cycle from 0000 to 9999 at
which point they overwrite the oldest data.  This is then somewhat
self-maintaining within the disk limits of the user's system.  Just some
suggestions as well as questions.

Best Regards,
David Schlossman