<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>Tnx Daniel for this!</p>
<p>I will try.</p>
<p><br>
</p>
<p>br</p>
<p>miha<br>
</p>
<br>
<div class="moz-cite-prefix">On 05/10/2016 15:09, Daniel Zanutti
wrote:<br>
</div>
<blockquote
cite="mid:CAN0jgiP3Wk=YUoQn1RQpNFoFn3Aq21yHvuR9DvKyn3HBO3S8sw@mail.gmail.com"
type="cite">
<div dir="ltr">Hi Miha
<div><br>
</div>
<div>I have a similar situation, but around 20 M routes. </div>
<div><br>
</div>
<div>The native routing mecanims wasn't performing well, so I
developed a custom mecanism using Opensips scripting.
Everything is stored on MySQL database. </div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>You have to find the longest route "by hand", so I
developed this procedure:</div>
<div><br>
</div>
<div>
<div>DELIMITER $$</div>
<div><br>
</div>
<div>CREATE DEFINER=`root`@`localhost` PROCEDURE
`getLongestRoute`(IN route VARCHAR(50), OUT bestroute
BIGINT, OUT regionid INT)</div>
<div>BEGIN</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>DECLARE
rotatemp VARCHAR(50);</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>DECLARE
tempprefix BIGINT;</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>CREATE
TEMPORARY TABLE IF NOT EXISTS temptabrotas ( prefix BIGINT
UNSIGNED) ENGINE=HEAP;</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>SET
rotatemp = SUBSTRING(route, 1, LENGTH(route));</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>INSERT
INTO temptabrotas (prefix) VALUES (rotatemp);</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>WHILE
(LENGTH(rotatemp) > 1) DO</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>
SET rotatemp = SUBSTRING(route, 1, LENGTH(rotatemp)-1);</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>
INSERT INTO temptabrotas (prefix) VALUES (rotatemp);</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>END
WHILE;</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>SELECT
routes.prefix, routes.regionid FROM routes</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>
INNER JOIN temptabrotas</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>
ON routes.prefix = temptabrotas.prefix</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>ORDER
BY routes.prefix DESC</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>LIMIT
1</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>INTO
bestroute, regionid;</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>DROP
TABLE temptabrotas;</div>
<div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div>
<div> END$$</div>
<div><br>
</div>
<div>DELIMITER ;</div>
</div>
<div><br>
</div>
<div>Hope it helps.</div>
<div><br>
</div>
<div>Regards</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Wed, Oct 5, 2016 at 4:16 AM, Miha <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:miha@softnet.si" target="_blank">miha@softnet.si</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Alex<br>
<br>
i tried, but mysql takes so long time for every select. What
do u have in mind?<br>
<br>
<br>
tnx<span class="HOEnZb"><font color="#888888"><br>
<br>
miha</font></span>
<div class="HOEnZb">
<div class="h5"><br>
<br>
<br>
On 05/10/2016 08:46, Alex Balashov wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
Why do you believe that using a traditional RDBM
necessarily means slow lookups?<br>
<br>
On 10/05/2016 02:44 AM, Miha wrote:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
HI<br>
<br>
the is not really opensips issue:) I need somehow to
store big amount of<br>
data for routing.<br>
<br>
To every telephone operator I must send RURI like<br>
Net_ID+Telephone_number (value indicates to who
number belongs to). In<br>
this country they have around 120 millions of
numbers.<br>
<br>
After i have all NET_IDs with numbers I will use
drouting for routing<br>
numbers to right operator based on NET_ID.<br>
<br>
Here is the issue:<br>
- I tried this with redis (lookup must be quick) but
this takes so much<br>
memory that basically redis brakes everytime in
between 50 millions and<br>
70 millions entries<br>
- I tried with hash (hset) in redis but did not do
any good<br>
<br>
<br>
Do you have any suggestion how to deal with this,
what would be the best<br>
thing to use?<br>
<br>
<br>
<br>
tnx<br>
<br>
miha<br>
<br>
<br>
______________________________<wbr>_________________<br>
Users mailing list<br>
<a moz-do-not-send="true"
href="mailto:Users@lists.opensips.org"
target="_blank">Users@lists.opensips.org</a><br>
<a moz-do-not-send="true"
href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users"
rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-<wbr>bin/mailman/listinfo/users</a><br>
</blockquote>
<br>
<br>
</blockquote>
<br>
<br>
______________________________<wbr>_________________<br>
Users mailing list<br>
<a moz-do-not-send="true"
href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a moz-do-not-send="true"
href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users"
rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-<wbr>bin/mailman/listinfo/users</a><br>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a>
<a class="moz-txt-link-freetext" href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a>
</pre>
</blockquote>
<br>
</body>
</html>