<div dir="ltr">This isn't normal at all.<div><br></div><div>I have ~2500 registered users (tcp) with db_mode 1 and i see around ~300 sql queries per second.</div><div><br></div><div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div>Regards,</div><div><br></div>David Villasmil<div>email: <a href="mailto:david.villasmil.work@gmail.com" target="_blank">david.villasmil.work@gmail.com</a></div><div>phone: +34669448337</div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 9, 2020 at 4:23 PM Sergiu Pojoga <<a href="mailto:pojogas@gmail.com">pojogas@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">The other option you could do is have usrloc DB in delayed mode. Then, call RPC <b>ul.flush</b> in your web app before displaying registration status.<div><a href="http://www.kamailio.org/docs/docbooks/devel/rpc_list/rpc_list.html#ul.flush" target="_blank">http://www.kamailio.org/docs/docbooks/devel/rpc_list/rpc_list.html#ul.flush</a> <br></div><div><div><br></div><div>Not to mention usrloc can be easily optimized by using htable for performance and less stress on DB.</div><div><a href="https://www.kamailio.org/events/2017-KamailioWorld/Day0/W05-Daniel-Constantin.Mierla-Kamailio-Optimizations.pdf" target="_blank">https://www.kamailio.org/events/2017-KamailioWorld/Day0/W05-Daniel-Constantin.Mierla-Kamailio-Optimizations.pdf</a> <br></div><div><br></div><div>Cheers.</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 9, 2020 at 8:45 AM Voip support <<a href="mailto:voipexpert0@gmail.com" target="_blank">voipexpert0@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Thank you for the idea, it looks great. <br>However what application i could use to consume the data and put it in DB. <br>Is this websocket connection? Which when connect to EVAPI address will receive the JSON data from kamailio ? <div><br></div><div>I wanted to show the regsistered clients in an web interface because i use kamaiio as proxy with dispatcher to handle active - passive load balancing. If main server is down we use it to route register and all other requests to secondary server.<br>The issue is when people register over kamailio to our sip server then we dont know the real source IP of client.<br>We want to store this information somewhere for reference.<br>So we have active registrations list and know the login and user-agent and source ip of REGISTER etc...</div><div><br></div><div>Best regards,</div><div>Tom</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">czw., 9 sty 2020 o 12:47 E. Schmidbauer <<a href="mailto:eschmidbauer@gmail.com" target="_blank">eschmidbauer@gmail.com</a>> napisał(a):<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"> is there a particular reason you need the db updated in real-time and "know the count of registered users and the source IP / user-agent / username data." ?<div>if it's for something outside of kamailio, id recommend using a module like evapi to push the data in real-time to an application that can consume it.</div><div>you can us routes like:</div><div><br></div><div>route[SAVE_LOCATION] {<br> if (!save("location", "0x04")) {<br> sl_reply_error();<br> exit;<br> }<br> route(REGISTER_UPDATE_EVENT)<br>}<br></div><div><br></div><div>event_route[usrloc:contact-expired] {<br> route(REGISTER_EXPIRE_EVENT);<br>}<br></div><div><br></div><div>route[REGISTER_UPDATE_EVENT] {<br> if (reg_fetch_contacts("location", "$fu", "caller")) {<br> $var(i) = 0;<br> while ($var(i) < $(ulc(caller=>count))) {<br> evapi_relay("{\"event\":\"register-update\",\n\"data\":{\"aor\":\"$(ulc(caller=>aor))\",\"addr\":\"$(ulc(caller=>addr)[$var(i)])\"\n}");<br> $var(i) = $var(i) + 1;<br> }<br> }<br>}<br><br>route[REGISTER_EXPIRE_EVENT] {<br> xlog("L_INFO", "registration [$ulc(exp=>aor)] expired");<br> evapi_relay("{\"event\":\"register-expire\",\n\"data\":{\"aor\":\"$ulc(exp=>aor)}\n}");<br>}<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 9, 2020 at 3:14 AM Voip support <<a href="mailto:voipexpert0@gmail.com" target="_blank">voipexpert0@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Dear Community,<div><br></div><div>I am facing an issue with using the usrloc and registrar module to save registration informations in database.<br>It works for me but when tested yesterday with around 1500-1600 users i got a lot of queries on database mostly SELECT. I figured out that changing db_mode to 0 resolves the issue because it works totally in memory (almost no select query on DB).</div><div>So i think its location module.<br><br>I tried db_mode=3 and db_mode=2 but both create a lot of load on database.<br><br></div><div>The only function i need is to store some information of REGISTER in DB to know the count of registered users and the source IP / user-agent / username data.<br>It needs to be saved in table on successfull REGISTRATION and removed when registration is expired or somebody UN-REGISTER.<br><br>When used location module for that there was lots of queries like this (20000-50000 per second): </div><div><br></div><div>select `contact`,`expires`,`q`,`callid`,`cseq`,`flags`,`cflags`,`user_agent`,`received`,`path`,`socket`,`methods`,`last_modified`,`ruid`,`instance`,`reg_id`,`server_id`,`connection_id`,`keepalive` from `location` where `username`='xxxx' order by q<br></div><div><br></div><div>update `location` set `expires`='2020-01-08 18:51:39',`q`=-1.000000 ,`cseq`=2042,`flags`=0,`cflags`=64,`user_agent`='n/a',`received`='sip:xxxx:5060',`path`=NULL,`socket`='udp:xxxx:20051',`methods`=NULL,`last_modified`='2020-01-08 18:50:39',`callid`='1205705227@xxxx',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019@xxx:5060' where `ruid`='uloc-5e1642a3-2b90-0161'<br></div><div><br></div><div>Looking up for ideas how to resolve this issue.<br>I was thinking of implementing INSERT UPDATE and DELETE based on SQLops. <br>But how to recognize that REGISTER was expiered?<br><br>I would like location module to work mostly like in-memory. <br>For each new registration INSERT entry to DB, each REGISTER re-new an UPDATE or DELETE should be triggered.<br> So i would expect for 1000 users around 2000 operations per minute.</div><div><br></div><div>Best regards,<br>Tom</div><div><br></div></div>
_______________________________________________<br>
Kamailio (SER) - Users Mailing List<br>
<a href="mailto:sr-users@lists.kamailio.org" target="_blank">sr-users@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</blockquote></div>
_______________________________________________<br>
Kamailio (SER) - Users Mailing List<br>
<a href="mailto:sr-users@lists.kamailio.org" target="_blank">sr-users@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</blockquote></div>
_______________________________________________<br>
Kamailio (SER) - Users Mailing List<br>
<a href="mailto:sr-users@lists.kamailio.org" target="_blank">sr-users@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</blockquote></div>
_______________________________________________<br>
Kamailio (SER) - Users Mailing List<br>
<a href="mailto:sr-users@lists.kamailio.org" target="_blank">sr-users@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users</a><br>
</blockquote></div>