[OpenSIPS-Users] Mysql stored proc

[Brett Nemeroff]

Well I've heard of a bunch of carriers now doing what they call "dynamic
LCR". Here's the issues at hand with carrier LCRs:1. Depending on
jurisdiction, I route differently (interstate, intrastate, international,
etc). External factors can determine jurisdiction. I use memcache + some
pattern matching today with good success.
2. A carrier will assign products/routetables/ratetables to a customer. Each
customer can have their own route/rate tables.
3. Ultimately, I need to pick a route based on cost
4. There is a max cost for a route. ALWAYS. in other words, if I sell a
route for $0.01, I don't use a route that costs $0.015 (without some sort of
forcing.)
5. Amongst the valid routes, I want to order them by cost, try them in order

That in itself is very useful. To have the ability to load cost tables and
rate tables and have it LCR against that would be immensely useful. I've
commented I think on the list and in the feature request tracker for a
"find_best_match" memcache function that could also be used to do some of
this.

Now the fancy bits.. I suspect this isn't nearly as complicated as it
sounds. However, it's very complicated to explain to someone who doesn't
know a whole lot about LCRs.

6. Assign weighting scores to PDDs and ASRs. Allow better quality routes
(metric to be set by avp or modparam) to be preferred even if more expensive
(as long as it's below cost).
7. Periodically evaluate X period ASR/PDD (ie 20 minute ASR/PDD) based on
results, re-prioritize the route order.

The idea is that I have 3 carriers.. they are sorted by cost.. all are
profitable routes.. but the cheapest, is by far the worst route.. But lets
 sometimes the route performs great (say an ASR of 89% and PDD of 3.4
seconds). however it has a habit of every other day, totally becoming a junk
route for a few hours (say ASR of 7% with a PDD of 12 seconds). So I'd like
to, without thinking about it, move this to a lower priority for a period of
time (set by avp or modparam),

8. Perform dialog limiting on a gateway basis. Part of the problem in
maintaining carrier side ASR is making sure you don't send too many calls to
that carrier.. if that carrier is selling you 100 ports and you just throw
over whatever calls that match in the LCR to them, you could likely run out
of ports on the cheap carrier and not even know it.. causing massive amounts
of failed calls simply because you're sending too many to one carrier.
Having dialog limiting also allows to "test" a route.. Say "this test route
only has 2 channels on it". Of course, much of that can be done with the
dialog module today (which is why my previous list questions asked "what if
I call do_routing and I don't want to use the resultant RURI"

Those are my ideas for now.. It seems to specific for a module in the public
domain, but I don't know, is this useful to anyone else? As far as aI see it
there is:
1. The Cost/Rate bit to pick a route, ensure it's not upside down (losing
money)
2. The re-prioritization of the route based on "X"

These seem like two distinct things. Maybe it can be implemented in some
generic sense where my logic could be constructed into it with good
scripting.

Thoughts?
-Brett

On Mon, Jun 15, 2009 at 5:34 PM, Bogdan-Andrei Iancu <bogdan at voice-system.ro
> wrote:

>  Hi Brett,
>
> Brett Nemeroff wrote:
>
> Ok, well I'll try this out for performance. I'm curious to see what I'd be
> able to handle doing live mysql routing decisions with all the new prepared
> statements and such.
>
> note that not all the DB ops are using prepared statements - most of the
> modules do use it internally, but when running raw queries (with
> avp_db_query() ), the query will be done in old fashion text one.
>
>
>  Of course, I'm a big proponent of doing memory routing, but I may need
> some capabilities to route based on other factors such as ASR and PDD to
> automatically set the route priorities. Maybe a module someday.. :D
>
> can you share some ideas ? :D
>
> Regards,
> Bogdan
>
>
>  -Brett
>
>
>
> On Sun, Jun 14, 2009 at 1:06 PM, Bogdan-Andrei Iancu <
> bogdan at voice-system.ro> wrote:
>
>> hard to say - to be honest I never used "temporary" tables - I just
>> commented from the opensips point of view, on how the mysql connections are
>> managed.
>>
>> Regards,
>> Bogdan
>>
>> Brett Nemeroff wrote:
>>
>>> Ok, so basically I build a temp table called "routes" from a stored proc.
>>> Can I not rely on this being a unique table per transaction if the session
>>> is held alive? Of course I could name the table something like
>>> routes_<timestamp>_<$ru> and trash it at the end of the stored proc. I just
>>> don't want to end up with a race condition.
>>>
>>>
>>>  On Thu, Jun 11, 2009 at 3:28 AM, Bogdan-Andrei Iancu <
>>> bogdan at voice-system.ro <mailto:bogdan at voice-system.ro>> wrote:
>>>
>>>    Saúl Ibarra wrote:
>>>    >> Each OpenSIPS process opens at startup a mysql connection and
>>>    it keeps it
>>>    >> open till the shutdown - so the connection is persistent at
>>>    runtime. Of
>>>    >> course, a conection can be re-established at runtime if some
>>>    connection lost
>>>    >> event happens (timesout etc).
>>>    >>
>>>    >>
>>>    >
>>>    > That's good to know :) But don't you think it's a bit risky to
>>>    rely on
>>>    > temporary tables in this case? If by any chance the connection
>>>    is lost
>>>    > strange things ca start to happen :-O ? I'd go for memcache ;)
>>>    >
>>>    well, opensips takes care of its internal DB stuff (like re-init the
>>>    prepared statements after a reconnect), but for other things you do by
>>>    yourself from script, you need to take care by yourself :)
>>>
>>>    But yes, the memcache is a good option :). especially that we are
>>>    working on interfacing the memcache interface with the memcached
>>>    daemon ;)
>>>
>>>    Regards,
>>>    Bogdan
>>>
>>>    _______________________________________________
>>>    Users mailing list
>>>     Users at lists.opensips.org <mailto:Users at lists.opensips.org>
>>>    http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>>
>>>
>>>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.opensips.org/pipermail/users/attachments/20090615/2dcd3a35/attachment.htm