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

[Miha]

Tnx Daniel for this!

I will try.


br

miha


On 05/10/2016 15:09, Daniel Zanutti wrote:
> 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 
> <mailto: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 <mailto:Users at lists.opensips.org>
>             http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>             <http://lists.opensips.org/cgi-bin/mailman/listinfo/users>
>
>
>
>
>
>     _______________________________________________
>     Users mailing list
>     Users at lists.opensips.org <mailto:Users at lists.opensips.org>
>     http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>     <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/20161006/621b9880/attachment.htm>