[SR-Users] Huge DB load while using location and registrar module (save()).

[Sergiu Pojoga]

The other option you could do is have usrloc DB in delayed mode. Then, call
RPC *ul.flush* in your web app before displaying registration status.
http://www.kamailio.org/docs/docbooks/devel/rpc_list/rpc_list.html#ul.flush


Not to mention usrloc can be easily optimized by using htable for
performance and less stress on DB.
https://www.kamailio.org/events/2017-KamailioWorld/Day0/W05-Daniel-Constantin.Mierla-Kamailio-Optimizations.pdf


Cheers.

On Thu, Jan 9, 2020 at 8:45 AM Voip support <voipexpert0 at gmail.com> wrote:

> Thank you for the idea, it looks great.
> However what application i could use to consume the data and put it in DB.
> Is this websocket connection? Which when connect to EVAPI address will
> receive the JSON data from kamailio ?
>
> 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.
> The issue is when people register over kamailio to our sip server then we
> dont know the real source IP of client.
> We want to store this information somewhere for reference.
> So we have active registrations list and know the login and user-agent and
> source ip of REGISTER etc...
>
> Best regards,
> Tom
>
> czw., 9 sty 2020 o 12:47 E. Schmidbauer <eschmidbauer at gmail.com>
> napisał(a):
>
>>  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." ?
>> 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.
>> you can us routes like:
>>
>> route[SAVE_LOCATION] {
>>     if (!save("location", "0x04")) {
>>         sl_reply_error();
>>         exit;
>>     }
>>     route(REGISTER_UPDATE_EVENT)
>> }
>>
>> event_route[usrloc:contact-expired] {
>>     route(REGISTER_EXPIRE_EVENT);
>> }
>>
>> route[REGISTER_UPDATE_EVENT] {
>>     if (reg_fetch_contacts("location", "$fu", "caller")) {
>>         $var(i) = 0;
>>         while ($var(i) < $(ulc(caller=>count))) {
>>
>> evapi_relay("{\"event\":\"register-update\",\n\"data\":{\"aor\":\"$(ulc(caller=>aor))\",\"addr\":\"$(ulc(caller=>addr)[$var(i)])\"\n}");
>>             $var(i) = $var(i) + 1;
>>         }
>>     }
>> }
>>
>> route[REGISTER_EXPIRE_EVENT] {
>>     xlog("L_INFO", "registration [$ulc(exp=>aor)] expired");
>>
>> evapi_relay("{\"event\":\"register-expire\",\n\"data\":{\"aor\":\"$ulc(exp=>aor)}\n}");
>> }
>>
>> On Thu, Jan 9, 2020 at 3:14 AM Voip support <voipexpert0 at gmail.com>
>> wrote:
>>
>>> Dear Community,
>>>
>>> I am facing an issue with using the usrloc and registrar module to save
>>> registration informations in database.
>>> 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).
>>> So i think its location module.
>>>
>>> I tried db_mode=3 and db_mode=2 but both create a lot of load on
>>> database.
>>>
>>> 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.
>>> It needs to be saved in table on successfull REGISTRATION and removed
>>> when registration is expired or somebody UN-REGISTER.
>>>
>>> When used location module for that there was lots of queries like this
>>> (20000-50000 per second):
>>>
>>> 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
>>>
>>> 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 at xxxx
>>> ',`instance`=NULL,`reg_id`=0,`server_id`=0,`connection_id`=-1,`keepalive`=1,`contact`='sip:1019 at xxx:5060'
>>> where `ruid`='uloc-5e1642a3-2b90-0161'
>>>
>>> Looking up for ideas how to resolve this issue.
>>> I was thinking of implementing INSERT UPDATE and DELETE based on SQLops.
>>> But how to recognize that REGISTER was expiered?
>>>
>>> I would like location module to work mostly like in-memory.
>>> For each new registration INSERT entry to DB, each REGISTER re-new an
>>> UPDATE or DELETE should be triggered.
>>>  So i would expect for 1000 users around 2000 operations per minute.
>>>
>>> Best regards,
>>> Tom
>>>
>>> _______________________________________________
>>> Kamailio (SER) - Users Mailing List
>>> sr-users at lists.kamailio.org
>>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>>>
>> _______________________________________________
>> Kamailio (SER) - Users Mailing List
>> sr-users at lists.kamailio.org
>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>>
> _______________________________________________
> Kamailio (SER) - Users Mailing List
> sr-users at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20200109/899d80b4/attachment.html>