<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 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 href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a 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 href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a 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>