[asterisk-dev] Re: RealTime, Large Dialplans, and headaches...work-around

[JR Richardson]

> OK, I just closed 9037 after a conversation with kpfleming.
> Here is the situation:
>
> 9037's reporter is complaining that with a large dialplan, in order to
> look up an extension, and do the pattern matching, asterisk pulls every
> extension from the context in the DB, and tries to find the best match.
> When the dialplan gets big, and you have hundreds/thousands (or more) of
> extensions, then this takes a while. The user was reporting 5 or more
> seconds.
>
> I've been working on a search-tree algorithm that would find an
> extension match a bit faster in a large dialplan. It's speed is a
> function of the length of the patterns, not of how many extensions there
> are. I thought I might apply this to the RT extensions, and keep an
> in-mem copy of the search tree... but it depends on a lot of things,
> like being able to discern an update to the DB of the dialplan, so you
> can reform (or spot edit, which I believe is possible) the in-mem search
> tree.
>
> Kevin pointed out:
>
>         This is impossible because you have no way of knowing when the
>         database
>         is modified. Even if you did, you'd have to resort to rebuilding
>         the
>         'search tree' in memory, and if the dialplan is large enough
>         then the
>         other issue comes into the picture as well.
>
>         This is an artifact of the way the Realtime system is
>         architected, which
>         means that it is broken. It is not fixable in the current
>         architecture.
>
> If someone in the community has a brilliant flash of inspiration as to
> how this problem could be solved, we'd love to hear about it! Any ideas?
>

I posted this a wile back, might help out.  This is just a work around
for a large LCR table, wish we had a more friendly solution.

> -----Original Message-----
> From: JR Richardson [mailto:jmr.richardson at gmail.com]
> Sent: Sunday, January 14, 2007 1:09 PM
> To: asterisk-users at lists.digium.com
> Subject: realtime mysql db performance difference with matching extensions
>
> Hi All,
>
> I'm testing different ways to implement a LCR/OCN tabe to shift calls
> to multiple carriers for better rates.  I'm using realtime mysql
> database access, asterisk 1.2.9.1 with mysql 3.23.
>
> Scenario 1:
> I send outgoing calls with with a Goto statement into the context with
> the realtime switch to dip into the mysql lcr database, currently I
> have ~13K records/routes.  The database records have matching exten
> like this
> _1504555XXXX
>
> Scenario 2:
> I send outgoing calls with a Goto statement into a context that does a
> realtime database lookup for a route and extracts the appdata from the
> record, put that into a Dial statement to complete the call like this
> exten => _1NXXNXX,1,RealTime(lcr|exten|${EXTEN:1}|LCR_)
> exten => _1NXXNXX,2,GotoIf($["${LCR_exten}" = ""]?${EXTEN},105:${EXTEN},3)
> exten => _1NXXNXX,3,Set(directdial=${LCR_exten}@${LCR_appdata})
> exten => _1NXXNXX,4,NoOp(${directdial})
> exten => _1NXXNXX,5,NoOp(${TIMESTAMP})
> the database has records with 504555, this is just a test setup
>
> The performance difference is substantial, scenario 1 takes 2-3
> seconds to complete with the mysql and asterisk combined process
> taking 100% processor utilization for the 2-3 second duration.
> Scenario 2 takes less than a second, completes immediately with little
> resource use.
>
> So the question is: is the extension matching routing in asterisk
> normally a high resource user with a realtime switch to a mysql
> database?  Is the resource use a product of the database having 13K
> records?  I'm more inclined to believe the extens matching is the
> culprit here, not the size of the database.
>
> Any thoughts/advice/alternative methods, other experience implementing
> LCR tables with asterisk?

JR Richardson
Engineering for the Masses