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

[Voip support]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20200109/6775bf4d/attachment.html>