[OpenSIPS-Users] Help with big amount of data for routing

[Daniel Zanutti]

Hi Miha

I have a similar situation, but around 20 M routes.

The native routing mecanims wasn't performing well, so I developed a custom
mecanism using Opensips scripting. Everything is stored on MySQL database.

The best approach was use avp_db_query to get the route, the primary key
(and index) of the table is the route prefix and stored as BIG INT, so you
have up to 19 digits of routes, which is OK to me. I could achieve more
than 100 cps with this method.

You have to find the longest route "by hand", so I developed this procedure:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getLongestRoute`(IN route
VARCHAR(50), OUT bestroute BIGINT, OUT regionid INT)
BEGIN
DECLARE rotatemp VARCHAR(50);
DECLARE tempprefix BIGINT;
CREATE TEMPORARY TABLE IF NOT EXISTS temptabrotas ( prefix BIGINT UNSIGNED)
ENGINE=HEAP;
SET rotatemp = SUBSTRING(route, 1, LENGTH(route));
INSERT INTO temptabrotas (prefix) VALUES (rotatemp);
WHILE (LENGTH(rotatemp) > 1) DO
 SET rotatemp = SUBSTRING(route, 1, LENGTH(rotatemp)-1);
 INSERT INTO temptabrotas (prefix) VALUES (rotatemp);
END WHILE;
SELECT routes.prefix, routes.regionid FROM routes
 INNER JOIN temptabrotas
   ON routes.prefix = temptabrotas.prefix
ORDER BY routes.prefix DESC
LIMIT 1
INTO bestroute, regionid;
DROP TABLE temptabrotas;
    END$$

DELIMITER ;

Hope it helps.

Regards


On Wed, Oct 5, 2016 at 4:16 AM, Miha <miha at softnet.si> wrote:

> Hi Alex
>
> i tried, but mysql takes so long time for every select. What do u have in
> mind?
>
>
> tnx
>
> miha
>
>
>
> On 05/10/2016 08:46, Alex Balashov wrote:
>
>> Why do you believe that using a traditional RDBM necessarily means slow
>> lookups?
>>
>> On 10/05/2016 02:44 AM, Miha wrote:
>>
>> HI
>>>
>>> the is not really opensips issue:) I need somehow to store big amount of
>>> data for routing.
>>>
>>> To every telephone operator I must send RURI like
>>> Net_ID+Telephone_number (value indicates to who number belongs to). In
>>> this country they have around 120 millions of numbers.
>>>
>>> After i have all NET_IDs with numbers I will use drouting for routing
>>> numbers to right operator based on NET_ID.
>>>
>>> Here is the issue:
>>> - I tried this with redis (lookup must be quick) but this takes so much
>>> memory that basically redis brakes everytime in between 50 millions and
>>> 70 millions entries
>>> - I tried with hash (hset) in redis but did not do any good
>>>
>>>
>>> Do you have any suggestion how to deal with this, what would be the best
>>> thing to use?
>>>
>>>
>>>
>>> tnx
>>>
>>> miha
>>>
>>>
>>> _______________________________________________
>>> Users mailing list
>>> Users at lists.opensips.org
>>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>>
>>
>>
>>
>
> _______________________________________________
> Users mailing list
> 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/20161005/552e5cb8/attachment.htm>